| 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 4.47 years ago |
Created: |
2021-01-27 16:06:27 4.47 years ago |
| Version Found In: | 3.34.1 | ||
| User Comments: | ||||
drh added on 2021-01-27 16:06:27:
(text/x-markdown)
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](forum:/forumpost/0e5dcaae44)
| ||||