Prove a NOT IN's left-hand expressions non-nullable from quals

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Prove a NOT IN's left-hand expressions non-nullable from quals
Date: 2026-06-19 01:13:25
Message-ID: CAMbWs4_TNUs1jn7q0J-=Esz7ziiFdjDAtW4x2u6tv6H5hhmhDA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

383eb21eb teaches the planner to convert "x NOT IN (SELECT ...)" to an
anti-join when both sides of the comparison are known to be non-null.
On the outer side we currently get that only from NOT NULL constraints
and the outer-join-aware-Var infrastructure.

That misses a case: the left-hand column has no NOT NULL constraint,
but a qual forces it non-null anyway, as in "x IS NOT NULL AND x NOT
IN (...)" or "x > 0 AND x NOT IN (...)". We leave those as SubPlan
filters today, even though x clearly can't be NULL where the NOT IN is
evaluated.

The attached patch proves the left-hand Var non-null from such a qual.
pull_up_sublinks_jointree_recurse collects the quals at or below the
NOT IN's jointree node (only those on rels not below the nullable side
of an outer join, so they really do filter the rows) and hands them to
convert_ANY_sublink_to_join, which checks them with
find_nonnullable_vars.

Quals above the NOT IN's node could help in some cases too, but that's
a separate extension and I've left it as a follow-up. See the details
in the draft commit message.

Thoughts?

- Richard

Attachment Content-Type Size
v1-0001-Prove-a-NOT-IN-s-left-hand-expressions-non-nullab.patch application/octet-stream 34.5 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Chao Li 2026-06-19 01:35:34 Re: Fix \crosstabview to honor \pset display_true/display_false
Previous Message Henson Choi 2026-06-19 00:43:10 Re: Row pattern recognition