SQLite Forum

How to avoid 'row X missing from index' when using a custom collation?
Yes of course my example was bad, the index can only be used in the order in that query, but that's the main use anyhow.

The idea of using a function in the index is nice, but I'm wondering what's the "least bad" thing between using the custom NOCASE collation and the function index:

1. NOCASE custom collation: integrity_check is broken, but database can be read in all instances, the index ordering actually works. BUT if you insert data to tables, you cannot match indexed columns using the custom collation (eg. `INSERT INTO names (name) VALUES ('Élodie TEST'); SELECT * FROM names WHERE name = 'Élodie TEST';` will return nothing but `SELECT * FROM names WHERE name = 'Élodie TEST' COLLATE BINARY;` will work).

2. function index: integrity_check works, but the database may not be read in some instances, and data cannot be inserted unless indexes are dropped.

Thanks for your help :)