Add the sqlite3_vtab_in() interface that allows virtual tables to process

IN constraints all at once, rather than one value at a time.

FossilOrigin-Name: 52559af093809b572082b5ebaacf97b727ee1860ae118530761b62e937545163

11 files changed, 468 insertions, 35 deletions

diff --git a/src/loadext.c b/src/loadext.c
index a4aad7e74..603516e18 100644
--- a/src/loadext.c
+++ b/src/loadext.c
@@ -489,6 +489,9 @@ static const sqlite3_api_routines sqlite3Apis = {
   sqlite3_error_offset,
   sqlite3_vtab_rhs_value,
   sqlite3_vtab_distinct,
+  sqlite3_vtab_in,
+  sqlite3_vtab_in_first,
+  sqlite3_vtab_in_next
 };
 
 /* True if x is the directory separator character
diff --git a/src/sqlite.h.in b/src/sqlite.h.in
index 82f844d6a..ead938a7d 100644
--- a/src/sqlite.h.in
+++ b/src/sqlite.h.in
@@ -9605,6 +9605,128 @@ SQLITE_EXPERIMENTAL const char *sqlite3_vtab_collation(sqlite3_index_info*,int);
 int sqlite3_vtab_distinct(sqlite3_index_info*);
 
 /*
+** CAPI3REF: Identify and handle IN constraints in xBestIndex
+**
+** This interface may only be used from within an 
+** [xBestIndex|xBestIndex() method] of a [virtual table] implementation.
+** The result of invoking this interface from any other context is
+** undefined and probably harmful.
+**
+** ^(A constraint on a virtual table of the form
+** "[IN operator|column IN (...)]" is
+** communicated to the xBestIndex method as a
+** [SQLITE_INDEX_CONSTRAINT_EQ] constraint.)^  If xBestIndex wants to use
+** this constraint, it must set the corresponding
+** aConstraintUsage[].argvIndex to a postive integer.  ^(Then, under
+** the usual mode of handling IN operators, SQLite generates [bytecode]
+** that invokes the [xFilter|xFilter() method] once for each value
+** on the right-hand side of the IN operator.)^  Thus the virtual table
+** only sees a single value from the right-hand side of the IN operator
+** at a time.
+**
+** In some cases, however, it would be advantageous for the virtual
+** table to see all values on the right-hand of the IN operator all at
+** once.  The sqlite3_vtab_in() interfaces facilitates this in two ways:
+**
+** <ol>
+** <li><p>
+**   ^A call to sqlite3_vtab_in(P,N,-1) will return true (non-zero)
+**   if and only if the [sqlite3_index_info|P->aConstraint][N] constraint
+**   is an [IN operator] that can be processed all at once.  ^In other words,
+**   sqlite3_vtab_in() with -1 in the third argument is a mechanism
+**   by which the virtual table can ask SQLite if all-at-once processing
+**   of the IN operator is even possible.
+**
+** <li><p>
+**   ^A call to sqlite3_vtab_in(P,N,F) with F==1 or F==0 indicates
+**   to SQLite that the virtual table does or does not want to process
+**   the IN operator all-at-once, respectively.  ^Thus when the third
+**   parameter (F) is non-negative, this interface is the mechanism by
+**   which the virtual table tells SQLite how it wants to process in
+**   IN operator.
+** </ol>
+**
+** ^The sqlite3_vtab_in(P,N,F) interface can be invoked multiple times
+** within the same xBestIndex method call.  ^For any given P,N pair,
+** the return value from sqlite3_vtab_in(P,N,F) will always be the same
+** within the same xBestIndex call.  ^If the interface returns true
+** (non-zero), that means that the constraint is an IN operator
+** that can be processed all-at-once.  ^If the constraint is not an IN
+** operator or cannot be processed all-at-once, then the interface returns
+** false.
+**
+** ^(All-at-once processing of the IN operator is selected if both of the
+** following conditions are met:
+**
+** <ol>
+** <li><p> The P->aConstraintUsage[N].argvIndex value is set to a positive
+** integer.  This is how the virtual table tells SQLite that it wants to
+** use the N-th constraint.
+**
+** <li><p> The last call to sqlite3_vtab_in(P,N,F) for which F was
+** non-negative had F>=1.
+** </ol>)^
+** 
+** ^If either or both of the conditions above are false, then SQLite uses
+** the traditional one-at-a-time processing strategy for IN constraint.
+** ^If both conditions are true, then the argvIndex-th parameter to the
+** xFilter method will be an [sqlite3_value] that appears to be NULL,
+** but which can be passed to [sqlite3_vtab_in_first()] and
+** [sqlite3_vtab_in_next()] to find all values on the right-hand side
+** of the IN constraint.
+*/
+int sqlite3_vtab_in(sqlite3_index_info*, int iCons, int bHandle);
+
+/*
+** CAPI3REF: Find all elements on the right-hand side of an IN constraint.
+**
+** These interfaces are only useful from within the
+** [xFilter|xFilter() method] of a [virtual table] implementation.
+** The result of invoking these interfaces from any other context
+** is undefined and probably harmful.
+**
+** The X parameter in a call to sqlite3_vtab_in_first(X,P) or
+** sqlite3_vtab_in_next(X,P) must be one of the parameters to the
+** xFilter method which invokes these routines, and specifically
+** a parameter that was previously selected for all-at-once IN constraint
+** processing use the [sqlite3_vtab_in()] interface in the
+** [xBestIndex|xBestIndex method].  ^(If the X parameter is not
+** an xFilter argument that was selected for all-at-once IN constraint
+** processing, then these routines return [SQLITE_MISUSE])^ or perhaps
+** exhibit some other undefined or harmful behavior.
+**
+** ^(Use these routines to access all values on the right-hand side
+** of the IN constraint using code like the following:
+**
+** <blockquote><pre>
+** &nbsp;  for(rc=sqlite3_vtab_in_first(pList, &pVal);
+** &nbsp;      rc==SQLITE_OK && pVal
+** &nbsp;      rc=sqlite3_vtab_in_next(pList, &pVal)
+** &nbsp;  ){
+** &nbsp;    // do something with pVal
+** &nbsp;  }
+** &nbsp;  if( rc!=SQLITE_OK ){
+** &nbsp;    // an error has occurred
+** &nbsp;  }
+** </pre></blockquote>)^
+**
+** ^On success, the sqlite3_vtab_in_first(X,P) and sqlite3_vtab_in_next(X,P)
+** routines return SQLITE_OK and set *P to point to the first or next value
+** on the RHS of the IN constraint.  ^If there are no more values on the
+** right hand side of the IN constraint, then *P is set to NULL and these
+** routines return [SQLITE_DONE].  ^The return value might be
+** some other value, such as SQLITE_NOMEM, in the event of a malfunction.
+**
+** The *ppOut values returned by these routines are only valid until the
+** next call to either of these routines or until the end of the xFilter
+** method from which these routines were called.  If the virtual table
+** implementation needs to retain the *ppOut values for longer, it must make
+** copies.
+*/
+int sqlite3_vtab_in_first(sqlite3_value *pVal, sqlite3_value **ppOut);
+int sqlite3_vtab_in_next(sqlite3_value *pVal, sqlite3_value **ppOut);
+
+/*
 ** CAPI3REF: Constraint values in xBestIndex()
 ** METHOD: sqlite3_index_info
 **
diff --git a/src/sqlite3ext.h b/src/sqlite3ext.h
index 88010b9d8..2eac4f3f0 100644
--- a/src/sqlite3ext.h
+++ b/src/sqlite3ext.h
@@ -348,6 +348,9 @@ struct sqlite3_api_routines {
   int (*error_offset)(sqlite3*);
   int (*vtab_rhs_value)(sqlite3_index_info*,int,sqlite3_value**);
   int (*vtab_distinct)(sqlite3_index_info*);
+  int (*vtab_in)(sqlite3_index_info*,int,int);
+  int (*vtab_in_first)(sqlite3_value*,sqlite3_value**);
+  int (*vtab_in_next)(sqlite3_value*,sqlite3_value**);
 };
 
 /*
@@ -663,6 +666,9 @@ typedef int (*sqlite3_loadext_entry)(
 #define sqlite3_error_offset           sqlite3_api->error_offset
 #define sqlite3_vtab_rhs_value         sqlite3_api->vtab_rhs_value
 #define sqlite3_vtab_distinct          sqlite3_api->vtab_distinct
+#define sqlite3_vtab_in                sqlite3_api->vtab_in
+#define sqlite3_vtab_in_first          sqlite3_api->vtab_in_first
+#define sqlite3_vtab_in_next           sqlite3_api->vtab_in_next
 #endif /* !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION) */
 
 #if !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION)
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 6d9365071..374fc9554 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1231,10 +1231,11 @@ typedef struct With With;
 /*
 ** A bit in a Bitmask
 */
-#define MASKBIT(n)   (((Bitmask)1)<<(n))
-#define MASKBIT64(n) (((u64)1)<<(n))
-#define MASKBIT32(n) (((unsigned int)1)<<(n))
-#define ALLBITS      ((Bitmask)-1)
+#define MASKBIT(n)    (((Bitmask)1)<<(n))
+#define MASKBIT64(n)  (((u64)1)<<(n))
+#define MASKBIT32(n)  (((unsigned int)1)<<(n))
+#define SMASKBIT32(n) ((n)<=31?((unsigned int)1)<<(n):0)
+#define ALLBITS       ((Bitmask)-1)
 
 /* A VList object records a mapping between parameters/variables/wildcards
 ** in the SQL statement (such as $abc, @pqr, or :xyz) and the integer
diff --git a/src/test_bestindex.c b/src/test_bestindex.c
index 6cda889e5..67a0c8258 100644
--- a/src/test_bestindex.c
+++ b/src/test_bestindex.c
@@ -299,7 +299,21 @@ static int tclFilter(
     const char *zVal = (const char*)sqlite3_value_text(argv[ii]);
     Tcl_Obj *pVal;
     if( zVal==0 ){
+      sqlite3_value *pMem;
       pVal = Tcl_NewObj();
+      for(rc=sqlite3_vtab_in_first(argv[ii], &pMem); 
+          rc==SQLITE_OK && pMem;
+          rc=sqlite3_vtab_in_next(argv[ii], &pMem)
+      ){
+        Tcl_Obj *pVal2 = 0;
+        zVal = (const char*)sqlite3_value_text(pMem);
+        if( zVal ){
+          pVal2 = Tcl_NewStringObj(zVal, -1);
+        }else{
+          pVal2 = Tcl_NewObj();
+        }
+        Tcl_ListObjAppendElement(interp, pVal, pVal2);
+      }
     }else{
       pVal = Tcl_NewStringObj(zVal, -1);
     }
@@ -374,20 +388,13 @@ static int tclEof(sqlite3_vtab_cursor *pVtabCursor){
   return (pCsr->pStmt==0);
 }
 
-static int tclBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
-  tcl_vtab *pTab = (tcl_vtab*)tab;
-  Tcl_Interp *interp = pTab->interp;
-  Tcl_Obj *pArg;
-  Tcl_Obj *pScript;
+static void testBestIndexObjConstraints(
+  Tcl_Interp *interp, 
+  sqlite3_index_info *pIdxInfo
+){
   int ii;
-  int rc = SQLITE_OK;
-
-  pScript = Tcl_DuplicateObj(pTab->pCmd);
-  Tcl_IncrRefCount(pScript);
-  Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xBestIndex", -1));
-
-  pArg = Tcl_NewObj();
-  Tcl_IncrRefCount(pArg);
+  Tcl_Obj *pRes = Tcl_NewObj();
+  Tcl_IncrRefCount(pRes);
   for(ii=0; ii<pIdxInfo->nConstraint; ii++){
     struct sqlite3_index_constraint const *pCons = &pIdxInfo->aConstraint[ii];
     Tcl_Obj *pElem = Tcl_NewObj();
@@ -437,15 +444,21 @@ static int tclBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
     Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("usable", -1));
     Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pCons->usable));
 
-    Tcl_ListObjAppendElement(0, pArg, pElem);
+    Tcl_ListObjAppendElement(0, pRes, pElem);
     Tcl_DecrRefCount(pElem);
   }
 
-  Tcl_ListObjAppendElement(0, pScript, pArg);
-  Tcl_DecrRefCount(pArg);
+  Tcl_SetObjResult(interp, pRes);
+  Tcl_DecrRefCount(pRes);
+}
 
-  pArg = Tcl_NewObj();
-  Tcl_IncrRefCount(pArg);
+static void testBestIndexObjOrderby(
+  Tcl_Interp *interp, 
+  sqlite3_index_info *pIdxInfo
+){
+  int ii;
+  Tcl_Obj *pRes = Tcl_NewObj();
+  Tcl_IncrRefCount(pRes);
   for(ii=0; ii<pIdxInfo->nOrderBy; ii++){
     struct sqlite3_index_orderby const *pOrder = &pIdxInfo->aOrderBy[ii];
     Tcl_Obj *pElem = Tcl_NewObj();
@@ -456,17 +469,150 @@ static int tclBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
     Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("desc", -1));
     Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pOrder->desc));
 
-    Tcl_ListObjAppendElement(0, pArg, pElem);
+    Tcl_ListObjAppendElement(0, pRes, pElem);
     Tcl_DecrRefCount(pElem);
   }
 
-  Tcl_ListObjAppendElement(0, pScript, pArg);
-  Tcl_DecrRefCount(pArg);
+  Tcl_SetObjResult(interp, pRes);
+  Tcl_DecrRefCount(pRes);
+}
 
-  Tcl_ListObjAppendElement(0, pScript, Tcl_NewWideIntObj(pIdxInfo->colUsed));
+/*
+** Implementation of the handle passed to each xBestIndex callback. This
+** object features the following sub-commands:
+**
+**    $hdl constraints
+**    $hdl orderby
+**    $hdl mask
+**
+**    $hdl distinct
+**      Return the result (an integer) of calling sqlite3_vtab_distinct()
+**      on the index-info structure.
+**
+**    $hdl in IDX BOOLEAN
+**      Wrapper around sqlite3_vtab_in(). Returns an integer.
+**
+**    $hdl rhs_value IDX ?DEFAULT?
+**      Wrapper around sqlite3_vtab_rhs_value().
+*/
+static int SQLITE_TCLAPI testBestIndexObj(
+  ClientData clientData, /* Pointer to sqlite3_enable_XXX function */
+  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
+  int objc,              /* Number of arguments */
+  Tcl_Obj *CONST objv[]  /* Command arguments */
+){
+  const char *azSub[] = {
+    "constraints",                /* 0 */
+    "orderby",                    /* 1 */
+    "mask",                       /* 2 */
+    "distinct",                   /* 3 */
+    "in",                         /* 4 */
+    "rhs_value",                  /* 5 */
+    0
+  };
+  int ii;
+  sqlite3_index_info *pIdxInfo = (sqlite3_index_info*)clientData;
 
+  if( objc<2 ){
+    Tcl_WrongNumArgs(interp, 1, objv, "SUB-COMMAND");
+    return TCL_ERROR;
+  }
+  if( Tcl_GetIndexFromObj(interp, objv[1], azSub, "sub-command", 0, &ii) ){
+    return TCL_ERROR;
+  }
+
+  if( ii<4 && objc!=2 ){
+    Tcl_WrongNumArgs(interp, 2, objv, "");
+    return TCL_ERROR;
+  }
+  if( ii==4 && objc!=4 ){
+    Tcl_WrongNumArgs(interp, 2, objv, "INDEX BOOLEAN");
+    return TCL_ERROR;
+  }
+  if( ii==5 && objc!=3 && objc!=4 ){
+    Tcl_WrongNumArgs(interp, 2, objv, "INDEX ?DEFAULT?");
+    return TCL_ERROR;
+  }
+
+  switch( ii ){
+    case 0: assert( sqlite3_stricmp(azSub[ii], "constraints")==0 );
+      testBestIndexObjConstraints(interp, pIdxInfo);
+      break;
+
+    case 1: assert( sqlite3_stricmp(azSub[ii], "orderby")==0 );
+      testBestIndexObjOrderby(interp, pIdxInfo);
+      break;
+
+    case 2: assert( sqlite3_stricmp(azSub[ii], "mask")==0 );
+      Tcl_SetObjResult(interp, Tcl_NewWideIntObj(pIdxInfo->colUsed));
+      break;
+
+    case 3: assert( sqlite3_stricmp(azSub[ii], "distinct")==0 ); {
+      int bDistinct = sqlite3_vtab_distinct(pIdxInfo);
+      Tcl_SetObjResult(interp, Tcl_NewIntObj(bDistinct));
+      break;
+    }
+
+    case 4: assert( sqlite3_stricmp(azSub[ii], "in")==0 ); {
+      int iCons;
+      int bHandle;
+      if( Tcl_GetIntFromObj(interp, objv[2], &iCons) 
+       || Tcl_GetBooleanFromObj(interp, objv[3], &bHandle) 
+      ){
+        return TCL_ERROR;
+      }
+      Tcl_SetObjResult(interp, 
+          Tcl_NewIntObj(sqlite3_vtab_in(pIdxInfo, iCons, bHandle))
+      );
+      break;
+    }
+
+    case 5: assert( sqlite3_stricmp(azSub[ii], "rhs_value")==0 ); {
+      int iCons = 0;
+      int rc;
+      sqlite3_value *pVal = 0;
+      const char *zVal = "";
+      if( Tcl_GetIntFromObj(interp, objv[2], &iCons) ){
+        return TCL_ERROR;
+      }
+      rc = sqlite3_vtab_rhs_value(pIdxInfo, iCons, &pVal);
+      if( rc!=SQLITE_OK && rc!=SQLITE_NOTFOUND ){
+        Tcl_SetResult(interp, (char *)sqlite3ErrName(rc), TCL_VOLATILE);
+        return TCL_ERROR;
+      }
+      if( pVal ){
+        zVal = (const char*)sqlite3_value_text(pVal);
+      }else if( objc==4 ){
+        zVal = Tcl_GetString(objv[3]);
+      }
+      Tcl_SetObjResult(interp, Tcl_NewStringObj(zVal, -1));
+      break;
+    }
+  }
+
+  return TCL_OK;
+}
+
+static int tclBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
+  tcl_vtab *pTab = (tcl_vtab*)tab;
+  Tcl_Interp *interp = pTab->interp;
+  int rc = SQLITE_OK;
+
+  static int iNext = 43;
+  char zHdl[24];
+  Tcl_Obj *pScript;
+
+  pScript = Tcl_DuplicateObj(pTab->pCmd);
+  Tcl_IncrRefCount(pScript);
+  Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xBestIndex", -1));
+
+  sqlite3_snprintf(sizeof(zHdl), zHdl, "bestindex%d", iNext++);
+  Tcl_CreateObjCommand(interp, zHdl, testBestIndexObj, pIdxInfo, 0);
+  Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj(zHdl, -1));
   rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL);
