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