Thread

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

secret <secret@kearneydev.com> — 1999-07-09T16:30:34Z

sszabo@bigpanda.com wrote:

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

    I have a .ZIP file of about 50k that has a data set with query that will produce the incorrect
results.  I put an explicit ORDER BY in to demonstrate the problem.  If anyone wants a copy let me know,
I hesitate to send it to the list due to the size.

David Secret
MIS Director
Kearney Development Co., Inc.