Fix HAVING-to-WHERE pushdown with nondeterministic collations

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Fix HAVING-to-WHERE pushdown with nondeterministic collations
Date: 2026-03-31 03:41:09
Message-ID: CAMbWs48Dn2wW6XM94GZsoyMiH42=KgMo+WcobPKuWvGYnWaPOQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Fix-HAVING-to-WHERE-pushdown-with-nondeterministi.patch application/octet-stream 20.2 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Hayato Kuroda (Fujitsu) 2026-03-31 03:54:37 RE: Skipping schema changes in publication
Previous Message David Rowley 2026-03-31 02:16:59 Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)