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. |