Re: [HACKERS] Clarification of NULL values

>
> 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

------------------------------