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.halsey@vectorsolutions.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.