Repro:
create collation test(provider=icu,
deterministic=false, locale='@colStrength=primary');
create collation "en_US"(provider=icu,
deterministic=false, locale='@colStrength=primary');
select 'a' = 'A' collate test; -- true
select 'a' = 'A' collate "en_US"; -- false
drop collation "en_US"; -- drops built-in collation
select 'a' = 'A' collate "en_US"; -- true
Explanation:
The second collation named "en_US" is hidden behind the built-in
collation "en_US" because the former is created with collencoding=-1
(as all icu collations are), and the latter is a libc collation with
collencoding equal to the current database encoding (which takes
precedence).
It's a minor bug, but could be surprising behavior.
Solution:
There's no reason to create user defined collations with collencoding=-
1. As far as I can tell, collencoding is only there to hide built-in
collations (inherited from template0) that aren't compatible with the
current database encoding. So we can just always create user-defined
collations with collencoding=GetDatabaseEncoding().
Patch attached (extracted from series posted elsewhere because this is
a bug in old versions). I don't intend to backport because the bug is
minor and it will affect catalog contents. If others agree with the
fix, then I'll also bump the catversion, though I'm not sure that it's
strictly necessary.
Regards,
Jeff Davis