From: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix pushdown of degenerate HAVING clauses
Date: Tue, 21 Oct 2025 03:39:59 +0000
Message-ID: <E1vB3E7-002i9E-01@gemulon.postgresql.org> (raw)
Fix pushdown of degenerate HAVING clauses
67a54b9e8 taught the planner to push down HAVING clauses even when
grouping sets are present, as long as the clause does not reference
any columns that are nullable by the grouping sets. However, there
was an oversight: if any empty grouping sets are present, the
aggregation node can produce a row that did not come from the input,
and pushing down a HAVING clause in this case may cause us to fail to
filter out that row.
Currently, non-degenerate HAVING clauses are not pushed down when
empty grouping sets are present, since the empty grouping sets would
nullify the vars they reference. However, degenerate (variable-free)
HAVING clauses are not subject to this restriction and may be
incorrectly pushed down.
To fix, explicitly check for the presence of empty grouping sets and
retain degenerate clauses in HAVING when they are present. This
ensures that we don't emit a bogus aggregated row. A copy of each
such clause is also put in WHERE so that query_planner() can use it in
a gating Result node.
To facilitate this check, this patch expands the groupingSets tree of
the query to a flat list of grouping sets before applying the HAVING
pushdown optimization. This does not add any additional planning
overhead, since we need to do this expansion anyway.
In passing, make a small tweak to preprocess_grouping_sets() by
reordering its initial operations a bit.
Backpatch to v18, where this issue was introduced.
Reported-by: Yuhang Qiu <iamqyh@gmail.com>
Author: Richard Guo <guofenglinux@gmail.com>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/0879D9C9-7FE2-4A20-9593-B23F7A0B5290@gmail.com
Backpatch-through: 18
Branch
------
REL_18_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/40c2428307b82d590f0caf73c93a5cdaf721e8b8
Modified Files
--------------
src/backend/optimizer/plan/planner.c | 67 +++++++++++++++---------
src/test/regress/expected/groupingsets.out | 82 +++++++++++++++++++++++++++++-
src/test/regress/sql/groupingsets.sql | 20 +++++++-
3 files changed, 143 insertions(+), 26 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 pushdown of degenerate HAVING clauses
In-Reply-To: <E1vB3E7-002i9E-01@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