/ View Ticket
Ticket UUID: d96eba87698a428c1ddd0790ea04382c8c4252df
Title: Incorrect results for OR query where both OR terms are different indexed expressions
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-12-14 11:17:01
Version Found In: 3.26
User Comments:
dan added on 2018-12-14 07:53:15:

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.

drh added on 2018-12-14 11:00:25:

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.