Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

Поиск
Список
Период
Сортировка
Искать

Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
Samuel Olaoye <dapsalmy@gmail.com>
Дата:
Вложения:

Hello,

We found a wrong-results bug at Bolrach Technologies while testing an access check that runs once per row of a sessions table. On a fresh database with default settings, the query below returns false for a row where the answer is true. Adding OFFSET 0 to the inner EXISTS gives the right answer.

Viewer 502 follows channel 2, yet Q1 returns (2, f). Q2 is the same query with OFFSET 0 on the inner EXISTS, and it returns (2, t).

We reproduced it on the latest release of each major version from 13 to 18, and on 19beta4:

x86_64 Linux18.6 (Ubuntu 26.04 host, kernel 7.0.0-30)
aarch64 Linux13.23, 14.24, 15.19, 16.15, 17.11, 18.6 and 19beta4 (Docker on macOS 26.6)
Buildsofficial postgres Docker images, Debian pgdg packages, default configuration

Reproduction

create table t_channels (id int primary key, owner_id int not null, visibility text not null);
create table t_posts (id int primary key, channel_id int not null, status text not null);
create table t_follows (channel_id int not null, user_id int not null, primary key (channel_id, user_id));
create table t_sessions (id int primary key, post_id int not null, viewer_id int not null);
insert into t_channels values (1, 100, 'private'), (2, 200, 'private');
insert into t_posts values (10, 1, 'published'), (20, 2, 'published');
insert into t_follows values (1, 501), (2, 502);
insert into t_sessions values (1, 10, 501), (2, 20, 502);
analyze t_channels, t_posts, t_follows, t_sessions;
-- Q1: each session's viewer follows the channel of that session's post, so both rows should be t
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.visibility = 'public'                           or exists (select 1 from t_follows f                                       where f.channel_id = c.id and f.user_id = s.viewer_id))))) as allowed  from t_sessions s order by s.id;
-- Q2: identical, with OFFSET 0 on the inner EXISTS
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.visibility = 'public'                           or exists (select 1 from t_follows f                                       where f.channel_id = c.id and f.user_id = s.viewer_id offset 0))))) as allowed  from t_sessions s order by s.id;

Result on 18.6 x86_64 (all the versions above give the same rows)

Q1: id | allowed
----+---------  1 | t  2 | f        <- wrong, viewer 502 follows channel 2

Q2: id | allowed
----+---------  1 | t  2 | t

EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF) of Q1 on 18.6 x86_64

Sort (actual rows=2.00 loops=1)  Output: s.id, (EXISTS(SubPlan 3))  Sort Key: s.id  Sort Method: quicksort  Memory: 25kB  Buffers: shared hit=8  ->  Seq Scan on public.t_sessions s (actual rows=2.00 loops=1)        Output: s.id, EXISTS(SubPlan 3)        Buffers: shared hit=8        SubPlan 3          ->  Hash Join (actual rows=0.50 loops=2)                Inner Unique: true                Hash Cond: (p.channel_id = c.id)                Join Filter: ((c.owner_id = s.viewer_id) OR ((p.status = 'published'::text) AND ((c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan 2).col1)))))                Rows Removed by Join Filter: 0                Buffers: shared hit=7                ->  Seq Scan on public.t_posts p (actual rows=1.00 loops=2)                      Output: p.id, p.channel_id, p.status                      Filter: (p.id = s.post_id)                      Rows Removed by Filter: 0                      Buffers: shared hit=2                ->  Hash (actual rows=1.00 loops=2)                      Output: c.id, c.owner_id, c.visibility                      Buckets: 1024  Batches: 1  Memory Usage: 9kB                      Buffers: shared hit=4                      ->  Seq Scan on public.t_channels c (actual rows=1.00 loops=2)                            Output: c.id, c.owner_id, c.visibility                            Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (ANY (c.id = (hashed SubPlan 2).col1)))                            Rows Removed by Filter: 1                            Buffers: shared hit=4                            SubPlan 2                              ->  Seq Scan on public.t_follows f (actual rows=1.00 loops=3)                                    Output: f.channel_id                                    Filter: (f.user_id = s.viewer_id)                                    Rows Removed by Filter: 1                                    Buffers: shared hit=3

Our current hypothesis

The reproduction shows the wrong result. The mechanism below is our reading of the plan and of the executor source, and we have not confirmed it in a debugger.

SubPlan 2 is in the plan twice. The planner extracts a restriction (owner OR public OR EXISTS) from the OR clause and applies it to the t_channels scan, while keeping the original OR as the Hash Join's Join Filter, so the same hashed SubPlan sits in both quals. The direct correlation on c.id has become the hash lookup, while the outer s.viewer_id dependency stays inside the planned subquery as a PARAM_EXEC parameter.

As far as we can tell from the executor initialization path, each of the two references gets its own SubPlanState, and so its own hash table, but ExecInitSubPlan resolves the same plan_id to one shared PlanState for the t_follows scan. ExecSubPlan rebuilds a table when node->hashtable is NULL or planstate->chgParam is not NULL, and buildSubPlanHash calls ExecReScan on that shared planstate, which clears chgParam.

For session 1 (viewer 501), the scan Filter builds its table, the owner check fails in the Join Filter, and the Join Filter copy builds a second table for the same viewer because its hashtable is still NULL. That row comes out right.

Session 2 (viewer 502) changes the parameter, which sets chgParam on the shared planstate. The scan Filter sees it and rebuilds for 502, and the ExecReScan inside that rebuild clears chgParam. When the Join Filter copy runs next, its hashtable is not NULL and chgParam is now clear, so it appears to reuse the table built for viewer 501. Channel 2 is not in that table, so the row comes out false. The t_follows scan shows loops=3, where two outer rows and two references would need four builds.

Checks that support this (pg_hashed_subplan_variations.sql, all on 18.6)

V1With the EXISTS written once (owner OR public OR EXISTS), the plan has one reference and both rows are t.
V2The Q1 rule called once per session with the viewer as a bind parameter returns t both times.
V3Q1 limited to session 2 alone returns t.
V4Q1 with the outer rows in reverse order returns (2, t) and (1, f). The wrong answer moves to whichever row comes second.

We searched the release notes and the list archives and found nothing matching. The 2020 thread "Broken resetting of subplan hash tables" is about the same area of nodeSubplan.c, but it dealt with how the hash tables were reset, not with two SubPlanStates sharing one PlanState's chgParam.

Anyone who hits this can add OFFSET 0 to the inner EXISTS, or evaluate the rule once per outer row with bound parameters.

Three files are attached. pg_hashed_subplan_repro.sql is the setup with Q1, Q2 and the EXPLAIN, and it runs with psql -X -f. pg_hashed_subplan_variations.sql holds V1 to V4. pg_hashed_subplan_results.txt has the full psql output from every version listed above, plus the variations run.

Thanks.

Samuel Olaoye
Bolrach Technologies

Attached: pg_hashed_subplan_repro.sql, pg_hashed_subplan_variations.sql, pg_hashed_subplan_results.txt

Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
rahul@rhyadav.dev
Дата:
Вложения:
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


Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
Alexandra Wang <alexandra.wang.oss@gmail.com>
Дата:
Hi Rahul,

On Fri, Oct 16, 2026 at 3:37 PM Rahul Yadav wrote:
> The attached patch gives each SubPlanState its own flag marking its
> hash table as stale.

Thanks for the patch!

I tested v1-0001 on current master (6b3f8857aa8) and it
looks good to me overall.

One minor question: with hashtablestale in place, do we still need the
"planstate->chgParam != NULL" test in ExecHashSubPlan()?

I also found a related bug in the EPQ recheck code, using the same
query shape.  Here is a minimal repro with the table and data from the
original report:

create table t_posts (id int primary key, channel_id int not null, status text not null);
create table t_follows (channel_id int not null, user_id int not null, primary key (channel_id, user_id));
create table t_sessions (id int primary key, post_id int not null, viewer_id int not null);
insert into t_posts values (10, 1, 'published'), (20, 2, 'published');
insert into t_follows values (1, 501), (2, 502);
insert into t_sessions values (1, 10, 501), (2, 20, 502);
analyze t_posts, t_follows, t_sessions;

-- session 1
begin;
update t_posts set status = status;

-- session 2 (blocks)
select s.id,
       exists (select 1 from t_posts p
                where p.id = s.post_id
                  and (p.status = 'draft'
                       or p.channel_id in (select f.channel_id from t_follows f
                                            where f.user_id = s.viewer_id))
                for update of p) as allowed -- FOR UPDATE is variant
  from t_sessions s order by s.id;

-- session 1
commit;

Session 2 then returns (1, t), (2, f).  Both rows should be true: each
viewer follows the channel of their own session's post.  The same
query without the concurrent update returns (2, t), and so does a
concurrent update of post 20 only.  I see the same result on master
and with v1 applied.  The report in this thread is fixed by v1; it is
this FOR UPDATE variant that is still wrong.

This needs only ONE hashed reference, so it is not the
duplicated-SubPlan bug.  The IN stays a hashed SubPlan inside an EPQ
recheck tree.  The EXISTS returns after its first row, so the same
recheck tree is reused for the second session row.  In the reuse
branch of EvalPlanQualBegin() the new values of all parent params are
copied, but only epqParam is marked changed:

        rcplanstate->chgParam = bms_add_member(rcplanstate->chgParam,
                                               epqstate->epqParam);

UpdateChangedParamSet() keeps only the params in each node's allParam,
so the hashed SubPlan's inner plan (allParam (b 0), where param 0 is
s.viewer_id) never sees a change.  ExecHashSubPlan() then keeps
probing the table built for viewer 501.

Here is my proposed fix:

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 2519af9e778..2a265a7d381 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -3044,6 +3044,17 @@ EvalPlanQualBegin(EPQState *epqstate)
  */
  rcplanstate->chgParam = bms_add_member(rcplanstate->chgParam,
  epqstate->epqParam);
+
+ /*
+ * We just overwrote the values of the parent parameters above, so
+ * flag all the external parameters the tree depends on as changed.
+ * Otherwise a node that caches state computed from a parameter, such
+ * as a hashed SubPlan, would keep using the value from the previous
+ * EPQ evaluation.  UpdateChangedParamSet() filters this set down to
+ * the parameters each descendant node actually depends on.
+ */
+ rcplanstate->chgParam = bms_add_members(rcplanstate->chgParam,
+ rcplanstate->plan->extParam);
  }
 }

The FOR UPDATE variant of the reporter's join query contains both
bugs: it returns (2, f) on master, with v1 alone, and with this change
alone, and (2, t) only with both applied.

Do you think this can go together with your fix?  Or would you prefer
it as a separate patch?  I am happy either way, and I can send the
isolation spec if that is useful.

Best,
Alex

--
Alexandra Wang

Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
Rahul <rahul@rhyadav.dev>
Дата:
Вложения:
Hi Alex,

On Fri, Oct 16, 2026, Alexandra Wang wrote:
> I tested v1-0001 on current master (6b3f8857aa8) and it looks good to
> me overall.

Thanks for testing and reviewing it!

> One minor question: with hashtablestale in place, do we still need the
> "planstate->chgParam != NULL" test in ExecHashSubPlan()?

No, I don't think we do.  The only place that sets chgParam on the
subplan of a hashed SubPlan is the subPlan loop in ExecReScan(), and
v1 already sets hashtablestale right there.  The attached v2 drops the
test, so the flag is the only signal.  Otherwise it's the same as v1,
rebased on current master.

> I also found a related bug in the EPQ recheck code, using the same
> query shape.

Nice catch.  I can reproduce it on current master (7787aec8ab), with
the same results you got: your FOR UPDATE query is fixed by your
change alone, and the FOR UPDATE variant of the join query needs both
fixes.

> Do you think this can go together with your fix?  Or would you prefer
> it as a separate patch?

I'd prefer a separate patch.  It's a different mechanism in different
code, and each fix can be committed without the other.  Could you post
it in a new thread with its own commitfest entry, along with your
isolation spec?  That also lets cfbot test each patch on its own.  A
link back to this thread would help, since the FOR UPDATE join query
needs both fixes.  I'm happy to review it.

With v2, the regression, isolation and contrib tests pass (I haven't
run the TAP tests).  I still owe back-branch versions; I'll post them
once the master version is settled.

Regards,
Rahul Yadav



Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
Richard Guo <guofenglinux@gmail.com>
Дата:
Вложения:
On Sat, Oct 17, 2026 at 9:06 PM Rahul  wrote:
> With v2, the regression, isolation and contrib tests pass (I haven't
> run the TAP tests).  I still owe back-branch versions; I'll post them
> once the master version is settled.

Nice catch!  The fix in v2 looks good to me.

I'm not very happy with the test case though.  You don't need an OR
clause to hit this bug.  It can happen whenever the same hashed
SubPlan is referenced more than once in the plan, so we can use a much
simpler query.  Also, we should avoid creating new tables in the tests
if existing ones can do the job.

Also, I don't quite like the comments.  They mainly explain why we no
longer look at the subplan's chgParam, which is really about the code
being removed.  I think the comments should describe what the code
does now, and the history can go in the commit message.

v3 attached.  Unless there are objections, I'll push it and back-patch
to 14 before the upcoming minor releases.

- Richard

Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Richard,

v3 looks right to me. Each reference gets its own flag, so the
rescan order does not matter.

I did some experiment on your patch and also tested JIT on it, everything
looks good

Thanks,
Shihao

Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
Richard Guo <guofenglinux@gmail.com>
Дата:
On Sat, Oct 31, 2026 at 1:49 PM shihao zhong  wrote:
> v3 looks right to me. Each reference gets its own flag, so the
> rescan order does not matter.
>
> I did some experiment on your patch and also tested JIT on it, everything
> looks good

I've pushed this and back-patched it to v14.

In the back branches the new field fits into existing padding in
SubPlanState, so the struct size and the offsets of its other members
don't change.  I checked this with abidiff locally.  But I guess the
buildfarm's abi-compliance-check will still report the change, so I'm
waiting for those results and will then update .abi-compliance-history
in the back branches.

- Richard


Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
Richard Guo <guofenglinux@gmail.com>
Дата:
On Mon, Nov 2, 2026 at 2:51 PM Richard Guo  wrote:
> ... so I'm
> waiting for those results and will then update .abi-compliance-history
> in the back branches.

Done.

- Richard


Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)

От:
Rahul <rahul@rhyadav.dev>
Дата:
Hi Richard,

On Mon, Nov 2, 2026, Richard Guo wrote:
> Done.

Thanks for reviewing, committing and back-patching it, and for taking
care of the ABI check.  The simpler test and the rewritten comments
are clear improvements, and I'll keep both points in mind for future
patches.

Thanks also to Alex and Shihao for reviewing, and to Samuel for the
report.

Regards,
Rahul Yadav



FAQ