Обсуждение: BUG #19425: Parametric settings in collation not working in rule syntax

The following bug has been logged on the website:

Bug reference:      19425
Logged by:          Dorian
Email address:      dorian.752@live.fr
PostgreSQL version: 18.3
Operating system:   Windows
Description:

Hello,

I want to create a custom collation with settings and tailoring rules, but I
ran into an issue.

When I create a custom collation, I first used collation settings in the
local, giving me something like this :
CREATE COLLATION IF NOT EXISTS und_ci_ai1(
    provider = icu,
    locale = 'und-u-ka-shifted-kc-false-ks-level1',
    deterministic = false
);

For instance, I can do this request and I will get the result '1' :
SELECT 1 WHERE 'adm' LIKE 'Adm' COLLATE und_ci_ai1;

But, I also want to add a tailoring rule. For the simplicity we will say
that I want to add 'p < q'
CREATE COLLATION IF NOT EXISTS und_ci_ai2(
    provider = icu,
    locale = 'und-u-ka-shifted-kc-false-ks-level1',
    deterministic = false,
    rules = $$
        & 'p' < 'q'
    $$
);

But, and this is my problem, the previous request doesn't seem to work
anymore. It doesn't return '1' :
SELECT 1 WHERE 'adm' LIKE 'Adm' COLLATE und_ci_ai2;

For this request to return '1', I have to add 'a' = 'A' like this :
rules = $$
        & 'p' < 'q'
        & 'a' = 'A'
$$

I didn't see in the documentation that I can't use settings in locale AND
tailoring rules at the same time. But just in case I tried to use parametric
settings in the rules syntax.
Unfortunately, it doesn't seem to work at all, the request still doesn't
return '1' :
CREATE COLLATION IF NOT EXISTS und_ci_ai3(
    provider = icu,
    locale = 'und',
    deterministic = false,
    rules = $$
        [strength 1]
        [caseLevel off]
    $$
);

SELECT 1 WHERE 'adm' LIKE 'Adm' COLLATE und_ci_ai3;

I hope I gave you all the infos you need. Thank you for your time.

Here the documentations I used :
https://www.postgresql.org/docs/current/collation.html#ICU-COLLATION-SETTINGS
https://www.unicode.org/reports/tr35/tr35-collation.html#Setting_Options
https://unicode-org.github.io/icu/userguide/collation/customization/

PG Bug reporting form wrote:

> But, I also want to add a tailoring rule. For the simplicity we will say
> that I want to add 'p < q'
> CREATE COLLATION IF NOT EXISTS und_ci_ai2(
>     provider = icu,
>     locale = 'und-u-ka-shifted-kc-false-ks-level1',
>     deterministic = false,
>     rules = $$
>         & 'p' < 'q'
>     $$
> );
>
> But, and this is my problem, the previous request doesn't seem to work
> anymore. It doesn't return '1' :

This looks the same as bug #18771 discussed at
https://www.postgresql.org/message-id/flat/18771-98bb23e455b0f367%40postgresql.org
and

https://www.postgresql.org/message-id/spHSrzQa0K_-Sqq9rNK-d6kelbfJG-z4XP6vn8tliiCHmjNYy45g2QOD92mrsNYqBpvj8Fi-qw4kXZhZmKjSVevzRSOvh6XzcNZBIV5wA3E=@protonmail.com

There's a simple fix available at
https://commitfest.postgresql.org/patch/6084/

Hopefully it will make it into the next major release, but so far it has been
lacking
reviews.
If you're able to compile Postgres, you may test the fix with your case and
report on
the results.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/