On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik <i...@tandetnik.org> wrote:
> Note that "ORDER BY lower(m)" is not valid SQL-92. The standard only
> allows sorting by columns that appear in the SELECT clause, referenced by
> name or by ordinal. It doesn't allow sorting by arbitrary expressions, nor
> even by columns that appear in the underlying tables but are not selected.
>
> Most DBMS allow sorting (and grouping) by arbitrary expressions, which
> means that the standard is not directly applicable. One has to extrapolate.
>
Over on the postgresql mailing list, Tom Lane has this to say:
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.
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
