"" <kbrannen@pwhome.com> writes:
> 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
seethat "upper(name)" isn't there, so it should pull that expression apart, find "name" and see that in the output list
beforeit starts searching the input list. After all, that's what happens when it wants to search the input list, it's
tornthe expression apart to find a column name. So why did it not look for "name" in the output list but was willing to
dothat for the input list. I guess that was really the heart of my question. :)
The short answer here is that the SQL committee changed their minds
between SQL92 and later editions of the standard, and PG is attempting
to split the difference and be reasonably compliant with both versions.
SQL92 allowed only output column names or numbers (no expressions) in
ORDER BY. Later editions essentially dropped that altogether, and
say that ORDER BY items are expressions over the set of available
table columns.
The column-number case is not so hard to continue supporting, because
"ORDER BY 1" is pretty pointless if you think that "1" means a literal
constant 1 (and anyway ordering by a constant is disallowed in the
later versions of the spec). But the column name case is problematic.
We can't insist on uniquely identifiable names, since plain old
"SELECT x FROM ... ORDER BY x" would break that --- x is then visible
as both an input and output name. Worse, the user might do weird stuff
like "SELECT x AS y" from a table that also contains a column y.
We ended up with the rule of checking first for an output name (thus
satisfying the SQL92 spec exactly) and failing that, or if the item
is an expression, interpreting names as input names only. That meets
the SQL99 rules as long as you don't do stuff like "SELECT x AS y ...
ORDER BY y" and expect "y" to be resolved as some other column name.
What it does *not* do is allow you to treat an output column name as
something you can re-use in an expression. If you want that, use a
sub-select, viz
SELECT * FROM (SELECT x+y AS z, q FROM ...) ss ORDER BY z+q
regards, tom lane