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

view thread (3+ messages)  latest in thread

Message-ID: <c6790a7b-7c10-4baf-9c8f-b404420c63f9@soptimal.com>
Permalink:  ../c6790a7b-7c10-4baf-9c8f-b404420c63f9@soptimal.com/
Also on:    postgresql.org/message-id/c6790a7b-7c10-4baf-9c8f-b404420c63f9@soptimal.com

 · 

reply

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