Tom Lane <tgl@sss.pgh.pa.us> writes:
>> SELECT a AS b FROM table1 ORDER BY a;
>> I think it should be:
>> SELECT a AS b FROM table1 ORDER BY b;
>
> You are correct that the latter is the SQL-standard version.
In fact, this becomes pretty obvious when you consider the theoretical
ordering of the evaluation of the various parts of the statement. The
ORDER BY is executed *after* the SELECT part, so it only knows about
the column name 'b', and doesn't know what 'a' refers to. Actually,
ORDER BY isn't even part of the SELECT statement, really, but hides an
implicit CURSOR operation taking place during data output. Allowing
the first version to work is of questionable value, since it might
cause errors and confusion when code is modified. Consider the case
where you have "SELECT a AS b, b AS a ...". I've done it! :-)
The order of evaluation is: FROM, WHERE, GROUP BY, HAVING, SELECT,
and, finally, ORDER BY.
-tih
--
The basic difference is this: hackers build things, crackers break them.