SQLite

View Ticket
Login
Plaintext
2021-01-27
19:15 • Fixed ticket [ee51301f]: Incorrect optimization of IN operator. plus 5 other changes (artifact: 8a0867c5 user: drh)
19:15
Fix an issue with IN operator optimization introduced by check-in [4a43430fd23f8835] and described by ticket [ee51301f316c09e9]. (check-in: 9dc7fc9f user: drh tags: trunk)
17:15
Ensure a cursor used by the SeekScan operator does not point to a valid row on the first iteration of the loop. Possible fix for [ee51301f316c09e]. (Closed-Leaf check-in: 390cf60a user: dan tags: fix-2d6e8400)
16:06 • New ticket [ee51301f] Incorrect optimization of IN operator.. (artifact: 2d6e8400 user: drh)
Ticket Hash: ee51301f316c09e9af91aa405dc5092e8416cce6
Title: Incorrect optimization of IN operator.
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Low
Subsystem: Unknown Resolution: Fixed
Last Modified: 2021-01-27 19:15:22
Version Found In: 3.34.1
User Comments:
drh added on 2021-01-27 16:06:27:

The two queries in the following script give different answers:

CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
CREATE UNIQUE INDEX t1y ON t1(y);
INSERT INTO t1 VALUES(111, 'AAA'),(222, 'BBB'),(333, 'CCC');
CREATE TABLE t2(z);
INSERT INTO t2 VALUES('BBB'),('AAA');
ANALYZE sqlite_schema;
INSERT INTO sqlite_stat1 VALUES('t1', 't1y','100 1');
ANALYZE sqlite_schema;

SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y AND t1.x IN (111, 222);
SELECT t1.x FROM t2 CROSS JOIN t1 WHERE t2.z = t1.y AND +t1.x IN (111, 222);

The problem appears to have originated in check-in 4a43430fd23f8835 on 2020-09-30. It was reported by Forum Post 0e5dcaae44