Hi,
383eb21eb converts a NOT IN to an anti-join once it can prove both the outer
expressions and the sub-select's output columns non-null. The second proof goes
wrong when an output column is an upper-level Var, and the query then keeps a
row it should have dropped. Column order matters here -- o.b and i.x have to
land on the same attnum:
\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)
The sub-select returns one row and its value is NULL, so the NOT IN is NULL and
that row should not come back:
SELECT o.a, o.b,
(SELECT count(*) FROM i WHERE i.x IS NOT NULL) AS subquery_rows,
(o.a NOT IN (SELECT o.b FROM i WHERE i.x IS NOT NULL)) AS
not_in_value
FROM o;
a | b | subquery_rows | not_in_value
---+--------+---------------+--------------
1 | <NULL> | 1 | <NULL>
(1 row)
and writing the same predicate so that it can't be pulled up agrees:
SELECT * FROM o
WHERE (o.a NOT IN (SELECT o.b FROM i WHERE i.x IS NOT NULL)) OR false;
b | a
---+---
(0 rows)
EXPLAIN (COSTS OFF) on the first query gets
Nested Loop Anti Join
Join Filter: (o.a = o.b)
-> Seq Scan on o
-> Materialize
-> Seq Scan on i
Filter: (x IS NOT NULL)
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.
383eb21eb is in 19beta1, so this wants fixing on that branch too.
The same weakness has a second instance in the follow-up patch on [1], which
adds an outer-side proof with its own find_nonnullable_vars() lookup; Ayush
reported that one there [2]. reduce_outer_joins() also calls
find_nonnullable_vars(), but it only compares two such sets, both built at the
same level, so it isn't affected -- those two are the places that match a single
Var against the set.
[1]
https://postgr.es/m/CAMbWs4_TNUs1jn7q0J-%3DEsz7ziiFdjDAtW4x2u6tv6H5hhmhDA%40mail.gmail.com
[2]
https://postgr.es/m/CAJTYsWWH6Pm2xiq%3DOM3vnBEBz5%3DJp93G5_VUSKKGdkXinbXzZw%40mail.gmail.com
Thanks,
Rui
From 1f22b4aa9dfa0fa930eb170b9459bf8e0d6f77af Mon Sep 17 00:00:00 2001
From: Rui Zhao <[email protected]>
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
