Re: Seemingly inconsistent ORDER BY behavior

On Wed, Aug 14, 2013 at 2:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.