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.
>
>
Thanks for the explanation, Tom.

Just to be clear, you intend that a COLLATE clause in the ORDER BY is
treated as an expression, right? So that the two queries in the following
SQL output rows in the opposite order:

------------------------
CREATE TABLE t1(m VARCHAR(4));
INSERT INTO t1 VALUES('az');
INSERT INTO t1 VALUES('by');
INSERT INTO t1 VALUES('cx');
SELECT '1', substr(m,2) AS m FROM t1 ORDER BY m;
SELECT '2', substr(m,2) AS m FROM t1 ORDER BY m COLLATE "POSIX";
------------------------

If that is not correct, please let me know because I am about to change
SQLite to work exactly as PostgreSQL does.

--
D. Richard Hipp
drh(at)sqlite(dot)org