pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
DISTINCT ON: speak now or forever hold your peace
36+ messages / 11 participants
[nested] [flat]

* DISTINCT ON: speak now or forever hold your peace
@ 2000-01-25 01:50 Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 02:41 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-25 02:50 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Mike Mascari <mascarm@mascari.com>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-26 18:34 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <peter_e@gmx.net>
  2000-01-26 22:23 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  0 siblings, 6 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-25 01:50 UTC (permalink / raw)
  To: pgsql-hackers@postgreSQL.org; pgsql-sql

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



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-25 02:41 ` Chris Bitmead <chris@bitmead.com>
  2000-01-25 03:45   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  5 siblings, 1 reply; 36+ messages in thread

From: Chris Bitmead @ 2000-01-25 02:41 UTC (permalink / raw)
  To: pgsql-hackers@postgreSQL.org; pgsql-sql

Tom Lane wrote:
> 
> 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 don't feel overly strongly about this, but if I remember right you can
do some pretty cool things with this feature, provided you do define
some semantics clearly. Like I think you can find the first tuple
(given some ORDER BY clause) that fulfills some criteria. I think it is

SELECT DISTINCT ON name name, age ORDER BY age;

will get the youngest person. This might not be clearly specified now,
but
as long as it's useful, how about clearly defining it? I don't know that
there is an easy way of doing this in standard SQL. I don't see any
problems with useful extensions to SQL. If people want standards, they
don't have to use it.



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 02:41 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
@ 2000-01-25 03:45   ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:55     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Bruce Momjian <pgman@candle.pha.pa.us>
  2000-01-25 07:15     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Adriaan Joubert <a.joubert@albourne.com>
  0 siblings, 2 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-25 03:45 UTC (permalink / raw)
  To: Chris Bitmead <chris@bitmead.com>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql

Chris Bitmead <chris@bitmead.com> writes:
> Tom Lane wrote:
>> 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 don't feel overly strongly about this, but if I remember right you can
> do some pretty cool things with this feature, provided you do define
> some semantics clearly.

We did talk about that, but I didn't hear any strong support for doing
it, as opposed to pulling the feature completely... in particular,
I didn't hear anyone volunteering to do the work...

> as long as it's useful, how about clearly defining it? I don't know that
> there is an easy way of doing this in standard SQL. I don't see any
> problems with useful extensions to SQL.

The only reason it came to my notice in the first place was people
posting questions asking why they weren't getting the results they
expected from it (whatever the heck those were; they weren't what you
actually get from the current implementation, anyway).  The problem
with a poorly-specified nonstandard feature is support costs: you
have to document it, answer questions about it, keep it working, etc.
In this case we'd also have to define how it should work and alter
the existing code to produce reasonable and predictable results.  The
existing code is not merely unpredictable, it is definitely broken.
For example:

regression=# select q1,q2 from int8_tbl;
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

regression=# select distinct on q1 q1,q2 from int8_tbl;
        q1        | q2
------------------+-----
              123 | 456
 4567890123456789 | 123
(2 rows)

-- OK so far, but:

regression=# select distinct on q1 q1,q2 from int8_tbl order by q2;
        q1        |        q2
------------------+-------------------
 4567890123456789 | -4567890123456789
              123 |               456
 4567890123456789 |  4567890123456789
(3 rows)

-- which is not "distinct on q1" by my notions...


In short, it's not clear to me that supporting DISTINCT ON is a good use
of our limited resources.  I'm willing to pull it out, but not to fix it.
Does someone else want to take responsibility for it?

			regards, tom lane



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 02:41 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-25 03:45   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-25 03:55     ` Bruce Momjian <pgman@candle.pha.pa.us>
  2000-01-25 07:12       ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Michael Meskes <meskes@postgresql.org>
  2000-01-25 10:30       ` Re: [SQL] Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
  1 sibling, 2 replies; 36+ messages in thread

From: Bruce Momjian @ 2000-01-25 03:55 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org; pgsql-sql

> In short, it's not clear to me that supporting DISTINCT ON is a good use
> of our limited resources.  I'm willing to pull it out, but not to fix it.
> Does someone else want to take responsibility for it?

OK, we can disable it and put it on the TODO list.  It is already there.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@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] 36+ messages in thread

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 02:41 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-25 03:45   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:55     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Bruce Momjian <pgman@candle.pha.pa.us>
@ 2000-01-25 07:12       ` Michael Meskes <meskes@postgresql.org>
  1 sibling, 0 replies; 36+ messages in thread

From: Michael Meskes @ 2000-01-25 07:12 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org

On Mon, Jan 24, 2000 at 10:55:02PM -0500, Bruce Momjian wrote:
> > In short, it's not clear to me that supporting DISTINCT ON is a good use
> > of our limited resources.  I'm willing to pull it out, but not to fix it.
> > Does someone else want to take responsibility for it?
> 
> OK, we can disable it and put it on the TODO list.  It is already there.

Or, as you suggested earlier, throw a NOTICE instead of disabling it. But
then I doubt Tom talked about physically removing the code anyway. Disabling
certainly is enough.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!



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

* Re: [SQL] Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 02:41 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-25 03:45   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:55     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Bruce Momjian <pgman@candle.pha.pa.us>
@ 2000-01-25 10:30       ` Peter Eisentraut <e99re41@DoCS.UU.SE>
  1 sibling, 0 replies; 36+ messages in thread

From: Peter Eisentraut @ 2000-01-25 10:30 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org; pgsql-sql

On Mon, 24 Jan 2000, Bruce Momjian wrote:

> > In short, it's not clear to me that supporting DISTINCT ON is a good use
> > of our limited resources.  I'm willing to pull it out, but not to fix it.
> > Does someone else want to take responsibility for it?
> 
> OK, we can disable it and put it on the TODO list.  It is already there.
> 

Am I wrong or can DISTINCT ON not be rewritten in terms of GROUP BY in all
cases?

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 02:41 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-25 03:45   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-25 07:15     ` Adriaan Joubert <a.joubert@albourne.com>
  2000-01-25 07:45       ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 36+ messages in thread

From: Adriaan Joubert @ 2000-01-25 07:15 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql

Tom Lane wrote:

