Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA78405; Mon, 24 Jan 2000 22:46:18 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id WAA11550; Mon, 24 Jan 2000 22:45:49 -0500 (EST) To: Chris Bitmead cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace In-reply-to: <388D0D55.84F13203@bitmead.com> References: <2295.948765015@sss.pgh.pa.us> <388D0D55.84F13203@bitmead.com> Comments: In-reply-to Chris Bitmead message dated "Tue, 25 Jan 2000 13:41:25 +1100" Date: Mon, 24 Jan 2000 22:45:49 -0500 Message-ID: <11547.948771949@sss.pgh.pa.us> From: Tom Lane Chris Bitmead writes: > Tom Lane wrote: >> If I don't hear loud hollers very soon, I'm going to eliminate the >> DISTINCT ON "feature" for 7.0. As previously discussed, this feature >> is not standard SQL and has no clear semantic interpretation. > I don't feel overly strongly about this, but if I remember right you can > do some pretty cool things with this feature, provided you do define > some semantics clearly. We did talk about that, but I didn't hear any strong support for doing it, as opposed to pulling the feature completely... in particular, I didn't hear anyone volunteering to do the work... > as long as it's useful, how about clearly defining it? I don't know that > there is an easy way of doing this in standard SQL. I don't see any > problems with useful extensions to SQL. The only reason it came to my notice in the first place was people posting questions asking why they weren't getting the results they expected from it (whatever the heck those were; they weren't what you actually get from the current implementation, anyway). The problem with a poorly-specified nonstandard feature is support costs: you have to document it, answer questions about it, keep it working, etc. In this case we'd also have to define how it should work and alter the existing code to produce reasonable and predictable results. The existing code is not merely unpredictable, it is definitely broken. For example: regression=# select q1,q2 from int8_tbl; q1 | q2 ------------------+------------------- 123 | 456 123 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 (5 rows) regression=# select distinct on q1 q1,q2 from int8_tbl; q1 | q2 ------------------+----- 123 | 456 4567890123456789 | 123 (2 rows) -- OK so far, but: regression=# select distinct on q1 q1,q2 from int8_tbl order by q2; q1 | q2 ------------------+------------------- 4567890123456789 | -4567890123456789 123 | 456 4567890123456789 | 4567890123456789 (3 rows) -- which is not "distinct on q1" by my notions... In short, it's not clear to me that supporting DISTINCT ON is a good use of our limited resources. I'm willing to pull it out, but not to fix it. Does someone else want to take responsibility for it? regards, tom lane