BUG #19045: Applying custom collation rules appears to erase existing rules

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: todd(dot)lang(at)d2l(dot)com
Subject: BUG #19045: Applying custom collation rules appears to erase existing rules
Date: 2025-09-09 15:13:41
Message-ID: 19045-eef31bf1c807b524@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19045
Logged by: Todd Lang
Email address: todd(dot)lang(at)d2l(dot)com
PostgreSQL version: 17.6
Operating system: Windows 10 64 bit
Description:

Setting up a collation on a table with the following:

DROP TABLE IF EXISTS test_table;
DROP COLLATION IF EXISTS CI_AS;
CREATE COLLATION CI_AS (PROVIDER=icu, LOCALE='en-US-u-ks-level2',
DETERMINISTIC=false);
CREATE TABLE test_table (field1 varchar(256) COLLATE CI_AS);
INSERT INTO test_table VALUES (U&'this is a string.');
INSERT INTO test_table VALUES (U&'THIS IS A STRING.');

Then issue the query:
SELECT * FROM test_table WHERE field1 = 'This is a string.';

This should provide:
"this is a string."
"THIS IS A STRING."

Now alter the collation slightly to include rules. (Note the CREATE
COLLATION line)

DROP TABLE IF EXISTS test_table;
DROP COLLATION IF EXISTS CI_AS;
CREATE COLLATION CI_AS (PROVIDER=icu, LOCALE='en-US-u-ks-level2',
DETERMINISTIC=false, rules='');
CREATE TABLE test_table (field1 varchar(256) COLLATE CI_AS);
INSERT INTO test_table VALUES (U&'this is a string.');
INSERT INTO test_table VALUES (U&'THIS IS A STRING.');

Now issue:
SELECT * FROM test_table WHERE field1 = 'This is a string.';

There are no results.

From the documentation it seems that any text supplied should be additional
rules to the standard rules.
In `pg_locale_icu.c` in the `make_icu_collator` method at line 455, it seems
that it does a simple:

u_strcpy(all_rules, std_rules);
u_strcat(all_rules, my_rules);

which seems like, with the above change, should just append nothing to the
standard rules, causing no change. This, however, is not the case.

I have tried it with various permutations of the `rules`, and while any
rules supplied during the CREATE COLLATION call appear to function, it seems
that all standard rules are forgotten when this option is utilized.

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2025-09-09 22:24:22 Re: 回复:BUG #19040: Memory leak in hashed subplan node due to missing hashtempcxt reset
Previous Message Wolfgang Walther 2025-09-09 09:33:19 PostgreSQL fails to start inside Nix' darwin sandbox