I found that if  I add the a extra field in the index like the pk and make the index unique it works fine.

Many of those collation problems seem to relate to unicode_ci and unicode_ci_ai,
and this relates to the usage of ICU functions.

FB seems to set collation strength properly only where it relates to unique index.
Otherwise the strength is left default, i.e. equivalent of collate unicode, for unicode_ci and unicode_ci_ai.
This is my guess after tested and observed many cases.

If a sorting was ordered by a single column with collate UNICODE,
the result could be shared with collate UNICODE_CI or UNICODE_CI_AI.
But when it comes to ordering by multiple columns, it should be a different story.

Is there anyone who can check the relating source code?
ICU's collation strength must be set as follows to make sort key according to the field's collate property:
default(unicode), primary(unicode_ci_ai) or secondary(unicode_ci)

Regards,
Hiro

(2018/10/16 5:04), Analitico analitico70@... [firebird-support] wrote:

 

I found that if  I add the a extra field in the index like the pk and make the index unique it works fine.