Thread

General Bug Report: GROUP BY with NULL not done properly(Oracle8 & DB/2 do this completely different)

Unprivileged user <nobody> — 1999-05-17T13:22:04Z

============================================================================
                        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.

--------------------------------------------------------------------------