SQLite

Consider the following SQL:

CREATE TABLE t1(m CHAR(2));
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 binary;

The identifier "m" used in the ORDER BY clauses might refer to either the input column "t1.m" or to the second column of output. The SQL standards are ambiguous on which should be used. PostgreSQL uses the output column for unadorned identifiers, such as found in query (1) and it uses the input column when m is part of an expression, such as query (2). Hence, PostgreSQL answers queries (1) and (2) with rows that in opposite orders from one another. MS-SQL 2008 works like PosgreSQL. Oracle appears to use the output column name in both cases.

SQLite should be changed to agree with PostgreSQL.

Add to the test case above:

SELECT '3', substr(m,2) AS m FROM t1 ORDER BY lower(m);

PostgreSQL binds ORDER BY terms to output columns if the term is an ordinal or a simple identifier that matches the name of an output column. If if the ORDER BY term is an expression, it matches against input columns instead. PostgreSQL considers "m COLLATE binary" to be an expression. But for compatibility with SQL92 and historical compatibility with older versions of SQLite, we will break with PostgreSQL and bind ORDER BY terms of the form "m COLLATE binary" to output columns. However, "lower(m)" is clearly an expression, so both PostgreSQL and SQLite will resolve the "m" variable there to match the input column, t1.m.