Thread
-
Re: [BUGS] General Bug Report: GROUP BY with NULL not done properly(Oracle8& DB/2 do this completely different)
sszabo@bigpanda.com — 1999-07-08T14:57:41Z
>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@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. >> > >> > -------------------------------------------------------------------------- >> > >> > >> > >> >> -- >> Bruce Momjian | http://www.op.net/~candle >> maillist@candle.pha.pa.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