Unexpected multiple join result involving full join and inner join

(1.2) By Zhaokun Xiang (silva9988) on 2025-05-28 13:03:40 edited from 1.1 [source]

Dear SQLite Developers,

I perform some join, but I meet a wrong result. Please see the below cases. The multiple join contains a full join and an inner join, both with a true join condition. It should be a no-empty result. I can reproduce it on the version SQLite version 3.50.0 2025-05-26 23:20:23 Check-in [fe670ddda9].

CREATE TABLE vt0(c0 INT);
CREATE TABLE vt2(c0 INT);
CREATE TABLE t1(c0 INT);


CREATE UNIQUE INDEX IF NOT EXISTS index1 ON t1((c0 NOT BETWEEN (((c0 AND c0))) AND c0)) WHERE c0 IN ();


INSERT INTO t1 VALUES (1);



SELECT *
FROM vt2 INNER JOIN vt0 ON (((vt2.c0)) IN ()) FULL JOIN  (SELECT 0) as subQuery1 ON TRUE INNER JOIN t1 ON TRUE;
-- empty result, which is wrong
-- expected result: NULL, NULL, 0, 1

(2) By Richard Hipp (drh) on 2025-05-28 17:10:02 in reply to 1.2 [link] [source]

Simplified test case:

CREATE TABLE t1(w INT);
CREATE TABLE t2(x INT);
CREATE TABLE t3(y INT); INSERT INTO t3(y) VALUES(3);
CREATE TABLE t4(z INT); INSERT INTO t4(z) VALUES(4);
CREATE INDEX t4z ON t4(z) WHERE FALSE;
SELECT *
  FROM t1 JOIN t2 ON FALSE
          RIGHT JOIN t3 ON TRUE
          JOIN t4 ON TRUE;

Now fixed on trunk. The fix will appear in the 3.50.0 release. The issue stems from the partial index that has a WHERE clause that always evaluates to FALSE. The fix was to enhance the query planner so that it knows not to use a partial index that has a constant WHERE clause.

This is not a problem that is likely to come up in a real-world application. This is something that would only happen in fuzzer-generated SQL.

(3) By Zhaokun Xiang (silva9988) on 2025-05-29 02:37:52 in reply to 2 [link] [source]

Thanks for your simplification and fixing!