Re: Seemingly inconsistent ORDER BY behavior

Richard Hipp <drh(at)sqlite(dot)org> writes:
> There are, of course, many ways to resolve the ambiguity (such as using a
> unique label for the result column, or by saying "t1.m" instead of just "m"
> when you mean the column of the table). But that is not really the point
> here. The question is, how should symbolic names in the ORDER BY clause be
> resolved? Should column names in the source table take precedence over
> result column name, or should it be the other way around?

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.

regards, tom lane