/ View Ticket
Login
Ticket UUID: 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.