SQLite Forum

How to avoid 'row X missing from index' when using a custom collation?
Integrity check works (for this purpose) by scanning the table and then doing a B-Tree traversal of the index to find the entry for that row.  If the row is not found by the time the traversal is complete, then you get an error message that the row is missing from the index (it was not found where it was supposed to be -- it may indeed be in the index, just not where it was expected to be found though this is not checked).

This is because the "collation function" is used to traverse the BTree.  That is, assuming a very simple B-Tree, the "collation function" is used to determine if the sought key is "greater than" or "less than" the value stored at the current node and thus whether you "go left" or "go right" or the "current node" is the one sought.  If the "collation function" indicates that the "current node" is supposed to be the one sought, then the key and the rowid are checked against the key and rowid in the original table.  If these do not match, then an error message is thrown.

If the function used to do the comparison when "searching" the B-Tree does not provide consistent results with the function used to construct the B-Tree in the first place, then you will go right when you should go left and will fail to find the entry sought and an error message will be produced when you eventually run out of nodes without finding the one for which you are looking.

There is no solution to this problem other than to make the necessary collation sequence consistently available when accessing the B-Tree.