Peter Eisentraut wrote:
> Here is an updated patch.
When using GROUP BY and ORDER BY on a field with a non-deterministic
collation, this pops out:
CREATE COLLATION myfr (locale='fr-u-ks-level1',
provider='icu', deterministic=false);
=# select n from (values ('été' collate "myfr"), ('ete')) x(n)
group by 1 order by 1 ;
n
-----
ete
(1 row)
=# select n from (values ('été' collate "myfr"), ('ete')) x(n)
group by 1 order by 1 desc;
n
-----
été
(1 row)
The single-row output is different whether it's sorted in the ASC or
DESC direction, even though in theory, ORDER BY is done after GROUP
BY, where it shouldn't make that difference.
EXPLAIN shows that the sort is done before grouping, which might
explain why it happens, but isn't that plan incorrect given the context?
postgres=# explain select n from (values ('été' collate "myfr"), ('ete'))
x(n)
group by 1 order by 1 desc;
QUERY PLAN
--------------------------------------------------------------------------
Group (cost=0.04..0.04 rows=2 width=32)
Group Key: "*VALUES*".column1
-> Sort (cost=0.04..0.04 rows=2 width=32)
Sort Key: "*VALUES*".column1 COLLATE myfr DESC
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=32)
(5 rows)
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite