OK, I finally got around to upgrading the sqlite version to 3.25.1, and testing this xBestIndex stuff out. Here is what I found:
These forms can still bypass the vtable's implementation of constraints/indices. They are all negations: NOT MATCH NOT LIKE NOT GLOB NOT REGEXP In each of these cases, there is no invocation of xBestIndex to let the vtable handle those negated predicates, but there /are/ invocations to let the vtable handle the asserted forms. As mentioned, I can live with this limitation for now, but you might consider extending support for such in the future for completeness. In my case, I implemented LIKE, but that code is bypassed if the user specifies NOT LIKE -- a situation which can produces surprising results! Also, I noticed SQLITE_INDEX_CONSTRAINT_FUNCTION. I don't know what this is, and I could not find any documentation for such, and so I couldn't test that one. I'm guessing it is now possible to have a user-defined predicate function? I'd like to know how to use that. At any rate, I suspect it might need a 'not' version as well. Lastly, I'm assuming that 'IS' and 'IS NOT' is functionally equivalent to the '=' and '<>' operators? Or is there some subtle difference? E.g. I can issue a query with a search condition "where name is 'person'" which triggers invocation of xBestIndex, and seems to behave like '='. It was my belief that the right-hand-side of 'IS' could only contain a boolean (true, false, null) as per SQL-92, but I'm guessing that SQLite extends it's meaning. Interestingly a search condition "where name is true" parses and runs, but does /not/ cause invocation of xBestIndex at all. Cheers! -dave > -----Original Message----- > From: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of dave > Sent: Monday, September 24, 2018 2:57 PM > To: 'SQLite mailing list' > Subject: Re: [sqlite] [EXTERNAL] virtual tables, > xBestIndex,pIdxInfo->aConstraint[].op, and 'NOT'... > > > I am using 3.20.1 > > Yes, I noticed LIKE etc. It looked like it changed from a > bitfield to an > enum at some point. So, I guess I am one versionpoint shy of > having NE and > IS. > OK, I'll check out at least 3.21 -- I was hesitant to upgrade just now > because there were some shell.c issues I had with those > embedded extensions > relative to the implementation in 3.20, but this may compell > me to do so. > (I use shell.c in a special debug build of my product). > > Thanks! > -dave > > > -----Original Message----- > > From: sqlite-users > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > > Behalf Of Hick Gunter > > Sent: Monday, September 24, 2018 1:57 AM > > To: 'SQLite mailing list' > > Subject: Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, > > pIdxInfo->aConstraint[].op, and 'NOT'... > > > > > > 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:sqlite-users-boun...@mailinglists.sqlite.org] Im > > Auftrag von dave > > Gesendet: Sonntag, 23. September 2018 23:26 > > An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> > > 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 > > sqlite-users@mailinglists.sqlite.org > > 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
