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.