On Tue, 8 Sep 2015 15:42:28 -0400 Richard Hipp <drh at sqlite.org> wrote:
> On 9/8/15, Eduardo Morras <emorrasg at yahoo.es> wrote: > > > > > > Hello, > > > > I have a virtual table that implements query perceptual hashing data > > [1]. Now I'm thinking about converting the virtual table > > implementation in a collation on a normal sqlite3 table, but > > collation requieres that '=','<' and '>' be well defined by obeying > > the rules cited on create_collation() page[2]. Sometimes, rule 2 > > may not be true, but I always query for '=', > > Yes, but under the hood, SQLlite never does an == query on the b-trees > even if you ask for a == query in the SQL. Instead, the b-trees are > queried using one of >, >=, <, or <=. A query of the form: > > x=$value > > Gets translated (at the b-tree layer) into > > x>=$value AND x<=$value > > So it is *very* important that the comparison operators all work > correctly on your collating sequence function. If they don't, then > SQLite will give incorrect answers. Yes, the comparison operators work correctly and the b-tree binary search should give correct answers, the only tweak is in xCompare, that returns 0 when left(x) and rigth($value) expressions distance is lower than a threshold. I begin with the implementation and test cases, I expect it be faster than virtual table. Thanks --- --- Eduardo Morras <emorrasg at yahoo.es>
