SQLite Forum

problem report: incorrect join behavior
That seems to be wrong. The ON condition of a left join should only result in left columns being null, no rows may be removed from a left join.  Even if you did `x left join y on 0` you still get all of `x`.

select * from (select 1) a left outer join (select 2) b on false;

So I think the optimization is too aggressive.