From: Bruce Momjian <maillist@candle.pha.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Chris Bitmead <chris.bitmead@bigfoot.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Confusion about DISTINCT.
Date: Mon, 10 May 1999 12:25:06 -0400 (EDT)
Message-ID: <199905101625.MAA06754@candle.pha.pa.us> (raw)
In-Reply-To: <10784.925057363@sss.pgh.pa.us>
Nice summary of the issue.
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > I'm a bit confused about DISTINCT. Firstly, what does DISTINCT mean when
> > you don't use "ON".
>
> Means the equality test is taken across all columns of the output.
> "ON" restricts the test for "duplicate row" to look at just some
> of the columns (thus creating the issue of which tuple out of a
> group of "duplicates" gets through the filter to determine the values
> of the other columns).
>
> > Like this...
> > SELECT DISTINCT category.image FROM story, category* WHERE
> > story.category = category.oid ORDER BY datetime DESC;
> > The DISTINCT here has no effect on the output other than changing the
> > ordering. There are duplicates!
>
> This is a known bug, or at least IMHO it's a bug; it's triggered by your
> use of ORDER BY with DISTINCT. You need to say
> ORDER BY category.image, datetime DESC
> to make the above example work.
>
> The reason is that DISTINCT is implemented by a sort followed by an
> adjacent-duplicate-elimination pass (just like "sort | uniq" in Unix
> shell programming). When you put on an explicit ORDER BY clause,
> you override the sort order that the DISTINCT wants, and so the
> duplicate filter doesn't necessarily spot the duplicates. You have
> to make sure to sort in an order that will keep the unwanted duplicates
> together, ie, list all the DISTINCT columns first in the ORDER BY.
>
> A straightforward solution would be to apply the user-specified ORDER BY
> sort *after* the DISTINCT-generated sort and dup-filter steps. I used
> to think this was the right fix. However, this would break that nifty
> technique of letting a user-specified ORDER BY resolve the ambiguity of
> DISTINCT ON --- the user ordering has to happen before the dup-filter
> for that to work.
>
> Alternatively, the system could alter the user's ORDER BY to ensure the
> DISTINCT columns are the primary sort keys --- ie, silently change your
> ORDER BY in the above example. I don't think I like that too much
> either. For one thing, people would complain that the resulting order
> wasn't what they asked for. For another, it seems possible that there
> are applications where applying the dup-filter to columns that aren't
> the primary keys might be useful. Let's see, if you did
> SELECT DISTINCT category.image FROM story, category* WHERE
> story.category = category.oid ORDER BY datetime DESC, category.image;
> then you'd get each image listed only once *per date* (I think).
> Doesn't that sound like it could be a useful behavior?
>
> So right at the moment, I think the system ought not tinker with the
> user's ORDER BY. But it probably should emit a warning message if the
> ORDER BY fails to mention all the columns being DISTINCTed on. If you
> aren't sorting by a DISTINCT column at all, you won't even get
> reproducible results, let alone desirable ones.
>
> > 2nd question: Why does the following query result in duplicates even
> > though I use DISTINCT ON?
>
> Same deal.
>
> 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
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: maillist@candle.pha.pa.us, tgl@sss.pgh.pa.us, chris.bitmead@bigfoot.com
Subject: Re: [SQL] Confusion about DISTINCT.
In-Reply-To: <199905101625.MAA06754@candle.pha.pa.us>
References: <10784.925057363@sss.pgh.pa.us> <199905101625.MAA06754@candle.pha.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