Hi,
On Fri, 19 Jun 2026 at 06:43, Richard Guo <[email protected]> wrote:
> 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.
>
Thanks for the patch!
I did see one issue in this here's a statement for repro:
CREATE TEMP TABLE outer_t (x int);
CREATE TEMP TABLE inner_t (y int NOT NULL);
INSERT INTO outer_t VALUES (NULL), (1);
INSERT INTO inner_t VALUES (2);
SELECT x
FROM outer_t
WHERE NOT (x = ANY('{}'::int[]))
AND x NOT IN (SELECT y FROM inner_t);
SQL semantics return only 1. With the patch, the planner
produces a hash anti-join and also returns the NULL row: the
first qual admits it, and the anti-join preserves it, whereas the
original NOT IN evaluates to NULL and filters it out.
This helper weakness also affects the existing inner-query
proof path, but the patch creates a new regression surface.
I've posted a likely fix in [1].
Apart from the above, your patch looked good to me.
Regards,
Ayush
[1] PostgreSQL: [PATCH] SAOP nullability analysis below NOT/BooleanTest
<https://www.postgresql.org/message-id/flat/cajtyswv3vqrjmst-gv1nsxeef-zonjvjpys910abaiumij4...@mail.gmail.com>
