Thread: BUG #1555: bug in GROUP BY?
The following bug has been logged online: Bug reference: 1555 Logged by: Peter Cottingham Email address: cottingh@rci.rutgers.edu PostgreSQL version: 7.4.7-3.FC3.1 Operating system: Linix Description: bug in GROUP BY? Details: Appears that the GROUP BY clause is not working correctly on new server (GROUP BY should order the result set in ASCENDING order, NOT DESCENDING order.) See below ********************************** select dom_id, count(ste_id) as ste_count from ste group by dom_id ********************************** ERRONEOUS RESULT SET: dom_id ste_count ----------- 5 4 3 12 2 50 1 53
On Mon, Mar 21, 2005 at 07:20:04PM +0000, Peter Cottingham wrote: > > Appears that the GROUP BY clause is not working correctly on new server > (GROUP BY should order the result set in ASCENDING order, NOT DESCENDING > order.) See below > > ********************************** > select dom_id, count(ste_id) as ste_count > from ste > group by dom_id > ********************************** As far as I know, the SQL standards don't specify that GROUP BY is supposed to give a particular order. A query without an ORDER BY clause, or a query with an ORDER BY clause that isn't specific enough, has an order that's implementation-dependent. If you want a particular order then use ORDER BY. Somebody please correct me if I'm mistaken (with a relevant citation from one of the standards). -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> On Mon, Mar 21, 2005 at 07:20:04PM +0000, Peter Cottingham wrote:
>> Appears that the GROUP BY clause is not working correctly on new server
>> (GROUP BY should order the result set in ASCENDING order, NOT DESCENDING
>> order.) See below
> As far as I know, the SQL standards don't specify that GROUP BY is
> supposed to give a particular order.
> Somebody please correct me if I'm mistaken
You're not. Peter is apparently accustomed to databases that only know
how to implement GROUP BY with a sort-and-uniq kind of implementation
... that would include Postgres from a few years ago ... but there is
no such requirement in the spec.
Current PG versions are likely to implement GROUP BY with a hashing
method, which will yield a completely unsorted output. It is pure
coincidence that it happened to look like descending order in Peter's
example.
regards, tom lane
On Mon, 21 Mar 2005, Peter Cottingham wrote: > Appears that the GROUP BY clause is not working correctly on new server > (GROUP BY should order the result set in ASCENDING order, NOT DESCENDING > order.) See below I do not believe this is a bug. Result sets have no guaranteed ordering unless you give an ORDER BY.