SQLite

View Ticket
Login
2017-07-21
19:33 Fixed ticket [ce68383b]: Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index plus 5 other changes (artifact: 6637feea user: drh)
2017-07-18
17:13
Avoid creating an automatic index on the RHS of a LEFT JOIN to optimize an IS term. Fix for [ce68383b]. (check-in: d2f0b2e8 user: dan tags: trunk)
16:57 New ticket [ce68383b] Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index. (artifact: 4776331e user: drh)

Ticket Hash: ce68383bf6aba2f8e2b0344d067364e22daf59dc
Title: Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-07-21 19:33:16
Version Found In: 3.19.3
User Comments:
drh added on 2017-07-18 16:57:15:

The same LEFT JOIN query gives different results depending on whether or not automatic indexes are enabled. The code below illustrates:

CREATE TABLE t1(w int);
CREATE TABLE t2(y int);
INSERT INTO t1 VALUES(NULL);
INSERT INTO t2 VALUES(1);
CREATE INDEX t2y ON t2(y);
SELECT * FROM t1 LEFT JOIN t2 WHERE t2.y IS t1.w;
PRAGMA automatic_index=off;
SELECT * FROM t1 LEFT JOIN t2 WHERE t2.y IS t1.w;

Both queries should return no rows. But when automatic indexes are turned on (the default) a single row of two NULLs is returned.

Bisecting shows this problem was introduced by check-in [3428043cd0029445]

This problem was discovered during internal testing and analysis and has never been reported in the wild.