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

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 Linux: 18.6 (Ubuntu 26.04 host, kernel 7.0.0-30)
aarch64 Linux: 13.23, 14.24, 15.19, 16.15, 17.11, 18.6 and 19beta4 (Docker on macOS 26.6)
Builds: official 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)

V1: With the EXISTS written once (owner OR public OR EXISTS), the plan has one reference and both rows are t.
V2: The Q1 rule called once per session with the viewer as a bind parameter returns t both times.
V3: Q1 limited to session 2 alone returns t.
V4: Q1 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