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:
(text/x-fossil-wiki)
With the current trunk: <verbatim> 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 </verbatim> The first result (without the index) is correct. drh added on 2016-10-26 16:05:24: (text/x-fossil-wiki) 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. |