pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
Date: Thu, 05 Nov 2026 09:39:38 +0000
Message-ID: <19834-234b62f9071e6fd8@postgresql.org> (raw)

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.








view thread (5+ messages)  latest in thread

Message-ID: <19834-234b62f9071e6fd8@postgresql.org>
Permalink:  ../19834-234b62f9071e6fd8@postgresql.org/
Also on:    postgresql.org/message-id/19834-234b62f9071e6fd8@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl
  In-Reply-To: <19834-234b62f9071e6fd8@postgresql.org>
  References: <19834-234b62f9071e6fd8@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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