From: Nidal FAKIH <nidal.fakih@soptimal.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
Date: Sun, 1 Nov 2026 10:27:43 +0100
Message-ID: <c6790a7b-7c10-4baf-9c8f-b404420c63f9@soptimal.com> (raw)
Hello,
I would like to report what looks like a wrong-result bug with presorted
aggregation when the GROUP BY column uses a nondeterministic collation
and the DISTINCT aggregate uses a different (deterministic) collation.
Version (SELECT version()):
PostgreSQL 18.3 on x86_64-windows, compiled by msvc-19.44.35223, 64-bit
System: Windows 11 x64. ICU collation provider. The script sets
max_parallel_workers_per_gather = 0 only to keep the plan simple; the
result is
the same without it.
_
Self-contained reproduction script:_
CREATE COLLATION ci_ai (provider = icu, locale = 'und-u-ks-level1',
deterministic = false);
CREATE TABLE t (x varchar(10) COLLATE ci_ai);
-- 60 rows: 'a' x20, 'A' x20, 'b' x20
INSERT INTO t SELECT (ARRAY['a','A','b'])[1 + i % 3] FROM
generate_series(0, 59) i;
SET max_parallel_workers_per_gather = 0;
SELECT x COLLATE "C" AS grp, count(DISTINCT x COLLATE "C") AS variants
FROM t GROUP BY x ORDER BY 1;
EXPLAIN (COSTS OFF)
SELECT count(DISTINCT x COLLATE "C") FROM t GROUP BY x;
SET enable_presorted_aggregate = off;
SELECT x COLLATE "C" AS grp, count(DISTINCT x COLLATE "C") AS variants
FROM t GROUP BY x ORDER BY 1;
RESET enable_presorted_aggregate;
DROP TABLE t;
DROP COLLATION ci_ai;
Result obtained:
grp | variants
-----+----------
a | 40
b | 1
Expected result: 'a' and 'A' are equal under ci_ai, so they form one
group, but they are two distinct values under "C":
grp | variants
-----+----------
a | 2
b | 1
Plan:
GroupAggregate
Group Key: x
-> Sort
Sort Key: x COLLATE ci_ai, x COLLATE "C"
-> Seq Scan on t
With SET enable_presorted_aggregate = off, the same query returns the
expected result (a = 2, b = 1).
The plan looks right: the input is sorted by (x COLLATE ci_ai, x COLLATE
"C").
So I suspect the presorted DISTINCT aggregate compares adjacent values
with the GROUP BY collation, or does not compare them at all, instead of
using theaggregate argument's collation "C". 40 is the number of rows in
the group ('a' and 'A'), so in effect every row is counted as a new
distinct value. With a different distribution of the same three values,
I got 41 in an earlier test, so the wrong count depends on the data.
Workaround: SET enable_presorted_aggregate = off, or compute the
distinct values in a subquery first, for example:
SELECT g, count(*) FROM (SELECT DISTINCT x AS g, x COLLATE "C" AS v
FROM t) s GROUP BY g;
Thank you for your work on PostgreSQL.
B.Regards
N.Fakih
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: nidal.fakih@soptimal.com
Subject: Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
In-Reply-To: <c6790a7b-7c10-4baf-9c8f-b404420c63f9@soptimal.com>
References: <c6790a7b-7c10-4baf-9c8f-b404420c63f9@soptimal.com>
* 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