pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
5+ messages / 3 participants
[nested] [flat]

* BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
@ 2026-11-05 09:39 PG Bug reporting form <noreply@postgresql.org>
  2026-11-09 05:16 ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl shihao zhong <zhong950419@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: PG Bug reporting form @ 2026-11-05 09:39 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com

The following bug has been logged on the website:

Bug reference:      19834
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 19rc1
Operating system:   Linux x86_64; platform-independent (reproduced on
Description:        

**Description:**

Commit 27c844034f8 made eager aggregation refuse to push partial aggregation
below a join clause that contains a volatile function. The guard uses
`contain_volatile_functions()`, which deliberately does **not** recurse into
SubPlan nodes. A volatile function inside a *correlated SubPlan* in the join
clause therefore still escapes the guard: eager aggregation is applied, the
join filter (and the SubPlan, and the volatile function) is evaluated once
per group instead of once per joined row, changing both the number of side
effects and the query result.

**Reproduction (self-contained SQL):**

```sql
CREATE TABLE e1(a int, b int);
CREATE TABLE e2(a int, b int);
INSERT INTO e1 SELECT g%10, g FROM generate_series(1,1000) g;
INSERT INTO e2 SELECT g%10, g FROM generate_series(1,1000) g;
ANALYZE e1; ANALYZE e2;
CREATE SEQUENCE s;

-- volatile DIRECTLY in the join clause: fix works, no eager aggregation,
900 evaluations
SET enable_eager_aggregate = on;
SELECT count(*) FROM e1 JOIN e2 ON e1.a = e2.a AND nextval('s')%2 = 0;

-- volatile INSIDE a correlated SubPlan: eager aggregation is applied anyway
SET enable_eager_aggregate = on;
SELECT count(*) FROM e1 JOIN e2 ON e1.a = e2.a AND (SELECT nextval('s')%2) =
0;

SET enable_eager_aggregate = off;
SELECT count(*) FROM e1 JOIN e2 ON e1.a = e2.a AND (SELECT nextval('s')%2) =
0;
```

**Actual behavior** (observable via `SELECT last_value FROM s` after each
statement, or `EXPLAIN (ANALYZE)` showing `Partial HashAggregate` and `Join
Filter ... loops=9`):

- direct volatile form: join filter evaluated 900 times (fix works);
- SubPlan form with eager aggregation **on**: join filter/SubPlan evaluated
**9 times** (once per group after partial aggregation);
- SubPlan form with eager aggregation **off**: 900 times.

So the same query silently returns a different result and performs a
different number of volatile side effects depending on the (default-on)
eager aggregation setting, and the count of side effects is wrong by 100x in
this example.

**Root cause:** the volatile check for eager aggregation
(src/backend/optimizer/util/relnode.c, guard added by 27c844034f8) uses
`contain_volatile_functions()`, which does not descend into SubPlan nodes.

**Expected behavior:** a join clause whose evaluation can invoke volatile
functions — including via a SubPlan — should suppress eager aggregation
below that join, same as the direct form.








^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
  2026-11-05 09:39 BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-09 05:16 ` shihao zhong <zhong950419@gmail.com>
  2026-11-11 21:08   ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: shihao zhong @ 2026-11-09 05:16 UTC (permalink / raw)
  To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org; +Cc: Guofenglinux <guofenglinux@gmail.com>

Thanks for the report. This follows up on [1], where we fixed volatile
join clauses. contain_volatile_functions() does not look into SubPlans,
so a volatile function in a correlated sub-select still gets through.

The query in the report does not show it, because its sub-select is
uncorrelated and runs once as an initplan.

The attached patch also refuses SubPlans, like we do when moving HAVING
to WHERE. I think it should go back to 19.

[1]
https://postgr.es/m/CAGRkXqSc_qtpuPm34qoS1z0LVx4+Hc+JcJkbDEVC=z2ai4VQ1w@mail.gmail.com

Thanks,
Shihao

Attachments:

v1-0001-Fix-eager-aggregation-with-volatile-functions-ins.patch (application/octet-stream, 5.3K) download From a23e9a1b73bcc1f1b26f11556f1a06a65816020b Mon Sep 17 00:00:00 2001 From: shihao zhong <zhong950419@gmail.com> Date: Sun, 8 Nov 2026 23:43:27 -0500 Subject: [PATCH v1] Fix eager aggregation with volatile functions inside SubPlans Commits 3a08a2a8b4f and 27c844034f8 keep eager aggregation away from aggregates and join clauses that contain volatile functions. But contain_volatile_functions() does not look into SubPlans, so a volatile function in a correlated sub-select was missed, and it could be evaluated once per group instead of once per row. Fix by also refusing SubPlans in both places, as we already do before moving HAVING clauses to WHERE. Back-patch to v19, where eager aggregation was introduced. Reported-by: chunling qin <303677365@qq.com> Author: shihao zhong <zhong950419@gmail.com> Discussion: https://postgr.es/m/19834-234b62f9071e6fd8@postgresql.org Backpatch-through: 19 --- src/backend/optimizer/plan/initsplan.c | 6 ++- src/backend/optimizer/util/relnode.c | 6 ++- src/test/regress/expected/eager_aggregate.out | 38 +++++++++++++++++++ src/test/regress/sql/eager_aggregate.sql | 13 +++++++ 4 files changed, 59 insertions(+), 4 deletions(-) diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index cec76342f6e..5c4f8828b71 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -803,9 +803,11 @@ create_agg_clause_infos(PlannerInfo *root) /* * We cannot push down aggregates that contain volatile functions. * Doing so would change the number of times the function is - * evaluated. + * evaluated. Also refuse SubPlans, which may hide volatile + * functions. */ - if (contain_volatile_functions((Node *) aggref)) + if (contain_volatile_functions((Node *) aggref) || + contain_subplans((Node *) aggref)) { eager_agg_applicable = false; break; diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index 90a687c32da..8b4d00a0839 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -3049,13 +3049,15 @@ eager_aggregation_possible_for_relation(PlannerInfo *root, RelOptInfo *rel) /* * We cannot push a partial aggregation down below a join clause that * contains volatile functions, because the clause would then be evaluated - * once per group rather than once per input row. + * once per group rather than once per input row. Also refuse SubPlans, + * which may hide volatile functions. */ foreach(lc, rel->joininfo) { RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc); - if (contain_volatile_functions((Node *) rinfo)) + if (contain_volatile_functions((Node *) rinfo) || + contain_subplans((Node *) rinfo->clause)) return false; } diff --git a/src/test/regress/expected/eager_aggregate.out b/src/test/regress/expected/eager_aggregate.out index bdda7da68f0..725f7b52e55 100644 --- a/src/test/regress/expected/eager_aggregate.out +++ b/src/test/regress/expected/eager_aggregate.out @@ -486,6 +486,44 @@ GROUP BY t1.a ORDER BY t1.a; -> Seq Scan on eager_agg_t1 t1 (10 rows) +-- Ensure eager aggregation is not applied when a SubPlan hides a volatile +-- function +EXPLAIN (COSTS OFF) +SELECT t1.a, avg((SELECT t2.c + random())) + FROM eager_agg_t1 t1 JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a; + QUERY PLAN +----------------------------------------------- + HashAggregate + Group Key: t1.a + -> Hash Join + Hash Cond: (t2.b = t1.b) + -> Seq Scan on eager_agg_t2 t2 + -> Hash + -> Seq Scan on eager_agg_t1 t1 + SubPlan expr_1 + -> Result +(9 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 JOIN eager_agg_t2 t2 + ON t1.b = t2.b AND t1.c > (SELECT t2.b * random()) +GROUP BY t1.a; + QUERY PLAN +------------------------------------------------ + HashAggregate + Group Key: t1.a + -> Hash Join + Hash Cond: (t2.b = t1.b) + Join Filter: (t1.c > (SubPlan expr_1)) + -> Seq Scan on eager_agg_t2 t2 + -> Hash + -> Seq Scan on eager_agg_t1 t1 + SubPlan expr_1 + -> Result +(10 rows) + -- Eager aggregation must not push a partial aggregate onto the inner side of a -- SEMI or ANTI join EXPLAIN (VERBOSE, COSTS OFF) diff --git a/src/test/regress/sql/eager_aggregate.sql b/src/test/regress/sql/eager_aggregate.sql index ab3592b67dd..00f9c6fa2ec 100644 --- a/src/test/regress/sql/eager_aggregate.sql +++ b/src/test/regress/sql/eager_aggregate.sql @@ -184,6 +184,19 @@ SELECT t1.a, avg(t2.c) JOIN eager_agg_t2 t2 ON t1.b = t2.b AND t1.c > t2.b * random() GROUP BY t1.a ORDER BY t1.a; +-- Ensure eager aggregation is not applied when a SubPlan hides a volatile +-- function +EXPLAIN (COSTS OFF) +SELECT t1.a, avg((SELECT t2.c + random())) + FROM eager_agg_t1 t1 JOIN eager_agg_t2 t2 ON t1.b = t2.b +GROUP BY t1.a; + +EXPLAIN (COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 JOIN eager_agg_t2 t2 + ON t1.b = t2.b AND t1.c > (SELECT t2.b * random()) +GROUP BY t1.a; + -- Eager aggregation must not push a partial aggregate onto the inner side of a -- SEMI or ANTI join EXPLAIN (VERBOSE, COSTS OFF) -- 2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
  2026-11-05 09:39 BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl PG Bug reporting form <noreply@postgresql.org>
  2026-11-09 05:16 ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl shihao zhong <zhong950419@gmail.com>
@ 2026-11-11 21:08   ` Tom Lane <tgl@sss.pgh.pa.us>
  2026-11-12 05:05     ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl shihao zhong <zhong950419@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2026-11-11 21:08 UTC (permalink / raw)
  To: shihao zhong <zhong950419@gmail.com>; +Cc: 303677365@qq.com, pgsql-bugs@lists.postgresql.org, Guofenglinux <guofenglinux@gmail.com>

shihao zhong <zhong950419@gmail.com> writes:
> Thanks for the report. This follows up on [1], where we fixed volatile
> join clauses. contain_volatile_functions() does not look into SubPlans,
> so a volatile function in a correlated sub-select still gets through.
> The query in the report does not show it, because its sub-select is
> uncorrelated and runs once as an initplan.
> The attached patch also refuses SubPlans, like we do when moving HAVING
> to WHERE. I think it should go back to 19.

I do not think this is a bug, so I don't think we should limit query
optimization to work around it (especially in such a brute-force way).
We have traditionally understood that a sub-select can be used to hide
or disable the volatility of a function.  For example, these clauses
behave quite differently:

	WHERE x = random()
	WHERE x = (SELECT random())

So the fact that contain_volatile_functions() doesn't descend into
SubPlans is intentional, and subverting that intention in just one
place does not seem to me like a good idea.

Maybe there is something to be done here, but this bug report shows
no awareness of that context.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
  2026-11-05 09:39 BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl PG Bug reporting form <noreply@postgresql.org>
  2026-11-09 05:16 ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl shihao zhong <zhong950419@gmail.com>
  2026-11-11 21:08   ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-11-12 05:05     ` shihao zhong <zhong950419@gmail.com>
  2026-11-12 15:22       ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: shihao zhong @ 2026-11-12 05:05 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 303677365@qq.com, pgsql-bugs@lists.postgresql.org, Guofenglinux <guofenglinux@gmail.com>

Hi Tom,

> For example, these clauses behave quite differently:

If I understand correctly, you mean the following case,

A table t with 100 rows,

    SELECT count(*) FROM t WHERE (SELECT random()) < 0.5;

returns either 100 or 0, because the sub-select runs only once.

I think technically that is one of the correct answers....It is just a
result the caller would almost never see otherwise.

I am okay to revert the patch..

Thanks,
Shihao

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
  2026-11-05 09:39 BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl PG Bug reporting form <noreply@postgresql.org>
  2026-11-09 05:16 ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl shihao zhong <zhong950419@gmail.com>
  2026-11-11 21:08   ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl Tom Lane <tgl@sss.pgh.pa.us>
  2026-11-12 05:05     ` Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl shihao zhong <zhong950419@gmail.com>
@ 2026-11-12 15:22       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2026-11-12 15:22 UTC (permalink / raw)
  To: shihao zhong <zhong950419@gmail.com>; +Cc: 303677365@qq.com, pgsql-bugs@lists.postgresql.org, Guofenglinux <guofenglinux@gmail.com>

shihao zhong <zhong950419@gmail.com> writes:
> If I understand correctly, you mean the following case,
> A table t with 100 rows,
>     SELECT count(*) FROM t WHERE (SELECT random()) < 0.5;
> returns either 100 or 0, because the sub-select runs only once.

Right.

> I think technically that is one of the correct answers....It is just a
> result the caller would almost never see otherwise.
> I am okay to revert the patch..

To be clear, I'm fine with 27c844034 as it stands.  I just don't think
it'd be consistent with our past practice to extend the volatility
check into subplans.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2026-11-12 15:22 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-05 09:39 BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl PG Bug reporting form <noreply@postgresql.org>
2026-11-09 05:16 ` shihao zhong <zhong950419@gmail.com>
2026-11-11 21:08   ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-12 05:05     ` shihao zhong <zhong950419@gmail.com>
2026-11-12 15:22       ` Tom Lane <tgl@sss.pgh.pa.us>

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