BUG #14188: "FOR SELECT DISTINCT, ORDER BY expressions must appear IN SELECT list" error and table alias

From: gabrimonfa(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14188: "FOR SELECT DISTINCT, ORDER BY expressions must appear IN SELECT list" error and table alias
Date: 2016-06-14 09:48:46
Message-ID: 20160614094846.5798.5862@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14188
Logged by: Gabriele Monfardini
Email address: gabrimonfa(at)gmail(dot)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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message root 2016-06-14 10:34:15 BUG #14189: Corrupted WAL through replication protocol
Previous Message zzia88 2016-06-14 09:07:30 BUG #14187: Function is running correct but not showing output