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

On Tue, Jun 14, 2016 at 5:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.
>

yes, it would probably not worth the effort.
Thank you for the explanation.
Best regards,

Gabriele Monfardini