> Chris Bitmead <chris@bitmead.com> writes:
> > Tom Lane wrote:
> >> 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 don't feel overly strongly about this, but if I remember right you can
> > do some pretty cool things with this feature, provided you do define
> > some semantics clearly.
>
> We did talk about that, but I didn't hear any strong support for doing
> it, as opposed to pulling the feature completely... in particular,
> I didn't hear anyone volunteering to do the work...

I've been using DISTINCT to fix things when vacuum gets into a mess (yes, it
happened again last night,
FATAL 1:  VACUUM (vc_rpfheap): BlowawayRelationBuffers returned -2
). I then have loads of duplicates in a table and by doing a SELECT DISTINCT
... INTO ... I can get rid of them. A few table renames later I'm back in
business. So how do I do this without DISTINCT?

Better would be of course to get rid of the BlowawayRelationBuffers problem.
I cannot reproduce it yet, but I think I can make it happen by running vacuum
and updating tables that are linked by triggers. Does this sound plausible?
And yes, I'm running on Alpha, i.e. 64 bits.

Adriaan




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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 02:41 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-25 03:45   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 07:15     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Adriaan Joubert <a.joubert@albourne.com>
@ 2000-01-25 07:45       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-25 07:45 UTC (permalink / raw)
  To: Adriaan Joubert <a.joubert@albourne.com>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql

Adriaan Joubert <a.joubert@albourne.com> writes:
> I've been using DISTINCT to fix things when vacuum gets into a mess (yes, it
> happened again last night,
> FATAL 1:  VACUUM (vc_rpfheap): BlowawayRelationBuffers returned -2
> ). I then have loads of duplicates in a table and by doing a SELECT DISTINCT
> ... INTO ... I can get rid of them. A few table renames later I'm back in
> business. So how do I do this without DISTINCT?

I didn't say anything about eliminating SELECT DISTINCT; I was talking
about SELECT DISTINCT ON, which is a whole 'nother thing.

> Better would be of course to get rid of the BlowawayRelationBuffers problem.

Quite so.  I think this is fixed for 7.0 ... that error message
indicates a buffer refcount leak, and I fixed several problems
along that line a couple months ago.  We shall see if I got 'em
all or not ...

			regards, tom lane



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-25 02:50 ` Mike Mascari <mascarm@mascari.com>
  2000-01-25 03:09   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Bruce Momjian <pgman@candle.pha.pa.us>
  5 siblings, 1 reply; 36+ messages in thread

From: Mike Mascari @ 2000-01-25 02:50 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql

Tom Lane wrote:
> 
> 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 grepped our source code and found this query:

INSERT INTO temp_sales
SELECT DISTINCT on key supplysources.supplysource, 
incharges.supply, targets.target, incharges.saledate, 
incharges.supplyunit, '', incharges.quantity, incharges.company, 
incharges.costcntr, 'Replenish', incharges.price, '','','', 0, 
text(supplysources.supplysource)||
text(incharges.supply)||
text(targets.target)||
text(incharges.saledate) as key 
FROM supplysources, incharges, targets WHERE 
supplysources.warehouse = incharges.warehouse AND 
(targets.site,targets.area) = (incharges.site,incharges.area);

What happens is that a large charges file which is transferred to
a mainframe ERP application is first brought into PostgreSQL.
Depending upon certain race conditions, duplicate "sales" records
can appear in the data file. We use DISTINCT ON to pick (as you
point out) an arbitrary record when duplicates appear. I suppose
we could do a DELETE ... WHERE NOT EXISTS after the import. Using
DISTINCT ON just saves a step. I don't have any arguments beyond
the grounds that we're using it in existing code as a duplicate
record filter - :-(

Just FYI, 

Mike Mascari



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 02:50 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Mike Mascari <mascarm@mascari.com>
@ 2000-01-25 03:09   ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 36+ messages in thread

From: Bruce Momjian @ 2000-01-25 03:09 UTC (permalink / raw)
  To: Mike Mascari <mascarm@mascari.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgreSQL.org; pgsql-sql

> INSERT INTO temp_sales
> SELECT DISTINCT on key supplysources.supplysource, 
> incharges.supply, targets.target, incharges.saledate, 
> incharges.supplyunit, '', incharges.quantity, incharges.company, 
> incharges.costcntr, 'Replenish', incharges.price, '','','', 0, 
> text(supplysources.supplysource)||
> text(incharges.supply)||
> text(targets.target)||
> text(incharges.saledate) as key 
> FROM supplysources, incharges, targets WHERE 
> supplysources.warehouse = incharges.warehouse AND 
> (targets.site,targets.area) = (incharges.site,incharges.area);
> 
> What happens is that a large charges file which is transferred to
> a mainframe ERP application is first brought into PostgreSQL.
> Depending upon certain race conditions, duplicate "sales" records
> can appear in the data file. We use DISTINCT ON to pick (as you
> point out) an arbitrary record when duplicates appear. I suppose
> we could do a DELETE ... WHERE NOT EXISTS after the import. Using
> DISTINCT ON just saves a step. I don't have any arguments beyond
> the grounds that we're using it in existing code as a duplicate
> record filter - :-(

Why not just throw a NOTICE and keep the feature.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@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] 36+ messages in thread

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-25 03:03 ` Philip Warner <pjw@rhyme.com.au>
  2000-01-25 03:09   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  5 siblings, 2 replies; 36+ messages in thread

From: Philip Warner @ 2000-01-25 03:03 UTC (permalink / raw)
  To: Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org; pgsql-sql

At 13:41 25/01/00 +1100, Chris Bitmead wrote:
>Tom Lane wrote:
>> 
>> 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 don't feel overly strongly about this, but if I remember right you can
>do some pretty cool things with this feature, provided you do define
>some semantics clearly. Like I think you can find the first tuple
>(given some ORDER BY clause) that fulfills some criteria. I think it is
>
>SELECT DISTINCT ON name name, age ORDER BY age;
>
>will get the youngest person. This might not be clearly specified now,
>but
>as long as it's useful, how about clearly defining it? I don't know that
>there is an easy way of doing this in standard SQL. 

I don't know about PGSQL, but in other systems, I use:

    Select <whatever> from <wherever> order by age asc limit to 1 row;

