I'm hoping someone can give us a little help understanding an error in the ORDER BY clause, because when I read https://www.postgresql.org/docs/9.5/static/sql-select. I just don't see anything that explains the behavior.html#SQL-ORDERBY
SELECT t1.pk, t1.name, t1.ref, CONCAT( t2.id , ':', t2.name ) AS ref_display
FROM test_table as t1
LEFT JOIN test_table as t2 ON t1.ref = t2.pk
ORDER BY name;
References the output column due to inherent preference.
SELECT t1.pk, t1.name, t1.ref, CONCAT( t2.id , ':', t2.name ) AS ref_display
FROM test_table as t1
LEFT JOIN test_table as t2 ON t1.ref = t2.pk
ORDER BY UPPER(name);
ERROR: column reference "name" is ambiguous
LINE 4: ORDER BY UPPER(name);
^
Eh? The parser (or whatever phase) understood "ORDER BY name" in the first query, so why did that UPPER() string function make a difference in the second query?
This is an _expression_ so names cannot be output columns. Attempts to pick an input column and finds two candidates and so emits the error.
Each _expression_ can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary _expression_ formed from input-column values.
David J.
