pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT DISTINCT question
10+ messages / 4 participants
[nested] [flat]

* SELECT DISTINCT question
@ 1999-07-10 20:16  Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 2 replies; 10+ messages in thread

From: Oleg Bartunov @ 1999-07-10 20:16 UTC (permalink / raw)
  To: hackers@postgreSQL.org; +Cc: pgsql-sql

I got a problem with query:

select distinct (date), bytes  from access_log;

which works but not as I expect. I thought this query will select
all rows with distinct values of 'date' column, but it get
distinct pairs 'date, bytes' . From documnetation I see

"DISTINCT will eliminate all duplicate rows from the selection. 
DISTINCT ON column will eliminate all duplicates in the specified column; 
this is equivalent to using GROUP BY column. 
ALL will return all candidate rows, including duplicates."
       
discovery=> select distinct on date,bytes  from access_log;
ERROR:  parser: parse error at or near ","

	Regards,

		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83





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

* Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-10 21:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Oleg Bartunov <oleg@sai.msu.su>
  1 sibling, 1 reply; 10+ messages in thread

From: Tom Lane @ 1999-07-10 21:18 UTC (permalink / raw)
  To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: hackers@postgreSQL.org; pgsql-sql

Oleg Bartunov <oleg@sai.msu.su> writes:
> discovery=> select distinct on date,bytes  from access_log;
> ERROR:  parser: parse error at or near ","

The syntax for SELECT DISTINCT ON is just as brain-damaged as the
functionality itself: there's no comma after the column name.
You want

select distinct on date  date,bytes  from access_log;

The reason the functionality is brain-damaged is that there's no way to
know which tuple out of the set of tuples with a given "date" value will
be the one returned.

SELECT DISTINCT ON is not in SQL92, and I think it shouldn't be in
Postgres either...

			regards, tom lane



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

* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-11 06:09  Oleg Bartunov <oleg@sai.msu.su>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Oleg Bartunov @ 1999-07-11 06:09 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: hackers@postgreSQL.org; pgsql-sql

On Sat, 10 Jul 1999, Tom Lane wrote:

> Date: Sat, 10 Jul 1999 17:18:28 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: hackers@postgreSQL.org, pgsql-sql@postgreSQL.org
> Subject: [SQL] Re: [HACKERS] SELECT DISTINCT question 
> 
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > discovery=> select distinct on date,bytes  from access_log;
> > ERROR:  parser: parse error at or near ","
> 
> The syntax for SELECT DISTINCT ON is just as brain-damaged as the
> functionality itself: there's no comma after the column name.
> You want
> 
> select distinct on date  date,bytes  from access_log;
> 

thanks, this works. But why parser complains about such query:

discovery=> select distinct on a.date  a.date, a.bytes  from access_log a;
ERROR:  parser: parse error at or near "."

In this query I could just omit '.', but in more complex query 
I probably could need one.

> The reason the functionality is brain-damaged is that there's no way to
> know which tuple out of the set of tuples with a given "date" value will
> be the one returned.
> 
> SELECT DISTINCT ON is not in SQL92, and I think it shouldn't be in
> Postgres either...


I'm not an SQL expert, but if it works and this feature is in standard
but only syntax is diffrent, why just not to use standard

select distinct(date), bytes  from access_log;

Or I'm missing here ?


	Regards,
		Oleg
> 
> 			regards, tom lane
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83




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

* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-11 15:38  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 1999-07-11 15:38 UTC (permalink / raw)
  To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: hackers@postgreSQL.org; pgsql-sql

Oleg Bartunov <oleg@sai.msu.su> writes:
> thanks, this works. But why parser complains about such query:

> discovery=> select distinct on a.date  a.date, a.bytes  from access_log a;
> ERROR:  parser: parse error at or near "."

Probably the grammar specifies just <column name> and not anything
more complex after DISTINCT ON.  It'd be risky to try to accept a
general expression after ON, due to the silly decision to leave out
any terminating punctuation.

>> SELECT DISTINCT ON is not in SQL92, and I think it shouldn't be in
>> Postgres either...

> I'm not an SQL expert, but if it works and this feature is in standard
> but only syntax is diffrent,

No, there is no feature in SQL that allows DISTINCT on a subset of
columns, period.  This is not merely a matter of syntax, it's a
fundamental semantic issue.

> why just not to use standard
>
> select distinct(date), bytes  from access_log;
>
> Or I'm missing here ?

I don't think that does what you expect it to (hint: the
parentheses are redundant).

			regards, tom lane



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

* Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-13 20:50  Hannu Krosing <hannu@trust.ee>
  parent: Oleg Bartunov <oleg@sai.msu.su>
  1 sibling, 1 reply; 10+ messages in thread

From: Hannu Krosing @ 1999-07-13 20:50 UTC (permalink / raw)
  To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: hackers@postgreSQL.org; pgsql-sql

Oleg Bartunov wrote:
> 
> I got a problem with query:
> 
> select distinct (date), bytes  from access_log;
> 
> which works but not as I expect. I thought this query will select
> all rows with distinct values of 'date' column, but it get
> distinct pairs 'date, bytes' . From documnetation I see
> 
> "DISTINCT will eliminate all duplicate rows from the selection.
> DISTINCT ON column will eliminate all duplicates in the specified column;
> this is equivalent to using GROUP BY column.

If it is equivalent to GROUP BY then it should allow only aggregates 
in non-distinct columns, like:

select distinct on date date, sum(bytes) from access_log;

