Thank you so much for your answers, we managed to do what we wanted.
Best, Manos Stamatis On 21 May 2015 at 21:42, Dan Kennedy <danielk1977 at gmail.com> wrote: > 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. > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
