"" <kbrannen@xxxxxxxxxx> 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 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. :) 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
