From: | Unprivileged user <nobody> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | General Bug Report: GROUP BY with NULL not done properly(Oracle8 & DB/2 do this completely different) |
Date: | 1999-05-17 13:22:04 |
Message-ID: | 199905171322.JAA69779@hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name :
Your email address : secret(at)kearneydev(dot)com
Category : runtime: back-end: SQL
Severity : non-critical
Summary: GROUP BY with NULL not done properly(Oracle8 & DB/2 do this completely different)
System Configuration
--------------------
Operating System : Linux 2.2.7 Redhat 5.2
PostgreSQL version : 6.4.2
Compiler used : 2.7.2.3
Hardware:
---------
Linux tau.kearneydev.com 2.2.7 #3 Thu Apr 29 10:10:41 EDT 1999 i686 unknown
Versions of other tools:
------------------------
--------------------------------------------------------------------------
Problem Description:
--------------------
The appearance of NULL in a table where a GROUP BY clause is
used causes the behavior of returning 1 line for every NULL.
Both Oracle8 and DB/2 perform this as I would expect. IE
a,b
1,1
1,2
NULL,1
NULL,2
SELECT a,sum(b) GROUP BY a returns on Postgres:
1,3
NULL,1
NULL,2
On Oracle8, DB/2, etc.:
1,3
NULL,3
Cut&paste from Oracle8:
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>
--------------------------------------------------------------------------
Test Case:
----------
CREATE TABLE z(a int4,b int4);
INSERT INTO z values (1,2);
INSERT INTO z VALUES (1,1);
INSERT INTO z(b) VALUES (1);
INSERT INTO z(b) VALUES (2);
SELECT a,sum(b) FROM z GROUP BY a;
--------------------------------------------------------------------------
Solution:
---------
For whatever reason I've observed many times that NULL<>NULL
under PostgreSQL, I've had to include many clauses in my
SQL statements to make up for this, perhaps if this was
corrected it would function properly.
--------------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Unprivileged user | 1999-05-17 15:38:30 | General Bug Report: adding column to table w/ index causes column not to be seen |
Previous Message | C. Wible | 1999-05-14 01:30:59 | 6.4.2 - VACUUM |