Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA92210; Thu, 23 Sep 1999 13:29:40 -0400 (EDT) (envelope-from maillist@candle.pha.pa.us) Received: (from maillist@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id NAA19629; Thu, 23 Sep 1999 13:18:18 -0400 (EDT) From: Bruce Momjian Message-Id: <199909231718.NAA19629@candle.pha.pa.us> Subject: Re: [SQL] Re: [HACKERS] SELECT DISTINCT question In-Reply-To: <24413.931901508@sss.pgh.pa.us> from Tom Lane at "Jul 13, 1999 05:31:48 pm" To: Tom Lane Date: Thu, 23 Sep 1999 13:18:18 -0400 (EDT) CC: Hannu Krosing , hackers@postgreSQL.org, pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL56 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Tom, any status on this DISTINCT ON ripout? > 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 > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026