SQLite
View Ticket
Not logged in
Ticket UUID: 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:

<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]