Re: [SQL] Oddities with NULL and GROUP BY

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