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

Thank you for the quick reply, Julien,

I'd missed seeing that note in the CREATE docs. I also found an issue with my test block, in that when running through this, I must have inadvertently re-run the create collation statement in the newly created db, which is why it was available. I re-ran it as written just now, and got an error that I should have in the first place that the named collation doesn't exist for that db.

This will be frustrating to work around, since it'll require injecting COLLATE sub-clauses for all order by clauses (or like clauses, if we re-define our columns to use a non-deterministic collation).
Is support for non-deterministic collation at the db level on the roadmap?
Is there a query to determine what the actual/effective collation settings are (per the underlying provider) for a given db (since pg_database.daticulocale x pg_catalog.pg_collation.collname doesn't necessarily return what's going to be used)?

Thank you again,
Daniel

-----Original Message-----
From: Julien Rouhaud <rjuju123(at)gmail(dot)com>
Sent: Thursday, August 4, 2022 11:18 AM
To: Daniel Halsey <daniel(dot)halsey(at)vectorsolutions(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: 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://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F15%2Fsql-createdatabase.html&amp;data=05%7C01%7Cdaniel.halsey%40vectorsolutions.com%7Ceed514c046194c339cdc08da762c7eed%7Cfd01ebd7e586432592d27ad43688e011%7C0%7C0%7C637952230721474583%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=S32wfQ3r05xhhIirx2c7eEmvnn46G5KDBKR9%2FIfrTaU%3D&amp;reserved=0)

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.