/ View Ticket
Login
Ticket UUID: eaa105fe743a067384fd72409e76ea9bdfa70249
Title: (...) WHERE [col] IN (val1) not reflected in xBestIndex
Status: Closed Type: Feature_Request
Severity: Important Priority: Immediate
Subsystem: Code_Generator Resolution: Not_A_Bug
Last Modified: 2014-03-11 12:58:48
Version Found In: 3.6.17
Description:
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:

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 ]

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.


dan added on 2009-09-09 05:39:29:
Technically, "WHERE col IN (?,?)" is equivalent to "WHERE col=? OR col=?". If you rewrite the query as:

SELECT * FROM t1 WHERE b=5 OR b=6

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.


anonymous added on 2009-09-17 21:01:48:
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.