Merge the virtual table query planner enhancement, the RTREE cost estimate

fix, and the statement journal spill delay enhancement from trunk.

FossilOrigin-Name: 17fd8f3cf0ec565e08403dc8e10a1cffc2bbe165

10 files changed, 342 insertions, 444 deletions

diff --git a/src/global.c b/src/global.c
index 294d62fea..bd70b3827 100644
--- a/src/global.c
+++ b/src/global.c
@@ -159,6 +159,18 @@ const unsigned char sqlite3CtypeMap[256] = {
 # define SQLITE_SORTER_PMASZ 250
 #endif
 
+/* Statement journals spill to disk when their size exceeds the following
+** threashold (in bytes). 0 means that statement journals are created and
+** written to disk immediately (the default behavior for SQLite versions
+** before 3.12.0).  -1 means always keep the entire statement journal in
+** memory.  (The statement journal is also always held entirely in memory
+** if journal_mode=MEMORY or if temp_store=MEMORY, regardless of this
+** setting.)
+*/
+#ifndef SQLITE_STMTJRNL_SPILL 
+# define SQLITE_STMTJRNL_SPILL (64*1024)
+#endif
+
 /*
 ** The following singleton contains the global configuration for
 ** the SQLite library.
@@ -173,6 +185,7 @@ SQLITE_WSD struct Sqlite3Config sqlite3Config = {
    0,                         /* neverCorrupt */
    128,                       /* szLookaside */
    500,                       /* nLookaside */
+   SQLITE_STMTJRNL_SPILL,     /* nStmtSpill */
    {0,0,0,0,0,0,0,0},         /* m */
    {0,0,0,0,0,0,0,0,0},       /* mutex */
    {0,0,0,0,0,0,0,0,0,0,0,0,0},/* pcache2 */
diff --git a/src/journal.c b/src/journal.c
deleted file mode 100644
index da59db0e5..000000000
--- a/src/journal.c
+++ /dev/null
@@ -1,258 +0,0 @@
-/*
-** 2007 August 22
-**
-** The author disclaims copyright to this source code.  In place of
-** a legal notice, here is a blessing:
-**
-**    May you do good and not evil.
-**    May you find forgiveness for yourself and forgive others.
-**    May you share freely, never taking more than you give.
-**
-*************************************************************************
-**
-** This file implements a special kind of sqlite3_file object used
-** by SQLite to create journal files if the atomic-write optimization
-** is enabled.
-**
-** The distinctive characteristic of this sqlite3_file is that the
-** actual on disk file is created lazily. When the file is created,
-** the caller specifies a buffer size for an in-memory buffer to
-** be used to service read() and write() requests. The actual file
-** on disk is not created or populated until either:
-**
-**   1) The in-memory representation grows too large for the allocated 
-**      buffer, or
-**   2) The sqlite3JournalCreate() function is called.
-*/
-#if 0 
-#ifdef SQLITE_ENABLE_ATOMIC_WRITE
-#include "sqliteInt.h"
-
-
-/*
-** A JournalFile object is a subclass of sqlite3_file used by
-** as an open file handle for journal files.
-*/
-struct JournalFile {
-  sqlite3_io_methods *pMethod;    /* I/O methods on journal files */
-  int nBuf;                       /* Size of zBuf[] in bytes */
-  char *zBuf;                     /* Space to buffer journal writes */
-  int iSize;                      /* Amount of zBuf[] currently used */
-  int flags;                      /* xOpen flags */
-  sqlite3_vfs *pVfs;              /* The "real" underlying VFS */
-  sqlite3_file *pReal;            /* The "real" underlying file descriptor */
-  const char *zJournal;           /* Name of the journal file */
-};
-typedef struct JournalFile JournalFile;
-
-/*
-** If it does not already exists, create and populate the on-disk file 
-** for JournalFile p.
-*/
-static int createFile(JournalFile *p){
-  int rc = SQLITE_OK;
-  if( !p->pReal ){
-    sqlite3_file *pReal = (sqlite3_file *)&p[1];
-    rc = sqlite3OsOpen(p->pVfs, p->zJournal, pReal, p->flags, 0);
-    if( rc==SQLITE_OK ){
-      p->pReal = pReal;
-      if( p->iSize>0 ){
-        assert(p->iSize<=p->nBuf);
-        rc = sqlite3OsWrite(p->pReal, p->zBuf, p->iSize, 0);
-      }
-      if( rc!=SQLITE_OK ){
-        /* If an error occurred while writing to the file, close it before
-        ** returning. This way, SQLite uses the in-memory journal data to 
-        ** roll back changes made to the internal page-cache before this
-        ** function was called.  */
-        sqlite3OsClose(pReal);
-        p->pReal = 0;
-      }
-    }
-  }
-  return rc;
-}
-
-/*
-** Close the file.
-*/
-static int jrnlClose(sqlite3_file *pJfd){
-  JournalFile *p = (JournalFile *)pJfd;
-  if( p->pReal ){
-    sqlite3OsClose(p->pReal);
-  }
-  sqlite3_free(p->zBuf);
-  return SQLITE_OK;
-}
-
-/*
-** Read data from the file.
-*/
-static int jrnlRead(
-  sqlite3_file *pJfd,    /* The journal file from which to read */
-  void *zBuf,            /* Put the results here */
-  int iAmt,              /* Number of bytes to read */
-  sqlite_int64 iOfst     /* Begin reading at this offset */
-){
-  int rc = SQLITE_OK;
-  JournalFile *p = (JournalFile *)pJfd;
-  if( p->pReal ){
-    rc = sqlite3OsRead(p->pReal, zBuf, iAmt, iOfst);
-  }else if( (iAmt+iOfst)>p->iSize ){
-    rc = SQLITE_IOERR_SHORT_READ;
-  }else{
-    memcpy(zBuf, &p->zBuf[iOfst], iAmt);
-  }
-  return rc;
-}
-
-/*
-** Write data to the file.
-*/
-static int jrnlWrite(
-  sqlite3_file *pJfd,    /* The journal file into which to write */
-  const void *zBuf,      /* Take data to be written from here */
-  int iAmt,              /* Number of bytes to write */
-  sqlite_int64 iOfst     /* Begin writing at this offset into the file */
-){
-  int rc = SQLITE_OK;
-  JournalFile *p = (JournalFile *)pJfd;
-  if( !p->pReal && (iOfst+iAmt)>p->nBuf ){
-    rc = createFile(p);
-  }
-  if( rc==SQLITE_OK ){
-    if( p->pReal ){
-      rc = sqlite3OsWrite(p->pReal, zBuf, iAmt, iOfst);
-    }else{
-      memcpy(&p->zBuf[iOfst], zBuf, iAmt);
-      if( p->iSize<(iOfst+iAmt) ){
-        p->iSize = (iOfst+iAmt);
-      }
-    }
-  }
-  return rc;
-}
-
-/*
-** Truncate the file.
-*/
-static int jrnlTruncate(sqlite3_file *pJfd, sqlite_int64 size){
-  int rc = SQLITE_OK;
-  JournalFile *p = (JournalFile *)pJfd;
-  if( p->pReal ){
-    rc = sqlite3OsTruncate(p->pReal, size);
-  }else if( size<p->iSize ){
-    p->iSize = size;
-  }
-  return rc;
-}
-
-/*
-** Sync the file.
-*/
-static int jrnlSync(sqlite3_file *pJfd, int flags){
-  int rc;
-  JournalFile *p = (JournalFile *)pJfd;
-  if( p->pReal ){
-    rc = sqlite3OsSync(p->pReal, flags);
-  }else{
-    rc = SQLITE_OK;
-  }
-  return rc;
-}
-
-/*
-** Query the size of the file in bytes.
-*/
-static int jrnlFileSize(sqlite3_file *pJfd, sqlite_int64 *pSize){
-  int rc = SQLITE_OK;
-  JournalFile *p = (JournalFile *)pJfd;
-  if( p->pReal ){
-    rc = sqlite3OsFileSize(p->pReal, pSize);
-  }else{
-    *pSize = (sqlite_int64) p->iSize;
-  }
-  return rc;
-}
-
-/*
-** Table of methods for JournalFile sqlite3_file object.
-*/
-static struct sqlite3_io_methods JournalFileMethods = {
-  1,             /* iVersion */
-  jrnlClose,     /* xClose */
-  jrnlRead,      /* xRead */
-  jrnlWrite,     /* xWrite */
-  jrnlTruncate,  /* xTruncate */
-  jrnlSync,      /* xSync */
-  jrnlFileSize,  /* xFileSize */
-  0,             /* xLock */
-  0,             /* xUnlock */
-  0,             /* xCheckReservedLock */
-  0,             /* xFileControl */
-  0,             /* xSectorSize */
-  0,             /* xDeviceCharacteristics */
-  0,             /* xShmMap */
-  0,             /* xShmLock */
-  0,             /* xShmBarrier */
-  0              /* xShmUnmap */
-};
-
-/* 
-** Open a journal file.
-*/
-int sqlite3JournalOpen(
-  sqlite3_vfs *pVfs,         /* The VFS to use for actual file I/O */
-  const char *zName,         /* Name of the journal file */
-  sqlite3_file *pJfd,        /* Preallocated, blank file handle */
-  int flags,                 /* Opening flags */
-  int nBuf                   /* Bytes buffered before opening the file */
-){
-  JournalFile *p = (JournalFile *)pJfd;
-  memset(p, 0, sqlite3JournalSize(pVfs));
-  if( nBuf>0 ){
-    p->zBuf = sqlite3MallocZero(nBuf);
-    if( !p->zBuf ){
-      return SQLITE_NOMEM_BKPT;
-    }
-  }else{
-    return sqlite3OsOpen(pVfs, zName, pJfd, flags, 0);
-  }
-  p->pMethod = &JournalFileMethods;
-  p->nBuf = nBuf;
-  p->flags = flags;
-  p->zJournal = zName;
-  p->pVfs = pVfs;
-  return SQLITE_OK;
-}
-
-/*
-** If the argument p points to a JournalFile structure, and the underlying
-** file has not yet been created, create it now.
-*/
-int sqlite3JournalCreate(sqlite3_file *p){
-  if( p->pMethods!=&JournalFileMethods ){
-    return SQLITE_OK;
-  }
-  return createFile((JournalFile *)p);
-}
-
-/*
-** The file-handle passed as the only argument is guaranteed to be an open
-** file. It may or may not be of class JournalFile. If the file is a
-** JournalFile, and the underlying file on disk has not yet been opened,
-** return 0. Otherwise, return 1.
-*/
-int sqlite3JournalExists(sqlite3_file *p){
-  return (p->pMethods!=&JournalFileMethods || ((JournalFile *)p)->pReal!=0);
-}
-
-/* 
-** Return the number of bytes required to store a JournalFile that uses vfs
-** pVfs to create the underlying on-disk files.
-*/
-int sqlite3JournalSize(sqlite3_vfs *pVfs){
-  return (pVfs->szOsFile+sizeof(JournalFile));
-}
-#endif
-#endif
diff --git a/src/main.c b/src/main.c
index 116353abe..422b970cc 100644
--- a/src/main.c
+++ b/src/main.c
@@ -633,6 +633,11 @@ int sqlite3_config(int op, ...){
       break;
     }
 
+    case SQLITE_CONFIG_STMTJRNL_SPILL: {
+      sqlite3GlobalConfig.nStmtSpill = va_arg(ap, int);
+      break;
+    }
+
     default: {
       rc = SQLITE_ERROR;
       break;
diff --git a/src/memjournal.c b/src/memjournal.c
index 9ecd2a9ba..04780df99 100644
--- a/src/memjournal.c
+++ b/src/memjournal.c
@@ -60,7 +60,7 @@ struct MemJournal {
   const sqlite3_io_methods *pMethod; /* Parent class. MUST BE FIRST */
   int nChunkSize;                 /* In-memory chunk-size */
 
-  int nBuf;                       /* Bytes of data before flushing */
+  int nSpill;                     /* Bytes of data before flushing */
   int nSize;                      /* Bytes of data currently in memory */
   FileChunk *pFirst;              /* Head of in-memory chunk-list */
   FilePoint endpoint;             /* Pointer to the end of the file */
@@ -109,7 +109,7 @@ static int memjrnlRead(
     do {
       int iSpace = p->nChunkSize - iChunkOffset;
       int nCopy = MIN(nRead, (p->nChunkSize - iChunkOffset));
-      memcpy(zOut, &pChunk->zChunk[iChunkOffset], nCopy);
+      memcpy(zOut, (u8*)pChunk->zChunk + iChunkOffset, nCopy);
       zOut += nCopy;
       nRead -= iSpace;
       iChunkOffset = 0;
@@ -137,7 +137,7 @@ static void memjrnlFreeChunks(MemJournal *p){
 /*
 ** Flush the contents of memory to a real file on disk.
 */
-static int createFile(MemJournal *p){
+static int memjrnlCreateFile(MemJournal *p){
   int rc = SQLITE_OK;
   if( !p->pReal ){
     sqlite3_file *pReal = (sqlite3_file *)&p[1];
@@ -153,7 +153,7 @@ static int createFile(MemJournal *p){
           nWrite = p->endpoint.iOffset % p->nChunkSize;
           if( nWrite==0 ) nWrite = p->nChunkSize;
         }
-        rc = sqlite3OsWrite(pReal, pIter->zChunk, nWrite, iOff);
+        rc = sqlite3OsWrite(pReal, (u8*)pIter->zChunk, nWrite, iOff);
         iOff += nWrite;
       }
       if( rc!=SQLITE_OK ){
@@ -192,8 +192,8 @@ static int memjrnlWrite(
   }
 
   /* If the file should be created now. */
-  else if( p->nBuf>0 && (iAmt+iOfst)>p->nBuf ){
-    int rc = createFile(p);
+  else if( p->nSpill>0 && (iAmt+iOfst)>p->nSpill ){
+    int rc = memjrnlCreateFile(p);
     if( rc==SQLITE_OK ){
       rc = memjrnlWrite(pJfd, zBuf, iAmt, iOfst);
     }
@@ -210,7 +210,7 @@ static int memjrnlWrite(
     assert( iOfst==p->endpoint.iOffset || iOfst==0 );
     if( iOfst==0 && p->pFirst ){
       assert( p->nChunkSize>iAmt );
-      memcpy(p->pFirst->zChunk, zBuf, iAmt);
+      memcpy((u8*)p->pFirst->zChunk, zBuf, iAmt);
     }else{
       while( nWrite>0 ){
         FileChunk *pChunk = p->endpoint.pChunk;
@@ -234,7 +234,7 @@ static int memjrnlWrite(
           p->endpoint.pChunk = pNew;
         }
 
-        memcpy(&p->endpoint.pChunk->zChunk[iChunkOffset], zWrite, iSpace);
+        memcpy((u8*)p->endpoint.pChunk->zChunk + iChunkOffset, zWrite, iSpace);
         zWrite += iSpace;
         nWrite -= iSpace;
         p->endpoint.iOffset += iSpace;
@@ -333,12 +333,12 @@ static const struct sqlite3_io_methods MemJournalMethods = {
 ** Open a journal file. 
 **
 ** The behaviour of the journal file depends on the value of parameter 
-** nBuf. If nBuf is 0, then the journal file is always create and 
-** accessed using the underlying VFS. If nBuf is less than zero, then
-** all content is always stored in main-memory. Finally, if nBuf is a
+** nSpill. If nSpill is 0, then the journal file is always create and 
+** accessed using the underlying VFS. If nSpill is less than zero, then
+** all content is always stored in main-memory. Finally, if nSpill is a
 ** positive value, then the journal file is initially created in-memory
 ** but may be flushed to disk later on. In this case the journal file is
-** flushed to disk either when it grows larger than nBuf bytes in size,
+** flushed to disk either when it grows larger than nSpill bytes in size,
 ** or when sqlite3JournalCreate() is called.
 */
 int sqlite3JournalOpen(
@@ -346,28 +346,28 @@ int sqlite3JournalOpen(
   const char *zName,         /* Name of the journal file */
   sqlite3_file *pJfd,        /* Preallocated, blank file handle */
   int flags,                 /* Opening flags */
-  int nBuf                   /* Bytes buffered before opening the file */
+  int nSpill                 /* Bytes buffered before opening the file */
 ){
   MemJournal *p = (MemJournal*)pJfd;
 
-  /* Zero the file-handle object. If nBuf was passed zero, initialize
+  /* Zero the file-handle object. If nSpill was passed zero, initialize
   ** it using the sqlite3OsOpen() function of the underlying VFS. In this
   ** case none of the code in this module is executed as a result of calls
   ** made on the journal file-handle.  */
   memset(p, 0, sizeof(MemJournal) + (pVfs ? pVfs->szOsFile : 0));
-  if( nBuf==0 ){
+  if( nSpill==0 ){
     return sqlite3OsOpen(pVfs, zName, pJfd, flags, 0);
   }
 
-  if( nBuf>0 ){
-    p->nChunkSize = nBuf;
+  if( nSpill>0 ){
+    p->nChunkSize = nSpill;
   }else{
     p->nChunkSize = 8 + MEMJOURNAL_DFLT_FILECHUNKSIZE - sizeof(FileChunk);
     assert( MEMJOURNAL_DFLT_FILECHUNKSIZE==fileChunkSize(p->nChunkSize) );
   }
 
   p->pMethod = (const sqlite3_io_methods*)&MemJournalMethods;
-  p->nBuf = nBuf;
+  p->nSpill = nSpill;
   p->flags = flags;
   p->zJournal = zName;
   p->pVfs = pVfs;
@@ -385,13 +385,13 @@ void sqlite3MemJournalOpen(sqlite3_file *pJfd){
 /*
 ** If the argument p points to a MemJournal structure that is not an 
 ** in-memory-only journal file (i.e. is one that was opened with a +ve
-** nBuf parameter), and the underlying file has not yet been created, 
+** nSpill parameter), and the underlying file has not yet been created, 
 ** create it now.
 */
 int sqlite3JournalCreate(sqlite3_file *p){
   int rc = SQLITE_OK;
-  if( p->pMethods==&MemJournalMethods && ((MemJournal*)p)->nBuf>0 ){
-    rc = createFile((MemJournal*)p);
+  if( p->pMethods==&MemJournalMethods && ((MemJournal*)p)->nSpill>0 ){
+    rc = memjrnlCreateFile((MemJournal*)p);
   }
   return rc;
 }
diff --git a/src/pager.c b/src/pager.c
index c74748c8e..f045ce0ef 100644
--- a/src/pager.c
+++ b/src/pager.c
@@ -4358,11 +4358,11 @@ static int openSubJournal(Pager *pPager){
     const int flags =  SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_READWRITE 
       | SQLITE_OPEN_CREATE | SQLITE_OPEN_EXCLUSIVE 
       | SQLITE_OPEN_DELETEONCLOSE;
-    int nBuf = 64*1024;
+    int nStmtSpill = sqlite3Config.nStmtSpill;
     if( pPager->journalMode==PAGER_JOURNALMODE_MEMORY || pPager->subjInMemory ){
-      nBuf = -1;
+      nStmtSpill = -1;
     }
-    rc = sqlite3JournalOpen(pPager->pVfs, 0, pPager->sjfd, flags, nBuf);
+    rc = sqlite3JournalOpen(pPager->pVfs, 0, pPager->sjfd, flags, nStmtSpill);
   }
   return rc;
 }
diff --git a/src/sqlite.h.in b/src/sqlite.h.in
index 31202ecc2..cce23aa22 100644
--- a/src/sqlite.h.in
+++ b/src/sqlite.h.in
@@ -1820,6 +1820,20 @@ struct sqlite3_mem_methods {
 ** is enabled (using the [PRAGMA threads] command) and the amount of content
 ** to be sorted exceeds the page size times the minimum of the
 ** [PRAGMA cache_size] setting and this value.
+**
+** [[SQLITE_CONFIG_STMTJRNL_SPILL]]
+** <dt>SQLITE_CONFIG_STMTJRNL_SPILL
+** <dd>^The SQLITE_CONFIG_STMTJRNL_SPILL option takes a single parameter which
+** becomes the [statement journal] spill-to-disk threshold.  
+** [Statement journals] are held in memory until their size (in bytes)
+** exceeds this threshold, at which point they are written to disk.
+** Or if the threshold is -1, statement journals are always held
+** exclusively in memory.
+** Since many statement journals never become large, setting the spill
+** threshold to a value such as 64KiB can greatly reduce the amount of
+** I/O required to support statement rollback.
+** The default value for this setting is controlled by the
+** [SQLITE_STMTJRNL_SPILL] compile-time option.
 ** </dl>
 */
 #define SQLITE_CONFIG_SINGLETHREAD  1  /* nil */
@@ -1847,6 +1861,7 @@ struct sqlite3_mem_methods {
 #define SQLITE_CONFIG_WIN32_HEAPSIZE      23  /* int nByte */
 #define SQLITE_CONFIG_PCACHE_HDRSZ        24  /* int *psz */
 #define SQLITE_CONFIG_PMASZ               25  /* unsigned int szPma */
+#define SQLITE_CONFIG_STMTJRNL_SPILL      26  /* int nByte */
 
 /*
 ** CAPI3REF: Database Connection Configuration Options
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 55e751574..e1e37b61d 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -3053,6 +3053,7 @@ struct Sqlite3Config {
   int neverCorrupt;                 /* Database is always well-formed */
   int szLookaside;                  /* Default lookaside buffer size */
   int nLookaside;                   /* Default lookaside buffer count */
+  int nStmtSpill;                   /* Stmt-journal spill-to-disk threshold */
   sqlite3_mem_methods m;            /* Low-level memory allocation interface */
   sqlite3_mutex_methods mutex;      /* Low-level mutex interface */
   sqlite3_pcache_methods2 pcache2;  /* Low-level page-cache interface */
@@ -3697,7 +3698,7 @@ LogEst sqlite3LogEstAdd(LogEst,LogEst);
 #ifndef SQLITE_OMIT_VIRTUALTABLE
 LogEst sqlite3LogEstFromDouble(double);
 #endif
-#if defined(SQLITE_ENABLE_STMT_SCANSTAT) || \
+#if defined(SQLITE_ENABLE_STMT_SCANSTATUS) || \
     defined(SQLITE_ENABLE_STAT3_OR_STAT4) || \
     defined(SQLITE_EXPLAIN_ESTIMATED_ROWS)
 u64 sqlite3LogEstToInt(LogEst);
diff --git a/src/test_bestindex.c b/src/test_bestindex.c
index ab10463fd..a955c2782 100644
--- a/src/test_bestindex.c
+++ b/src/test_bestindex.c
@@ -117,6 +117,40 @@ struct tcl_cursor {
 };
 
 /*
+** Dequote string z in place.
+*/
+static void tclDequote(char *z){
+  char q = z[0];
+
+  /* Set stack variable q to the close-quote character */
+  if( q=='[' || q=='\'' || q=='"' || q=='`' ){
+    int iIn = 1;
+    int iOut = 0;
+    if( q=='[' ) q = ']';  
+
+    while( ALWAYS(z[iIn]) ){
+      if( z[iIn]==q ){
+        if( z[iIn+1]!=q ){
+          /* Character iIn was the close quote. */
+          iIn++;
+          break;
+        }else{
+          /* Character iIn and iIn+1 form an escaped quote character. Skip
+          ** the input cursor past both and copy a single quote character 
+          ** to the output buffer. */
+          iIn += 2;
+          z[iOut++] = q;
+        }
+      }else{
+        z[iOut++] = z[iIn++];
+      }
+    }
+
+    z[iOut] = '\0';
+  }
+}
+
+/*
 ** This function is the implementation of both the xConnect and xCreate
 ** methods of the fs virtual table.
 **
@@ -135,43 +169,49 @@ static int tclConnect(
   char **pzErr
 ){
   Tcl_Interp *interp = (Tcl_Interp*)pAux;
-  tcl_vtab *pTab;
-  const char *zCmd;
+  tcl_vtab *pTab = 0;
+  char *zCmd = 0;
   Tcl_Obj *pScript = 0;
-  int rc;
+  int rc = SQLITE_OK;
 
   if( argc!=4 ){
     *pzErr = sqlite3_mprintf("wrong number of arguments");
     return SQLITE_ERROR;
   }
-  zCmd = argv[3];
 
+  zCmd = sqlite3_malloc(strlen(argv[3])+1);
   pTab = (tcl_vtab*)sqlite3_malloc(sizeof(tcl_vtab));
-  if( pTab==0 ) return SQLITE_NOMEM;
-  memset(pTab, 0, sizeof(tcl_vtab));
+  if( zCmd && pTab ){
+    memcpy(zCmd, argv[3], strlen(argv[3])+1);
+    tclDequote(zCmd);
+    memset(pTab, 0, sizeof(tcl_vtab));
 
-  pTab->pCmd = Tcl_NewStringObj(zCmd, -1);
-  pTab->interp = interp;
-  pTab->db = db;
-  Tcl_IncrRefCount(pTab->pCmd);
+    pTab->pCmd = Tcl_NewStringObj(zCmd, -1);
+    pTab->interp = interp;
+    pTab->db = db;
+    Tcl_IncrRefCount(pTab->pCmd);
 
-  pScript = Tcl_DuplicateObj(pTab->pCmd);
-  Tcl_IncrRefCount(pScript);
-  Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xConnect", -1));
+    pScript = Tcl_DuplicateObj(pTab->pCmd);
+    Tcl_IncrRefCount(pScript);
+    Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xConnect", -1));
 
-  rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL);
-  if( rc!=TCL_OK ){
-    *pzErr = sqlite3_mprintf("%s", Tcl_GetStringResult(interp));
-    rc = SQLITE_ERROR;
-  }else{
-    rc = sqlite3_declare_vtab(db, Tcl_GetStringResult(interp));
-  }
+    rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL);
+    if( rc!=TCL_OK ){
+      *pzErr = sqlite3_mprintf("%s", Tcl_GetStringResult(interp));
+      rc = SQLITE_ERROR;
+    }else{
+      rc = sqlite3_declare_vtab(db, Tcl_GetStringResult(interp));
+    }
 
-  if( rc!=SQLITE_OK ){
-    sqlite3_free(pTab);
-    pTab = 0;
+    if( rc!=SQLITE_OK ){
+      sqlite3_free(pTab);
+      pTab = 0;
+    }
+  }else{
+    rc = SQLITE_NOMEM;
   }
 
+  sqlite3_free(zCmd);
   *ppVtab = &pTab->base;
   return rc;
 }
@@ -454,7 +494,9 @@ static int tclBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
           pIdxInfo->needToFreeIdxStr = 1;
         }else
         if( sqlite3_stricmp("rows", zCmd)==0 ){
-          rc = Tcl_GetWideIntFromObj(interp, p, &pIdxInfo->estimatedRows);
+          Tcl_WideInt x = 0;
+          rc = Tcl_GetWideIntFromObj(interp, p, &x);
+          pIdxInfo->estimatedRows = (tRowcnt)x;
         }else
         if( sqlite3_stricmp("use", zCmd)==0 
          || sqlite3_stricmp("omit", zCmd)==0 
diff --git a/src/util.c b/src/util.c
index 81274260f..6aead47fa 100644
--- a/src/util.c
+++ b/src/util.c
@@ -1093,7 +1093,7 @@ u8 sqlite3GetVarint32(const unsigned char *p, u32 *v){
 */
 int sqlite3VarintLen(u64 v){
   int i;
-  for(i=1; (v >>= 7)!=0; i++){ assert( i<9 ); }
+  for(i=1; (v >>= 7)!=0; i++){ assert( i<10 ); }
   return i;
 }
 
@@ -1408,7 +1408,7 @@ LogEst sqlite3LogEstFromDouble(double x){
 }
 #endif /* SQLITE_OMIT_VIRTUALTABLE */
 
-#if defined(SQLITE_ENABLE_STMT_SCANSTAT) || \
+#if defined(SQLITE_ENABLE_STMT_SCANSTATUS) || \
     defined(SQLITE_ENABLE_STAT3_OR_STAT4) || \
     defined(SQLITE_EXPLAIN_ESTIMATED_ROWS)
 /*
diff --git a/src/where.c b/src/where.c
index ed90f6144..ea62617c5 100644
--- a/src/where.c
+++ b/src/where.c
@@ -2748,6 +2748,151 @@ static int whereLoopAddBtree(
 }
 
 #ifndef SQLITE_OMIT_VIRTUALTABLE
+
+/*
+** Argument pIdxInfo is already populated with all constraints that may
+** be used by the virtual table identified by pBuilder->pNew->iTab. This
+** function marks a subset of those constraints usable, invokes the
+** xBestIndex method and adds the returned plan to pBuilder.
+**
+** A constraint is marked usable if:
+**
+**   * Argument mUsable indicates that its prerequisites are available, and
+**
+**   * It is not one of the operators specified in the mExclude mask passed
+**     as the fourth argument (which in practice is either WO_IN or 0).
+**
+** Argument mExtra is a mask of tables that must be scanned before the
+** virtual table in question. These are added to the plans prerequisites
+** before it is added to pBuilder.
+**
+** Output parameter *pbIn is set to true if the plan added to pBuilder
+** uses one or more WO_IN terms, or false otherwise.
+*/
+static int whereLoopAddVirtualOne(
+  WhereLoopBuilder *pBuilder,
+  Bitmask mExtra,                 /* Mask of tables that must be used. */
+  Bitmask mUsable,                /* Mask of usable prereqs */
+  u16 mExclude,                   /* Exclude terms for this operator */
+  sqlite3_index_info *pIdxInfo,   /* Populated object for xBestIndex */
+  int *pbIn                       /* OUT: True if plan uses an IN(...) op */
+){
+  WhereClause *pWC = pBuilder->pWC;
+  struct sqlite3_index_constraint *pIdxCons;
+  struct sqlite3_index_constraint_usage *pUsage = pIdxInfo->aConstraintUsage;
+  int i;
+  int mxTerm;
+  int rc = SQLITE_OK;
+  WhereLoop *pNew = pBuilder->pNew;
+  Parse *pParse = pBuilder->pWInfo->pParse;
+  struct SrcList_item *pSrc = &pBuilder->pWInfo->pTabList->a[pNew->iTab];
+  int nConstraint = pIdxInfo->nConstraint;
+
+  assert( (mUsable & mExtra)==mExtra );
+  *pbIn = 0;
+  pNew->prereq = mExtra;
+
+  /* Set the usable flag on the subset of constraints identified by 
+  ** arguments mUsable and mExclude. */
+  pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
+  for(i=0; i<nConstraint; i++, pIdxCons++){
+    WhereTerm *pTerm = &pWC->a[pIdxCons->iTermOffset];
+    pIdxCons->usable = 0;
+    if( (pTerm->prereqRight & mUsable)==pTerm->prereqRight 
+     && (pTerm->eOperator & mExclude)==0
+    ){
+      pIdxCons->usable = 1;
+    }
+  }
+
+  /* Initialize the output fields of the sqlite3_index_info structure */
+  memset(pUsage, 0, sizeof(pUsage[0])*nConstraint);
+  if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
+  pIdxInfo->idxStr = 0;
+  pIdxInfo->idxNum = 0;
+  pIdxInfo->needToFreeIdxStr = 0;
+  pIdxInfo->orderByConsumed = 0;
+  pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
+  pIdxInfo->estimatedRows = 25;
+  pIdxInfo->idxFlags = 0;
+  pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed;
+
+  /* Invoke the virtual table xBestIndex() method */
+  rc = vtabBestIndex(pParse, pSrc->pTab, pIdxInfo);
+  if( rc ) return rc;
+
+  mxTerm = -1;
+  assert( pNew->nLSlot>=nConstraint );
+  for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
+  pNew->u.vtab.omitMask = 0;
+  pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
+  for(i=0; i<nConstraint; i++, pIdxCons++){
+    int iTerm;
+    if( (iTerm = pUsage[i].argvIndex - 1)>=0 ){
+      WhereTerm *pTerm;
+      int j = pIdxCons->iTermOffset;
+      if( iTerm>=nConstraint
+       || j<0
+       || j>=pWC->nTerm
+       || pNew->aLTerm[iTerm]!=0
+      ){
+        rc = SQLITE_ERROR;
+        sqlite3ErrorMsg(pParse,"%s.xBestIndex() malfunction",pSrc->pTab->zName);
+        return rc;
+      }
+      testcase( iTerm==nConstraint-1 );
+      testcase( j==0 );
+      testcase( j==pWC->nTerm-1 );
+      pTerm = &pWC->a[j];
+      pNew->prereq |= pTerm->prereqRight;
+      assert( iTerm<pNew->nLSlot );
+      pNew->aLTerm[iTerm] = pTerm;
+      if( iTerm>mxTerm ) mxTerm = iTerm;
+      testcase( iTerm==15 );
+      testcase( iTerm==16 );
+      if( iTerm<16 && pUsage[i].omit ) pNew->u.vtab.omitMask |= 1<<iTerm;
+      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
+        ** (2) Multiple outputs from a single IN value will not merge
+        ** together.  */
+        pIdxInfo->orderByConsumed = 0;
+        pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE;
+        *pbIn = 1;
+      }
+    }
+  }
+
+  pNew->nLTerm = mxTerm+1;
+  assert( pNew->nLTerm<=pNew->nLSlot );
+  pNew->u.vtab.idxNum = pIdxInfo->idxNum;
+  pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
+  pIdxInfo->needToFreeIdxStr = 0;
+  pNew->u.vtab.idxStr = pIdxInfo->idxStr;
+  pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ?
+      pIdxInfo->nOrderBy : 0);
+  pNew->rSetup = 0;
+  pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost);
+  pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows);
+
+  /* Set the WHERE_ONEROW flag if the xBestIndex() method indicated
+  ** that the scan will visit at most one row. Clear it otherwise. */
+  if( pIdxInfo->idxFlags & SQLITE_INDEX_SCAN_UNIQUE ){
+    pNew->wsFlags |= WHERE_ONEROW;
+  }else{
+    pNew->wsFlags &= ~WHERE_ONEROW;
+  }
+  whereLoopInsert(pBuilder, pNew);
+  if( pNew->u.vtab.needFree ){
+    sqlite3_free(pNew->u.vtab.idxStr);
+    pNew->u.vtab.needFree = 0;
+  }
+
+  return SQLITE_OK;
+}
+
+
 /*
 ** Add all WhereLoop objects for a table of the join identified by
 ** pBuilder->pNew->iTab.  That table is guaranteed to be a virtual table.
@@ -2778,167 +2923,102 @@ static int whereLoopAddVirtual(
   Bitmask mExtra,              /* Tables that must be scanned before this one */
   Bitmask mUnusable            /* Tables that must be scanned after this one */
 ){
+  int rc = SQLITE_OK;          /* Return code */
   WhereInfo *pWInfo;           /* WHERE analysis context */
   Parse *pParse;               /* The parsing context */
   WhereClause *pWC;            /* The WHERE clause */
   struct SrcList_item *pSrc;   /* The FROM clause term to search */
-  Table *pTab;
-  sqlite3 *db;
-  sqlite3_index_info *pIdxInfo;
-  struct sqlite3_index_constraint *pIdxCons;
-  struct sqlite3_index_constraint_usage *pUsage;
-  WhereTerm *pTerm;
-  int i, j;
-  int iTerm, mxTerm;
-  int nConstraint;
-  int seenIn = 0;              /* True if an IN operator is seen */
-  int seenVar = 0;             /* True if a non-constant constraint is seen */
-  int iPhase;                  /* 0: const w/o IN, 1: const, 2: no IN,  2: IN */
+  sqlite3_index_info *p;       /* Object to pass to xBestIndex() */
+  int nConstraint;             /* Number of constraints in p */
+  int bIn;                     /* True if plan uses IN(...) operator */
   WhereLoop *pNew;
-  int rc = SQLITE_OK;
+  Bitmask mBest;               /* Tables used by best possible plan */
 
   assert( (mExtra & mUnusable)==0 );
   pWInfo = pBuilder->pWInfo;
   pParse = pWInfo->pParse;
-  db = pParse->db;
   pWC = pBuilder->pWC;
   pNew = pBuilder->pNew;
   pSrc = &pWInfo->pTabList->a[pNew->iTab];
-  pTab = pSrc->pTab;
-  assert( IsVirtual(pTab) );
-  pIdxInfo = allocateIndexInfo(pParse, pWC, mUnusable, pSrc,pBuilder->pOrderBy);
-  if( pIdxInfo==0 ) return SQLITE_NOMEM_BKPT;
-  pNew->prereq = 0;
+  assert( IsVirtual(pSrc->pTab) );
+  p = allocateIndexInfo(pParse, pWC, mUnusable, pSrc,pBuilder->pOrderBy);
+  if( p==0 ) return SQLITE_NOMEM_BKPT;
   pNew->rSetup = 0;
   pNew->wsFlags = WHERE_VIRTUALTABLE;
   pNew->nLTerm = 0;
   pNew->u.vtab.needFree = 0;
-  pUsage = pIdxInfo->aConstraintUsage;
-  nConstraint = pIdxInfo->nConstraint;
-  if( whereLoopResize(db, pNew, nConstraint) ){
-    sqlite3DbFree(db, pIdxInfo);
+  nConstraint = p->nConstraint;
+  if( whereLoopResize(pParse->db, pNew, nConstraint) ){
+    sqlite3DbFree(pParse->db, p);
     return SQLITE_NOMEM_BKPT;
   }
 
-  for(iPhase=0; iPhase<=3; iPhase++){
-    if( !seenIn && (iPhase&1)!=0 ){
-      iPhase++;
-      if( iPhase>3 ) break;
-    }
-    if( !seenVar && iPhase>1 ) break;
-    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
-    for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
-      j = pIdxCons->iTermOffset;
-      pTerm = &pWC->a[j];
-      switch( iPhase ){
-        case 0:    /* Constants without IN operator */
-          pIdxCons->usable = 0;
-          if( (pTerm->eOperator & WO_IN)!=0 ){
-            seenIn = 1;
-          }
-          if( (pTerm->prereqRight & ~mExtra)!=0 ){
-            seenVar = 1;
-          }else if( (pTerm->eOperator & WO_IN)==0 ){
-            pIdxCons->usable = 1;
-          }
-          break;
-        case 1:    /* Constants with IN operators */
-          assert( seenIn );
-          pIdxCons->usable = (pTerm->prereqRight & ~mExtra)==0;
-          break;
-        case 2:    /* Variables without IN */
-          assert( seenVar );
-          pIdxCons->usable = (pTerm->eOperator & WO_IN)==0;
-          break;
-        default:   /* Variables with IN */
-          assert( seenVar && seenIn );
-          pIdxCons->usable = 1;
-          break;
-      }
-    }
-    memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint);
-    if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
-    pIdxInfo->idxStr = 0;
-    pIdxInfo->idxNum = 0;
-    pIdxInfo->needToFreeIdxStr = 0;
-    pIdxInfo->orderByConsumed = 0;
-    pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
-    pIdxInfo->estimatedRows = 25;
-    pIdxInfo->idxFlags = 0;
-    pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed;
-    rc = vtabBestIndex(pParse, pTab, pIdxInfo);
-    if( rc ) goto whereLoopAddVtab_exit;
-    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
-    pNew->prereq = mExtra;
-    mxTerm = -1;
-    assert( pNew->nLSlot>=nConstraint );
-    for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
-    pNew->u.vtab.omitMask = 0;
-    for(i=0; i<nConstraint; i++, pIdxCons++){
-      if( (iTerm = pUsage[i].argvIndex - 1)>=0 ){
-        j = pIdxCons->iTermOffset;
-        if( iTerm>=nConstraint
-         || j<0
-         || j>=pWC->nTerm
-         || pNew->aLTerm[iTerm]!=0
-        ){
-          rc = SQLITE_ERROR;
-          sqlite3ErrorMsg(pParse, "%s.xBestIndex() malfunction", pTab->zName);
-          goto whereLoopAddVtab_exit;
-        }
-        testcase( iTerm==nConstraint-1 );
-        testcase( j==0 );
-        testcase( j==pWC->nTerm-1 );
-        pTerm = &pWC->a[j];
-        pNew->prereq |= pTerm->prereqRight;
-        assert( iTerm<pNew->nLSlot );
-        pNew->aLTerm[iTerm] = pTerm;
-        if( iTerm>mxTerm ) mxTerm = iTerm;
-        testcase( iTerm==15 );
-        testcase( iTerm==16 );
-        if( iTerm<16 && pUsage[i].omit ) pNew->u.vtab.omitMask |= 1<<iTerm;
-        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
-          ** (2) Multiple outputs from a single IN value will not merge
-          ** together.  */
-          pIdxInfo->orderByConsumed = 0;
-          pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE;
-        }
+  /* First call xBestIndex() with all constraints usable. */
+  rc = whereLoopAddVirtualOne(pBuilder, mExtra, (Bitmask)(-1), 0, p, &bIn);
+  mBest = pNew->prereq & ~mExtra;
+
+  /* If the call to xBestIndex() with all terms enabled produced a plan
+  ** that does not require any source tables, there is no point in making
+  ** any further calls - if the xBestIndex() method is sane they will all
+  ** return the same plan anyway.
+  */
+  if( mBest ){
+    int seenZero = 0;             /* True if a plan with no prereqs seen */
+    int seenZeroNoIN = 0;         /* Plan with no prereqs and no IN(...) seen */
+    Bitmask mPrev = 0;
+    Bitmask mBestNoIn = 0;
+
+    /* If the plan produced by the earlier call uses an IN(...) term, call
+    ** xBestIndex again, this time with IN(...) terms disabled. */
+    if( rc==SQLITE_OK && bIn ){
+      rc = whereLoopAddVirtualOne(pBuilder, mExtra, (Bitmask)-1, WO_IN, p,&bIn);
+      mBestNoIn = pNew->prereq & ~mExtra;
+      if( mBestNoIn==0 ){
+        seenZero = 1;
+        if( bIn==0 ) seenZeroNoIN = 1;
       }
     }
-    if( i>=nConstraint ){
-      pNew->nLTerm = mxTerm+1;
-      assert( pNew->nLTerm<=pNew->nLSlot );
-      pNew->u.vtab.idxNum = pIdxInfo->idxNum;
-      pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
-      pIdxInfo->needToFreeIdxStr = 0;
-      pNew->u.vtab.idxStr = pIdxInfo->idxStr;
-      pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ?
-                                      pIdxInfo->nOrderBy : 0);
-      pNew->rSetup = 0;
-      pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost);
-      pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows);
 
-      /* Set the WHERE_ONEROW flag if the xBestIndex() method indicated
-      ** that the scan will visit at most one row. Clear it otherwise. */
-      if( pIdxInfo->idxFlags & SQLITE_INDEX_SCAN_UNIQUE ){
-        pNew->wsFlags |= WHERE_ONEROW;
-      }else{
-        pNew->wsFlags &= ~WHERE_ONEROW;
+    /* Call xBestIndex once for each distinct value of (prereqRight & ~mExtra) 
+    ** in the set of terms that apply to the current virtual table.  */
+    while( rc==SQLITE_OK ){
+      int i;
+      Bitmask mNext = (Bitmask)(-1);
+      assert( mNext>0 );
+      for(i=0; i<nConstraint; i++){
+        Bitmask mThis = (
+            pWC->a[p->aConstraint[i].iTermOffset].prereqRight & ~mExtra
+        );
+        if( mThis>mPrev && mThis<mNext ) mNext = mThis;
       }
-      whereLoopInsert(pBuilder, pNew);
-      if( pNew->u.vtab.needFree ){
-        sqlite3_free(pNew->u.vtab.idxStr);
-        pNew->u.vtab.needFree = 0;
+      mPrev = mNext;
+      if( mNext==(Bitmask)(-1) ) break;
+      if( mNext==mBest || mNext==mBestNoIn ) continue;
+      rc = whereLoopAddVirtualOne(pBuilder, mExtra, mNext|mExtra, 0, p, &bIn);
+      if( pNew->prereq==mExtra ){
+        seenZero = 1;
+        if( bIn==0 ) seenZeroNoIN = 1;
       }
     }
-  }  
 
-whereLoopAddVtab_exit:
-  if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
-  sqlite3DbFree(db, pIdxInfo);
+    /* If the calls to xBestIndex() in the above loop did not find a plan
+    ** that requires no source tables at all (i.e. one guaranteed to be
+    ** usable), make a call here with all source tables disabled */
+    if( rc==SQLITE_OK && seenZero==0 ){
+      rc = whereLoopAddVirtualOne(pBuilder, mExtra, mExtra, 0, p, &bIn);
+      if( bIn==0 ) seenZeroNoIN = 1;
+    }
+
+    /* If the calls to xBestIndex() have so far failed to find a plan
+    ** that requires no source tables at all and does not use an IN(...)
+    ** operator, make a final call to obtain one here.  */
+    if( rc==SQLITE_OK && seenZeroNoIN==0 ){
+      rc = whereLoopAddVirtualOne(pBuilder, mExtra, mExtra, WO_IN, p, &bIn);
+    }
+  }
+
+  if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr);
+  sqlite3DbFree(pParse->db, p);
   return rc;
 }
 #endif /* SQLITE_OMIT_VIRTUALTABLE */