pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Wrong 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