+  Tcl_DeleteCommand(interp, zHdl);
   Tcl_DecrRefCount(pScript);
+
   if( rc!=TCL_OK ){
     const char *zErr = Tcl_GetStringResult(interp);
     rc = SQLITE_ERROR;
@@ -493,6 +639,7 @@ static int tclBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
       rc = SQLITE_ERROR;
       pTab->base.zErrMsg = sqlite3_mprintf("%s", zErr);
     }else{
+      int ii;
       int iArgv = 1;
       for(ii=0; rc==SQLITE_OK && ii<nElem; ii+=2){
         const char *zCmd = Tcl_GetString(apElem[ii]);
diff --git a/src/vdbe.c b/src/vdbe.c
index d4ff33141..5410a7912 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -7735,6 +7735,34 @@ case OP_VOpen: {
 #endif /* SQLITE_OMIT_VIRTUALTABLE */
 
 #ifndef SQLITE_OMIT_VIRTUALTABLE
+/* Opcode: VInitIn P1 P2 P3 * *
+** Synopsis: r[P2]=ValueList(P1,P3)
+**
+** Set register P2 to be a pointer to a ValueList object for cursor P1
+** with cache register P3 and output register P3+1.  This ValueList object
+** can be used as the first argument to sqlite3_vtab_in_first() and
+** sqlite3_vtab_in_next() to extract all of the values stored in the P1
+** cursor.  Register P3 is used to hold the values returned by
+** sqlite3_vtab_in_first() and sqlite3_vtab_in_next().
+*/
+case OP_VInitIn: {        /* out2 */
+  VdbeCursor *pC;         /* The cursor containing the RHS values */
+  ValueList *pRhs;        /* New ValueList object to put in reg[P2] */
+
+  pC = p->apCsr[pOp->p1];
+  pRhs = sqlite3_malloc64( sizeof(*pRhs) );
+  if( pRhs==0 ) goto no_mem;
+  pRhs->pCsr = pC->uc.pCursor;
+  pRhs->pOut = &aMem[pOp->p3];
+  pOut = out2Prerelease(p, pOp);
+  pOut->flags = MEM_Null;
+  sqlite3VdbeMemSetPointer(pOut, pRhs, "ValueList", sqlite3_free);
+  break;
+}
+#endif /* SQLITE_OMIT_VIRTUALTABLE */
+
+
+#ifndef SQLITE_OMIT_VIRTUALTABLE
 /* Opcode: VFilter P1 P2 P3 P4 *
 ** Synopsis: iplan=r[P3] zplan='P4'
 **
diff --git a/src/vdbeInt.h b/src/vdbeInt.h
index 376c9edac..f02b37c6a 100644
--- a/src/vdbeInt.h
+++ b/src/vdbeInt.h
@@ -483,6 +483,24 @@ struct PreUpdate {
 };
 
 /*
+** An instance of this object is used to pass an vector of values into
+** OP_VFilter, the xFilter method of a virtual table.  The vector is the
+** set of values on the right-hand side of an IN constraint.
+**
+** The value as passed into xFilter is an sqlite3_value with a "pointer"
+** type, such as is generated by sqlite3_result_pointer() and read by
+** sqlite3_value_pointer.  Such values have MEM_Term|MEM_Subtype|MEM_Null
+** and a subtype of 'p'.  The sqlite3_vtab_in_first() and _next() interfaces
+** know how to use this object to step through all the values in the
+** right operand of the IN constraint.
+*/
+typedef struct ValueList ValueList;
+struct ValueList {
+  BtCursor *pCsr;          /* An ephemeral table holding all values */
+  sqlite3_value *pOut;     /* Register to hold each decoded output value */
+};
+
+/*
 ** Function prototypes
 */
 void sqlite3VdbeError(Vdbe*, const char *, ...);
diff --git a/src/vdbeapi.c b/src/vdbeapi.c
index 17df807de..9cc200298 100644
--- a/src/vdbeapi.c
+++ b/src/vdbeapi.c
@@ -847,6 +847,70 @@ int sqlite3_vtab_nochange(sqlite3_context *p){
 }
 
 /*
+** Implementation of sqlite3_vtab_in_first() (if bNext==0) and
+** sqlite3_vtab_in_next() (if bNext!=0).
+*/
+static int valueFromValueList(
+  sqlite3_value *pVal,        /* Pointer to the ValueList object */
+  sqlite3_value **ppOut,      /* Store the next value from the list here */
+  int bNext                   /* 1 for _next(). 0 for _first() */
+){
+  int rc;
+  ValueList *pRhs;
+
+  *ppOut = 0;
+  if( pVal==0 ) return SQLITE_MISUSE;
+  pRhs = (ValueList*)sqlite3_value_pointer(pVal, "ValueList");
+  if( pRhs==0 ) return SQLITE_MISUSE;
+  if( bNext ){
+    rc = sqlite3BtreeNext(pRhs->pCsr, 0);
+  }else{
+    int dummy = 0;
+    rc = sqlite3BtreeFirst(pRhs->pCsr, &dummy);
+    assert( rc==SQLITE_OK || sqlite3BtreeEof(pRhs->pCsr) );
+    if( sqlite3BtreeEof(pRhs->pCsr) ) rc = SQLITE_DONE;
+  }
+  if( rc==SQLITE_OK ){
+    u32 sz;       /* Size of current row in bytes */
+    Mem sMem;     /* Raw content of current row */
+    memset(&sMem, 0, sizeof(sMem));
+    sz = sqlite3BtreePayloadSize(pRhs->pCsr);
+    rc = sqlite3VdbeMemFromBtreeZeroOffset(pRhs->pCsr,(int)sz,&sMem);
+    if( rc==SQLITE_OK ){
+      u8 *zBuf = (u8*)sMem.z;
+      u32 iSerial;
+      sqlite3_value *pOut = pRhs->pOut;
+      int iOff = 1 + getVarint32(&zBuf[1], iSerial);
+      sqlite3VdbeSerialGet(&zBuf[iOff], iSerial, pOut);
+      pOut->enc = ENC(pOut->db);
+      if( (pOut->flags & MEM_Ephem)!=0 && sqlite3VdbeMemMakeWriteable(pOut) ){
+        rc = SQLITE_NOMEM;
+      }else{
+        *ppOut = pOut;
+      }
+    }
+    sqlite3VdbeMemRelease(&sMem);
+  }
+  return rc;
+}
+
+/*
+** Set the iterator value pVal to point to the first value in the set.
+** Set (*ppOut) to point to this value before returning.
+*/
+int sqlite3_vtab_in_first(sqlite3_value *pVal, sqlite3_value **ppOut){
+  return valueFromValueList(pVal, ppOut, 0);
+}
+
+/*
+** Set the iterator value pVal to point to the next value in the set.
+** Set (*ppOut) to point to this value before returning.
+*/
+int sqlite3_vtab_in_next(sqlite3_value *pVal, sqlite3_value **ppOut){
+  return valueFromValueList(pVal, ppOut, 1);
+}
+
+/*
 ** Return the current time for a statement.  If the current time
 ** is requested more than once within the same run of a single prepared
 ** statement, the exact same time is returned for each invocation regardless
diff --git a/src/where.c b/src/where.c
index 4efb7084d..11eae60e6 100644
--- a/src/where.c
+++ b/src/where.c
@@ -33,6 +33,8 @@ struct HiddenIndexInfo {
   WhereClause *pWC;        /* The Where clause being analyzed */
   Parse *pParse;           /* The parsing context */
   int eDistinct;           /* Value to return from sqlite3_vtab_distinct() */
+  u32 mIn;                 /* Mask of terms that are <col> IN (...) */
+  u32 mHandleIn;           /* Terms that vtab will handle as <col> IN (...) */
   sqlite3_value *aRhs[1];  /* RHS values for constraints. MUST BE LAST
                            ** because extra space is allocated to hold up
                            ** to nTerm such values */
@@ -1143,6 +1145,7 @@ static sqlite3_index_info *allocateIndexInfo(
     testcase( pTerm->eOperator & WO_ALL );
     if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue;
     if( pTerm->wtFlags & TERM_VNULL ) continue;
+
     assert( (pTerm->eOperator & (WO_OR|WO_AND))==0 );
     assert( pTerm->u.x.leftColumn>=XN_ROWID );
     assert( pTerm->u.x.leftColumn<pTab->nCol );
@@ -1204,7 +1207,7 @@ static sqlite3_index_info *allocateIndexInfo(
       /* No matches cause a break out of the loop */
       break;
     }
-    if( i==n){
+    if( i==n ){
       nOrderBy = n;
       if( (pWInfo->wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY)) ){
         eDistinct = 1 + ((pWInfo->wctrlFlags & WHERE_DISTINCTBY)!=0);
@@ -1232,13 +1235,17 @@ static sqlite3_index_info *allocateIndexInfo(
   pHidden->pWC = pWC;
   pHidden->pParse = pParse;
   pHidden->eDistinct = eDistinct;
+  pHidden->mIn = 0;
   for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
     u16 op;
     if( (pTerm->wtFlags & TERM_OK)==0 ) continue;
     pIdxCons[j].iColumn = pTerm->u.x.leftColumn;
     pIdxCons[j].iTermOffset = i;
     op = pTerm->eOperator & WO_ALL;
-    if( op==WO_IN ) op = WO_EQ;
+    if( op==WO_IN ){
+      pHidden->mIn |= SMASKBIT32(j);
+      op = WO_EQ;
+    }
     if( op==WO_AUX ){
       pIdxCons[j].op = pTerm->eMatchOp;
     }else if( op & (WO_ISNULL|WO_IS) ){
@@ -3499,6 +3506,7 @@ static int whereLoopAddVirtualOne(
   int *pbRetryLimit               /* OUT: Retry without LIMIT/OFFSET */
 ){
   WhereClause *pWC = pBuilder->pWC;
+  HiddenIndexInfo *pHidden = (HiddenIndexInfo*)&pIdxInfo[1];
   struct sqlite3_index_constraint *pIdxCons;
   struct sqlite3_index_constraint_usage *pUsage = pIdxInfo->aConstraintUsage;
   int i;
@@ -3537,6 +3545,7 @@ static int whereLoopAddVirtualOne(
   pIdxInfo->estimatedRows = 25;
   pIdxInfo->idxFlags = 0;
   pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed;
+  pHidden->mHandleIn = 0;
 
   /* Invoke the virtual table xBestIndex() method */
   rc = vtabBestIndex(pParse, pSrc->pTab, pIdxInfo);
@@ -3593,7 +3602,9 @@ static int whereLoopAddVirtualOne(
           pNew->u.vtab.bOmitOffset = 1;
         }
       }
-      if( (pTerm->eOperator & WO_IN)!=0 ){
+      if( SMASKBIT32(i) & pHidden->mHandleIn ){ 
+        pNew->u.vtab.mHandleIn |= MASKBIT32(iTerm);
+      }else if( (pTerm->eOperator & WO_IN)!=0 ){
         /* A virtual table that is constrained by an IN clause may not
         ** consume the ORDER BY clause because (1) the order of IN terms
         ** is not necessarily related to the order of output terms and
@@ -3692,6 +3703,25 @@ const char *sqlite3_vtab_collation(sqlite3_index_info *pIdxInfo, int iCons){
 }
 
 /*
+** Return true if constraint iCons is really an IN(...) constraint, or
+** false otherwise. If iCons is an IN(...) constraint, set (if bHandle!=0)
+** or clear (if bHandle==0) the flag to handle it using an iterator.
+*/
+int sqlite3_vtab_in(sqlite3_index_info *pIdxInfo, int iCons, int bHandle){
+  HiddenIndexInfo *pHidden = (HiddenIndexInfo*)&pIdxInfo[1];
+  u32 m = SMASKBIT32(iCons);
+  if( m & pHidden->mIn ){
+    if( bHandle==0 ){ 
+      pHidden->mHandleIn &= ~m;
+    }else if( bHandle>0 ){
+      pHidden->mHandleIn |= m;
+    }
+    return 1;
+  }
+  return 0;
+}
+
+/*
 ** This interface is callable from within the xBestIndex callback only.
 **
 ** If possible, set (*ppVal) to point to an object containing the value 
diff --git a/src/whereInt.h b/src/whereInt.h
index 14c40fcb4..fc3740f51 100644
--- a/src/whereInt.h
+++ b/src/whereInt.h
@@ -128,6 +128,7 @@ struct WhereLoop {
       i8 isOrdered;          /* True if satisfies ORDER BY */
       u16 omitMask;          /* Terms that may be omitted */
       char *idxStr;          /* Index identifier string */
+      u32 mHandleIn;         /* Terms to handle as IN(...) instead of == */
     } vtab;
   } u;
   u32 wsFlags;          /* WHERE_* flags describing the plan */
diff --git a/src/wherecode.c b/src/wherecode.c
index 2beb596e6..8d7163ce0 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -1532,8 +1532,15 @@ Bitmask sqlite3WhereCodeOneLoopStart(
       pTerm = pLoop->aLTerm[j];
       if( NEVER(pTerm==0) ) continue;
       if( pTerm->eOperator & WO_IN ){
-        codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget);
-        addrNotFound = pLevel->addrNxt;
+        if( SMASKBIT32(j) & pLoop->u.vtab.mHandleIn ){
+          int iTab = pParse->nTab++;
+          int iCache = ++pParse->nMem;
+          sqlite3CodeRhsOfIN(pParse, pTerm->pExpr, iTab);
+          sqlite3VdbeAddOp3(v, OP_VInitIn, iTab, iTarget, iCache);
+        }else{
+          codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget);
+          addrNotFound = pLevel->addrNxt;
+        }
       }else{
         Expr *pRight = pTerm->pExpr->pRight;
         codeExprOrVector(pParse, pRight, iTarget, 1);
@@ -1568,13 +1575,19 @@ Bitmask sqlite3WhereCodeOneLoopStart(
       iIn = 0;
     }
     for(j=nConstraint-1; j>=0; j--){
+      int bIn;     /* True to generate byte code to loop over RHS IN values */
       pTerm = pLoop->aLTerm[j];
-      if( (pTerm->eOperator & WO_IN)!=0 ) iIn--;
+      if( (pTerm->eOperator & WO_IN)!=0
+       && (SMASKBIT32(j) & pLoop->u.vtab.mHandleIn)==0
+      ){
+        bIn = 1;
+      }else{
+        bIn = 0;
+      }
+      if( bIn ) iIn--;
       if( j<16 && (pLoop->u.vtab.omitMask>>j)&1 ){
         disableTerm(pLevel, pTerm);
-      }else if( (pTerm->eOperator & WO_IN)!=0
-        && sqlite3ExprVectorSize(pTerm->pExpr->pLeft)==1
-      ){
+      }else if( bIn && sqlite3ExprVectorSize(pTerm->pExpr->pLeft)==1 ){
         Expr *pCompare;  /* The comparison operator */
         Expr *pRight;    /* RHS of the comparison */
         VdbeOp *pOp;     /* Opcode to access the value of the IN constraint */