Zhang Mingli <[email protected]> 于2026年2月4日周三 12:50写道： > > Hi, > > On Feb 3, 2026 at 15:13 +0800, Richard Guo <[email protected]>, wrote: > > > This topic has been discussed several times in the past. Due to the > semantic mismatch regarding NULL handling, NOT IN is not ordinarily > equivalent to an anti-join. However, if we can prove that neither the > outer expressions nor the subquery outputs can yield NULL values, it > should be safe to convert NOT IN to an anti-join. > > > > Thanks for working on this! > I've reviewed the patch and it looks good overall. > > I noticed several minor issues in the test case comments: > > 1. The comment doesn't match the SQL: > > +-- ANTI JOIN: outer side is defined NOT NULL, inner side is forced > nonnullable > +-- by qual clause > +SELECT * FROM not_null_tab > +WHERE id NOT IN ( > + SELECT t1.id > + FROM null_tab t1 > + INNER JOIN null_tab t2 ON t1.id = t2.id > + LEFT JOIN null_tab t3 ON TRUE > +); > > The comment says "forced nonnullable by qual clause", but there's no explicit > IS NOT NULL qual here.
I guess that it means "t1.id = t2.id". This join clause makes t1.id forced non-nullable. -- Thanks, Tender Wang
