On 1/11/15, Venkat Murty <vmu...@fiberlink.com> wrote: > Hi, > > I have a virtual table. > create table store (id int, stamp int, value blob); > > When i execute the following sql > select stamp, to_text(value) from store where id = 42; > > I get as expected sqlite3_index_info to have one usable entry with > aConstraint[i].iColumn = 0 > aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ > In xBestIndex I set the aConstraintUsage[i].argvIndex value to 1, to > indicate that '42' should be the first argument in corresponding xFilter > call. > > And in the corresponding xFilter I get a value of 42 as the first argument > and I can initialize the cursor with just one element with id = 42. > > When i execute the following sql > select stamp, to_text(value) from store where id = 42 or id = 24; > > I am expecting two usable entries in sqlite3_index_info with > aConstraint[i].iColumn = 0 and aConstraint[i].op == > SQLITE_INDEX_CONSTRAINT_EQ for each check
What actually happens is that SQLite transforms the query into SELECT stamp, to_text(value) FROM store WHERE id IN (42,24). The xBestIndex method does not distinguish between == and IN, so only a single == constraint will be mentioned in the xBestIndex call. > > xBestIndex is called twice, > > a) once with only one non-usable entry with aConstraint[i].iColumn = 0 > and aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ and Here it is checking the cost of a full table scan. > b) once with only one usable entry with aConstraint[i].iColumn = 0 and > aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ. Here it is checking the codes of using the IN operator. What estimatedCost values are you returning for these two calls. Because when I run it, and the cost of the second version is less, it chooses the second version. What version of SQLite are you using? > > And the xFilter called corresponds to the first call to xBestIndex. Thus it > does a table scan to find entries (42, 24) > > Am i missing something here ? > > -venkat murty > > Fiberlink Disclaimer: The information transmitted is intended only for the > person or entity to which it is addressed and may contain confidential > and/or privileged material. Any review, retransmission, dissemination or > other use of, or taking of any action in reliance upon, this information by > persons or entities other than the intended recipient is prohibited. If you > received this in error, please contact the sender and delete the material > from any computer. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
