SQLite

The SELECT statement at the end of the SQL shown below should return a two-row result, but instead returns an empty set.

CREATE TABLE t1(id, json);
INSERT INTO t1(id,json) VALUES(1,'{"items":[3,5]}');
CREATE TABLE t2(id, json);
INSERT INTO t2(id,json) VALUES(2,'{"value":2}');
INSERT INTO t2(id,json) VALUES(3,'{"value":3}');
INSERT INTO t2(id,json) VALUES(4,'{"value":4}');
INSERT INTO t2(id,json) VALUES(5,'{"value":5}');
INSERT INTO t2(id,json) VALUES(6,'{"value":6}');
SELECT * FROM t2 CROSS JOIN t1
 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
               WHERE Z.value==t2.id);

The problem is that the query planner fails to recognize that the EXISTS expression in the WHERE clause has a dependency on table t1, and so it generates code for the EXISTS expression after the outer loop over t2 and before the inner loop over t1 has been run. This means that the t1.json value for the EXISTS expression is incorrect, and an incorrect answer results.

This problem appears to have been in the code ever since table-valued functions were introduced with version 3.9.0 (2015-10-14).

This problem was reported on the sqlite-users mailing list by Jens Alfke.