On Wed, Aug 14, 2013 at 2:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Our interpretation is that a bare column name ("ORDER BY foo") is resolved first as an output-column label, or failing that as an input-column name. However, as soon as you embed a name in an expression, it will be treated *only* as an input column name.
The SQL standard is not a lot of help here. In SQL92, the only allowed forms of ORDER BY arguments were an output column name or an output column number. SQL99 and later dropped that definition (acknowledging that they were being incompatible) and substituted some fairly impenetrable verbiage that seems to boil down to allowing input column names that can be within expressions. At least that's how we've chosen to read it. Our current behavior is a compromise that tries to support both editions of the spec.
Asking as a comparative know-nothing who would like to be more informed, is there something wrong with the notion of throwing an error that m in the ORDER BY clause is ambiguous here? As near as I can tell, it really is ambiguous as long as both input or output columns are accepted, so either way is essentially a total guess about what the user wants. It seems to me that throwing an error would be the most intuitive and clearly defined way of handling this case.