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
