SQLite

View Ticket
Login
2017-06-20
16:15 New ticket [892fc34f] Incorrect query result when a LEFT JOIN subquery is flattened. (artifact: d28d6544 user: drh)
2017-05-25
16:28 Fixed ticket [7fde638e]: Assertion fault on a LEFT JOIN plus 5 other changes (artifact: f6f681e5 user: drh)
00:28
The TK_IF_NULL_ROW expression node must be treated as a variable that references the table Expr.iTable. Proposed fix for ticket [7fde638e94287d2c]. (check-in: b30a364a user: drh tags: branch-3.19)
00:12
Increase the version number to 3.19.2 since ticket [7fde638e94287d2] is going to necessitate another patch release. (check-in: c315727a user: drh tags: branch-3.19)
00:08
The TK_IF_NULL_ROW expression node must be treated as a variable that references the table Expr.iTable. Proposed fix for ticket [7fde638e94287d2c]. (check-in: 77fc2301 user: drh tags: trunk)
2017-05-24
22:39 New ticket [7fde638e] Assertion fault on a LEFT JOIN. (artifact: dcc0914b user: drh)

Ticket Hash: 7fde638e94287d2c948cd9389c8fb1f835f4ed5d
Title: Assertion fault on a LEFT JOIN
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-05-25 16:28:42
Version Found In: 3.19.1
User Comments:
drh added on 2017-05-24 22:39:10:

The query at the end of the following SQL causes an assertion fault in the OP_IfNullRow opcode, which is part of the new LEFT JOIN flattening optimization:

CREATE TABLE t1(a);
INSERT INTO t1 VALUES(1),(2),(3);
CREATE VIEW v2 AS SELECT a, 1 AS b FROM t1;
CREATE TABLE t3(x);
INSERT INTO t3 VALUES(2),(4);

SELECT * FROM t3 LEFT JOIN v2 ON a=x WHERE b=1;

This bug was reported via private email from an SQLite user. The problem appears to be that the TK_IF_NULL_ROW nodes of the expression tree that are inserted as part of the LEFT JOIN query flattening transformation are being identified as constants (assuming that have all constant substructure) and are hence being factored out of the query evaluation loop to a point where the cursor for the LEFT JOIN table is not valid.