BUG #14188: "FOR SELECT DISTINCT, ORDER BY expressions must appear IN SELECT list" error and table alias
The following bug has been logged on the website:
Bug reference: 14188
Logged by: Gabriele Monfardini
Email address: gabrimonfa@gmail.com
PostgreSQL version: 9.5.3
Operating system: Debian
Description:
CREATE TABLE table1 (id integer primary key, name varchar);
CREATE TABLE table2 (id integer primary key, home varchar);
SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id)
ORDER BY t1.name;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select
list
RIGA 1: ...t1 INNER JOIN table2 t2 ON (t1.id = t2.id) ORDER BY t1.name;
SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id)
ORDER BY name;
name
------
(0 righe)
Sure name may be qualified in SELECT list but it is not ambiguous.
Other example:
CREATE TABLE table1 (id INTEGER, NAME VARCHAR);
CREATE TABLE table2 (id INTEGER, home VARCHAR);
SELECT DISTINCT * FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id)
ORDER BY NAME;
id | NAME | id | home
----+------+----+------
(0 righe)
SELECT DISTINCT * FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id)
ORDER BY t1.NAME;
ERROR: FOR SELECT DISTINCT, ORDER BY expressions must appear IN SELECT
list
RIGA 1: ...t1 INNER JOIN table2 t2 ON (t1.id = t2.id) ORDER BY t1.NAME;
SELECT DISTINCT t1.*,t2.* FROM table1 t1 INNER JOIN table2 t2 ON (t1.id =
t2.id) ORDER BY t1.NAME;
id | NAME | id | home
----+------+----+------
(0 righe)
Surely query may be made smarter always qualifying attributes in SELECT list
but I think it is may be considered valid SQL.
Or it is to be considered invalid since "name" without qualification in
SELECT is considered not an attribute of t1 but an attribute of "(t1 join
t2)" and thus t1.name is not in select list?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
gabrimonfa@gmail.com writes:
CREATE TABLE table1 (id integer primary key, name varchar);
CREATE TABLE table2 (id integer primary key, home varchar);
SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id)
ORDER BY t1.name;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id)
ORDER BY name;
[ok]
The reason for the discrepancy is that "t1.name" refers to an output
column of t1, while "name" refers to an output column of the unnamed JOIN.
While those are semantically equivalent in this particular case, they are
not so in general --- in particular, had this been a FULL JOIN, they
would definitely not be equivalent. PG's parser treats them as different
variables and therefore sees "ORDER BY t1.name" as unrelated to the value
being distinct'ed on.
We might someday try to make the parser smarter about recognizing such
equivalences earlier, but I'm not terribly excited about it.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Tue, Jun 14, 2016 at 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
gabrimonfa@gmail.com writes:
CREATE TABLE table1 (id integer primary key, name varchar);
CREATE TABLE table2 (id integer primary key, home varchar);SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id =
t2.id)
ORDER BY t1.name;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in selectlist
SELECT DISTINCT name FROM table1 t1 INNER JOIN table2 t2 ON (t1.id =
t2.id)
ORDER BY name;
[ok]The reason for the discrepancy is that "t1.name" refers to an output
column of t1, while "name" refers to an output column of the unnamed JOIN.
While those are semantically equivalent in this particular case, they are
not so in general --- in particular, had this been a FULL JOIN, they
would definitely not be equivalent. PG's parser treats them as different
variables and therefore sees "ORDER BY t1.name" as unrelated to the value
being distinct'ed on.We might someday try to make the parser smarter about recognizing such
equivalences earlier, but I'm not terribly excited about it.
yes, it would probably not worth the effort.
Thank you for the explanation.
Best regards,
Gabriele Monfardini