From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: secret <secret@kearneydev.com>
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:44:32 +0300
Message-ID: <l03130307b3688a1e21f0@[147.233.159.109]> (raw)
In-Reply-To: <3742D8AA.3161112D@kearneydev.com>
References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail>
<3740164A.E604F6E5@kearneydev.com>
<l03130303b3686def8239@[147.233.159.109]>
<3742D5E4.D220A67@alumni.caltech.edu>
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
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, secret@kearneydev.com, lockhart@alumni.caltech.edu, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] Oddities with NULL and GROUP BY
In-Reply-To: <l03130307b3688a1e21f0@[147.233.159.109]>
References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail> <3740164A.E604F6E5@kearneydev.com> <l03130303b3686def8239@[147.233.159.109]> <3742D5E4.D220A67@alumni.caltech.edu> <l03130307b3688a1e21f0@[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