From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@hub.org
To: pgsql-hackers@postgreSQL.org
Subject: Re: SELECT DISTINCT ON ... ORDER BY ...
Date: Fri, 29 Jan 1999 12:02:52 -0500
Message-ID: <25791.917629372@sss.pgh.pa.us> (raw)
In-Reply-To: <199901290400.XAA46643@hub.org>
Thomas Metz <tmetz@gsf.de> writes:
> SELECT DISTINCT ON id id, name FROM test ORDER BY name;
> [doesn't work as expected]
There have been related discussions before on pg-hackers mail list;
you might care to check the list archives. The conclusion I recall
is that it's not real clear how the combination of SELECT DISTINCT
on one column and ORDER BY on another *should* work. Postgres'
current behavior is clearly wrong IMHO, but there isn't a unique
definition of right behavior, because it's not clear which tuples
should get selected for the sort.
This "SELECT DISTINCT ON attribute" option strikes me as even more
bogus. Where did we get that from --- is it in the SQL92 standard?
If you SELECT DISTINCT on a subset of the attributes to be returned,
then there's no unique definition of which values get returned in the
other columns. In Thomas' example:
> Assuming the table TEST as follows:
> ID NAME
> - -----------------
> 1 Alex
> 2 Oliver
> 1 Thomas
> 2 Fenella
> SELECT DISTINCT ON id id, name FROM test;
> produces:
> ID NAME
> - -----------------
> 1 Alex
> 2 Oliver
There's no justifiable reason for preferring this output over
1 Thomas
2 Oliver
or
1 Alex
2 Fenella
or
1 Thomas
2 Fenella
Any of these are "DISTINCT ON id", but it's purely a matter of
happenstance table order and unspecified implementation choices which
one will appear. Do we really have (or want) a statement with
inherently undefined behavior?
Anyway, to answer Thomas' question, the way SELECT DISTINCT is
implemented is that first there's a sort on the DISTINCT columns,
then there's a pass that eliminates adjacent duplicates (like the Unix
uniq(1) program). In the current backend, doing an ORDER BY on another
column overrides the sorting on the DISTINCT columns, so when the
duplicate-eliminator runs it will fail to get rid of duplicates that
don't happen to appear consecutively in its input. That's pretty
broken, but then the entire concept of combining these two options
doesn't seem well defined; the SELECT DISTINCT doesn't make any promises
about which tuples (with the same DISTINCT columns) it's going to pick,
therefore the result of ordering by some other column isn't clear.
If you're willing to live with poorly defined behavior, the fix
is fairly obvious: run the sort and uniq passes for the DISTINCT
columns, *then* run the sort on the ORDER BY columns --- which
will use whichever tuple the DISTINCT phase selected at random
out of each set with the same DISTINCT value.
I think the issue got put on the back burner last time in hopes that
some definition with consistent behavior would come up, but I haven't
seen any hope that there is one.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org
Subject: Re: SELECT DISTINCT ON ... ORDER BY ...
In-Reply-To: <25791.917629372@sss.pgh.pa.us>
References: <199901290400.XAA46643@hub.org> <25791.917629372@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox