/ View Ticket
Login
Ticket UUID: 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: (text/x-fossil-wiki)
The SELECT statement at the end of the SQL shown below should return a two-row result, but instead returns
an empty set.

<blockquote><verbatim>
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);
</verbatim></blockquote>

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.