virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

dave
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

Hick Gunter
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:[hidden email]] Im Auftrag von dave
Gesendet: Sonntag, 23. September 2018 23:26
An: 'SQLite mailing list' <[hidden email]>
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

dave
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:[hidden email]] 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:[hidden email]] Im
> Auftrag von dave
> Gesendet: Sonntag, 23. September 2018 23:26
> An: 'SQLite mailing list' <[hidden email]>
> 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
> [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

dave
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:[hidden email]] 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:[hidden email]] 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:[hidden email]] Im
> > Auftrag von dave
> > Gesendet: Sonntag, 23. September 2018 23:26
> > An: 'SQLite mailing list' <[hidden email]>
> > 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
> > [hidden email]
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

Keith Medcalf

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

dave
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

Richard Hipp-3
On 10/16/18, dave <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables, xBestIndex, pIdxInfo->aConstraint[].op, and 'NOT'...

dave
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users