On 05/21/2015 10:20 PM, Emmanouil Karvounis wrote: > Greetings, > > We are having an issue with BestIndex in our Virtual Table implementation. > Allow us to illustrate the problem with a simple example. > > We create a virtual table named 'vt' that conceptually has 3 columns > "value1, value2, value3" and then we want to execute the following queries: > > 1) select value1 from vt where value1 = 7; > > In this case, BestIndex passes the equal constraint on the first column and > by setting the output as pdxInfo->aConstraintUsage[0].argvIndex = 1, > we indicate that we accept this specific constraint. So, at Filter we get > the value 7 as argument0. Everything behaves normally so far. > > However, in case we run any of the following equivalent queries (that > should pass to Filter more than one value), we get an error message > "xBestIndex returned an invalid plan": > > 2) select value1 from vt where value1 = 7 or value1 = 8; > 3) select value1 from vt where value1 in (select * from tableA); // > suppose tableA contains an integer 'id' column and records (7, 8) > 4) select value1 from vt, tableA where value1 = tableA.id; > > Again, in each case we set pdxInfo->aConstraintUsage[0].argvIndex = 1 but > we get the above error message. > > This behavior seems rather weird, so we'd like some expert help on what we > might be doing wrong.
That happens if you set the argvIndex variable on a constraint for which the "usable" flag is not set. For each constraint in the aConstraint[] array, you need to check that the "usable" flag is set - and ignore the constraint if it is not. Search for "usable" in this section of the docs: https://www.sqlite.org/vtab.html#xbestindex Dan.
