SQLite

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.

<blockquote><verbatim>
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;
</verbatim></blockquote>

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.

Index on expression leads to an incorrect LEFT JOIN