Thread

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

Tom Lane <tgl@sss.pgh.pa.us> — 1999-07-10T18:35:06Z

sszabo@bigpanda.com writes:
> 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.

You're right, "GROUP BY a,b ORDER BY a,b" is redundant because Postgres
implements GROUP BY using a sort-and-merge-adjacent-duplicates
algorithm.  So the result of a GROUPed query is going to be sorted on
the group columns anyway.

I have just fixed a bug in lselect.c that would cause tuples containing
null fields to be sorted in an unpredictable order.  This manifested
itself in David's example by failing to bring together all the tuples
that in fact had the same values of a + b; whereupon the
duplicate-recognizer of course wasn't able to merge all the tuples it
should have.  The second sort step probably didn't produce perfect
ordering either, though it seems to have mostly worked.

With the bug fix I get 1736 rows out of David's example, rather than
5322 rows (while he got 5321, another indication that the sort wasn't
predictable...)

			regards, tom lane