SQLite Forum

How to avoid 'row X missing from index' when using a custom collation?
Thanks for the help, I finally found the issue, I could not understand how the ICU collator of PHP could return something that is no coherent.

And finally I found it: users had imported CSV files in the database, and these had non-UTF-8 characters, eg. Window-1252 and so on.

Because this was not valid UTF-8, the ICU collator did return false, and as a result the custom collation function returned incoherent results.

Fixing it with PHP was easy:

$str = !preg_match('//u', $str) ? utf8_encode($str) : $str;

on both strings before doing the collator comparison.

Thanks everyone :)