Re: Problem in 'ORDER BY' of a column using a created collation?
От | Nishant Sharma |
---|---|
Тема | Re: Problem in 'ORDER BY' of a column using a created collation? |
Дата | |
Msg-id | CADrsxdbBn9NShy41AXUi2NUWUV48arffMcQbpzy8c-=e0Q1HSw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Problem in 'ORDER BY' of a column using a created collation? (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: Problem in 'ORDER BY' of a column using a created collation?
|
Список | pgsql-hackers |
On Fri, Sep 26, 2025 at 6:46 PM Robert Haas <robertmhaas@gmail.com> wrote:
Generally, there are two methods for performing collation-aware string
comparisons, and they are expected to produce equivalent results. For
libc collations, the first method is to use strcoll() or strcoll_l()
or similar to directly compared the strings, and the other is to use
strxfrm() or similar to convert the string to a normalize
representation which can then be compared using memcmp(). Equivalent
facilities exist for ICU; see collate_methods_icu and
collate_methods_icu_utf8 in pg_local_icu.c; one or the other of those
should be getting used here.
Even though these two methods are expected to produce equivalent
results, if there's a bug, they might not. That bug could either exist
in our code or it could exist in ICU; I suspect the latter is more
likely, but the former is certainly possible. What I think you want to
do is try to track down two specific strings where transforming them
via strnxfrm_icu() produces results that compare in one order using
memcmp(), but passing the same strings to strncoll_icu() or
strncoll_icu_utf8() -- whichever is appropriate -- produces a
different result. If you're able to find such strings, then you can
probably rephrase those examples in terms of whatever underlying
functions strncoll_icu() and strxfrm_icu() are calling and we can
maybe report the problem to the ICU maintainers. If no such strings
seem to exist, then there's probably a problem in the PostgreSQL code
someplace, and you should try to figure out why we're getting the
wrong answer despite ICU apparently doing the right thing.
--
Robert Haas
EDB: http://www.enterprisedb.com
Thanks Robert for your reply!
I was able to create an independent C src file. With no relation to PG in it.
Did a simple comparison of the two methods mentioned by Robert for '1'
& 'a' as input strings. The ASCENDING SORT ORDER of them is printed.
Here is how I compiled the C src code:-
"gcc icu_issue_repro.c -L/usr/lib -licui18n -licuuc -licudata -o
icu_issue_repro.o"
Here is its output:-
------------------------------------------------------------------------------------------
Testing sort order for '1' & 'a' using ICU library with collation = 'ja-u-kr-latn-digit'
With Method ucol_strcollUTF8():
SORT ORDER ASC : '1', 'a'
With Method ucol_nextSortKeyPart() (i.e transform and memcmp):
SORT ORDER ASC : 'a', '1'
Testing Ends
------------------------------------------------------------------------------------------
We can clearly see that both methods are showing different sort order
for the same input strings.
So, I think this helps confirm that the problem lies in the ICU library
method "ucol_strcollUTF8()".
Robert, if you agree with my shared details, can you please help me
with details on how I can share this issue to ICU maintainers for the
correction?
Thank you!
PFA, the C src code.
Regards,
Nishant Sharma,
EDB, Pune.
Вложения
В списке pgsql-hackers по дате отправления: