The following bug has been logged online:

Bug reference: 1555
Logged by: Peter Cottingham
Email address: cottingh (AT) rci (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Mon, Mar 21, 2005 at 07:20:04PM +0000, Peter Cottingham wrote:
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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Mon, 21 Mar 2005, Peter Cottingham wrote:

I do not believe this is a bug. Result sets have no guaranteed ordering
unless you give an ORDER BY.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster