On Wed, Aug 14, 2013 at 2:56 PM, Tom Lane <tgl@xxxxxxxxxxxxx> 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";
------------------------
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@xxxxxxxxxx
