From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19797: Wrong results: HAVING pushdown past GROUP BY on composite column with nondeterministic collation
Date: Mon, 26 Oct 2026 09:30:01 +0000
Message-ID: <19797-7054aa017fa9d791@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19797
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux x86_64 (TencentOS 4.4), self-compiled, --wit
Description:
Hello,
When the grouped expression is a composite (record) column whose field
carries a nondeterministic
collation, a HAVING clause is pushed into WHERE and evaluated below the
aggregation. Grouping
equality is nondeterministic in that case, so the clause can remove members
of a group before
aggregation and change count(*).
The pushdown safety check only inspects the top-level varcollid of the
grouping Var. A composite
column has varcollid = InvalidOid (record is not sortable), so the check is
skipped -- yet record_eq
compares fields using each field's declared collation, which may be
nondeterministic.
Test case (self-contained):
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2',
deterministic = false);
CREATE TYPE t_pair AS (f text COLLATE ci);
CREATE TABLE t_rec2 (r t_pair, n int);
INSERT INTO t_rec2 VALUES (row('foo')::t_pair, 1), (row('FOO')::t_pair,
2);
ANALYZE t_rec2;
-- reference: the group {foo, FOO} has size 2
SELECT r, count(*) FROM t_rec2 GROUP BY r;
-- pushed down -> wrong
SELECT r, count(*) FROM t_rec2 GROUP BY r HAVING (r).f = 'foo' COLLATE
"C";
-- not pushable (contains an aggregate) -> correct
SELECT r, count(*) FROM t_rec2 GROUP BY r HAVING (r).f = 'foo' COLLATE
"C" OR count(*) = 99;
Actual output (18.6):
r | count
-------+-------
(foo) | 2 <- reference
r | count
-------+-------
(foo) | 1 <- pushed down
r | count
-------+-------
(foo) | 2 <- not pushable
Expected: the second query returns 2, or no row at all. The group is {foo,
FOO} under the field
collation, so its size is fixed at 2; evaluating the HAVING clause against
the group's
representative row can only yield (representative, 2) or nothing. 1 is not
reachable.
Plan of the second query on 18.6 (filter below the aggregate):
HashAggregate
Group Key: r
-> Seq Scan on t_rec2
Filter: ((r).f = 'foo'::text COLLATE "C")
Source: in 18.4 this is having_collation_conflict_walker() in planner.c
(~line 1517):
OidIsValid(var->varcollid) &&
!get_collation_isdeterministic(var->varcollid).
On current REL_18_STABLE that walker has been replaced by the shared
expression_has_grouping_conflict() (clauses.c), introduced by fe5d6295
(2026-07-06, "Fix qual
pushdown past grouping with mismatched equivalence", backpatched to 18), but
the new walker keeps
the same precondition (clauses.c:5606-5608):
if (OidIsValid(ctx->get_eqop(var, ctx->cb_context)) &&
OidIsValid(var->varcollid) &&
!get_collation_isdeterministic(var->varcollid))
so a composite grouping column is still not recognised as having
nondeterministic equality.
Notes:
- fe5d6295 does fix the same class of problem for ordinary columns. I
confirmed on 18.6 that the
related cases it targets now behave correctly: HAVING length(x) = 2 over a
ci grouping (returns
count = 3, matching the non-pushable form) and qual pushdown past window
PARTITION BY / DISTINCT ON
(window count 2 instead of 1; DISTINCT ON 0 rows instead of one wrong
row). Composite columns
appear to be the remaining gap.
- I have not checked branches 14-17, nor master.
Thanks,
chunling qin
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, 303677365@qq.com
Subject: Re: BUG #19797: Wrong results: HAVING pushdown past GROUP BY on composite column with nondeterministic collation
In-Reply-To: <19797-7054aa017fa9d791@postgresql.org>
References: <19797-7054aa017fa9d791@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