|
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. | ||||