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