Re: Confusing order by error

Thank you Brian and David, I'll use David's reply to refine my question...

--- david(dot)g(dot)johnston(at)gmail(dot)com wrote:

> From: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>

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

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

So, what I get out of those explanations is that I should view it as:

ORDER BY expr

and if "expr" is in the output list (optionally prefixed with a table name), then that is used as is and explains the first example;
but if "expr" is not in the output verbatim -- "upper(name)" in the example -- then it goes to the input list to satisfy what it's ordering by.

I can go with that now that I understand it (and I did not read the docs that way), but I guess I was expecting it to see that "upper(name)" isn't there, so it should pull that expression apart, find "name" and see that in the output list before it starts searching the input list. After all, that's what happens when it wants to search the input list, it's torn the expression apart to find a column name. So why did it not look for "name" in the output list but was willing to do that for the input list. I guess that was really the heart of my question. :)

If it makes it easier, this was my (expected) mental process model, thinking it would work thru the steps and stop when something is found:
1. look for expression in output list: upper(name)
2. look for column names in expression in output list: name
3. look for expression in input list: upper(name)
4. look for column names in expression in input list: name

I expected it do that and stop at #2, but it seems to have no #2 and went to #4. :(

Thanks,
Kevin