Re: Explicit deterministic COLLATE fails with pattern matching operations on column with non-deterministic collation - Mailing list pgsql-bugs

James Lucas <jlucasdba@gmail.com> writes:
> I tried setting up a pathological test case for this, and it seems
> like at least currently, even with a non-deterministic collation
> statistics still count values as distinct, even if the default
> collation would consider them equivalent.  Not sure if that's as
> intended or not?

I experimented with this, and what I'm seeing is that ucol_strcollUTF8()
reports that 'aaa' is different from 'aAa'.  So the behavior on the
Postgres side is as-expected.  I suspect that the 'en-US-ks-level2'
ICU locale doesn't act as you think it does.  (That is, just saying
that a collation is nondeterministic doesn't make it so; it only forces
Postgres through slower code paths that allow for the possibility of
bitwise-unequal strings being reported as equal by ICU.)  Not knowing
anything about ICU, I can't say more than that.

[ Tested on libicu-60.3-2.el8_1 ]

            regards, tom lane