From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
Cc: pgsql-sql@postgreSQL.org
Cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] Oddities with NULL and GROUP BY
Date: Wed, 19 May 1999 18:31:19 +0300
Message-ID: <l03130306b36887af8f7d@[147.233.159.109]> (raw)
In-Reply-To: <3742D5E4.D220A67@alumni.caltech.edu>
References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail>
<3740164A.E604F6E5@kearneydev.com>
<l03130303b3686def8239@[147.233.159.109]>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, lockhart@alumni.caltech.edu, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] Oddities with NULL and GROUP BY
In-Reply-To: <l03130306b36887af8f7d@[147.233.159.109]>
References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail> <3740164A.E604F6E5@kearneydev.com> <l03130303b3686def8239@[147.233.159.109]> <l03130306b36887af8f7d@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox