/ Check-in [ab3b0fc5]
Login

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

Overview
Comment:Add the experimental sqlite3_stmt_scanstatus() API. For comparing the number of rows actually visited by a loop with the estimate used by the query planner.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ab3b0fc5760c6d428dbe1f974726a7d3526640bc
User & Date: dan 2014-11-03 16:56:43
Context
2014-11-03
18:03
Updates to the sqlite3_stmt_scanstatus() documentation. No changes to code. check-in: d97c324e user: drh tags: trunk
16:56
Add the experimental sqlite3_stmt_scanstatus() API. For comparing the number of rows actually visited by a loop with the estimate used by the query planner. check-in: ab3b0fc5 user: dan tags: trunk
16:39
Fix a typo preventing this from building with SQLITE_ENABLE_STMT_SCANSTATUS defined. Closed-Leaf check-in: 4c5714ab user: dan tags: scanstatus
14:46
Use exponential buffer size growth in StrAccum, as long as the size does not grow to large, to avoid excess memory allocation resize operations. Also, document the fact that setting scratch memory causes SQLite to try to avoid large memory allocations. check-in: a518bc33 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

  7407   7407   */
  7408   7408   #define SQLITE_ROLLBACK 1
  7409   7409   /* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */
  7410   7410   #define SQLITE_FAIL     3
  7411   7411   /* #define SQLITE_ABORT 4  // Also an error code */
  7412   7412   #define SQLITE_REPLACE  5
  7413   7413   
         7414  +/* CAPI3REF: Prepared Statement Scan Status Opcodes
         7415  +** KEYWORDS: {scanstatus option}
         7416  +**
         7417  +** The following constants can be used for the T parameter to the
         7418  +** [sqlite3_stmt_scanstatus(S,X,T,V)] interface.  Each constant designates a
         7419  +** different metric for sqlite3_stmt_scanstatus() to return.
         7420  +**
         7421  +** <dl>
         7422  +** [[SQLITE_SCANSTAT_NLOOP]] <td>SQLITE_SCANSTAT_NLOOP</dt>
         7423  +** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
         7424  +** total number of times that the X-th loop has run.</dd>
         7425  +**
         7426  +** [[SQLITE_SCANSTAT_NVISIT]] <td>SQLITE_SCANSTAT_NVISIT</dt>
         7427  +** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
         7428  +** total number of rows visited by the X-th loop.</dd>
         7429  +**
         7430  +** [[SQLITE_SCANSTAT_EST]] <td>SQLITE_SCANSTAT_EST</dt>
         7431  +** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
         7432  +** query planner's estimate for the number of rows visited for each
         7433  +** iteration of the X-th loop.  If the query planner's estimate was accurate,
         7434  +** then this value should be approximately NVISIT/NLOOP.
         7435  +**
         7436  +** [[SQLITE_SCANSTAT_NAME]] <td>SQLITE_SCANSTAT_NAME</dt>
         7437  +** <dd>The "const char *" variable pointed to by the T parameter will be set to 
         7438  +** a zero-terminated UTF-8 string containing the name of the index or table used
         7439  +** for the X-th loop.
         7440  +**
         7441  +** [[SQLITE_SCANSTAT_EXPLAIN]] <td>SQLITE_SCANSTAT_EXPLAIN</dt>
         7442  +** <dd>The "const char *" variable pointed to by the T parameter will be set to 
         7443  +** a zero-terminated UTF-8 string containing the [EXPLAIN QUERY PLAN] description
         7444  +** for the X-th loop.
         7445  +** </dl>
         7446  +*/
         7447  +#define SQLITE_SCANSTAT_NLOOP    0
         7448  +#define SQLITE_SCANSTAT_NVISIT   1
         7449  +#define SQLITE_SCANSTAT_EST      2
         7450  +#define SQLITE_SCANSTAT_NAME     3
         7451  +#define SQLITE_SCANSTAT_EXPLAIN  4
         7452  +
         7453  +/*
         7454  +** CAPI3REF: Prepared Statement Scan Status
         7455  +**
         7456  +** Return status data for a single loop within query pStmt.
         7457  +**
         7458  +** The "iScanStatusOp" parameter determines which status information to return.
         7459  +** The "iScanStatusOp" must be one of the [scanstatus options] or the behavior of
         7460  +** this interface is undefined.
         7461  +** The requested measurement is written into a variable pointed to by
         7462  +** the "pOut" parameter.
         7463  +** Parameter "idx" identifies the specific loop to retrieve statistics for.
         7464  +** Loops are numbered starting from zero. If idx is out of range - less than
         7465  +** zero or greater than or equal to the total number of loops used to implement
         7466  +** the statement - a non-zero value is returned and the variable that pOut
         7467  +** points to is unchanged.
         7468  +**
         7469  +** Statistics might not be available for all loops in all statements. In cases
         7470  +** where there exist loops with no available statistics, this function behaves
         7471  +** as if the loop did not exist - it returns non-zero and leave the variable
         7472  +** that pOut points to unchanged.
         7473  +**
         7474  +** This API is only available if the library is built with pre-processor
         7475  +** symbol [SQLITE_ENABLE_STMT_SCANSTATUS] defined.
         7476  +*/
         7477  +SQLITE_EXPERIMENTAL int sqlite3_stmt_scanstatus(
         7478  +  sqlite3_stmt *pStmt,      /* Prepared statement for which info desired */
         7479  +  int idx,                  /* Index of loop to report on */
         7480  +  int iScanStatusOp,        /* Information desired.  SQLITE_SCANSTAT_* */
         7481  +  void *pOut                /* Result written here */
         7482  +);     
         7483  +
         7484  +/*
         7485  +** CAPI3REF: Zero Scan-Status Counters
         7486  +**
         7487  +** Zero all sqlite3_stmt_scanstatus() related event counters.
         7488  +**
         7489  +** This API is only available if the library is built with pre-processor
         7490  +** symbol [SQLITE_ENABLE_STMT_SCANSTATUS] defined.
         7491  +*/
         7492  +SQLITE_EXPERIMENTAL void sqlite3_stmt_scanstatus_reset(sqlite3_stmt*);
  7414   7493   
  7415   7494   
  7416   7495   /*
  7417   7496   ** Undo the hack that converts floating point types to integer for
  7418   7497   ** builds on processors without floating point support.
  7419   7498   */
  7420   7499   #ifdef SQLITE_OMIT_FLOATING_POINT

Changes to src/tclsqlite.c.

  3637   3637     }
  3638   3638   
  3639   3639     pDb->bLegacyPrepare = bPrepare;
  3640   3640   
  3641   3641     Tcl_ResetResult(interp);
  3642   3642     return TCL_OK;
  3643   3643   }
         3644  +
         3645  +/*
         3646  +** Tclcmd: db_last_stmt_ptr DB
         3647  +**
         3648  +**   If the statement cache associated with database DB is not empty,
         3649  +**   return the text representation of the most recently used statement
         3650  +**   handle.
         3651  +*/
         3652  +static int db_last_stmt_ptr(
         3653  +  ClientData cd,
         3654  +  Tcl_Interp *interp,
         3655  +  int objc,
         3656  +  Tcl_Obj *CONST objv[]
         3657  +){
         3658  +  extern int sqlite3TestMakePointerStr(Tcl_Interp*, char*, void*);
         3659  +  Tcl_CmdInfo cmdInfo;
         3660  +  SqliteDb *pDb;
         3661  +  sqlite3_stmt *pStmt = 0;
         3662  +  char zBuf[100];
         3663  +
         3664  +  if( objc!=2 ){
         3665  +    Tcl_WrongNumArgs(interp, 1, objv, "DB");
         3666  +    return TCL_ERROR;
         3667  +  }
         3668  +
         3669  +  if( !Tcl_GetCommandInfo(interp, Tcl_GetString(objv[1]), &cmdInfo) ){
         3670  +    Tcl_AppendResult(interp, "no such db: ", Tcl_GetString(objv[1]), (char*)0);
         3671  +    return TCL_ERROR;
         3672  +  }
         3673  +  pDb = (SqliteDb*)cmdInfo.objClientData;
         3674  +
         3675  +  if( pDb->stmtList ) pStmt = pDb->stmtList->pStmt;
         3676  +  if( sqlite3TestMakePointerStr(interp, zBuf, pStmt) ){
         3677  +    return TCL_ERROR;
         3678  +  }
         3679  +  Tcl_SetResult(interp, zBuf, TCL_VOLATILE);
         3680  +
         3681  +  return TCL_OK;
         3682  +}
  3644   3683   #endif
  3645   3684   
  3646   3685   /*
  3647   3686   ** Configure the interpreter passed as the first argument to have access
  3648   3687   ** to the commands and linked variables that make up:
  3649   3688   **
  3650   3689   **   * the [sqlite3] extension itself, 
................................................................................
  3756   3795   
  3757   3796       Tcl_CreateObjCommand(
  3758   3797           interp, "load_testfixture_extensions", init_all_cmd, 0, 0
  3759   3798       );
  3760   3799       Tcl_CreateObjCommand(
  3761   3800           interp, "db_use_legacy_prepare", db_use_legacy_prepare_cmd, 0, 0
  3762   3801       );
         3802  +    Tcl_CreateObjCommand(
         3803  +        interp, "db_last_stmt_ptr", db_last_stmt_ptr, 0, 0
         3804  +    );
  3763   3805   
  3764   3806   #ifdef SQLITE_SSE
  3765   3807       Sqlitetestsse_Init(interp);
  3766   3808   #endif
  3767   3809     }
  3768   3810   #endif
  3769   3811   }

Changes to src/test1.c.

  2297   2297     }
  2298   2298     if( Tcl_GetBooleanFromObj(interp, objv[3], &resetFlag) ) return TCL_ERROR;
  2299   2299     iValue = sqlite3_stmt_status(pStmt, op, resetFlag);
  2300   2300     Tcl_SetObjResult(interp, Tcl_NewIntObj(iValue));
  2301   2301     return TCL_OK;
  2302   2302   }
  2303   2303   
         2304  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         2305  +/*
         2306  +** Usage:  sqlite3_stmt_scanstatus STMT IDX
         2307  +*/
         2308  +static int test_stmt_scanstatus(
         2309  +  void * clientData,
         2310  +  Tcl_Interp *interp,
         2311  +  int objc,
         2312  +  Tcl_Obj *CONST objv[]
         2313  +){
         2314  +  sqlite3_stmt *pStmt;            /* First argument */
         2315  +  int idx;                        /* Second argument */
         2316  +
         2317  +  const char *zName;
         2318  +  const char *zExplain;
         2319  +  sqlite3_int64 nLoop;
         2320  +  sqlite3_int64 nVisit;
         2321  +  sqlite3_int64 nEst;
         2322  +  int res;
         2323  +
         2324  +  if( objc!=3 ){
         2325  +    Tcl_WrongNumArgs(interp, 1, objv, "STMT IDX");
         2326  +    return TCL_ERROR;
         2327  +  }
         2328  +  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
         2329  +  if( Tcl_GetIntFromObj(interp, objv[2], &idx) ) return TCL_ERROR;
         2330  +
         2331  +  res = sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop);
         2332  +  if( res==0 ){
         2333  +    Tcl_Obj *pRet = Tcl_NewObj();
         2334  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nLoop", -1));
         2335  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nLoop));
         2336  +    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
         2337  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nVisit", -1));
         2338  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nVisit));
         2339  +    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EST, (void*)&nEst);
         2340  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nEst", -1));
         2341  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nEst));
         2342  +    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NAME, (void*)&zName);
         2343  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zName", -1));
         2344  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zName, -1));
         2345  +    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
         2346  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zExplain", -1));
         2347  +    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zExplain, -1));
         2348  +    Tcl_SetObjResult(interp, pRet);
         2349  +  }else{
         2350  +    Tcl_ResetResult(interp);
         2351  +  }
         2352  +  return TCL_OK;
         2353  +}
         2354  +
         2355  +/*
         2356  +** Usage:  sqlite3_stmt_scanstatus_reset  STMT
         2357  +*/
         2358  +static int test_stmt_scanstatus_reset(
         2359  +  void * clientData,
         2360  +  Tcl_Interp *interp,
         2361  +  int objc,
         2362  +  Tcl_Obj *CONST objv[]
         2363  +){
         2364  +  sqlite3_stmt *pStmt;            /* First argument */
         2365  +  if( objc!=2 ){
         2366  +    Tcl_WrongNumArgs(interp, 1, objv, "STMT");
         2367  +    return TCL_ERROR;
         2368  +  }
         2369  +  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
         2370  +  sqlite3_stmt_scanstatus_reset(pStmt);
         2371  +  return TCL_OK;
         2372  +}
         2373  +#endif
         2374  +
  2304   2375   /*
  2305   2376   ** Usage:  sqlite3_next_stmt  DB  STMT
  2306   2377   **
  2307   2378   ** Return the next statment in sequence after STMT.
  2308   2379   */
  2309   2380   static int test_next_stmt(
  2310   2381     void * clientData,
................................................................................
  6864   6935        { "sorter_test_sort4_helper", sorter_test_sort4_helper },
  6865   6936   #ifdef SQLITE_USER_AUTHENTICATION
  6866   6937        { "sqlite3_user_authenticate", test_user_authenticate, 0 },
  6867   6938        { "sqlite3_user_add",          test_user_add,          0 },
  6868   6939        { "sqlite3_user_change",       test_user_change,       0 },
  6869   6940        { "sqlite3_user_delete",       test_user_delete,       0 },
  6870   6941   #endif
         6942  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         6943  +     { "sqlite3_stmt_scanstatus",       test_stmt_scanstatus,   0 },
         6944  +     { "sqlite3_stmt_scanstatus_reset", test_stmt_scanstatus_reset,   0 },
         6945  +#endif
  6871   6946   
  6872   6947     };
  6873   6948     static int bitmask_size = sizeof(Bitmask)*8;
  6874   6949     int i;
  6875   6950     extern int sqlite3_sync_count, sqlite3_fullsync_count;
  6876   6951     extern int sqlite3_opentemp_count;
  6877   6952     extern int sqlite3_like_count;

Changes to src/test_config.c.

   479    479     Tcl_SetVar2(interp, "sqlite_options", "stat4", "0", TCL_GLOBAL_ONLY);
   480    480   #endif
   481    481   #if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4)
   482    482     Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY);
   483    483   #else
   484    484     Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY);
   485    485   #endif
          486  +
          487  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
          488  +  Tcl_SetVar2(interp, "sqlite_options", "scanstatus", "1", TCL_GLOBAL_ONLY);
          489  +#else
          490  +  Tcl_SetVar2(interp, "sqlite_options", "scanstatus", "0", TCL_GLOBAL_ONLY);
          491  +#endif
   486    492   
   487    493   #if !defined(SQLITE_ENABLE_LOCKING_STYLE)
   488    494   #  if defined(__APPLE__)
   489    495   #    define SQLITE_ENABLE_LOCKING_STYLE 1
   490    496   #  else
   491    497   #    define SQLITE_ENABLE_LOCKING_STYLE 0
   492    498   #  endif

Changes to src/vdbe.c.

   604    604       assert( pc>=0 && pc<p->nOp );
   605    605       if( db->mallocFailed ) goto no_mem;
   606    606   #ifdef VDBE_PROFILE
   607    607       start = sqlite3Hwtime();
   608    608   #endif
   609    609       nVmStep++;
   610    610       pOp = &aOp[pc];
          611  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
          612  +    if( p->anExec ) p->anExec[pc]++;
          613  +#endif
   611    614   
   612    615       /* Only allow tracing if SQLITE_DEBUG is defined.
   613    616       */
   614    617   #ifdef SQLITE_DEBUG
   615    618       if( db->flags & SQLITE_VdbeTrace ){
   616    619         sqlite3VdbePrintOp(stdout, pc, pOp);
   617    620       }
................................................................................
  4484   4487     p->aCounter[SQLITE_STMTSTATUS_SORT]++;
  4485   4488     /* Fall through into OP_Rewind */
  4486   4489   }
  4487   4490   /* Opcode: Rewind P1 P2 * * *
  4488   4491   **
  4489   4492   ** The next use of the Rowid or Column or Next instruction for P1 
  4490   4493   ** will refer to the first entry in the database table or index.
  4491         -** If the table or index is empty and P2>0, then jump immediately to P2.
  4492         -** If P2 is 0 or if the table or index is not empty, fall through
  4493         -** to the following instruction.
         4494  +** If the table or index is empty, jump immediately to P2.
         4495  +** If the table or index is not empty, fall through to the following 
         4496  +** instruction.
  4494   4497   **
  4495   4498   ** This opcode leaves the cursor configured to move in forward order,
  4496   4499   ** from the beginning toward the end.  In other words, the cursor is
  4497   4500   ** configured to use Next, not Prev.
  4498   4501   */
  4499   4502   case OP_Rewind: {        /* jump */
  4500   4503     VdbeCursor *pC;
................................................................................
  5402   5405       pFrame->apCsr = p->apCsr;
  5403   5406       pFrame->nCursor = p->nCursor;
  5404   5407       pFrame->aOp = p->aOp;
  5405   5408       pFrame->nOp = p->nOp;
  5406   5409       pFrame->token = pProgram->token;
  5407   5410       pFrame->aOnceFlag = p->aOnceFlag;
  5408   5411       pFrame->nOnceFlag = p->nOnceFlag;
         5412  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         5413  +    pFrame->anExec = p->anExec;
         5414  +#endif
  5409   5415   
  5410   5416       pEnd = &VdbeFrameMem(pFrame)[pFrame->nChildMem];
  5411   5417       for(pMem=VdbeFrameMem(pFrame); pMem!=pEnd; pMem++){
  5412   5418         pMem->flags = MEM_Undefined;
  5413   5419         pMem->db = db;
  5414   5420       }
  5415   5421     }else{
................................................................................
  5430   5436     p->nMem = pFrame->nChildMem;
  5431   5437     p->nCursor = (u16)pFrame->nChildCsr;
  5432   5438     p->apCsr = (VdbeCursor **)&aMem[p->nMem+1];
  5433   5439     p->aOp = aOp = pProgram->aOp;
  5434   5440     p->nOp = pProgram->nOp;
  5435   5441     p->aOnceFlag = (u8 *)&p->apCsr[p->nCursor];
  5436   5442     p->nOnceFlag = pProgram->nOnce;
         5443  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         5444  +  p->anExec = 0;
         5445  +#endif
  5437   5446     pc = -1;
  5438   5447     memset(p->aOnceFlag, 0, p->nOnceFlag);
  5439   5448   
  5440   5449     break;
  5441   5450   }
  5442   5451   
  5443   5452   /* Opcode: Param P1 P2 * * *

Changes to src/vdbe.h.

   277    277   #else
   278    278   # define VdbeCoverage(v)
   279    279   # define VdbeCoverageIf(v,x)
   280    280   # define VdbeCoverageAlwaysTaken(v)
   281    281   # define VdbeCoverageNeverTaken(v)
   282    282   # define VDBE_OFFSET_LINENO(x) 0
   283    283   #endif
          284  +
          285  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
          286  +void sqlite3VdbeScanStatus(Vdbe*, int, int, int, i64, const char*);
          287  +#else
          288  +# define sqlite3VdbeScanStatus(a,b,c,d,e)
          289  +#endif
   284    290   
   285    291   #endif

Changes to src/vdbeInt.h.

   128    128   ** set to NULL if the currently executing frame is the main program.
   129    129   */
   130    130   typedef struct VdbeFrame VdbeFrame;
   131    131   struct VdbeFrame {
   132    132     Vdbe *v;                /* VM this frame belongs to */
   133    133     VdbeFrame *pParent;     /* Parent of this frame, or NULL if parent is main */
   134    134     Op *aOp;                /* Program instructions for parent frame */
          135  +  i64 *anExec;            /* Event counters from parent frame */
   135    136     Mem *aMem;              /* Array of memory cells for parent frame */
   136    137     u8 *aOnceFlag;          /* Array of OP_Once flags for parent frame */
   137    138     VdbeCursor **apCsr;     /* Array of Vdbe cursors for parent frame */
   138    139     void *token;            /* Copy of SubProgram.token */
   139    140     i64 lastRowid;          /* Last insert rowid (sqlite3.lastRowid) */
   140    141     int nCursor;            /* Number of entries in apCsr */
   141    142     int pc;                 /* Program Counter in parent (calling) frame */
................................................................................
   292    293   };
   293    294   
   294    295   /* A bitfield type for use inside of structures.  Always follow with :N where
   295    296   ** N is the number of bits.
   296    297   */
   297    298   typedef unsigned bft;  /* Bit Field Type */
   298    299   
          300  +typedef struct ScanStatus ScanStatus;
          301  +struct ScanStatus {
          302  +  int addrExplain;                /* OP_Explain for loop */
          303  +  int addrLoop;                   /* Address of "loops" counter */
          304  +  int addrVisit;                  /* Address of "rows visited" counter */
          305  +  i64 nEst;                       /* Estimated rows per loop */
          306  +  char *zName;                    /* Name of table or index */
          307  +};
          308  +
   299    309   /*
   300    310   ** An instance of the virtual machine.  This structure contains the complete
   301    311   ** state of the virtual machine.
   302    312   **
   303    313   ** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare()
   304    314   ** is really a pointer to an instance of this structure.
   305    315   **
................................................................................
   364    374     VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */
   365    375     int nFrame;             /* Number of frames in pFrame list */
   366    376     u32 expmask;            /* Binding to these vars invalidates VM */
   367    377     SubProgram *pProgram;   /* Linked list of all sub-programs used by VM */
   368    378     int nOnceFlag;          /* Size of array aOnceFlag[] */
   369    379     u8 *aOnceFlag;          /* Flags for OP_Once */
   370    380     AuxData *pAuxData;      /* Linked list of auxdata allocations */
          381  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
          382  +  i64 *anExec;            /* Number of times each op has been executed */
          383  +  int nScan;              /* Entries in aScan[] */
          384  +  ScanStatus *aScan;      /* Scan definitions for sqlite3_stmt_scanstatus() */
          385  +#endif
   371    386   };
   372    387   
   373    388   /*
   374    389   ** The following are allowed values for Vdbe.magic
   375    390   */
   376    391   #define VDBE_MAGIC_INIT     0x26bceaa5    /* Building a VDBE program */
   377    392   #define VDBE_MAGIC_RUN      0xbdf20da3    /* VDBE is ready to execute */

Changes to src/vdbeapi.c.

  1471   1471       return 0;
  1472   1472     }
  1473   1473   #endif
  1474   1474     v = pVdbe->aCounter[op];
  1475   1475     if( resetFlag ) pVdbe->aCounter[op] = 0;
  1476   1476     return (int)v;
  1477   1477   }
         1478  +
         1479  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         1480  +/*
         1481  +** Return status data for a single loop within query pStmt.
         1482  +*/
         1483  +int sqlite3_stmt_scanstatus(
         1484  +  sqlite3_stmt *pStmt,            /* Prepared statement being queried */
         1485  +  int idx,                        /* Index of loop to report on */
         1486  +  int iScanStatusOp,              /* Which metric to return */
         1487  +  void *pOut                      /* OUT: Write the answer here */
         1488  +){
         1489  +  Vdbe *p = (Vdbe*)pStmt;
         1490  +  ScanStatus *pScan;
         1491  +  if( idx<0 || idx>=p->nScan ) return 1;
         1492  +  pScan = &p->aScan[idx];
         1493  +  switch( iScanStatusOp ){
         1494  +    case SQLITE_SCANSTAT_NLOOP: {
         1495  +      *(sqlite3_int64*)pOut = p->anExec[pScan->addrLoop];
         1496  +      break;
         1497  +    }
         1498  +    case SQLITE_SCANSTAT_NVISIT: {
         1499  +      *(sqlite3_int64*)pOut = p->anExec[pScan->addrVisit];
         1500  +      break;
         1501  +    }
         1502  +    case SQLITE_SCANSTAT_EST: {
         1503  +      *(sqlite3_int64*)pOut = pScan->nEst;
         1504  +      break;
         1505  +    }
         1506  +    case SQLITE_SCANSTAT_NAME: {
         1507  +      *(const char**)pOut = pScan->zName;
         1508  +      break;
         1509  +    }
         1510  +    case SQLITE_SCANSTAT_EXPLAIN: {
         1511  +      if( pScan->addrExplain ){
         1512  +        *(const char**)pOut = p->aOp[ pScan->addrExplain ].p4.z;
         1513  +      }else{
         1514  +        *(const char**)pOut = 0;
         1515  +      }
         1516  +      break;
         1517  +    }
         1518  +    default: {
         1519  +      return 1;
         1520  +    }
         1521  +  }
         1522  +  return 0;
         1523  +}
         1524  +
         1525  +/*
         1526  +** Zero all counters associated with the sqlite3_stmt_scanstatus() data.
         1527  +*/
         1528  +void sqlite3_stmt_scanstatus_reset(sqlite3_stmt *pStmt){
         1529  +  Vdbe *p = (Vdbe*)pStmt;
         1530  +  memset(p->anExec, 0, p->nOp * sizeof(i64));
         1531  +}
         1532  +#endif /* SQLITE_ENABLE_STMT_SCANSTATUS */

Changes to src/vdbeaux.c.

   592    592         }
   593    593   #endif
   594    594       }
   595    595       p->nOp += nOp;
   596    596     }
   597    597     return addr;
   598    598   }
          599  +
          600  +#if defined(SQLITE_ENABLE_STMT_SCANSTATUS)
          601  +/*
          602  +** Add an entry to the array of counters managed by sqlite3_stmt_scanstatus().
          603  +*/
          604  +void sqlite3VdbeScanStatus(
          605  +  Vdbe *p,                        /* VM to add scanstatus() to */
          606  +  int addrExplain,                /* Address of OP_Explain (or 0) */
          607  +  int addrLoop,                   /* Address of loop counter */ 
          608  +  int addrVisit,                  /* Address of rows visited counter */
          609  +  i64 nEst,                       /* Estimated number of rows */
          610  +  const char *zName               /* Name of table or index being scanned */
          611  +){
          612  +  int nByte = (p->nScan+1) * sizeof(ScanStatus);
          613  +  ScanStatus *aNew;
          614  +  aNew = (ScanStatus*)sqlite3DbRealloc(p->db, p->aScan, nByte);
          615  +  if( aNew ){
          616  +    ScanStatus *pNew = &aNew[p->nScan++];
          617  +    pNew->addrExplain = addrExplain;
          618  +    pNew->addrLoop = addrLoop;
          619  +    pNew->addrVisit = addrVisit;
          620  +    pNew->nEst = nEst;
          621  +    pNew->zName = sqlite3DbStrDup(p->db, zName);
          622  +    p->aScan = aNew;
          623  +  }
          624  +}
          625  +#endif
          626  +
   599    627   
   600    628   /*
   601    629   ** Change the value of the P1 operand for a specific instruction.
   602    630   ** This routine is useful when a large program is loaded from a
   603    631   ** static array using sqlite3VdbeAddOpList but we want to make a
   604    632   ** few minor changes to the program.
   605    633   */
................................................................................
  1691   1719       p->aMem = allocSpace(p->aMem, nMem*sizeof(Mem), &zCsr, zEnd, &nByte);
  1692   1720       p->aVar = allocSpace(p->aVar, nVar*sizeof(Mem), &zCsr, zEnd, &nByte);
  1693   1721       p->apArg = allocSpace(p->apArg, nArg*sizeof(Mem*), &zCsr, zEnd, &nByte);
  1694   1722       p->azVar = allocSpace(p->azVar, nVar*sizeof(char*), &zCsr, zEnd, &nByte);
  1695   1723       p->apCsr = allocSpace(p->apCsr, nCursor*sizeof(VdbeCursor*),
  1696   1724                             &zCsr, zEnd, &nByte);
  1697   1725       p->aOnceFlag = allocSpace(p->aOnceFlag, nOnce, &zCsr, zEnd, &nByte);
         1726  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         1727  +    p->anExec = allocSpace(p->anExec, p->nOp*sizeof(i64), &zCsr, zEnd, &nByte);
         1728  +#endif
  1698   1729       if( nByte ){
  1699   1730         p->pFree = sqlite3DbMallocZero(db, nByte);
  1700   1731       }
  1701   1732       zCsr = p->pFree;
  1702   1733       zEnd = &zCsr[nByte];
  1703   1734     }while( nByte && !db->mallocFailed );
  1704   1735   
................................................................................
  1758   1789   /*
  1759   1790   ** Copy the values stored in the VdbeFrame structure to its Vdbe. This
  1760   1791   ** is used, for example, when a trigger sub-program is halted to restore
  1761   1792   ** control to the main program.
  1762   1793   */
  1763   1794   int sqlite3VdbeFrameRestore(VdbeFrame *pFrame){
  1764   1795     Vdbe *v = pFrame->v;
         1796  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         1797  +  v->anExec = pFrame->anExec;
         1798  +#endif
  1765   1799     v->aOnceFlag = pFrame->aOnceFlag;
  1766   1800     v->nOnceFlag = pFrame->nOnceFlag;
  1767   1801     v->aOp = pFrame->aOp;
  1768   1802     v->nOp = pFrame->nOp;
  1769   1803     v->aMem = pFrame->aMem;
  1770   1804     v->nMem = pFrame->nMem;
  1771   1805     v->apCsr = pFrame->apCsr;
................................................................................
  2681   2715       sqlite3DbFree(db, pSub);
  2682   2716     }
  2683   2717     for(i=p->nzVar-1; i>=0; i--) sqlite3DbFree(db, p->azVar[i]);
  2684   2718     vdbeFreeOpArray(db, p->aOp, p->nOp);
  2685   2719     sqlite3DbFree(db, p->aColName);
  2686   2720     sqlite3DbFree(db, p->zSql);
  2687   2721     sqlite3DbFree(db, p->pFree);
         2722  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         2723  +  for(i=0; i<p->nScan; i++){
         2724  +    sqlite3DbFree(db, p->aScan[i].zName);
         2725  +  }
         2726  +  sqlite3DbFree(db, p->aScan);
         2727  +#endif
  2688   2728   }
  2689   2729   
  2690   2730   /*
  2691   2731   ** Delete an entire VDBE.
  2692   2732   */
  2693   2733   void sqlite3VdbeDelete(Vdbe *p){
  2694   2734     sqlite3 *db;

Changes to src/where.c.

  2804   2804       explainAppendTerm(pStr, i, z, "<");
  2805   2805     }
  2806   2806     sqlite3StrAccumAppend(pStr, ")", 1);
  2807   2807   }
  2808   2808   
  2809   2809   /*
  2810   2810   ** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
  2811         -** command. If the query being compiled is an EXPLAIN QUERY PLAN, a single
  2812         -** record is added to the output to describe the table scan strategy in 
  2813         -** pLevel.
         2811  +** command, or if either SQLITE_DEBUG or SQLITE_ENABLE_STMT_SCANSTATUS was
         2812  +** defined at compile-time. If it is not a no-op, a single OP_Explain opcode 
         2813  +** is added to the output to describe the table scan strategy in pLevel.
         2814  +**
         2815  +** If an OP_Explain opcode is added to the VM, its address is returned.
         2816  +** Otherwise, if no OP_Explain is coded, zero is returned.
  2814   2817   */
  2815         -static void explainOneScan(
         2818  +static int explainOneScan(
  2816   2819     Parse *pParse,                  /* Parse context */
  2817   2820     SrcList *pTabList,              /* Table list this loop refers to */
  2818   2821     WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
  2819   2822     int iLevel,                     /* Value for "level" column of output */
  2820   2823     int iFrom,                      /* Value for "from" column of output */
  2821   2824     u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
  2822   2825   ){
  2823         -#ifndef SQLITE_DEBUG
         2826  +  int ret = 0;
         2827  +#if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS)
  2824   2828     if( pParse->explain==2 )
  2825   2829   #endif
  2826   2830     {
  2827   2831       struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
  2828   2832       Vdbe *v = pParse->pVdbe;      /* VM being constructed */
  2829   2833       sqlite3 *db = pParse->db;     /* Database handle */
  2830   2834       int iId = pParse->iSelectId;  /* Select id (left-most output column) */
................................................................................
  2833   2837       u32 flags;                    /* Flags that describe this loop */
  2834   2838       char *zMsg;                   /* Text to add to EQP output */
  2835   2839       StrAccum str;                 /* EQP output string */
  2836   2840       char zBuf[100];               /* Initial space for EQP output string */
  2837   2841   
  2838   2842       pLoop = pLevel->pWLoop;
  2839   2843       flags = pLoop->wsFlags;
  2840         -    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;
         2844  +    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return 0;
  2841   2845   
  2842   2846       isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
  2843   2847               || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))
  2844   2848               || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));
  2845   2849   
  2846   2850       sqlite3StrAccumInit(&str, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
  2847   2851       str.db = db;
................................................................................
  2905   2909       if( pLoop->nOut>=10 ){
  2906   2910         sqlite3XPrintf(&str, 0, " (~%llu rows)", sqlite3LogEstToInt(pLoop->nOut));
  2907   2911       }else{
  2908   2912         sqlite3StrAccumAppend(&str, " (~1 row)", 9);
  2909   2913       }
  2910   2914   #endif
  2911   2915       zMsg = sqlite3StrAccumFinish(&str);
  2912         -    sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC);
         2916  +    ret = sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg,P4_DYNAMIC);
  2913   2917     }
         2918  +  return ret;
         2919  +}
         2920  +#else
         2921  +# define explainOneScan(u,v,w,x,y,z) 0
         2922  +#endif /* SQLITE_OMIT_EXPLAIN */
         2923  +
         2924  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         2925  +/*
         2926  +** Configure the VM passed as the first argument with an
         2927  +** sqlite3_stmt_scanstatus() entry corresponding to the scan used to 
         2928  +** implement level pLvl. Argument pSrclist is a pointer to the FROM 
         2929  +** clause that the scan reads data from.
         2930  +**
         2931  +** If argument addrExplain is not 0, it must be the address of an 
         2932  +** OP_Explain instruction that describes the same loop.
         2933  +*/
         2934  +static void addScanStatus(
         2935  +  Vdbe *v,                        /* Vdbe to add scanstatus entry to */
         2936  +  SrcList *pSrclist,              /* FROM clause pLvl reads data from */
         2937  +  WhereLevel *pLvl,               /* Level to add scanstatus() entry for */
         2938  +  int addrExplain                 /* Address of OP_Explain (or 0) */
         2939  +){
         2940  +  const char *zObj = 0;
         2941  +  i64 nEst = 1;
         2942  +  WhereLoop *pLoop = pLvl->pWLoop;
         2943  +  if( (pLoop->wsFlags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
         2944  +    zObj = pLoop->u.btree.pIndex->zName;
         2945  +  }else{
         2946  +    zObj = pSrclist->a[pLvl->iFrom].zName;
         2947  +  }
         2948  +  if( pLoop->nOut>=10 ){
         2949  +    nEst = sqlite3LogEstToInt(pLoop->nOut);
         2950  +  }
         2951  +  sqlite3VdbeScanStatus(
         2952  +      v, addrExplain, pLvl->addrBody, pLvl->addrVisit, nEst, zObj
         2953  +  );
  2914   2954   }
  2915   2955   #else
  2916         -# define explainOneScan(u,v,w,x,y,z)
  2917         -#endif /* SQLITE_OMIT_EXPLAIN */
         2956  +# define addScanStatus(a, b, c, d) ((void)d)
         2957  +#endif
         2958  +
  2918   2959   
  2919   2960   
  2920   2961   /*
  2921   2962   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  2922   2963   ** implementation described by pWInfo.
  2923   2964   */
  2924   2965   static Bitmask codeOneLoopStart(
................................................................................
  3578   3619           /* Loop through table entries that match term pOrTerm. */
  3579   3620           WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
  3580   3621           pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
  3581   3622                                         wctrlFlags, iCovCur);
  3582   3623           assert( pSubWInfo || pParse->nErr || db->mallocFailed );
  3583   3624           if( pSubWInfo ){
  3584   3625             WhereLoop *pSubLoop;
  3585         -          explainOneScan(
         3626  +          int addrExplain = explainOneScan(
  3586   3627                 pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
  3587   3628             );
         3629  +          addScanStatus(v, pOrTab, &pSubWInfo->a[0], addrExplain);
         3630  +
  3588   3631             /* This is the sub-WHERE clause body.  First skip over
  3589   3632             ** duplicate rows from prior sub-WHERE clauses, and record the
  3590   3633             ** rowid (or PRIMARY KEY) for the current row so that the same
  3591   3634             ** row will be skipped in subsequent sub-WHERE clauses.
  3592   3635             */
  3593   3636             if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  3594   3637               int r;
................................................................................
  3710   3753         pLevel->p1 = iCur;
  3711   3754         pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk);
  3712   3755         VdbeCoverageIf(v, bRev==0);
  3713   3756         VdbeCoverageIf(v, bRev!=0);
  3714   3757         pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
  3715   3758       }
  3716   3759     }
         3760  +
         3761  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         3762  +  pLevel->addrVisit = sqlite3VdbeCurrentAddr(v);
         3763  +#endif
  3717   3764   
  3718   3765     /* Insert code to test every subexpression that can be completely
  3719   3766     ** computed using the current set of tables.
  3720   3767     */
  3721   3768     for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
  3722   3769       Expr *pE;
  3723   3770       testcase( pTerm->wtFlags & TERM_VIRTUAL );
................................................................................
  6412   6459   
  6413   6460     /* Generate the code to do the search.  Each iteration of the for
  6414   6461     ** loop below generates code for a single nested loop of the VM
  6415   6462     ** program.
  6416   6463     */
  6417   6464     notReady = ~(Bitmask)0;
  6418   6465     for(ii=0; ii<nTabList; ii++){
         6466  +    int addrExplain;
         6467  +    int wsFlags;
  6419   6468       pLevel = &pWInfo->a[ii];
         6469  +    wsFlags = pLevel->pWLoop->wsFlags;
  6420   6470   #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
  6421   6471       if( (pLevel->pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0 ){
  6422   6472         constructAutomaticIndex(pParse, &pWInfo->sWC,
  6423   6473                   &pTabList->a[pLevel->iFrom], notReady, pLevel);
  6424   6474         if( db->mallocFailed ) goto whereBeginError;
  6425   6475       }
  6426   6476   #endif
  6427         -    explainOneScan(pParse, pTabList, pLevel, ii, pLevel->iFrom, wctrlFlags);
         6477  +    addrExplain = explainOneScan(
         6478  +        pParse, pTabList, pLevel, ii, pLevel->iFrom, wctrlFlags
         6479  +    );
  6428   6480       pLevel->addrBody = sqlite3VdbeCurrentAddr(v);
  6429   6481       notReady = codeOneLoopStart(pWInfo, ii, notReady);
  6430   6482       pWInfo->iContinue = pLevel->addrCont;
         6483  +    if( (wsFlags&WHERE_MULTI_OR)==0 && (wctrlFlags&WHERE_ONETABLE_ONLY)==0 ){
         6484  +      addScanStatus(v, pTabList, pLevel, addrExplain);
         6485  +    }
  6431   6486     }
  6432   6487   
  6433   6488     /* Done. */
  6434   6489     VdbeModuleComment((v, "Begin WHERE-core"));
  6435   6490     return pWInfo;
  6436   6491   
  6437   6492     /* Jump here if malloc fails */

Changes to src/whereInt.h.

    81     81           u8 eEndLoopOp;         /* IN Loop terminator. OP_Next or OP_Prev */
    82     82         } *aInLoop;           /* Information about each nested IN operator */
    83     83       } in;                 /* Used when pWLoop->wsFlags&WHERE_IN_ABLE */
    84     84       Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
    85     85     } u;
    86     86     struct WhereLoop *pWLoop;  /* The selected WhereLoop object */
    87     87     Bitmask notReady;          /* FROM entries not usable at this level */
           88  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
           89  +  int addrVisit;        /* Address at which row is visited */
           90  +#endif
    88     91   };
    89     92   
    90     93   /*
    91     94   ** Each instance of this object represents an algorithm for evaluating one
    92     95   ** term of a join.  Every term of the FROM clause will have at least
    93     96   ** one corresponding WhereLoop object (unless INDEXED BY constraints
    94     97   ** prevent a query solution - which is an error) and many terms of the

Added test/scanstatus.test.

            1  +# 2014 November 1
            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  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix scanstatus
           16  +
           17  +ifcapable !scanstatus {
           18  +  finish_test
           19  +  return
           20  +}
           21  +
           22  +do_execsql_test 1.0 {
           23  +  CREATE TABLE t1(a, b);
           24  +  CREATE TABLE t2(x, y);
           25  +  INSERT INTO t1 VALUES(1, 2);
           26  +  INSERT INTO t1 VALUES(3, 4);
           27  +  INSERT INTO t2 VALUES('a', 'b');
           28  +  INSERT INTO t2 VALUES('c', 'd');
           29  +  INSERT INTO t2 VALUES('e', 'f');
           30  +}
           31  +
           32  +proc do_scanstatus_test {tn res} {
           33  +  set stmt [db_last_stmt_ptr db]
           34  +  set idx 0
           35  +  set ret [list]
           36  +  while {1} {
           37  +    set r [sqlite3_stmt_scanstatus $stmt $idx]
           38  +    if {[llength $r]==0} break
           39  +    lappend ret {*}$r
           40  +    incr idx
           41  +  }
           42  +
           43  +  uplevel [list do_test $tn [list set {} $ret] [list {*}$res]]
           44  +}
           45  +
           46  +do_execsql_test 1.1 { SELECT count(*) FROM t1, t2; } 6
           47  +do_scanstatus_test 1.2 {
           48  +  nLoop 1 nVisit 2 nEst 1048576 zName t1 zExplain {SCAN TABLE t1}
           49  +  nLoop 2 nVisit 6 nEst 1048576 zName t2 zExplain {SCAN TABLE t2}
           50  +}
           51  +
           52  +do_execsql_test 1.3 {
           53  +  ANALYZE;
           54  +  SELECT count(*) FROM t1, t2;
           55  +} 6
           56  +do_scanstatus_test 1.4 {
           57  +  nLoop 1 nVisit 2 nEst 2 zName t1 zExplain {SCAN TABLE t1}
           58  +  nLoop 2 nVisit 6 nEst 3 zName t2 zExplain {SCAN TABLE t2}
           59  +}
           60  +
           61  +do_execsql_test 1.5 { ANALYZE }
           62  +do_execsql_test 1.6 {
           63  +  SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
           64  +} 4
           65  +do_scanstatus_test 1.7 {
           66  +  nLoop 1 nVisit 2 nEst 2 zName t2 zExplain 
           67  +  {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
           68  +  nLoop 2 nVisit 4 nEst 2 zName t1 zExplain {SCAN TABLE t1}
           69  +}
           70  +
           71  +do_execsql_test 1.8 {
           72  +  SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
           73  +} 4
           74  +
           75  +do_scanstatus_test 1.9 {
           76  +  nLoop 2 nVisit 4 nEst 2 zName t2 zExplain 
           77  +  {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
           78  +  nLoop 4 nVisit 8 nEst 2 zName t1 zExplain {SCAN TABLE t1}
           79  +}
           80  +
           81  +do_test 1.9 {
           82  +  sqlite3_stmt_scanstatus_reset [db_last_stmt_ptr db]
           83  +} {}
           84  +
           85  +do_scanstatus_test 1.10 {
           86  +  nLoop 0 nVisit 0 nEst 2 zName t2 zExplain 
           87  +  {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
           88  +  nLoop 0 nVisit 0 nEst 2 zName t1 zExplain {SCAN TABLE t1}
           89  +}
           90  +
           91  +#-------------------------------------------------------------------------
           92  +# Try a few different types of scans.
           93  +#
           94  +reset_db
           95  +do_execsql_test 2.1 {
           96  +  CREATE TABLE x1(i INTEGER PRIMARY KEY, j);
           97  +  INSERT INTO x1 VALUES(1, 'one');
           98  +  INSERT INTO x1 VALUES(2, 'two');
           99  +  INSERT INTO x1 VALUES(3, 'three');
          100  +  INSERT INTO x1 VALUES(4, 'four');
          101  +  CREATE INDEX x1j ON x1(j);
          102  +
          103  +  SELECT * FROM x1 WHERE i=2;
          104  +} {2 two}
          105  +
          106  +do_scanstatus_test 2.2 {
          107  +  nLoop 1 nVisit 1 nEst 1 zName x1 
          108  +  zExplain {SEARCH TABLE x1 USING INTEGER PRIMARY KEY (rowid=?)}
          109  +}
          110  +
          111  +do_execsql_test 2.3.1 {
          112  +  SELECT * FROM x1 WHERE j='two'
          113  +} {2 two}
          114  +do_scanstatus_test 2.3.2 {
          115  +  nLoop 1 nVisit 1 nEst 10 zName x1j 
          116  +  zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j=?)}
          117  +}
          118  +
          119  +do_execsql_test 2.4.1 {
          120  +  SELECT * FROM x1 WHERE j<'two'
          121  +} {4 four 1 one 3 three}
          122  +do_scanstatus_test 2.4.2 {
          123  +  nLoop 1 nVisit 3 nEst 262144 zName x1j 
          124  +  zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j<?)}
          125  +}
          126  +
          127  +do_execsql_test 2.5.1 {
          128  +  SELECT * FROM x1 WHERE j>='two'
          129  +} {2 two}
          130  +do_scanstatus_test 2.5.2 {
          131  +  nLoop 1 nVisit 1 nEst 262144 zName x1j 
          132  +  zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>?)}
          133  +}
          134  +
          135  +do_execsql_test 2.6.1 {
          136  +  SELECT * FROM x1 WHERE j BETWEEN 'three' AND 'two'
          137  +} {3 three 2 two}
          138  +do_scanstatus_test 2.6.2 {
          139  +  nLoop 1 nVisit 2 nEst 16384 zName x1j 
          140  +  zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>? AND j<?)}
          141  +}
          142  +
          143  +do_execsql_test 2.7.1 {
          144  +  CREATE TABLE x2(i INTEGER, j, k);
          145  +  INSERT INTO x2 SELECT i, j, i || ' ' || j FROM x1;
          146  +  CREATE INDEX x2j ON x2(j);
          147  +  CREATE INDEX x2ij ON x2(i, j);
          148  +  SELECT * FROM x2 WHERE j BETWEEN 'three' AND 'two'
          149  +} {3 three {3 three} 2 two {2 two}}
          150  +
          151  +do_scanstatus_test 2.7.2 {
          152  +  nLoop 1 nVisit 2 nEst 16384 zName x2j 
          153  +  zExplain {SEARCH TABLE x2 USING INDEX x2j (j>? AND j<?)}
          154  +}
          155  +
          156  +do_execsql_test 2.8.1 {
          157  +  SELECT * FROM x2 WHERE i=1 AND j='two'
          158  +}
          159  +do_scanstatus_test 2.8.2 {
          160  +  nLoop 1 nVisit 0 nEst 8 zName x2ij 
          161  +  zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
          162  +}
          163  +
          164  +do_execsql_test 2.9.1 {
          165  +  SELECT * FROM x2 WHERE i=5 AND j='two'
          166  +}
          167  +do_scanstatus_test 2.9.2 {
          168  +  nLoop 1 nVisit 0 nEst 8 zName x2ij 
          169  +  zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
          170  +}
          171  +
          172  +do_execsql_test 2.10.1 {
          173  +  SELECT * FROM x2 WHERE i=3 AND j='three'
          174  +} {3 three {3 three}}
          175  +do_scanstatus_test 2.10.2 {
          176  +  nLoop 1 nVisit 1 nEst 8 zName x2ij 
          177  +  zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
          178  +}
          179  +
          180  +#-------------------------------------------------------------------------
          181  +# Try with queries that use the OR optimization.
          182  +#
          183  +do_execsql_test 3.1 {
          184  +  CREATE TABLE a1(a, b, c, d);
          185  +  CREATE INDEX a1a ON a1(a);
          186  +  CREATE INDEX a1bc ON a1(b, c);
          187  +
          188  +  WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100)
          189  +  INSERT INTO a1 SELECT x, x, x, x FROM d;
          190  +}
          191  +
          192  +do_execsql_test 3.2.1 {
          193  +  SELECT d FROM a1 WHERE (a=4 OR b=13)
          194  +} {4 13}
          195  +do_scanstatus_test 3.2.2 {
          196  +  nLoop 1 nVisit 1 nEst 10 zName a1a 
          197  +  zExplain {SEARCH TABLE a1 USING INDEX a1a (a=?)}
          198  +  nLoop 1 nVisit 1 nEst 10 zName a1bc 
          199  +  zExplain {SEARCH TABLE a1 USING INDEX a1bc (b=?)}
          200  +}
          201  +
          202  +do_execsql_test 3.2.1 {
          203  +  SELECT count(*) FROM a1 WHERE (a BETWEEN 4 AND 12) OR (b BETWEEN 40 AND 60)
          204  +} {30}
          205  +do_scanstatus_test 3.2.2 {
          206  +  nLoop 1 nVisit 9 nEst 16384 zName a1a 
          207  +  zExplain {SEARCH TABLE a1 USING INDEX a1a (a>? AND a<?)}
          208  +  nLoop 1 nVisit 21 nEst 16384 zName a1bc
          209  +  zExplain {SEARCH TABLE a1 USING INDEX a1bc (b>? AND b<?)}
          210  +}
          211  +
          212  +do_execsql_test 3.3.1 {
          213  +  SELECT count(*) FROM a1 AS x, a1 AS y 
          214  +  WHERE (x.a BETWEEN 4 AND 12) AND (y.b BETWEEN 1 AND 10)
          215  +} {90}
          216  +do_scanstatus_test 3.2.2 {
          217  +  nLoop 1 nVisit 10 nEst 16384 zName a1bc 
          218  +  zExplain {SEARCH TABLE a1 AS y USING COVERING INDEX a1bc (b>? AND b<?)}
          219  +  nLoop 10 nVisit 90 nEst 16384 zName a1a
          220  +  zExplain {SEARCH TABLE a1 AS x USING COVERING INDEX a1a (a>? AND a<?)}
          221  +}
          222  +
          223  +do_execsql_test 3.4.1 {
          224  +  SELECT count(*) FROM a1 WHERE a IN (1, 5, 10, 15);
          225  +} {4}
          226  +do_scanstatus_test 3.4.2 {
          227  +  nLoop 1 nVisit 4 nEst 40 zName a1a 
          228  +  zExplain {SEARCH TABLE a1 USING COVERING INDEX a1a (a=?)}
          229  +}
          230  +
          231  +do_execsql_test 3.4.1 {
          232  +  SELECT count(*) FROM a1 WHERE rowid IN (1, 5, 10, 15);
          233  +} {4}
          234  +do_scanstatus_test 3.4.2 {
          235  +  nLoop 1 nVisit 4 nEst 4 zName a1
          236  +  zExplain {SEARCH TABLE a1 USING INTEGER PRIMARY KEY (rowid=?)}
          237  +}
          238  +
          239  +#-------------------------------------------------------------------------
          240  +# Test that scanstatus() data is not available for searches performed
          241  +# by triggers.
          242  +#
          243  +# It is available for searches performed as part of FK processing, but 
          244  +# not FK action processing.
          245  +#
          246  +do_execsql_test 4.0 {
          247  +  CREATE TABLE t1(a, b, c);
          248  +  CREATE TABLE t2(x PRIMARY KEY, y, z);
          249  +  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
          250  +    SELECT * FROM t2 WHERE x BETWEEN 20 AND 40;
          251  +  END;
          252  +  WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100)
          253  +  INSERT INTO t2 SELECT x, x*2, x*3 FROM d;
          254  +}
          255  +
          256  +do_execsql_test    4.1.1 { INSERT INTO t1 VALUES(1, 2, 3); }
          257  +do_scanstatus_test 4.1.2 { }
          258  +
          259  +do_execsql_test 4.2 {
          260  +  CREATE TABLE p1(x PRIMARY KEY);
          261  +  INSERT INTO p1 VALUES(1), (2), (3), (4);
          262  +  CREATE TABLE c1(y REFERENCES p1);
          263  +  INSERT INTO c1 VALUES(1), (2), (3);
          264  +  PRAGMA foreign_keys=on;
          265  +}
          266  +do_execsql_test    4.2.1 { DELETE FROM p1 WHERE x=4 }
          267  +do_scanstatus_test 4.2.2 { 
          268  +  nLoop 1 nVisit 1 nEst 1 zName sqlite_autoindex_p1_1 
          269  +  zExplain {SEARCH TABLE p1 USING INDEX sqlite_autoindex_p1_1 (x=?)}
          270  +
          271  +  nLoop 1 nVisit 3 nEst 524288 zName c1 zExplain {SCAN TABLE c1}
          272  +}
          273  +
          274  +#-------------------------------------------------------------------------
          275  +# Further tests of different scan types.
          276  +#
          277  +reset_db
          278  +proc tochar {i} {
          279  +  set alphabet {a b c d e f g h i j k l m n o p q r s t u v w x y z}
          280  +  return [lindex $alphabet [expr $i % [llength $alphabet]]]
          281  +}
          282  +db func tochar tochar
          283  +do_execsql_test 5.0 {
          284  +  CREATE TABLE t1(a PRIMARY KEY, b, c);
          285  +  INSERT INTO t1 VALUES(0, 1, 'a');
          286  +  INSERT INTO t1 VALUES(1, 0, 'b');
          287  +  INSERT INTO t1 VALUES(2, 1, 'c');
          288  +  INSERT INTO t1 VALUES(3, 0, 'd');
          289  +  INSERT INTO t1 VALUES(4, 1, 'e');
          290  +  INSERT INTO t1 VALUES(5, 0, 'a');
          291  +  INSERT INTO t1 VALUES(6, 1, 'b');
          292  +  INSERT INTO t1 VALUES(7, 0, 'c');
          293  +  INSERT INTO t1 VALUES(8, 1, 'd');
          294  +  INSERT INTO t1 VALUES(9, 0, 'e');
          295  +  CREATE INDEX t1bc ON t1(b, c);
          296  +
          297  +  CREATE TABLE t2(x, y);
          298  +  CREATE INDEX t2xy ON t2(x, y);
          299  +  WITH data(i, x, y) AS (
          300  +    SELECT 0, 0, tochar(0) 
          301  +    UNION ALL
          302  +    SELECT i+1, (i+1)%2, tochar(i+1) FROM data WHERE i<500
          303  +  ) INSERT INTO t2 SELECT x, y FROM data;
          304  +
          305  +  CREATE TABLE t3(x, y);
          306  +  INSERT INTO t3 SELECT * FROM t2;
          307  +
          308  +  ANALYZE;
          309  +}
          310  +
          311  +do_execsql_test 5.1.1 {
          312  +  SELECT count(*) FROM t1 WHERE a IN (SELECT b FROM t1 AS ii)
          313  +} {2}
          314  +do_scanstatus_test 5.1.2 { 
          315  +  nLoop 1 nVisit 10 nEst 10 zName t1bc 
          316  +  zExplain {SCAN TABLE t1 AS ii USING COVERING INDEX t1bc}
          317  +  nLoop 1 nVisit 2 nEst 8 zName sqlite_autoindex_t1_1
          318  +  zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
          319  +}
          320  +
          321  +do_execsql_test 5.2.1 {
          322  +  SELECT count(*) FROM t1 WHERE a IN (0, 1)
          323  +} {2}
          324  +do_scanstatus_test 5.2.2 { 
          325  +  nLoop 1 nVisit 2 nEst 2 zName sqlite_autoindex_t1_1
          326  +  zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
          327  +}
          328  +
          329  +do_eqp_test 5.3.1 {
          330  +  SELECT count(*) FROM t2 WHERE y = 'j';
          331  +} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
          332  +do_execsql_test 5.3.2 {
          333  +  SELECT count(*) FROM t2 WHERE y = 'j';
          334  +} {19}
          335  +do_scanstatus_test 5.3.3 { 
          336  +  nLoop 1 nVisit 19 nEst 56 zName t2xy zExplain
          337  +  {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
          338  +}
          339  +
          340  +do_eqp_test 5.4.1 {
          341  +  SELECT count(*) FROM t1, t2 WHERE y = c;
          342  +} {
          343  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
          344  +  0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
          345  +}
          346  +do_execsql_test 5.4.2 {
          347  +  SELECT count(*) FROM t1, t2 WHERE y = c;
          348  +} {200}
          349  +do_scanstatus_test 5.4.3 { 
          350  +  nLoop 1 nVisit 10 nEst 10 zName t1bc 
          351  +  zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
          352  +  nLoop 10 nVisit 200 nEst 56 zName t2xy 
          353  +  zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
          354  +}
          355  +
          356  +do_eqp_test 5.5.1 {
          357  +  SELECT count(*) FROM t1, t3 WHERE y = c;
          358  +} {
          359  +  0 0 1 {SCAN TABLE t3} 
          360  +  0 1 0 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
          361  +}
          362  +do_execsql_test 5.5.2 {
          363  +  SELECT count(*) FROM t1, t3 WHERE y = c;
          364  +} {200}
          365  +do_scanstatus_test 5.5.3 { 
          366  +  nLoop 1 nVisit 501 nEst 480 zName t3 zExplain {SCAN TABLE t3}
          367  +  nLoop 501 nVisit 200 nEst 20 zName auto-index zExplain
          368  +  {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
          369  +}
          370  +
          371  +#-------------------------------------------------------------------------
          372  +# Virtual table scans
          373  +#
          374  +ifcapable fts3 {
          375  +  do_execsql_test 6.0 {
          376  +    CREATE VIRTUAL TABLE ft1 USING fts4;
          377  +    INSERT INTO ft1 VALUES('a d c f g h e i f c');
          378  +    INSERT INTO ft1 VALUES('g c h b g b f f f g');
          379  +    INSERT INTO ft1 VALUES('h h c c h f a e d d');
          380  +    INSERT INTO ft1 VALUES('e j i j i e b c f g');
          381  +    INSERT INTO ft1 VALUES('g f b g j c h a d f');
          382  +    INSERT INTO ft1 VALUES('j i a e g f a i a c');
          383  +    INSERT INTO ft1 VALUES('f d g g j j c a h g');
          384  +    INSERT INTO ft1 VALUES('b d h a d j j j b i');
          385  +    INSERT INTO ft1 VALUES('j e a b j e c b c i');
          386  +    INSERT INTO ft1 VALUES('a d e f b j j c g d');
          387  +  }
          388  +  do_execsql_test 6.1.1 {
          389  +    SELECT count(*) FROM ft1 WHERE ft1 MATCH 'd'
          390  +  } {6}
          391  +  do_scanstatus_test 6.1.2 { 
          392  +    nLoop 1 nVisit 6 nEst 24 zName ft1 zExplain 
          393  +    {SCAN TABLE ft1 VIRTUAL TABLE INDEX 3:}
          394  +  }
          395  +}
          396  +
          397  +
          398  +finish_test