View Ticket
23:48 Fixed ticket [80177f0c]: Incorrect result when join condition uses a table-valued function plus 5 other changes (artifact: ca393f73 user: drh)
Fix the query planner so that it takes into account dependencies in the arguments to table-valued functions in subexpressions in the WHERE clause. Fix for ticket [80177f0c226ff54f6dd]. (check-in: 7daa6873 user: drh tags: trunk)
22:26 New ticket [80177f0c] Incorrect result when join condition uses a table-valued function. (artifact: 8e86e0cc user: drh)

Ticket Hash: 80177f0c226ff54f6ddd410fc5db9b04f0d8573d
Title: Incorrect result when join condition uses a table-valued function
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2018-01-26 23:48:05
Version Found In: 3.22.0
User Comments:
drh added on 2018-01-26 22:26:59:

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}');
 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.