Rui Zhao <[email protected]> 于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
