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

Hi,

On Tue, 28 Jul 2026 at 12:57, Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com>
wrote:

> 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);
>
> Apart from the above, your patch looked good to me.
>

I tried the patch on current master after Tom's SAOP fix. I may be
missing something, but I think there could be an issue when a NOT IN
left-hand expression contains an upper-level Var.

For example:

CREATE TEMP TABLE outer_t (a int);
CREATE TEMP TABLE local_t (a int);
CREATE TEMP TABLE inner_t (x int NOT NULL, y int NOT NULL);

INSERT INTO outer_t VALUES (NULL), (10);
INSERT INTO local_t VALUES (1);
INSERT INTO inner_t VALUES (1, 999);

SELECT o.a,
ARRAY(SELECT l.a
FROM local_t l
WHERE l.a IS NOT NULL
AND (l.a, o.a) NOT IN
(SELECT i.x, i.y FROM inner_t i)) AS vals
FROM outer_t o
ORDER BY o.a NULLS FIRST;

With the patch I get {1} for both outer rows, while without the
anti-join conversion the NULL row produces {}.

It looks like l.a and the upper-level o.a can have the same
varno/varattno, despite having different varlevelsup values. Could the
mbms_is_member() fallback therefore mistake the proof from
l.a IS NOT NULL as applying to o.a?

Would it make sense to restrict that fallback to level-zero Vars, for
example:

if (nonnullable_quals != NIL && IsA(expr, Var) &&
((Var *) expr)->varlevelsup == 0)

With that additional check, I get the expected result.

Regards,
Ayush