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

gabrimonfa(at)gmail(dot)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