Fix HAVING-to-WHERE pushdown with nondeterministic collations
Richard Guo <guofenglinux@gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-03-31T03:41:09Z
Lists: pgsql-hackers
Attachments
- v1-0001-Fix-HAVING-to-WHERE-pushdown-with-nondeterministi.patch (application/octet-stream) patch v1-0001
As briefly discussed on Discord, when a GROUP BY clause uses a
nondeterministic collation, the planner's optimization of moving
HAVING clauses to WHERE can produce incorrect results if the HAVING
clause applies a stricter collation.
CREATE TABLE t (x TEXT COLLATE case_insensitive);
INSERT INTO t VALUES ('a'), ('A');
SELECT x, count(*) FROM t GROUP BY x HAVING x = 'a' COLLATE "C";
This returns count=1, but should return count=2.
The attached draft patch fixes this for HEAD by leveraging GROUP Vars
(Vars referencing RTE_GROUP) to detect collation conflicts on a
per-clause basis, so only unsafe clauses are kept in HAVING while safe
ones are still pushed. Please see the commit message for more
details.
For versions prior to v18, we do not have GROUP Vars. I wonder if we
can take a conservative approach: skipping the HAVING-to-WHERE
pushdown optimization entirely if any GROUP BY expression uses a
nondeterministic collation.
Thoughts and reviews are welcome.
- Richard
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Fix HAVING-to-WHERE pushdown for simple-CASE form
- 1132af22cf7d 18.4 landed
- ba82de48e62c 19 (unreleased) landed
-
Fix HAVING-to-WHERE pushdown with nondeterministic collations
- e8fd5e579223 18.4 landed
- f76686ce7f77 19 (unreleased) landed