dimanche 13 mars 2016

Sqlite glob on a function index

Using the latest SQLite, I can make an index on a deterministic function of a text field (e.g. MYFUNC(MyCol)). But whenever I try to do a glob search on this, e.g.

select * from myTable where MYFUNC(MyCol) glob 'Z*'

it does not appear to use the index, but instead slowly calls MYFUNC on every row in the table. According to http://ift.tt/1MPeTww you cannot use overloaded built-in functions in glob expressions with an index, but it doesn't say you can't use non-overloading custom function. Is this actually another limitation, or am I doing something wrong?

(it seems like a very common thing to want to do, e.g. to allow searching on name fields without sensitivity to accents, prefixes, etc, by using a MYFUNC that standardizes field formatting).

Aucun commentaire:

Enregistrer un commentaire