** The [sqlite3_strnicmp()] API allows applications and extensions to
** compare the contents of two buffers containing UTF-8 strings in a
** case-indendent fashion, using the same definition of case independence 
** that SQLite uses internally when comparing identifiers.
*/
int sqlite3_strnicmp(const char *, const char *, int);

/*
** CAPI3REF: Optimizing for Bound Parameters
** EXPERIMENTAL
**
** If possible, optimize the SQL statement passed as the only argument
** for the values currently bound to the statements SQL variables.
**
** SQLite currently contains two optimizations that depend upon the values 
** of SQL literals specified as part of a query:
**
** <ul>
**   <li> The [range query optimization], and 
**   <li> The [LIKE optimization].
** </ul>
**
** Normally, queries that use an SQL [parameter] in the relevant expression 
** or expressions may not benefit from either of the above optimizations.
** This function may be used to overcome this limitation.
**
** Calling sqlite3_reoptimize() on a statement handle after values have been
** bound to its SQL parameters causes SQLite to inspect the query and the
** bound values to determine if either of the above optimizations may be
** applied to it. If so, a new query plan is formulated to take advantage
** of the optimizations.
**
** If it is determined that neither of the above optimizations may be
** profitably applied to the query based on the values bound to SQL 
** parameters, this function is a no-op.
*/
int sqlite3_reoptimize(sqlite3_stmt *pStmt);

/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif

** The [sqlite3_strnicmp()] API allows applications and extensions to
** compare the contents of two buffers containing UTF-8 strings in a
** case-indendent fashion, using the same definition of case independence 
** that SQLite uses internally when comparing identifiers.
*/
int sqlite3_strnicmp(const char *, const char *, int);
































/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif

        sqlite3_bind_null(pStmt, i);
      }
    }
  }
  pPreStmt->nParm = iParm;
  *ppPreStmt = pPreStmt;

  /* Call sqlite3_reoptimize() to optimize the statement according to
  ** the values just bound to it. If SQLITE_ENABLE_STAT2 is not defined
  ** or the statement will not benefit from re-optimization, this 
  ** call is a no-op.  */
  if( SQLITE_OK!=sqlite3_reoptimize(pPreStmt->pStmt) ){
    Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db)));
    return TCL_ERROR;
  }

  return TCL_OK;
}


/*
** Release a statement reference obtained by calling dbPrepareAndBind().
** There should be exactly one call to this function for each call to

        sqlite3_bind_null(pStmt, i);
      }
    }
  }
  pPreStmt->nParm = iParm;
  *ppPreStmt = pPreStmt;










  return TCL_OK;
}


/*
** Release a statement reference obtained by calling dbPrepareAndBind().
** There should be exactly one call to this function for each call to

  }
  if( Tcl_GetBooleanFromObj(interp, objv[3], &resetFlag) ) return TCL_ERROR;
  iValue = sqlite3_stmt_status(pStmt, op, resetFlag);
  Tcl_SetObjResult(interp, Tcl_NewIntObj(iValue));
  return TCL_OK;
}

/*
** Usage:  sqlite3_reoptimize  STMT
**
** Call sqlite3_reoptimize() on the statement handle passed as the
** only parameter. Return a string representing the value returned by
** sqlite3_reoptimize - "SQLITE_OK", "SQLITE_MISUSE" etc.
*/
static int test_reoptimize(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  sqlite3_stmt *pStmt;
  int rc;

  if( objc!=2 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", 
       Tcl_GetString(objv[0]), " STMT", 0);
    return TCL_ERROR;
  }
  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  rc = sqlite3_reoptimize(pStmt);
  Tcl_ResetResult(interp);
  Tcl_SetResult(interp, (char *)t1ErrorName(rc), 0);
  return TCL_OK;
}

/*
** Usage:  sqlite3_next_stmt  DB  STMT
**
** Return the next statment in sequence after STMT.
*/
static int test_next_stmt(
  void * clientData,
................................................................................
     { "sqlite3_reset",                 test_reset         ,0 },
     { "sqlite3_expired",               test_expired       ,0 },
     { "sqlite3_transfer_bindings",     test_transfer_bind ,0 },
     { "sqlite3_changes",               test_changes       ,0 },
     { "sqlite3_step",                  test_step          ,0 },
     { "sqlite3_sql",                   test_sql           ,0 },
     { "sqlite3_next_stmt",             test_next_stmt     ,0 },
     { "sqlite3_reoptimize",            test_reoptimize    ,0 },

     { "sqlite3_release_memory",        test_release_memory,     0},
     { "sqlite3_soft_heap_limit",       test_soft_heap_limit,    0},
     { "sqlite3_thread_cleanup",        test_thread_cleanup,     0},
     { "sqlite3_pager_refcounts",       test_pager_refcounts,    0},

     { "sqlite3_load_extension",        test_load_extension,     0},

  }
  if( Tcl_GetBooleanFromObj(interp, objv[3], &resetFlag) ) return TCL_ERROR;
  iValue = sqlite3_stmt_status(pStmt, op, resetFlag);
  Tcl_SetObjResult(interp, Tcl_NewIntObj(iValue));
  return TCL_OK;
}





























/*
** Usage:  sqlite3_next_stmt  DB  STMT
**
** Return the next statment in sequence after STMT.
*/
static int test_next_stmt(
  void * clientData,
................................................................................
     { "sqlite3_reset",                 test_reset         ,0 },
     { "sqlite3_expired",               test_expired       ,0 },
     { "sqlite3_transfer_bindings",     test_transfer_bind ,0 },
     { "sqlite3_changes",               test_changes       ,0 },
     { "sqlite3_step",                  test_step          ,0 },
     { "sqlite3_sql",                   test_sql           ,0 },
     { "sqlite3_next_stmt",             test_next_stmt     ,0 },


     { "sqlite3_release_memory",        test_release_memory,     0},
     { "sqlite3_soft_heap_limit",       test_soft_heap_limit,    0},
     { "sqlite3_thread_cleanup",        test_thread_cleanup,     0},
     { "sqlite3_pager_refcounts",       test_pager_refcounts,    0},

     { "sqlite3_load_extension",        test_load_extension,     0},

void sqlite3VdbeCountChanges(Vdbe*);
sqlite3 *sqlite3VdbeDb(Vdbe*);
void sqlite3VdbeSetSql(Vdbe*, const char *z, int n, int);
void sqlite3VdbeSwap(Vdbe*,Vdbe*);
VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
void sqlite3VdbeProgramDelete(sqlite3 *, SubProgram *, int);
sqlite3_value *sqlite3VdbeGetValue(Vdbe*, int, u8);
void sqlite3VdbeSetVarmask(Vdbe*,int,int);

#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
int sqlite3VdbeReleaseMemory(int);
#endif
UnpackedRecord *sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,char*,int);
void sqlite3VdbeDeleteUnpackedRecord(UnpackedRecord*);
int sqlite3VdbeRecordCompare(int,const void*,UnpackedRecord*);

void sqlite3VdbeCountChanges(Vdbe*);
sqlite3 *sqlite3VdbeDb(Vdbe*);
void sqlite3VdbeSetSql(Vdbe*, const char *z, int n, int);
void sqlite3VdbeSwap(Vdbe*,Vdbe*);
VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
void sqlite3VdbeProgramDelete(sqlite3 *, SubProgram *, int);
sqlite3_value *sqlite3VdbeGetValue(Vdbe*, int, u8);
void sqlite3VdbeSetVarmask(Vdbe*, int);

#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
int sqlite3VdbeReleaseMemory(int);
#endif
UnpackedRecord *sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,char*,int);
void sqlite3VdbeDeleteUnpackedRecord(UnpackedRecord*);
int sqlite3VdbeRecordCompare(int,const void*,UnpackedRecord*);

  i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
  int iStatement;         /* Statement number (or 0 if has not opened stmt) */
#ifdef SQLITE_DEBUG
  FILE *trace;            /* Write an execution trace here, if not NULL */
#endif
  VdbeFrame *pFrame;      /* Parent frame */
  int nFrame;             /* Number of frames in pFrame list */
  u8 optimizable;         /* True if VM may benefit from sqlite3_reoptimize() */
  u32 optmask;            /* Bitmask of vars that may be used by reoptimize() */
  u32 expmask;            /* Binding to these vars invalidates VM */
};

/*
** The following are allowed values for Vdbe.magic
*/
#define VDBE_MAGIC_INIT     0x26bceaa5    /* Building a VDBE program */

  i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
  int iStatement;         /* Statement number (or 0 if has not opened stmt) */
#ifdef SQLITE_DEBUG
  FILE *trace;            /* Write an execution trace here, if not NULL */
#endif
  VdbeFrame *pFrame;      /* Parent frame */
  int nFrame;             /* Number of frames in pFrame list */


  u32 expmask;            /* Binding to these vars invalidates VM */
};

/*
** The following are allowed values for Vdbe.magic
*/
#define VDBE_MAGIC_INIT     0x26bceaa5    /* Building a VDBE program */

  }
  i--;
  pVar = &p->aVar[i];
  sqlite3VdbeMemRelease(pVar);
  pVar->flags = MEM_Null;
  sqlite3Error(p->db, SQLITE_OK, 0);

  /* If the bit corresponding to this variable is set in Vdbe.opmask, set 
  ** the optimizable flag before returning. This tells the sqlite3_reoptimize()
  ** function that the VM program may benefit from recompilation. 
  **
  ** If the bit in Vdbe.expmask is set, then binding a new value to this
  ** variable invalidates the current query plan. This comes about when the
  ** variable is the RHS of a LIKE or GLOB operator and the LIKE/GLOB is
  ** able to use an index.  */
  if( (i<32 && p->optmask & ((u32)1 << i)) || p->optmask==0xffffffff ){
    p->optimizable = 1;
  }


  if( (i<32 && p->expmask & ((u32)1 << i)) || p->expmask==0xffffffff ){
    p->expired = 1;
  }
  return SQLITE_OK;
}

/*
................................................................................
int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
  Vdbe *pVdbe = (Vdbe*)pStmt;
  int v = pVdbe->aCounter[op-1];
  if( resetFlag ) pVdbe->aCounter[op-1] = 0;
  return v;
}

/*
** If possible, optimize the statement for the current bindings.
*/
int sqlite3_reoptimize(sqlite3_stmt *pStmt){
  int rc = SQLITE_OK;
  Vdbe *v = (Vdbe *)pStmt;
  sqlite3 *db = v->db;

  sqlite3_mutex_enter(db->mutex);
  if( v->isPrepareV2==0 || v->pc>0 ){
    rc = SQLITE_MISUSE;
  }else if( v->optimizable ){
    rc = sqlite3Reprepare(v);
    rc = sqlite3ApiExit(db, rc);
  }
  assert( rc!=SQLITE_OK || v->optimizable==0 );
  sqlite3_mutex_leave(db->mutex);

  return rc;
}

  }
  i--;
  pVar = &p->aVar[i];
  sqlite3VdbeMemRelease(pVar);
  pVar->flags = MEM_Null;
  sqlite3Error(p->db, SQLITE_OK, 0);

  /* If the bit corresponding to this variable in Vdbe.expmask is set, then 




  ** binding a new value to this variable invalidates the current query plan.





  */
  assert( p->isPrepareV2 || p->expmask==0 );
  if( (i<32 && p->expmask & ((u32)1 << i)) || p->expmask==0xffffffff ){
    p->expired = 1;
  }
  return SQLITE_OK;
}

/*
................................................................................
int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
  Vdbe *pVdbe = (Vdbe*)pStmt;
  int v = pVdbe->aCounter[op-1];
  if( resetFlag ) pVdbe->aCounter[op-1] = 0;
  return v;
}

  return p;
}

/*
** Remember the SQL string for a prepared statement.
*/
void sqlite3VdbeSetSql(Vdbe *p, const char *z, int n, int isPrepareV2){

  if( p==0 ) return;
#ifdef SQLITE_OMIT_TRACE
  if( !isPrepareV2 ) return;
#endif
  assert( p->zSql==0 );
  p->zSql = sqlite3DbStrNDup(p->db, z, n);
  p->isPrepareV2 = isPrepareV2 ? 1 : 0;
}

/*
** Return the SQL associated with a prepared statement
*/
const char *sqlite3_sql(sqlite3_stmt *pStmt){
  Vdbe *p = (Vdbe *)pStmt;
................................................................................
}

/*
** Configure SQL variable iVar so that binding a new value to it signals
** to sqlite3_reoptimize() that re-preparing the statement may result
** in a better query plan.
*/
void sqlite3VdbeSetVarmask(Vdbe *v, int iVar, int isExpire){
  u32 *mask = (isExpire ? &v->expmask : &v->optmask);
  assert( iVar>0 );
  if( iVar>32 ){
    *mask = 0xffffffff;
  }else{
    *mask |= ((u32)1 << (iVar-1));
  }
}

  return p;
}

/*
** Remember the SQL string for a prepared statement.
*/
void sqlite3VdbeSetSql(Vdbe *p, const char *z, int n, int isPrepareV2){
  assert( isPrepareV2==1 || isPrepareV2==0 );
  if( p==0 ) return;
#ifdef SQLITE_OMIT_TRACE
  if( !isPrepareV2 ) return;
#endif
  assert( p->zSql==0 );
  p->zSql = sqlite3DbStrNDup(p->db, z, n);
  p->isPrepareV2 = isPrepareV2;
}

/*
** Return the SQL associated with a prepared statement
*/
const char *sqlite3_sql(sqlite3_stmt *pStmt){
  Vdbe *p = (Vdbe *)pStmt;
................................................................................
}

/*
** Configure SQL variable iVar so that binding a new value to it signals
** to sqlite3_reoptimize() that re-preparing the statement may result
** in a better query plan.
*/
void sqlite3VdbeSetVarmask(Vdbe *v, int iVar){

  assert( iVar>0 );
  if( iVar>32 ){
    v->expmask = 0xffffffff;
  }else{
    v->expmask |= ((u32)1 << (iVar-1));
  }
}

  }
  if( op==TK_VARIABLE ){
    Vdbe *pReprepare = pParse->pReprepare;
    pVal = sqlite3VdbeGetValue(pReprepare, pRight->iColumn, SQLITE_AFF_NONE);
    if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
      z = (char *)sqlite3_value_text(pVal);
    }
    sqlite3VdbeSetVarmask(pParse->pVdbe, pRight->iColumn, 0);
    assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
  }else if( op==TK_STRING ){
    z = pRight->u.zToken;
  }
  if( z ){
    cnt = 0;
    while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
................................................................................
      Expr *pPrefix;
      *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;
      pPrefix = sqlite3Expr(db, TK_STRING, z);
      if( pPrefix ) pPrefix->u.zToken[cnt] = 0;
      *ppPrefix = pPrefix;
      if( op==TK_VARIABLE ){
        Vdbe *v = pParse->pVdbe;
        sqlite3VdbeSetVarmask(v, pRight->iColumn, 1);
        if( *pisComplete && pRight->u.zToken[1] ){
          /* If the rhs of the LIKE expression is a variable, and the current
          ** value of the variable means there is no need to invoke the LIKE
          ** function, then no OP_Variable will be added to the program.
          ** This causes problems for the sqlite3_bind_parameter_name()
          ** API. To workaround them, add a dummy OP_Variable here.
          */ 
................................................................................
  u8 aff, 
  sqlite3_value **pp
){
  if( (pExpr->op==TK_VARIABLE)
   || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
  ){
    int iVar = pExpr->iColumn;
    sqlite3VdbeSetVarmask(pParse->pVdbe, iVar, 0);
    *pp = sqlite3VdbeGetValue(pParse->pReprepare, iVar, aff);
    return SQLITE_OK;
  }
  return sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, aff, pp);
}
#endif

  }
  if( op==TK_VARIABLE ){
    Vdbe *pReprepare = pParse->pReprepare;
    pVal = sqlite3VdbeGetValue(pReprepare, pRight->iColumn, SQLITE_AFF_NONE);
    if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
      z = (char *)sqlite3_value_text(pVal);
    }
    sqlite3VdbeSetVarmask(pParse->pVdbe, pRight->iColumn);
    assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
  }else if( op==TK_STRING ){
    z = pRight->u.zToken;
  }
  if( z ){
    cnt = 0;
    while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
................................................................................
      Expr *pPrefix;
      *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;
      pPrefix = sqlite3Expr(db, TK_STRING, z);
      if( pPrefix ) pPrefix->u.zToken[cnt] = 0;
      *ppPrefix = pPrefix;
      if( op==TK_VARIABLE ){
        Vdbe *v = pParse->pVdbe;
        sqlite3VdbeSetVarmask(v, pRight->iColumn);
        if( *pisComplete && pRight->u.zToken[1] ){
          /* If the rhs of the LIKE expression is a variable, and the current
          ** value of the variable means there is no need to invoke the LIKE
          ** function, then no OP_Variable will be added to the program.
          ** This causes problems for the sqlite3_bind_parameter_name()
          ** API. To workaround them, add a dummy OP_Variable here.
          */ 
................................................................................
  u8 aff, 
  sqlite3_value **pp
){
  if( (pExpr->op==TK_VARIABLE)
   || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
  ){
    int iVar = pExpr->iColumn;
    sqlite3VdbeSetVarmask(pParse->pVdbe, iVar);
    *pp = sqlite3VdbeGetValue(pParse->pReprepare, iVar, aff);
    return SQLITE_OK;
  }
  return sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, aff, pp);
}
#endif

#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements regression tests for SQLite library. This file 
# implements tests for the sqlite3_reoptimize() functionality.

#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !stat2 {
  finish_test
................................................................................
#               in the same way as constants when planning queries that
#               use range constraints.
#
# analyze3-2.*: Test that the values of bound parameters are considered 
#               in the same way as constants when planning queries that
#               use LIKE expressions in the WHERE clause.
#
# analyze3-3.*: Test that sqlite3_reoptimize() is a no-op when there is
#               no way for re-preparing the query to produce a superior 
#               query plan.
#
# analyze3-4.*: Test that SQL or authorization callback errors occuring
#               within sqlite3_reoptimize() are handled correctly.
#

proc getvar {varname} { uplevel #0 set $varname }
db function var getvar

proc eqp {sql {db db}} {
  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
................................................................................
#   Show that there are two possible plans for querying the table with
#   a range constraint on the indexed column - "full table scan" or "use 
#   the index". When the range is specified using literal values, SQLite
#   is able to pick the best plan based on the samples in sqlite_stat2.
#
# analyze3-1.1.4 - 3.1.9
#   Show that using SQL variables produces the same results as using
#   literal values to constrain the range scan. This works because the
#   Tcl interface always calls [sqlite3_reoptimize] after binding values.
#
#   These tests also check that the compiler code considers column 
#   affinities when estimating the number of rows scanned by the "use 
#   index strategy".
#
do_test analyze3-1.1.1 {
  execsql {
................................................................................
do_test analyze3-2.5 {
  set like "%a"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {999 999 100}


#-------------------------------------------------------------------------
# This block of tests checks that sqlite3_reoptimize() is a no-op if 
# the values bound to any parameters that may affect the query plan
# have not changed since the statement was last compiled.
#
# It is possible to tell if sqlite3_reoptimize() is a no-op by registering
# an authorization callback. If the auth callback is not invoked from
# within a give call to reoptimize(), then it must have been a no-op.
#
# Also test that:
#
#   * sqlite3_reoptimize() returns SQLITE_MISUSE if called on a statement
#     that was prepared using the legacy sqlite3_prepare() interface,
#
#   * sqlite3_reoptimize() returns SQLITE_MISUSE if called on a statement
#     that is not in the "reset" state.
#
drop_all_tables
db auth auth
proc auth {args} {
  set ::auth 1
  return SQLITE_OK
}

# Return true if calling reoptimize() on the statement handle passed 
# as an argument causes the statement to be recompiled.
#
proc test_reoptimize {stmt} {
  set ::auth 0
  sqlite3_reoptimize $stmt
  set ::auth
}

do_test analyze3-3.1 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(b);
  }
  for {set i 0} {$i < 100} {incr i} {
................................................................................
  }
  execsql COMMIT
  execsql ANALYZE
} {}

do_test analyze3-3.2.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy]
  test_reoptimize $S
} {0}
do_test analyze3-3.2.2 {
  sqlite3_bind_text $S 1 "abc" 3
  test_reoptimize $S
} {1}
do_test analyze3-3.2.3 {
  test_reoptimize $S
} {0}
do_test analyze3-3.2.4 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.2.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy]
  test_reoptimize $S
} {0}
do_test analyze3-3.2.2 {
  sqlite3_bind_text $S 1 "abc" 3
  test_reoptimize $S
} {0}
do_test analyze3-3.2.3 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.3.1 {
  set S [sqlite3_prepare db "SELECT * FROM t1 WHERE b=?" -1 dummy]
  sqlite3_reoptimize $S
} {SQLITE_MISUSE}
do_test analyze3-3.3.2 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.3.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1" -1 dummy]
  sqlite3_reoptimize $S
} {SQLITE_OK}
do_test analyze3-3.3.2 {
  sqlite3_step $S
} {SQLITE_ROW}
do_test analyze3-3.3.3 {
  sqlite3_reoptimize $S
} {SQLITE_MISUSE}
do_test analyze3-3.3.4 {
  while {"SQLITE_ROW" == [sqlite3_step $S]} {}
  sqlite3_reoptimize $S
} {SQLITE_MISUSE}
do_test analyze3-3.3.5 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.4.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
  test_reoptimize $S
} {0}
do_test analyze3-3.4.2 {
  sqlite3_bind_text $S 1 "abc" 3
  test_reoptimize $S
} {0}
do_test analyze3-3.4.3 {
  sqlite3_bind_text $S 2 "def" 3
  test_reoptimize $S
} {1}
do_test analyze3-3.4.4 {
  sqlite3_bind_text $S 2 "ghi" 3
  test_reoptimize $S
} {1}
do_test analyze3-3.4.5 {
  test_reoptimize $S
} {0}
do_test analyze3-3.4.6 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.5.1 {
  set S [sqlite3_prepare_v2 db {
    SELECT * FROM t1 WHERE a IN (
      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,
      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31
    ) AND b>?32;
  } -1 dummy]
  test_reoptimize $S
} {0}
do_test analyze3-3.5.2 {
  sqlite3_bind_text $S 31 "abc" 3
  test_reoptimize $S
} {0}
do_test analyze3-3.5.3 {
  sqlite3_bind_text $S 32 "def" 3
  test_reoptimize $S
} {1}
do_test analyze3-3.5.4 {
  test_reoptimize $S
} {0}
do_test analyze3-3.5.5 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.6.1 {
  set S [sqlite3_prepare_v2 db {
    SELECT * FROM t1 WHERE a IN (
      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,
      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32
    ) AND b>?33;
  } -1 dummy]
  test_reoptimize $S
} {0}
do_test analyze3-3.6.2 {
  sqlite3_bind_text $S 32 "abc" 3
  test_reoptimize $S
} {1}
do_test analyze3-3.6.3 {
  sqlite3_bind_text $S 33 "def" 3
  test_reoptimize $S
} {1}
do_test analyze3-3.6.4 {
  test_reoptimize $S
} {0}
do_test analyze3-3.6.5 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.7.1 {
breakpoint
  set S [sqlite3_prepare_v2 db {
    SELECT * FROM t1 WHERE a IN (
      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?33,
      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32
    ) AND b>?10;
  } -1 dummy]
  test_reoptimize $S
} {0}
do_test analyze3-3.7.2 {
  sqlite3_bind_text $S 32 "abc" 3
  test_reoptimize $S
} {0}
do_test analyze3-3.7.3 {
  sqlite3_bind_text $S 33 "def" 3
  test_reoptimize $S
} {0}
do_test analyze3-3.7.4 {
  sqlite3_bind_text $S 10 "def" 3
  test_reoptimize $S
} {1}
do_test analyze3-3.7.5 {
  test_reoptimize $S
} {0}
do_test analyze3-3.7.6 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.8.1 {
  execsql {
    CREATE TABLE t4(x, y TEXT COLLATE NOCASE);
................................................................................
    CREATE INDEX i4 ON t4(y);
  }
} {}
do_test analyze3-3.8.2 {
  set S [sqlite3_prepare_v2 db {
    SELECT * FROM t4 WHERE x != ? AND y LIKE ?
  } -1 dummy]
  test_reoptimize $S
} {0}
do_test analyze3-3.8.3 {
  sqlite3_bind_text $S 1 "abc" 3
  test_reoptimize $S
} {0}
do_test analyze3-3.8.4 {
  sqlite3_bind_text $S 2 "def" 3
  test_reoptimize $S
} {1}
do_test analyze3-3.8.5 {
  test_reoptimize $S
} {0}
do_test analyze3-3.8.6 {
  sqlite3_expired $S
} {0}
do_test analyze3-3.8.7 {
  sqlite3_bind_text $S 2 "ghi%" 4
  sqlite3_expired $S
} {0}
do_test analyze3-3.8.8 {
  test_reoptimize $S
} {1}
do_test analyze3-3.8.9 {
  sqlite3_bind_text $S 2 "ghi%def" 7
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.10 {
  test_reoptimize $S
} {1}
do_test analyze3-3.8.11 {
  sqlite3_bind_text $S 2 "%ab" 3
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.12 {
  test_reoptimize $S
} {1}
do_test analyze3-3.8.12 {
  sqlite3_bind_text $S 2 "%de" 3
  sqlite3_expired $S
} {0}
do_test analyze3-3.8.13 {
  test_reoptimize $S
} {1}
do_test analyze3-3.8.14 {
  sqlite3_finalize $S
} {SQLITE_OK}

#-------------------------------------------------------------------------
# These tests check that errors encountered while repreparing an SQL
# statement within sqlite3_reoptimize() are handled correctly.
#

# Check an schema error.
#
do_test analyze3-4.1.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
  sqlite3_reoptimize $S
} {SQLITE_OK}
do_test analyze3-4.1.2 {

  sqlite3_bind_text $S 2 "abc" 3
  execsql { DROP TABLE t1 }
  sqlite3_reoptimize $S
} {SQLITE_SCHEMA}
do_test analyze3-4.1.3 {
  sqlite3_step $S
} {SQLITE_SCHEMA}
do_test analyze3-4.1.4 {
  sqlite3_finalize $S
} {SQLITE_SCHEMA}

# Check an authorization error.
#
do_test analyze3-4.2.1 {
  execsql {
................................................................................
  }
  for {set i 0} {$i < 100} {incr i} {
    execsql { INSERT INTO t1 VALUES($i, $i, $i) }
  }
  execsql COMMIT
  execsql ANALYZE
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
  sqlite3_reoptimize $S
} {SQLITE_OK}
db auth auth
proc auth {args} {
  if {[lindex $args 0] == "SQLITE_READ"} {return SQLITE_DENY}
  return SQLITE_OK
}
do_test analyze3-4.2.2 {

  sqlite3_bind_text $S 2 "abc" 3
  sqlite3_reoptimize $S
} {SQLITE_SCHEMA}
do_test analyze3-4.2.3 {
  sqlite3_step $S
} {SQLITE_SCHEMA}
do_test analyze3-4.2.4 {
  sqlite3_finalize $S
} {SQLITE_SCHEMA}

# Check the effect of an authorization error that occurs in a re-prepare
# performed by sqlite3_step() is the same as one that occurs within
# sqlite3_reoptimize().
#
do_test analyze3-4.3.1 {
  db auth {}
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
  execsql { CREATE TABLE t2(d, e, f) }
  db auth auth
  sqlite3_step $S
} {SQLITE_SCHEMA}
do_test analyze3-4.3.2 {
  sqlite3_finalize $S
} {SQLITE_SCHEMA}

finish_test

#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements regression tests for SQLite library. This file 
# implements tests for range and LIKE constraints that use bound variables
# instead of literal constant arguments.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !stat2 {
  finish_test
................................................................................
#               in the same way as constants when planning queries that
#               use range constraints.
#
# analyze3-2.*: Test that the values of bound parameters are considered 
#               in the same way as constants when planning queries that
#               use LIKE expressions in the WHERE clause.
#
# analyze3-3.*: Test that binding to a variable does not invalidate the 
#               query plan when there is no way in which replanning the
#               query may produce a superior outcome.
#
# analyze3-4.*: Test that SQL or authorization callback errors occuring
#               within sqlite3Reprepare() are handled correctly.
#

proc getvar {varname} { uplevel #0 set $varname }
db function var getvar

proc eqp {sql {db db}} {
  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
................................................................................
#   Show that there are two possible plans for querying the table with
#   a range constraint on the indexed column - "full table scan" or "use 
#   the index". When the range is specified using literal values, SQLite
#   is able to pick the best plan based on the samples in sqlite_stat2.
#
# analyze3-1.1.4 - 3.1.9
#   Show that using SQL variables produces the same results as using
#   literal values to constrain the range scan.

#
#   These tests also check that the compiler code considers column 
#   affinities when estimating the number of rows scanned by the "use 
#   index strategy".
#
do_test analyze3-1.1.1 {
  execsql {
................................................................................
do_test analyze3-2.5 {
  set like "%a"
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {999 999 100}


#-------------------------------------------------------------------------
# This block of tests checks that statements are correctly marked as
# expired when the values bound to any parameters that may affect the 
# query plan are modified.












#
drop_all_tables
db auth auth
proc auth {args} {
  set ::auth 1
  return SQLITE_OK
}










do_test analyze3-3.1 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(b);
  }
  for {set i 0} {$i < 100} {incr i} {
................................................................................
  }
  execsql COMMIT
  execsql ANALYZE
} {}

do_test analyze3-3.2.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy]
  sqlite3_expired $S
} {0}
do_test analyze3-3.2.2 {
  sqlite3_bind_text $S 1 "abc" 3
  sqlite3_expired $S
} {1}



do_test analyze3-3.2.4 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.2.5 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b=?" -1 dummy]
  sqlite3_expired $S
} {0}
do_test analyze3-3.2.6 {
  sqlite3_bind_text $S 1 "abc" 3
  sqlite3_expired $S
} {0}
do_test analyze3-3.2.7 {


























  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.4.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
  sqlite3_expired $S
} {0}
do_test analyze3-3.4.2 {
  sqlite3_bind_text $S 1 "abc" 3
  sqlite3_expired $S
} {0}
do_test analyze3-3.4.3 {
  sqlite3_bind_text $S 2 "def" 3
  sqlite3_expired $S
} {1}
do_test analyze3-3.4.4 {
  sqlite3_bind_text $S 2 "ghi" 3
  sqlite3_expired $S
} {1}
do_test analyze3-3.4.5 {
  sqlite3_expired $S
} {1}
do_test analyze3-3.4.6 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.5.1 {
  set S [sqlite3_prepare_v2 db {
    SELECT * FROM t1 WHERE a IN (
      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,
      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31
    ) AND b>?32;
  } -1 dummy]
  sqlite3_expired $S
} {0}
do_test analyze3-3.5.2 {
  sqlite3_bind_text $S 31 "abc" 3
  sqlite3_expired $S
} {0}
do_test analyze3-3.5.3 {
  sqlite3_bind_text $S 32 "def" 3
  sqlite3_expired $S
} {1}



do_test analyze3-3.5.5 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.6.1 {
  set S [sqlite3_prepare_v2 db {
    SELECT * FROM t1 WHERE a IN (
      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10,
      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32
    ) AND b>?33;
  } -1 dummy]
  sqlite3_expired $S
} {0}
do_test analyze3-3.6.2 {
  sqlite3_bind_text $S 32 "abc" 3
  sqlite3_expired $S
} {1}
do_test analyze3-3.6.3 {
  sqlite3_bind_text $S 33 "def" 3
  sqlite3_expired $S
} {1}



do_test analyze3-3.6.5 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.7.1 {
breakpoint
  set S [sqlite3_prepare_v2 db {
    SELECT * FROM t1 WHERE a IN (
      ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?33,
      ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20,
      ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32
    ) AND b>?10;
  } -1 dummy]
  sqlite3_expired $S
} {0}
do_test analyze3-3.7.2 {
  sqlite3_bind_text $S 32 "abc" 3
  sqlite3_expired $S
} {0}
do_test analyze3-3.7.3 {
  sqlite3_bind_text $S 33 "def" 3
  sqlite3_expired $S
} {0}
do_test analyze3-3.7.4 {
  sqlite3_bind_text $S 10 "def" 3
  sqlite3_expired $S
} {1}



do_test analyze3-3.7.6 {
  sqlite3_finalize $S
} {SQLITE_OK}

do_test analyze3-3.8.1 {
  execsql {
    CREATE TABLE t4(x, y TEXT COLLATE NOCASE);
................................................................................
    CREATE INDEX i4 ON t4(y);
  }
} {}
do_test analyze3-3.8.2 {
  set S [sqlite3_prepare_v2 db {
    SELECT * FROM t4 WHERE x != ? AND y LIKE ?
  } -1 dummy]
  sqlite3_expired $S
} {0}
do_test analyze3-3.8.3 {
  sqlite3_bind_text $S 1 "abc" 3
  sqlite3_expired $S
} {0}
do_test analyze3-3.8.4 {
  sqlite3_bind_text $S 2 "def" 3






  sqlite3_expired $S
} {1}
do_test analyze3-3.8.7 {
  sqlite3_bind_text $S 2 "ghi%" 4
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.8 {
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.9 {
  sqlite3_bind_text $S 2 "ghi%def" 7
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.10 {
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.11 {
  sqlite3_bind_text $S 2 "%ab" 3
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.12 {
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.12 {
  sqlite3_bind_text $S 2 "%de" 3
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.13 {
  sqlite3_expired $S
} {1}
do_test analyze3-3.8.14 {
  sqlite3_finalize $S
} {SQLITE_OK}

#-------------------------------------------------------------------------
# These tests check that errors encountered while repreparing an SQL
# statement within sqlite3Reprepare() are handled correctly.
#

# Check a schema error.
#
do_test analyze3-4.1.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
  sqlite3_step $S
} {SQLITE_DONE}
do_test analyze3-4.1.2 {
  sqlite3_reset $S
  sqlite3_bind_text $S 2 "abc" 3
  execsql { DROP TABLE t1 }



  sqlite3_step $S
} {SQLITE_SCHEMA}
do_test analyze3-4.1.3 {
  sqlite3_finalize $S
} {SQLITE_SCHEMA}

# Check an authorization error.
#
do_test analyze3-4.2.1 {
  execsql {
................................................................................
  }
  for {set i 0} {$i < 100} {incr i} {
    execsql { INSERT INTO t1 VALUES($i, $i, $i) }
  }
  execsql COMMIT
  execsql ANALYZE
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
  sqlite3_step $S
} {SQLITE_DONE}
db auth auth
proc auth {args} {
  if {[lindex $args 0] == "SQLITE_READ"} {return SQLITE_DENY}
  return SQLITE_OK
}
do_test analyze3-4.2.2 {
  sqlite3_reset $S
  sqlite3_bind_text $S 2 "abc" 3



  sqlite3_step $S
} {SQLITE_SCHEMA}
do_test analyze3-4.2.4 {
  sqlite3_finalize $S
} {SQLITE_SCHEMA}

# Check the effect of an authorization error that occurs in a re-prepare
# performed by sqlite3_step() is the same as one that occurs within
# sqlite3Reprepare().
#
do_test analyze3-4.3.1 {
  db auth {}
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE a=? AND b>?" -1 dummy]
  execsql { CREATE TABLE t2(d, e, f) }
  db auth auth
  sqlite3_step $S
} {SQLITE_SCHEMA}
do_test analyze3-4.3.2 {
  sqlite3_finalize $S
} {SQLITE_SCHEMA}

finish_test