|Title:||Incorrect result on a LEFT JOIN when using an index|
|Last Modified:||2018-04-24 14:42:54|
|Version Found In:||3.23.1|
drh added on 2018-04-24 12:48:46:
The query below gets a different result with and without the index. The result without the index is correct.
CREATE TABLE t1(x); INSERT INTO t1 VALUES(1); CREATE TABLE t2(y, z); SELECT ifnull(z, '!!!') FROM t1 LEFT JOIN t2 ON (x=y); CREATE INDEX t2i ON t2(y, ifnull(z, '!!!')); SELECT ifnull(z, '!!!') FROM t1 LEFT JOIN t2 ON (x=y);
This problem appears to have been introduced by check-in [a47efb7c8520a01110ce3] on 2017-04-11, and first released with version 3.19.0. The problem was discovered during internal testing and has not been observed in the wild.