pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: pgsql-sql@postgresql.org
To: pgsql-hackers@postgresql.org
Subject: Re: [SQL] Oddities with NULL and GROUP BY
Date: Wed, 19 May 1999 16:52:54 +0300
Message-ID: <l03130303b3686def8239@[147.233.159.109]> (raw )
In-Reply-To: <374035A7.F51806F@sferacarta.com >
References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail >
<3740164A.E604F6E5@kearneydev.com >
At 18:28 +0300 on 17/05/1999, José Soares wrote:
> The Pratical SQL Handbook at page 171 says:
> Since nulls represent "the great unknown", there is no way to know
> whether one null is equal to any other null. Each unknown value
> may or may not be different from another.
> However, if the grouping column contains more than one null,
> all of them are put into a single group.
>
> Thus: NULL!=NULL but on GROUP BY it is considered as NULL=NULL.
This is something I have complained about time and again. It is time
something is changed about it, otherwise Postgres will NEVER be a
standard-compliant RDBMS.
The SQL92 text says:
A null value is an implementation-dependent special value that
is distinct from all non-null values of the associated data type.
There is effectively only one null value and that value is a member
of every SQL data type. There is no <literal> for a null value,
although the keyword NULL is used in some places to indicate that a
null value is desired.
Thus, by rights, NULL=NULL should be true, because there is only one null
value.
About the <group by clause>, the text says:
1) The result of the <group by clause> is a partitioning of T into
a set of groups. The set is the minimum number of groups such
that, for each grouping column of each group of more than one
row, no two values of that grouping column are distinct.
And the treatment of nulls is implied from the definition of distinctness:
h) distinct: Two values are said to be not distinct if either:
both are the null value, or they compare equal according to
Subclause 8.2, "<comparison predicate>". Otherwise they are
distinct. Two rows (or partial rows) are distinct if at least
one of their pairs of respective values is distinct. Otherwise
they are not distinct. The result of evaluating whether or not
two values or two rows are distinct is never unknown.
About uniqueness, 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.
One should note, however, that when the actual comparison operator "=" is
used, the standard says that if one of the operands is null, the result of
the comparison is unknown. One should make a distinction between making
comparisons within group by, uniqueness, and other database-logic
operations, and between making the actual comparison (though in my opinion,
this should not be so. Comparing a null value to something should be always
false unless the other something is also null. But that's my opinion and
not the standard's).
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
view thread (15+ messages) latest in thread
Message-ID: <l03130303b3686def8239@[147.233.159.109]>
Permalink: ../l03130303b3686def8239@%5B147.233.159.109%5D/
Also on: postgresql.org/message-id/l03130303b3686def8239@[147.233.159.109]
copy link · copy postgr.es
reply 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, pgsql-hackers@postgresql.org
Subject: Re: [SQL] Oddities with NULL and GROUP BY
In-Reply-To: <l03130303b3686def8239@[147.233.159.109]>
References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail> <3740164A.E604F6E5@kearneydev.com> <l03130303b3686def8239@[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