If it does not, then it should be files as a bug imho.

-----------------
Hannu



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

* Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-13 21:31  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Hannu Krosing <hannu@trust.ee>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 1999-07-13 21:31 UTC (permalink / raw)
  To: Hannu Krosing <hannu@trust.ee>; +Cc: hackers@postgreSQL.org; pgsql-sql

Hannu Krosing <hannu@trust.ee> writes:
>> "DISTINCT will eliminate all duplicate rows from the selection.
>> DISTINCT ON column will eliminate all duplicates in the specified column;
>> this is equivalent to using GROUP BY column."

> If it is equivalent to GROUP BY then it should allow only aggregates 
> in non-distinct columns, like:
> select distinct on date date, sum(bytes) from access_log;
> If it does not, then it should be files as a bug imho.

It does not.  Whether that is a bug is hard to say, since there is no
standard I know of that says what it *is* supposed to do.

If you look at the select_distinct_on regress test outputs, I bet you
will be even less happy:

QUERY: SELECT DISTINCT ON string4 two, string4, ten
	   FROM tmp
   ORDER BY two using <, string4 using <, ten using <;
two|string4|ten
---+-------+---
  0|AAAAxx |  0
  0|HHHHxx |  0
  0|OOOOxx |  0
  0|VVVVxx |  0
  1|AAAAxx |  1
  1|HHHHxx |  1
  1|OOOOxx |  1
  1|VVVVxx |  1
(8 rows)

That's not exactly my idea of "distinct" values of string4 ---
but apparently whoever made up the regress test thought it was OK!

Can anyone defend this feature or provide a coherent definition
of what it's supposed to be doing?  My urge to rip it out is
growing stronger and stronger...

			regards, tom lane



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

* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-09-23 17:18  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Bruce Momjian @ 1999-09-23 17:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@trust.ee>; hackers@postgreSQL.org; pgsql-sql

Tom, any status on this DISTINCT ON ripout?


> Hannu Krosing <hannu@trust.ee> writes:
> >> "DISTINCT will eliminate all duplicate rows from the selection.
> >> DISTINCT ON column will eliminate all duplicates in the specified column;
> >> this is equivalent to using GROUP BY column."
> 
> > If it is equivalent to GROUP BY then it should allow only aggregates 
> > in non-distinct columns, like:
> > select distinct on date date, sum(bytes) from access_log;
> > If it does not, then it should be files as a bug imho.
> 
> It does not.  Whether that is a bug is hard to say, since there is no
> standard I know of that says what it *is* supposed to do.
> 
> If you look at the select_distinct_on regress test outputs, I bet you
> will be even less happy:
> 
> QUERY: SELECT DISTINCT ON string4 two, string4, ten
> 	   FROM tmp
>    ORDER BY two using <, string4 using <, ten using <;
> two|string4|ten
> ---+-------+---
>   0|AAAAxx |  0
>   0|HHHHxx |  0
>   0|OOOOxx |  0
>   0|VVVVxx |  0
>   1|AAAAxx |  1
>   1|HHHHxx |  1
>   1|OOOOxx |  1
>   1|VVVVxx |  1
> (8 rows)
> 
> That's not exactly my idea of "distinct" values of string4 ---
> but apparently whoever made up the regress test thought it was OK!
> 
> Can anyone defend this feature or provide a coherent definition
> of what it's supposed to be doing?  My urge to rip it out is
> growing stronger and stronger...
> 
> 			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] 10+ messages in thread

* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-09-23 21:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 2 replies; 10+ messages in thread

From: Tom Lane @ 1999-09-23 21:27 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: Hannu Krosing <hannu@trust.ee>; hackers@postgreSQL.org; pgsql-sql

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Tom, any status on this DISTINCT ON ripout?

I haven't done anything about it, if that's what you mean.

I didn't get any indication of whether anyone else agreed with me
(maybe the lack of loud complaints should be taken as consent?)

			regards, tom lane



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

* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-09-23 21:56  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 10+ messages in thread

From: Bruce Momjian @ 1999-09-23 21:56 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@trust.ee>; hackers@postgreSQL.org; pgsql-sql

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Tom, any status on this DISTINCT ON ripout?
> 
> I haven't done anything about it, if that's what you mean.
> 
> I didn't get any indication of whether anyone else agreed with me
> (maybe the lack of loud complaints should be taken as consent?)

I think so.

-- 
  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] 10+ messages in thread

* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-09-23 21:58  Bruce Momjian <maillist@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 10+ messages in thread

From: Bruce Momjian @ 1999-09-23 21:58 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@trust.ee>; hackers@postgresql.org; pgsql-sql

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Tom, any status on this DISTINCT ON ripout?
> 
> I haven't done anything about it, if that's what you mean.
> 
> I didn't get any indication of whether anyone else agreed with me
> (maybe the lack of loud complaints should be taken as consent?)

I will wrap up the mail messages, and put it on the TODO list.

-- 
  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] 10+ messages in thread


end of thread, other threads:[~1999-09-23 21:58 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-10 20:16 SELECT DISTINCT question Oleg Bartunov <oleg@sai.msu.su>
1999-07-10 21:18 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-11 06:09   ` Oleg Bartunov <oleg@sai.msu.su>
1999-07-11 15:38     ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-13 20:50 ` Hannu Krosing <hannu@trust.ee>
1999-07-13 21:31   ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-23 17:18     ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-23 21:27       ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-23 21:56         ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-23 21:58         ` 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