SQLite

WHERE terms of the form:

  ... WHERE func1(cols) = ?1 OR func2(cols) = ?2

are being incorrectly transformed to:

  ... WHERE func2(cols) IN (?1, ?2)

For example:

  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES('abc', 'ABC');
  INSERT INTO t1 VALUES('def', 'DEF');

  .print "---- without indexes:"
  SELECT * FROM t1 WHERE upper(a) = 'ABC' OR lower(b) = 'def';

  .print "---- with indexes:"
  CREATE INDEX i1 ON t1( upper(a) );
  CREATE INDEX i2 ON t1( lower(b) );
  SELECT * FROM t1 WHERE upper(a) = 'ABC' OR lower(b) = 'def';

The first query above returns both table rows, but the second only returns the ('def', 'DEF') tuple.

This problem has existed in all versions of SQLite since support for indexes on expressions was first added, with version 3.9.0 on 2015-10-14. This problem is a previously undetected error in the implementation of indexes on expressions.