Re: [HACKERS] Re: [SQL] Oddities with NULL and GROUP BY

At 18:28 +0300 on 19/05/1999, secret wrote:

> Sigh. PostgreSQL seems pretty inconsitant in this... GROUP BY with 1
>column
> produces NULLs grouped, with 2 colums it usually seems not to(although I
>somehow
> came up with an example where it did, grr... but lets ignore this since it's
> supposed to "not work" that way.)... Oracle8, DB/2, and Sybase all group
>NULLs
> together, for compatibility sake wouldn't it be reasonable for PostgreSQL
>to do
> the same? Else porting applications could fail miserably when one hits this
> inconsistency.

Please, please, the standard is clear about each of these things
separately. It absolutely says that nulls should be grouped together, and
it absolutely says that the comparison operator should not. It's true that
these things are not consistent, but for each operation, the standard is
quite clear on how it should be done.

In my opinion, there should be null comparison for internal operations, and
null comparison for the comparison operator. For this purpose, what
Postgres does now - return a NULL boolean if one of its operands is null -
is consistent with the standard. For GROUP BY and ORDER BY, they should be
compared equal, and for UNIQUE, they should not be compared.

UNIQUE has explicit mention of nulls in the standard.
ORDER BY has explicit mention of nulls in the standard.
GROUP BY has implicit mention of nulls, by using the term "distinct" which
is defined earlier and includes and explicit mention of nulls.
"=" has explicit mention of nulls in the standard.

And although they are not consistent (some are equal, some are not equal,
and some are unknown), they are covered in no uncertain terms.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma