Re: Confusing order by error

On Friday, March 31, 2017, <kbrannen(at)pwhome(dot)com> wrote:

> 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.html#SQL-ORDERBY I just don't see anything
> that explains the behavior.
>
> 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.