Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA07852 for ; Wed, 19 May 1999 10:42:25 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.8/NETTuno 3.2) with ESMTP id QAA02263; Wed, 19 May 1999 16:42:03 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10k9NG-0002P1-00; Wed, 19 May 1999 16:39:18 +0000 Message-ID: <3742CCD1.257BEB36@sferacarta.com> Date: Wed, 19 May 1999 16:38:09 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: secret CC: "Jackson, DeJuan" , PG-SQL Subject: Re: [SQL] Oddities with NULL and GROUP BY References: <3740164A.E604F6E5@kearneydev.com> <374035A7.F51806F@sferacarta.com> <3742C0CC.FA412323@kearneydev.com> Content-Type: multipart/alternative; boundary="------------E186622E95E3B9A4AEB6AA09" --------------E186622E95E3B9A4AEB6AA09 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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' --------------E186622E95E3B9A4AEB6AA09 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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'
  --------------E186622E95E3B9A4AEB6AA09--