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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users