agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: rahul@rhyadav.dev
To: Samuel Olaoye <dapsalmy@gmail.com>
Cc: Pgsql Bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)
Date: Fri, 2 Oct 2026 09:47:18 +0200 (CEST)
Message-ID: <P2v_Ach--F-9@rhyadav.dev> (raw)
In-Reply-To: <179087936257.78721.1170374394656748858@mail.gmail.com>
References: <179087936257.78721.1170374394656748858@mail.gmail.com>
Hi Samuel,
Thanks for the detailed report and the reproduction.
I can reproduce it on master (45277ca0d1): Q1 returns (2, f), Q2
returns (2, t), and the plan has the same hashed SubPlan in both the
t_channels scan filter and the join filter, with loops=3 on the
t_follows scan.
Your reading of the executor is right; I confirmed it by adding
temporary logging to ExecHashSubPlan(). Both references get their
own SubPlanState and hash table, but share the subplan's PlanState.
For the second outer row (viewer 502):
scan filter: has a hash table, chgParam set -> rebuilds it
join filter: has a hash table, chgParam clear -> keeps the table
built for viewer 501, so channel 2 isn't found
buildSubPlanHash() calls ExecReScan() on the shared PlanState, which
clears its chgParam, so the join filter's SubPlanState never sees
that the parameter changed.
This isn't specific to the EXISTS-to-ANY conversion. A similar query
with a plain correlated IN gives the same wrong result on master:
select s.id,
exists (select 1 from t_posts p
join t_channels c on c.id = p.channel_id
where p.id = s.post_id
and (c.owner_id = s.viewer_id
or (p.status = 'published'
and c.id in (select f.channel_id
from t_follows f
where f.user_id = s.viewer_id))))
from t_sessions s order by s.id;
The attached patch gives each SubPlanState its own flag marking its
hash table as stale. ExecReScan() sets it where it propagates
chgParam to the node's subplans, ExecHashSubPlan() rebuilds the
table when it is set, and buildSubPlanHash() clears it. With the
patch, both queries return (2, t) and the t_follows scan shows
loops=4. The patch adds a test to subselect.sql that fails without
the fix, and the regression, isolation and contrib tests pass (I
haven't run the TAP tests).
I went for an executor fix rather than changing
extract_restriction_or_clauses(), because the same subplan being
referenced from more than one place is already expected elsewhere:
ExplainSubPlans() notes that several SubPlan nodes can reference the
same subplan from different plan nodes, e.g. a bitmap index scan's
indexqual and its parent heap scan's recheck qual. Giving each
reference its own PlanState would also work, but seems much more
invasive.
The new bool goes into the alignment padding after havenullrows, so
sizeof(SubPlanState) and the offsets of the other fields don't change
(checked on a 64-bit build of master; the layout around it is the same
in 14-18), which should keep it safe to back-patch. The code changes
apply to REL_19_STABLE as is; 14-18 need small adjustments, so I'll
post tested back-branch versions next.
Regards,
Rahul Yadav
Attachments:
[application/octet-stream] v1-0001-Fix-reuse-of-stale-hash-tables-by-duplicated-hash.patch (9.1K, ../P2v_Ach--F-9@rhyadav.dev/2-v1-0001-Fix-reuse-of-stale-hash-tables-by-duplicated-hash.patch)
download | inline diff:
From e0c97829b0e56bcffac9fec70f6380cf01105ec8 Mon Sep 17 00:00:00 2001
From: Rahul Yadav <rahul@rhyadav.dev>
Date: Fri, 2 Oct 2026 08:19:11 +0100
Subject: [PATCH v1] 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 extract_restriction_or_clauses() extracts a restriction
clause for one relation from a join OR clause, a SubPlan in the
extracted sub-clauses ends up both in the new restriction clause and
in the original join clause. Each reference gets its own
SubPlanState, but they all share the subplan's PlanState.
A hashed SubPlan whose subquery references outer query levels must
rebuild its hash table 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
table, 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 table as
stale. ExecReScan() sets it, alongside propagating chgParam to the
node's subplans, and buildSubPlanHash() clears it.
Reported-by: Samuel Olaoye <dapsalmy@gmail.com>
Author: Rahul Yadav <rahul@rhyadav.dev>
Discussion: https://postgr.es/m/179087936257.78721.1170374394656748858@mail.gmail.com
Backpatch-through: 14
---
src/backend/executor/execAmi.c | 9 +++++
src/backend/executor/nodeSubplan.c | 12 +++++-
src/include/nodes/execnodes.h | 1 +
src/test/regress/expected/subselect.out | 51 +++++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 31 +++++++++++++++
5 files changed, 102 insertions(+), 2 deletions(-)
diff --git a/src/backend/executor/execAmi.c b/src/backend/executor/execAmi.c
index 37fe03fdc3..a843a5dcff 100644
--- a/src/backend/executor/execAmi.c
+++ b/src/backend/executor/execAmi.c
@@ -116,6 +116,15 @@ ExecReScan(PlanState *node)
if (splan->plan->extParam != NULL)
UpdateChangedParamSet(splan, node->chgParam);
+
+ /*
+ * Also mark this SubPlanState's hash table, if any, as stale. The
+ * subplan's chgParam isn't enough, since the subplan may be
+ * shared with other SubPlanStates, and building one of their hash
+ * tables rescans the subplan and clears its chgParam.
+ */
+ 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 c6dd463c11..bc94c1e8e9 100644
--- a/src/backend/executor/nodeSubplan.c
+++ b/src/backend/executor/nodeSubplan.c
@@ -110,9 +110,15 @@ ExecHashSubPlan(SubPlanState *node,
/*
* If first time through or we need to rescan the subplan, build the hash
- * table.
+ * table. planstate->chgParam alone isn't enough to detect the latter: if
+ * the same SubPlan appears more than once in the plan tree, all its
+ * SubPlanStates share one planstate, and building any of their hash
+ * tables rescans it and clears its chgParam. So we also check our own
+ * hashtablestale flag, which is set when our parent node is rescanned
+ * with changed parameters.
*/
- if (node->hashtable == NULL || planstate->chgParam != NULL)
+ if (node->hashtable == NULL || node->hashtablestale ||
+ planstate->chgParam != NULL)
buildSubPlanHash(node, econtext);
/*
@@ -505,6 +511,7 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
*/
node->havehashrows = false;
node->havenullrows = false;
+ node->hashtablestale = false;
nentries = planstate->plan->plan_rows;
@@ -881,6 +888,7 @@ ExecInitSubPlan(SubPlan *subplan, PlanState *parent)
sstate->projRight = NULL;
sstate->hashtable = NULL;
sstate->hashnulls = NULL;
+ sstate->hashtablestale = false;
sstate->tuplesContext = NULL;
sstate->innerecontext = NULL;
sstate->keyColIdx = NULL;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 91bb0bd2e1..b2d7bf3756 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1037,6 +1037,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 tuplesContext; /* context containing hash tables' tuples */
ExprContext *innerecontext; /* econtext for computing inner tuples */
int numCols; /* number of columns being hashed */
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index cf295d5650..06c6504a1b 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1450,6 +1450,57 @@ where o.ten = 0;
100
(1 row)
+--
+-- Test rescan of a hashed subplan that appears in more than one qual.
+-- Here the OR clause's restriction on "c" is extracted and also applied to
+-- the scan of "c", so the same hashed SubPlan is evaluated in two places;
+-- each must rebuild its hash table when the outer parameter changes.
+--
+create temp table hsp_c (id int primary key, owner int);
+create temp table hsp_p (id int primary key, cid int, pub bool);
+create temp table hsp_f (cid int, uid int);
+create temp table hsp_s (id int primary key, pid int, uid int);
+insert into hsp_c values (1, 100), (2, 200);
+insert into hsp_p values (10, 1, true), (20, 2, true);
+insert into hsp_f values (1, 501), (2, 502);
+insert into hsp_s values (1, 10, 501), (2, 20, 502);
+analyze hsp_c, hsp_p, hsp_f, hsp_s;
+explain (costs off)
+select s.id, exists (select 1 from hsp_p p join hsp_c c on c.id = p.cid
+ where p.id = s.pid
+ and (c.owner = s.uid
+ or (p.pub and c.id in (select cid from hsp_f
+ where uid = s.uid))))
+from hsp_s s order by s.id;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: s.id
+ -> Seq Scan on hsp_s s
+ SubPlan exists_1
+ -> Nested Loop
+ Join Filter: ((c.id = p.cid) AND ((c.owner = s.uid) OR (p.pub AND (ANY (c.id = (hashed SubPlan any_1).col1)))))
+ -> Seq Scan on hsp_p p
+ Filter: (id = s.pid)
+ -> Seq Scan on hsp_c c
+ Filter: ((owner = s.uid) OR (ANY (id = (hashed SubPlan any_1).col1)))
+ SubPlan any_1
+ -> Seq Scan on hsp_f
+ Filter: (uid = s.uid)
+(13 rows)
+
+select s.id, exists (select 1 from hsp_p p join hsp_c c on c.id = p.cid
+ where p.id = s.pid
+ and (c.owner = s.uid
+ or (p.pub and c.id in (select cid from hsp_f
+ where uid = s.uid))))
+from hsp_s s order by s.id;
+ id | exists
+----+--------
+ 1 | t
+ 2 | t
+(2 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 07438694f6..eb00c98252 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -726,6 +726,37 @@ 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 appears in more than one qual.
+-- Here the OR clause's restriction on "c" is extracted and also applied to
+-- the scan of "c", so the same hashed SubPlan is evaluated in two places;
+-- each must rebuild its hash table when the outer parameter changes.
+--
+create temp table hsp_c (id int primary key, owner int);
+create temp table hsp_p (id int primary key, cid int, pub bool);
+create temp table hsp_f (cid int, uid int);
+create temp table hsp_s (id int primary key, pid int, uid int);
+insert into hsp_c values (1, 100), (2, 200);
+insert into hsp_p values (10, 1, true), (20, 2, true);
+insert into hsp_f values (1, 501), (2, 502);
+insert into hsp_s values (1, 10, 501), (2, 20, 502);
+analyze hsp_c, hsp_p, hsp_f, hsp_s;
+
+explain (costs off)
+select s.id, exists (select 1 from hsp_p p join hsp_c c on c.id = p.cid
+ where p.id = s.pid
+ and (c.owner = s.uid
+ or (p.pub and c.id in (select cid from hsp_f
+ where uid = s.uid))))
+from hsp_s s order by s.id;
+
+select s.id, exists (select 1 from hsp_p p join hsp_c c on c.id = p.cid
+ where p.id = s.pid
+ and (c.owner = s.uid
+ or (p.pub and c.id in (select cid from hsp_f
+ where uid = s.uid))))
+from hsp_s s order by s.id;
+
--
-- Test rescan of a hashed SetOp node
--
--
2.50.1 (Apple Git-155)
view thread (2+ messages)
Message-ID: <P2v_Ach--F-9@rhyadav.dev>
Permalink: ../P2v_Ach--F-9@rhyadav.dev/
Also on: postgresql.org/message-id/P2v_Ach--F-9@rhyadav.dev
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: rahul@rhyadav.dev, dapsalmy@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)
In-Reply-To: <P2v_Ach--F-9@rhyadav.dev>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox