pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Bitmead <chris.bitmead@bigfoot.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Confusion about DISTINCT.
Date: Sun, 25 Apr 1999 12:22:43 -0400
Message-ID: <10784.925057363@sss.pgh.pa.us> (raw)
In-Reply-To: <3722DF08.D8E19160@bigfoot.com>

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



view thread (3+ messages)  latest in thread

Message-ID: <10784.925057363@sss.pgh.pa.us>
Permalink:  ../10784.925057363@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10784.925057363@sss.pgh.pa.us

 · 

reply

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, chris.bitmead@bigfoot.com
  Subject: Re: [SQL] Confusion about DISTINCT.
  In-Reply-To: <10784.925057363@sss.pgh.pa.us>
  References: <3722DF08.D8E19160@bigfoot.com> <10784.925057363@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