Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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.

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


Responses

pgsql-bugs by date

Next:From: Unprivileged userDate: 1999-05-17 15:38:30
Subject: General Bug Report: adding column to table w/ index causes column not to be seen
Previous:From: C. WibleDate: 1999-05-14 01:30:59
Subject: 6.4.2 - VACUUM

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group