postgres.git / summary / log / commit / refs
commit f5b8f05ee960ae5e534a56b8b7e4164a06db92ff
Author: Richard Guo <rguo@postgresql.org>
Date: Mon Nov 02 05:40:19 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 b6245994f02..fac4a62beec 100644
--- a/src/backend/executor/execAmi.c
+++ b/src/backend/executor/execAmi.c
@@ -116,6 +116,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 left/right trees. */
if (node->lefttree != NULL)
diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.c
index 4d51be45784..b25e48b6b0b 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);
/*
@@ -512,6 +511,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)
@@ -839,6 +839,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 1531be2101e..5117cdb2ab9 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -952,6 +952,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 3cd0d830e2a..c6cdeaeceab 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1125,6 +1125,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 = ((hashed SubPlan 1))::integer) AND (four = ((hashed SubPlan 1))::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 SetOp node
--
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index f5c7e05b137..50dc138a700 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -604,6 +604,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 SetOp node
--
[parent: a7a2cab57447]