I *think* the PGSQL syntax is:

    Select <whatever> from <wherever> order by age asc limit 1;

I have no idea if the optimizer does 'fast first' optimizations, so I don't
know how quick this would be on a large table.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 03:09   ` Chris Bitmead <chris@bitmead.com>
  2000-01-26 18:34     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 1 reply; 36+ messages in thread

From: Chris Bitmead @ 2000-01-25 03:09 UTC (permalink / raw)
  To: pgsql-hackers@postgreSQL.org; +Cc: pgsql-sql

Philip Warner wrote:
> >I don't feel overly strongly about this, but if I remember right you can
> >do some pretty cool things with this feature, provided you do define
> >some semantics clearly. Like I think you can find the first tuple
> >(given some ORDER BY clause) that fulfills some criteria. I think it is
> >
> >SELECT DISTINCT ON name name, age ORDER BY age;
> >
> >will get the youngest person. This might not be clearly specified now,
> >but
> >as long as it's useful, how about clearly defining it? I don't know that
> >there is an easy way of doing this in standard SQL.
> 
> I don't know about PGSQL, but in other systems, I use:
> 
>     Select <whatever> from <wherever> order by age asc limit to 1 row;
> 
> I *think* the PGSQL syntax is:
> 
>     Select <whatever> from <wherever> order by age asc limit 1;

I think what I really meant was...

SELECT DISTINCT ON firstname firstname, age ORDER BY age.

Which would find the youngest person called "fred", the youngest person
called "paul", the youngest person called "jim" etc etc. which your
limit example wouldn't do.



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 03:09   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
@ 2000-01-26 18:34     ` Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 36+ messages in thread

From: Peter Eisentraut @ 2000-01-26 18:34 UTC (permalink / raw)
  To: Chris Bitmead <chris@bitmead.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql

On 2000-01-25, Chris Bitmead mentioned:

> I think what I really meant was...
> 
> SELECT DISTINCT ON firstname firstname, age ORDER BY age.
> 
> Which would find the youngest person called "fred", the youngest person
> called "paul", the youngest person called "jim" etc etc. which your
> limit example wouldn't do.

SELECT firstname, min(age) FROM x GROUP BY firstname [ ORDER BY min(age) ]

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden





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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 04:05   ` Philip Warner <pjw@rhyme.com.au>
  2000-01-25 05:01     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 05:20     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Don Baccus <dhogaza@pacifier.com>
  2000-01-25 06:00     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 15:41     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Don Baccus <dhogaza@pacifier.com>
  1 sibling, 4 replies; 36+ messages in thread

From: Philip Warner @ 2000-01-25 04:05 UTC (permalink / raw)
  To: Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org

At 14:09 25/01/00 +1100, Chris Bitmead wrote:
>Philip Warner wrote:
>> >I don't feel overly strongly about this, but if I remember right you can
>> >do some pretty cool things with this feature, provided you do define
>> >some semantics clearly. Like I think you can find the first tuple
>> >(given some ORDER BY clause) that fulfills some criteria. I think it is
>> >
>> >SELECT DISTINCT ON name name, age ORDER BY age;
>> >
>> >will get the youngest person. This might not be clearly specified now,
>> >but
>> >as long as it's useful, how about clearly defining it? I don't know that
>> >there is an easy way of doing this in standard SQL.
>> 
>> I don't know about PGSQL, but in other systems, I use:
>> 
>>     Select <whatever> from <wherever> order by age asc limit to 1 row;
>> 
>> I *think* the PGSQL syntax is:
>> 
>>     Select <whatever> from <wherever> order by age asc limit 1;
>
>I think what I really meant was...
>
>SELECT DISTINCT ON firstname firstname, age ORDER BY age.
>
>Which would find the youngest person called "fred", the youngest person
>called "paul", the youngest person called "jim" etc etc. which your
>limit example wouldn't do.

Just a thought, but would I be right in saying that this could be easily
done with the addition of a new aggregate function 'FIRST', which simply
returns the first value sent to it? Since the aggregates operate a row at a
time, you are guaranteed a consistent set of values, I think.

eg.

    SELECT firstname, FIRST(age) ORDER BY age.

Just an idea, but it seems like a cute solution for the more general problem.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 05:01     ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-25 05:01 UTC (permalink / raw)
  To: Philip Warner <pjw@rhyme.com.au>; +Cc: Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org

Philip Warner <pjw@rhyme.com.au> writes:
> Just a thought, but would I be right in saying that this could be easily
> done with the addition of a new aggregate function 'FIRST', which simply
> returns the first value sent to it? Since the aggregates operate a row at
> a time, you are guaranteed a consistent set of values, I think.

No, because the system doesn't guarantee to deliver tuples to the
aggregate in any particular order.  The physical order of tuples
on the disk will depend on the order in which they were last updated
(plus random perturbations introduced by VACUUM); and the order in
which they are processed by a query will depend on the query plan
chosen by the planner (sequential scan vs. indexscan, etc).
SQL in general doesn't believe that tuple ordering has any semantic
significance --- you can ask for ORDER BY, but that's only honored
at the end stage of SELECT result delivery, not necessarily anywhere
in the bowels of a query.

Also, if you are going to use an aggregate, ISTM you might as well
use one like MIN() or MAX() and get a fully predictable result.
I suppose a FIRST() aggregate would save a few cycles by not running
comparisons, but I bet it'd be tough to notice any difference.

			regards, tom lane



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 05:20     ` Don Baccus <dhogaza@pacifier.com>
  3 siblings, 0 replies; 36+ messages in thread

From: Don Baccus @ 2000-01-25 05:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Philip Warner <pjw@rhyme.com.au>; +Cc: Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org

At 12:01 AM 1/25/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> Just a thought, but would I be right in saying that this could be easily
>> done with the addition of a new aggregate function 'FIRST', which simply
>> returns the first value sent to it? Since the aggregates operate a row at
>> a time, you are guaranteed a consistent set of values, I think.
>
>No, because the system doesn't guarantee to deliver tuples to the
>aggregate in any particular order.

