I have a virtual table implementation, that implements the
xBestIndex/xFilter funktions and I found the following problem:
This query
SELECT * FROM vf WHERE field LIKE "F%";
will result in a call to xBestIndex with the following constraint
field >= ... AND field < ...
when SQLITE calls xFilter, the right hand values are delivered
correctly with
field >= "F" AND field < "g" /* !! */
because
PRAGMA case_sensitive_like = OFF; /* Standard */
is in effect. My xFilter implementation correctly filters records
according to the constraint given by SQLITE and returns records,
where for example field = 'eight'. ("F" <= "eight" < "g")
But obviously SQLITE does no post processing of the received records
and presents this record in the result too.
When I set the option
idxinfo->aConstraintUsage[i].omit = 0;
in xBestIndex() SQLITE removes records where field = 'eight' from the
result. But when I set the option
idxinfo->aConstraintUsage[i].omit = 1;
(which I want to do because of optimizing reasons) SQLITE does no
further filtering and presents the wrong records in the result.
Where is my error? xBestFilter() does not know, that currently a LIKE
expression is parsed/optimised and has therefore no chance to set omit
only in this case to 0. Setting omit in any case to 0 is currently the
only chance I can see...
The other possibility will be to set the case_sensitive_like PRAGMA to
ON, but this spoils the SQL Standard?
Am I missing something else?
Thanks
Kurt
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
