|
2018-12-14
| ||
| 11:17 | • Fixed ticket [d96eba87]: Incorrect results for OR query where both OR terms are different indexed expressions plus 3 other changes (artifact: 8279f197 user: drh) | |
| 11:16 | Fix a problem with bytecode generation when a query involves two or more indexes on expressions connected by OR. Ticket [d96eba87698a428c1ddd0790ea04] (check-in: 7e4ed8b5 user: drh tags: trunk) | |
| 11:00 | • Ticket [d96eba87] Incorrect results for OR query where both OR terms are different indexed expressions status still Open with 7 other changes (artifact: 5f232414 user: drh) | |
| 08:40 | Possible fix for [d96eba87]. (Closed-Leaf check-in: 89a25abf user: dan tags: tkt-d96eba87) | |
| 07:53 | • New ticket [d96eba87] Incorrect results for OR query where both OR terms are different indexed expressions. (artifact: 180077be user: dan) | |
| 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:
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. | ||||