SQLite

The query in the following SQL hits an assertion fault:

<blockquote><verbatim>
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);
</verbatim></blockquote>

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:

<blockquote><verbatim>
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;
</verbatim></blockquote>

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.