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

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

From: sszabo(at)bigpanda(dot)com
To: secret <secret(at)kearneydev(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 14:57:41
Message-ID: 199907081457.KAA27274@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
>Bruce Momjian wrote:
>
>> Looks like this is fixed in 6.5.
>>
>>         test=> SELECT a,sum(b) FROM z GROUP BY a;
>>         a|sum
>>         -+---
>>         1|  6
>>          |  4
>>         (2 rows)
>>
>> >
>> > ============================================================================
>> >                         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.
>> >
>> > --------------------------------------------------------------------------
>> >
>> >
>> >
>>
>> --
>>   Bruce Momjian                        |  http://www.op.net/~candle
>>   maillist(at)candle(dot)pha(dot)pa(dot)us            |  (610) 853-3000
>>   +  If your life is a hard drive,     |  830 Blythe Avenue
>>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>
>    It works fine with 1 variable, try it with 2.  6.4.2 worked fine for 1, it's just when you
>GROUP BY 2 variables that contain NULLs issues start appearing.(Another reason it looks like a
>bug, not a feature :))


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

Responses

pgsql-bugs by date

Next:From: secretDate: 1999-07-08 16:00:27
Subject: Re: [BUGS] General Bug Report: GROUP BY with NULL not done properly(Oracle8& DB/2 do this completely different)
Previous:From: PostmasterDate: 1999-07-08 14:47:42
Subject: Please update and reply

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