From: | secret <secret(at)kearneydev(dot)com> |
---|---|
To: | "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com> |
Cc: | PG-SQL <pgsql-sql(at)postgreSQL(dot)org> |
Subject: | Re: [SQL] Oddities with NULL and GROUP BY |
Date: | 1999-05-17 13:14:50 |
Message-ID: | 3740164A.E604F6E5@kearneydev.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
"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(at)kearneydev(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-05-17 13:44:18 | Re: [HACKERS] GEQO optimizer (was Re: Backend message type 0x44 arrived while idle) |
Previous Message | Erik Riedel | 1999-05-17 13:14:35 | Re: [HACKERS] 64-bit hashjoins |
From | Date | Subject | |
---|---|---|---|
Next Message | secret | 1999-05-17 13:49:54 | Re: [SQL] Oddities with NULL and GROUP BY |
Previous Message | Willian Jakobs | 1999-05-17 12:11:51 | creating a new table with data from other tables |