Skip site navigation (1) Skip section navigation (2)

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

From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
To: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
Cc: pgsql-sql(at)postgreSQL(dot)org, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Re: [SQL] Oddities with NULL and GROUP BY
Date: 1999-05-19 15:31:19
Message-ID: l03130306b36887af8f7d@[147.233.159.109] (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-hackerspgsql-sql
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



In response to

pgsql-hackers by date

Next:From: Herouth MaozDate: 1999-05-19 15:44:32
Subject: Re: [HACKERS] Re: [SQL] Oddities with NULL and GROUP BY
Previous:From: secretDate: 1999-05-19 15:28:42
Subject: Re: [HACKERS] Re: [SQL] Oddities with NULL and GROUP BY

pgsql-sql by date

Next:From: Roberto FicheraDate: 1999-05-19 15:44:03
Subject: Testing permissions
Previous:From: secretDate: 1999-05-19 15:28:42
Subject: Re: [HACKERS] Re: [SQL] Oddities with NULL and GROUP BY

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group