SQLite

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.

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.

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.