pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
5+ messages / 4 participants
[nested] [flat]
* "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
@ 2007-02-15 11:19 Walter Cruz <walter.php@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Walter Cruz @ 2007-02-15 11:19 UTC (permalink / raw)
To: pgsql-sql
CREATE TABLE test
(
id int4 NOT NULL DEFAULT nextval('teste_id_seq'::regclass),
name varchar,
number int4
)
WITHOUT OIDS;
The data:
1;"walter";1
2;"walter";1
3;"walter";1
4;"walter";1
5;"walter";2
6;"walter";3
7;"rodrigo";1
8;"rodrigo";2
9;"rodrigo";3
The query:
SELECT distinct name from test order by number
(well, I think that que query doesn't make any sense, but raises the error :) )
The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must
appear in select list is due to a standart implementarion or a design
decision of postgres?
The comentary on parse_clause.c looks like the second option. I'm right?
[]'s
- Walter
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
@ 2007-02-15 13:35 Richard Huxton <dev@archonet.com>
parent: Walter Cruz <walter.php@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2007-02-15 13:35 UTC (permalink / raw)
To: Walter Cruz <walter.php@gmail.com>; +Cc: pgsql-sql
Walter Cruz wrote:
>
> SELECT distinct name from test order by number
>
> (well, I think that que query doesn't make any sense, but raises the
> error :) )
>
> The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must
> appear in select list is due to a standart implementarion or a design
> decision of postgres?
I think ORDER BY is defined to take place after DISTINCT, which means
there is no meaningful "number" for it to order by. You could
arbitrarily choose the first number encountered, but I can't see what
sense it would make to order by them.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
@ 2007-02-15 14:04 Michael Glaesemann <grzm@seespotcode.net>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Glaesemann @ 2007-02-15 14:04 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Walter Cruz <walter.php@gmail.com>; pgsql-sql
On Feb 15, 2007, at 22:35 , Richard Huxton wrote:
> Walter Cruz wrote:
>> SELECT distinct name from test order by number
>> (well, I think that que query doesn't make any sense, but raises
>> the error :) )
>> The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must
>> appear in select list is due to a standart implementarion or a design
>> decision of postgres?
>
> I think ORDER BY is defined to take place after DISTINCT, which
> means there is no meaningful "number" for it to order by. You could
> arbitrarily choose the first number encountered, but I can't see
> what sense it would make to order by them.
I believe the reason is that DISTINCT depends on a sort to determine
uniqueness (distinctness), so it's a implementation detail that if
you're going to include an ORDER BY, you also need to include the
same columns in the ORDER BY in the DISTINCT clause. Though I suspect
Richard is right that ORDER BY takes place after DISTINCT. (My
cursory attempt at parsing the SQL 2003 draft failed me.)
On further thought, I bet
SELECT DISTINCT name
FROM test
ORDER BY name, number
fails with a different error, one directly supporting Richard's
conclusion.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
@ 2007-02-15 17:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2007-02-15 17:10 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Richard Huxton <dev@archonet.com>; Walter Cruz <walter.php@gmail.com>; pgsql-sql
Michael Glaesemann <grzm@seespotcode.net> writes:
> On Feb 15, 2007, at 22:35 , Richard Huxton wrote:
>> Walter Cruz wrote:
>>> The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must
>>> appear in select list is due to a standart implementarion or a design
>>> decision of postgres?
>>
>> I think ORDER BY is defined to take place after DISTINCT, which
>> means there is no meaningful "number" for it to order by. You could
>> arbitrarily choose the first number encountered, but I can't see
>> what sense it would make to order by them.
> I believe the reason is that DISTINCT depends on a sort to determine
> uniqueness (distinctness), so it's a implementation detail that if
> you're going to include an ORDER BY, you also need to include the
> same columns in the ORDER BY in the DISTINCT clause.
No, there's actually a definitional reason for it. Consider
SELECT DISTINCT x FROM tab ORDER BY y;
For any particular x-value in the table there might be many different y
values. Which one will you use to sort that x-value in the output?
Back in SQL92 they avoided this problem by specifying that ORDER BY
entries had to reference output columns. SQL99 has some messy verbiage
that I think comes out at the same place as our restriction:
A) If K(i) is not equivalent to a <value expression>
immediately contained in any <derived column> in the
<select list> SL of <query specification> QS contained
in QE, then:
I) T shall not be a grouped table.
II) QS shall not specify the <set quantifier> DISTINCT
or directly contain one or more <set function
specification>s.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
@ 2007-02-15 22:59 Walter Cruz <walter.php@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Walter Cruz @ 2007-02-15 22:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; Richard Huxton <dev@archonet.com>; pgsql-sql
Thanks Tom, Thank all :)
Maybe the commentary on parse_clase.c (beggining with "Now, DISTINCT
list consists of all non-resjunk") needs to be updated - In the
comment, looks likes this is a postgresql limitation.
[]'s
- Walter
On 2/15/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Michael Glaesemann <grzm@seespotcode.net> writes:
> > On Feb 15, 2007, at 22:35 , Richard Huxton wrote:
> >> Walter Cruz wrote:
> >>> The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must
> >>> appear in select list is due to a standart implementarion or a design
> >>> decision of postgres?
> >>
> >> I think ORDER BY is defined to take place after DISTINCT, which
> >> means there is no meaningful "number" for it to order by. You could
> >> arbitrarily choose the first number encountered, but I can't see
> >> what sense it would make to order by them.
>
> > I believe the reason is that DISTINCT depends on a sort to determine
> > uniqueness (distinctness), so it's a implementation detail that if
> > you're going to include an ORDER BY, you also need to include the
> > same columns in the ORDER BY in the DISTINCT clause.
>
> No, there's actually a definitional reason for it. Consider
>
> SELECT DISTINCT x FROM tab ORDER BY y;
>
> For any particular x-value in the table there might be many different y
> values. Which one will you use to sort that x-value in the output?
>
> Back in SQL92 they avoided this problem by specifying that ORDER BY
> entries had to reference output columns. SQL99 has some messy verbiage
> that I think comes out at the same place as our restriction:
>
> A) If K(i) is not equivalent to a <value expression>
> immediately contained in any <derived column> in the
> <select list> SL of <query specification> QS contained
> in QE, then:
>
> I) T shall not be a grouped table.
>
> II) QS shall not specify the <set quantifier> DISTINCT
> or directly contain one or more <set function
> specification>s.
>
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-02-15 22:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-15 11:19 "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql? Walter Cruz <walter.php@gmail.com>
2007-02-15 13:35 ` Richard Huxton <dev@archonet.com>
2007-02-15 14:04 ` Michael Glaesemann <grzm@seespotcode.net>
2007-02-15 17:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-15 22:59 ` Walter Cruz <walter.php@gmail.com>
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