Re: Wrong result: count(DISTINCT x COLLATE "C") with GROUP BY x on a nondeterministic collation

On Mon, 2 Nov 2026 at 23:44, Nidal FAKIH <nidal(dot)fakih(at)soptimal(dot)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