Thread

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.