From: | secret <secret(at)kearneydev(dot)com> |
---|---|
To: | sszabo(at)bigpanda(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-09 16:30:34 |
Message-ID: | 378623AA.CCA03C57@kearneydev.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
sszabo(at)bigpanda(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-07-10 18:35:06 | Re: [BUGS] General Bug Report: GROUP BY with NULL not done properly(Oracle8& DB/2 do this completely different) |
Previous Message | sszabo | 1999-07-09 16:17:23 | Re: [BUGS] General Bug Report: GROUP BY with NULL not done properly(Oracle8& DB/2 do this completely different) |