pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Confusion about DISTINCT.
3+ messages / 3 participants
[nested] [flat]

* Confusion about DISTINCT.
@ 1999-04-25 09:23 Chris Bitmead <chris.bitmead@bigfoot.com>
  1999-04-25 16:22 ` Re: [SQL] Confusion about DISTINCT. Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Chris Bitmead @ 1999-04-25 09:23 UTC (permalink / raw)
  To: pgsql-sql


I'm a bit confused about DISTINCT. Firstly, what does DISTINCT mean when
you don't use "ON". 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!

2nd question: Why does the following query result in duplicates even
though I use DISTINCT ON? If I change the ORDER BY to be on image, then
there are no duplicates but that isn't what I want. I want the time to
be the sort order because I want the X most recent images but only
unique ones. Is this a bug? It certainly seems wierd that DISTINCT would
return duplicates. Why should it be up to the user to order the output
with reference to the DISTINCT clause? Shouldn't the database take care
of that?
...

SELECT DISTINCT ON image category.image FROM story, category* WHERE
story.category = category.oid ORDER BY datetime DESC;
image               
--------------------
/icon/canon.gif     
/icon/arca-swiss.gif
/icon/canon.gif     
/icon/hasselblad.gif
/icon/nikon.gif     
/icon/olympus.gif   
(6 rows)


-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Confusion about DISTINCT.
  1999-04-25 09:23 Confusion about DISTINCT. Chris Bitmead <chris.bitmead@bigfoot.com>
@ 1999-04-25 16:22 ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-05-10 16:25   ` Re: [SQL] Confusion about DISTINCT. Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 1999-04-25 16:22 UTC (permalink / raw)
  To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Confusion about DISTINCT.
  1999-04-25 09:23 Confusion about DISTINCT. Chris Bitmead <chris.bitmead@bigfoot.com>
  1999-04-25 16:22 ` Re: [SQL] Confusion about DISTINCT. Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-05-10 16:25   ` Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Bruce Momjian @ 1999-05-10 16:25 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Bitmead <chris.bitmead@bigfoot.com>; pgsql-sql



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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-05-10 16:25 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-25 09:23 Confusion about DISTINCT. Chris Bitmead <chris.bitmead@bigfoot.com>
1999-04-25 16:22 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-10 16:25   ` Bruce Momjian <maillist@candle.pha.pa.us>

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