Ticket Hash: | 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: 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: |