In fact, this is a KEY notion behind RDBMS systems...queries return an
unordered set, pure and simple, unless you supply an "order" clause
(though "group by" appears to be implemented by ordering in various 
RDBMS systems, that's an efficiency hack not a given AFAIK).

>SQL in general doesn't believe that tuple ordering has any semantic
>significance --- you can ask for ORDER BY, but that's only honored
>at the end stage of SELECT result delivery, not necessarily anywhere
>in the bowels of a query.

Yep...



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 06:00     ` Philip Warner <pjw@rhyme.com.au>
  2000-01-25 06:07       ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-25 06:12       ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 07:54       ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  3 siblings, 3 replies; 36+ messages in thread

From: Philip Warner @ 2000-01-25 06:00 UTC (permalink / raw)
  To: Don Baccus <dhogaza@pacifier.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org

At 21:20 24/01/00 -0800, Don Baccus wrote:
>At 12:01 AM 1/25/00 -0500, Tom Lane wrote:
>>Philip Warner <pjw@rhyme.com.au> writes:
>>> Just a thought, but would I be right in saying that this could be easily
>>> done with the addition of a new aggregate function 'FIRST', which simply
>>> returns the first value sent to it? Since the aggregates operate a row at
>>> a time, you are guaranteed a consistent set of values, I think.
>>
>>No, because the system doesn't guarantee to deliver tuples to the
>>aggregate in any particular order.
>
>In fact, this is a KEY notion behind RDBMS systems...queries return an
>unordered set, pure and simple, unless you supply an "order" clause
>(though "group by" appears to be implemented by ordering in various 
>RDBMS systems, that's an efficiency hack not a given AFAIK).

That's why there was an 'order by' in the query (there should have also
been a 'group by', to make it valid SQL). I had (naievely) assumed that the
aggregates would be passed the rows in sorted order.

>>SQL in general doesn't believe that tuple ordering has any semantic
>>significance --- you can ask for ORDER BY, but that's only honored
>>at the end stage of SELECT result delivery, not necessarily anywhere
>>in the bowels of a query.
>
>Yep...
>

Out of curiosity, does the SQL spec give any rules or guidelines about when
aggregates should be applied to resultant rows? Or is it one of the
implementation-dependant things?



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 06:00     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 06:07       ` Chris Bitmead <chris@bitmead.com>
  2 siblings, 0 replies; 36+ messages in thread

From: Chris Bitmead @ 2000-01-25 06:07 UTC (permalink / raw)
  To: pgsql-hackers@postgreSQL.org

Philip Warner wrote:

> >In fact, this is a KEY notion behind RDBMS systems...queries return an
> >unordered set, pure and simple, unless you supply an "order" clause
> >(though "group by" appears to be implemented by ordering in various
> >RDBMS systems, that's an efficiency hack not a given AFAIK).

On second thoughts, maybe I agree DISTINCT ON should be dumped. In my
example, if I wanted the youngest people with a given name sorted oldest
first things would be confusing. It's an inconsistant command, I can see
that now.



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 06:00     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 06:12       ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-25 06:12 UTC (permalink / raw)
  To: Philip Warner <pjw@rhyme.com.au>; +Cc: Don Baccus <dhogaza@pacifier.com>; Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org

Philip Warner <pjw@rhyme.com.au> writes:
>>> SQL in general doesn't believe that tuple ordering has any semantic
>>> significance --- you can ask for ORDER BY, but that's only honored
>>> at the end stage of SELECT result delivery, not necessarily anywhere
>>> in the bowels of a query.

> Out of curiosity, does the SQL spec give any rules or guidelines about when
> aggregates should be applied to resultant rows? Or is it one of the
> implementation-dependant things?

Well, SQL's conceptual model is perfectly clear about the processing
work flow of a SELECT: after you've constructed the tuple set (which
might involve joining multiple relations), you apply the WHERE condition
to filter out uninteresting tuples.  Then you apply GROUP BY (if given)
to divvy the tuples into groups.  Then you apply HAVING to eliminate
uninteresting groups.  Then you apply aggregate functions (if any) to
individual groups, or to the whole filtered result set if no groups.
Finally you apply ORDER BY to whatever's left.

Note that at no point except the final output is there any notion
of the tuples being generated or processed in a particular order.
Given appropriate hardware, much of this could be done in parallel.
Parallel or not, an implementation is free to choose the processing
order for its convenience.

			regards, tom lane



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 06:00     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 07:54       ` Philip Warner <pjw@rhyme.com.au>
  2000-01-25 08:20         ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 1 reply; 36+ messages in thread

From: Philip Warner @ 2000-01-25 07:54 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Don Baccus <dhogaza@pacifier.com>; Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org

At 01:12 25/01/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>>>> SQL in general doesn't believe that tuple ordering has any semantic
>>>> significance --- you can ask for ORDER BY, but that's only honored
>>>> at the end stage of SELECT result delivery, not necessarily anywhere
>>>> in the bowels of a query.
>
>> Out of curiosity, does the SQL spec give any rules or guidelines about when
>> aggregates should be applied to resultant rows? Or is it one of the
>> implementation-dependant things?
>
>Well, SQL's conceptual model is perfectly clear about the processing
>work flow of a SELECT: after you've constructed the tuple set (which
>might involve joining multiple relations), you apply the WHERE condition
>to filter out uninteresting tuples.  Then you apply GROUP BY (if given)
>to divvy the tuples into groups.  Then you apply HAVING to eliminate
>uninteresting groups.  Then you apply aggregate functions (if any) to
>individual groups, or to the whole filtered result set if no groups.
>Finally you apply ORDER BY to whatever's left.

Thanks, but now I'm confused. I would have thought that aggregates went
*before* the having clause, since at least one DB I know allows:

select job_type,avg(age) from <wherever> where <stuff> group by job_type
having avg(age) > 70;

ie. the use of aggregate results in the 'having' clause.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 06:00     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 07:54       ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 08:20         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-25 08:20 UTC (permalink / raw)
  To: Philip Warner <pjw@rhyme.com.au>; +Cc: Don Baccus <dhogaza@pacifier.com>; Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org

Philip Warner <pjw@rhyme.com.au> writes:
> Thanks, but now I'm confused. I would have thought that aggregates went
> *before* the having clause,

Arrgh.  You are right of course.  Shouldn't pontificate late at night...

			regards, tom lane



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 03:03 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  2000-01-25 04:05   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
@ 2000-01-25 15:41     ` Don Baccus <dhogaza@pacifier.com>
  3 siblings, 0 replies; 36+ messages in thread

From: Don Baccus @ 2000-01-25 15:41 UTC (permalink / raw)
  To: Philip Warner <pjw@rhyme.com.au>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Bitmead <chris@bitmead.com>; pgsql-hackers@postgreSQL.org

At 05:00 PM 1/25/00 +1100, Philip Warner wrote:

>Out of curiosity, does the SQL spec give any rules or guidelines about when
>aggregates should be applied to resultant rows? Or is it one of the
>implementation-dependant things?

Well...my copy of Date's now actually in the mail, rather than in 
Boston, but at the moment that's not much help!

"order by" happens after everything, AFAIK.  For instance...

select ...
union
select ...
order by

orders the result of the union, which pretty much implies that
aggregates will happen first.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



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

* Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-25 08:30 ` Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 10:59   ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
  5 siblings, 1 reply; 36+ messages in thread

From: Julian Scarfe @ 2000-01-25 08:30 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql

Tom Lane wrote:
> 
> 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 use 'distinct on' to get the most recent reports for each of a group of
locations.  E.g.:

create table reports (
location     varchar(16),
report_time  datetime,
report_text  text);

select distinct on location * from reports where location ~~ 'Lond%' order by
location, reporttime desc;

to get the tuples that offer the most recent reports for each of London,
Londonderry, Londy etc.

Is there an alternative?

Julian Scarfe



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

* Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
@ 2000-01-25 10:59   ` Peter Eisentraut <e99re41@DoCS.UU.SE>
  2000-01-25 13:23     ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  0 siblings, 1 reply; 36+ messages in thread

From: Peter Eisentraut @ 2000-01-25 10:59 UTC (permalink / raw)
  To: Julian Scarfe <jscarfe@callnetuk.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgreSQL.org; pgsql-sql

On Tue, 25 Jan 2000, Julian Scarfe wrote:

> Tom Lane wrote:
> > 
> > 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 use 'distinct on' to get the most recent reports for each of a group of
> locations.  E.g.:
> 
> create table reports (
> location     varchar(16),
> report_time  datetime,
> report_text  text);
> 
> select distinct on location * from reports where location ~~ 'Lond%' order by
> location, reporttime desc;
> 
> to get the tuples that offer the most recent reports for each of London,
> Londonderry, Londy etc.
> 
> Is there an alternative?

select location, max(report_time) from reports group by location

This also has the negligible advantage that it gives you determinate
results.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




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

* Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 10:59   ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
@ 2000-01-25 13:23     ` Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 17:16       ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 36+ messages in thread

From: Julian Scarfe @ 2000-01-25 13:23 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql

on 25/1/00 10:59, Peter Eisentraut at e99re41@DoCS.UU.SE wrote:

> On Tue, 25 Jan 2000, Julian Scarfe wrote:

>> I use 'distinct on' to get the most recent reports for each of a group of
>> locations.  E.g.:
>> 
>> create table reports (
>> location     varchar(16),
>> report_time  datetime,
>> report_text  text);
>> 
>> select distinct on location * from reports where location ~~ 'Lond%' order by
>> location, reporttime desc;<pgsql-hackers@postgreSQL.org>
>> 
>> to get the tuples that offer the most recent reports for each of London,
>> Londonderry, Londy etc.
>> 
>> Is there an alternative?
> 
> select location, max(report_time) from reports group by location
> 
> This also has the negligible advantage that it gives you determinate
> results.

But I want report_text *too* -- that's the important bit, and it's not an
aggregate or common column in the group. So:

> Am I wrong or can DISTINCT ON not be rewritten in terms of GROUP BY in all
> cases?

I don't think so.

It has to be something like:

select * from reports as r1 where r1.report_time = (select max(report_time)
from reports as r2 where r2.location = r1.location) and r1.location ~~
'Lond%';

However, to my surprise, that's as quick as doing the 'distinct on'.


host=> explain select * from reports as r1 where r1.report_time = (select
max(report_time) from reports as r2 where r2.location = r1.location) and
r1.location ~~ 'Lond%';
NOTICE:  QUERY PLAN:

Index Scan using reports_by_location_issuetime on reports r1  (cost=186.24
size=2 width=334)
  SubPlan
    ->  Aggregate  (cost=3.62 size=0 width=0)
          ->  Index Scan using reports_by_location_report_time on reports r2
(cost=3.62 size=13 width=8)

EXPLAIN

host=> explain select distinct on location * from reports where location ~~
'Lond%' order by location, report_time desc;
NOTICE:  QUERY PLAN:

Unique  (cost=186.24 size=0 width=0)
  ->  Sort  (cost=186.24 size=0 width=0)
        ->  Index Scan using reports_by_location_issuetime on reports
(cost=186.24 size=2 width=334)

I'm surprise that the "reports_by_location_issuetime" index got used, since
it involves the issuetime field which is not the same as the report_time
field. I edited it and numerous other fields from the quoted 'create table'
for simplicity. There's also a "reports_by_location_report_time" which
indexes on, obviously, (location, report_time). Comments?

Julian Scarfe, learning...





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

* Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 10:59   ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
  2000-01-25 13:23     ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
@ 2000-01-25 17:16       ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 22:35         ` Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace Bruce Momjian <pgman@candle.pha.pa.us>
  2000-01-26 22:53         ` Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-27 18:33         ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 3 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-25 17:16 UTC (permalink / raw)
  To: Julian Scarfe <jscarfe@callnetuk.com>; +Cc: pgsql-sql

Julian Scarfe <jscarfe@callnetuk.com> writes:
>>> I use 'distinct on' to get the most recent reports for each of a group of
>>> locations.  E.g.:
>>> 
>>> select distinct on location * from reports where location ~~ 'Lond%'
>>> order by location, reporttime desc;
>>> 
>>> to get the tuples that offer the most recent reports for each of London,
>>> Londonderry, Londy etc.
>>> 
>>> Is there an alternative?
>> 
>> select location, max(report_time) from reports group by location

> But I want report_text *too* -- that's the important bit, and it's not an
> aggregate or common column in the group.

Right.  That is the motivation that was given for keeping DISTINCT ON
and trying to clean it up: if you ORDER BY the DISTINCT field plus
enough other fields to give a unique ordering, and then rely on the
(undocumented) implementation property that you'll get the first tuple
out of each group with the same DISTINCT field, then you can get useful
effects like this one.

It's a kluge, and it's not readily extensible to cases such as needing to
apply the DISTINCT filter across two fields, but it does solve problems
that you can't straightforwardly rewrite into GROUP BY.

Tell you what: there are two things that are really bugging me about
DISTINCT ON.  If I could get consensus on patching these things,
I'd be willing to do that rather than yank it.

One gripe is the lame-brain syntax: because there is no separator
between the DISTINCT ON column name and the first select targetlist
item, the DISTINCT ON target cannot be anything more complex than an
unadorned column name.  For example "SELECT DISTINCT ON A+B+C, ..."
must be interpreted as DISTINCT ON A with a first target of +B+C;
if we tried to allow more complex expressions then it becomes ambiguous
whether that was meant or DISTINCT ON A+B with a first target of +C.
To fix this we need some kind of syntactic separator.  The cleanest
idea that comes to my mind is to require parentheses around the ON
target:
		SELECT DISTINCT ON (expression) target1, ...
One immediate advantage of allowing an expression is that you can do
distinct-on-two-fields in a rather klugy way, eg
		SELECT DISTINCT ON (field1 || ' ' || field2) ...
We might someday extend it to allow multiple DISTINCT fields, eg,
		SELECT DISTINCT ON (expr1 [, expr2 ...]) target1, ...
but I'm not promising to do that now (unless it's really easy ;-)).

The other gripe is the bad interaction with ORDER BY, as was illustrated
in my example of last night: if you ORDER BY something that's not the
DISTINCT field, it doesn't work.  We could get something that kind of
works by applying two sorting passes: first sort by the DISTINCT field,
then run the duplicate-elimination filter, then re-sort by the ORDER BY
field(s).  The trouble with that is that it means the user no longer has
control of the sort order within the same-DISTINCT-field-value groups
during the first sort phase, so there's no way for him to control which
tuple gets kept from each group.  What I'd prefer to do is put in an
error check that says "if you use both DISTINCT ON and ORDER BY, then
the DISTINCT ON expression must be the first ORDER BY item".  That way
the ORDER BY ordering can be the same one used for the
duplicate-elimination pass, and we don't have the semantic problem.
Note that Julian's example meets this constraint.

(BTW, ordinary SELECT DISTINCT has this same sort of problem if you try
to ORDER BY an expression that doesn't appear in the target list.
SQL92 avoids the issue by not allowing you to ORDER BY expressions that
aren't in the target list, period.  We do allow that --- but not when
you use DISTINCT.  Essentially, I want to enforce that same restriction
for DISTINCT ON.)

The other piece of the puzzle would be to document that DISTINCT ON
keeps the first tuple out of each set with the same DISTINCT ON value.

Does that sound like a plan?

			regards, tom lane



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

* Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 10:59   ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
  2000-01-25 13:23     ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 17:16       ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-25 22:35         ` Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 0 replies; 36+ messages in thread

From: Bruce Momjian @ 2000-01-25 22:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Julian Scarfe <jscarfe@callnetuk.com>; pgsql-sql

> (BTW, ordinary SELECT DISTINCT has this same sort of problem if you try
> to ORDER BY an expression that doesn't appear in the target list.
> SQL92 avoids the issue by not allowing you to ORDER BY expressions that
> aren't in the target list, period.  We do allow that --- but not when
> you use DISTINCT.  Essentially, I want to enforce that same restriction
> for DISTINCT ON.)
> 
> The other piece of the puzzle would be to document that DISTINCT ON
> keeps the first tuple out of each set with the same DISTINCT ON value.
> 
> Does that sound like a plan?

Yes, very clear.  Good.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@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] 36+ messages in thread

* Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 10:59   ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
  2000-01-25 13:23     ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 17:16       ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-26 22:53         ` Chris Bitmead <chris@bitmead.com>
  2000-01-26 23:05           ` Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 1 reply; 36+ messages in thread

From: Chris Bitmead @ 2000-01-26 22:53 UTC (permalink / raw)
  To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>

Tom Lane wrote:
>      SELECT DISTINCT ON (expression) target1, ...
> One immediate advantage of allowing an expression is that you can do
> distinct-on-two-fields in a rather klugy way, eg
>                 SELECT DISTINCT ON (field1 || ' ' || field2) ...

As long as we're fixing the syntax, I'm wondering if it wouldn't be more
logical to have DISTINCT ON somewhere later in the syntax. I'm wondering
if that might also avoid the need for () as a side effect. Like this
perhaps....

SELECT x, y, z FROM foo WHERE z DISTINCT ON x, y ORDER BY x, y;

> What I'd prefer to do is put in an
> error check that says "if you use both DISTINCT ON and ORDER BY, then
> the DISTINCT ON expression must be the first ORDER BY item".  

Better, but still a little kludgy. What about a syntax that really
supports everything you want? Like maybe...

SELECT x, y, z FROM foo DISTINCT ON x, y DESC ORDER BY z ASC;

Distinct on now has a similar syntax to the order by clause. What this
means is, do the DISTINCT ON test by ordering DESC (so you get the
distinct item with the largest value of x, y), and then order the final
result by z ascending.

