SQLite

View Ticket
Login
Ticket Hash: 4baa464912129477f3c91259616bb51a1300411e
Title: NULL handling for indexes on expressions
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-10-10 14:18:13
Version Found In: 3.14.2
User Comments:
drh added on 2016-10-10 13:05:03:

The fourth SELECT statement in the example below outputs one row where t1.a is NULL. The first three SELECT statements demonstrate that the fourth SELECT gives an is an incorrect result.

CREATE TABLE t1(a);
INSERT INTO t1 VALUES(NULL),(1);
SELECT '1:', typeof(a), a FROM t1 WHERE a<10;
SELECT '2:', typeof(a), a FROM t1 WHERE a+0<10;
CREATE INDEX t1x1 ON t1(a);
CREATE INDEX t1x2 ON t1(a+0);
SELECT '3:', typeof(a), a FROM t1 WHERE a<10;
SELECT '4:', typeof(a), a FROM t1 WHERE a+0<10;