Consider:
<verbatim>
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;
</verbatim>
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.
<hr><i>dan added on 2012-04-20 14:58:22:</i><br>
<verbatim>
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
</verbatim>
<hr><i>dan added on 2012-04-20 17:41:40:</i><br>
Introduced here: [e53403b6].
<hr><i>dan added on 2012-04-20 17:43:46:</i><br>
Fixed here: [9870e4c4fe]
|