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

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
associateddata type.    There is effectively only one null value and that value is a member    of every SQL data type.
Thereis no <literal> for a null value,    although the keyword NULL is used in some places to indicate that a    null
valueis 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
numberof groups such      that, for each grouping column of each group of more than one      row, no two values of that
groupingcolumn 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
accordingto      Subclause 8.2, "<comparison predicate>". Otherwise they are      distinct. Two rows (or partial rows)
aredistinct if at least      one of their pairs of respective values is distinct. Otherwise      they are not distinct.
Theresult 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
valuesin 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

Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> Thus, by rights, NULL=NULL should be true, because there is only one null
> value.

You are jumping to a conclusion not supported by the text you have
quoted.

It does appear that GROUP BY and DISTINCT should treat all nulls as
falling into the same class, because of

>     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>".

Kindly note, however, that the standards authors felt it necessary to
describe those two cases as separate cases.  If nulls compare as equal,
there would be no need to write more than "Two values are not distinct
if they compare equal".

> 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.

Precisely.  A fortiori, if both operands are null, the result of the
comparison is still unknown.

We do seem to have a bug in GROUP BY/DISTINCT if nulls are producing
more than one output tuple in those operations.  But that has nothing
to do with what the comparison operator produces.
        regards, tom lane

> > 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.

Although I've noticed some questionable statements quoted from this
book, this looks good...

> > 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.

Postgres conforms to SQL92 in this regard. Date and Darwen, "A Guide
to the SQL Standard", 3rd ed., are explicit about this near the top of
page 249:

Duplicates are relevant to the ... GROUP BY ... operations ...
... GROUP BY groups rows together on the basis of duplicate values in
the set of grouping columns (and those sets of grouping column values
can be regarded as "rows" for present purposes). The point is,
however, the definition of duplicate rows requires some refinement in
the presence of nulls. Let "left" and "right" be as defined
(previously). Then "left" and "right" are defined to be "duplicates"
of one another if and only if, for all "i" in the range 1 to "n",
either "left_i" = "right_i" is TRUE, or "left_i" and "right_i" are
both null.

There is a single exception to Postgres' SQL92 conformance wrt NULLs
afaik, involving DISTINCT column constraints which I discuss below.

> > 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.
> 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.

No! An explicit "unknown" = "unknown" in a constraint clause should
always evaluate to FALSE (we'll get to GROUP BY later). SQL92 and all
of my reference books are clear about this. Date and Darwen have a
good discussion of the shortcomings of NULL in SQL92, pointing out
that with NULL handling one would really like a distinct UNKNOWN added
to the possible boolean values TRUE and FALSE so that SQL would have
true three-value logic.

> 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.

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.

> 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).

One can't take a portion of SQL92 statements wrt NULLs and apply it to
all uses of NULL, because SQL92 is not internally consistant in this
regard.

In most GROUP BY situations, a corresponding WHERE col IS NOT NULL is
probably a good idea.

Regards.
                        - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart 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.
>
> Although I've noticed some questionable statements quoted from this
> book, this looks good...
>
> > > 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.
>
> Postgres conforms to SQL92 in this regard. Date and Darwen, "A Guide
> to the SQL Standard", 3rd ed., are explicit about this near the top of
> page 249:
>
> Duplicates are relevant to the ... GROUP BY ... operations ...
> ... GROUP BY groups rows together on the basis of duplicate values in
> the set of grouping columns (and those sets of grouping column values
> can be regarded as "rows" for present purposes). The point is,
> however, the definition of duplicate rows requires some refinement in
> the presence of nulls. Let "left" and "right" be as defined
> (previously). Then "left" and "right" are defined to be "duplicates"
> of one another if and only if, for all "i" in the range 1 to "n",
> either "left_i" = "right_i" is TRUE, or "left_i" and "right_i" are
> both null.
>
> There is a single exception to Postgres' SQL92 conformance wrt NULLs
> afaik, involving DISTINCT column constraints which I discuss below.
>
> > > 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.
> > 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.
>
> No! An explicit "unknown" = "unknown" in a constraint clause should
> always evaluate to FALSE (we'll get to GROUP BY later). SQL92 and all
> of my reference books are clear about this. Date and Darwen have a
> good discussion of the shortcomings of NULL in SQL92, pointing out
> that with NULL handling one would really like a distinct UNKNOWN added
> to the possible boolean values TRUE and FALSE so that SQL would have
> true three-value logic.
>
> > 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.
>
> 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.
>
> > 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).
>
> One can't take a portion of SQL92 statements wrt NULLs and apply it to
> all uses of NULL, because SQL92 is not internally consistant in this
> regard.
>
> In most GROUP BY situations, a corresponding WHERE col IS NOT NULL is
> probably a good idea.
>
> Regards.
>
>                          - Thomas
>
> --
> Thomas Lockhart                         lockhart@alumni.caltech.edu
> South Pasadena, California
   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.

--David

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
valuesin 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

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