Re: BUG #17571: PostgreSQL 15b2 ICU collation defined at db level sorts differently in collate clause vs implicit

Hi,

On Thu, Aug 04, 2022 at 02:56:32PM +0000, PG Bug reporting form wrote:
>
> Bug reference: 17571
> Logged by: Daniel Halsey
> Email address: daniel(dot)halsey(at)vectorsolutions(dot)com
> PostgreSQL version: Unsupported/Unknown
> Operating system: Debian (Docker PG15b2 Bullseye)
> Description:
>
> I'm using an "official" 15beta2 bullseye Docker image for this: When using a
> nondeterministic ICU collation as the default collation for a database,
> sorting (ORDER BY) without a COLLATE subclause acts differently than the
> same query with one.

This is working as expected. As mentioned in CREATE DATABASE documentation
(https://www.postgresql.org/docs/15/sql-createdatabase.html):

There is currently no option to use a database locale with nondeterministic
comparisons (see CREATE COLLATION for an explanation). If this is needed, then
per-column collations would need to be used.

And indeed:

> create collation if not exists "und-sorttest-x-icu"
> (provider=icu,
> locale='und-u-ks-level2-kc-false',
> deterministic=false);

This is an collation created in a specific database, and doesn't exist outside.

> create database sorttest with
> owner = postgres
> encoding = 'UTF8'
> locale_provider = 'icu'
> icu_locale = 'und-sorttest-x-icu'
> connection limit = -1
> template = template0;

Here "und-sorttest-x-icu "is just a string passed to the ICU library, which
probably understands it as the root collation, definitely not what you defined
in the other database.