From 1f22b4aa9dfa0fa930eb170b9459bf8e0d6f77af Mon Sep 17 00:00:00 2001 From: Rui Zhao Date: Fri, 31 Jul 2026 11:10:44 +0800 Subject: Don't prove a sub-select's upper-level output Var non-nullable from a qual query_outputs_are_not_nullable() falls back to find_nonnullable_vars() over the sub-select's safe quals when expr_is_nonnullable() cannot prove an output column non-null. find_nonnullable_vars() only reports Vars of the current query level, and the multibitmapset it returns identifies them by varno and varattno alone, so an output column that is an upper-level Var gets matched against a local Var of an unrelated relation that happens to have the same varno and varattno. The sub-select is then wrongly taken to produce no NULLs, a NOT IN over it is converted to an anti-join, and rows the NOT IN should have discarded come back: 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 ---+--- | 1 (1 row) The sub-select returns a single NULL row, so the NOT IN is NULL and the row should not be returned; spelling the same predicate so that it cannot be pulled up gives no rows. Restrict the qual-based proof to Vars of the sub-select's own level. expr_is_nonnullable() already declines upper-level Vars, and we have no access to the outer query here, so there is nothing else to try for them. Introduced by 383eb21eb. --- src/backend/optimizer/util/clauses.c | 19 ++++++++++++------ src/test/regress/expected/subselect.out | 26 +++++++++++++++++++++++++ src/test/regress/sql/subselect.sql | 14 +++++++++++++ 3 files changed, 53 insertions(+), 6 deletions(-) diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 7d7f2f9664..219e8651a3 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -2163,16 +2163,23 @@ query_outputs_are_not_nullable(Query *query) if (expr_is_nonnullable(&subroot, expr, NOTNULL_SOURCE_CATALOG)) continue; - if (IsA(expr, Var)) + if (IsA(expr, Var) && ((Var *) expr)->varlevelsup == 0) { Var *var = (Var *) expr; /* - * For a plain Var, even if that didn't work, we can conclude that - * the Var is not nullable if find_nonnullable_vars can find a - * "var IS NOT NULL" or similarly strict condition among the quals - * on non-outerjoined-rels. Compute the list of Vars having such - * quals if we didn't already. + * For a plain Var of this query level, even if that didn't work, + * we can conclude that the Var is not nullable if + * find_nonnullable_vars can find a "var IS NOT NULL" or similarly + * strict condition among the quals on non-outerjoined-rels. + * Compute the list of Vars having such quals if we didn't + * already. + * + * Upper-level Vars must be excluded: find_nonnullable_vars only + * reports Vars of this level, and identifies them by varno and + * varattno alone, so an upper-level Var would be matched against + * a local Var of an unrelated relation that happens to have the + * same varno and varattno. */ if (!computed_nonnullable_vars) { diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index e7ff719108..fbb7962394 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -3818,6 +3818,32 @@ ON t2.id NOT IN (SELECT id FROM not_null_tab); -> Seq Scan on not_null_tab (8 rows) +-- No ANTI JOIN: the sub-select's output is an upper-level Var, which a qual +-- on a local Var must not prove non-nullable. The column order matters here: +-- corr_outer.b and corr_inner.x must have the same attnum for the two to be +-- confusable. +CREATE TEMP TABLE corr_outer (b int, a int NOT NULL); +CREATE TEMP TABLE corr_inner (x int); +INSERT INTO corr_outer VALUES (NULL, 1); +INSERT INTO corr_inner VALUES (5); +EXPLAIN (COSTS OFF) +SELECT * FROM corr_outer +WHERE a NOT IN (SELECT corr_outer.b FROM corr_inner WHERE corr_inner.x IS NOT NULL); + QUERY PLAN +-------------------------------------------------- + Seq Scan on corr_outer + Filter: (NOT (ANY (a = (SubPlan any_1).col1))) + SubPlan any_1 + -> Seq Scan on corr_inner + Filter: (x IS NOT NULL) +(5 rows) + +SELECT * FROM corr_outer +WHERE a NOT IN (SELECT corr_outer.b FROM corr_inner WHERE corr_inner.x IS NOT NULL); + b | a +---+--- +(0 rows) + -- ANTI JOIN: both sides are defined NOT NULL EXPLAIN (COSTS OFF) SELECT * FROM not_null_tab diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 76bce5cdba..32b1d39f85 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -1671,6 +1671,20 @@ SELECT * FROM not_null_tab t1 LEFT JOIN not_null_tab t2 ON t2.id NOT IN (SELECT id FROM not_null_tab); +-- No ANTI JOIN: the sub-select's output is an upper-level Var, which a qual +-- on a local Var must not prove non-nullable. The column order matters here: +-- corr_outer.b and corr_inner.x must have the same attnum for the two to be +-- confusable. +CREATE TEMP TABLE corr_outer (b int, a int NOT NULL); +CREATE TEMP TABLE corr_inner (x int); +INSERT INTO corr_outer VALUES (NULL, 1); +INSERT INTO corr_inner VALUES (5); +EXPLAIN (COSTS OFF) +SELECT * FROM corr_outer +WHERE a NOT IN (SELECT corr_outer.b FROM corr_inner WHERE corr_inner.x IS NOT NULL); +SELECT * FROM corr_outer +WHERE a NOT IN (SELECT corr_outer.b FROM corr_inner WHERE corr_inner.x IS NOT NULL); + -- ANTI JOIN: both sides are defined NOT NULL EXPLAIN (COSTS OFF) SELECT * FROM not_null_tab -- 2.43.7