MIME-Version: 1.0
Date: Wed, 25 Sep 2024 22:13:24 +0700
From: Oleg Tselebrovskiy <o.tselebrovskiy@postgrespro.ru>
To: pgsql-docs@lists.postgresql.org
Subject: Initcap works differently with different locale providers
User-Agent: Roundcube Webmail/1.6.9
Message-ID: <804cc10ef95d4d3b298e76b181fd9437@postgrespro.ru>
Organization: Postgres Pro
Content-Type: multipart/mixed;
 boundary="=_9f334a4d6ec0e68aab377bfc6a719812"
Archived-At: 
 <https://www.postgresql.org/message-id/804cc10ef95d4d3b298e76b181fd9437%40postgrespro.ru>
Precedence: bulk

--=_9f334a4d6ec0e68aab377bfc6a719812
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=UTF-8;
 format=flowed

Greetings, everyone!

One of our clients has found a difference in behaviour of initcap 
function when
using different locale providers, shown below

	postgres=# create database test_db_1 locale_provider=icu 
locale="ru_RU.UTF-8" template=template0;
	NOTICE:  using standard form "ru-RU" for ICU locale "ru_RU.UTF-8"
	CREATE DATABASE
	postgres=# \c test_db_1;
	You are now connected to database "test_db_1" as user "postgres".
	test_db_1=# select initcap('ЧиЮ А.Ю.');
	initcap
	----------
	Чию А.ю.
	(1 row)
	test_db_1=# select initcap('joHn d.e.');
	initcap
	-----------
	John D.e.
	(1 row)
	postgres=# create database test_db_2 locale_provider=libc 
locale="ru_RU.UTF-8" template=template0;
	CREATE DATABASE
	postgres=# \c test_db_2
	You are now connected to database "test_db_2" as user "postgres".
	test_db_2=# select initcap('ЧиЮ А.Ю.');
	initcap
	----------
	Чию А.Ю.
	(1 row)
	test_db_2=# select initcap('joHn d.e.');
	initcap
	-----------
	John D.E.
	(1 row)

And an easier reproduction (should work for REL_12_STABLE and up)

	postgres=# SELECT initcap('first.second' COLLATE "en-x-icu");
	initcap
	--------------
	First.second
	(1 row)
	postgres=# SELECT initcap('first.second' COLLATE "en_US");
	initcap
	--------------
	First.Second
	(1 row)

This behaviour is reproducible on REL_12_STABLE and up to master

I don't believe that this is an erroneous behaviour, just a differing 
one, hence
just a documentation change proposition

I suggest adding a clarification that this function works differently 
with libc
and ICU providers because there is a difference in what a "word" is 
between them

In libc a word is a sequence of alphanumeric characters, separated by
non-alphanumeric characters (as it is written in documentation right 
now)
In ICU words are divided according to Unicode® Standard Annex #29 [1]

Similar issue was briefly discussed in [2]

The suggested documentation patch is attached (versions for 
REL_13_STABLE+ and
for REL_12_STABLE only)

[1]: https://www.unicode.org/reports/tr29/#Word_Boundaries
[2]: 
https://www.postgresql.org/message-id/CAEwbS1R8pwhRkwRo3XsPt24ErBNtFWuReAZhVPJwA3oqo148tA%40mail.gmail.com

Oleg Tselebrovskiy, Postgres Professional
--=_9f334a4d6ec0e68aab377bfc6a719812
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
 name=v1-0001-string-functions.patch
Content-Disposition: attachment;
 filename=v1-0001-string-functions.patch;
 size=952

ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9mdW5jLnNnbWwgYi9kb2Mvc3JjL3NnbWwvZnVuYy5z
Z21sCmluZGV4IDFiZGU0MDkxY2E2Li4zY2U1YWQxZDFmMSAxMDA2NDQKLS0tIGEvZG9jL3NyYy9z
Z21sL2Z1bmMuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvZnVuYy5zZ21sCkBAIC0zMTAwLDggKzMx
MDAsMTEgQEAgU0VMRUNUIE5PVChST1codGFibGUuKikgSVMgTk9UIE5VTEwpIEZST00gVEFCTEU7
IC0tIGRldGVjdCBhdCBsZWFzdCBvbmUgbnVsbCBpbgogICAgICAgIDwvcGFyYT4KICAgICAgICA8
cGFyYT4KICAgICAgICAgQ29udmVydHMgdGhlIGZpcnN0IGxldHRlciBvZiBlYWNoIHdvcmQgdG8g
dXBwZXIgY2FzZSBhbmQgdGhlCi0gICAgICAgIHJlc3QgdG8gbG93ZXIgY2FzZS4gV29yZHMgYXJl
IHNlcXVlbmNlcyBvZiBhbHBoYW51bWVyaWMKLSAgICAgICAgY2hhcmFjdGVycyBzZXBhcmF0ZWQg
Ynkgbm9uLWFscGhhbnVtZXJpYyBjaGFyYWN0ZXJzLgorICAgICAgICByZXN0IHRvIGxvd2VyIGNh
c2UuIFdoZW4gdXNpbmcgdGhlIDxsaXRlcmFsPmxpYmM8L2xpdGVyYWw+IGxvY2FsZQorICAgICAg
ICBwcm92aWRlciwgd29yZHMgYXJlIHNlcXVlbmNlcyBvZiBhbHBoYW51bWVyaWMgY2hhcmFjdGVy
cyBzZXBhcmF0ZWQKKyAgICAgICAgYnkgbm9uLWFscGhhbnVtZXJpYyBjaGFyYWN0ZXJzOyB3aGVu
IHVzaW5nIHRoZSBJQ1UgbG9jYWxlIHByb3ZpZGVyLAorICAgICAgICB3b3JkcyBhcmUgc2VwYXJh
dGVkIGFjY29yZGluZyB0bworICAgICAgICA8dWxpbmsgdXJsPSJodHRwczovL3d3dy51bmljb2Rl
Lm9yZy9yZXBvcnRzL3RyMjkvI1dvcmRfQm91bmRhcmllcyI+VW5pY29kZcKuIFN0YW5kYXJkIEFu
bmV4ICMyOTwvdWxpbms+LgogICAgICAgIDwvcGFyYT4KICAgICAgICA8cGFyYT4KICAgICAgICAg
PGxpdGVyYWw+aW5pdGNhcCgnaGkgVEhPTUFTJyk8L2xpdGVyYWw+Cg==
--=_9f334a4d6ec0e68aab377bfc6a719812
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
 name=v1-0002-string-functions-REL_12.patch
Content-Disposition: attachment;
 filename=v1-0002-string-functions-REL_12.patch;
 size=931

ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9mdW5jLnNnbWwgYi9kb2Mvc3JjL3NnbWwvZnVuYy5z
Z21sCmluZGV4IDQ4N2JiMTAzNjM3Li4xY2QyODFkZDkwYiAxMDA2NDQKLS0tIGEvZG9jL3NyYy9z
Z21sL2Z1bmMuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwvZnVuYy5zZ21sCkBAIC0xOTMyLDggKzE5
MzIsMTEgQEAKICAgICAgICA8ZW50cnk+PHR5cGU+dGV4dDwvdHlwZT48L2VudHJ5PgogICAgICAg
IDxlbnRyeT4KICAgICAgICAgQ29udmVydCB0aGUgZmlyc3QgbGV0dGVyIG9mIGVhY2ggd29yZCB0
byB1cHBlciBjYXNlIGFuZCB0aGUKLSAgICAgICAgcmVzdCB0byBsb3dlciBjYXNlLiBXb3JkcyBh
cmUgc2VxdWVuY2VzIG9mIGFscGhhbnVtZXJpYwotICAgICAgICBjaGFyYWN0ZXJzIHNlcGFyYXRl
ZCBieSBub24tYWxwaGFudW1lcmljIGNoYXJhY3RlcnMuCisgICAgICAgIHJlc3QgdG8gbG93ZXIg
Y2FzZS4gV2hlbiB1c2luZyB0aGUgPGxpdGVyYWw+bGliYzwvbGl0ZXJhbD4gbG9jYWxlCisgICAg
ICAgIHByb3ZpZGVyLCB3b3JkcyBhcmUgc2VxdWVuY2VzIG9mIGFscGhhbnVtZXJpYyBjaGFyYWN0
ZXJzIHNlcGFyYXRlZAorCQlieSBub24tYWxwaGFudW1lcmljIGNoYXJhY3RlcnM7IHdoZW4gdXNp
bmcgdGhlIElDVSBsb2NhbGUgcHJvdmlkZXIsCisJCXdvcmRzIGFyZSBzZXBhcmF0ZWQgYWNjb3Jk
aW5nIHRvCisJCTx1bGluayB1cmw9Imh0dHBzOi8vd3d3LnVuaWNvZGUub3JnL3JlcG9ydHMvdHIy
OS8jV29yZF9Cb3VuZGFyaWVzIj5Vbmljb2Rlwq4gU3RhbmRhcmQgQW5uZXggIzI5PC91bGluaz4u
CiAgICAgICAgPC9lbnRyeT4KICAgICAgICA8ZW50cnk+PGxpdGVyYWw+aW5pdGNhcCgnaGkgVEhP
TUFTJyk8L2xpdGVyYWw+PC9lbnRyeT4KICAgICAgICA8ZW50cnk+PGxpdGVyYWw+SGkgVGhvbWFz
PC9saXRlcmFsPjwvZW50cnk+Cg==
--=_9f334a4d6ec0e68aab377bfc6a719812--