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

At 18:16 +0300 on 19/05/1999, Thomas Lockhart wrote:

> Interesting. Note that SQL92 asks that any column with the DISTINCT
> constraint contain *only one* NULL value in the entire column. Date
> and Darwen point out that this is inconsistant with the fundamental
> notion of "unknown" and renders DISTINCT constraints without NOT NULL
> to be effectively useless. They recommend against having any DISTINCT
> column without having an additional NOT NULL constraint. We've had
> this discussion wrt Postgres, and concluded that we would diverge from
> the standard by allowing multiple NULL fields in DISTINCT columns, to
> make DISTINCT a useful feature with NULLs. It probably didn't hurt
> that Postgres already behaved this way :)
>
> afaik this last point is the *only* place where Postgres intentionally
> diverges from SQL92, and it was done (or rather retained from existing
> behavior) to make a useless feature useful.

You are probably referring to UNIQUE, not DISTINCT, which is not a
constraint but a query qualifier.

As for uniqueness, as I already quoted, it says:

A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns. In
addition, if the unique constraint was defined with PRIMARY KEY,
then it requires that none of the values in the specified column or
columns be the null value.

Which means that what Postgres does is quite the correct thing. You see?
"No two rows in a table have the same non-null values in the unique
columns". They *can* have the same *null* values!. The constraints only
talks about the non-null ones!

So I think Date and Darwen misinterpreted the rule, and you got this part
right in PostgreSQL. However, there *is* a bug in the GROUP BY behaviour,
at least over one column, and it should be checked if it doesn't work
according to the old convention of comparing nulls internally as they are
compared with the "=" operator.

Herouth

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