SQLite

  CREATE TABLE t1(a UNIQUE, b);
  INSERT INTO t1 VALUES(NULL, 2);
  INSERT INTO t1 VALUES(NULL, 1);
  SELECT * FROM t1 ORDER BY a, b;

SQLite currently assumes that since the values in column "a" are "UNIQUE", scanning the index on column a will deliver rows in an order compatible with "ORDER BY a, b".

However, since UNIQUE indexes allow duplicate NULL values, this assumption only holds true if there is a NOT NULL constraint on the UNIQUE column.

dan added on 2012-04-20 14:58:22:

SQLite version 3.7.12 2012-04-20 12:02:32
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE t1(a UNIQUE, b);                                                                                         
sqlite> INSERT INTO t1 VALUES(NULL, 2);
sqlite> INSERT INTO t1 VALUES(NULL, 1);
sqlite> SELECT * FROM t1 ORDER BY a, b;
|2
|1

dan added on 2012-04-20 17:41:40:
Introduced here: [e53403b6].

dan added on 2012-04-20 17:43:46:
Fixed here: [9870e4c4fe]