SQLite: View Ticket

2018-04-24
17:34		• Fixed ticket [4ba5abf6]: Index on expression leads to an incorrect LEFT JOIN plus 5 other changes (artifact: f7d169fc user: drh)
17:34		Do not attempt to use terms from the WHERE clause to drive indexes on the right table of a LEFT JOIN. Fix for ticket [4ba5abf65c5b0f9a96a7a40cd18b] (check-in: aeb694e3 user: drh tags: trunk)
14:30		• New ticket [4ba5abf6] Index on expression leads to an incorrect LEFT JOIN. (artifact: e5a141f8 user: drh)

Ticket Hash:	4ba5abf65c5b0f9a96a7a40cd18b854478d40ced
Title:	Index on expression leads to an incorrect LEFT JOIN
Status:	Fixed	Type:	Code_Defect
Severity:	Severe	Priority:	Immediate
Subsystem:	Unknown	Resolution:	Fixed
Last Modified:	2018-04-24 17:34:13
Version Found In:	3.23.1
User Comments:
drh added on 2018-04-24 14:30:17: The following SQL demonstrates how an index on an expression that can be non-NULL even if all its arguments are NULL can lead to an incorrect answer in a LEFT JOIN. CREATE TABLE t1(x); CREATE TABLE t2(y, z); INSERT INTO t1 VALUES('key'); INSERT INTO t2 VALUES('key', -1); SELECT count() FROM t1 LEFT JOIN t2 ON (x=y) WHERE ifnull(z, 0) >=0; CREATE INDEX t2i ON t2(y, ifnull(z, 0)); SELECT count() FROM t1 LEFT JOIN t2 ON (x=y) WHERE ifnull(z, 0) >=0; This problem has been in the code ever since support for indexes on expression was added by check-in [2131a5ca53f0e9b0b98a9dd9a20e495] on 2015-09-04. The problem first appeared in the 3.9.0 release. This problem was discovered during internal testing and analysis and has not been observed in the wild.