Horton
Menu

Thread

  1. RE: [SQL] Oddities with NULL and GROUP BY

    Jackson, DeJuan <djackson@cpsgroup.com> — 1999-05-14T18:10:25Z

    The behavior is valid, if you define NULL as meaning undefined.
In other words when you define something as NULL you're saying, "I don't
know what it is. It could be equal or not."
	-DEJ

> -----Original Message-----
> From:	secret [SMTP:secret@kearneydev.com]
> Sent:	Friday, May 14, 1999 11:58 AM
> To:	PG-SQL
> Subject:	[SQL] Oddities with NULL and GROUP BY
> 
>     Maybe there is something I don't know about how GROUP BY should
> work, but if I have a table like:
> a,b,c
> 1,1,1
> 1,1,2
> 1,1,3
> 1,2,1
> 1,3,1
> 
> And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
> 1,1,6
> 1,2,1
> 1,3,1
> 
> So whenever a or b changes we get a new summed row, well if I have rows
> where a or b are null, this doesn't happen, infact I seem to get all
> those rows individually... Like if:
> 1,1,1
> 1,1,3
> 1,NULL,10
> 1,NULL,20
> 1,2,3
> 
> I get:
> 1,1,4
> 1,NULL,10
> 1,NULL,20
> 1,2,3
> 
> Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like any other
> value?  Or is there some bit of information I'm missing?  I can set
> everything from NULL to 0 if need be, but I'd rather not...
> 
> David Secret
> MIS Director
> Kearney Development Co., Inc.
>

  2. Re: [SQL] Oddities with NULL and GROUP BY

    secret <secret@kearneydev.com> — 1999-05-17T13:14:50Z

    "Jackson, DeJuan" wrote:

> The behavior is valid, if you define NULL as meaning undefined.
> In other words when you define something as NULL you're saying, "I don't
> know what it is. It could be equal or not."
>         -DEJ
>
> > -----Original Message-----
> > From: secret [SMTP:secret@kearneydev.com]
> > Sent: Friday, May 14, 1999 11:58 AM
> > To:   PG-SQL
> > Subject:      [SQL] Oddities with NULL and GROUP BY
> >
> >     Maybe there is something I don't know about how GROUP BY should
> > work, but if I have a table like:
> > a,b,c
> > 1,1,1
> > 1,1,2
> > 1,1,3
> > 1,2,1
> > 1,3,1
> >
> > And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
> > 1,1,6
> > 1,2,1
> > 1,3,1
> >
> > So whenever a or b changes we get a new summed row, well if I have rows
> > where a or b are null, this doesn't happen, infact I seem to get all
> > those rows individually... Like if:
> > 1,1,1
> > 1,1,3
> > 1,NULL,10
> > 1,NULL,20
> > 1,2,3
> >
> > I get:
> > 1,1,4
> > 1,NULL,10
> > 1,NULL,20
> > 1,2,3
> >
> > Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like any other
> > value?  Or is there some bit of information I'm missing?  I can set
> > everything from NULL to 0 if need be, but I'd rather not...
> >
> > David Secret
> > MIS Director
> > Kearney Development Co., Inc.
> >

    IBM's DB/2 Disagrees, so does Oracle8!


Here is a cut & paste from Oracle SQL+:

SQL> select * from z;

        A         B
--------- ---------
        1         1
        1         2
                  5
                 10

SQL> select a,sum(b) from z group by a;

        A    SUM(B)
--------- ---------
        1         3
                 15

SQL>

    I'm going to report this as a bug now that I've verified 2 major database
vendors perform the task as I would expect them to, and PostgreSQL does it
very differently.  The question is really is NULL=NULL, which I would say it
should be.

  3. Re: [SQL] Oddities with NULL and GROUP BY

    secret <secret@kearneydev.com> — 1999-05-17T13:49:54Z

    "Jackson, DeJuan" wrote:

