Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNpW6-00000002ljQ-3jQ9 for pgsql-bugs@arkaria.postgresql.org; Mon, 02 Nov 2026 10:43:55 +0000 Received: from localhost ([127.0.0.1]:52540 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xNpW5-00000002wm7-1QDk for pgsql-bugs@arkaria.postgresql.org; Mon, 02 Nov 2026 10:43:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:40320) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNRqu-0000000GcP7-2IEc for pgsql-bugs@lists.postgresql.org; Sun, 01 Nov 2026 09:27:48 +0000 Received: from spool-3.smtp-2.kalanda.net ([185.124.202.222]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xNRqs-000000023BZ-11BY for pgsql-bugs@lists.postgresql.org; Sun, 01 Nov 2026 09:27:48 +0000 Received: from ks7.kalanda.net (ks7.kalanda.net [185.43.44.108]) by spool-3.smtp-2.kalanda.net (Postfix) with ESMTPS id B4F702E022F for ; Sun, 01 Nov 2026 10:27:44 +0100 (CET) Received: from [192.168.1.24] ([81.250.232.192]) by ks7.kalanda.net (13.0.3 build 13 x64) with ASMTP id 202611011027449396 for ; Sun, 01 Nov 2026 10:27:44 +0100 Content-Type: multipart/alternative; boundary="------------ey5EPrLslxAp6HnmND1Vg3Iq" Message-ID: Date: Sun, 1 Nov 2026 10:27:43 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: fr To: pgsql-bugs@lists.postgresql.org From: Nidal FAKIH Subject: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation X-CTCH: RefID="str=0001.0A2D0309.6AE70612.0037,ss=1,re=0.000,recu=0.000,reip=0.000,cl=1,cld=1,fgs=0"; Spam="Unknown"; VOD="Unknown" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ey5EPrLslxAp6HnmND1Vg3Iq Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------ey5EPrLslxAp6HnmND1Vg3Iq Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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

--------------ey5EPrLslxAp6HnmND1Vg3Iq--