postgres - simplified chinese collation on BTP Hyperscaler Option

[martin-kl]

Description of erroneous behaviour

Hello,
when using "Simplified Chinese" in SAP Build Work Zone, requests use the "zh_CN" locale. On queries with orderBy, the method orderByICU in PostgresService.js translates "zh_CN" to "zh-CN-x-icu". Those queries fail on the PostgreSQL Hyperscaler Option (AWS, PostgreSQL version 14.9) with:

collation "zh-CN-x-icu" for encoding "UTF8" does not exist

Turns out that the following locales beginning with "zh" are available:

collname	collcollate	collctype	collversion
zh-Hans-CN-x-icu	zh-Hans-CN	zh-Hans-CN	153.80.32.1
zh-Hans-HK-x-icu	zh-Hans-HK	zh-Hans-HK	153.80.32.1
zh-Hans-MO-x-icu	zh-Hans-MO	zh-Hans-MO	153.80.32.1
zh-Hans-SG-x-icu	zh-Hans-SG	zh-Hans-SG	153.80.32.1
zh-Hans-x-icu	zh-Hans	zh-Hans	153.80.32.1
zh-Hant-HK-x-icu	zh-Hant-HK	zh-Hant-HK	153.80.32.1
zh-Hant-MO-x-icu	zh-Hant-MO	zh-Hant-MO	153.80.32.1
zh-Hant-TW-x-icu	zh-Hant-TW	zh-Hant-TW	153.80.32.1
zh-Hant-x-icu	zh-Hant	zh-Hant	153.80.32.1
zh-x-icu	zh	zh	153.80.32.1
zh_CN	zh_CN	zh_CN	2.26-59.amzn2
zh_CN	zh_CN.utf8	zh_CN.utf8	2.26-59.amzn2
zh_CN.gb2312	zh_CN.gb2312	zh_CN.gb2312	2.26-59.amzn2
zh_CN.utf8	zh_CN.utf8	zh_CN.utf8	2.26-59.amzn2
zh_HK	zh_HK.utf8	zh_HK.utf8	2.26-59.amzn2
zh_HK.utf8	zh_HK.utf8	zh_HK.utf8	2.26-59.amzn2
zh_SG	zh_SG.utf8	zh_SG.utf8	2.26-59.amzn2
zh_SG	zh_SG	zh_SG	2.26-59.amzn2
zh_SG.gb2312	zh_SG.gb2312	zh_SG.gb2312	2.26-59.amzn2
zh_SG.utf8	zh_SG.utf8	zh_SG.utf8	2.26-59.amzn2
zh_TW	zh_TW.utf8	zh_TW.utf8	2.26-59.amzn2
zh_TW	zh_TW.euctw	zh_TW.euctw	2.26-59.amzn2
zh_TW.euctw	zh_TW.euctw	zh_TW.euctw	2.26-59.amzn2
zh_TW.utf8	zh_TW.utf8	zh_TW.utf8	2.26-59.amzn2

where "zh-CN-x-icu" is missing but "zh-Hans-CN-x-icu" would be available - "Hans" for "Han (simplified variant)" Source

We managed to get it to work in our project by creating the missing collation via

CREATE COLLATION "zh-CN-x-icu" from "zh-Hans-CN-x-icu";

but perhaps this could be integrated natively into @cap-js/postgres?

Detailed steps to reproduce

execute query containing "orderBy" on locale "zh_CN" on PostgreSQL Hyperscaler Option

Details about your project

customer-project
OData version	v4
Node.js version	v18.19.1
@sap/cds	7.9.3
@sap/cds-compiler	4.9.2
@sap/cds-dk	7.9.4
@cap-js/postgres	1.8.0
@cap-js/sqlite	1.7.0

[BobdenOs]

@martin-kl Thank you for reporting this productive behavior.

As we don't have access to all deployments of Postgres out there. We had to follow and trust the documentation of Postgres here. Which states that the icu localization auto generates C equivalent collations with the suffix -x-icu. It seems that zh_CN is an exception as the icu definition has colliding definitions with the traditional and simplified versions being of equal weight.

Currently @cap-js/postgres does an initial check whether the database instance has icu collations defined (code). If that is the case it will use icu as collation, but when the collation en-x-icu does not exist it will use the C collations. It will also check that when a locale comes in that the database knows the C collation. If not it fallbacks to using no collation in queries in that unknown locale.

@martin-kl would it have an impact whether zh_CN or zh-Hans-CN-x-icu is used ? Or would it be sufficient if we provide an option to pick the C collation over the icu collations ?

[martin-kl]

@BobdenOs thanks for the quick reply.
As far as I can see it doesn't make a difference between zh_CN and zh-Hans-CN-x-icu in our application - so both would be fine for us.

Generally speaking as CAP developer I'd like it to work automatically, meaning that I don't need an option if it works with either the C or the icu collation out-of-the-box.