agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Fix HAVING-to-WHERE pushdown with nondeterministic collations 2+ messages / 1 participants [nested] [flat]
* pgsql: Fix HAVING-to-WHERE pushdown with nondeterministic collations @ 2026-05-01 02:17 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Richard Guo @ 2026-05-01 02:17 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix HAVING-to-WHERE pushdown with nondeterministic collations When GROUP BY uses a nondeterministic collation, the planner's optimization of moving HAVING clauses to WHERE can produce incorrect query results. The HAVING clause may apply a stricter collation that distinguishes values the GROUP BY considers equal. Pushing such a clause to WHERE causes it to filter individual rows before grouping, potentially eliminating group members and changing aggregate results. Fix this by detecting collation conflicts before flatten_group_exprs, while the HAVING clause still contains GROUP Vars (Vars referencing RTE_GROUP). At that point, each GROUP Var directly carries the GROUP BY collation as its varcollid, making it straightforward to compare against the operator's inputcollid. A mismatch where the GROUP BY collation is nondeterministic means the clause is unsafe to push down. RowCompareExpr is treated specially, since it carries per-column inputcollids[] rather than a single inputcollid. The conflicting clause indices are recorded in a Bitmapset and consulted during the existing HAVING-to-WHERE loop, so that only affected clauses are kept in HAVING; other safe clauses in the same query are still pushed. Back-patch to v18 only. The fix relies on the RTE_GROUP mechanism introduced in v18 (commit 247dea89f), which is what lets us identify grouping expressions and their resolved collations via GROUP Vars on pre-flatten havingQual. Pre-v18 branches lack that machinery, so a back-patch there would need a different approach. Given the absence of field reports of this bug on back branches, the risk of carrying a different fix on stable branches is not justified. Author: Richard Guo <guofenglinux@gmail.com> Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com> Discussion: https://postgr.es/m/CAMbWs48Dn2wW6XM94GZsoyMiH42=KgMo+WcobPKuWvGYnWaPOQ@mail.gmail.com Backpatch-through: 18 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/f76686ce7f77e57456ad3644f2f6b4280181d1f4 Modified Files -------------- src/backend/optimizer/plan/planner.c | 188 ++++++++++++++++++++++ src/test/regress/expected/collate.icu.utf8.out | 213 +++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 66 ++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 468 insertions(+) ^ permalink raw reply [nested|flat] 2+ messages in thread
* pgsql: Fix HAVING-to-WHERE pushdown with nondeterministic collations @ 2026-05-01 02:17 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 2+ messages in thread From: Richard Guo @ 2026-05-01 02:17 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix HAVING-to-WHERE pushdown with nondeterministic collations When GROUP BY uses a nondeterministic collation, the planner's optimization of moving HAVING clauses to WHERE can produce incorrect query results. The HAVING clause may apply a stricter collation that distinguishes values the GROUP BY considers equal. Pushing such a clause to WHERE causes it to filter individual rows before grouping, potentially eliminating group members and changing aggregate results. Fix this by detecting collation conflicts before flatten_group_exprs, while the HAVING clause still contains GROUP Vars (Vars referencing RTE_GROUP). At that point, each GROUP Var directly carries the GROUP BY collation as its varcollid, making it straightforward to compare against the operator's inputcollid. A mismatch where the GROUP BY collation is nondeterministic means the clause is unsafe to push down. RowCompareExpr is treated specially, since it carries per-column inputcollids[] rather than a single inputcollid. The conflicting clause indices are recorded in a Bitmapset and consulted during the existing HAVING-to-WHERE loop, so that only affected clauses are kept in HAVING; other safe clauses in the same query are still pushed. Back-patch to v18 only. The fix relies on the RTE_GROUP mechanism introduced in v18 (commit 247dea89f), which is what lets us identify grouping expressions and their resolved collations via GROUP Vars on pre-flatten havingQual. Pre-v18 branches lack that machinery, so a back-patch there would need a different approach. Given the absence of field reports of this bug on back branches, the risk of carrying a different fix on stable branches is not justified. Author: Richard Guo <guofenglinux@gmail.com> Reviewed-by: wenhui qiu <qiuwenhuifx@gmail.com> Discussion: https://postgr.es/m/CAMbWs48Dn2wW6XM94GZsoyMiH42=KgMo+WcobPKuWvGYnWaPOQ@mail.gmail.com Backpatch-through: 18 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/e8fd5e579223f669245a8f7961c71b94afec2307 Modified Files -------------- src/backend/optimizer/plan/planner.c | 188 ++++++++++++++++++++++ src/test/regress/expected/collate.icu.utf8.out | 213 +++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 66 ++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 468 insertions(+) ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-05-01 02:17 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-05-01 02:17 pgsql: Fix HAVING-to-WHERE pushdown with nondeterministic collations Richard Guo <rguo@postgresql.org> 2026-05-01 02:17 pgsql: Fix HAVING-to-WHERE pushdown with nondeterministic collations Richard Guo <rguo@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox