Unexpected multiple join result involving natural join

(1) By Zhaokun Xiang (silva9988) on 2025-05-30 18:15:34 [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.50.0 2025-05-30 16:22:39 and the latest Check-in [6747183c90].

CREATE TABLE t1 (c0 INT , c1 INT );
CREATE TABLE t2 (c0 INT NOT NULL );


INSERT INTO t1(c1) VALUES (1);



SELECT * FROM  t2 RIGHT JOIN  ( SELECT 1) as subQuery1 ON TRUE NATURAL LEFT JOIN t1;
-- NULL, 1, 1.  Wrong Result.
-- Expected Result: NULL, 1, NULL

(2) By anonymous on 2025-05-30 18:33:01 in reply to 1 [link] [source]

Hmm, that's interesting. You can add as many values into t1 like this and they'll all come up, until you add a value in t2. I assume this should show nothing.

.mode column
INSERT INTO t1(c1) VALUES (2), (3), (4);
SELECT * FROM  t2 RIGHT JOIN  ( SELECT 1) as subQuery1 ON TRUE NATURAL LEFT JOIN t1;

c0  1  c1
--  -  --
    1  1
    1  2
    1  3
    1  4

INSERT INTO t2(c0) VALUES(1);
SELECT * FROM  t2 RIGHT JOIN  ( SELECT 1) as subQuery1 ON TRUE NATURAL LEFT JOIN t1;

c0  1  c1
--  -  --
1   1  2

(3) By anonymous on 2025-05-30 18:50:57 in reply to 2 [link] [source]

Actually correct that, it should show up as:


c0  1  c1
--  -  --
1   1

instead of the 2. I had inserted another 2 into t1 before.

(4) By Richard Hipp (drh) on 2025-05-30 18:56:58 in reply to 1 [link] [source]

Preliminary analysis: I think the problem is the NOT NULL constraint on column t2.c0. If you omit that NOT NULL, then the correct answer comes out. Perhaps SQLite is assuming that t2.c0 is never NULL because of the NOT NULL constraint, even if t2 is the left-hand table of a RIGHT JOIN. Looking into that possibility now...

(5) By Richard Hipp (drh) on 2025-05-30 20:01:19 in reply to 1 [link] [source]

As predicted, this problem arises from an optimization that attempts to omit checks for NULL on columns that are marked NOT NULL. That optimization should not apply if the column in question is coming from an outer join. However, I missed a case: when the join constraint is coming from a NATURAL JOIN or from a USING clause and the left side of the JOIN is a RIGHT JOIN, I was not setting the flag that indicates the the column can be NULL even if it is marked as NOT NULL. Hence the optimizer was omitting the NULL check and causing the incorrect result.

The problem should now be fixed on trunk.

(6) By Zhaokun Xiang (silva9988) on 2025-05-31 01:38:02 in reply to 5 [link] [source]

Thanks for your analysis and fixing!