/ View Ticket
Login
2019-02-23
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)
2019-02-22
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)
2019-02-20
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)

Ticket UUID: df46dfb631f75694fbb97033b6949c3f0f778f0f
Title: Assertion fault in self-join with a IN constraint
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-02-23 00:21:35
Version Found In: 3.27.1
User Comments:
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.