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

От:
Nidal FAKIH <nidal.fakih@soptimal.com>
Дата:

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

От:
David Rowley <dgrowleyml@gmail.com>
Дата:
Вложения:
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

От:
David Rowley <dgrowleyml@gmail.com>
Дата:
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


FAQ