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]