agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedWrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)
2+ messages / 2 participants
[nested] [flat]
* Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)
@ 2026-10-01 18:29 Samuel Olaoye <dapsalmy@gmail.com>
2026-10-02 07:47 ` Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4) rahul@rhyadav.dev
0 siblings, 1 reply; 2+ messages in thread
From: Samuel Olaoye @ 2026-10-01 18:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
\pset pager off
select version();
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;
\echo '== Q1: each session viewer follows its own channel; expected (1,t), (2,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;
\echo '== Q2: identical except 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;
\echo '== Q1 plan'
explain (analyze, verbose, costs off, timing off, summary off)
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;
\pset pager off
select version();
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;
\echo '== V1: EXISTS referenced only once (no OR-qual extraction duplicate); expected (1,t), (2,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 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;
\echo '== V2: same rule as Q1, viewer passed as a bind parameter, one call per session; expected t then t'
prepare allowed(int, int) as
select exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
where p.id = $1
and (c.owner_id = $2
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 = $2))))) as allowed;
execute allowed(10, 501);
execute allowed(20, 502);
\echo '== V3: Q1 restricted to session 2 alone (single outer row); expected 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 where s.id = 2;
\echo '== V4: Q1 with the outer rows in reverse order; expected (2,t), (1,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 (select * from t_sessions order by id desc offset 0) s;
psql output for pg_hashed_subplan_repro.sql and pg_hashed_subplan_variations.sql
Each run: a fresh official postgres Docker image, default configuration, run as: psql -X -v ON_ERROR_STOP=1 -f <file>
Expected in Q1: (1,t) and (2,t). Every version below returns (2,f) in Q1 and the correct (2,t) in Q2.
Hosts:
x86_64: Ubuntu 26.04.1 server, kernel 7.0.0-30, Docker 29.8.2
aarch64: Docker 29.7.2 on macOS 26.6 (Apple M3 Max)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 18.6 (x86_64)
==================================================================
Pager usage is off.
version
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
Planning:
Buffers: shared hit=8
(37 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 19beta4 (aarch64)
==================================================================
Pager usage is off.
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 19beta4 (Debian 19~beta4-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2.00 loops=1)
Output: s.id, (EXISTS(SubPlan exists_1))
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 exists_1)
Buffers: shared hit=8
SubPlan exists_1
-> 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 exists_to_any_1).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 exists_to_any_1).col1)))
Rows Removed by Filter: 1
Buffers: shared hit=4
SubPlan exists_to_any_1
-> 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
Planning:
Buffers: shared hit=8
(37 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 18.6 (aarch64)
==================================================================
Pager usage is off.
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
Planning:
Buffers: shared hit=8
(37 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 17.11 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, (EXISTS(SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, EXISTS(SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 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
SubPlan 2
-> Seq Scan on public.t_follows f (actual rows=1 loops=3)
Output: f.channel_id
Filter: (f.user_id = s.viewer_id)
Rows Removed by Filter: 1
(28 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 16.15 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, ((SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, (SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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 (hashed SubPlan 2))))
Rows Removed by Join Filter: 0
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2))
Rows Removed by Filter: 1
SubPlan 2
-> Seq Scan on public.t_follows f (actual rows=1 loops=3)
Output: f.channel_id
Filter: (f.user_id = s.viewer_id)
Rows Removed by Filter: 1
(28 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 15.19 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.19 (Debian 15.19-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, ((SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, (SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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 (hashed SubPlan 2))))
Rows Removed by Join Filter: 0
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2))
Rows Removed by Filter: 1
SubPlan 2
-> Seq Scan on public.t_follows f (actual rows=1 loops=3)
Output: f.channel_id
Filter: (f.user_id = s.viewer_id)
Rows Removed by Filter: 1
(28 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 14.24 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.24 (Debian 14.24-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, ((SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, (SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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 (hashed SubPlan 2))))
Rows Removed by Join Filter: 0
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2))
Rows Removed by Filter: 1
SubPlan 2
-> Seq Scan on public.t_follows f (actual rows=1 loops=3)
Output: f.channel_id
Filter: (f.user_id = s.viewer_id)
Rows Removed by Filter: 1
(28 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 13.23 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.23 (Debian 13.23-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, ((SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, (SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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 (alternatives: SubPlan 1 or hashed SubPlan 2))))
Rows Removed by Join Filter: 0
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2))
Rows Removed by Filter: 1
SubPlan 1
-> Seq Scan on public.t_follows f (never executed)
Filter: ((f.channel_id = c.id) AND (f.user_id = s.viewer_id))
SubPlan 2
-> Seq Scan on public.t_follows f_1 (actual rows=1 loops=3)
Output: f_1.channel_id
Filter: (f_1.user_id = s.viewer_id)
Rows Removed by Filter: 1
(31 rows)
==================================================================
== pg_hashed_subplan_variations.sql on PostgreSQL 18.6 (aarch64)
==================================================================
Pager usage is off.
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== V1: EXISTS referenced only once (no OR-qual extraction duplicate); expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== V2: same rule as Q1, viewer passed as a bind parameter, one call per session; expected t then t
PREPARE
allowed
---------
t
(1 row)
allowed
---------
t
(1 row)
== V3: Q1 restricted to session 2 alone (single outer row); expected t
id | allowed
----+---------
2 | t
(1 row)
== V4: Q1 with the outer rows in reverse order; expected (2,t), (1,t)
id | allowed
----+---------
2 | t
1 | f
(2 rows)
Attachments:
[text/plain] pg_hashed_subplan_repro.sql (2.2K, ../../179087936257.78721.1170374394656748858@mail.gmail.com/3-pg_hashed_subplan_repro.sql)
download | inline:
\pset pager off
select version();
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;
\echo '== Q1: each session viewer follows its own channel; expected (1,t), (2,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;
\echo '== Q2: identical except 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;
\echo '== Q1 plan'
explain (analyze, verbose, costs off, timing off, summary off)
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;
[text/plain] pg_hashed_subplan_variations.sql (2.7K, ../../179087936257.78721.1170374394656748858@mail.gmail.com/4-pg_hashed_subplan_variations.sql)
download | inline:
\pset pager off
select version();
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;
\echo '== V1: EXISTS referenced only once (no OR-qual extraction duplicate); expected (1,t), (2,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 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;
\echo '== V2: same rule as Q1, viewer passed as a bind parameter, one call per session; expected t then t'
prepare allowed(int, int) as
select exists (select 1 from t_posts p join t_channels c on c.id = p.channel_id
where p.id = $1
and (c.owner_id = $2
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 = $2))))) as allowed;
execute allowed(10, 501);
execute allowed(20, 502);
\echo '== V3: Q1 restricted to session 2 alone (single outer row); expected 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 where s.id = 2;
\echo '== V4: Q1 with the outer rows in reverse order; expected (2,t), (1,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 (select * from t_sessions order by id desc offset 0) s;
[text/plain] pg_hashed_subplan_results.txt (26.2K, ../../179087936257.78721.1170374394656748858@mail.gmail.com/5-pg_hashed_subplan_results.txt)
download | inline:
psql output for pg_hashed_subplan_repro.sql and pg_hashed_subplan_variations.sql
Each run: a fresh official postgres Docker image, default configuration, run as: psql -X -v ON_ERROR_STOP=1 -f <file>
Expected in Q1: (1,t) and (2,t). Every version below returns (2,f) in Q1 and the correct (2,t) in Q2.
Hosts:
x86_64: Ubuntu 26.04.1 server, kernel 7.0.0-30, Docker 29.8.2
aarch64: Docker 29.7.2 on macOS 26.6 (Apple M3 Max)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 18.6 (x86_64)
==================================================================
Pager usage is off.
version
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
Planning:
Buffers: shared hit=8
(37 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 19beta4 (aarch64)
==================================================================
Pager usage is off.
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 19beta4 (Debian 19~beta4-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2.00 loops=1)
Output: s.id, (EXISTS(SubPlan exists_1))
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 exists_1)
Buffers: shared hit=8
SubPlan exists_1
-> 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 exists_to_any_1).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 exists_to_any_1).col1)))
Rows Removed by Filter: 1
Buffers: shared hit=4
SubPlan exists_to_any_1
-> 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
Planning:
Buffers: shared hit=8
(37 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 18.6 (aarch64)
==================================================================
Pager usage is off.
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
Planning:
Buffers: shared hit=8
(37 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 17.11 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, (EXISTS(SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, EXISTS(SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 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
SubPlan 2
-> Seq Scan on public.t_follows f (actual rows=1 loops=3)
Output: f.channel_id
Filter: (f.user_id = s.viewer_id)
Rows Removed by Filter: 1
(28 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 16.15 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, ((SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, (SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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 (hashed SubPlan 2))))
Rows Removed by Join Filter: 0
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2))
Rows Removed by Filter: 1
SubPlan 2
-> Seq Scan on public.t_follows f (actual rows=1 loops=3)
Output: f.channel_id
Filter: (f.user_id = s.viewer_id)
Rows Removed by Filter: 1
(28 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 15.19 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.19 (Debian 15.19-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, ((SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, (SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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 (hashed SubPlan 2))))
Rows Removed by Join Filter: 0
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2))
Rows Removed by Filter: 1
SubPlan 2
-> Seq Scan on public.t_follows f (actual rows=1 loops=3)
Output: f.channel_id
Filter: (f.user_id = s.viewer_id)
Rows Removed by Filter: 1
(28 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 14.24 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.24 (Debian 14.24-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, ((SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, (SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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 (hashed SubPlan 2))))
Rows Removed by Join Filter: 0
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (hashed SubPlan 2))
Rows Removed by Filter: 1
SubPlan 2
-> Seq Scan on public.t_follows f (actual rows=1 loops=3)
Output: f.channel_id
Filter: (f.user_id = s.viewer_id)
Rows Removed by Filter: 1
(28 rows)
==================================================================
== pg_hashed_subplan_repro.sql on PostgreSQL 13.23 (aarch64)
==================================================================
Pager usage is off.
version
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.23 (Debian 13.23-1.pgdg13+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== Q1: each session viewer follows its own channel; expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | f
(2 rows)
== Q2: identical except OFFSET 0 on the inner EXISTS
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== Q1 plan
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (actual rows=2 loops=1)
Output: s.id, ((SubPlan 3))
Sort Key: s.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t_sessions s (actual rows=2 loops=1)
Output: s.id, (SubPlan 3)
SubPlan 3
-> Hash Join (actual rows=0 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 (alternatives: SubPlan 1 or hashed SubPlan 2))))
Rows Removed by Join Filter: 0
-> Seq Scan on public.t_posts p (actual rows=1 loops=2)
Output: p.id, p.channel_id, p.status
Filter: (p.id = s.post_id)
Rows Removed by Filter: 0
-> Hash (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t_channels c (actual rows=1 loops=2)
Output: c.id, c.owner_id, c.visibility
Filter: ((c.owner_id = s.viewer_id) OR (c.visibility = 'public'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2))
Rows Removed by Filter: 1
SubPlan 1
-> Seq Scan on public.t_follows f (never executed)
Filter: ((f.channel_id = c.id) AND (f.user_id = s.viewer_id))
SubPlan 2
-> Seq Scan on public.t_follows f_1 (actual rows=1 loops=3)
Output: f_1.channel_id
Filter: (f_1.user_id = s.viewer_id)
Rows Removed by Filter: 1
(31 rows)
==================================================================
== pg_hashed_subplan_variations.sql on PostgreSQL 18.6 (aarch64)
==================================================================
Pager usage is off.
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 2
INSERT 0 2
ANALYZE
== V1: EXISTS referenced only once (no OR-qual extraction duplicate); expected (1,t), (2,t)
id | allowed
----+---------
1 | t
2 | t
(2 rows)
== V2: same rule as Q1, viewer passed as a bind parameter, one call per session; expected t then t
PREPARE
allowed
---------
t
(1 row)
allowed
---------
t
(1 row)
== V3: Q1 restricted to session 2 alone (single outer row); expected t
id | allowed
----+---------
2 | t
(1 row)
== V4: Q1 with the outer rows in reverse order; expected (2,t), (1,t)
id | allowed
----+---------
2 | t
1 | f
(2 rows)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)
2026-10-01 18:29 Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4) Samuel Olaoye <dapsalmy@gmail.com>
@ 2026-10-02 07:47 ` rahul@rhyadav.dev
0 siblings, 0 replies; 2+ messages in thread
From: rahul@rhyadav.dev @ 2026-10-02 07:47 UTC (permalink / raw)
To: Samuel Olaoye <dapsalmy@gmail.com>; +Cc: Pgsql Bugs <pgsql-bugs@lists.postgresql.org>
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-10-02 07:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-01 18:29 Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4) Samuel Olaoye <dapsalmy@gmail.com>
2026-10-02 07:47 ` rahul@rhyadav.dev
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox