SQLite

Check-in [2c50b3d5aa]
Login

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

Overview
Comment:Remove the sqlite3_reoptimize() API. The same functionality is now provided automatically to queries prepared using prepare_v2().
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2c50b3d5aab7cd8cc841d61f8c3b2b34d2f0b54b
User & Date: dan 2009-10-19 18:11:10.000
Context
2009-10-19
18:30
Fix an assert() that may fail if sqlite3_step() is called on a statement after a previous call has already returned SQLITE_SCHEMA. (check-in: 63bf73452d user: dan tags: trunk)
18:11
Remove the sqlite3_reoptimize() API. The same functionality is now provided automatically to queries prepared using prepare_v2(). (check-in: 2c50b3d5aa user: dan tags: trunk)
15:52
When generating WHERE clause terms internally for NATURAL and USING joins, identify the table by its position in the FROM list, not by its name or alias. Fix for [b73fb0bd64]. (check-in: 6fe6371175 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/sqlite.h.in.
5739
5740
5741
5742
5743
5744
5745
5746
5747
5748
5749
5750
5751
5752
5753
5754
5755
5756
5757
5758
5759
5760
5761
5762
5763
5764
5765
5766
5767
5768
5769
5770
5771
5772
5773
5774
5775
5776
5777
5778
5779
5780
5781
5782
5783
** 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







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







5739
5740
5741
5742
5743
5744
5745































5746
5747
5748
5749
5750
5751
5752
** 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
Changes to src/tclsqlite.c.
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
        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







<
<
<
<
<
<
<
<
<







1125
1126
1127
1128
1129
1130
1131









1132
1133
1134
1135
1136
1137
1138
        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
Changes to src/test1.c.
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
  }
  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,







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







2045
2046
2047
2048
2049
2050
2051




























2052
2053
2054
2055
2056
2057
2058
  }
  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,
5027
5028
5029
5030
5031
5032
5033
5034
5035
5036
5037
5038
5039
5040
5041
     { "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},







<







4999
5000
5001
5002
5003
5004
5005

5006
5007
5008
5009
5010
5011
5012
     { "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},
Changes to src/vdbe.h.
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
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*);







|







199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
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*);
Changes to src/vdbeInt.h.
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
  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 */







<
<







319
320
321
322
323
324
325


326
327
328
329
330
331
332
  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 */
Changes to src/vdbeapi.c.
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925

926
927
928
929
930
931
932
933
934
935
  }
  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;
}

/*







|
<
<
<
<
|
<
<
>
|
<
<







911
912
913
914
915
916
917
918




919


920
921


922
923
924
925
926
927
928
  }
  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;
}

/*
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
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;
}








<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
1210
1211
1212
1213
1214
1215
1216





















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;
}






















Changes to src/vdbeaux.c.
49
50
51
52
53
54
55

56
57
58
59
60
61
62
63
64
65
66
67
68
69
  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;







>






|







49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
  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;
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
3065
}

/*
** 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));
  }
}








|
<


|

|



3050
3051
3052
3053
3054
3055
3056
3057

3058
3059
3060
3061
3062
3063
3064
3065
}

/*
** 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));
  }
}

Changes to src/where.c.
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
  }
  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] ){
      cnt++;
    }
    if( cnt!=0 && c!=0 && 255!=(u8)z[cnt-1] ){
      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.
          */ 







|

















|







667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
  }
  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] ){
      cnt++;
    }
    if( cnt!=0 && c!=0 && 255!=(u8)z[cnt-1] ){
      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.
          */ 
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
  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








|







2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
  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

Changes to test/analyze3.test.
1
2
3
4
5
6
7
8
9
10
11
12
13

14
15
16
17
18
19
20
# 2009 August 06
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements 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












|
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2009 August 06
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#               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







|
|
|


|







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#               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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
#   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 {







|
<







66
67
68
69
70
71
72
73

74
75
76
77
78
79
80
#   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 {
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540

541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573

574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
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 { INSERT INTO t1 VALUES($i, $i, $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 {
    BEGIN;
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(b);
  }
  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







|
|
<
<
<
<
<
<
|
<
<
<
<
<
<








<
<
<
<
<
<
<
<
<















|



|

<
<
<




|

|

|

|

|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<





|



|



|



|


|
|












|



|



|

<
<
<












|



|



|

<
<
<













|



|



|



|

<
<
<














|



|



<
<
<
<
<
<

|



|

|






|






|




|

|







|


|



|
|

>


<
<
<


|

















|
|






>

<
<
<








|













263
264
265
266
267
268
269
270
271






272






273
274
275
276
277
278
279
280









281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301



302
303
304
305
306
307
308
309
310
311
312
313
314


























315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358



359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380



381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407



408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429






430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478



479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508



509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
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 { INSERT INTO t1 VALUES($i, $i, $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 {
    BEGIN;
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(b);
  }
  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