============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name :
Your email address : secret@kearneydev.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.
--------------------------------------------------------------------------