SQLite

View Ticket
Login
2016-11-03
18:33
If all branches of an OR optimize scan that is the rhs of a LEFT JOIN use the same index, set the index cursor to return NULL values if there are no matches for a row on the lhs. Fix for ticket [34a579141b2c5ac] (check-in: 54eeddec user: drh tags: branch-3.15)
2016-10-26
16:05 Closed ticket [34a57914]: Incorrect results with OR terms in the ON clause of a LEFT JOIN plus 3 other changes (artifact: eecf570b user: dan)
16:05 Ticket [34a57914]: 6 changes (artifact: 0155188b user: drh)
16:05
If all branches of an OR optimize scan that is the rhs of a LEFT JOIN use the same index, set the index cursor to return NULL values if there are no matches for a row on the lhs. Fix for ticket [34a579141b2c5ac] (check-in: ec9dab80 user: dan tags: trunk)
15:39 New ticket [34a57914] Incorrect results with OR terms in the ON clause of a LEFT JOIN. (artifact: 79f8ae17 user: dan)

Ticket Hash: 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.