SQLite

View Ticket
Login
Ticket Hash: 7f39060a24b47353b9343db9c214e3727f8c698b
Title: LEFT JOIN malfunctions with partial ISNULL index
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-11-30 19:29:44
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-11-30 17:51:24:

Consider the following test case:

CREATE TABLE t0(c0);
CREATE TABLE t1(c0);
CREATE INDEX i0 ON t0(1) WHERE c0 ISNULL;
INSERT INTO t0(c0) VALUES (1);
INSERT INTO t1(c0) VALUES (1);
SELECT * FROM t1 LEFT JOIN t0 WHERE t0.c0 ISNULL; -- unexpected: row is fetched

Unexpectedly, a row is fetched. It is not fetched when the index is omitted and the following statement indicates that t0.c0 ISNULL should evaluate to false:

SELECT t0.c0 ISNULL FROM t1 LEFT JOIN t0; -- 0