Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA94565; Tue, 13 Jul 1999 17:33:19 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id RAA24415; Tue, 13 Jul 1999 17:31:49 -0400 (EDT) To: Hannu Krosing cc: hackers@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Re: [HACKERS] SELECT DISTINCT question In-reply-to: Your message of Tue, 13 Jul 1999 23:50:57 +0300 <378BA6B1.2B226DDB@trust.ee> Date: Tue, 13 Jul 1999 17:31:48 -0400 Message-ID: <24413.931901508@sss.pgh.pa.us> From: Tom Lane Hannu Krosing writes: >> "DISTINCT will eliminate all duplicate rows from the selection. >> DISTINCT ON column will eliminate all duplicates in the specified column; >> this is equivalent to using GROUP BY column." > If it is equivalent to GROUP BY then it should allow only aggregates > in non-distinct columns, like: > select distinct on date date, sum(bytes) from access_log; > If it does not, then it should be files as a bug imho. It does not. Whether that is a bug is hard to say, since there is no standard I know of that says what it *is* supposed to do. If you look at the select_distinct_on regress test outputs, I bet you will be even less happy: QUERY: SELECT DISTINCT ON string4 two, string4, ten FROM tmp ORDER BY two using <, string4 using <, ten using <; two|string4|ten ---+-------+--- 0|AAAAxx | 0 0|HHHHxx | 0 0|OOOOxx | 0 0|VVVVxx | 0 1|AAAAxx | 1 1|HHHHxx | 1 1|OOOOxx | 1 1|VVVVxx | 1 (8 rows) That's not exactly my idea of "distinct" values of string4 --- but apparently whoever made up the regress test thought it was OK! Can anyone defend this feature or provide a coherent definition of what it's supposed to be doing? My urge to rip it out is growing stronger and stronger... regards, tom lane