> The behavior is valid, if you define NULL as meaning undefined.
> In other words when you define something as NULL you're saying, "I don't
> know what it is. It could be equal or not."
>         -DEJ
>
> > -----Original Message-----
> > From: secret [SMTP:secret@kearneydev.com]
> > Sent: Friday, May 14, 1999 11:58 AM
> > To:   PG-SQL
> > Subject:      [SQL] Oddities with NULL and GROUP BY
> >
> >     Maybe there is something I don't know about how GROUP BY should
> > work, but if I have a table like:
> > a,b,c
> > 1,1,1
> > 1,1,2
> > 1,1,3
> > 1,2,1
> > 1,3,1
> >
> > And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
> > 1,1,6
> > 1,2,1
> > 1,3,1
> >
> > So whenever a or b changes we get a new summed row, well if I have rows
> > where a or b are null, this doesn't happen, infact I seem to get all
> > those rows individually... Like if:
> > 1,1,1
> > 1,1,3
> > 1,NULL,10
> > 1,NULL,20
> > 1,2,3
> >
> > I get:
> > 1,1,4
> > 1,NULL,10
> > 1,NULL,20
> > 1,2,3
> >
> > Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like any other
> > value?  Or is there some bit of information I'm missing?  I can set
> > everything from NULL to 0 if need be, but I'd rather not...
> >
> > David Secret
> > MIS Director
> > Kearney Development Co., Inc.
> >

    Oh, I just observed this oddity... PostgreSQL groups just fine when there
is a table of 2 fields a int4, b int4...

SELECT a,sum(b) FROM z GROUP BY a         Groups NULLs fine
SELECT a,b,sum(c) FROM z GROUP BY a,b    Error in grouping NULLs in b...

  4. Re: [SQL] Oddities with NULL and GROUP BY

    jose <jose@sferacarta.com> — 1999-05-17T15:28:39Z

    secret ha scritto:

> "Jackson, DeJuan" wrote:
>
> > The behavior is valid, if you define NULL as meaning undefined.
> > In other words when you define something as NULL you're saying, "I don't
> > know what it is. It could be equal or not."
> >         -DEJ
> >
> > > -----Original Message-----
> > > From: secret [SMTP:secret@kearneydev.com]
> > > Sent: Friday, May 14, 1999 11:58 AM
> > > To:   PG-SQL
> > > Subject:      [SQL] Oddities with NULL and GROUP BY
> > >
> > >     Maybe there is something I don't know about how GROUP BY should
> > > work, but if I have a table like:
> > > a,b,c
> > > 1,1,1
> > > 1,1,2
> > > 1,1,3
> > > 1,2,1
> > > 1,3,1
> > >
> > > And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
> > > 1,1,6
> > > 1,2,1
> > > 1,3,1
> > >
> > > So whenever a or b changes we get a new summed row, well if I have rows
> > > where a or b are null, this doesn't happen, infact I seem to get all
> > > those rows individually... Like if:
> > > 1,1,1
> > > 1,1,3
> > > 1,NULL,10
> > > 1,NULL,20
> > > 1,2,3
> > >
> > > I get:
> > > 1,1,4
> > > 1,NULL,10
> > > 1,NULL,20
> > > 1,2,3
> > >
> > > Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like any other
> > > value?  Or is there some bit of information I'm missing?  I can set
> > > everything from NULL to 0 if need be, but I'd rather not...
> > >
> > > David Secret
> > > MIS Director
> > > Kearney Development Co., Inc.
> > >
>
>     IBM's DB/2 Disagrees, so does Oracle8!
>
> Here is a cut & paste from Oracle SQL+:
>
> SQL> select * from z;
>
>         A         B
> --------- ---------
>         1         1
>         1         2
>                   5
>                  10
>
> SQL> select a,sum(b) from z group by a;
>
>         A    SUM(B)
> --------- ---------
>         1         3
>                  15
>
> SQL>
>
>     I'm going to report this as a bug now that I've verified 2 major database
> vendors perform the task as I would expect them to, and PostgreSQL does it
> very differently.  The question is really is NULL=NULL, which I would say it
> should be.

I tried it in PostgreSQL 6.5beta1 with the same result:

select * from z;
a| b
-+--
1| 1
1| 2
 | 5
 |10
(4 rows)

select a,sum(b) from z group by a;
a|sum
-+---
1|  3
 | 15
(2 rows)

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.

José




--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

  5. Re: [SQL] Oddities with NULL and GROUP BY

    secret <secret@kearneydev.com> — 1999-05-19T13:46:52Z

    José Soares wrote:

