pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19828: "plan should not reference subplan's variable" with Memoize since f8da64e6
2+ messages / 2 participants
[nested] [flat]
* BUG #19828: "plan should not reference subplan's variable" with Memoize since f8da64e6
@ 2026-11-02 16:06 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-11-02 16:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: venu2805k@gmail.com
The following bug has been logged on the website:
Bug reference: 19828
Logged by: Venu Kummari
Email address: venu2805k@gmail.com
PostgreSQL version: 19rc1
Operating system: Ubuntu 24.04, x86_64
Description:
PostgreSQL version: master (20devel) 633dee1b, built from source with
assertions. Operating system: Ubuntu 24.04, x86_64.
Since commit f8da64e6 ("Make cache keys of other Params a Memoize subplan
depends on"), a correlated ALL/ANY sublink in the quals of a memoized
relation makes planning fail, with default settings:
CREATE TABLE d_a (x int NOT NULL);
CREATE TABLE d_b (x int, pad text);
INSERT INTO d_a SELECT g % 10 FROM generate_series(1, 1000) g;
INSERT INTO d_b SELECT g, repeat('x', 100) FROM generate_series(1, 100000)
g;
CREATE INDEX ON d_b (x);
VACUUM ANALYZE d_a, d_b;
SELECT count(*) FROM d_a a JOIN d_b b ON a.x = b.x WHERE 5 > ALL (SELECT
b.x);
ERROR: plan should not reference subplan's variable
Expected: 400 (the result with enable_memoize = off, on 126976a8, and with
f8da64e6 reverted).
Cause: paraminfo_get_equal_hashops() now collects every PARAM_EXEC Param in
the inner relation's base quals and targetlist with
pull_exec_params_walker() and makes it a cache key. The walker descends into
SubPlans, so the Param in the SubPlan's testexpr (5 > $n), which stands for
the subplan's own output and is set by the subplan itself, also becomes a
cache key. The Memoize node then references a Param that only its child
sets, and finalize_plan() raises the error.
I will reply with a patch that, for a SubPlan, walks only its args (which
are evaluated in the memoized relation's context), with a regression test
in memoize.sql; make check passes.
Found with a grammar-based fuzzer for PostgreSQL.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19828: "plan should not reference subplan's variable" with Memoize since f8da64e6
@ 2026-11-02 16:08 Venu K <venu2805k@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Venu K @ 2026-11-02 16:08 UTC (permalink / raw)
To: venu2805k@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: guofenglinux@gmail.com <guofenglinux@gmail.com>
Hi,
Attached is a patch. pull_exec_params_walker() now walks only the args of a
SubPlan, which are evaluated in the memoized relation's context, and not its
testexpr, whose Params are set by the subplan itself. With the patch the
reproducer returns 400, and Memoize is still used (cache key a.x, with the
SubPlan as a filter on the inner scan).
The patch adds a regression test to memoize.sql (nested loop with Memoize
over tenk1 and 5 > ALL (SELECT t2.unique1)). make check passes (239/239).
Richard, Cc'ing you as the author of f8da64e6.
Regards,
Venu Kummari
On Mon, Nov 2, 2026 at 9:37 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19828
> Logged by: Venu Kummari
> Email address: venu2805k@gmail.com
> PostgreSQL version: 19rc1
> Operating system: Ubuntu 24.04, x86_64
> Description:
>
> PostgreSQL version: master (20devel) 633dee1b, built from source with
> assertions. Operating system: Ubuntu 24.04, x86_64.
>
> Since commit f8da64e6 ("Make cache keys of other Params a Memoize subplan
> depends on"), a correlated ALL/ANY sublink in the quals of a memoized
> relation makes planning fail, with default settings:
>
> CREATE TABLE d_a (x int NOT NULL);
> CREATE TABLE d_b (x int, pad text);
> INSERT INTO d_a SELECT g % 10 FROM generate_series(1, 1000) g;
> INSERT INTO d_b SELECT g, repeat('x', 100) FROM generate_series(1, 100000)
> g;
> CREATE INDEX ON d_b (x);
> VACUUM ANALYZE d_a, d_b;
>
> SELECT count(*) FROM d_a a JOIN d_b b ON a.x = b.x WHERE 5 > ALL (SELECT
> b.x);
> ERROR: plan should not reference subplan's variable
>
> Expected: 400 (the result with enable_memoize = off, on 126976a8, and with
> f8da64e6 reverted).
>
> Cause: paraminfo_get_equal_hashops() now collects every PARAM_EXEC Param in
> the inner relation's base quals and targetlist with
> pull_exec_params_walker() and makes it a cache key. The walker descends into
> SubPlans, so the Param in the SubPlan's testexpr (5 > $n), which stands for
> the subplan's own output and is set by the subplan itself, also becomes a
> cache key. The Memoize node then references a Param that only its child
> sets, and finalize_plan() raises the error.
>
> I will reply with a patch that, for a SubPlan, walks only its args (which
> are evaluated in the memoized relation's context), with a regression test
> in memoize.sql; make check passes.
>
> Found with a grammar-based fuzzer for PostgreSQL.
>
>
Attachments:
v1-0001-Dont-make-a-SubPlans-output-Params-Memoize-cache-keys.patch (application/octet-stream, 4.3K) download
From 6b7c82c4b3792a426ed8968669d4b7e995bba611 Mon Sep 17 00:00:00 2001
From: Venu Kummari <venu2805k@gmail.com>
Date: Mon, 2 Nov 2026 15:58:47 +0000
Subject: [PATCH] Don't make a SubPlan's output Params Memoize cache keys
Commit f8da64e6 makes cache keys of the PARAM_EXEC Params found in the
memoized relation's base quals and targetlist. pull_exec_params_walker()
also descended into SubPlans, so the Params in a SubPlan's testexpr, which
stand for the subplan's own output and are set by the subplan itself,
became cache keys too. The Memoize node then referenced a Param that only
its child sets, and finalize_plan() failed with "plan should not reference
subplan's variable", e.g. for
SELECT ... FROM a JOIN b ON a.x = b.x WHERE 5 > ALL (SELECT b.x);
with a nested loop and Memoize over b.
Only a SubPlan's args are evaluated in the memoized relation's context, so
look only at those.
---
src/backend/optimizer/path/joinpath.c | 10 ++++++++
src/test/regress/expected/memoize.out | 34 +++++++++++++++++++++++++++
src/test/regress/sql/memoize.sql | 12 ++++++++++
3 files changed, 56 insertions(+)
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index b690deb6..f25b7a22 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -697,6 +697,16 @@ pull_exec_params_walker(Node *node, List **params)
*params = lappend(*params, param);
return false;
}
+ if (IsA(node, SubPlan))
+ {
+ /*
+ * The Params in a SubPlan's testexpr stand for the subplan's own
+ * output, which it sets itself; they are not supplied from above, so
+ * they must not become cache keys. Only the args are evaluated in our
+ * context.
+ */
+ return pull_exec_params_walker((Node *) ((SubPlan *) node)->args, params);
+ }
return expression_tree_walker(node, pull_exec_params_walker, params);
}
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index 819cdf41..66f4f634 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -677,3 +677,37 @@ SELECT * FROM tab_anti t1 WHERE t1.a IN
(10 rows)
DROP TABLE tab_anti;
+-- A SubPlan in the memoized relation's quals sets the Params of its own
+-- output; they must not be made cache keys
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.twenty = t2.unique1
+WHERE 5 > ALL (SELECT t2.unique1) AND t1.unique1 < 1000;
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Bitmap Heap Scan on tenk1 t1
+ Recheck Cond: (unique1 < 1000)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 < 1000)
+ -> Memoize
+ Cache Key: t1.twenty
+ Cache Mode: logical
+ -> Index Only Scan using tenk1_unique1 on tenk1 t2
+ Index Cond: (unique1 = t1.twenty)
+ Filter: (ALL (5 > (SubPlan all_1).col1))
+ SubPlan all_1
+ -> Result
+(14 rows)
+
+SELECT COUNT(*) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.twenty = t2.unique1
+WHERE 5 > ALL (SELECT t2.unique1) AND t1.unique1 < 1000;
+ count
+-------
+ 250
+(1 row)
+
+RESET enable_hashjoin;
+RESET enable_mergejoin;
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index bf7ea5c0..db6ed228 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -332,3 +332,15 @@ SELECT * FROM tab_anti t1 WHERE t1.a IN
(SELECT t1.b FROM tab_anti t3 WHERE t2.a > 1 OFFSET 0));
DROP TABLE tab_anti;
+
+-- A SubPlan in the memoized relation's quals sets the Params of its own
+-- output; they must not be made cache keys
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.twenty = t2.unique1
+WHERE 5 > ALL (SELECT t2.unique1) AND t1.unique1 < 1000;
+SELECT COUNT(*) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.twenty = t2.unique1
+WHERE 5 > ALL (SELECT t2.unique1) AND t1.unique1 < 1000;
+RESET enable_hashjoin;
+RESET enable_mergejoin;
--
2.43.0
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-11-02 16:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-02 16:06 BUG #19828: "plan should not reference subplan's variable" with Memoize since f8da64e6 PG Bug reporting form <noreply@postgresql.org>
2026-11-02 16:08 ` Venu K <venu2805k@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox