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 UAA33611; Mon, 24 Jan 2000 20:50:17 -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 UAA02298; Mon, 24 Jan 2000 20:50:16 -0500 (EST) To: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: DISTINCT ON: speak now or forever hold your peace Date: Mon, 24 Jan 2000 20:50:15 -0500 Message-ID: <2295.948765015@sss.pgh.pa.us> From: Tom Lane 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 hadn't been planning to touch DISTINCT before 7.0, but changed my mind when I noticed this little gem: create table foo1 (f1 int, f2 int, f3 int); insert into foo1 values(1,2,3); insert into foo1 values(1,2,null); insert into foo1 values(1,null,2); insert into foo1 values(1,2,4); insert into foo1 values(1,2,4); select * from foo1; f1 | f2 | f3 ----+----+---- 1 | 2 | 3 1 | 2 | 1 | | 2 1 | 2 | 4 1 | 2 | 4 (5 rows) select distinct * from foo1; f1 | f2 | f3 ----+----+---- 1 | 2 | 3 1 | 2 | 4 1 | 2 | (3 rows) Didn't know that (NULL, 2) == (2, NULL), did you? The problem here is that nodeUnique is doing a bitwise comparison of the tuple contents (which is bad enough --- not all datatypes think equality is bitwise), and it's neglecting to include the null-field bitmap in what it compares. Rather than just band-aid the null-field problem, I'm going to fix it right. As long as I have to touch it, I'll deal with DISTINCT ON too. regards, tom lane