pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: secret <secret@kearneydev.com>
To: José Soares <jose@sferacarta.com>
Cc: Jackson, DeJuan <djackson@cpsgroup.com>
Cc: PG-SQL <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Oddities with NULL and GROUP BY
Date: Wed, 19 May 1999 09:46:52 -0400
Message-ID: <3742C0CC.FA412323@kearneydev.com> (raw)
References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail>
	<3740164A.E604F6E5@kearneydev.com>
	<374035A7.F51806F@sferacarta.com>

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.




view thread (15+ messages)  latest in thread

Message-ID: <3742C0CC.FA412323@kearneydev.com>
Permalink:  ../3742C0CC.FA412323@kearneydev.com/
Also on:    postgresql.org/message-id/3742C0CC.FA412323@kearneydev.com

 · 

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: secret@kearneydev.com, jose@sferacarta.com, djackson@cpsgroup.com
  Subject: Re: [SQL] Oddities with NULL and GROUP BY
  In-Reply-To: <3742C0CC.FA412323@kearneydev.com>
  References: <D05EF808F2DFD211AE4A00105AA1B5D216ECC9@cpsmail> <3740164A.E604F6E5@kearneydev.com> <374035A7.F51806F@sferacarta.com> <3742C0CC.FA412323@kearneydev.com>

* 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