postgres.git / summary / log / commit / refs
commit 5b730e81fbdfd36ed45473770676ab3f88c776db
Author: Richard Guo <rguo@postgresql.org>
Date: Mon Nov 02 05:38:32 2026 +0000
First released in: 14.25, 15.20, 16.16, 17.12, 18.7, 19.0
Fix reuse of stale hash tables by duplicated hashed SubPlans
The same SubPlan can appear more than once in a plan tree. For
example, when an equivalence class has a constant member containing a
SubPlan, each derived "member = constant" clause references that
SubPlan; a hash join's hash keys and its hash clauses can both contain
it; and extract_restriction_or_clauses() can copy it into a new
restriction clause while leaving it in the original join clause. Each
reference gets its own SubPlanState, with its own hash tables, but
they all share the subplan's PlanState.
A hashed SubPlan whose subquery references outer query levels must
rebuild its hash tables whenever those parameters change.
ExecHashSubPlan() detected that by checking chgParam of the subplan's
PlanState. But buildSubPlanHash() rescans that PlanState, which
clears its chgParam, so after one SubPlanState had rebuilt its hash
tables, the others no longer saw the change and kept probing hash
tables built for previous parameter values, producing wrong query
results.
To fix, give each SubPlanState its own flag to mark its hash tables as
stale. ExecReScan() sets it, alongside propagating chgParam to the
node's subplans, ExecHashSubPlan() checks it instead of the subplan's
chgParam, and buildSubPlanHash() clears it. The new field fits into
existing padding in SubPlanState, so this does not change the struct's
size or the offsets of its other fields.
Reported-by: Samuel Olaoye <dapsalmy@gmail.com>
Author: Rahul Yadav <rahul@rhyadav.dev>
Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com>
Reviewed-by: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: shihao zhong <zhong950419@gmail.com>
Discussion: https://postgr.es/m/179087936257.78721.1170374394656748858@mail.gmail.com
Backpatch-through: 14
src/backend/executor/execAmi.c | 7 +++++++
src/backend/executor/nodeSubplan.c | 5 +++--
src/include/nodes/execnodes.h | 1 +
src/test/regress/expected/subselect.out | 33 +++++++++++++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 13 +++++++++++++
5 files changed, 57 insertions(+), 2 deletions(-)
diff --git a/src/backend/executor/execAmi.c b/src/backend/executor/execAmi.c
index 1d0e8ad57b4..522500c9f7b 100644
--- a/src/backend/executor/execAmi.c
+++ b/src/backend/executor/execAmi.c
@@ -115,6 +115,13 @@ ExecReScan(PlanState *node)
if (splan->plan->extParam != NULL)
UpdateChangedParamSet(splan, node->chgParam);
+
+ /*
+ * Mark the hash table stale. This is tracked per SubPlanState,
+ * since several of them can share one subplan PlanState.
+ */
+ if (splan->chgParam != NULL)
+ sstate->hashtablestale = true;
}
/* Well. Now set chgParam for child trees. */
if (outerPlanState(node) != NULL)
diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.c
index 8e55dcc159b..ad1ccd9846e 100644
--- a/src/backend/executor/nodeSubplan.c
+++ b/src/backend/executor/nodeSubplan.c
@@ -104,7 +104,6 @@ ExecHashSubPlan(SubPlanState *node,
{
bool result = false;
SubPlan *subplan = node->subplan;
- PlanState *planstate = node->planstate;
TupleTableSlot *slot;
/* Shouldn't have any direct correlation Vars */
@@ -115,7 +114,7 @@ ExecHashSubPlan(SubPlanState *node,
* If first time through or we need to rescan the subplan, build the hash
* table.
*/
- if (node->hashtable == NULL || planstate->chgParam != NULL)
+ if (node->hashtable == NULL || node->hashtablestale)
buildSubPlanHash(node, econtext);
/*
@@ -509,6 +508,7 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
MemoryContextReset(node->hashtablecxt);
node->havehashrows = false;
node->havenullrows = false;
+ node->hashtablestale = false;
nbuckets = clamp_cardinality_to_long(planstate->plan->plan_rows);
if (nbuckets < 1)
@@ -841,6 +841,7 @@ ExecInitSubPlan(SubPlan *subplan, PlanState *parent)
sstate->projRight = NULL;
sstate->hashtable = NULL;
sstate->hashnulls = NULL;
+ sstate->hashtablestale = false;
sstate->hashtablecxt = NULL;
sstate->hashtempcxt = NULL;
sstate->innerecontext = NULL;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index efecc4a8689..25efeb1a8a6 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1012,6 +1012,7 @@ typedef struct SubPlanState
TupleHashTable hashnulls; /* hash table for rows with null(s) */
bool havehashrows; /* true if hashtable is not empty */
bool havenullrows; /* true if hashnulls is not empty */
+ bool hashtablestale; /* hash tables must be rebuilt before use */
MemoryContext hashtablecxt; /* memory context containing hash tables */
MemoryContext hashtempcxt; /* temp memory context for hash tables */
ExprContext *innerecontext; /* econtext for computing inner tuples */
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index fa4eab3fdf3..f668b510f7b 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1281,6 +1281,39 @@ where o.ten = 0;
100
(1 row)
+--
+-- Test rescan of a hashed subplan that is referenced more than once (here
+-- the equivalence class produces two copies of it in the scan filter)
+--
+explain (costs off)
+select i4.f1, (select count(*) from onek
+ where two = four and two = (0 in (select i4.f1))::int)
+from int4_tbl i4 order by i4.f1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: i4.f1
+ -> Seq Scan on int4_tbl i4
+ SubPlan 2
+ -> Aggregate
+ -> Seq Scan on onek
+ Filter: ((two = ((ANY (0 = (hashed SubPlan 1).col1)))::integer) AND (four = ((ANY (0 = (hashed SubPlan 1).col1)))::integer))
+ SubPlan 1
+ -> Result
+(9 rows)
+
+select i4.f1, (select count(*) from onek
+ where two = four and two = (0 in (select i4.f1))::int)
+from int4_tbl i4 order by i4.f1;
+ f1 | count
+-------------+-------
+ -2147483647 | 250
+ -123456 | 250
+ 0 | 250
+ 123456 | 250
+ 2147483647 | 250
+(5 rows)
+
--
-- Test rescan of a hashed SetOp node
--
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 222c7f0bb14..15aae03900e 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -669,6 +669,19 @@ select sum(ss.tst::int) from
from onek i where i.unique1 = o.unique1 ) ss
where o.ten = 0;
+--
+-- Test rescan of a hashed subplan that is referenced more than once (here
+-- the equivalence class produces two copies of it in the scan filter)
+--
+explain (costs off)
+select i4.f1, (select count(*) from onek
+ where two = four and two = (0 in (select i4.f1))::int)
+from int4_tbl i4 order by i4.f1;
+
+select i4.f1, (select count(*) from onek
+ where two = four and two = (0 in (select i4.f1))::int)
+from int4_tbl i4 order by i4.f1;
+
--
-- Test rescan of a hashed SetOp node
--
[parent: f10500777b2a]