/ View Ticket
Login
2012-04-21
00:31
Merge the latest trunk changes into the WinRT branch (fixes for tickets [2a5629202f] and [385a5b56b9]). (check-in: 25478dcf user: mistachkin tags: winrt)
2012-04-20
17:43 Ticket [2a562920] Malfunctioning interaction between a multi-term ORDER BY clause and UNIQUE index containing NULL values status still Closed with 1 other change (artifact: 1d908f1b user: dan)
17:42 Closed ticket [2a562920]. (artifact: 4323f4f0 user: dan)
17:41 Ticket [2a562920]: 1 change (artifact: be0c3a72 user: dan)
15:24
Fix for [2a5629202f]. When considering whether or not a UNIQUE index may be used to optimize an ORDER BY clause, do not assume that all index entries are distinct unless there is some reason to believe that the index contains no NULL values. (check-in: 9870e4c4 user: dan tags: trunk)
14:58 Ticket [2a562920] Malfunctioning interaction between a multi-term ORDER BY clause and UNIQUE index containing NULL values status still Open with 1 other change (artifact: d85ca2b4 user: dan)
14:58 Ticket [2a562920]: 1 change (artifact: 50b1f4f6 user: dan)
14:54 Ticket [2a562920]: 3 changes (artifact: ac7b9f66 user: dan)
14:54 New ticket [2a562920]. (artifact: c5983115 user: dan)

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]