Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
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
Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
On Mon, 2 Nov 2026 at 23:44, Nidal FAKIH wrote:
> 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).
Thanks for the report. I had suspected this was an issue with the
presorted aggregate code, but it's not.
I looked at the results of "select x from t order by t.x COLLATE
ci_ai, t.x COLLATE "C";" and the order of those rows wasn't the same
order as what I was seeing on your test query while watching the
"value" variable after hitting the breakpoint I had in
ExecEvalPreOrderedDistinctSingle(). I was seeing the values
alternating between "A" and "a", whereas, confusingly, that's not what
the plain select query outputs.
As it turns out, the bug is in nodeSort.c. The Datum sort code path
thinks it's ok to use a Datum sort when there's only 1 column. You
might assume only 1 sort key actually matters when there's only 1
column since both sort keys must be on that column, but that's not
true when the first sort key is non-deterministic.
The code change required is basically:
- if (outerTupDesc->natts == 1)
+ if (outerTupDesc->natts == 1 && node->numCols == 1)
sortstate->datumSort = true;
else
sortstate->datumSort = false;
Looking at the explain verbose for "select x from t order by t.x
COLLATE ci_ai, t.x COLLATE "C";" shows the Seq Scan has "Output: x, x,
x", which is why that query didn't highlight the sorting issue.
There's 3 output columns, so no Datum sort. I guess the planner added
the duplicate Vars because it thought it needed the two resjunk
columns for the ORDER BY keys due to the Vars not comparing as equal
because of the varying collation.
See attached for a draft patch. I'll look at committing this tomorrow
as it's a bit late here right now.
David
Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
On Wed, 4 Nov 2026 at 22:34, David Rowley wrote: > See attached for a draft patch. I'll look at committing this tomorrow > as it's a bit late here right now. Now pushed and backpatched to v15. v15 did limit the Datum sort to only byval columns, and I don't believe we have any built-in byval types that could have caused issues here, but I pushed the fix to v15 anyway, as it might not be impossible for someone to dream up a custom type that's collatable and byval. v15 also doesn't have the presorted aggregate optimisation, so it might not be affected at all, depending on if that's the only method of triggering this. Thanks again for the report. David