Re: Wrong results: NOT IN to anti-join with an upper-level Var in the sub-select's output

Rui Zhao <zhaorui126(at)gmail(dot)com> 于2026年7月31日周五 14:02写道：
>
> Hi,
>
>
> \pset null '<NULL>'
> CREATE TABLE o (b int, a int NOT NULL);
> CREATE TABLE i (x int);
> INSERT INTO o VALUES (NULL, 1);
> INSERT INTO i VALUES (5);
>
> SELECT * FROM o WHERE o.a NOT IN (SELECT o.b FROM i WHERE i.x IS NOT NULL);
> b | a
> --------+---
> <NULL> | 1
> (1 row)
>
> When expr_is_nonnullable() can't prove an output column non-null,
> query_outputs_are_not_nullable() falls back to find_nonnullable_vars() over the
> quals find_subquery_safe_quals() collected. find_nonnullable_vars() only
> reports Vars of the current level, and the multibitmapset it returns identifies
> them by varno and varattno alone, so the upper-level o.b -- varno 1, varattno 1
> in the outer rangetable -- matches i.x, varno 1, varattno 1 in the sub-select's.
> var_is_nonnullable() turns upper-level Vars away; this fallback doesn't:
>
> - if (IsA(expr, Var))
> + if (IsA(expr, Var) && ((Var *) expr)->varlevelsup == 0)
>
> There is nothing else to try for an upper-level Var there anyway, since
> query_outputs_are_not_nullable() only has the sub-Query to work with. Patch
> attached, with a test; the test fails without the one-line change, and make
> check is green with it.

query_outputs_are_not_nullable() forgot to process the upper-level reference.
I think it is rare for the output columns of a subquery to include
columns from the parent query.
So we just add ((Var *) expr)->varlevelsup == 0 here; it is ok for
v19. We can support more kinds of NOT IN pull-up in future versions.

In my opinion, it's better to add comments before "if (IsA(expr, Var)
&& ((Var *) expr)->varlevelsup == 0)". The original comment inside the
if can remain unchanged.

--
Thanks,
Tender Wang