> secret ha scritto:
>
>> "Jackson, DeJuan" wrote:
>>
>> > The behavior is valid, if you define NULL as meaning undefined.
>> > In other words when you define something as NULL you're saying, "I
>> don't
>> > know what it is. It could be equal or not."
>> >         -DEJ
>> >
>> > > -----Original Message-----
>> > > From: secret [SMTP:secret@kearneydev.com]
>> > > Sent: Friday, May 14, 1999 11:58 AM
>> > > To:   PG-SQL
>> > > Subject:      [SQL] Oddities with NULL and GROUP BY
>> > >
>> > >     Maybe there is something I don't know about how GROUP BY
>> should
>> > > work, but if I have a table like:
>> > > a,b,c
>> > > 1,1,1
>> > > 1,1,2
>> > > 1,1,3
>> > > 1,2,1
>> > > 1,3,1
>> > >
>> > > And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
>> > > 1,1,6
>> > > 1,2,1
>> > > 1,3,1
>> > >
>> > > So whenever a or b changes we get a new summed row, well if I
>> have rows
>> > > where a or b are null, this doesn't happen, infact I seem to get
>> all
>> > > those rows individually... Like if:
>> > > 1,1,1
>> > > 1,1,3
>> > > 1,NULL,10
>> > > 1,NULL,20
>> > > 1,2,3
>> > >
>> > > I get:
>> > > 1,1,4
>> > > 1,NULL,10
>> > > 1,NULL,20
>> > > 1,2,3
>> > >
>> > > Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like
>> any other
>> > > value?  Or is there some bit of information I'm missing?  I can
>> set
>> > > everything from NULL to 0 if need be, but I'd rather not...
>> > >
>> > > David Secret
>> > > MIS Director
>> > > Kearney Development Co., Inc.
>> > >
>>
>>     IBM's DB/2 Disagrees, so does Oracle8!
>>
>> Here is a cut & paste from Oracle SQL+:
>>
>> SQL> select * from z;
>>
>>         A         B
>> --------- ---------
>>         1         1
>>         1         2
>>                   5
>>                  10
>>
>> SQL> select a,sum(b) from z group by a;
>>
>>         A    SUM(B)
>> --------- ---------
>>         1         3
>>                  15
>>
>> SQL>
>>
>>     I'm going to report this as a bug now that I've verified 2 major
>> database
>> vendors perform the task as I would expect them to, and PostgreSQL
>> does it
>> very differently.  The question is really is NULL=NULL, which I
>> would say it
>> should be.
>
>
> I tried it in PostgreSQL 6.5beta1 with the same result:
>
> select * from z;
> a| b
> -+--
> 1| 1
> 1| 2
>  | 5
>  |10
> (4 rows)
>
> select a,sum(b) from z group by a;
> a|sum
> -+---
> 1|  3
>  | 15
> (2 rows)
>
> 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.
>
> José
>
>
>
>
> --
> ______________________________________________________________
> PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Jose'
>

    Wonderful, that's as I expected.  However please try this in 6.5
Beta1,
CREATE TABLE z(a int4,b int4, c int4);
INSERT INTO z VALUES (1,1,1);
INSERT INTO z VALUES (1,1,2);
INSERT INTO z(a,c) VALUES (2,1);
INSERT INTO z(a,c) VALUES (2,2);

SELECT a,b,sum(c) FROM z GROUP BY a,b

GROUPing in PostgreSQL w/NULLs works just fine when there is only 1
column, however when one throws 2 in, the 2nd one having NULLs it starts
failing.  Your example demonstrates the right answer for 1 group by
column, try it with 2 and I expect 6.5beta1 will fail as 6.4.2 does.

    As to NULL=NULL or NULL!=NULL, evadentally my estimation of why the
problem is occuring was wrong. :)  But from the SQL handbook we
definately have a bug here.

David Secret
MIS Director
Kearney Development Co., Inc.

  6. Re: [SQL] Oddities with NULL and GROUP BY

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-05-19T13:52:54Z

    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

  7. Re: [SQL] Oddities with NULL and GROUP BY

    jose <jose@sferacarta.com> — 1999-05-19T14:38:09Z

    Here the result:

SELECT a,b,sum(c) FROM z GROUP BY a,b;
a|b|sum
-+-+---
1|1|  3
2| |  3
(2 rows)


secret ha scritto:

> José Soares wrote:
>
> > secret ha scritto:
> >
> >> "Jackson, DeJuan" wrote:
> >>
> >> > The behavior is valid, if you define NULL as meaning undefined.
> >> > In other words when you define something as NULL you're saying, "I
> >> don't
> >> > know what it is. It could be equal or not."
> >> >         -DEJ
> >> >
> >> > > -----Original Message-----
> >> > > From: secret [SMTP:secret@kearneydev.com]
> >> > > Sent: Friday, May 14, 1999 11:58 AM
> >> > > To:   PG-SQL
> >> > > Subject:      [SQL] Oddities with NULL and GROUP BY
> >> > >
> >> > >     Maybe there is something I don't know about how GROUP BY
> >> should
> >> > > work, but if I have a table like:
> >> > > a,b,c
> >> > > 1,1,1
> >> > > 1,1,2
> >> > > 1,1,3
> >> > > 1,2,1
> >> > > 1,3,1
> >> > >
> >> > > And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
> >> > > 1,1,6
> >> > > 1,2,1
> >> > > 1,3,1
> >> > >
> >> > > So whenever a or b changes we get a new summed row, well if I
> >> have rows
> >> > > where a or b are null, this doesn't happen, infact I seem to get
> >> all
> >> > > those rows individually... Like if:
> >> > > 1,1,1
> >> > > 1,1,3
> >> > > 1,NULL,10
> >> > > 1,NULL,20
> >> > > 1,2,3
> >> > >
> >> > > I get:
> >> > > 1,1,4
> >> > > 1,NULL,10
> >> > > 1,NULL,20
> >> > > 1,2,3
> >> > >
> >> > > Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like
> >> any other
> >> > > value?  Or is there some bit of information I'm missing?  I can
> >> set
> >> > > everything from NULL to 0 if need be, but I'd rather not...
> >> > >
> >> > > David Secret
> >> > > MIS Director
> >> > > Kearney Development Co., Inc.
> >> > >
> >>
> >>     IBM's DB/2 Disagrees, so does Oracle8!
> >>
> >> Here is a cut & paste from Oracle SQL+:
> >>
> >> SQL> select * from z;
> >>
> >>         A         B
> >> --------- ---------
> >>         1         1
> >>         1         2
> >>                   5
> >>                  10
> >>
> >> SQL> select a,sum(b) from z group by a;
> >>
> >>         A    SUM(B)
> >> --------- ---------
> >>         1         3
> >>                  15
> >>
> >> SQL>
> >>
> >>     I'm going to report this as a bug now that I've verified 2 major
> >> database
> >> vendors perform the task as I would expect them to, and PostgreSQL
> >> does it
> >> very differently.  The question is really is NULL=NULL, which I
> >> would say it
> >> should be.
> >
> >
> > I tried it in PostgreSQL 6.5beta1 with the same result:
> >
> > select * from z;
> > a| b
> > -+--
> > 1| 1
> > 1| 2
> >  | 5
> >  |10
> > (4 rows)
> >
> > select a,sum(b) from z group by a;
> > a|sum
> > -+---
> > 1|  3
> >  | 15
> > (2 rows)
> >
> > 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.
> >
> > José
> >
> >
> >
> >
> > --
> > ______________________________________________________________
> > PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > Jose'
> >
>
>     Wonderful, that's as I expected.  However please try this in 6.5
> Beta1,
> CREATE TABLE z(a int4,b int4, c int4);
> INSERT INTO z VALUES (1,1,1);
> INSERT INTO z VALUES (1,1,2);
> INSERT INTO z(a,c) VALUES (2,1);
> INSERT INTO z(a,c) VALUES (2,2);
>
> SELECT a,b,sum(c) FROM z GROUP BY a,b
>
> GROUPing in PostgreSQL w/NULLs works just fine when there is only 1
> column, however when one throws 2 in, the 2nd one having NULLs it starts
> failing.  Your example demonstrates the right answer for 1 group by
> column, try it with 2 and I expect 6.5beta1 will fail as 6.4.2 does.
>
>     As to NULL=NULL or NULL!=NULL, evadentally my estimation of why the
> problem is occuring was wrong. :)  But from the SQL handbook we
> definately have a bug here.
>
> David Secret
> MIS Director
> Kearney Development Co., Inc.

> ______________________________________________________________

PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

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

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-05-19T15:16:52Z

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

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

    secret <secret@kearneydev.com> — 1999-05-19T15:28:42Z

    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

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

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-05-19T15:31:19Z

    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

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

    Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-05-19T15:44:32Z

    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

  12. Re: [SQL] Oddities with NULL and GROUP BY

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-07-07T18:23:55Z

    
Looks like this is fixed in 6.5 too.
	
	a|b|sum
	-+-+---
	1|1|  3
	2| |  3
	(2 rows)

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Jos_ Soares wrote:
> 
> > secret ha scritto:
> >
> >> "Jackson, DeJuan" wrote:
> >>
> >> > The behavior is valid, if you define NULL as meaning undefined.
> >> > In other words when you define something as NULL you're saying, "I
> >> don't
> >> > know what it is. It could be equal or not."
> >> >         -DEJ
> >> >
> >> > > -----Original Message-----
> >> > > From: secret [SMTP:secret@kearneydev.com]
> >> > > Sent: Friday, May 14, 1999 11:58 AM
> >> > > To:   PG-SQL
> >> > > Subject:      [SQL] Oddities with NULL and GROUP BY
> >> > >
> >> > >     Maybe there is something I don't know about how GROUP BY
> >> should
> >> > > work, but if I have a table like:
> >> > > a,b,c
> >> > > 1,1,1
> >> > > 1,1,2
> >> > > 1,1,3
> >> > > 1,2,1
> >> > > 1,3,1
> >> > >
> >> > > And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
> >> > > 1,1,6
> >> > > 1,2,1
> >> > > 1,3,1
> >> > >
> >> > > So whenever a or b changes we get a new summed row, well if I
> >> have rows
> >> > > where a or b are null, this doesn't happen, infact I seem to get
> >> all
> >> > > those rows individually... Like if:
> >> > > 1,1,1
> >> > > 1,1,3
> >> > > 1,NULL,10
> >> > > 1,NULL,20
> >> > > 1,2,3
> >> > >
> >> > > I get:
> >> > > 1,1,4
> >> > > 1,NULL,10
> >> > > 1,NULL,20
> >> > > 1,2,3
> >> > >
> >> > > Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like
> >> any other
> >> > > value?  Or is there some bit of information I'm missing?  I can
> >> set
> >> > > everything from NULL to 0 if need be, but I'd rather not...
> >> > >
> >> > > David Secret
> >> > > MIS Director
> >> > > Kearney Development Co., Inc.
> >> > >
> >>
> >>     IBM's DB/2 Disagrees, so does Oracle8!
> >>
> >> Here is a cut & paste from Oracle SQL+:
> >>
> >> SQL> select * from z;
> >>
> >>         A         B
> >> --------- ---------
> >>         1         1
> >>         1         2
> >>                   5
> >>                  10
> >>
> >> SQL> select a,sum(b) from z group by a;
> >>
> >>         A    SUM(B)
> >> --------- ---------
> >>         1         3
> >>                  15
> >>
> >> SQL>
> >>
> >>     I'm going to report this as a bug now that I've verified 2 major
> >> database
> >> vendors perform the task as I would expect them to, and PostgreSQL
> >> does it
> >> very differently.  The question is really is NULL=NULL, which I
> >> would say it
> >> should be.
> >
> >
> > I tried it in PostgreSQL 6.5beta1 with the same result:
> >
> > select * from z;
> > a| b
> > -+--
> > 1| 1
> > 1| 2
> >  | 5
> >  |10
> > (4 rows)
> >
> > select a,sum(b) from z group by a;
> > a|sum
> > -+---
> > 1|  3
> >  | 15
> > (2 rows)
> >
> > 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.
> >
> > Jos_
> >
> >
> >
> >
> > --
> > ______________________________________________________________
> > PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
> > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > Jose'
> >
> 
>     Wonderful, that's as I expected.  However please try this in 6.5
> Beta1,
> CREATE TABLE z(a int4,b int4, c int4);
> INSERT INTO z VALUES (1,1,1);
> INSERT INTO z VALUES (1,1,2);
> INSERT INTO z(a,c) VALUES (2,1);
> INSERT INTO z(a,c) VALUES (2,2);
> 
> SELECT a,b,sum(c) FROM z GROUP BY a,b
> 
> GROUPing in PostgreSQL w/NULLs works just fine when there is only 1
> column, however when one throws 2 in, the 2nd one having NULLs it starts
> failing.  Your example demonstrates the right answer for 1 group by
> column, try it with 2 and I expect 6.5beta1 will fail as 6.4.2 does.
> 
>     As to NULL=NULL or NULL!=NULL, evadentally my estimation of why the
> problem is occuring was wrong. :)  But from the SQL handbook we
> definately have a bug here.
> 
> David Secret
> MIS Director
> Kearney Development Co., Inc.
> 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026