pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix pushdown of degenerate HAVING clauses 2+ messages / 1 participants [nested] [flat]
* pgsql: Fix pushdown of degenerate HAVING clauses @ 2025-10-21 03:36 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Richard Guo @ 2025-10-21 03:36 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 ------ master Details ------- https://git.postgresql.org/pg/commitdiff/18d2614093481cb7ae89e1f5b72f3ddf5fb49b4c 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(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Fix pushdown of degenerate HAVING clauses @ 2025-10-21 03:39 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Richard Guo @ 2025-10-21 03:39 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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(-) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-10-21 03:39 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-21 03:36 pgsql: Fix pushdown of degenerate HAVING clauses Richard Guo <rguo@postgresql.org> 2025-10-21 03:39 pgsql: Fix pushdown of degenerate HAVING clauses Richard Guo <rguo@postgresql.org>
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