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

Hi,

On Fri, 19 Jun 2026 at 06:43, Richard Guo <guofenglinux(at)gmail(dot)com> 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-zOnjVJpYS910aBaiuMij4nFg(at)mail(dot)gmail(dot)com>