Losing my latin on Ordering...

Hi. Porting a unit test to PostgreSQL, we got a failure related to ordering.

We've distilled it to the below. The DB is en_US.UTF-8, and the sorting we
get
does not make sense to me. The same prefix can be sorted differently based
on the suffix apprently, which doesn't make any sense to me.

Surely sorting should be "constant left-to-right", no? What are we missing?

I'm already surprised (star) comes before (space), when the latter "comes
before" the former in both ASCII and UTF-8, but that the two "Foo*" and
"Foo " prefixed pairs are not clustered after sorting is just mistifying to
me. So how come?

For now we can work-around this by explicitly adding the `collate "C"` on
the queries underlying that particular test, but that would be wrong in the
general case of international strings to sort, so I'd really like to
understand what's going on.

Thanks, --DD

PS: if I try "en_US.UTF-8" or "en_US"."UTF-8" for the collate, it fails.
How come what pg_database.datcollate displays is not a valid value for
collate?

PPS: We tried on v12 and v13 I believe. Somehow my v14.2 on Windows doesn't
have en_US as a collation...

ddevienne=> with t(v) as (values ('Foo All'), ('Foo*'), ('Foo Brief'),
('Foo*All')) select '<'||v||'>', v::bytea from t order by v collate "en_US";
?column? | v
-------------+----------------------
<Foo*> | \x466f6f2a
<Foo All> | \x466f6f20416c6c
<Foo*All> | \x466f6f2a416c6c
<Foo Brief> | \x466f6f204272696566
(4 rows)

ddevienne=> with t(v) as (values ('Foo All'), ('Foo*'), ('Foo Brief'),
('Foo*All')) select '<'||v||'>', v::bytea from t order by v collate "C";
?column? | v
-------------+----------------------
<Foo All> | \x466f6f20416c6c
<Foo Brief> | \x466f6f204272696566
<Foo*> | \x466f6f2a
<Foo*All> | \x466f6f2a416c6c
(4 rows)