pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedWrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
3+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2026-11-04 1c2ba4df37 bfbb3d8420 9171fca990 0500fb3ab2 6e86051162 b715192e52 Fix accidental ignoring of subsequent keys in Sort nodes
* Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
@ 2026-11-01 09:27 Nidal FAKIH <nidal.fakih@soptimal.com>
2026-11-04 09:34 ` Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nidal FAKIH @ 2026-11-01 09:27 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
2026-11-01 09:27 Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation Nidal FAKIH <nidal.fakih@soptimal.com>
@ 2026-11-04 09:34 ` David Rowley <dgrowleyml@gmail.com>
2026-11-04 21:33 ` Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2026-11-04 09:34 UTC (permalink / raw)
To: Nidal FAKIH <nidal.fakih@soptimal.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Mon, 2 Nov 2026 at 23:44, Nidal FAKIH <nidal.fakih@soptimal.com> 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
Attachments:
draft_fix.patch (application/octet-stream, 738B) download
diff --git a/src/backend/executor/nodeSort.c b/src/backend/executor/nodeSort.c
index 2398bdabf0d..00d65a53aca 100644
--- a/src/backend/executor/nodeSort.c
+++ b/src/backend/executor/nodeSort.c
@@ -263,10 +263,10 @@ ExecInitSort(Sort *node, EState *estate, int eflags)
outerTupDesc = ExecGetResultType(outerPlanState(sortstate));
/*
- * We perform a Datum sort when we're sorting just a single column,
- * otherwise we perform a tuple sort.
+ * We perform a Datum sort when we're sorting just a single column by a
+ * single key, otherwise we perform a tuple sort.
*/
- if (outerTupDesc->natts == 1)
+ if (outerTupDesc->natts == 1 && node->numCols == 1)
sortstate->datumSort = true;
else
sortstate->datumSort = false;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation
2026-11-01 09:27 Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation Nidal FAKIH <nidal.fakih@soptimal.com>
2026-11-04 09:34 ` Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation David Rowley <dgrowleyml@gmail.com>
@ 2026-11-04 21:33 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: David Rowley @ 2026-11-04 21:33 UTC (permalink / raw)
To: Nidal FAKIH <nidal.fakih@soptimal.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Wed, 4 Nov 2026 at 22:34, David Rowley <dgrowleyml@gmail.com> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-11-04 21:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-01 09:27 Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation Nidal FAKIH <nidal.fakih@soptimal.com>
2026-11-04 09:34 ` David Rowley <dgrowleyml@gmail.com>
2026-11-04 21:33 ` David Rowley <dgrowleyml@gmail.com>
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