Unless I'm missing something that gives everybody what they want.

HANG ON, I've got a better idea.....

The other alternative is to make DISTINCT ON a bit like GROUP BY. So you
would have
something like

SELECT x, y, max(z) AS mmm FROM foo DISTINCT ON x, y ORDER BY mmm;

Like GROUP BY where you group by the non-aggregate fields in the SELECT
clause, you use DISTINCT ON, to mention the non-aggregate clauses in the
SELECT. The aggregate clause are used to select WHICH of the resulting
fields are selected for presentation. This would have the benefit of
being more general so you could select, say the city with the longest
name in each state...

SELECT state, zipcode, longest_string(cityname) FROM cities DISTINCT ON
state ORDER BY zipcode;

I don't know how far I'm stepping here from the SQL paradigm, but it
sure seems cool....

Chris.



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

* Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 10:59   ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
  2000-01-25 13:23     ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 17:16       ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-26 22:53         ` Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
@ 2000-01-26 23:05           ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-26 23:16             ` Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 36+ messages in thread

From: Tom Lane @ 2000-01-26 23:05 UTC (permalink / raw)
  To: Chris Bitmead <chris@bitmead.com>; +Cc: pgsql-sql

Chris Bitmead <chris@bitmead.com> writes:
> As long as we're fixing the syntax, I'm wondering if it wouldn't be more
> logical to have DISTINCT ON somewhere later in the syntax.

Well, SELECT DISTINCT is that way because SQL92 says so.  Putting the
DISTINCT ON variant somewhere else might be logically purer, but I think
it'd be confusing.

Also, isn't the reason we have DISTINCT ON at all that it's there to
be compatible with MySQL or someone?  I figured adding parens would be
about the least-surprising variant syntax for a person used to those
other products.

			regards, tom lane



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

* Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 10:59   ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
  2000-01-25 13:23     ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 17:16       ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-26 22:53         ` Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace Chris Bitmead <chris@bitmead.com>
  2000-01-26 23:05           ` Re: [HACKERS] Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-26 23:16             ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 36+ messages in thread

From: Bruce Momjian @ 2000-01-26 23:16 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Bitmead <chris@bitmead.com>; pgsql-sql

> Chris Bitmead <chris@bitmead.com> writes:
> > As long as we're fixing the syntax, I'm wondering if it wouldn't be more
> > logical to have DISTINCT ON somewhere later in the syntax.
> 
> Well, SELECT DISTINCT is that way because SQL92 says so.  Putting the
> DISTINCT ON variant somewhere else might be logically purer, but I think
> it'd be confusing.
> 
> Also, isn't the reason we have DISTINCT ON at all that it's there to
> be compatible with MySQL or someone?  I figured adding parens would be
> about the least-surprising variant syntax for a person used to those
> other products.
> 

Makes sense to me.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@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] 36+ messages in thread

* Re: [SQL] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-25 08:30 ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 10:59   ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <e99re41@DoCS.UU.SE>
  2000-01-25 13:23     ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Julian Scarfe <jscarfe@callnetuk.com>
  2000-01-25 17:16       ` Re: [SQL] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-27 18:33         ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-27 18:33 UTC (permalink / raw)
  To: Julian Scarfe <jscarfe@callnetuk.com>; +Cc: pgsql-sql

I wrote:
> To fix this we need some kind of syntactic separator.  The cleanest
> idea that comes to my mind is to require parentheses around the ON
> target:
> 		SELECT DISTINCT ON (expression) target1, ...
> One immediate advantage of allowing an expression is that you can do
> distinct-on-two-fields in a rather klugy way, eg
> 		SELECT DISTINCT ON (field1 || ' ' || field2) ...
> We might someday extend it to allow multiple DISTINCT fields, eg,
> 		SELECT DISTINCT ON (expr1 [, expr2 ...]) target1, ...
> but I'm not promising to do that now (unless it's really easy ;-)).

FYI, I have committed this change.  It did turn out to be just as easy
to support multiple DISTINCT ON expressions, so 7.0 will accept
	SELECT DISTINCT ON (expr1 [, expr2 ...]) target1, ...

> What I'd prefer to do is put in an
> error check that says "if you use both DISTINCT ON and ORDER BY, then
> the DISTINCT ON expression must be the first ORDER BY item".

More generally, if you use both then the DISTINCT and ORDER lists must
match until one or the other runs out.

			regards, tom lane



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-26 18:34 ` Peter Eisentraut <peter_e@gmx.net>
  2000-01-26 18:40   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  5 siblings, 1 reply; 36+ messages in thread

From: Peter Eisentraut @ 2000-01-26 18:34 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql

On 2000-01-24, Tom Lane mentioned:

> 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.

Our documents say that DISTINCT ON is equivalent to GROUP BY. I still
don't see why that wouldn't be true. You can always rewrite

select distinct on a a,b from test

as

select a, xxx(b) from test group by a

where xxx is some aggregate function (presumably min or max).

You can also rewrite

select distinct on a a,b,c from test

as

select a, b, c from test group by a, b, c

or using some aggregates here as well. At least you can control your
results that way.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-26 18:34 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <peter_e@gmx.net>
@ 2000-01-26 18:40   ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-27 22:28     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <peter_e@gmx.net>
  2000-01-28 01:21     ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Philip Warner <pjw@rhyme.com.au>
  0 siblings, 2 replies; 36+ messages in thread

From: Tom Lane @ 2000-01-26 18:40 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql

Peter Eisentraut <peter_e@gmx.net> writes:
> Our documents say that DISTINCT ON is equivalent to GROUP BY. I still
> don't see why that wouldn't be true. You can always rewrite
> select distinct on a a,b from test
> as
> select a, xxx(b) from test group by a
> where xxx is some aggregate function (presumably min or max).

Not really.  Look at Julian's example.  He can't rewrite as

	select a, min(b), min(c) from test group by a

because the idea is to get the c that corresponds to the min b.
If you do it with two independent aggregates then the b and c
you get back may be from different tuples.

I could imagine fixing this with a two-input aggregate, say

	select a, min(b), keyofmin(b, c) from test group by a

