RIGHT JOIN to "table valued function" broken from 3.40

(1) By Code Hz (codehz) on 2023-02-26 17:02:47 [source]

TLDR, this code snippet shows different results between 3.39 and 3.40

select a.key, b.value from json_each('{"a": 1, "b": 2}') a right join json_each('["a", "c"]') b on a.key = b.value;

3.39 (and 3.39.5)

a|a
|c

3.40 (and 3.41.0)

|a
|c

I tried looking in the changelog for information, but couldn't find any changes related to this issue.

Background: I'm trying to implement a test if all given keys are contained in a json object.
It worked in 3.39, but no longer works in 3.40

The whole code is list below:

CREATE TABLE IF NOT EXISTS b (
  id INTEGER PRIMARY KEY,
  value JSON NOT NULL
);
INSERT INTO "b" (value) VALUES ('{"b": 1, "d": 2}');
SELECT json_object('id', "b"."id", 'value', "b"."value") AS value FROM "b" WHERE NOT EXISTS (SELECT 1 FROM json_each("b"."value") L RIGHT OUTER JOIN json_each('["b"]') R ON L.key = R.value WHERE L.key IS NULL);
-- it should print "{"id":1,"value":"{\"b\":1,\"d\":2}"

(2) By Richard Hipp (drh) on 2023-02-26 19:42:05 in reply to 1 [link] [source]

Thank you for the bug report. The following script clearly shows the malfunction. All four queries in the following script should get the same answer, but the last one turns out different:

.echo on
.mode qbox
CREATE TABLE a(key TEXT);
INSERT INTO a(key) SELECT key FROM json_each('{"a": 1, "b": 2}');
CREATE TABLE b(value TEXT);
INSERT INTO b(value) SELECT value FROM json_each('["a", "c"]');
SELECT a.key, b.value FROM a RIGHT JOIN b ON a.key = b.value;
SELECT a.key, b.value FROM b LEFT JOIN a ON a.key = b.value;
SELECT a.key, b.value 
  FROM json_each('["a", "c"]') AS b
       LEFT JOIN
       json_each('{"a": 1, "b": 2}') AS a ON a.key = b.value;
SELECT a.key, b.value 
  FROM json_each('{"a": 1, "b": 2}') AS a
       RIGHT JOIN
       json_each('["a", "c"]') AS b ON a.key = b.value

This is my bisect: https://sqlite.org/src/timeline?bid=y14e166f40d-n89c459e766-n0aa6dee7f7-y01de7ec44f-yc72756b0f2-y8d8c124349-naa367a3c97-nf25cf63471-n3283bbd12a-n38aaf26e08-n501609eddf

So it appears that the problem arose due to an attempt to fix an earlier performance concern reported in forum thread 96b9e5709cf47cda.

(3) By Richard Hipp (drh) on 2023-02-26 21:02:27 in reply to 2 [link] [source]

A simpler test case:

CREATE TABLE a(key TEXT);
INSERT INTO a(key) VALUES('a'),('b');
SELECT a.key, b.value
  FROM a RIGHT JOIN json_each('["a","c"]') AS b ON a.key=b.value;

The fix is at check-in 46639f682975dac6.

ANALYSIS

A table-valued function in SQLite is really a virtual table with the arguments serving as equality constraints against hidden columns of the virtual table. The problem was that in a RIGHT JOIN, these equality constraints were being applied to the WHERE clause instead of to the ON clause where they belong.

(4) By Code Hz (codehz) on 2023-02-27 01:17:10 in reply to 3 [link] [source]

It works as expected after this commit.

Thanks