SQLite User Forum

Unexpected multiple join result
Login

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.