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-09T16:17:23Z

>    Smaller examples I try work too under v6.5, but here is an example from a larger one:
>
>    As you see above, the GROUP on a didn't function for b=102110, we have 2 rows, both of which
>were returned, both of which A is NULL.  If you'd like a dump of this table I'll send it to you and
>not cc the list on it...  It's a stripped down version of another table I use quite a bit.

I got a similar result on a set of 23000 records or so
I did a 
create table a (a int4, b int4, c int4);
perl -e 'use integer; $x=1; while (1) {
 print "insert into a values(null, $x, ".(rand(100)*1).");\n"; 
 if (rand(10)<=1) { $x++; } if ($x == 100000) { exit; } }' > out
cat out | psql 

(I paused it part way through at about 23000 records)

psql
select a,b,sum(c) from a group by a,b;
and got 2 more rows than i should have

Interesting thing however was that the rows i got out where ordered
null|1|<something>
null|<something like 2000>|<something>
null|<prev+1>|<something>
...
null|1|<something>
null|2|<something>
[note that null,1 showed up more than once]

where i had thought that normally because of the way postgres does its
grouping, the results are ordered by the group by fields before the
grouping is done, and in most cases of group by i get the output
sorted by those values, in the case where it failed, i did not.
I'm not certain if that's normal or not, so i thought it would be
worth mentioning.

In the case originally done on the 60000+ records, an explicit
order by was done so this wouldn't have shown up.

Stephan