|Title:||Assertion fault on a LEFT JOIN|
|Last Modified:||2017-05-25 16:28:42|
|Version Found In:||3.19.1|
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.