Merge recent enhancements from trunk.

FossilOrigin-Name: 5fbcb208d24d45169fc53ad8738dd3545d9bbd26b7434e31afc7f6419cd4e958

15 files changed, 560 insertions, 258 deletions

diff --git a/src/attach.c b/src/attach.c
index 3b5c57f0c..04274c6a7 100644
--- a/src/attach.c
+++ b/src/attach.c
@@ -434,6 +434,63 @@ void sqlite3Attach(Parse *pParse, Expr *p, Expr *pDbname, Expr *pKey){
 #endif /* SQLITE_OMIT_ATTACH */
 
 /*
+** Expression callback used by sqlite3FixAAAA() routines.
+*/
+static int fixExprCb(Walker *p, Expr *pExpr){
+  DbFixer *pFix = p->u.pFix;
+  if( !pFix->bTemp ) ExprSetProperty(pExpr, EP_FromDDL);
+  if( pExpr->op==TK_VARIABLE ){
+    if( pFix->pParse->db->init.busy ){
+      pExpr->op = TK_NULL;
+    }else{
+      sqlite3ErrorMsg(pFix->pParse, "%s cannot use variables", pFix->zType);
+      return WRC_Abort;
+    }
+  }
+  return WRC_Continue;
+}
+
+/*
+** Select callback used by sqlite3FixAAAA() routines.
+*/
+static int fixSelectCb(Walker *p, Select *pSelect){
+  DbFixer *pFix = p->u.pFix;
+  int i;
+  struct SrcList_item *pItem;
+  sqlite3 *db = pFix->pParse->db;
+  int iDb = sqlite3FindDbName(db, pFix->zDb);
+  SrcList *pList = pSelect->pSrc;
+
+  if( NEVER(pList==0) ) return WRC_Continue;
+  for(i=0, pItem=pList->a; i<pList->nSrc; i++, pItem++){
+    if( pFix->bTemp==0 ){
+      if( pItem->zDatabase && iDb!=sqlite3FindDbName(db, pItem->zDatabase) ){
+        sqlite3ErrorMsg(pFix->pParse,
+            "%s %T cannot reference objects in database %s",
+            pFix->zType, pFix->pName, pItem->zDatabase);
+        return WRC_Abort;
+      }
+      sqlite3DbFree(db, pItem->zDatabase);
+      pItem->zDatabase = 0;
+      pItem->pSchema = pFix->pSchema;
+      pItem->fg.fromDDL = 1;
+    }
+#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
+    if( sqlite3WalkExpr(&pFix->w, pList->a[i].pOn) ) return WRC_Abort;
+#endif
+  }
+  if( pSelect->pWith ){
+    int i;
+    for(i=0; i<pSelect->pWith->nCte; i++){
+      if( sqlite3WalkSelect(p, pSelect->pWith->a[i].pSelect) ){
+        return WRC_Abort;
+      }
+    }
+  }
+  return WRC_Continue;
+}
+
+/*
 ** Initialize a DbFixer structure.  This routine must be called prior
 ** to passing the structure to one of the sqliteFixAAAA() routines below.
 */
@@ -444,9 +501,7 @@ void sqlite3FixInit(
   const char *zType,  /* "view", "trigger", or "index" */
   const Token *pName  /* Name of the view, trigger, or index */
 ){
-  sqlite3 *db;
-
-  db = pParse->db;
+  sqlite3 *db = pParse->db;
   assert( db->nDb>iDb );
   pFix->pParse = pParse;
   pFix->zDb = db->aDb[iDb].zDbSName;
@@ -454,6 +509,13 @@ void sqlite3FixInit(
   pFix->zType = zType;
   pFix->pName = pName;
   pFix->bTemp = (iDb==1);
+  pFix->w.pParse = pParse;
+  pFix->w.xExprCallback = fixExprCb;
+  pFix->w.xSelectCallback = fixSelectCb;
+  pFix->w.xSelectCallback2 = 0;
+  pFix->w.walkerDepth = 0;
+  pFix->w.eCode = 0;
+  pFix->w.u.pFix = pFix;
 }
 
 /*
@@ -474,115 +536,27 @@ int sqlite3FixSrcList(
   DbFixer *pFix,       /* Context of the fixation */
   SrcList *pList       /* The Source list to check and modify */
 ){
-  int i;
-  struct SrcList_item *pItem;
-  sqlite3 *db = pFix->pParse->db;
-  int iDb = sqlite3FindDbName(db, pFix->zDb);
-
-  if( NEVER(pList==0) ) return 0;
-
-  for(i=0, pItem=pList->a; i<pList->nSrc; i++, pItem++){
-    if( pFix->bTemp==0 ){
-      if( pItem->zDatabase && iDb!=sqlite3FindDbName(db, pItem->zDatabase) ){
-        sqlite3ErrorMsg(pFix->pParse,
-            "%s %T cannot reference objects in database %s",
-            pFix->zType, pFix->pName, pItem->zDatabase);
-        return 1;
-      }
-      sqlite3DbFree(db, pItem->zDatabase);
-      pItem->zDatabase = 0;
-      pItem->pSchema = pFix->pSchema;
-      pItem->fg.fromDDL = 1;
-    }
-#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
-    if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1;
-    if( sqlite3FixExpr(pFix, pItem->pOn) ) return 1;
-#endif
-    if( pItem->fg.isTabFunc && sqlite3FixExprList(pFix, pItem->u1.pFuncArg) ){
-      return 1;
-    }
+  int res = 0;
+  if( pList ){
+    Select s; 
+    memset(&s, 0, sizeof(s));
+    s.pSrc = pList;
+    res = sqlite3WalkSelect(&pFix->w, &s);
   }
-  return 0;
+  return res;
 }
 #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
 int sqlite3FixSelect(
   DbFixer *pFix,       /* Context of the fixation */
   Select *pSelect      /* The SELECT statement to be fixed to one database */
 ){
-  while( pSelect ){
-    if( sqlite3FixExprList(pFix, pSelect->pEList) ){
-      return 1;
-    }
-    if( sqlite3FixSrcList(pFix, pSelect->pSrc) ){
-      return 1;
-    }
-    if( sqlite3FixExpr(pFix, pSelect->pWhere) ){
-      return 1;
-    }
-    if( sqlite3FixExprList(pFix, pSelect->pGroupBy) ){
-      return 1;
-    }
-    if( sqlite3FixExpr(pFix, pSelect->pHaving) ){
-      return 1;
-    }
-    if( sqlite3FixExprList(pFix, pSelect->pOrderBy) ){
-      return 1;
-    }
-    if( sqlite3FixExpr(pFix, pSelect->pLimit) ){
-      return 1;
-    }
-    if( pSelect->pWith ){
-      int i;
-      for(i=0; i<pSelect->pWith->nCte; i++){
-        if( sqlite3FixSelect(pFix, pSelect->pWith->a[i].pSelect) ){
-          return 1;
-        }
-      }
-    }
-    pSelect = pSelect->pPrior;
-  }
-  return 0;
+  return sqlite3WalkSelect(&pFix->w, pSelect);
 }
 int sqlite3FixExpr(
   DbFixer *pFix,     /* Context of the fixation */
   Expr *pExpr        /* The expression to be fixed to one database */
 ){
-  while( pExpr ){
-    if( !pFix->bTemp ) ExprSetProperty(pExpr, EP_FromDDL);
-    if( pExpr->op==TK_VARIABLE ){
-      if( pFix->pParse->db->init.busy ){
-        pExpr->op = TK_NULL;
-      }else{
-        sqlite3ErrorMsg(pFix->pParse, "%s cannot use variables", pFix->zType);
-        return 1;
-      }
-    }
-    if( ExprHasProperty(pExpr, EP_TokenOnly|EP_Leaf) ) break;
-    if( ExprHasProperty(pExpr, EP_xIsSelect) ){
-      if( sqlite3FixSelect(pFix, pExpr->x.pSelect) ) return 1;
-    }else{
-      if( sqlite3FixExprList(pFix, pExpr->x.pList) ) return 1;
-    }
-    if( sqlite3FixExpr(pFix, pExpr->pRight) ){
-      return 1;
-    }
-    pExpr = pExpr->pLeft;
-  }
-  return 0;
-}
-int sqlite3FixExprList(
-  DbFixer *pFix,     /* Context of the fixation */
-  ExprList *pList    /* The expression to be fixed to one database */
-){
-  int i;
-  struct ExprList_item *pItem;
-  if( pList==0 ) return 0;
-  for(i=0, pItem=pList->a; i<pList->nExpr; i++, pItem++){
-    if( sqlite3FixExpr(pFix, pItem->pExpr) ){
-      return 1;
-    }
-  }
-  return 0;
+  return sqlite3WalkExpr(&pFix->w, pExpr);
 }
 #endif
 
@@ -592,25 +566,20 @@ int sqlite3FixTriggerStep(
   TriggerStep *pStep /* The trigger step be fixed to one database */
 ){
   while( pStep ){
-    if( sqlite3FixSelect(pFix, pStep->pSelect) ){
-      return 1;
-    }
-    if( sqlite3FixExpr(pFix, pStep->pWhere) ){
-      return 1;
-    }
-    if( sqlite3FixExprList(pFix, pStep->pExprList) ){
-      return 1;
-    }
-    if( pStep->pFrom && sqlite3FixSrcList(pFix, pStep->pFrom) ){
+    if( sqlite3WalkSelect(&pFix->w, pStep->pSelect)
+     || sqlite3WalkExpr(&pFix->w, pStep->pWhere) 
+     || sqlite3WalkExprList(&pFix->w, pStep->pExprList)
+     || sqlite3FixSrcList(pFix, pStep->pFrom)
+    ){
       return 1;
     }
 #ifndef SQLITE_OMIT_UPSERT
     if( pStep->pUpsert ){
       Upsert *pUp = pStep->pUpsert;
-      if( sqlite3FixExprList(pFix, pUp->pUpsertTarget)
-       || sqlite3FixExpr(pFix, pUp->pUpsertTargetWhere)
-       || sqlite3FixExprList(pFix, pUp->pUpsertSet)
-       || sqlite3FixExpr(pFix, pUp->pUpsertWhere)
+      if( sqlite3WalkExprList(&pFix->w, pUp->pUpsertTarget)
+       || sqlite3WalkExpr(&pFix->w, pUp->pUpsertTargetWhere)
+       || sqlite3WalkExprList(&pFix->w, pUp->pUpsertSet)
+       || sqlite3WalkExpr(&pFix->w, pUp->pUpsertWhere)
       ){
         return 1;
       }
@@ -618,6 +587,7 @@ int sqlite3FixTriggerStep(
 #endif
     pStep = pStep->pNext;
   }
+
   return 0;
 }
 #endif
diff --git a/src/btree.c b/src/btree.c
index a587332ce..a3fbfef04 100644
--- a/src/btree.c
+++ b/src/btree.c
@@ -2901,6 +2901,7 @@ int sqlite3BtreeSetPageSize(Btree *p, int pageSize, int nReserve, int iFix){
         ((pageSize-1)&pageSize)==0 ){
     assert( (pageSize & 7)==0 );
     assert( !pBt->pCursor );
+    if( nReserve>32 && pageSize==512 ) pageSize = 1024;
     pBt->pageSize = (u32)pageSize;
     freeTempSpace(pBt);
   }
diff --git a/src/expr.c b/src/expr.c
index 685f04175..f225b59bc 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -95,7 +95,18 @@ Expr *sqlite3ExprAddCollateToken(
   const Token *pCollName,  /* Name of collating sequence */
   int dequote              /* True to dequote pCollName */
 ){
-  if( pCollName->n>0 ){
+  assert( pExpr!=0 || pParse->db->mallocFailed );
+  if( pExpr==0 ) return 0;
+  if( pExpr->op==TK_VECTOR ){
+    ExprList *pList = pExpr->x.pList;
+    if( ALWAYS(pList!=0) ){
+      int i;
+      for(i=0; i<pList->nExpr; i++){
+        pList->a[i].pExpr = sqlite3ExprAddCollateToken(pParse,pList->a[i].pExpr,
+                                                       pCollName, dequote);
+      }
+    }
+  }else if( pCollName->n>0 ){
     Expr *pNew = sqlite3ExprAlloc(pParse->db, TK_COLLATE, pCollName, dequote);
     if( pNew ){
       pNew->pLeft = pExpr;
diff --git a/src/func.c b/src/func.c
index dcf5e6cf2..e6f293ef0 100644
--- a/src/func.c
+++ b/src/func.c
@@ -1865,7 +1865,9 @@ void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
 int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
   FuncDef *pDef;
   int nExpr;
-  if( pExpr->op!=TK_FUNCTION || !pExpr->x.pList ){
+  assert( pExpr!=0 );
+  assert( pExpr->op==TK_FUNCTION );
+  if( !pExpr->x.pList ){
     return 0;
   }
   assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
diff --git a/src/insert.c b/src/insert.c
index c0ab0cf37..6047969c0 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -1301,7 +1301,9 @@ void sqlite3Insert(
     sqlite3VdbeJumpHere(v, addrInsTop);
   }
 
+#ifndef SQLITE_OMIT_XFER_OPT
 insert_end:
+#endif /* SQLITE_OMIT_XFER_OPT */
   /* Update the sqlite_sequence table by storing the content of the
   ** maximum rowid counter values recorded while inserting into
   ** autoincrement tables.
diff --git a/src/main.c b/src/main.c
index bbf81e778..0f3989d9d 100644
--- a/src/main.c
+++ b/src/main.c
@@ -4080,7 +4080,7 @@ int sqlite3_test_control(int op, ...){
     */
     case SQLITE_TESTCTRL_OPTIMIZATIONS: {
       sqlite3 *db = va_arg(ap, sqlite3*);
-      db->dbOptFlags = (u16)(va_arg(ap, int) & 0xffff);
+      db->dbOptFlags = va_arg(ap, u32);
       break;
     }
 
diff --git a/src/prepare.c b/src/prepare.c
index f93a6f07a..87c1ab936 100644
--- a/src/prepare.c
+++ b/src/prepare.c
@@ -571,10 +571,10 @@ void sqlite3ParserReset(Parse *pParse){
     pThis = pNext;
   }
   while( pParse->pCleanup ){
-    ParseCleanup *pThis = pParse->pCleanup;
-    pParse->pCleanup = pThis->pNext;
-    pThis->xCleanup(db, pThis->pPtr);
-    sqlite3DbFree(db, pThis);
+    ParseCleanup *pCleanup = pParse->pCleanup;
+    pParse->pCleanup = pCleanup->pNext;
+    pCleanup->xCleanup(db, pCleanup->pPtr);
+    sqlite3DbFree(db, pCleanup);
   }
   sqlite3DbFree(db, pParse->aLabel);
   if( pParse->pConstExpr ){
diff --git a/src/select.c b/src/select.c
index c4d17810e..7c3019890 100644
--- a/src/select.c
+++ b/src/select.c
@@ -4640,7 +4640,11 @@ static u8 minMaxQuery(sqlite3 *db, Expr *pFunc, ExprList **ppMinMax){
   assert( *ppMinMax==0 );
   assert( pFunc->op==TK_AGG_FUNCTION );
   assert( !IsWindowFunc(pFunc) );
-  if( pEList==0 || pEList->nExpr!=1 || ExprHasProperty(pFunc, EP_WinFunc) ){
+  if( pEList==0 
+   || pEList->nExpr!=1
+   || ExprHasProperty(pFunc, EP_WinFunc)
+   || OptimizationDisabled(db, SQLITE_MinMaxOpt)
+  ){
     return eRet;
   }
   zFunc = pFunc->u.zToken;
@@ -6580,6 +6584,10 @@ int sqlite3Select(
       int ii;
       SELECTTRACE(0x400,pParse,p,("After aggregate analysis %p:\n", pAggInfo));
       sqlite3TreeViewSelect(0, p, 0);
+      if( minMaxFlag ){
+        sqlite3DebugPrintf("MIN/MAX Optimization (0x%02x) adds:\n", minMaxFlag);
+        sqlite3TreeViewExprList(0, pMinMaxOrderBy, 0, "ORDERBY");
+      }
       for(ii=0; ii<pAggInfo->nColumn; ii++){
         sqlite3DebugPrintf("agg-column[%d] iMem=%d\n",
             ii, pAggInfo->aCol[ii].iMem);
@@ -6769,7 +6777,7 @@ int sqlite3Select(
       /* End of the loop
       */
       if( groupBySort ){
-        sqlite3VdbeAddOp2(v, OP_SorterNext, pAggInfo->sortingIdx, addrTopOfLoop);
+        sqlite3VdbeAddOp2(v, OP_SorterNext, pAggInfo->sortingIdx,addrTopOfLoop);
         VdbeCoverage(v);
       }else{
         sqlite3WhereEnd(pWInfo);
@@ -6881,7 +6889,6 @@ int sqlite3Select(
         explainSimpleCount(pParse, pTab, pBest);
       }else{
         int regAcc = 0;           /* "populate accumulators" flag */
-        int addrSkip;
 
         /* If there are accumulator registers but no min() or max() functions
         ** without FILTER clauses, allocate register regAcc. Register regAcc
@@ -6930,9 +6937,8 @@ int sqlite3Select(
         }
         updateAccumulator(pParse, regAcc, pAggInfo);
         if( regAcc ) sqlite3VdbeAddOp2(v, OP_Integer, 1, regAcc);
-        addrSkip = sqlite3WhereOrderByLimitOptLabel(pWInfo);
-        if( addrSkip!=sqlite3WhereContinueLabel(pWInfo) ){
-          sqlite3VdbeGoto(v, addrSkip);
+        if( minMaxFlag ){
+          sqlite3WhereMinMaxOptEarlyOut(v, pWInfo);
         }
         sqlite3WhereEnd(pWInfo);
         finalizeAggFunctions(pParse, pAggInfo);
diff --git a/src/shell.c.in b/src/shell.c.in
index 806ca2102..bfb964812 100644
--- a/src/shell.c.in
+++ b/src/shell.c.in
@@ -2027,6 +2027,7 @@ static int shell_callback(
       if( azArg==0 ) break;
       for(i=0; i<nArg; i++){
         int w = aExplainWidth[i];
+        if( i==nArg-1 ) w = 0;
         if( azArg[i] && strlenChar(azArg[i])>w ){
           w = strlenChar(azArg[i]);
         }
@@ -9994,7 +9995,7 @@ static int do_meta_command(char *zLine, ShellState *p){
         /* sqlite3_test_control(int, db, int) */
         case SQLITE_TESTCTRL_OPTIMIZATIONS:
           if( nArg==3 ){
-            int opt = (int)strtol(azArg[2], 0, 0);
+            unsigned int opt = (unsigned int)strtol(azArg[2], 0, 0);
             rc2 = sqlite3_test_control(testctrl, p->db, opt);
             isOk = 3;
           }
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index c7dd11862..20943a476 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1137,6 +1137,7 @@ typedef struct Bitvec Bitvec;
 typedef struct CollSeq CollSeq;
 typedef struct Column Column;
 typedef struct Db Db;
+typedef struct DbFixer DbFixer;
 typedef struct Schema Schema;
 typedef struct Expr Expr;
 typedef struct ExprList ExprList;
@@ -1496,7 +1497,7 @@ struct sqlite3 {
   int errCode;                  /* Most recent error code (SQLITE_*) */
   int errMask;                  /* & result codes with this before returning */
   int iSysErrno;                /* Errno value from last system error */
-  u16 dbOptFlags;               /* Flags to enable/disable optimizations */
+  u32 dbOptFlags;               /* Flags to enable/disable optimizations */
   u8 enc;                       /* Text encoding */
   u8 autoCommit;                /* The auto-commit flag. */
   u8 temp_store;                /* 1: file 2: memory 0: default */
@@ -1703,24 +1704,26 @@ struct sqlite3 {
 ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
 ** selectively disable various optimizations.
 */
-#define SQLITE_QueryFlattener 0x0001   /* Query flattening */
-#define SQLITE_WindowFunc     0x0002   /* Use xInverse for window functions */
-#define SQLITE_GroupByOrder   0x0004   /* GROUPBY cover of ORDERBY */
-#define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
-#define SQLITE_DistinctOpt    0x0010   /* DISTINCT using indexes */
-#define SQLITE_CoverIdxScan   0x0020   /* Covering index scans */
-#define SQLITE_OrderByIdxJoin 0x0040   /* ORDER BY of joins via index */
-#define SQLITE_Transitive     0x0080   /* Transitive constraints */
-#define SQLITE_OmitNoopJoin   0x0100   /* Omit unused tables in joins */
-#define SQLITE_CountOfView    0x0200   /* The count-of-view optimization */
-#define SQLITE_CursorHints    0x0400   /* Add OP_CursorHint opcodes */
-#define SQLITE_Stat4          0x0800   /* Use STAT4 data */
-   /* TH3 expects the Stat4   ^^^^^^ value to be 0x0800.  Don't change it */
-#define SQLITE_PushDown       0x1000   /* The push-down optimization */
-#define SQLITE_SimplifyJoin   0x2000   /* Convert LEFT JOIN to JOIN */
-#define SQLITE_SkipScan       0x4000   /* Skip-scans */
-#define SQLITE_PropagateConst 0x8000   /* The constant propagation opt */
-#define SQLITE_AllOpts        0xffff   /* All optimizations */
+#define SQLITE_QueryFlattener 0x00000001 /* Query flattening */
+#define SQLITE_WindowFunc     0x00000002 /* Use xInverse for window functions */
+#define SQLITE_GroupByOrder   0x00000004 /* GROUPBY cover of ORDERBY */
+#define SQLITE_FactorOutConst 0x00000008 /* Constant factoring */
+#define SQLITE_DistinctOpt    0x00000010 /* DISTINCT using indexes */
+#define SQLITE_CoverIdxScan   0x00000020 /* Covering index scans */
+#define SQLITE_OrderByIdxJoin 0x00000040 /* ORDER BY of joins via index */
+#define SQLITE_Transitive     0x00000080 /* Transitive constraints */
+#define SQLITE_OmitNoopJoin   0x00000100 /* Omit unused tables in joins */
+#define SQLITE_CountOfView    0x00000200 /* The count-of-view optimization */
+#define SQLITE_CursorHints    0x00000400 /* Add OP_CursorHint opcodes */
+#define SQLITE_Stat4          0x00000800 /* Use STAT4 data */
+   /* TH3 expects this value  ^^^^^^^^^^ to be 0x0000800. Don't change it */
+#define SQLITE_PushDown       0x00001000 /* The push-down optimization */
+#define SQLITE_SimplifyJoin   0x00002000 /* Convert LEFT JOIN to JOIN */
+#define SQLITE_SkipScan       0x00004000 /* Skip-scans */
+#define SQLITE_PropagateConst 0x00008000 /* The constant propagation opt */
+#define SQLITE_MinMaxOpt      0x00010000 /* The min/max optimization */
+#define SQLITE_ExistsToIN     0x00020000 /* The EXISTS-to-IN optimization */
+#define SQLITE_AllOpts        0xffffffff /* All optimizations */
 
 /*
 ** Macros for testing whether or not optimizations are enabled or disabled.
@@ -3650,21 +3653,6 @@ struct TriggerStep {
 };
 
 /*
-** The following structure contains information used by the sqliteFix...
-** routines as they walk the parse tree to make database references
-** explicit.
-*/
-typedef struct DbFixer DbFixer;
-struct DbFixer {
-  Parse *pParse;      /* The parsing context.  Error messages written here */
-  Schema *pSchema;    /* Fix items to this schema */
-  u8 bTemp;           /* True for TEMP schema entries */
-  const char *zDb;    /* Make sure all objects are contained in this database */
-  const char *zType;  /* Type of the container - used for error messages */
-  const Token *pName; /* Name of the container - used for error messages */
-};
-
-/*
 ** An objected used to accumulate the text of a string where we
 ** do not necessarily know how big the string will be in the end.
 */
@@ -3814,9 +3802,25 @@ struct Walker {
     struct RenameCtx *pRename;                /* RENAME COLUMN context */
     struct Table *pTab;                       /* Table of generated column */
     struct SrcList_item *pSrcItem;            /* A single FROM clause item */
+    DbFixer *pFix;
   } u;
 };
 
+/*
+** The following structure contains information used by the sqliteFix...
+** routines as they walk the parse tree to make database references
+** explicit.
+*/
+struct DbFixer {
+  Parse *pParse;      /* The parsing context.  Error messages written here */
+  Walker w;           /* Walker object */
+  Schema *pSchema;    /* Fix items to this schema */
+  u8 bTemp;           /* True for TEMP schema entries */
+  const char *zDb;    /* Make sure all objects are contained in this database */
+  const char *zType;  /* Type of the container - used for error messages */
+  const Token *pName; /* Name of the container - used for error messages */
+};
+
 /* Forward declarations */
 int sqlite3WalkExpr(Walker*, Expr*);
 int sqlite3WalkExprList(Walker*, ExprList*);
@@ -4343,6 +4347,7 @@ LogEst sqlite3WhereOutputRowCount(WhereInfo*);
 int sqlite3WhereIsDistinct(WhereInfo*);
 int sqlite3WhereIsOrdered(WhereInfo*);
 int sqlite3WhereOrderByLimitOptLabel(WhereInfo*);
+void sqlite3WhereMinMaxOptEarlyOut(Vdbe*,WhereInfo*);
 int sqlite3WhereIsSorted(WhereInfo*);
 int sqlite3WhereContinueLabel(WhereInfo*);
 int sqlite3WhereBreakLabel(WhereInfo*);
@@ -4526,7 +4531,6 @@ void sqlite3FixInit(DbFixer*, Parse*, int, const char*, const Token*);
 int sqlite3FixSrcList(DbFixer*, SrcList*);
 int sqlite3FixSelect(DbFixer*, Select*);
 int sqlite3FixExpr(DbFixer*, Expr*);
-int sqlite3FixExprList(DbFixer*, ExprList*);
 int sqlite3FixTriggerStep(DbFixer*, TriggerStep*);
 int sqlite3RealSameAsInt(double,sqlite3_int64);
 void sqlite3Int64ToText(i64,char*);
diff --git a/src/vdbe.c b/src/vdbe.c
index 8e107a8a5..3a00515e5 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -4909,8 +4909,10 @@ case OP_NewRowid: {           /* out2 */
   VdbeCursor *pC;        /* Cursor of table to get the new rowid */
   int res;               /* Result of an sqlite3BtreeLast() */
   int cnt;               /* Counter to limit the number of searches */
+#ifndef SQLITE_OMIT_AUTOINCREMENT
   Mem *pMem;             /* Register holding largest rowid for AUTOINCREMENT */
   VdbeFrame *pFrame;     /* Root frame of VDBE */
+#endif
 
   v = 0;
   res = 0;
@@ -5910,7 +5912,7 @@ case OP_IdxDelete: {
     rc = sqlite3BtreeDelete(pCrsr, BTREE_AUXDELETE);
     if( rc ) goto abort_due_to_error;
   }else if( pOp->p5 ){
-    rc = SQLITE_CORRUPT_INDEX;
+    rc = sqlite3ReportError(SQLITE_CORRUPT_INDEX, __LINE__, "index corruption");
     goto abort_due_to_error;
   }
   assert( pC->deferredMoveto==0 );
diff --git a/src/where.c b/src/where.c
index 0f219d439..8128c2ed0 100644
--- a/src/where.c
+++ b/src/where.c
@@ -100,6 +100,32 @@ int sqlite3WhereOrderByLimitOptLabel(WhereInfo *pWInfo){
 }
 
 /*
+** While generating code for the min/max optimization, after handling
+** the aggregate-step call to min() or max(), check to see if any
+** additional looping is required.  If the output order is such that
+** we are certain that the correct answer has already been found, then
+** code an OP_Goto to by pass subsequent processing.
+**
+** Any extra OP_Goto that is coded here is an optimization.  The
+** correct answer should be obtained regardless.  This OP_Goto just
+** makes the answer appear faster.
+*/
+void sqlite3WhereMinMaxOptEarlyOut(Vdbe *v, WhereInfo *pWInfo){
+  WhereLevel *pInner;
+  int i;
+  if( !pWInfo->bOrderedInnerLoop ) return;
+  if( pWInfo->nOBSat==0 ) return;
+  for(i=pWInfo->nLevel-1; i>=0; i--){
+    pInner = &pWInfo->a[i];
+    if( (pInner->pWLoop->wsFlags & WHERE_COLUMN_IN)!=0 ){
+      sqlite3VdbeGoto(v, pInner->addrNxt);
+      return;
+    }
+  }
+  sqlite3VdbeGoto(v, pWInfo->iBreak);
+}
+
+/*
 ** Return the VDBE address or label to jump to in order to continue
 ** immediately with the next row of a WHERE clause.
 */
diff --git a/src/whereInt.h b/src/whereInt.h
index f8509996f..89a463dc3 100644
--- a/src/whereInt.h
+++ b/src/whereInt.h
@@ -270,11 +270,7 @@ struct WhereTerm {
 #define TERM_ORINFO     0x0010 /* Need to free the WhereTerm.u.pOrInfo object */
 #define TERM_ANDINFO    0x0020 /* Need to free the WhereTerm.u.pAndInfo obj */
 #define TERM_OR_OK      0x0040 /* Used during OR-clause processing */
-#ifdef SQLITE_ENABLE_STAT4
-#  define TERM_VNULL    0x0080 /* Manufactured x>NULL or x<=NULL term */
-#else
-#  define TERM_VNULL    0x0000 /* Disabled if not using stat4 */
-#endif
+#define TERM_VNULL      0x0080 /* Manufactured x>NULL or x<=NULL term */
 #define TERM_LIKEOPT    0x0100 /* Virtual terms from the LIKE optimization */
 #define TERM_LIKECOND   0x0200 /* Conditionally this LIKE operator term */
 #define TERM_LIKE       0x0400 /* The original LIKE operator */
diff --git a/src/wherecode.c b/src/wherecode.c
index 4afe0ac9c..17dc36152 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -1743,6 +1743,12 @@ Bitmask sqlite3WhereCodeOneLoopStart(
       SWAP(u8, nBtm, nTop);
     }
 
+    if( iLevel>0 && (pLoop->wsFlags & WHERE_IN_SEEKSCAN)!=0 ){
+      /* In case OP_SeekScan is used, ensure that the index cursor does not
+      ** point to a valid row for the first iteration of this loop. */
+      sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur);
+    }
+
     /* Generate code to evaluate all constraint terms using == or IN
     ** and store the values of those terms in an array of registers
     ** starting at regBase.
diff --git a/src/whereexpr.c b/src/whereexpr.c
index a77eb3610..f69210975 100644
--- a/src/whereexpr.c
+++ b/src/whereexpr.c
@@ -1008,6 +1008,271 @@ static int exprMightBeIndexed(
 }
 
 /*
+** Expression callback for exprUsesSrclist().
+*/
+static int exprUsesSrclistCb(Walker *p, Expr *pExpr){
+  if( pExpr->op==TK_COLUMN ){
+    SrcList *pSrc = p->u.pSrcList;
+    int iCsr = pExpr->iTable;
+    int ii;
+    for(ii=0; ii<pSrc->nSrc; ii++){
+      if( pSrc->a[ii].iCursor==iCsr ){
+        return p->eCode ? WRC_Abort : WRC_Continue;
+      }
+    }
+    return p->eCode ? WRC_Continue : WRC_Abort;
+  }
+  return WRC_Continue;
+}
+
+/*
+** Select callback for exprUsesSrclist().
+*/
+static int exprUsesSrclistSelectCb(Walker *p, Select *pSelect){
+  return WRC_Abort;
+}
+
+/*
+** This function always returns true if expression pExpr contains
+** a sub-select.
+**
+** If there is no sub-select in pExpr, then return true if pExpr
+** contains a TK_COLUMN node for a table that is (bUses==1)
+** or is not (bUses==0) in pSrc.
+**
+** Said another way:
+**
+**   bUses      Return     Meaning
+**   --------   ------     ------------------------------------------------
+**
+**   bUses==1   true       pExpr contains either a sub-select or a
+**                         TK_COLUMN referencing pSrc.
+**
+**   bUses==1   false      pExpr contains no sub-selects and all TK_COLUMN
+**                         nodes reference tables not found in pSrc
+**
+**   bUses==0   true       pExpr contains either a sub-select or a TK_COLUMN
+**                         that references a table not in pSrc.
+**
+**   bUses==0   false      pExpr contains no sub-selects and all TK_COLUMN
+**                         nodes reference pSrc
+*/
+static int exprUsesSrclist(SrcList *pSrc, Expr *pExpr, int bUses){
+  Walker sWalker;
+  memset(&sWalker, 0, sizeof(Walker));
+  sWalker.eCode = bUses;
+  sWalker.u.pSrcList = pSrc;
+  sWalker.xExprCallback = exprUsesSrclistCb;
+  sWalker.xSelectCallback = exprUsesSrclistSelectCb;
+  return (sqlite3WalkExpr(&sWalker, pExpr)==WRC_Abort);
+}
+
+/*
+** Context object used by exprExistsToInIter() as it iterates through an
+** expression tree.
+*/
+struct ExistsToInCtx {
+  SrcList *pSrc;    /* The tables in an EXISTS(SELECT ... FROM <here> ...) */
+  Expr *pInLhs;     /* OUT:  Use this as the LHS of the IN operator */
+  Expr *pEq;        /* OUT:  The == term that include pInLhs */
+  Expr **ppAnd;     /* OUT:  The AND operator that includes pEq as a child */
+  Expr **ppParent;  /* The AND operator currently being examined */
+};
+
+/*
+** Iterate through all AND connected nodes in the expression tree
+** headed by (*ppExpr), populating the structure passed as the first
+** argument with the values required by exprAnalyzeExistsFindEq().
+**
+** This function returns non-zero if the expression tree does not meet
+** the two conditions described by the header comment for
+** exprAnalyzeExistsFindEq(), or zero if it does.
+*/
+static int exprExistsToInIter(struct ExistsToInCtx *p, Expr **ppExpr){
+  Expr *pExpr = *ppExpr;
+  switch( pExpr->op ){
+    case TK_AND:
+      p->ppParent = ppExpr;
+      if( exprExistsToInIter(p, &pExpr->pLeft) ) return 1;
+      p->ppParent = ppExpr;
+      if( exprExistsToInIter(p, &pExpr->pRight) ) return 1;
+      break;
+    case TK_EQ: {
+      int bLeft = exprUsesSrclist(p->pSrc, pExpr->pLeft, 0);
+      int bRight = exprUsesSrclist(p->pSrc, pExpr->pRight, 0);
+      if( bLeft || bRight ){
+        if( (bLeft && bRight) || p->pInLhs ) return 1;
+        p->pInLhs = bLeft ? pExpr->pLeft : pExpr->pRight;
+        if( exprUsesSrclist(p->pSrc, p->pInLhs, 1) ) return 1;
+        p->pEq = pExpr;
+        p->ppAnd = p->ppParent;
+      }
+      break;
+    }
+    default:
+      if( exprUsesSrclist(p->pSrc, pExpr, 0) ){
+        return 1;
+      }
+      break;
+  }
+
+  return 0;
+}
+
+/*
+** This function is used by exprAnalyzeExists() when creating virtual IN(...)
+** terms equivalent to user-supplied EXIST(...) clauses. It splits the WHERE
+** clause of the Select object passed as the first argument into one or more
+** expressions joined by AND operators, and then tests if the following are
+** true:
+**
+**   1. Exactly one of the AND separated terms refers to the outer
+**      query, and it is an == (TK_EQ) expression.
+**
+**   2. Only one side of the == expression refers to the outer query, and 
+**      it does not refer to any columns from the inner query.
+**
+** If both these conditions are true, then a pointer to the side of the ==
+** expression that refers to the outer query is returned. The caller will
+** use this expression as the LHS of the IN(...) virtual term. Or, if one
+** or both of the above conditions are not true, NULL is returned.
+**
+** If non-NULL is returned and ppEq is non-NULL, *ppEq is set to point
+** to the == expression node before returning. If pppAnd is non-NULL and
+** the == node is not the root of the WHERE clause, then *pppAnd is set
+** to point to the pointer to the AND node that is the parent of the ==
+** node within the WHERE expression tree.
+*/
+static Expr *exprAnalyzeExistsFindEq(
+  Select *pSel,                   /* The SELECT of the EXISTS */
+  Expr **ppEq,                    /* OUT: == node from WHERE clause */
+  Expr ***pppAnd                  /* OUT: Pointer to parent of ==, if any */
+){
+  struct ExistsToInCtx ctx;
+  memset(&ctx, 0, sizeof(ctx));
+  ctx.pSrc = pSel->pSrc;
+  if( exprExistsToInIter(&ctx, &pSel->pWhere) ){
+    return 0;
+  }
+  if( ppEq ) *ppEq = ctx.pEq;
+  if( pppAnd ) *pppAnd = ctx.ppAnd;
+  return ctx.pInLhs;
+}
+
+/*
+** Term idxTerm of the WHERE clause passed as the second argument is an
+** EXISTS expression with a correlated SELECT statement on the RHS.
+** This function analyzes the SELECT statement, and if possible adds an
+** equivalent "? IN(SELECT...)" virtual term to the WHERE clause. 
+**
+** For an EXISTS term such as the following:
+**
+**     EXISTS (SELECT ... FROM <srclist> WHERE <e1> = <e2> AND <e3>)
+**
+** The virtual IN() term added is:
+**
+**     <e1> IN (SELECT <e2> FROM <srclist> WHERE <e3>)
+**
+** The virtual term is only added if the following conditions are met:
+**
+**     1. The sub-select must not be an aggregate or use window functions,
+**
+**     2. The sub-select must not be a compound SELECT,
+**
+**     3. Expression <e1> must refer to at least one column from the outer
+**        query, and must not refer to any column from the inner query 
+**        (i.e. from <srclist>).
+**
+**     4. <e2> and <e3> must not refer to any values from the outer query.
+**        In other words, once <e1> has been removed, the inner query
+**        must not be correlated.
+**
+*/
+static void exprAnalyzeExists(
+  SrcList *pSrc,            /* the FROM clause */
+  WhereClause *pWC,         /* the WHERE clause */
+  int idxTerm               /* Index of the term to be analyzed */
+){
+  Parse *pParse = pWC->pWInfo->pParse;
+  WhereTerm *pTerm = &pWC->a[idxTerm];
+  Expr *pExpr = pTerm->pExpr;
+  Select *pSel = pExpr->x.pSelect;
+  Expr *pDup = 0;
+  Expr *pEq = 0;
+  Expr *pRet = 0;
+  Expr *pInLhs = 0;
+  Expr **ppAnd = 0;
+  int idxNew;
+  sqlite3 *db = pParse->db;
+
+  assert( pExpr->op==TK_EXISTS );
+  assert( (pExpr->flags & EP_VarSelect) && (pExpr->flags & EP_xIsSelect) );
+
+  if( (pSel->selFlags & SF_Aggregate) || pSel->pWin ) return;
+  if( pSel->pPrior ) return;
+  if( pSel->pWhere==0 ) return;
+  if( 0==exprAnalyzeExistsFindEq(pSel, 0, 0) ) return;
+
+  pDup = sqlite3ExprDup(db, pExpr, 0);
+  if( db->mallocFailed ){
+    sqlite3ExprDelete(db, pDup);
+    return;
+  }
+  pSel = pDup->x.pSelect;
+  sqlite3ExprListDelete(db, pSel->pEList);
+  pSel->pEList = 0;
+
+  pInLhs = exprAnalyzeExistsFindEq(pSel, &pEq, &ppAnd);
+  assert( pInLhs && pEq );
+  assert( pEq==pSel->pWhere || ppAnd );
+  if( pInLhs==pEq->pLeft ){
+    pRet = pEq->pRight;
+  }else{
+    CollSeq *p = sqlite3ExprCompareCollSeq(pParse, pEq);
+    pInLhs = sqlite3ExprAddCollateString(pParse, pInLhs, p?p->zName:"BINARY");
+    pRet = pEq->pLeft;
+  }
+
+  assert( pDup->pLeft==0 );
+  pDup->op = TK_IN;
+  pDup->pLeft = pInLhs;
+  pDup->flags &= ~EP_VarSelect;
+  if( pRet->op==TK_VECTOR ){
+    pSel->pEList = pRet->x.pList;
+    pRet->x.pList = 0;
+    sqlite3ExprDelete(db, pRet);
+  }else{
+    pSel->pEList = sqlite3ExprListAppend(pParse, 0, pRet);
+  }
+  pEq->pLeft = 0;
+  pEq->pRight = 0;
+  if( ppAnd ){
+    Expr *pAnd = *ppAnd;
+    Expr *pOther = (pAnd->pLeft==pEq) ? pAnd->pRight : pAnd->pLeft;
+    pAnd->pLeft = pAnd->pRight = 0;
+    sqlite3ExprDelete(db, pAnd);
+    *ppAnd = pOther;
+  }else{
+    assert( pSel->pWhere==pEq );
+    pSel->pWhere = 0;
+  }
+  sqlite3ExprDelete(db, pEq);
+
+#ifdef WHERETRACE_ENABLED  /* 0x20 */
+  if( sqlite3WhereTrace & 0x20 ){
+    sqlite3DebugPrintf("Convert EXISTS:\n");
+    sqlite3TreeViewExpr(0, pExpr, 0);
+    sqlite3DebugPrintf("into IN:\n");
+    sqlite3TreeViewExpr(0, pDup, 0);
+  }
+#endif
+  idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
+  exprAnalyze(pSrc, pWC, idxNew);
+  markTermAsChild(pWC, idxNew, idxTerm);
+  pWC->a[idxTerm].wtFlags |= TERM_COPIED;
+}
+
+/*
 ** The input to this routine is an WhereTerm structure with only the
 ** "pExpr" field filled in.  The job of this routine is to analyze the
 ** subexpression and populate all the other fields of the WhereTerm
@@ -1192,6 +1457,52 @@ static void exprAnalyze(
   }
 #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
 
+  else if( pExpr->op==TK_EXISTS ){
+    /* Perhaps treat an EXISTS operator as an IN operator */
+    if( (pExpr->flags & EP_VarSelect)!=0
+     && OptimizationEnabled(db, SQLITE_ExistsToIN)
+    ){
+      exprAnalyzeExists(pSrc, pWC, idxTerm);
+    }
+  }
+
+  /* The form "x IS NOT NULL" can sometimes be evaluated more efficiently
+  ** as "x>NULL" if x is not an INTEGER PRIMARY KEY.  So construct a
+  ** virtual term of that form.
+  **
+  ** The virtual term must be tagged with TERM_VNULL.
+  */
+  else if( pExpr->op==TK_NOTNULL ){
+    if( pExpr->pLeft->op==TK_COLUMN
+     && pExpr->pLeft->iColumn>=0
+     && !ExprHasProperty(pExpr, EP_FromJoin)
+    ){
+      Expr *pNewExpr;
+      Expr *pLeft = pExpr->pLeft;
+      int idxNew;
+      WhereTerm *pNewTerm;
+  
+      pNewExpr = sqlite3PExpr(pParse, TK_GT,
+                              sqlite3ExprDup(db, pLeft, 0),
+                              sqlite3ExprAlloc(db, TK_NULL, 0, 0));
+  
+      idxNew = whereClauseInsert(pWC, pNewExpr,
+                                TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL);
+      if( idxNew ){
+        pNewTerm = &pWC->a[idxNew];
+        pNewTerm->prereqRight = 0;
+        pNewTerm->leftCursor = pLeft->iTable;
+        pNewTerm->u.x.leftColumn = pLeft->iColumn;
+        pNewTerm->eOperator = WO_GT;
+        markTermAsChild(pWC, idxNew, idxTerm);
+        pTerm = &pWC->a[idxTerm];
+        pTerm->wtFlags |= TERM_COPIED;
+        pNewTerm->prereqAll = pTerm->prereqAll;
+      }
+    }
+  }
+
+
 #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
   /* Add constraints to reduce the search space on a LIKE or GLOB
   ** operator.
@@ -1206,7 +1517,8 @@ static void exprAnalyze(
   ** bound is made all lowercase so that the bounds also work when comparing
   ** BLOBs.
   */
-  if( pWC->op==TK_AND 
+  else if( pExpr->op==TK_FUNCTION
+   && pWC->op==TK_AND
    && isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase)
   ){
     Expr *pLeft;       /* LHS of LIKE/GLOB operator */
@@ -1276,52 +1588,6 @@ static void exprAnalyze(
   }
 #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
 
-#ifndef SQLITE_OMIT_VIRTUALTABLE
-  /* Add a WO_AUX auxiliary term to the constraint set if the
-  ** current expression is of the form "column OP expr" where OP
-  ** is an operator that gets passed into virtual tables but which is
-  ** not normally optimized for ordinary tables.  In other words, OP
-  ** is one of MATCH, LIKE, GLOB, REGEXP, !=, IS, IS NOT, or NOT NULL.
-  ** This information is used by the xBestIndex methods of
-  ** virtual tables.  The native query optimizer does not attempt
-  ** to do anything with MATCH functions.
-  */
-  if( pWC->op==TK_AND ){
-    Expr *pRight = 0, *pLeft = 0;
-    int res = isAuxiliaryVtabOperator(db, pExpr, &eOp2, &pLeft, &pRight);
-    while( res-- > 0 ){
-      int idxNew;
-      WhereTerm *pNewTerm;
-      Bitmask prereqColumn, prereqExpr;
-
-      prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
-      prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
-      if( (prereqExpr & prereqColumn)==0 ){
-        Expr *pNewExpr;
-        pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
-            0, sqlite3ExprDup(db, pRight, 0));
-        if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
-          ExprSetProperty(pNewExpr, EP_FromJoin);
-          pNewExpr->iRightJoinTable = pExpr->iRightJoinTable;
-        }
-        idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
-        testcase( idxNew==0 );
-        pNewTerm = &pWC->a[idxNew];
-        pNewTerm->prereqRight = prereqExpr;
-        pNewTerm->leftCursor = pLeft->iTable;
-        pNewTerm->u.x.leftColumn = pLeft->iColumn;
-        pNewTerm->eOperator = WO_AUX;
-        pNewTerm->eMatchOp = eOp2;
-        markTermAsChild(pWC, idxNew, idxTerm);
-        pTerm = &pWC->a[idxTerm];
-        pTerm->wtFlags |= TERM_COPIED;
-        pNewTerm->prereqAll = pTerm->prereqAll;
-      }
-      SWAP(Expr*, pLeft, pRight);
-    }
-  }
-#endif /* SQLITE_OMIT_VIRTUALTABLE */
-
   /* If there is a vector == or IS term - e.g. "(a, b) == (?, ?)" - create
   ** new terms for each component comparison - "a = ?" and "b = ?".  The
   ** new terms completely replace the original vector comparison, which is
@@ -1329,12 +1595,12 @@ static void exprAnalyze(
   **
   ** This is only required if at least one side of the comparison operation
   ** is not a sub-select.  */
-  if( pWC->op==TK_AND 
-  && (pExpr->op==TK_EQ || pExpr->op==TK_IS)
-  && (nLeft = sqlite3ExprVectorSize(pExpr->pLeft))>1
-  && sqlite3ExprVectorSize(pExpr->pRight)==nLeft
-  && ( (pExpr->pLeft->flags & EP_xIsSelect)==0 
-    || (pExpr->pRight->flags & EP_xIsSelect)==0)
+  if( (pExpr->op==TK_EQ || pExpr->op==TK_IS)
+   && (nLeft = sqlite3ExprVectorSize(pExpr->pLeft))>1
+   && sqlite3ExprVectorSize(pExpr->pRight)==nLeft
+   && ( (pExpr->pLeft->flags & EP_xIsSelect)==0 
+     || (pExpr->pRight->flags & EP_xIsSelect)==0)
+   && pWC->op==TK_AND
   ){
     int i;
     for(i=0; i<nLeft; i++){
@@ -1362,12 +1628,14 @@ static void exprAnalyze(
   ** This only works if the RHS is a simple SELECT (not a compound) that does
   ** not use window functions.
   */
-  if( pWC->op==TK_AND && pExpr->op==TK_IN && pTerm->u.x.iField==0
+  else if( pExpr->op==TK_IN
+   && pTerm->u.x.iField==0
    && pExpr->pLeft->op==TK_VECTOR
    && pExpr->x.pSelect->pPrior==0
 #ifndef SQLITE_OMIT_WINDOWFUNC
    && pExpr->x.pSelect->pWin==0
 #endif
+   && pWC->op==TK_AND
   ){
     int i;
     for(i=0; i<sqlite3ExprVectorSize(pExpr->pLeft); i++){
@@ -1379,44 +1647,51 @@ static void exprAnalyze(
     }
   }
 
-#ifdef SQLITE_ENABLE_STAT4
-  /* When sqlite_stat4 histogram data is available an operator of the
-  ** form "x IS NOT NULL" can sometimes be evaluated more efficiently
-  ** as "x>NULL" if x is not an INTEGER PRIMARY KEY.  So construct a
-  ** virtual term of that form.
-  **
-  ** Note that the virtual term must be tagged with TERM_VNULL.
+#ifndef SQLITE_OMIT_VIRTUALTABLE
+  /* Add a WO_AUX auxiliary term to the constraint set if the
+  ** current expression is of the form "column OP expr" where OP
+  ** is an operator that gets passed into virtual tables but which is
+  ** not normally optimized for ordinary tables.  In other words, OP
+  ** is one of MATCH, LIKE, GLOB, REGEXP, !=, IS, IS NOT, or NOT NULL.
+  ** This information is used by the xBestIndex methods of
+  ** virtual tables.  The native query optimizer does not attempt
+  ** to do anything with MATCH functions.
   */
-  if( pExpr->op==TK_NOTNULL
-   && pExpr->pLeft->op==TK_COLUMN
-   && pExpr->pLeft->iColumn>=0
-   && !ExprHasProperty(pExpr, EP_FromJoin)
-   && OptimizationEnabled(db, SQLITE_Stat4)
-  ){
-    Expr *pNewExpr;
-    Expr *pLeft = pExpr->pLeft;
-    int idxNew;
-    WhereTerm *pNewTerm;
-
-    pNewExpr = sqlite3PExpr(pParse, TK_GT,
-                            sqlite3ExprDup(db, pLeft, 0),
-                            sqlite3ExprAlloc(db, TK_NULL, 0, 0));
-
-    idxNew = whereClauseInsert(pWC, pNewExpr,
-                              TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL);
-    if( idxNew ){
-      pNewTerm = &pWC->a[idxNew];
-      pNewTerm->prereqRight = 0;
-      pNewTerm->leftCursor = pLeft->iTable;
-      pNewTerm->u.x.leftColumn = pLeft->iColumn;
-      pNewTerm->eOperator = WO_GT;
-      markTermAsChild(pWC, idxNew, idxTerm);
-      pTerm = &pWC->a[idxTerm];
-      pTerm->wtFlags |= TERM_COPIED;
-      pNewTerm->prereqAll = pTerm->prereqAll;
+  else if( pWC->op==TK_AND ){
+    Expr *pRight = 0, *pLeft = 0;
+    int res = isAuxiliaryVtabOperator(db, pExpr, &eOp2, &pLeft, &pRight);
+    while( res-- > 0 ){
+      int idxNew;
+      WhereTerm *pNewTerm;
+      Bitmask prereqColumn, prereqExpr;
+
+      prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
+      prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
+      if( (prereqExpr & prereqColumn)==0 ){
+        Expr *pNewExpr;
+        pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
+            0, sqlite3ExprDup(db, pRight, 0));
+        if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
+          ExprSetProperty(pNewExpr, EP_FromJoin);
+          pNewExpr->iRightJoinTable = pExpr->iRightJoinTable;
+        }
+        idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
+        testcase( idxNew==0 );
+        pNewTerm = &pWC->a[idxNew];
+        pNewTerm->prereqRight = prereqExpr;
+        pNewTerm->leftCursor = pLeft->iTable;
+        pNewTerm->u.x.leftColumn = pLeft->iColumn;
+        pNewTerm->eOperator = WO_AUX;
+        pNewTerm->eMatchOp = eOp2;
+        markTermAsChild(pWC, idxNew, idxTerm);
+        pTerm = &pWC->a[idxTerm];
+        pTerm->wtFlags |= TERM_COPIED;
+        pNewTerm->prereqAll = pTerm->prereqAll;
+      }
+      SWAP(Expr*, pLeft, pRight);
     }
   }
-#endif /* SQLITE_ENABLE_STAT4 */
+#endif /* SQLITE_OMIT_VIRTUALTABLE */
 
   /* Prevent ON clause terms of a LEFT JOIN from being used to drive
   ** an index for tables to the left of the join.