Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xOxTo-00000000Q8H-29Io for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Nov 2026 13:26:13 +0000 Received: from localhost ([127.0.0.1]:44152 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xOxTn-0000000CYMA-48Ny for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Nov 2026 13:26:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:44538) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xOtx4-0000000BoK0-01MO for pgsql-bugs@lists.postgresql.org; Thu, 05 Nov 2026 09:40:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xOtx0-00000000Nha-1Ol0 for pgsql-bugs@lists.postgresql.org; Thu, 05 Nov 2026 09:40:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=Bxr+AhigtAibKh8NrFXRcyg1fI5UEj2Gys58AUgspnI=; b=aHyC0JAmAjbKbEOKLF8h1UB15D YYL8JArV22u0jkZgCEcCNiWoXqTck8kVdu+RK8fu8Ag6NbMnDPXd7B8JQBlifTgBzvyaCimhtCL78 djYt9gMYhc+Fe9bTAo8amWn3wKdrwg10/j9T7WCfNOiUBZ7X5u4wfh6aIsdOOk+mrwR827bEsA+up eVqV+fg3WW6+ZjuRm2YQ3psE3QQjdk2w4eIMN8QCka8LZZBwjaAPpc3Frk8aDkFe4wDFQZtLVPEhk h4xSAscq/iPsfVDERLwzVpf7WVXDhI+BOfD2RBhfF8kHzPYQ2x1i8PH/rAGf7MBQhdBEO8MlbTlLX hkQ2c4Mw==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xOtwx-00000000xtG-23ei for pgsql-bugs@lists.postgresql.org; Thu, 05 Nov 2026 09:40:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xOtww-00000002Usi-3GFP for pgsql-bugs@lists.postgresql.org; Thu, 05 Nov 2026 09:40:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19834: eager aggregation still evaluates volatile join clauses when the volatile function is inside a SubPl To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Thu, 05 Nov 2026 09:39:38 +0000 Message-ID: <19834-234b62f9071e6fd8@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 **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 =3D on; SELECT count(*) FROM e1 JOIN e2 ON e1.a =3D e2.a AND nextval('s')%2 =3D 0; -- volatile INSIDE a correlated SubPlan: eager aggregation is applied anyway SET enable_eager_aggregate =3D on; SELECT count(*) FROM e1 JOIN e2 ON e1.a =3D e2.a AND (SELECT nextval('s')%2= ) =3D 0; SET enable_eager_aggregate =3D off; SELECT count(*) FROM e1 JOIN e2 ON e1.a =3D e2.a AND (SELECT nextval('s')%2= ) =3D 0; ``` **Actual behavior** (observable via `SELECT last_value FROM s` after each statement, or `EXPLAIN (ANALYZE)` showing `Partial HashAggregate` and `Join Filter ... loops=3D9`): - 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 =E2=80=94 including via a SubPlan =E2=80=94 should suppress eager= aggregation below that join, same as the direct form.