Unexpected multiple join result
(1) By Zhaokun Xiang (silva9988) on 2025-06-16 07:31:55 [source]
Dear SQLite Developers,
I meet a wrong result. Please see the below cases. I can reproduce it on the version SQLite version SQLite version 3.51.0 2025-06-14 18:02:12 and the latest Check-in [eda518028f]. I would like to express my gratitude to your efforts in bug fixing and detained analysis!
CREATE TABLE t0(c0, c1, c2);
CREATE TABLE t1(c0);
CREATE TABLE t2 (c0 REAL, PRIMARY KEY (c0));
CREATE TEMP VIEW IF NOT EXISTS v0(c0) AS SELECT CAST(t1.c0 AS NUMERIC) AS col_0 FROM t1 NATURAL LEFT JOIN t2;
INSERT INTO t1(c0) VALUES (0.6);
INSERT INTO t2(c0) VALUES (NULL);
SELECT v0.c0,t1.c0 FROM t0 NATURAL CROSS JOIN v0 NATURAL FULL JOIN t1 INNER JOIN t2 on (((((t2.c0)IS(v0.c0)))));
-- Wrong Execution Result: Empty result
-- Expected Result: NULL, 0.6
(2) By Richard Hipp (drh) on 2025-06-16 17:38:20 in reply to 1 [link] [source]
This problem goes back to the introduction of RIGHT JOIN in version 3.39.0 (2022-06-25). It is likely resolved by check-in 2025-06-16T17:36Z.