Re: BUG #16570: Collation not working

    Arnaud Perrier wrote:

> 5/ SELECT * FROM TBL ORDER BY TEXT1 COLLATE digitslast, TEXT2 COLLATE
> digitslast;
> 5/ KO = TEXT1 = digits after letters + TEXT2 = digits before letters

I'm seeing wrong results too, and I don't really understand what's going
on with this collation.

Here's a simpler test with the current dev branch (14devel)
and ICU 60.2-3ubuntu.

=# CREATE COLLATION digitslast (provider = icu, locale =
'en@colReorder=latn-digit');

=# select * from (values('1'),('a')) as list(x) order by x collate
digitslast;
 x
---
 a
 1

The digit is sorted after the letter, OK.

But then why would an inequality test report the opposite?

=# select 'a' < '1' collate digitslast;
 ?column?
----------
 f

I'm wondering whether there is a discrepancy between the binary
sort keys and results of direct comparisons by ucol_strcoll()
for this collation.
Maybe that could explain why the second level of ORDER BY
produces results that are inconsistent with the first level.

With icu_ext we can visualize the sort keys and see that key('a')
if lower than key('1').

=# select icu_sort_key('a' collate digitslast) as sort_key_a,
  icu_sort_key('1' collate digitslast) as sort_key_1

   sort_key_a  |  sort_key_1
--------------+--------------
 \x0f01050105 | \x4901050105

But then a direct comparison says the opposite, just like the
'<' operator as above.

=# select icu_compare('a', '1', 'en@colReorder=latn-digit');
 icu_compare
-------------
       1

icu_compare() is a simple interface on top of ucol_strcollUTF8()
or ucol_strcoll(), and 1 as a result maps to UCOL_GREATER,  so unless
I'm missing something, this is looking more like an ICU bug than a
Postgres bug.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite