/ Check-in [9bd6f3d8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the experimental sqlite3_reoptimize() API.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1:9bd6f3d8864d422fe42074688b191915b27ad8ea
User & Date: dan 2009-10-15 18:35:39
Context
2009-10-16
15:19
Remove a mutex reference leak from sqlite3_reoptimize(). check-in: 9f093706 user: dan tags: experimental
2009-10-15
19:45
On any rollback, immediately interrupt all running statements on the same database connection. This is a partial fix for ticket [f777251dc7]. Closed-Leaf check-in: c304b44c user: drh tags: experimental
18:35
Add the experimental sqlite3_reoptimize() API. check-in: 9bd6f3d8 user: dan tags: experimental
2009-10-14
11:33
Version 3.6.19 check-in: c1d499af user: drh tags: trunk, release
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

   567    567     assert( !ExprHasAnyProperty(pExpr, EP_IntValue|EP_Reduced|EP_TokenOnly) );
   568    568     z = pExpr->u.zToken;
   569    569     assert( z!=0 );
   570    570     assert( z[0]!=0 );
   571    571     if( z[1]==0 ){
   572    572       /* Wildcard of the form "?".  Assign the next variable number */
   573    573       assert( z[0]=='?' );
   574         -    pExpr->iTable = ++pParse->nVar;
          574  +    pExpr->iColumn = ++pParse->nVar;
   575    575     }else if( z[0]=='?' ){
   576    576       /* Wildcard of the form "?nnn".  Convert "nnn" to an integer and
   577    577       ** use it as the variable number */
   578    578       int i;
   579         -    pExpr->iTable = i = atoi((char*)&z[1]);
          579  +    pExpr->iColumn = i = atoi((char*)&z[1]);
   580    580       testcase( i==0 );
   581    581       testcase( i==1 );
   582    582       testcase( i==db->aLimit[SQLITE_LIMIT_VARIABLE_NUMBER]-1 );
   583    583       testcase( i==db->aLimit[SQLITE_LIMIT_VARIABLE_NUMBER] );
   584    584       if( i<1 || i>db->aLimit[SQLITE_LIMIT_VARIABLE_NUMBER] ){
   585    585         sqlite3ErrorMsg(pParse, "variable number must be between ?1 and ?%d",
   586    586             db->aLimit[SQLITE_LIMIT_VARIABLE_NUMBER]);
................................................................................
   596    596       int i;
   597    597       u32 n;
   598    598       n = sqlite3Strlen30(z);
   599    599       for(i=0; i<pParse->nVarExpr; i++){
   600    600         Expr *pE = pParse->apVarExpr[i];
   601    601         assert( pE!=0 );
   602    602         if( memcmp(pE->u.zToken, z, n)==0 && pE->u.zToken[n]==0 ){
   603         -        pExpr->iTable = pE->iTable;
          603  +        pExpr->iColumn = pE->iColumn;
   604    604           break;
   605    605         }
   606    606       }
   607    607       if( i>=pParse->nVarExpr ){
   608         -      pExpr->iTable = ++pParse->nVar;
          608  +      pExpr->iColumn = ++pParse->nVar;
   609    609         if( pParse->nVarExpr>=pParse->nVarExprAlloc-1 ){
   610    610           pParse->nVarExprAlloc += pParse->nVarExprAlloc + 10;
   611    611           pParse->apVarExpr =
   612    612               sqlite3DbReallocOrFree(
   613    613                 db,
   614    614                 pParse->apVarExpr,
   615    615                 pParse->nVarExprAlloc*sizeof(pParse->apVarExpr[0])
................................................................................
  2160   2160       case TK_VARIABLE: {
  2161   2161         VdbeOp *pOp;
  2162   2162         assert( !ExprHasProperty(pExpr, EP_IntValue) );
  2163   2163         assert( pExpr->u.zToken!=0 );
  2164   2164         assert( pExpr->u.zToken[0]!=0 );
  2165   2165         if( pExpr->u.zToken[1]==0
  2166   2166            && (pOp = sqlite3VdbeGetOp(v, -1))->opcode==OP_Variable
  2167         -         && pOp->p1+pOp->p3==pExpr->iTable
         2167  +         && pOp->p1+pOp->p3==pExpr->iColumn
  2168   2168            && pOp->p2+pOp->p3==target
  2169   2169            && pOp->p4.z==0
  2170   2170         ){
  2171   2171           /* If the previous instruction was a copy of the previous unnamed
  2172   2172           ** parameter into the previous register, then simply increment the
  2173   2173           ** repeat count on the prior instruction rather than making a new
  2174   2174           ** instruction.
  2175   2175           */
  2176   2176           pOp->p3++;
  2177   2177         }else{
  2178         -        sqlite3VdbeAddOp3(v, OP_Variable, pExpr->iTable, target, 1);
         2178  +        sqlite3VdbeAddOp3(v, OP_Variable, pExpr->iColumn, target, 1);
  2179   2179           if( pExpr->u.zToken[1]!=0 ){
  2180   2180             sqlite3VdbeChangeP4(v, -1, pExpr->u.zToken, 0);
  2181   2181           }
  2182   2182         }
  2183   2183         break;
  2184   2184       }
  2185   2185       case TK_REGISTER: {
................................................................................
  2797   2797     ** keep the ALWAYS() in case the conditions above change with future
  2798   2798     ** modifications or enhancements. */
  2799   2799     if( ALWAYS(pExpr->op!=TK_REGISTER) ){  
  2800   2800       int iMem;
  2801   2801       iMem = ++pParse->nMem;
  2802   2802       sqlite3VdbeAddOp2(v, OP_Copy, inReg, iMem);
  2803   2803       pExpr->iTable = iMem;
         2804  +    pExpr->op2 = pExpr->op;
  2804   2805       pExpr->op = TK_REGISTER;
  2805   2806     }
  2806   2807     return inReg;
  2807   2808   }
  2808   2809   
  2809   2810   /*
  2810   2811   ** Return TRUE if pExpr is an constant expression that is appropriate

Changes to src/main.c.

  1477   1477   #endif
  1478   1478   #if SQLITE_MAX_ATTACHED<0 || SQLITE_MAX_ATTACHED>30
  1479   1479   # error SQLITE_MAX_ATTACHED must be between 0 and 30
  1480   1480   #endif
  1481   1481   #if SQLITE_MAX_LIKE_PATTERN_LENGTH<1
  1482   1482   # error SQLITE_MAX_LIKE_PATTERN_LENGTH must be at least 1
  1483   1483   #endif
  1484         -#if SQLITE_MAX_VARIABLE_NUMBER<1
  1485         -# error SQLITE_MAX_VARIABLE_NUMBER must be at least 1
         1484  +#if SQLITE_MAX_VARIABLE_NUMBER<1 || SQLITE_MAX_VARIABLE_NUMBER>32767
         1485  +# error SQLITE_MAX_VARIABLE_NUMBER must be between 1 and 32767
  1486   1486   #endif
  1487   1487   #if SQLITE_MAX_COLUMN>32767
  1488   1488   # error SQLITE_MAX_COLUMN must not exceed 32767
  1489   1489   #endif
  1490   1490   #if SQLITE_MAX_TRIGGER_DEPTH<1
  1491   1491   # error SQLITE_MAX_TRIGGER_DEPTH must be at least 1
  1492   1492   #endif

Changes to src/prepare.c.

   521    521   ** Compile the UTF-8 encoded SQL statement zSql into a statement handle.
   522    522   */
   523    523   static int sqlite3Prepare(
   524    524     sqlite3 *db,              /* Database handle. */
   525    525     const char *zSql,         /* UTF-8 encoded SQL statement. */
   526    526     int nBytes,               /* Length of zSql in bytes. */
   527    527     int saveSqlFlag,          /* True to copy SQL text into the sqlite3_stmt */
          528  +  Vdbe *pReprepare,         /* VM being reprepared */
   528    529     sqlite3_stmt **ppStmt,    /* OUT: A pointer to the prepared statement */
   529    530     const char **pzTail       /* OUT: End of parsed string */
   530    531   ){
   531    532     Parse *pParse;            /* Parsing context */
   532    533     char *zErrMsg = 0;        /* Error message */
   533    534     int rc = SQLITE_OK;       /* Result code */
   534    535     int i;                    /* Loop counter */
................................................................................
   535    536   
   536    537     /* Allocate the parsing context */
   537    538     pParse = sqlite3StackAllocZero(db, sizeof(*pParse));
   538    539     if( pParse==0 ){
   539    540       rc = SQLITE_NOMEM;
   540    541       goto end_prepare;
   541    542     }
          543  +  pParse->pReprepare = pReprepare;
   542    544   
   543    545     if( sqlite3SafetyOn(db) ){
   544    546       rc = SQLITE_MISUSE;
   545    547       goto end_prepare;
   546    548     }
   547    549     assert( ppStmt && *ppStmt==0 );
   548    550     assert( !db->mallocFailed );
................................................................................
   692    694     return rc;
   693    695   }
   694    696   static int sqlite3LockAndPrepare(
   695    697     sqlite3 *db,              /* Database handle. */
   696    698     const char *zSql,         /* UTF-8 encoded SQL statement. */
   697    699     int nBytes,               /* Length of zSql in bytes. */
   698    700     int saveSqlFlag,          /* True to copy SQL text into the sqlite3_stmt */
          701  +  Vdbe *pOld,               /* VM being reprepared */
   699    702     sqlite3_stmt **ppStmt,    /* OUT: A pointer to the prepared statement */
   700    703     const char **pzTail       /* OUT: End of parsed string */
   701    704   ){
   702    705     int rc;
   703    706     assert( ppStmt!=0 );
   704    707     *ppStmt = 0;
   705    708     if( !sqlite3SafetyCheckOk(db) ){
   706    709       return SQLITE_MISUSE;
   707    710     }
   708    711     sqlite3_mutex_enter(db->mutex);
   709    712     sqlite3BtreeEnterAll(db);
   710         -  rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, ppStmt, pzTail);
          713  +  rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
   711    714     if( rc==SQLITE_SCHEMA ){
   712    715       sqlite3_finalize(*ppStmt);
   713         -    rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, ppStmt, pzTail);
          716  +    rc = sqlite3Prepare(db, zSql, nBytes, saveSqlFlag, pOld, ppStmt, pzTail);
   714    717     }
   715    718     sqlite3BtreeLeaveAll(db);
   716    719     sqlite3_mutex_leave(db->mutex);
   717    720     return rc;
   718    721   }
   719    722   
   720    723   /*
................................................................................
   732    735     sqlite3 *db;
   733    736   
   734    737     assert( sqlite3_mutex_held(sqlite3VdbeDb(p)->mutex) );
   735    738     zSql = sqlite3_sql((sqlite3_stmt *)p);
   736    739     assert( zSql!=0 );  /* Reprepare only called for prepare_v2() statements */
   737    740     db = sqlite3VdbeDb(p);
   738    741     assert( sqlite3_mutex_held(db->mutex) );
   739         -  rc = sqlite3LockAndPrepare(db, zSql, -1, 0, &pNew, 0);
          742  +  rc = sqlite3LockAndPrepare(db, zSql, -1, 0, p, &pNew, 0);
   740    743     if( rc ){
   741    744       if( rc==SQLITE_NOMEM ){
   742    745         db->mallocFailed = 1;
   743    746       }
   744    747       assert( pNew==0 );
   745    748       return (rc==SQLITE_LOCKED) ? SQLITE_LOCKED : SQLITE_SCHEMA;
   746    749     }else{
................................................................................
   766    769     sqlite3 *db,              /* Database handle. */
   767    770     const char *zSql,         /* UTF-8 encoded SQL statement. */
   768    771     int nBytes,               /* Length of zSql in bytes. */
   769    772     sqlite3_stmt **ppStmt,    /* OUT: A pointer to the prepared statement */
   770    773     const char **pzTail       /* OUT: End of parsed string */
   771    774   ){
   772    775     int rc;
   773         -  rc = sqlite3LockAndPrepare(db,zSql,nBytes,0,ppStmt,pzTail);
          776  +  rc = sqlite3LockAndPrepare(db,zSql,nBytes,0,0,ppStmt,pzTail);
   774    777     assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 );  /* VERIFY: F13021 */
   775    778     return rc;
   776    779   }
   777    780   int sqlite3_prepare_v2(
   778    781     sqlite3 *db,              /* Database handle. */
   779    782     const char *zSql,         /* UTF-8 encoded SQL statement. */
   780    783     int nBytes,               /* Length of zSql in bytes. */
   781    784     sqlite3_stmt **ppStmt,    /* OUT: A pointer to the prepared statement */
   782    785     const char **pzTail       /* OUT: End of parsed string */
   783    786   ){
   784    787     int rc;
   785         -  rc = sqlite3LockAndPrepare(db,zSql,nBytes,1,ppStmt,pzTail);
          788  +  rc = sqlite3LockAndPrepare(db,zSql,nBytes,1,0,ppStmt,pzTail);
   786    789     assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 );  /* VERIFY: F13021 */
   787    790     return rc;
   788    791   }
   789    792   
   790    793   
   791    794   #ifndef SQLITE_OMIT_UTF16
   792    795   /*
................................................................................
   812    815     *ppStmt = 0;
   813    816     if( !sqlite3SafetyCheckOk(db) ){
   814    817       return SQLITE_MISUSE;
   815    818     }
   816    819     sqlite3_mutex_enter(db->mutex);
   817    820     zSql8 = sqlite3Utf16to8(db, zSql, nBytes);
   818    821     if( zSql8 ){
   819         -    rc = sqlite3LockAndPrepare(db, zSql8, -1, saveSqlFlag, ppStmt, &zTail8);
          822  +    rc = sqlite3LockAndPrepare(db, zSql8, -1, saveSqlFlag, 0, ppStmt, &zTail8);
   820    823     }
   821    824   
   822    825     if( zTail8 && pzTail ){
   823    826       /* If sqlite3_prepare returns a tail pointer, we calculate the
   824    827       ** equivalent pointer into the UTF-16 string by counting the unicode
   825    828       ** characters between zSql8 and zTail8, and then returning a pointer
   826    829       ** the same number of characters into the UTF-16 string.

Changes to src/sqlite.h.in.

  5739   5739   ** The [sqlite3_strnicmp()] API allows applications and extensions to
  5740   5740   ** compare the contents of two buffers containing UTF-8 strings in a
  5741   5741   ** case-indendent fashion, using the same definition of case independence 
  5742   5742   ** that SQLite uses internally when comparing identifiers.
  5743   5743   */
  5744   5744   int sqlite3_strnicmp(const char *, const char *, int);
  5745   5745   
         5746  +/*
         5747  +** CAPI3REF: Optimizing for Bound Parameters
         5748  +** EXPERIMENTAL
         5749  +**
         5750  +** If possible, optimize the SQL statement passed as the only argument
         5751  +** for the values currently bound to the statements SQL variables.
         5752  +*/
         5753  +int sqlite3_reoptimize(sqlite3_stmt *pStmt);
         5754  +
  5746   5755   /*
  5747   5756   ** Undo the hack that converts floating point types to integer for
  5748   5757   ** builds on processors without floating point support.
  5749   5758   */
  5750   5759   #ifdef SQLITE_OMIT_FLOATING_POINT
  5751   5760   # undef double
  5752   5761   #endif
  5753   5762   
  5754   5763   #ifdef __cplusplus
  5755   5764   }  /* End of the 'extern "C"' block */
  5756   5765   #endif
  5757   5766   #endif

Changes to src/sqliteInt.h.

  1586   1586     ** space is allocated for the fields below this point. An attempt to
  1587   1587     ** access them will result in a segfault or malfunction.
  1588   1588     *********************************************************************/
  1589   1589   
  1590   1590     int iTable;            /* TK_COLUMN: cursor number of table holding column
  1591   1591                            ** TK_REGISTER: register number
  1592   1592                            ** TK_TRIGGER: 1 -> new, 0 -> old */
  1593         -  i16 iColumn;           /* TK_COLUMN: column index.  -1 for rowid */
         1593  +  i16 iColumn;           /* TK_COLUMN: column index.  -1 for rowid.
         1594  +                         ** TK_VARIABLE: variable number (always >= 1). */
  1594   1595     i16 iAgg;              /* Which entry in pAggInfo->aCol[] or ->aFunc[] */
  1595   1596     i16 iRightJoinTable;   /* If EP_FromJoin, the right table of the join */
  1596   1597     u8 flags2;             /* Second set of flags.  EP2_... */
  1597   1598     u8 op2;                /* If a TK_REGISTER, the original value of Expr.op */
  1598   1599     AggInfo *pAggInfo;     /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */
  1599   1600     Table *pTab;           /* Table for TK_COLUMN expressions. */
  1600   1601   #if SQLITE_MAX_EXPR_DEPTH>0
................................................................................
  2128   2129     /* Above is constant between recursions.  Below is reset before and after
  2129   2130     ** each recursion */
  2130   2131   
  2131   2132     int nVar;            /* Number of '?' variables seen in the SQL so far */
  2132   2133     int nVarExpr;        /* Number of used slots in apVarExpr[] */
  2133   2134     int nVarExprAlloc;   /* Number of allocated slots in apVarExpr[] */
  2134   2135     Expr **apVarExpr;    /* Pointers to :aaa and $aaaa wildcard expressions */
         2136  +  Vdbe *pReprepare;    /* VM being reprepared (sqlite3Reprepare()) */
  2135   2137     int nAlias;          /* Number of aliased result set columns */
  2136   2138     int nAliasAlloc;     /* Number of allocated slots for aAlias[] */
  2137   2139     int *aAlias;         /* Register used to hold aliased result */
  2138   2140     u8 explain;          /* True if the EXPLAIN flag is found on the query */
  2139   2141     Token sNameToken;    /* Token with unqualified schema object name */
  2140   2142     Token sLastToken;    /* The last token parsed */
  2141   2143     const char *zTail;   /* All SQL text past the last semicolon parsed */

Changes to src/tclsqlite.c.

  1124   1124         }else{
  1125   1125           sqlite3_bind_null(pStmt, i);
  1126   1126         }
  1127   1127       }
  1128   1128     }
  1129   1129     pPreStmt->nParm = iParm;
  1130   1130     *ppPreStmt = pPreStmt;
         1131  +
         1132  +  /* Call sqlite3_reoptimize() to optimize the statement according to
         1133  +  ** the values just bound to it. If SQLITE_ENABLE_STAT2 is not defined
         1134  +  ** or the statement will not benefit from re-optimization, this 
         1135  +  ** call is a no-op.  */
         1136  +  if( SQLITE_OK!=sqlite3_reoptimize(pPreStmt->pStmt) ){
         1137  +    Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db)));
         1138  +    return TCL_ERROR;
         1139  +  }
         1140  +
  1131   1141     return TCL_OK;
  1132   1142   }
  1133   1143   
  1134   1144   
  1135   1145   /*
  1136   1146   ** Release a statement reference obtained by calling dbPrepareAndBind().
  1137   1147   ** There should be exactly one call to this function for each call to

Changes to src/test1.c.

  2044   2044       if( Tcl_GetIntFromObj(interp, objv[2], &op) ) return TCL_ERROR;
  2045   2045     }
  2046   2046     if( Tcl_GetBooleanFromObj(interp, objv[3], &resetFlag) ) return TCL_ERROR;
  2047   2047     iValue = sqlite3_stmt_status(pStmt, op, resetFlag);
  2048   2048     Tcl_SetObjResult(interp, Tcl_NewIntObj(iValue));
  2049   2049     return TCL_OK;
  2050   2050   }
         2051  +
         2052  +/*
         2053  +** Usage:  sqlite3_reoptimize  STMT
         2054  +**
         2055  +** Call sqlite3_reoptimize() on the statement handle passed as the
         2056  +** only parameter. Return a string representing the value returned by
         2057  +** sqlite3_reoptimize - "SQLITE_OK", "SQLITE_MISUSE" etc.
         2058  +*/
         2059  +static int test_reoptimize(
         2060  +  void * clientData,
         2061  +  Tcl_Interp *interp,
         2062  +  int objc,
         2063  +  Tcl_Obj *CONST objv[]
         2064  +){
         2065  +  sqlite3_stmt *pStmt;
         2066  +  int rc;
         2067  +
         2068  +  if( objc!=2 ){
         2069  +    Tcl_AppendResult(interp, "wrong # args: should be \"", 
         2070  +       Tcl_GetString(objv[0]), " STMT", 0);
         2071  +    return TCL_ERROR;
         2072  +  }
         2073  +  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
         2074  +  rc = sqlite3_reoptimize(pStmt);
         2075  +  Tcl_ResetResult(interp);
         2076  +  Tcl_SetResult(interp, (char *)t1ErrorName(rc), 0);
         2077  +  return TCL_OK;
         2078  +}
  2051   2079   
  2052   2080   /*
  2053   2081   ** Usage:  sqlite3_next_stmt  DB  STMT
  2054   2082   **
  2055   2083   ** Return the next statment in sequence after STMT.
  2056   2084   */
  2057   2085   static int test_next_stmt(
................................................................................
  3299   3327       return TCL_ERROR;
  3300   3328     }
  3301   3329     if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  3302   3330     zSql = Tcl_GetString(objv[2]);
  3303   3331     if( Tcl_GetIntFromObj(interp, objv[3], &bytes) ) return TCL_ERROR;
  3304   3332   
  3305   3333     rc = sqlite3_prepare(db, zSql, bytes, &pStmt, objc>=5 ? &zTail : 0);
         3334  +  Tcl_ResetResult(interp);
  3306   3335     if( sqlite3TestErrCode(interp, db, rc) ) return TCL_ERROR;
  3307   3336     if( zTail && objc>=5 ){
  3308   3337       if( bytes>=0 ){
  3309   3338         bytes = bytes - (zTail-zSql);
  3310   3339       }
  3311   3340       if( strlen(zTail)<bytes ){
  3312   3341         bytes = strlen(zTail);
................................................................................
  3356   3385     }
  3357   3386     if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  3358   3387     zSql = Tcl_GetString(objv[2]);
  3359   3388     if( Tcl_GetIntFromObj(interp, objv[3], &bytes) ) return TCL_ERROR;
  3360   3389   
  3361   3390     rc = sqlite3_prepare_v2(db, zSql, bytes, &pStmt, objc>=5 ? &zTail : 0);
  3362   3391     assert(rc==SQLITE_OK || pStmt==0);
         3392  +  Tcl_ResetResult(interp);
  3363   3393     if( sqlite3TestErrCode(interp, db, rc) ) return TCL_ERROR;
  3364   3394     if( zTail && objc>=5 ){
  3365   3395       if( bytes>=0 ){
  3366   3396         bytes = bytes - (zTail-zSql);
  3367   3397       }
  3368   3398       Tcl_ObjSetVar2(interp, objv[4], 0, Tcl_NewStringObj(zTail, bytes), 0);
  3369   3399     }
................................................................................
  4997   5027        { "sqlite3_reset",                 test_reset         ,0 },
  4998   5028        { "sqlite3_expired",               test_expired       ,0 },
  4999   5029        { "sqlite3_transfer_bindings",     test_transfer_bind ,0 },
  5000   5030        { "sqlite3_changes",               test_changes       ,0 },
  5001   5031        { "sqlite3_step",                  test_step          ,0 },
  5002   5032        { "sqlite3_sql",                   test_sql           ,0 },
  5003   5033        { "sqlite3_next_stmt",             test_next_stmt     ,0 },
         5034  +     { "sqlite3_reoptimize",            test_reoptimize    ,0 },
  5004   5035   
  5005   5036        { "sqlite3_release_memory",        test_release_memory,     0},
  5006   5037        { "sqlite3_soft_heap_limit",       test_soft_heap_limit,    0},
  5007   5038        { "sqlite3_thread_cleanup",        test_thread_cleanup,     0},
  5008   5039        { "sqlite3_pager_refcounts",       test_pager_refcounts,    0},
  5009   5040   
  5010   5041        { "sqlite3_load_extension",        test_load_extension,     0},

Changes to src/vdbe.c.

   147    147   ** P if required.
   148    148   */
   149    149   #define ExpandBlob(P) (((P)->flags&MEM_Zero)?sqlite3VdbeMemExpandBlob(P):0)
   150    150   
   151    151   /*
   152    152   ** Argument pMem points at a register that will be passed to a
   153    153   ** user-defined function or returned to the user as the result of a query.
   154         -** The second argument, 'db_enc' is the text encoding used by the vdbe for
   155         -** register variables.  This routine sets the pMem->enc and pMem->type
   156         -** variables used by the sqlite3_value_*() routines.
          154  +** This routine sets the pMem->type variable used by the sqlite3_value_*() 
          155  +** routines.
   157    156   */
   158         -#define storeTypeInfo(A,B) _storeTypeInfo(A)
   159         -static void _storeTypeInfo(Mem *pMem){
          157  +void sqlite3VdbeMemStoreType(Mem *pMem){
   160    158     int flags = pMem->flags;
   161    159     if( flags & MEM_Null ){
   162    160       pMem->type = SQLITE_NULL;
   163    161     }
   164    162     else if( flags & MEM_Int ){
   165    163       pMem->type = SQLITE_INTEGER;
   166    164     }
................................................................................
   323    321   ** loss of information and return the revised type of the argument.
   324    322   **
   325    323   ** This is an EXPERIMENTAL api and is subject to change or removal.
   326    324   */
   327    325   int sqlite3_value_numeric_type(sqlite3_value *pVal){
   328    326     Mem *pMem = (Mem*)pVal;
   329    327     applyNumericAffinity(pMem);
   330         -  storeTypeInfo(pMem, 0);
          328  +  sqlite3VdbeMemStoreType(pMem);
   331    329     return pMem->type;
   332    330   }
   333    331   
   334    332   /*
   335    333   ** Exported version of applyAffinity(). This one works on sqlite3_value*, 
   336    334   ** not the internal Mem* type.
   337    335   */
................................................................................
  1017   1015     Mem *pVar;       /* Value being transferred */
  1018   1016   
  1019   1017     p1 = pOp->p1 - 1;
  1020   1018     p2 = pOp->p2;
  1021   1019     n = pOp->p3;
  1022   1020     assert( p1>=0 && p1+n<=p->nVar );
  1023   1021     assert( p2>=1 && p2+n-1<=p->nMem );
  1024         -  assert( pOp->p4.z==0 || pOp->p3==1 );
         1022  +  assert( pOp->p4.z==0 || pOp->p3==1 || pOp->p3==0 );
  1025   1023   
  1026   1024     while( n-- > 0 ){
  1027   1025       pVar = &p->aVar[p1++];
  1028   1026       if( sqlite3VdbeMemTooBig(pVar) ){
  1029   1027         goto too_big;
  1030   1028       }
  1031   1029       pOut = &p->aMem[p2++];
................................................................................
  1164   1162     /* Make sure the results of the current row are \000 terminated
  1165   1163     ** and have an assigned type.  The results are de-ephemeralized as
  1166   1164     ** as side effect.
  1167   1165     */
  1168   1166     pMem = p->pResultSet = &p->aMem[pOp->p1];
  1169   1167     for(i=0; i<pOp->p2; i++){
  1170   1168       sqlite3VdbeMemNulTerminate(&pMem[i]);
  1171         -    storeTypeInfo(&pMem[i], encoding);
         1169  +    sqlite3VdbeMemStoreType(&pMem[i]);
  1172   1170       REGISTER_TRACE(pOp->p1+i, &pMem[i]);
  1173   1171     }
  1174   1172     if( db->mallocFailed ) goto no_mem;
  1175   1173   
  1176   1174     /* Return SQLITE_ROW
  1177   1175     */
  1178   1176     p->pc = pc + 1;
................................................................................
  1383   1381     assert( apVal || n==0 );
  1384   1382   
  1385   1383     assert( n==0 || (pOp->p2>0 && pOp->p2+n<=p->nMem+1) );
  1386   1384     assert( pOp->p3<pOp->p2 || pOp->p3>=pOp->p2+n );
  1387   1385     pArg = &p->aMem[pOp->p2];
  1388   1386     for(i=0; i<n; i++, pArg++){
  1389   1387       apVal[i] = pArg;
  1390         -    storeTypeInfo(pArg, encoding);
         1388  +    sqlite3VdbeMemStoreType(pArg);
  1391   1389       REGISTER_TRACE(pOp->p2, pArg);
  1392   1390     }
  1393   1391   
  1394   1392     assert( pOp->p4type==P4_FUNCDEF || pOp->p4type==P4_VDBEFUNC );
  1395   1393     if( pOp->p4type==P4_FUNCDEF ){
  1396   1394       ctx.pFunc = pOp->p4.pFunc;
  1397   1395       ctx.pVdbeFunc = 0;
................................................................................
  5047   5045     n = pOp->p5;
  5048   5046     assert( n>=0 );
  5049   5047     pRec = &p->aMem[pOp->p2];
  5050   5048     apVal = p->apArg;
  5051   5049     assert( apVal || n==0 );
  5052   5050     for(i=0; i<n; i++, pRec++){
  5053   5051       apVal[i] = pRec;
  5054         -    storeTypeInfo(pRec, encoding);
         5052  +    sqlite3VdbeMemStoreType(pRec);
  5055   5053     }
  5056   5054     ctx.pFunc = pOp->p4.pFunc;
  5057   5055     assert( pOp->p3>0 && pOp->p3<=p->nMem );
  5058   5056     ctx.pMem = pMem = &p->aMem[pOp->p3];
  5059   5057     pMem->n++;
  5060   5058     ctx.s.flags = MEM_Null;
  5061   5059     ctx.s.z = 0;
................................................................................
  5335   5333   
  5336   5334     /* Invoke the xFilter method */
  5337   5335     {
  5338   5336       res = 0;
  5339   5337       apArg = p->apArg;
  5340   5338       for(i = 0; i<nArg; i++){
  5341   5339         apArg[i] = &pArgc[i+1];
  5342         -      storeTypeInfo(apArg[i], 0);
         5340  +      sqlite3VdbeMemStoreType(apArg[i]);
  5343   5341       }
  5344   5342   
  5345   5343       if( sqlite3SafetyOff(db) ) goto abort_due_to_misuse;
  5346   5344       p->inVtabMethod = 1;
  5347   5345       rc = pModule->xFilter(pVtabCursor, iQuery, pOp->p4.z, nArg, apArg);
  5348   5346       p->inVtabMethod = 0;
  5349   5347       sqlite3DbFree(db, p->zErrMsg);
................................................................................
  5539   5537     pModule = (sqlite3_module *)pVtab->pModule;
  5540   5538     nArg = pOp->p2;
  5541   5539     assert( pOp->p4type==P4_VTAB );
  5542   5540     if( ALWAYS(pModule->xUpdate) ){
  5543   5541       apArg = p->apArg;
  5544   5542       pX = &p->aMem[pOp->p3];
  5545   5543       for(i=0; i<nArg; i++){
  5546         -      storeTypeInfo(pX, 0);
         5544  +      sqlite3VdbeMemStoreType(pX);
  5547   5545         apArg[i] = pX;
  5548   5546         pX++;
  5549   5547       }
  5550   5548       if( sqlite3SafetyOff(db) ) goto abort_due_to_misuse;
  5551   5549       rc = pModule->xUpdate(pVtab, nArg, apArg, &rowid);
  5552   5550       sqlite3DbFree(db, p->zErrMsg);
  5553   5551       p->zErrMsg = pVtab->zErrMsg;

Changes to src/vdbe.h.

   198    198   int sqlite3VdbeSetColName(Vdbe*, int, int, const char *, void(*)(void*));
   199    199   void sqlite3VdbeCountChanges(Vdbe*);
   200    200   sqlite3 *sqlite3VdbeDb(Vdbe*);
   201    201   void sqlite3VdbeSetSql(Vdbe*, const char *z, int n, int);
   202    202   void sqlite3VdbeSwap(Vdbe*,Vdbe*);
   203    203   VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
   204    204   void sqlite3VdbeProgramDelete(sqlite3 *, SubProgram *, int);
          205  +sqlite3_value *sqlite3VdbeGetValue(Vdbe*, int, u8);
          206  +void sqlite3VdbeSetVarmask(Vdbe*,int,int);
   205    207   
   206    208   #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
   207    209   int sqlite3VdbeReleaseMemory(int);
   208    210   #endif
   209    211   UnpackedRecord *sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,char*,int);
   210    212   void sqlite3VdbeDeleteUnpackedRecord(UnpackedRecord*);
   211    213   int sqlite3VdbeRecordCompare(int,const void*,UnpackedRecord*);

Changes to src/vdbeInt.h.

   319    319     i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
   320    320     int iStatement;         /* Statement number (or 0 if has not opened stmt) */
   321    321   #ifdef SQLITE_DEBUG
   322    322     FILE *trace;            /* Write an execution trace here, if not NULL */
   323    323   #endif
   324    324     VdbeFrame *pFrame;      /* Parent frame */
   325    325     int nFrame;             /* Number of frames in pFrame list */
          326  +  u8 optimizable;         /* True if VM may benefit from sqlite3_reoptimize() */
          327  +  u32 optmask;            /* Bitmask of vars that may be used by reoptimize() */
          328  +  u32 expmask;            /* Binding to these vars invalidates VM */
   326    329   };
   327    330   
   328    331   /*
   329    332   ** The following are allowed values for Vdbe.magic
   330    333   */
   331    334   #define VDBE_MAGIC_INIT     0x26bceaa5    /* Building a VDBE program */
   332    335   #define VDBE_MAGIC_RUN      0xbdf20da3    /* VDBE is ready to execute */
................................................................................
   384    387   int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve);
   385    388   int sqlite3VdbeCloseStatement(Vdbe *, int);
   386    389   void sqlite3VdbeFrameDelete(VdbeFrame*);
   387    390   int sqlite3VdbeFrameRestore(VdbeFrame *);
   388    391   #ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
   389    392   int sqlite3VdbeReleaseBuffers(Vdbe *p);
   390    393   #endif
          394  +void sqlite3VdbeMemStoreType(Mem *pMem);
   391    395   
   392    396   #ifndef SQLITE_OMIT_FOREIGN_KEY
   393    397   int sqlite3VdbeCheckFk(Vdbe *, int);
   394    398   #else
   395    399   # define sqlite3VdbeCheckFk(p,i) 0
   396    400   #endif
   397    401   

Changes to src/vdbeapi.c.

   910    910       return SQLITE_RANGE;
   911    911     }
   912    912     i--;
   913    913     pVar = &p->aVar[i];
   914    914     sqlite3VdbeMemRelease(pVar);
   915    915     pVar->flags = MEM_Null;
   916    916     sqlite3Error(p->db, SQLITE_OK, 0);
          917  +
          918  +  /* If the bit corresponding to this variable is set in Vdbe.opmask, set 
          919  +  ** the optimizable flag before returning. This tells the sqlite3_reoptimize()
          920  +  ** function that the VM program may benefit from recompilation. 
          921  +  **
          922  +  ** If the bit in Vdbe.expmask is set, then binding a new value to this
          923  +  ** variable invalidates the current query plan. This comes about when the
          924  +  ** variable is the RHS of a LIKE or GLOB operator and the LIKE/GLOB is
          925  +  ** able to use an index.  */
          926  +  if( (i<32 && p->optmask & ((u32)1 << i)) || p->optmask==0xffffffff ){
          927  +    p->optimizable = 1;
          928  +  }
          929  +  if( (i<32 && p->expmask & ((u32)1 << i)) || p->expmask==0xffffffff ){
          930  +    p->expired = 1;
          931  +  }
   917    932     return SQLITE_OK;
   918    933   }
   919    934   
   920    935   /*
   921    936   ** Bind a text or BLOB value.
   922    937   */
   923    938   static int bindText(
................................................................................
  1201   1216   */
  1202   1217   int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
  1203   1218     Vdbe *pVdbe = (Vdbe*)pStmt;
  1204   1219     int v = pVdbe->aCounter[op-1];
  1205   1220     if( resetFlag ) pVdbe->aCounter[op-1] = 0;
  1206   1221     return v;
  1207   1222   }
         1223  +
         1224  +/*
         1225  +** If possible, optimize the statement for the current bindings.
         1226  +*/
         1227  +int sqlite3_reoptimize(sqlite3_stmt *pStmt){
         1228  +  int rc = SQLITE_OK;
         1229  +  Vdbe *v = (Vdbe *)pStmt;
         1230  +  sqlite3 *db = v->db;
         1231  +
         1232  +  sqlite3_mutex_enter(db->mutex);
         1233  +  if( v->isPrepareV2==0 || v->pc>0 ){
         1234  +    rc = SQLITE_MISUSE;
         1235  +  }else if( v->optimizable ){
         1236  +    sqlite3 *db = v->db;
         1237  +    sqlite3_mutex_enter(db->mutex);
         1238  +    rc = sqlite3Reprepare(v);
         1239  +    rc = sqlite3ApiExit(db, rc);
         1240  +  }
         1241  +  assert( rc!=SQLITE_OK || v->optimizable==0 );
         1242  +  sqlite3_mutex_leave(db->mutex);
         1243  +
         1244  +  return rc;
         1245  +}
         1246  +

Changes to src/vdbeaux.c.

  3018   3018   
  3019   3019   /*
  3020   3020   ** Return the database associated with the Vdbe.
  3021   3021   */
  3022   3022   sqlite3 *sqlite3VdbeDb(Vdbe *v){
  3023   3023     return v->db;
  3024   3024   }
         3025  +
         3026  +/*
         3027  +** Return a pointer to an sqlite3_value structure containing the value bound
         3028  +** parameter iVar of VM v. Except, if the value is an SQL NULL, return 
         3029  +** 0 instead. Unless it is NULL, apply affinity aff (one of the SQLITE_AFF_*
         3030  +** constants) to the value before returning it.
         3031  +**
         3032  +** The returned value must be freed by the caller using sqlite3ValueFree().
         3033  +*/
         3034  +sqlite3_value *sqlite3VdbeGetValue(Vdbe *v, int iVar, u8 aff){
         3035  +  assert( iVar>0 );
         3036  +  if( v ){
         3037  +    Mem *pMem = &v->aVar[iVar-1];
         3038  +    if( 0==(pMem->flags & MEM_Null) ){
         3039  +      sqlite3_value *pRet = sqlite3ValueNew(v->db);
         3040  +      if( pRet ){
         3041  +        sqlite3VdbeMemCopy((Mem *)pRet, pMem);
         3042  +        sqlite3ValueApplyAffinity(pRet, aff, SQLITE_UTF8);
         3043  +        sqlite3VdbeMemStoreType((Mem *)pRet);
         3044  +      }
         3045  +      return pRet;
         3046  +    }
         3047  +  }
         3048  +  return 0;
         3049  +}
         3050  +
         3051  +/*
         3052  +** Configure SQL variable iVar so that binding a new value to it signals
         3053  +** to sqlite3_reoptimize() that re-preparing the statement may result
         3054  +** in a better query plan.
         3055  +*/
         3056  +void sqlite3VdbeSetVarmask(Vdbe *v, int iVar, int isExpire){
         3057  +  u32 *mask = (isExpire ? &v->expmask : &v->optmask);
         3058  +  assert( iVar>0 );
         3059  +  if( iVar>32 ){
         3060  +    *mask = 0xffffffff;
         3061  +  }else{
         3062  +    *mask |= ((u32)1 << (iVar-1));
         3063  +  }
         3064  +}
         3065  +

Changes to src/vdbemem.c.

  1049   1049       nVal = sqlite3Strlen30(zVal)-1;
  1050   1050       assert( zVal[nVal]=='\'' );
  1051   1051       sqlite3VdbeMemSetStr(pVal, sqlite3HexToBlob(db, zVal, nVal), nVal/2,
  1052   1052                            0, SQLITE_DYNAMIC);
  1053   1053     }
  1054   1054   #endif
  1055   1055   
         1056  +  if( pVal ){
         1057  +    sqlite3VdbeMemStoreType(pVal);
         1058  +  }
  1056   1059     *ppVal = pVal;
  1057   1060     return SQLITE_OK;
  1058   1061   
  1059   1062   no_mem:
  1060   1063     db->mallocFailed = 1;
  1061   1064     sqlite3DbFree(db, zVal);
  1062   1065     sqlite3ValueFree(pVal);

Changes to src/where.c.

   621    621   **
   622    622   ** In order for the operator to be optimizible, the RHS must be a string
   623    623   ** literal that does not begin with a wildcard.  
   624    624   */
   625    625   static int isLikeOrGlob(
   626    626     Parse *pParse,    /* Parsing and code generating context */
   627    627     Expr *pExpr,      /* Test this expression */
   628         -  int *pnPattern,   /* Number of non-wildcard prefix characters */
          628  +  Expr **ppPrefix,  /* Pointer to TK_STRING expression with pattern prefix */
   629    629     int *pisComplete, /* True if the only wildcard is % in the last character */
   630    630     int *pnoCase      /* True if uppercase is equivalent to lowercase */
   631    631   ){
   632         -  const char *z;             /* String on RHS of LIKE operator */
          632  +  const char *z = 0;         /* String on RHS of LIKE operator */
   633    633     Expr *pRight, *pLeft;      /* Right and left size of LIKE operator */
   634    634     ExprList *pList;           /* List of operands to the LIKE operator */
   635    635     int c;                     /* One character in z[] */
   636    636     int cnt;                   /* Number of non-wildcard prefix characters */
   637    637     char wc[3];                /* Wildcard characters */
   638    638     CollSeq *pColl;            /* Collating sequence for LHS */
   639    639     sqlite3 *db = pParse->db;  /* Database connection */
          640  +  sqlite3_value *pVal = 0;
          641  +  int op;                    /* Opcode of pRight */
   640    642   
   641    643     if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){
   642    644       return 0;
   643    645     }
   644    646   #ifdef SQLITE_EBCDIC
   645    647     if( *pnoCase ) return 0;
   646    648   #endif
   647    649     pList = pExpr->x.pList;
   648         -  pRight = pList->a[0].pExpr;
   649         -  if( pRight->op!=TK_STRING ){
   650         -    return 0;
   651         -  }
   652    650     pLeft = pList->a[1].pExpr;
   653    651     if( pLeft->op!=TK_COLUMN ){
   654    652       return 0;
   655    653     }
   656    654     pColl = sqlite3ExprCollSeq(pParse, pLeft);
   657    655     assert( pColl!=0 || pLeft->iColumn==-1 );
   658    656     if( pColl==0 ) return 0;
   659    657     if( (pColl->type!=SQLITE_COLL_BINARY || *pnoCase) &&
   660    658         (pColl->type!=SQLITE_COLL_NOCASE || !*pnoCase) ){
   661    659       return 0;
   662    660     }
   663    661     if( sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT ) return 0;
   664         -  z = pRight->u.zToken;
   665         -  if( ALWAYS(z) ){
          662  +
          663  +  pRight = pList->a[0].pExpr;
          664  +  op = pRight->op;
          665  +  if( op==TK_REGISTER ){
          666  +    op = pRight->op2;
          667  +  }
          668  +  if( op==TK_VARIABLE ){
          669  +    Vdbe *pReprepare = pParse->pReprepare;
          670  +    pVal = sqlite3VdbeGetValue(pReprepare, pRight->iColumn, SQLITE_AFF_NONE);
          671  +    if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
          672  +      z = (char *)sqlite3_value_text(pVal);
          673  +    }
          674  +    sqlite3VdbeSetVarmask(pParse->pVdbe, pRight->iColumn, 0);
          675  +    assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
          676  +  }else if( op==TK_STRING ){
          677  +    z = pRight->u.zToken;
          678  +  }
          679  +  if( z ){
   666    680       cnt = 0;
   667    681       while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
   668    682         cnt++;
   669    683       }
   670    684       if( cnt!=0 && c!=0 && 255!=(u8)z[cnt-1] ){
          685  +      Expr *pPrefix;
   671    686         *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;
   672         -      *pnPattern = cnt;
   673         -      return 1;
          687  +      pPrefix = sqlite3Expr(db, TK_STRING, z);
          688  +      if( pPrefix ) pPrefix->u.zToken[cnt] = 0;
          689  +      *ppPrefix = pPrefix;
          690  +      if( op==TK_VARIABLE ){
          691  +        Vdbe *v = pParse->pVdbe;
          692  +        sqlite3VdbeSetVarmask(v, pRight->iColumn, 1);
          693  +        if( *pisComplete && pRight->u.zToken[1] ){
          694  +          /* If the rhs of the LIKE expression is a variable, and the current
          695  +          ** value of the variable means there is no need to invoke the LIKE
          696  +          ** function, then no OP_Variable will be added to the program.
          697  +          ** This causes problems for the sqlite3_bind_parameter_name()
          698  +          ** API. To workaround them, add a dummy OP_Variable here.  */ 
          699  +          sqlite3ExprCodeTarget(pParse, pRight, 1);
          700  +          sqlite3VdbeChangeP3(v, sqlite3VdbeCurrentAddr(v)-1, 0);
          701  +        }
          702  +      }
          703  +    }else{
          704  +      z = 0;
   674    705       }
   675    706     }
   676         -  return 0;
          707  +
          708  +  sqlite3ValueFree(pVal);
          709  +  return (z!=0);
   677    710   }
   678    711   #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
   679    712   
   680    713   
   681    714   #ifndef SQLITE_OMIT_VIRTUALTABLE
   682    715   /*
   683    716   ** Check to see if the given expression is of the form
................................................................................
  1051   1084   ){
  1052   1085     WhereTerm *pTerm;                /* The term to be analyzed */
  1053   1086     WhereMaskSet *pMaskSet;          /* Set of table index masks */
  1054   1087     Expr *pExpr;                     /* The expression to be analyzed */
  1055   1088     Bitmask prereqLeft;              /* Prerequesites of the pExpr->pLeft */
  1056   1089     Bitmask prereqAll;               /* Prerequesites of pExpr */
  1057   1090     Bitmask extraRight = 0;
  1058         -  int nPattern;
  1059   1091     int isComplete;
  1060   1092     int noCase;
  1061   1093     int op;                          /* Top-level operator.  pExpr->op */
  1062   1094     Parse *pParse = pWC->pParse;     /* Parsing context */
  1063   1095     sqlite3 *db = pParse->db;        /* Database connection */
         1096  +  Expr *pStr1;
  1064   1097   
  1065   1098     if( db->mallocFailed ){
  1066   1099       return;
  1067   1100     }
  1068   1101     pTerm = &pWC->a[idxTerm];
  1069   1102     pMaskSet = pWC->pMaskSet;
  1070   1103     pExpr = pTerm->pExpr;
................................................................................
  1188   1221     ** A like pattern of the form "x LIKE 'abc%'" is changed into constraints
  1189   1222     **
  1190   1223     **          x>='abc' AND x<'abd' AND x LIKE 'abc%'
  1191   1224     **
  1192   1225     ** The last character of the prefix "abc" is incremented to form the
  1193   1226     ** termination condition "abd".
  1194   1227     */
  1195         -  if( isLikeOrGlob(pParse, pExpr, &nPattern, &isComplete, &noCase)
  1196         -         && pWC->op==TK_AND ){
  1197         -    Expr *pLeft, *pRight;
  1198         -    Expr *pStr1, *pStr2;
         1228  +  if( pWC->op==TK_AND 
         1229  +   && isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase)
         1230  +  ){
         1231  +    Expr *pLeft;
         1232  +    Expr *pStr2;
  1199   1233       Expr *pNewExpr1, *pNewExpr2;
  1200   1234       int idxNew1, idxNew2;
  1201   1235   
  1202   1236       pLeft = pExpr->x.pList->a[1].pExpr;
  1203         -    pRight = pExpr->x.pList->a[0].pExpr;
  1204         -    pStr1 = sqlite3Expr(db, TK_STRING, pRight->u.zToken);
  1205         -    if( pStr1 ) pStr1->u.zToken[nPattern] = 0;
  1206   1237       pStr2 = sqlite3ExprDup(db, pStr1, 0);
  1207   1238       if( !db->mallocFailed ){
  1208   1239         u8 c, *pC;       /* Last character before the first wildcard */
  1209         -      pC = (u8*)&pStr2->u.zToken[nPattern-1];
         1240  +      pC = (u8*)&pStr2->u.zToken[sqlite3Strlen30(pStr2->u.zToken)-1];
  1210   1241         c = *pC;
  1211   1242         if( noCase ){
  1212   1243           /* The point is to increment the last character before the first
  1213   1244           ** wildcard.  But if we increment '@', that will push it into the
  1214   1245           ** alphabetic range where case conversions will mess up the 
  1215   1246           ** inequality.  To avoid this, make sure to also run the full
  1216   1247           ** LIKE on all candidate expressions by clearing the isComplete flag
................................................................................
  1979   2010   
  1980   2011       assert( i>=0 && i<=SQLITE_INDEX_SAMPLES );
  1981   2012       *piRegion = i;
  1982   2013     }
  1983   2014     return SQLITE_OK;
  1984   2015   }
  1985   2016   #endif   /* #ifdef SQLITE_ENABLE_STAT2 */
         2017  +
         2018  +/*
         2019  +** If expression pExpr represents a literal value, set *pp to point to
         2020  +** an sqlite3_value structure containing the same value, with affinity
         2021  +** aff applied to it, before returning. It is the responsibility of the 
         2022  +** caller to eventually release this structure by passing it to 
         2023  +** sqlite3ValueFree().
         2024  +**
         2025  +** If the current parse is a recompile (sqlite3Reprepare()) and pExpr
         2026  +** is an SQL variable that currently has a non-NULL value bound to it,
         2027  +** create an sqlite3_value structure containing this value, again with
         2028  +** affinity aff applied to it, instead.
         2029  +**
         2030  +** If neither of the above apply, set *pp to NULL.
         2031  +**
         2032  +** If an error occurs, return an error code. Otherwise, SQLITE_OK.
         2033  +*/
         2034  +static int valueFromExpr(
         2035  +  Parse *pParse, 
         2036  +  Expr *pExpr, 
         2037  +  u8 aff, 
         2038  +  sqlite3_value **pp
         2039  +){
         2040  +  if( (pExpr->op==TK_VARIABLE)
         2041  +   || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
         2042  +  ){
         2043  +    int iVar = pExpr->iColumn;
         2044  +    sqlite3VdbeSetVarmask(pParse->pVdbe, iVar, 0);
         2045  +    *pp = sqlite3VdbeGetValue(pParse->pReprepare, iVar, aff);
         2046  +    return SQLITE_OK;
         2047  +  }
         2048  +  return sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, aff, pp);
         2049  +}
  1986   2050   
  1987   2051   /*
  1988   2052   ** This function is used to estimate the number of rows that will be visited
  1989   2053   ** by scanning an index for a range of values. The range may have an upper
  1990   2054   ** bound, a lower bound, or both. The WHERE clause terms that set the upper
  1991   2055   ** and lower bounds are represented by pLower and pUpper respectively. For
  1992   2056   ** example, assuming that index p is on t1(a):
................................................................................
  2032   2096     WhereTerm *pLower,   /* Lower bound on the range. ex: "x>123" Might be NULL */
  2033   2097     WhereTerm *pUpper,   /* Upper bound on the range. ex: "x<455" Might be NULL */
  2034   2098     int *piEst           /* OUT: Return value */
  2035   2099   ){
  2036   2100     int rc = SQLITE_OK;
  2037   2101   
  2038   2102   #ifdef SQLITE_ENABLE_STAT2
  2039         -  sqlite3 *db = pParse->db;
  2040         -  sqlite3_value *pLowerVal = 0;
  2041         -  sqlite3_value *pUpperVal = 0;
  2042   2103   
  2043   2104     if( nEq==0 && p->aSample ){
         2105  +    sqlite3_value *pLowerVal = 0;
         2106  +    sqlite3_value *pUpperVal = 0;
  2044   2107       int iEst;
  2045   2108       int iLower = 0;
  2046   2109       int iUpper = SQLITE_INDEX_SAMPLES;
  2047         -    u8 aff = p->pTable->aCol[0].affinity;
         2110  +    u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;
  2048   2111   
  2049   2112       if( pLower ){
  2050   2113         Expr *pExpr = pLower->pExpr->pRight;
  2051         -      rc = sqlite3ValueFromExpr(db, pExpr, SQLITE_UTF8, aff, &pLowerVal);
         2114  +      rc = valueFromExpr(pParse, pExpr, aff, &pLowerVal);
  2052   2115       }
  2053   2116       if( rc==SQLITE_OK && pUpper ){
  2054   2117         Expr *pExpr = pUpper->pExpr->pRight;
  2055         -      rc = sqlite3ValueFromExpr(db, pExpr, SQLITE_UTF8, aff, &pUpperVal);
         2118  +      rc = valueFromExpr(pParse, pExpr, aff, &pUpperVal);
  2056   2119       }
  2057   2120   
  2058   2121       if( rc!=SQLITE_OK || (pLowerVal==0 && pUpperVal==0) ){
  2059   2122         sqlite3ValueFree(pLowerVal);
  2060   2123         sqlite3ValueFree(pUpperVal);
  2061   2124         goto range_est_fallback;
  2062   2125       }else if( pLowerVal==0 ){

Added test/analyze3.test.

            1  +# 2009 August 06
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library. This file 
           13  +# implements tests for the sqlite3_reoptimize() functionality.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +ifcapable !stat2 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +#----------------------------------------------------------------------
           25  +# Test Organization:
           26  +#
           27  +# analyze3-1.*: Test that the values of bound parameters are considered 
           28  +#               in the same way as constants when planning queries that
           29  +#               use range constraints.
           30  +#
           31  +# analyze3-2.*: Test that the values of bound parameters are considered 
           32  +#               in the same way as constants when planning queries that
           33  +#               use LIKE expressions in the WHERE clause.
           34  +#
           35  +# analyze3-3.*: Test that sqlite3_reoptimize() is a no-op when there is
           36  +#               no way for re-preparing the query to produce a superior 
           37  +#               query plan.
           38  +#
           39  +# analyze3-4.*: Test that SQL or authorization callback errors occuring
           40  +#               within sqlite3_reoptimize() are handled correctly.
           41  +#
           42  +
           43  +proc getvar {varname} { uplevel #0 set $varname }
           44  +db function var getvar
           45  +
           46  +proc eqp {sql {db db}} {
           47  +  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
           48  +}
           49  +
           50  +proc sf_execsql {sql {db db}} {
           51  +  set ::sqlite_search_count 0
           52  +  set r [uplevel [list execsql $sql $db]]
           53  +
           54  +  concat $::sqlite_search_count [$db status step] $r
           55  +}
           56  +
           57  +#-------------------------------------------------------------------------
           58  +#
           59  +# analyze3-1.1.1: 
           60  +#   Create a table with two columns. Populate the first column (affinity 
           61  +#   INTEGER) with integer values from 100 to 1100. Create an index on this 
           62  +#   column. ANALYZE the table.
           63  +#
           64  +# analyze3-1.1.2 - 3.1.3
           65  +#   Show that there are two possible plans for querying the table with
           66  +#   a range constraint on the indexed column - "full table scan" or "use 
           67  +#   the index". When the range is specified using literal values, SQLite
           68  +#   is able to pick the best plan based on the samples in sqlite_stat2.
           69  +#
           70  +# analyze3-1.1.4 - 3.1.9
           71  +#   Show that using SQL variables produces the same results as using
           72  +#   literal values to constrain the range scan. This works because the
           73  +#   Tcl interface always calls [sqlite3_reoptimize] after binding values.
           74  +#
           75  +#   These tests also check that the compiler code considers column 
           76  +#   affinities when estimating the number of rows scanned by the "use 
           77  +#   index strategy".
           78  +#
           79  +do_test analyze3-1.1.1 {
           80  +  execsql {
           81  +    BEGIN;
           82  +    CREATE TABLE t1(x INTEGER, y);
           83  +    CREATE INDEX i1 ON t1(x);
           84  +  }
           85  +  for {set i 0} {$i < 1000} {incr i} {
           86  +    execsql { INSERT INTO t1 VALUES($i+100, $i) }
           87  +  }
           88  +  execsql {
           89  +    COMMIT;
           90  +    ANALYZE;
           91  +  }
           92  +} {}
           93  +
           94  +do_test analyze3-1.1.2 {
           95  +  eqp { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
           96  +} {0 0 {TABLE t1 WITH INDEX i1}}
           97  +do_test analyze3-1.1.3 {
           98  +  eqp { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
           99  +} {0 0 {TABLE t1}}
          100  +
          101  +do_test analyze3-1.1.4 {
          102  +  sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
          103  +} {199 0 14850}
          104  +do_test analyze3-1.1.5 {
          105  +  set l [string range "200" 0 end]
          106  +  set u [string range "300" 0 end]
          107  +  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
          108  +} {199 0 14850}
          109  +do_test analyze3-1.1.6 {
          110  +  set l [expr int(200)]
          111  +  set u [expr int(300)]
          112  +  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
          113  +} {199 0 14850}
          114  +do_test analyze3-1.1.7 {
          115  +  sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
          116  +} {999 999 499500}
          117  +do_test analyze3-1.1.8 {
          118  +  set l [string range "0" 0 end]
          119  +  set u [string range "1100" 0 end]
          120  +  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
          121  +} {999 999 499500}
          122  +do_test analyze3-1.1.9 {
          123  +  set l [expr int(0)]
          124  +  set u [expr int(1100)]
          125  +  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
          126  +} {999 999 499500}
          127  +
          128  +
          129  +# The following tests are similar to the block above. The difference is
          130  +# that the indexed column has TEXT affinity in this case. In the tests
          131  +# above the affinity is INTEGER.
          132  +#
          133  +do_test analyze3-1.2.1 {
          134  +  execsql {
          135  +    BEGIN;
          136  +      CREATE TABLE t2(x TEXT, y);
          137  +      INSERT INTO t2 SELECT * FROM t1;
          138  +      CREATE INDEX i2 ON t2(x);
          139  +    COMMIT;
          140  +    ANALYZE;
          141  +  }
          142  +} {}
          143  +do_test analyze3-1.2.2 {
          144  +  eqp { SELECT sum(y) FROM t2 WHERE x>1 AND x<2 }
          145  +} {0 0 {TABLE t2 WITH INDEX i2}}
          146  +do_test analyze3-1.2.3 {
          147  +  eqp { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
          148  +} {0 0 {TABLE t2}}
          149  +do_test analyze3-1.2.4 {
          150  +  sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
          151  +} {161 0 4760}
          152  +do_test analyze3-1.2.5 {
          153  +  set l [string range "12" 0 end]
          154  +  set u [string range "20" 0 end]
          155  +  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
          156  +} {161 0 text text 4760}
          157  +do_test analyze3-1.2.6 {
          158  +  set l [expr int(12)]
          159  +  set u [expr int(20)]
          160  +  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
          161  +} {161 0 integer integer 4760}
          162  +do_test analyze3-1.2.7 {
          163  +  sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
          164  +} {999 999 490555}
          165  +do_test analyze3-1.2.8 {
          166  +  set l [string range "0" 0 end]
          167  +  set u [string range "99" 0 end]
          168  +  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
          169  +} {999 999 text text 490555}
          170  +do_test analyze3-1.2.9 {
          171  +  set l [expr int(0)]
          172  +  set u [expr int(99)]
          173  +  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
          174  +} {999 999 integer integer 490555}
          175  +
          176  +# Same tests a third time. This time, column x has INTEGER affinity and
          177  +# is not the leftmost column of the table. This triggered a bug causing
          178  +# SQLite to use sub-optimal query plans in 3.6.18 and earlier.
          179  +#
          180  +do_test analyze3-1.3.1 {
          181  +  execsql {
          182  +    BEGIN;
          183  +      CREATE TABLE t3(y TEXT, x INTEGER);
          184  +      INSERT INTO t3 SELECT y, x FROM t1;
          185  +      CREATE INDEX i3 ON t3(x);
          186  +    COMMIT;
          187  +    ANALYZE;
          188  +  }
          189  +} {}
          190  +do_test analyze3-1.3.2 {
          191  +  eqp { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
          192  +} {0 0 {TABLE t3 WITH INDEX i3}}
          193  +do_test analyze3-1.3.3 {
          194  +  eqp { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
          195  +} {0 0 {TABLE t3}}
          196  +
          197  +do_test analyze3-1.3.4 {
          198  +  sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
          199  +} {199 0 14850}
          200  +do_test analyze3-1.3.5 {
          201  +  set l [string range "200" 0 end]
          202  +  set u [string range "300" 0 end]
          203  +  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
          204  +} {199 0 14850}
          205  +do_test analyze3-1.3.6 {
          206  +  set l [expr int(200)]
          207  +  set u [expr int(300)]
          208  +  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
          209  +} {199 0 14850}
          210  +do_test analyze3-1.3.7 {
          211  +  sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
          212  +} {999 999 499500}
          213  +do_test analyze3-1.3.8 {
          214  +  set l [string range "0" 0 end]
          215  +  set u [string range "1100" 0 end]
          216  +  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
          217  +} {999 999 499500}
          218  +do_test analyze3-1.3.9 {
          219  +  set l [expr int(0)]
          220  +  set u [expr int(1100)]
          221  +  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
          222  +} {999 999 499500}
          223  +
          224  +#-------------------------------------------------------------------------
          225  +# Test that the values of bound SQL variables may be used for the LIKE
          226  +# optimization.
          227  +#
          228  +drop_all_tables
          229  +do_test analyze3-2.1 {
          230  +  execsql {
          231  +    PRAGMA case_sensitive_like=off;
          232  +    BEGIN;
          233  +    CREATE TABLE t1(a, b TEXT COLLATE nocase);
          234  +    CREATE INDEX i1 ON t1(b);
          235  +  }
          236  +  for {set i 0} {$i < 1000} {incr i} {
          237  +    set t ""
          238  +    append t [lindex {a b c d e f g h i j} [expr $i/100]]
          239  +    append t [lindex {a b c d e f g h i j} [expr ($i/10)%10]]
          240  +    append t [lindex {a b c d e f g h i j} [expr ($i%10)]]
          241  +    execsql { INSERT INTO t1 VALUES($i, $t) }
          242  +  }
          243  +  execsql COMMIT
          244  +} {}
          245  +do_test analyze3-2.2 {
          246  +  eqp { SELECT count(a) FROM t1 WHERE b LIKE 'a%' }
          247  +} {0 0 {TABLE t1 WITH INDEX i1}}
          248  +do_test analyze3-2.3 {
          249  +  eqp { SELECT count(a) FROM t1 WHERE b LIKE '%a' }
          250  +} {0 0 {TABLE t1}}
          251  +
          252  +do_test analyze3-2.4 {
          253  +  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
          254  +} {101 0 100}
          255  +do_test analyze3-2.5 {
          256  +  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }
          257  +} {999 999 100}
          258  +
          259  +do_test analyze3-2.4 {
          260  +  set like "a%"
          261  +  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
          262  +} {101 0 100}
          263  +do_test analyze3-2.5 {
          264  +  set like "%a"
          265  +  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
          266  +} {999 999 100}
          267  +
          268  +
          269  +#-------------------------------------------------------------------------
          270  +# This block of tests checks that sqlite3_reoptimize() is a no-op if 
          271  +# the values bound to any parameters that may affect the query plan
          272  +# have not changed since the statement was last compiled.
          273  +#
          274  +# It is possible to tell if sqlite3_reoptimize() is a no-op by registering
          275  +# an authorization callback. If the auth callback is not invoked from
          276  +# within a give call to reoptimize(), then it must have been a no-op.
          277  +#
          278  +# Also test that:
          279  +#
          280  +#   * sqlite3_reoptimize() returns SQLITE_MISUSE if called on a statement
          281  +#     that was prepared using the legacy sqlite3_prepare() interface,
          282  +#
          283  +#   * sqlite3_reoptimize() returns SQLITE_MISUSE if called on a statement
          284  +#     that is not in the "reset" state.
          285  +#
          286  +drop_all_tables
          287  +db auth auth
          288  +proc auth {args} {
          289  +  set ::auth 1
          290  +  return SQLITE_OK
          291  +}
          292  +
          293  +# Return true if calling reoptimize() on the statement handle passed 
          294  +# as an argument causes the statement to be recompiled.
          295  +#
          296  +proc test_reoptimize {stmt} {
          297  +  set ::auth 0
          298  +  sqlite3_reoptimize $stmt
          299  +  set ::auth
          300  +}
          301  +
          302  +do_test analyze3-3.1 {
          303  +  execsql {
          304  +    BEGIN;
          305  +    CREATE TABLE t1(a, b, c);
          306  +    CREATE INDEX i1 ON t1(b);
          307  +  }
          308  +  for {set i 0} {$i < 100} {incr i} {
          309  +    execsql { INSERT INTO t1 VALUES($i, $i, $i) }
          310  +  }
          311  +  execsql COMMIT
          312  +  execsql ANALYZE
          313  +} {}
          314  +
          315  +do_test analyze3-3.2.1 {
          316  +  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy]
          317  +  test_reoptimize $S
          318  +} {0}
          319  +do_test analyze3-3.2.2 {
          320  +  sqlite3_bind_text $S 1 "abc" -1
          321  +  test_reoptimize $S
          322  +} {1}
          323  +do_test analyze3-3.2.3 {
          324  +  test_reoptimize $S
          325  +} {0}
          326  +do_test analyze3-3.2.4 {
          327  +  sqlite3_finalize $S
          328  +} {SQLITE_OK}
          329  +
          330  +do_test analyze3-3.2.1 {
          331  +  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy]
          332  +  test_reoptimize $S
          333  +} {0}
          334  +do_test analyze3-3.2.2 {
          335  +  sqlite3_bind_text $S 1 "abc" -1
          336  +  test_reoptimize $S
          337  +} {0}
          338  +do_test analyze3-3.2.3 {
          339  +  sqlite3_finalize $S
          340  +} {SQLITE_OK}
          341  +
          342  +do_test analyze3-3.3.1 {
          343  +  set S [sqlite3_prepare db "SELECT * FROM t1 WHERE b=?" -1 dummy]
          344  +  sqlite3_reoptimize $S
          345  +} {SQLITE_MISUSE}
          346  +do_test analyze3-3.3.2 {
          347  +  sqlite3_finalize $S
          348  +} {SQLITE_OK}
          349  +
          350  +do_test analyze3-3.3.1 {
          351  +  set S [sqlite3_prepare_v2 db "SELECT * FROM t1" -1 dummy]
          352  +  sqlite3_reoptimize $S
          353  +} {SQLITE_OK}
          354  +do_test analyze3-3.3.2 {
          355  +  sqlite3_step $S
          356  +} {SQLITE_ROW}
          357  +do_test analyze3-3.3.3 {
          358  +  sqlite3_reoptimize $S
          359  +} {SQLITE_MISUSE}
          360  +do_test analyze3-3.3.4 {
          361  +  while {"SQLITE_ROW" == [sqlite3_step $S]} {}
          362  +  sqlite3_reoptimize $S
          363  +} {SQLITE_MISUSE}
          364  +do_test analyze3-3.3.5 {
          365  +  sqlite3_finalize $S
          366  +} {SQLITE_OK}
          367  +
          368  +do_test analyze3-3.4.1 {
          369  +  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
          370  +  test_reoptimize $S
          371  +} {0}
          372  +do_test analyze3-3.4.2 {
          373  +  sqlite3_bind_text $S 1 "abc" -1
          374  +  test_reoptimize $S
          375  +} {0}
          376  +do_test analyze3-3.4.3 {
          377  +  sqlite3_bind_text $S 2 "def" -1
          378  +  test_reoptimize $S
          379  +} {1}
          380  +do_test analyze3-3.4.4 {
          381  +  sqlite3_bind_text $S 2 "ghi" -1
          382  +  test_reoptimize $S
          383  +} {1}
          384  +do_test analyze3-3.4.5 {
          385  +  test_reoptimize $S
          386  +} {0}
          387  +do_test analyze3-3.4.6 {
          388  +  sqlite3_finalize $S
          389  +} {SQLITE_OK}
          390  +
          391  +do_test analyze3-3.5.1 {
          392  +  set S [sqlite3_prepare_v2 db {
          393  +    SELECT * FROM t1 WHERE a IN (
          394  +      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,
          395  +      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
          396  +      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31
          397  +    ) AND b>?32;
          398  +  } -1 dummy]
          399  +  test_reoptimize $S
          400  +} {0}
          401  +do_test analyze3-3.5.2 {
          402  +  sqlite3_bind_text $S 31 "abc" -1
          403  +  test_reoptimize $S
          404  +} {0}
          405  +do_test analyze3-3.5.3 {
          406  +  sqlite3_bind_text $S 32 "def" -1
          407  +  test_reoptimize $S
          408  +} {1}
          409  +do_test analyze3-3.5.4 {
          410  +  test_reoptimize $S
          411  +} {0}
          412  +do_test analyze3-3.5.5 {
          413  +  sqlite3_finalize $S
          414  +} {SQLITE_OK}
          415  +
          416  +do_test analyze3-3.6.1 {
          417  +  set S [sqlite3_prepare_v2 db {
          418  +    SELECT * FROM t1 WHERE a IN (
          419  +      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,
          420  +      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
          421  +      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32
          422  +    ) AND b>?33;
          423  +  } -1 dummy]
          424  +  test_reoptimize $S
          425  +} {0}
          426  +do_test analyze3-3.6.2 {
          427  +  sqlite3_bind_text $S 32 "abc" -1
          428  +  test_reoptimize $S
          429  +} {1}
          430  +do_test analyze3-3.6.3 {
          431  +  sqlite3_bind_text $S 33 "def" -1
          432  +  test_reoptimize $S
          433  +} {1}
          434  +do_test analyze3-3.6.4 {
          435  +  test_reoptimize $S
          436  +} {0}
          437  +do_test analyze3-3.6.5 {
          438  +  sqlite3_finalize $S
          439  +} {SQLITE_OK}
          440  +
          441  +do_test analyze3-3.7.1 {
          442  +breakpoint
          443  +  set S [sqlite3_prepare_v2 db {
          444  +    SELECT * FROM t1 WHERE a IN (
          445  +      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?33,
          446  +      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
          447  +      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32
          448  +    ) AND b>?10;
          449  +  } -1 dummy]
          450  +  test_reoptimize $S
          451  +} {0}
          452  +do_test analyze3-3.7.2 {
          453  +  sqlite3_bind_text $S 32 "abc" -1
          454  +  test_reoptimize $S
          455  +} {0}
          456  +do_test analyze3-3.7.3 {
          457  +  sqlite3_bind_text $S 33 "def" -1
          458  +  test_reoptimize $S
          459  +} {0}
          460  +do_test analyze3-3.7.4 {
          461  +  sqlite3_bind_text $S 10 "def" -1
          462  +  test_reoptimize $S
          463  +} {1}
          464  +do_test analyze3-3.7.5 {
          465  +  test_reoptimize $S
          466  +} {0}
          467  +do_test analyze3-3.7.6 {
          468  +  sqlite3_finalize $S
          469  +} {SQLITE_OK}
          470  +
          471  +do_test analyze3-3.8.1 {
          472  +  execsql {
          473  +    CREATE TABLE t4(x, y TEXT COLLATE NOCASE);
          474  +    CREATE INDEX i4 ON t4(y);
          475  +  }
          476  +} {}
          477  +do_test analyze3-3.8.2 {
          478  +  set S [sqlite3_prepare_v2 db {
          479  +    SELECT * FROM t4 WHERE x != ? AND y LIKE ?
          480  +  } -1 dummy]
          481  +  test_reoptimize $S
          482  +} {0}
          483  +do_test analyze3-3.8.3 {
          484  +  sqlite3_bind_text $S 1 "abc" -1
          485  +  test_reoptimize $S
          486  +} {0}
          487  +do_test analyze3-3.8.4 {
          488  +  sqlite3_bind_text $S 2 "def" -1
          489  +  test_reoptimize $S
          490  +} {1}
          491  +do_test analyze3-3.8.5 {
          492  +  test_reoptimize $S
          493  +} {0}
          494  +do_test analyze3-3.8.6 {
          495  +  sqlite3_expired $S
          496  +} {0}
          497  +do_test analyze3-3.8.7 {
          498  +  sqlite3_bind_text $S 2 "ghi%" -1
          499  +  sqlite3_expired $S
          500  +} {0}
          501  +do_test analyze3-3.8.8 {
          502  +  test_reoptimize $S
          503  +} {1}
          504  +do_test analyze3-3.8.9 {
          505  +  sqlite3_bind_text $S 2 "ghi%def" -1
          506  +  sqlite3_expired $S
          507  +} {1}
          508  +do_test analyze3-3.8.10 {
          509  +  test_reoptimize $S
          510  +} {1}
          511  +do_test analyze3-3.8.11 {
          512  +  sqlite3_bind_text $S 2 "%ab" -1
          513  +  sqlite3_expired $S
          514  +} {1}
          515  +do_test analyze3-3.8.12 {
          516  +  test_reoptimize $S
          517  +} {1}
          518  +do_test analyze3-3.8.12 {
          519  +  sqlite3_bind_text $S 2 "%de" -1
          520  +  sqlite3_expired $S
          521  +} {0}
          522  +do_test analyze3-3.8.13 {
          523  +  test_reoptimize $S
          524  +} {1}
          525  +do_test analyze3-3.8.14 {
          526  +  sqlite3_finalize $S
          527  +} {SQLITE_OK}
          528  +
          529  +#-------------------------------------------------------------------------
          530  +# These tests check that errors encountered while repreparing an SQL
          531  +# statement within sqlite3_reoptimize() are handled correctly.
          532  +#
          533  +
          534  +# Check an schema error.
          535  +#
          536  +do_test analyze3-4.1.1 {
          537  +  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
          538  +  sqlite3_reoptimize $S
          539  +} {SQLITE_OK}
          540  +do_test analyze3-4.1.2 {
          541  +  sqlite3_bind_text $S 2 "abc" -1
          542  +  execsql { DROP TABLE t1 }
          543  +  sqlite3_reoptimize $S
          544  +} {SQLITE_SCHEMA}
          545  +do_test analyze3-4.1.3 {
          546  +  sqlite3_step $S
          547  +} {SQLITE_SCHEMA}
          548  +do_test analyze3-4.1.4 {
          549  +  sqlite3_finalize $S
          550  +} {SQLITE_SCHEMA}
          551  +
          552  +# Check an authorization error.
          553  +#
          554  +do_test analyze3-4.2.1 {
          555  +  execsql {
          556  +    BEGIN;
          557  +    CREATE TABLE t1(a, b, c);
          558  +    CREATE INDEX i1 ON t1(b);
          559  +  }
          560  +  for {set i 0} {$i < 100} {incr i} {
          561  +    execsql { INSERT INTO t1 VALUES($i, $i, $i) }
          562  +  }
          563  +  execsql COMMIT
          564  +  execsql ANALYZE
          565  +  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
          566  +  sqlite3_reoptimize $S
          567  +} {SQLITE_OK}
          568  +db auth auth
          569  +proc auth {args} {
          570  +  if {[lindex $args 0] == "SQLITE_READ"} {return SQLITE_DENY}
          571  +  return SQLITE_OK
          572  +}
          573  +do_test analyze3-4.2.2 {
          574  +  sqlite3_bind_text $S 2 "abc" -1
          575  +  sqlite3_reoptimize $S
          576  +} {SQLITE_SCHEMA}
          577  +do_test analyze3-4.2.3 {
          578  +  sqlite3_step $S
          579  +} {SQLITE_SCHEMA}
          580  +do_test analyze3-4.2.4 {
          581  +  sqlite3_finalize $S
          582  +} {SQLITE_SCHEMA}
          583  +
          584  +# Check the effect of an authorization error that occurs in a re-prepare
          585  +# performed by sqlite3_step() is the same as one that occurs within
          586  +# sqlite3_reoptimize().
          587  +#
          588  +do_test analyze3-4.3.1 {
          589  +  db auth {}
          590  +  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
          591  +  execsql { CREATE TABLE t2(d, e, f) }
          592  +  db auth auth
          593  +  sqlite3_step $S
          594  +} {SQLITE_SCHEMA}
          595  +do_test analyze3-4.3.2 {
          596  +  sqlite3_finalize $S
          597  +} {SQLITE_SCHEMA}
          598  +
          599  +finish_test