I have created module implementing virtual table and registered it.
When I query virtual table using WHERE col IN (?,? ...)
pIdxInfo->nConstraint passed to xBestIndex is equal zero, and therefore also argc in xFilter is 0 (col=? constraint is not reflected). I would expect that every value after IN will be reflected by separate 'equal' type record in pIdxInfo->aConstraint (i.e. above should be equivalent to WHERE col=? AND col=? ...).
Sometimes it is not a problem as sqlite filters records out anyway, but in my case this leads to incorrect query results.
This can be also observed by adding following test to vtab1.test:
<verbatim>
do_test vtab1-3.10-BUG {
set echo_module ""
execsql {
SELECT * FROM t1 WHERE b IN (5,6);
}
} {4 5 6}
do_test vtab1-3.11-BUG {
set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ? AND b = ?} \
xFilter {SELECT rowid, * FROM 'treal' WHERE b = ? AND b = ?} 5 6 ]
</verbatim>
vtab1-3.11-BUG will fail:
Expected: |xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ? AND b = ?} xFilter {SELECT rowid, * FROM 'treal' WHERE b = ? AND b = ?} 5 6|
Got: |xBestIndex {SELECT rowid, * FROM 'treal'} xFilter {SELECT rowid, * FROM 'treal'}|
Regards,
Grzegorz W.
<hr><i>dan added on 2009-09-09 05:39:29:</i><br>
Technically, "WHERE col IN (?,?)" is equivalent to "WHERE col=? OR col=?". If
you rewrite the query as:
<pre>
SELECT * FROM t1 WHERE b=5 OR b=6
</pre>
then SQLite uses xBestIndex() to query the virtual table implementation to see
if "WHERE b=?" can be optimized. If it can, SQLite may use this to optimize the
query (depending on the estimated-cost returned by the vtab implementation for
any other possible query plans.
<hr><i>anonymous added on 2009-09-17 21:01:48:</i><br>
Thats right, my mistake, of course it should be OR in an example.
But problem still exist. In my application query is freely entered by user, so rewriting it to equivalent is not an option, and unfortunately I need information about constraints in xBestIndex/xFilter to return correct query result.
|