From: | secret <secret(at)kearneydev(dot)com> |
---|---|
To: | sszabo(at)bigpanda(dot)com |
Cc: | pgsql-bugs(at)postgreSQL(dot)org |
Subject: | Re: [BUGS] General Bug Report: GROUP BY with NULL not done properly(Oracle8& DB/2 do this completely different) |
Date: | 1999-07-08 16:00:27 |
Message-ID: | 3784CB1B.60D1A@kearneydev.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
>
>
> When i make the following table:
>
> a|b| c
> -+-+--
> 1|1|10
> 1|2|10
> 1|2| 5
> 2|2|15
> 2|2|-3
> 2|1|50
> 2| |25
> 2| |15
> 1| |34
> 1| |54
> | | 5
> | | 7
> |1| 7
> |4| 7
> |4| 7
> (15 rows)
>
> And do the following query, I get what i think was desired,
> the null/null, null/4, 1/null, 2/null are grouped together into a single
> output row...
>
> sszabo=> select a,b,sum(c) from b group by a,b;
> a|b|sum
> -+-+---
> 1|1| 10
> 1|2| 15
> 1| | 88
> 2|1| 50
> 2|2| 12
> 2| | 40
> |1| 7
> |4| 14
> | | 12
> (9 rows)
>
> sszabo=> select version();
> version
> --------------------------------------------------------------------
> PostgreSQL 6.5.0 on i386-unknown-freebsd3.1, compiled by gcc 2.7.2.1
Smaller examples I try work too under v6.5, but here is an example from a larger one:
SELECT a,b,sum(c) FROM xx GROUP BY a,b ORDER BY a,b;
a | b | sum
..
|102060| 6
|102060| 1
|102060| 6
|102060| 6
|102060| 0
|102060| 6
|102061| 6
|102061| 6
|102061| 6
|102061| 6
|102061| 7
|102084| 10
|102084| 10
|102084| 10
|102085| 4
|102109| 18
|102109| 18
|102109| 54
|102109| 18
|102110| 1
|102110| 1
There are actually 65,000 rows, so I can't quote all of them, but I will give you a dump of the
test table upon request so you can duplicate the results... Here are a couple queries that
illustrate the errors:
ftc=> select count(*) from xx where b=102110;
count
-----
2
(1 row)
ftc=> select count(*) from xx where a is null and b=102110;
count
-----
2
(1 row)
As you see above, the GROUP on a didn't function for b=102110, we have 2 rows, both of which
were returned, both of which A is NULL. If you'd like a dump of this table I'll send it to you and
not cc the list on it... It's a stripped down version of another table I use quite a bit.
Version: [PostgreSQL 6.5.0 on i586-pc-linux-gnulibc1, compiled by gcc 2.7.2.1]
David Secret
MIS Director
Kearney Development Co., Inc.
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 1999-07-09 01:00:58 | Re: Please update and reply |
Previous Message | sszabo | 1999-07-08 14:57:41 | Re: [BUGS] General Bug Report: GROUP BY with NULL not done properly(Oracle8& DB/2 do this completely different) |