Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...
> Behalf Of Richard Hipp > > And also the meaning/use of > SQLITE_INDEX_CONSTRAINT_FUNCTION, that would be ... > The SQLITE_INDEX_CONSTRAINT_FUNCTION mechanism was added to support > the new Geopoly extension, and the ability to index on things like > "WHERE geopoly_within(_shape,...)" and "WHERE > geopoly_overlap(_shape,...)". There is little to no documentation on > the SQLITE_INDEX_CONSTRAINT_FUNCTION mechanism yet. See the geopoly > implementation for an example. > -- > D. Richard Hipp Thanks for the scoop! OK, at this point I'll assume the 'NOT' variant of MATCH, LIKE, GLOB, REGEXP are currently /not/ supported in xBestIndex, and just hope that maybe someday they will be. Cheers, and thanks for all the feedback! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...
On 10/16/18, dave wrote: > > And also the meaning/use of SQLITE_INDEX_CONSTRAINT_FUNCTION, that would be > super helpful. > The SQLITE_INDEX_CONSTRAINT_FUNCTION mechanism was added to support the new Geopoly extension, and the ability to index on things like "WHERE geopoly_within(_shape,...)" and "WHERE geopoly_overlap(_shape,...)". There is little to no documentation on the SQLITE_INDEX_CONSTRAINT_FUNCTION mechanism yet. See the geopoly implementation for an example. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...
> Behalf Of Keith Medcalf > Sent: Saturday, October 13, 2018 6:53 PM ... > > > Lastly, I'm assuming that 'IS' and 'IS NOT' is functionally > > equivalent to the '=' and '<>' operators? > > Or is there some subtle difference > > As long as neither the LHS or the RHS are null, then IS and > IS NOT are the same as == and <> respectively. > > However, if you use the "comparison" operators (==, <>) then > if either the LHS or the RHS or both are NULL, then the > results is NULL (that is, false). For the purpose of these > comparisons NULL is a value that is neither equal to nor not > equal to any other value, including null. > > IS and IS NOT mean that NULL is a distinct value and NULL IS > NULL is TRUE, NULL IS NOT 7 is TRUE, and so on and so forth. > Thanks for the confirmation of the behaviour of 'is' in sqlite. And if anyone has comnments regarding the first two issues I mentioned, namely the absence of support of NOT MATCH, NOT LIKE, NOT GLOB, NOT REGEXP in xBestIndex() And also the meaning/use of SQLITE_INDEX_CONSTRAINT_FUNCTION, that would be super helpful. Cheers! -dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...
> Lastly, I'm assuming that 'IS' and 'IS NOT' is functionally > equivalent to the '=' and '<>' operators? > Or is there some subtle difference As long as neither the LHS or the RHS are null, then IS and IS NOT are the same as == and <> respectively. However, if you use the "comparison" operators (==, <>) then if either the LHS or the RHS or both are NULL, then the results is NULL (that is, false). For the purpose of these comparisons NULL is a value that is neither equal to nor not equal to any other value, including null. IS and IS NOT mean that NULL is a distinct value and NULL IS NULL is TRUE, NULL IS NOT 7 is TRUE, and so on and so forth. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...
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' > > 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
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' > 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
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' 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
