python collation sort "shift-trimmed"

Question

How would I make this test pass?

names = [
    "cote",
    "coté",
    "côte",
    "côté",
    "ReasonE",
    "Reason1",
    "ReasonĔ",
    "Reason Super",
    "ReasonÅ",
    "ReasonA",
    "Reasona",
    "Reasone",
    "death",
    "deluge",
    "de luge",
    "disílva John",
    "diSilva John",
    "di Silva Fred",
    "diSilva Fred",
    "disílva Fred",
    "di Silva John",
]
expected = [
    "cote",
    "côte",
    "coté",
    "côté",
    "death",
    "deluge",
    "de luge",
    "di Silva Fred",
    "diSilva Fred",
    "disílva Fred",
    "di Silva John",
    "diSilva John",
    "disílva John",
    "Reason1",
    "Reasona",
    "ReasonA",
    "ReasonÅ",
    "Reasone",
    "ReasonE",
    "ReasonĔ",
    "Reason Super",
]
assert sorted(names, key=some_sort_key) == expected

Tried a few locale variations like this:

loc = icu.Locale("und-u-ka-shifted-kb-true")
c = icu.Collator.createInstance(loc)
assert sorted(names, key=c.getSortKey) == expected

Background:

I'm trying to replicate (in python) the sorting behavior from an AWS postgres database. Best I can figure is it's got custom rules for space/punctuation based on the 'shift-trimmed' option, along with 'backwards accent' (kb-true or [backwards 2])

ICU doesn't appear to support shift-trimmed and I'm not sure how else I could get these to sort "properly".

See collation settings and contextual sensitivity for more explanation.

Answer 1

Honestly, I would question your sentence "I'm trying to replicate (in python) the sorting behavior from an AWS postgres database".

The underlying problem is that the AWS postgres database is using PostgreSQL glibc 2.26 (AWS) -- LC_COLLATE is en_US.utf8 under the hood (as you wrote in one comment). This means that the sorting behavior depends on glib, which depends on the locale en_US.utf8, see here:

Compares two strings for ordering using the linguistically correct rules for the current locale.

(I would argue, that being "linguistically correct" for sorting strings in different languages is a problem of its own. But I don't want to open Pandora's box here.)

So you have to ensure that you're using the same glib version as PostgreSQL on AWS is using, having the same en_US.utf8 definitions, maybe also other variables to consider. What I want to show is that this approach is quite fragile. By using ICU as another engine to collate, you are even using other mechanisms and rules, which is a hard and also a fragile task to get what you want (there are already multiple comments giving more details). And even if PostgreSQL is somehow configured to use ICU as an engine under the hood, you will have similar problems keeping PostgreSQL ICU version and your local version aligned (probably more to consider), so that challenges are still there.

My approach would be to see the sorting behavior as a black box. Use the PostgreSQL instance on AWS for test data one time and copy the sorted result as an expectation to the test. Then, the application code is tested according to this order and if something changes on PostgreSQL AWS side (e.g. after upgrading), you can see it in your test and you can react upon this. This keeps the test very small and clean, no complicated setup is needed.

If you really want to replicate the behavior, then you definitely need a more complicated setup. My way (instead of using ICU) would be to use a testcontainer for this job. But the PostgreSQL container has to be build on the specific glib version, which is OS-dependent. So, it would mean:

1. Write your own Dockerfile, which ultimately gives you the glib version of the AWS instance and build this image with a specific name
2. Consume the image via Testcontainers by the specific name
3. Put the list of strings in a temporary table and order them
4. Compare the result with your expectations

Point 1 is the most critical of course and needs also maintenance effort (besides of glib version, there are probably also more things to consider). But I haven't checked if the images used in AWS are publicly available somewhere.

You can question this approach of course and trying to get the ICU approach working, which you have started, instead. Both approaches have their downsides and need maintenance effort. That's why I questioned the whole approach of re-implementing this at all, because there are hard dependencies outside of Python itself, which complicates the whole test setup and introduces maintenance efforts.