SQLite Forum

problem report: incorrect join behavior
Login
Hi,

In version 3.36 and the latest snapshot, it looks like a LEFT JOIN behaves like a plain JOIN in some circumstances. I have the following minimal case, which returns 1 row instead of the expected 2:

SELECT *, sqlite_version()
FROM (
    SELECT NULL val FROM (SELECT 1)
    UNION ALL
    SELECT 'missing' FROM (SELECT 1)
) a
LEFT JOIN (SELECT 1)
    ON a.val IS NULL

/*
returns
val     1       sqlite_version()
        1       3.37.0
*/