SQLite
View Ticket
Not logged in
Ticket UUID: 34a579141b2c5acbced77742b1bcbb7c489dab8e
Title: Incorrect results with OR terms in the ON clause of a LEFT JOIN
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2016-10-26 16:05:55
Version Found In: trunk
User Comments:
dan added on 2016-10-26 15:39:38:

With the current trunk:

SQLite version 3.16.0 2016-10-26 13:58:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE y1(a, b);
sqlite> CREATE TABLE y2(x, y);
sqlite> INSERT INTO y1 VALUES(1, 1);
sqlite> INSERT INTO y2 VALUES(3, 3);
sqlite> SELECT * FROM y1 LEFT JOIN y2 ON ((x=1 AND y=b) OR (x=2 AND y=b));
1|1||
sqlite> CREATE INDEX y2xy ON y2(x, y);
sqlite> SELECT * FROM y1 LEFT JOIN y2 ON ((x=1 AND y=b) OR (x=2 AND y=b));
1|1|3|3

The first result (without the index) is correct.


drh added on 2016-10-26 16:05:24:

This problem was introduced by the Next Generation Query Planner cutover at check-in [0fe31f60cadc5fe5] on 2013-06-26 and first appeared in version 3.8.0 - over three years ago.