Merge the latest changes from trunk: chiefly the outer/inner loop query

optimizer scoring enhancement and the INSTR() function.

FossilOrigin-Name: 2993ca20207f8dac02f58d01e31d68c84328356a

9 files changed, 90 insertions, 12 deletions

diff --git a/src/expr.c b/src/expr.c
index 3eee3c6d6..ae828489a 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -4030,8 +4030,10 @@ static int analyzeAggregate(Walker *pWalker, Expr *pExpr){
         ExprSetIrreducible(pExpr);
         pExpr->iAgg = (i16)i;
         pExpr->pAggInfo = pAggInfo;
+        return WRC_Prune;
+      }else{
+        return WRC_Continue;
       }
-      return WRC_Prune;
     }
   }
   return WRC_Continue;
@@ -4043,9 +4045,10 @@ static int analyzeAggregatesInSelect(Walker *pWalker, Select *pSelect){
 }
 
 /*
-** Analyze the given expression looking for aggregate functions and
-** for variables that need to be added to the pParse->aAgg[] array.
-** Make additional entries to the pParse->aAgg[] array as necessary.
+** Analyze the pExpr expression looking for aggregate functions and
+** for variables that need to be added to AggInfo object that pNC->pAggInfo
+** points to.  Additional entries are made on the AggInfo object as
+** necessary.
 **
 ** This routine should only be called after the expression has been
 ** analyzed by sqlite3ResolveExprNames().
diff --git a/src/func.c b/src/func.c
index cc74a4ddd..c7af7d679 100644
--- a/src/func.c
+++ b/src/func.c
@@ -169,6 +169,55 @@ static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 }
 
 /*
+** Implementation of the instr() function.
+**
+** instr(haystack,needle) finds the first occurrence of needle
+** in haystack and returns the number of previous characters plus 1,
+** or 0 if needle does not occur within haystack.
+**
+** If both haystack and needle are BLOBs, then the result is one more than
+** the number of bytes in haystack prior to the first occurrence of needle,
+** or 0 if needle never occurs in haystack.
+*/
+static void instrFunc(
+  sqlite3_context *context,
+  int argc,
+  sqlite3_value **argv
+){
+  const unsigned char *zHaystack;
+  const unsigned char *zNeedle;
+  int nHaystack;
+  int nNeedle;
+  int typeHaystack, typeNeedle;
+  int N = 1;
+  int isText;
+
+  typeHaystack = sqlite3_value_type(argv[0]);
+  typeNeedle = sqlite3_value_type(argv[1]);
+  if( typeHaystack==SQLITE_NULL || typeNeedle==SQLITE_NULL ) return;
+  nHaystack = sqlite3_value_bytes(argv[0]);
+  nNeedle = sqlite3_value_bytes(argv[1]);
+  if( typeHaystack==SQLITE_BLOB && typeNeedle==SQLITE_BLOB ){
+    zHaystack = sqlite3_value_blob(argv[0]);
+    zNeedle = sqlite3_value_blob(argv[1]);
+    isText = 0;
+  }else{
+    zHaystack = sqlite3_value_text(argv[0]);
+    zNeedle = sqlite3_value_text(argv[1]);
+    isText = 1;
+  }
+  while( nNeedle<=nHaystack && memcmp(zHaystack, zNeedle, nNeedle)!=0 ){
+    N++;
+    do{
+      nHaystack--;
+      zHaystack++;
+    }while( isText && (zHaystack[0]&0xc0)==0x80 );
+  }
+  if( nNeedle>nHaystack ) N = 0;
+  sqlite3_result_int(context, N);
+}
+
+/*
 ** Implementation of the substr() function.
 **
 ** substr(x,p1,p2)  returns p2 characters of x[] beginning with p1.
@@ -1536,6 +1585,7 @@ void sqlite3RegisterGlobalFunctions(void){
     AGGREGATE(max,               1, 1, 1, minmaxStep,      minMaxFinalize ),
     FUNCTION2(typeof,            1, 0, 0, typeofFunc,  SQLITE_FUNC_TYPEOF),
     FUNCTION2(length,            1, 0, 0, lengthFunc,  SQLITE_FUNC_LENGTH),
+    FUNCTION(instr,              2, 0, 0, instrFunc        ),
     FUNCTION(substr,             2, 0, 0, substrFunc       ),
     FUNCTION(substr,             3, 0, 0, substrFunc       ),
     FUNCTION(abs,                1, 0, 0, absFunc          ),
diff --git a/src/os_unix.c b/src/os_unix.c
index 9b6c9401a..ca6213943 100644
--- a/src/os_unix.c
+++ b/src/os_unix.c
@@ -5374,8 +5374,13 @@ static int unixDelete(
   int rc = SQLITE_OK;
   UNUSED_PARAMETER(NotUsed);
   SimulateIOError(return SQLITE_IOERR_DELETE);
-  if( osUnlink(zPath)==(-1) && errno!=ENOENT ){
-    return unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
+  if( osUnlink(zPath)==(-1) ){
+    if( errno==ENOENT ){
+      rc = SQLITE_IOERR_DELETE_NOENT;
+    }else{
+      rc = unixLogError(SQLITE_IOERR_DELETE, "unlink", zPath);
+    }
+    return rc;
   }
 #ifndef SQLITE_DISABLE_DIRSYNC
   if( (dirSync & 1)!=0 ){
diff --git a/src/pager.c b/src/pager.c
index a96dc45b8..7c039e7a7 100644
--- a/src/pager.c
+++ b/src/pager.c
@@ -3159,6 +3159,7 @@ static int pagerOpenWalIfPresent(Pager *pPager){
     if( rc ) return rc;
     if( nPage==0 ){
       rc = sqlite3OsDelete(pPager->pVfs, pPager->zWal, 0);
+      if( rc==SQLITE_IOERR_DELETE_NOENT ) rc = SQLITE_OK;
       isWal = 0;
     }else{
       rc = sqlite3OsAccess(
diff --git a/src/sqlite.h.in b/src/sqlite.h.in
index 1d5406f0f..0522973f8 100644
--- a/src/sqlite.h.in
+++ b/src/sqlite.h.in
@@ -469,6 +469,7 @@ int sqlite3_exec(
 #define SQLITE_IOERR_SHMLOCK           (SQLITE_IOERR | (20<<8))
 #define SQLITE_IOERR_SHMMAP            (SQLITE_IOERR | (21<<8))
 #define SQLITE_IOERR_SEEK              (SQLITE_IOERR | (22<<8))
+#define SQLITE_IOERR_DELETE_NOENT      (SQLITE_IOERR | (23<<8))
 #define SQLITE_LOCKED_SHAREDCACHE      (SQLITE_LOCKED |  (1<<8))
 #define SQLITE_BUSY_RECOVERY           (SQLITE_BUSY   |  (1<<8))
 #define SQLITE_CANTOPEN_NOTEMPDIR      (SQLITE_CANTOPEN | (1<<8))
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index d7857e566..658b67ba4 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1975,6 +1975,7 @@ struct WhereLevel {
     } in;                 /* Used when plan.wsFlags&WHERE_IN_ABLE */
     Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
   } u;
+  double rOptCost;      /* "Optimal" cost for this level */
 
   /* The following field is really not part of the current level.  But
   ** we need a place to cache virtual table index information for each
diff --git a/src/status.c b/src/status.c
index 9b0df8001..28349e6d3 100644
--- a/src/status.c
+++ b/src/status.c
@@ -209,6 +209,7 @@ int sqlite3_db_status(
       db->pnBytesFreed = &nByte;
       for(pVdbe=db->pVdbe; pVdbe; pVdbe=pVdbe->pNext){
         sqlite3VdbeClearObject(db, pVdbe);
+        sqlite3DbFree(db, pVdbe);
       }
       db->pnBytesFreed = 0;
 
diff --git a/src/test_quota.c b/src/test_quota.c
index e1ec12d37..166a512f1 100644
--- a/src/test_quota.c
+++ b/src/test_quota.c
@@ -1179,7 +1179,13 @@ int sqlite3_quota_ftruncate(quota_FILE *p, sqlite3_int64 szNew){
   rc = ftruncate(fileno(p->f), szNew);
 #endif
 #if SQLITE_OS_WIN
-  rc = _chsize_s(_fileno(p->f), szNew);
+#  if defined(__MINGW32__) && defined(SQLITE_TEST)
+     /* _chsize_s() is missing from MingW (as of 2012-11-06).  Use
+     ** _chsize() as a work-around for testing purposes. */
+     rc = _chsize(_fileno(p->f), (long)szNew);
+#  else
+     rc = _chsize_s(_fileno(p->f), szNew);
+#  endif
 #endif
   if( pFile && rc==0 ){
     quotaGroup *pGroup = pFile->pGroup;
diff --git a/src/where.c b/src/where.c
index 38f6f1436..20bd90979 100644
--- a/src/where.c
+++ b/src/where.c
@@ -5102,6 +5102,19 @@ WhereInfo *sqlite3WhereBegin(
         if( isOptimal && (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ){
           notIndexed |= m;
         }
+        if( isOptimal ){
+          pWInfo->a[j].rOptCost = sWBI.cost.rCost;
+        }else if( iFrom<nTabList-1 ){
+          /* If two or more tables have nearly the same outer loop cost,
+          ** very different inner loop (optimal) cost, we want to choose
+          ** for the outer loop that table which benefits the least from
+          ** being in the inner loop.  The following code scales the 
+          ** outer loop cost estimate to accomplish that. */
+          WHERETRACE(("   scaling cost from %.1f to %.1f\n",
+                      sWBI.cost.rCost,
+                      sWBI.cost.rCost/pWInfo->a[j].rOptCost));
+          sWBI.cost.rCost /= pWInfo->a[j].rOptCost;
+        }
 
         /* Conditions under which this table becomes the best so far:
         **
@@ -5109,8 +5122,8 @@ WhereInfo *sqlite3WhereBegin(
         **       yet run.  (In other words, it must not depend on tables
         **       in inner loops.)
         **
-        **   (2) A full-table-scan plan cannot supercede indexed plan unless
-        **       the full-table-scan is an "optimal" plan as defined above.
+        **   (2) (This rule was removed on 2012-11-09.  The scaling of the
+        **       cost using the optimal scan cost made this rule obsolete.)
         **
         **   (3) All tables have an INDEXED BY clause or this table lacks an
         **       INDEXED BY clause or this table uses the specific
@@ -5125,9 +5138,6 @@ WhereInfo *sqlite3WhereBegin(
         **       is defined by the compareCost() function above. 
         */
         if( (sWBI.cost.used&sWBI.notValid)==0                    /* (1) */
-            && (bestJ<0 || (notIndexed&m)!=0                     /* (2) */
-                || (bestPlan.plan.wsFlags & WHERE_NOT_FULLSCAN)==0
-                || (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)
             && (nUnconstrained==0 || sWBI.pSrc->pIndex==0        /* (3) */
                 || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
             && (bestJ<0 || compareCost(&sWBI.cost, &bestPlan))   /* (4) */