|Title:||Assertion fault in self-join with a IN constraint|
|Last Modified:||2019-02-23 00:21:35|
|Version Found In:||3.27.1|
drh added on 2019-02-20 01:39:07:
The query in the following SQL hits an assertion fault:
CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY); INSERT INTO t1 VALUES(1); SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3);
The problem was reported on the SQLite users mailing list by Ignacio Losiggio. Ignacio ran a bisect and found that the problem was introduced by check-in [e130319317e76119], which means that the problem is new for SQLite version 3.27.
drh added on 2019-02-20 12:59:00:
The problem was that the same IN operator was being used twice, once as IN_INDEX_LOOP to drive a loop and later as IN_INDEX_EPH to do a membership test. The first case uses a table-btree and the second case uses an index-btree. The optimization to reuse the RHS of IN operators generated a table-btree for the first case, then tried to reuse that table-btree for the second case, which will not work, and hence the error.
The first fix (now on a closed branch) was to disable the IN-operator RHS reuse optimization for IN_INDEX_LOOP. That worked, but it also reduced the number of cases where the IN-operator RHS reuse optimization would apply, resulting in some slower queries. The second fix (now on trunk) marks the IN operator as TERM_CODED after it is used as an IN_INDEX_LOOP, then preventing it from being reused later for a membership test.
drh added on 2019-02-22 21:35:32:
Reopened because the previous fix caused a new problem:
CREATE TABLE t1(a INTEGER PRIMARY KEY); INSERT INTO t1(a) VALUES(1),(2),(3); CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT); INSERT INTO t2(y) VALUES(2),(3); SELECT * FROM t1, t2 WHERE a=y AND y=3;
The previous fix caused the final SELECT statement in the code above to output a row where y<>3, in clear violation of the WHERE clause.