Re: [SQL] Oddities with NULL and GROUP BY

secret ha scritto: <blockquote type="CITE">"Jackson, DeJuan" wrote: <p>> The behavior is valid, if you define NULL
asmeaning undefined. <br />> In other words when you define something as NULL you're saying, "I don't <br />>
knowwhat it is. It could be equal or not." <br />>         -DEJ <br />> <br />> > -----Original
Message-----<br />> > From: secret [SMTP:secret@kearneydev.com] <br />> > Sent: Friday, May 14, 1999 11:58
AM<br />> > To:   PG-SQL <br />> > Subject:      [SQL] Oddities with NULL and GROUP BY <br />> > <br
/>>>     Maybe there is something I don't know about how GROUP BY should <br />> > work, but if I have a
tablelike: <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 SELECT a,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
weget a new summed row, well if I have rows <br />> > where a or b are null, this doesn't happen, infact I seem
toget 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'tI get 1,NULL,30?  Ie shouldn't NULL be treated like any other <br />> > value?  Or is there some bit of
informationI'm missing?  I can set <br />> > everything from NULL to 0 if need be, but I'd rather not... <br
/>>> <br />> > David Secret <br />> > MIS Director <br />> > Kearney Development Co., Inc. <br
/>>> <p>    IBM's DB/2 Disagrees, so does Oracle8! <p>Here is a cut & paste from Oracle SQL+: <p>SQL>
select* from z; <p>        A         B <br />--------- --------- <br />        1         1 <br />        1         2
<br/>                  5 <br />                 10 <p>SQL> select a,sum(b) from z group by a; <p>        A    SUM(B)
<br/>--------- --------- <br />        1         3 <br />                 15 <p>SQL> <p>    I'm going to report this
asa bug now that I've verified 2 major database <br />vendors perform the task as I would expect them to, and
PostgreSQLdoes it <br />very differently.  The question is really is NULL=NULL, which I would say it <br />should
be.</blockquote><p><br/>I tried it in PostgreSQL 6.5beta1 with the same result: <p><tt>select * from z;</tt><br
/><tt>a|b</tt><br /><tt>-+--</tt><br /><tt>1| 1</tt><br /><tt>1| 2</tt><br /><tt> | 5</tt><br /><tt> |10</tt><br
/><tt>(4rows)</tt><tt></tt><p><tt>select a,sum(b) from z group by a;</tt><br /><tt>a|sum</tt><br /><tt>-+---</tt><br
/><tt>1| 3</tt><br /><tt> | 15</tt><br /><tt>(2 rows)</tt><p>The Pratical SQL Handbook at page 171 says: <br />Since
nullsrepresent "the great unknown", there is no way to know <br />whether one null is equal to any other null. Each
unknownvalue <br />may or may not be different from another. <br />However, if the grouping column contains more than
onenull, <br />all of them are put into a single group. <p>Thus: NULL!=NULL but on GROUP BY it is considered as
NULL=NULL.<p>José <br />  <br />  <br />  <p>-- <br />______________________________________________________________
<br/>PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 <br
/>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^<br />Jose' <br />