Lew <noone@lwsc.ehost-services.com> writes:
> Eric B. Ridge wrote:
>> That explains it. Thanks. Breaks the rule of least surprise, but it is SQL.
> I don't think it does break the rule of least surprise. How would one
> expect the column or the alias to have precedence without knowledge of
> the rule from documentation? The only thing I would expect if I were
> unaware of the rule is that there might be a conflict and that I'd
> have to look up the precedence.
There's actually a good deal more there than meets the eye. Our SELECT
reference page spells it out, if you read the fine print. In particular
it's worth noting this bit in the Compatibility section:
Namespace Available to GROUP BY and ORDER BY
In the SQL-92 standard, an ORDER BY clause can only use output column
names or numbers, while a GROUP BY clause can only use expressions based
on input column names. PostgreSQL extends each of these clauses to allow
the other choice as well (but it uses the standard's interpretation if
there is ambiguity). PostgreSQL also allows both clauses to specify
arbitrary expressions. Note that names appearing in an expression will
always be taken as input-column names, not as output-column names.
SQL:1999 and later use a slightly different definition which is not
entirely upward compatible with SQL-92. In most cases, however,
PostgreSQL will interpret an ORDER BY or GROUP BY expression the same
way SQL:1999 does.
regards, tom lane