Unexpected multiple join result involving full join, inner join and left join

(1.1) By Zhaokun Xiang (silva9988) on 2025-05-30 03:15:58 edited from 1.0 [source]

Dear SQLite Developers,

I perform some joins, but I meet a wrong result. Please see the below cases. I can reproduce it on the version SQLite version 3.50.0 2025-05-29 14:47:35 and the latest Check-in [4b1a38ff6b]

CREATE TABLE t0(c0);
CREATE TABLE t1(c0);
CREATE TABLE t2(c0);

CREATE INDEX i1 ON t1(c0) WHERE c0;

INSERT INTO t2 VALUES ('');
INSERT INTO t1 VALUES ('');
INSERT INTO t0 VALUES (1);

SELECT subQuery1.col_0 FROM t2 FULL JOIN t1 ON t1.c0  LEFT JOIN ( SELECT 0 AS col_0) as subQuery1 ON t1.c0 INNER JOIN t0 ON ((((t1.c0))!=(0)));
-- 0.  wrong result
-- expected result: NULL

(2) By Richard Hipp (drh) on 2025-05-29 16:48:44 in reply to 1.0 [link] [source]

The following is a simplification and clarification of the script that causes the problem. The same script also runs on PostgreSQL and confirms that the correct answer is NULL. SQLite returns 3, due to some issue associated with the partial index. If you omit the index, SQLite also gets the correct answer. The problem goes back to the introduction of support for RIGHT JOIN in version 3.39.0 and has been in every version of SQLite since then.

A fix is still pending.

CREATE TABLE t1(x INT);      INSERT INTO t1(x) VALUES(1);
CREATE TABLE t2(y BOOLEAN);  INSERT INTO t2(y) VALUES(false);
CREATE TABLE t3(z INT);      INSERT INTO t3(z) VALUES(3);
CREATE INDEX t2y ON t2(y) WHERE y;
SELECT z FROM t1 RIGHT JOIN t2 ON y LEFT JOIN t3 ON y;

(3.1) By Richard Hipp (drh) on 2025-05-30 10:04:48 edited from 3.0 in reply to 1.0 [link] [source]

This was the same problem reported by ticket 623eff57e76d45f6 except that it applies to RIGHT JOIN instead of LEFT JOIN. The problem should now be fixed on trunk.

The original ticket was written by Manuel Rigger in 2019. Zhaokun: Are you one of Manuel's students? Or, have you been reading his papers on RDBMS fuzzing?

(4) By Zhaokun Xiang (silva9988) on 2025-05-30 03:12:03 in reply to 3.0 [link] [source]

Thanks for your simplification and fixing! Yeah, I am currently a visiting student of Manuel and working on database testing.