where keyofmin is defined to return the c associated with the min b.
But that'd be a pain to implement, first because we have no support
for multi-argument aggregates, and second because you'd need a ton
of separate keyofmin implementations for the cross-product of the
data types you might want to deal with.  So this is nearly as
klugy as the SELECT DISTINCT ON approach --- and not any more
standard, either.

			regards, tom lane



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-26 18:34 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <peter_e@gmx.net>
  2000-01-26 18:40   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-27 22:28     ` Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 0 replies; 36+ messages in thread

From: Peter Eisentraut @ 2000-01-27 22:28 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql

On 2000-01-26, Tom Lane mentioned:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > Our documents say that DISTINCT ON is equivalent to GROUP BY. I still
> > don't see why that wouldn't be true. You can always rewrite
> > select distinct on a a,b from test
> > as
> > select a, xxx(b) from test group by a
> > where xxx is some aggregate function (presumably min or max).
> 
> Not really.  Look at Julian's example.  He can't rewrite as
> 
> 	select a, min(b), min(c) from test group by a
> 
> because the idea is to get the c that corresponds to the min b.

I see. I believe what you want is this:

select one.a, two.b, two.c
from
    (select a, min(b) as "min_b" from test group by a) as one,
    (select b, c from test) as two
where one."min_b" = two.b

Not sure if this is completely legal as it stands but at least the idea
would be to join the grouped select with the plain one to get the c
corresponding to the minimum b. But of course we don't offer that, so it's
distinct on until then. (It would really surprise me if the distinct on
functionality was not at all possible to emulate using SQL, since in my
experience it is fairly complete with regards to querying options at
least.)

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden





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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
  2000-01-26 18:34 ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Peter Eisentraut <peter_e@gmx.net>
  2000-01-26 18:40   ` Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-28 01:21     ` Philip Warner <pjw@rhyme.com.au>
  1 sibling, 0 replies; 36+ messages in thread

From: Philip Warner @ 2000-01-28 01:21 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql

At 23:28 27/01/00 +0100, Peter Eisentraut wrote:
>
>select one.a, two.b, two.c
>from
>    (select a, min(b) as "min_b" from test group by a) as one,
>    (select b, c from test) as two
>where one."min_b" = two.b
>
>Not sure if this is completely legal as it stands but at least the idea
>would be to join the grouped select with the plain one to get the c
>corresponding to the minimum b. But of course we don't offer that, so it's
>distinct on until then. (It would really surprise me if the distinct on
>functionality was not at all possible to emulate using SQL, since in my
>experience it is fairly complete with regards to querying options at
>least.)

You are quite right - with a complete SQL impleentation, DISTINCT ON
becomes superfluous. Although it may give the optimizer usefull hints as to
how to approach the query. You actually have to be a bit more carefull to
avoid duplicates, something like:

Select 
   a,b,c
From 
    (Select a, min(b) from test group by a) as one(a,min_b),
    (Select b, c from test t2 where t2.a = one.a limit to 1 row) as two

This is legal on the DB I use most of the time.






----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



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

* Re: [HACKERS] DISTINCT ON: speak now or forever hold your peace
  2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-26 22:23 ` Philip Warner <pjw@rhyme.com.au>
  5 siblings, 0 replies; 36+ messages in thread

From: Philip Warner @ 2000-01-26 22:23 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql

At 19:34 26/01/00 +0100, Peter Eisentraut wrote:
>On 2000-01-24, Tom Lane mentioned:
>
>> 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.
>
>Our documents say that DISTINCT ON is equivalent to GROUP BY. I still
>don't see why that wouldn't be true. You can always rewrite
>
>select distinct on a a,b from test
>
>as
>
>select a, xxx(b) from test group by a
>
>where xxx is some aggregate function (presumably min or max).
>
>You can also rewrite
>
>select distinct on a a,b,c from test
>
>as
>
>select a, b, c from test group by a, b, c
>
>or using some aggregates here as well. At least you can control your
>results that way.


I only learned about DISTINCT ON in this discussion, but my impression is
that it has one advantage over GROUP BY, i that it produces a 'consistent'
tuple. ie. the tuple it produces is guaranteed to exist in the database,
whereas using GROUP BY with aggregates will not produce a 'real' row:

f1|f2|f3
--------
1  2  3
1  3  2
2  3  1
2  1  3
3  1  2
3  2  1

'select distinct on f1 f1,f2,f3 from <somewhere> order by f1, f2, f3' will
produce (I think):

f1|f2|f3
--------
1  2  3
2  1  3
3  1  2

...where each resulting tuple actually exists in the DB. I can't see a way
of doing this with aggreagates unless a 'FIRST' function is defined, but
that (as already discussed) will not obey the 'order by' clause. Maybe an
'ANY' aggregate would do it, but then it would not always produce the same
results.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



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


end of thread, other threads:[~2000-01-28 01:21 UTC | newest]

Thread overview: 36+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-25 01:50 DISTINCT ON: speak now or forever hold your peace Tom Lane <tgl@sss.pgh.pa.us>
2000-01-25 02:41 ` Chris Bitmead <chris@bitmead.com>
2000-01-25 03:45   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-25 03:55     ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-25 07:12       ` Michael Meskes <meskes@postgresql.org>
2000-01-25 10:30       ` Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-01-25 07:15     ` Adriaan Joubert <a.joubert@albourne.com>
2000-01-25 07:45       ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-25 02:50 ` Mike Mascari <mascarm@mascari.com>
2000-01-25 03:09   ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-25 03:03 ` Philip Warner <pjw@rhyme.com.au>
2000-01-25 03:09   ` Chris Bitmead <chris@bitmead.com>
2000-01-26 18:34     ` Peter Eisentraut <peter_e@gmx.net>
2000-01-25 04:05   ` Philip Warner <pjw@rhyme.com.au>
2000-01-25 05:01     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-25 05:20     ` Don Baccus <dhogaza@pacifier.com>
2000-01-25 06:00     ` Philip Warner <pjw@rhyme.com.au>
2000-01-25 06:07       ` Chris Bitmead <chris@bitmead.com>
2000-01-25 06:12       ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-25 07:54       ` Philip Warner <pjw@rhyme.com.au>
2000-01-25 08:20         ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-25 15:41     ` Don Baccus <dhogaza@pacifier.com>
2000-01-25 08:30 ` Julian Scarfe <jscarfe@callnetuk.com>
2000-01-25 10:59   ` Peter Eisentraut <e99re41@DoCS.UU.SE>
2000-01-25 13:23     ` Julian Scarfe <jscarfe@callnetuk.com>
2000-01-25 17:16       ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-25 22:35         ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-26 22:53         ` Chris Bitmead <chris@bitmead.com>
2000-01-26 23:05           ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-26 23:16             ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-27 18:33         ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-26 18:34 ` Peter Eisentraut <peter_e@gmx.net>
2000-01-26 18:40   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-27 22:28     ` Peter Eisentraut <peter_e@gmx.net>
2000-01-28 01:21     ` Philip Warner <pjw@rhyme.com.au>
2000-01-26 22:23 ` Philip Warner <pjw@rhyme.com.au>

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