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:[email protected]] 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:[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 > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
