SQLite

View Ticket
Login
Ticket Hash: 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: (text/x-fossil-wiki)
WHERE terms of the form:

<verbatim>
  ... WHERE func1(cols) = ?1 OR func2(cols) = ?2
</verbatim>

are being incorrectly transformed to:

<verbatim>
  ... WHERE func2(cols) IN (?1, ?2)
</verbatim>

For example:

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

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: (text/x-fossil-wiki)
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.