Обсуждение: [HACKERS] Clarification of NULL values
> > >From X3H2-97-030 (I think) > c) Let P be any row of TS and let Q be any other row of TS, > and let PVi and QVi be the values of Ki in these rows, > respectively. The relative position of rows P and Q in the > result is determined by comparing PVi and QVi according to > the rules of Subclause 8.2, "<comparison predicate>", where > the <comp op> is the applicable <comp op> for Ki, with the > following special treatment of null values. Whether a sort > key value that is null is considered greater or less than > a non-null value is implementation-defined, but all sort > key values that are null shall either be considered greater > than all non-null values or be considered less than all non- > null values. PVi is said to precede QVi if the value of the > <comparison predicate> "PVi <comp op> QVi" is true for the > applicable <comp op>. > And if anyone can follow that....... > Adrian > Basically, it means that NULLs are always to be treated as greater or less than any non-NULL value. Which of these it is is up to the programmer/designer, but it must be consistant. Otherwise stated, regardless of NULLs, a set of records should always sort in the same relative order, ascending or descending. (Gee, that ALMOST made more sense than the standard text... *sigh*) - -Brandon :) ------------------------------
adrian@hottub.org said: > > > >From X3H2-97-030 (I think) > > > c) Let P be any row of TS and let Q be any other row of TS, > > > and let PVi and QVi be the values of Ki in these rows, > > > respectively. The relative position of rows P and Q in the > > > result is determined by comparing PVi and QVi according to > > > the rules of Subclause 8.2, "<comparison predicate>", where > > > the <comp op> is the applicable <comp op> for Ki, with the > > > following special treatment of null values. Whether a sort > > > key value that is null is considered greater or less than > > > a non-null value is implementation-defined, but all sort > > > key values that are null shall either be considered greater > > > than all non-null values or be considered less than all non- > > > null values. PVi is said to precede QVi if the value of the > > > <comparison predicate> "PVi <comp op> QVi" is true for the > > > applicable <comp op>. > > > And if anyone can follow that....... > > > Adrian > > > > > Basically, it means that NULLs are always to be treated as greater or > > less than any non-NULL value. Which of these it is is up to the > > programmer/designer, but it must be consistant. Otherwise stated, > > regardless of NULLs, a set of records should always sort in the same > > relative order, ascending or descending. > > (Gee, that ALMOST made more sense than the standard text... *sigh*) > > > Oh, I knew this...My point is that the standards writers use the most > obfuscated language. Actually, this section is fairly readable compared > to some of them. For example, the CuRSOR part goes on for maybe five > pages just explaining what a cursor is. > Yeah, I think they do that mostly to keep everyone happy. If the language is very vague, then it is almost impossible to create something that DOESN'T comply with the standard, due to the incredible range of possible interpretations. In any case, I did want to make the point that I DON'T think Postgres is doing this correctly right now, as I believe the examples in the original posting showed the NULL-valued records at the end of the sorted list both when sorted ascending and descending. I believe the standard is saying that NULLs should always be either at the beginning or end of sorts, and thus at OPPOSITE ends of ascending and descending sorts. - -Brandon :) ------------------------------
> > adrian@hottub.org said: > > > > >From X3H2-97-030 (I think) > > > > c) Let P be any row of TS and let Q be any other row of TS, > > > > and let PVi and QVi be the values of Ki in these rows, > > > > respectively. The relative position of rows P and Q in the > > > > result is determined by comparing PVi and QVi according to > > > > the rules of Subclause 8.2, "<comparison predicate>", where > > > > the <comp op> is the applicable <comp op> for Ki, with the > > > > following special treatment of null values. Whether a sort > > > > key value that is null is considered greater or less than > > > > a non-null value is implementation-defined, but all sort > > > > key values that are null shall either be considered greater > > > > than all non-null values or be considered less than all non- > > > > null values. PVi is said to precede QVi if the value of the > > > > <comparison predicate> "PVi <comp op> QVi" is true for the > > > > applicable <comp op>. > > > > And if anyone can follow that....... > > > > Adrian > > > > > > > Basically, it means that NULLs are always to be treated as greater or > > > less than any non-NULL value. Which of these it is is up to the > > > programmer/designer, but it must be consistant. Otherwise stated, > > > regardless of NULLs, a set of records should always sort in the same > > > relative order, ascending or descending. > > > (Gee, that ALMOST made more sense than the standard text... *sigh*) > > > > > Oh, I knew this...My point is that the standards writers use the most > > obfuscated language. Actually, this section is fairly readable compared > > to some of them. For example, the CuRSOR part goes on for maybe five > > pages just explaining what a cursor is. > > > Yeah, I think they do that mostly to keep everyone happy. If the > language is very vague, then it is almost impossible to create > something that DOESN'T comply with the standard, due to the incredible > range of possible interpretations. > In any case, I did want to make the point that I DON'T think Postgres > is doing this correctly right now, as I believe the examples in the > original posting showed the NULL-valued records at the end of the > sorted list both when sorted ascending and descending. I believe the > standard is saying that NULLs should always be either at the beginning > or end of sorts, and thus at OPPOSITE ends of ascending and descending > sorts. I see the distinction now, and I think you are correct that the sorting should make NULL's come out at the beginning or end depending on the order of the sort. I will add it to the TODO list. - -- Bruce Momjian maillist@candle.pha.pa.us ------------------------------
> >
> Yeah, I think they do that mostly to keep everyone happy. If the
> language is very vague, then it is almost impossible to create
> something that DOESN'T comply with the standard, due to the incredible
> range of possible interpretations.
> In any case, I did want to make the point that I DON'T think Postgres
> is doing this correctly right now, as I believe the examples in the
> original posting showed the NULL-valued records at the end of the
> sorted list both when sorted ascending and descending. I believe the
> standard is saying that NULLs should always be either at the beginning
> or end of sorts, and thus at OPPOSITE ends of ascending and descending
> sorts.
>
You know that interpretation thing....
If you look at other standards for interpretation, for example the JDBC
standards, there are four possibilities:
NULLs sort high
NULLs sort low
NULLs always at end
NULLs always at beginning
I think the standard says "do what you want, high or low, beginning or
end - as long as you are consistent"
Adrian
------------------------------