|00:56||Import from trunk the new fix to ticket [df46dfb631f75694] in which all ephemeral tables used as the RHS of an IN operator be index btrees and never table btrees so that they can always be reused. (check-in: 0e64ac12 user: drh tags: branch-3.27)|
|00:21||• Fixed ticket [df46dfb6]: Assertion fault in self-join with a IN constraint plus 3 other changes (artifact: 8e1fc5b8 user: drh)|
|00:21||Check-in [fa792714ae62fa98] is incorrect. Add a test case to refute it and also a fix to make it right. Then add an alternative fix to ticket [df46dfb631f75694] in which all ephemeral tables used as the RHS of an IN operator be index btrees and never table btrees so that they can always be reused. (check-in: d3915230 user: drh tags: trunk)|
|21:35||• Open ticket [df46dfb6]: Assertion fault in self-join with a IN constraint plus 4 other changes (artifact: 0bf6d95b user: drh)|
|21:33||Check-in [fa792714ae62fa980] is not a valid fix for ticket [df46dfb631f75694], as the new test case in this check-in demonstrates. The fix here causes test cases for the [df46dfb631f75694] bug to fail again, so this check-in is on a branch. A new fix is needed for [df46dfb631f75694]. (check-in: 0d456456 user: drh tags: tkt-df46dfb631)|
|12:59||• Fixed ticket [df46dfb6]: Assertion fault in self-join with a IN constraint plus 6 other changes (artifact: 784f2606 user: drh)|
|12:52||When an IN operator drives a query loop, mark it as "CODED" so that it will not be used afterwards for a (pointless) membership test. This is a better fix for ticket [df46dfb631f75694] than the previous fix that is now on a branch as it preserves the full optimization of check-in [e130319317e76119]. (check-in: fa792714 user: drh tags: trunk)|
|03:38||Back off the optimization of check-in [e130319317e76119] slightly so that it only applies to IN operators that are used for membership tests. Proposed fix for ticket [df46dfb631f75694]. (Closed-Leaf check-in: b5f90bfe user: drh tags: tkt-df46dfb631)|
|01:39||• New ticket [df46dfb6] Assertion fault in self-join with a IN constraint. (artifact: 967c921b user: drh)|
|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.