|Title:||LEFT JOIN fails to fetch row|
|Last Modified:||2019-08-17 17:11:14|
|Version Found In:||3.29.0|
mrigger added on 2019-08-17 14:43:41:
Consider the following test case:
CREATE TABLE t0(c0); CREATE VIEW v0(c0) AS SELECT TYPEOF(1) FROM t0; INSERT INTO t0(c0) VALUES (0), (1); SELECT * FROM t0 LEFT JOIN v0 ON t0.c0 WHERE NOT(v0.c0 = 'a'); -- unexpected: fetches no row
I would expect the last query to be equivalent to the following query:
SELECT * FROM t0 LEFT JOIN v0 ON t0.c0 WHERE v0.c0 != 'a'; -- fetches two rows
While the first query does not fetch any rows, the second one fetches two rows.
drh added on 2019-08-17 17:11:14:
The expression "typeof(1)" appears to be constant, always returning the string 'integer'. However, because it originates from the right-hand side of a LEFT JOIN, it might also sometimes be NULL, and so it is not really constant. The logic that factors out constant expressions (to avoid evaluating them multiple times) failed to detect this and factored out the "typeof(1)" expression. The fix is to disable the constant-expression factoring optimization when evaluating expressions that originate on the right-hand side of a LEFT JOIN.