SQLite User Forum

Unexpected anti join result
Login

Unexpected anti join result

(1) By Zhaokun Xiang (silva9988) on 2025-07-13 13:45:38 [source]

Dear SQLite Developers,

I meet a wrong execution result. Please see the below cases. I can reproduce it on the version SQLite version 3.51.0 2025-07-12 18:14:41 and the latest Check-in [18ba07c152]. I would like to express my gratitude to your efforts in bug fixing and detailed analysis!

CREATE TABLE t0(c0);
CREATE VIRTUAL TABLE t1 USING rtree(c0, c1, c2, +c3 BLOB );


INSERT INTO t0(c0) VALUES (NULL);
INSERT INTO t0(c0) VALUES (1);
INSERT INTO t1(c2, c3, c0) VALUES (1, 2, 1);


SELECT * FROM t0 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE ((t1.c3)OR((t0.c0) ISNULL)));
-- Wrong Execution Result: 1
-- Expected Result: Empty Result

(2) By Dan Kennedy (dan) on 2025-07-14 15:44:57 in reply to 1 [link] [source]

Thanks for this one. Hopefully now fixed here:

https://sqlite.org/src/info/3c0afda372

Dan.

(3) By Zhaokun Xiang (silva9988) on 2025-07-15 03:50:24 in reply to 2 [link] [source]

Thanks for your fixing!