From: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix unsafe pushdown of quals referencing grouping Vars
Date: Mon, 19 Jan 2026 02:13:51 +0000
Message-ID: <E1vhem6-001I0o-16@gemulon.postgresql.org> (raw)
Fix unsafe pushdown of quals referencing grouping Vars
When checking a subquery's output expressions to see if it's safe to
push down an upper-level qual, check_output_expressions() previously
treated grouping Vars as opaque Vars. This implicitly assumed they
were stable and scalar.
However, a grouping Var's underlying expression corresponds to the
grouping clause, which may be volatile or set-returning. If an
upper-level qual references such an output column, pushing it down
into the subquery is unsafe. This can cause strange results due to
multiple evaluation of a volatile function, or introduce SRFs into
the subquery's WHERE/HAVING quals.
This patch teaches check_output_expressions() to look through grouping
Vars to their underlying expressions. This ensures that any
volatility or set-returning properties in the grouping clause are
detected, preventing the unsafe pushdown.
We do not need to recursively examine the Vars contained in these
underlying expressions. Even if they reference outputs from
lower-level subqueries (at any depth), those references are guaranteed
not to expand to volatile or set-returning functions, because
subqueries containing such functions in their targetlists are never
pulled up.
Backpatch to v18, where this issue was introduced.
Reported-by: Eric Ridge <eebbrr@gmail.com>
Diagnosed-by: Tom Lane <tgl@sss.pgh.pa.us>
Author: Richard Guo <guofenglinux@gmail.com>
Discussion: https://postgr.es/m/7900964C-F99E-481E-BEE5-4338774CEB9F@gmail.com
Backpatch-through: 18
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/34740b90bc123d645a3a71231b765b778bdcf049
Modified Files
--------------
src/backend/optimizer/path/allpaths.c | 26 +++++++-
src/backend/optimizer/util/var.c | 8 ++-
src/test/regress/expected/subselect.out | 104 ++++++++++++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 40 ++++++++++++
4 files changed, 175 insertions(+), 3 deletions(-)
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 using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: rguo@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix unsafe pushdown of quals referencing grouping Vars
In-Reply-To: <E1vhem6-001I0o-16@gemulon.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