Which version are you using? The set of constraint constants was extended in versions 3.10 (LIKE, GLOB, REGEXP), 3.21 (NE, IS*) and most recently 3.25 (FUNCTION)
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von dave Gesendet: Sonntag, 23. September 2018 23:26 An: 'SQLite mailing list' <[email protected]> Betreff: [EXTERNAL] [sqlite] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'... Folks, I cannot seem to find a means of filtering on negated operators, e.g. <>, not null, not like, etc., in the xBestIndex() method for virtual vables. As best as I can tell, I cannot, unless there is something I am missing, hence this inquiry. In a few virtual tables I have implemented, I have handled the SQLITE_INDEX_CONSTRAINT_EQ, and the SQLITE_INDEX_CONSTRAINT_LIKE (for example) in the xBestIndex and xFilter. These code paths are taken for queries of the form: select * from myvtab where mycol = 'xxx'; select * from myvtab where mycol like 'xxx'; but /not/ for queries of the form: select * from myvtab where mycol <> 'xxx'; select * from myvtab where mycol not like 'xxx'; I can work around these things for now with caveats in documentation, but it does sometimes cause confusion to users. For example, in one case I have extended the syntax of LIKE . That extension of syntax is invoked for a positive LIKE constraint, but is bypassed for a negated one. I can work around that with an extension function, but I won't get the hints at record enumeration time that could reduce the dataset from the underlying source. In other cases, I have some 'required' columns, which must be present in a EQ constraints (usually they wind up being parameters to a function call that generates the underlying data). I emit an error when such constraints are missing, but it can be confusing to users when: select * from myvtab where mycol <> 'xxx'; indicates that "you must have a constraint on 'mycol'" Lastly, some behavioural inconsistencies occur between these forms: select * from myvtab where mycol = null; select * from myvtab where mycol is null; Since the first comes in as a constraint to xBestIndex, whereas the second does not. Anyway, as I said, I can work around this for now, but I thought I would ask if: 1) is it true: xBestIndex doesn't get to see negated predicates, or is it just somewhere that I have not found? 2) if it's not possible, would it be worthwhile to consider extending the operator set in some way to present the negative clauses at some release in the future? Thanks for any info! -dave _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
