SQLite

View Ticket
Login
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.