On Wed, Feb 4, 2026 at 1:49 PM Zhang Mingli <[email protected]> wrote: > 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.
The qual "t1.id = t2.id" here is sufficient to force t1.id (and t2.id) nonnullable, as the operator is strict. It doesn't have to be an explicit IS NOT NULL qual. > 2. Another test case that could use a more precise comment: > > +-- ANTI JOIN: outer side is defined NOT NULL and is not nulled by outer join, > +-- inner side is defined NOT NULL > +SELECT * FROM not_null_tab t1 > +LEFT JOIN not_null_tab t2 > +ON t2.id NOT IN (SELECT id FROM not_null_tab); > > Correct me if I’m wrong. > This is a subtle case - the key point is that the ON clause is evaluated on > actual t2 rows *before* LEFT JOIN's null-padding. > The current comment is technically correct but might be clearer as: > > -- ANTI JOIN: outer side(t2) is defined NOT NULL. > — ON clause is evaluated on actual t2 rows before LEFT JOIN's > -- null-padding, so t2.id is NOT NULL; inner side is also defined NOT NULL Hmm, I'm not sure if this is necessary. I don't think this test case needs to concern itself with explaining standard JOIN/ON semantics. > 3.Also, one suggestion for additional test coverage - the case where the > subquery output comes from the nullable side of an outer join but is forced > non-nullable by qual: > > -- ANTI JOIN: inner side comes from nullable side of outer join > -- but is forced non-nullable by WHERE clause > EXPLAIN (COSTS OFF) > SELECT * FROM not_null_tab > WHERE id NOT IN ( > SELECT t2.id > FROM not_null_tab t1 > LEFT JOIN not_null_tab t2 ON t1.id = t2.id > WHERE t2.id IS NOT NULL > ); > > The existing tests cover t1.id (non-nullable side) with IS NOT NULL, but not > t2.id (nullable side). Right, we can include this one in the test case. - Richard
