The following bug has been logged on the website:
Bug reference: 19045
Logged by: Todd Lang
Email address: todd.lang@d2l.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.
