SQLite

View Ticket
Login
Ticket Hash: 2a5629202f85f3d1289ca13d4ed0115382815d7b
Title: Malfunctioning interaction between a multi-term ORDER BY clause and UNIQUE index containing NULL values
Status: Closed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2012-04-20 17:43:46
Version Found In: trunk
Description:
Consider:
  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]