/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/sqlite.h.in.

7407
7408
7409
7410
7411
7412
7413















































































7414
7415
7416
7417
7418
7419
7420
*/
#define SQLITE_ROLLBACK 1
/* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */
#define SQLITE_FAIL     3
/* #define SQLITE_ABORT 4  // Also an error code */
#define SQLITE_REPLACE  5


















































































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







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







7407
7408
7409
7410
7411
7412
7413
7414
7415
7416
7417
7418
7419
7420
7421
7422
7423
7424
7425
7426
7427
7428
7429
7430
7431
7432
7433
7434
7435
7436
7437
7438
7439
7440
7441
7442
7443
7444
7445
7446
7447
7448
7449
7450
7451
7452
7453
7454
7455
7456
7457
7458
7459
7460
7461
7462
7463
7464
7465
7466
7467
7468
7469
7470
7471
7472
7473
7474
7475
7476
7477
7478
7479
7480
7481
7482
7483
7484
7485
7486
7487
7488
7489
7490
7491
7492
7493
7494
7495
7496
7497
7498
7499
*/
#define SQLITE_ROLLBACK 1
/* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */
#define SQLITE_FAIL     3
/* #define SQLITE_ABORT 4  // Also an error code */
#define SQLITE_REPLACE  5

/* CAPI3REF: Prepared Statement Scan Status Opcodes
** KEYWORDS: {scanstatus option}
**
** The following constants can be used for the T parameter to the
** [sqlite3_stmt_scanstatus(S,X,T,V)] interface.  Each constant designates a
** different metric for sqlite3_stmt_scanstatus() to return.
**
** <dl>
** [[SQLITE_SCANSTAT_NLOOP]] <td>SQLITE_SCANSTAT_NLOOP</dt>
** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
** total number of times that the X-th loop has run.</dd>
**
** [[SQLITE_SCANSTAT_NVISIT]] <td>SQLITE_SCANSTAT_NVISIT</dt>
** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
** total number of rows visited by the X-th loop.</dd>
**
** [[SQLITE_SCANSTAT_EST]] <td>SQLITE_SCANSTAT_EST</dt>
** <dd>The [sqlite3_int64] variable pointed to by the T parameter will be set to the
** query planner's estimate for the number of rows visited for each
** iteration of the X-th loop.  If the query planner's estimate was accurate,
** then this value should be approximately NVISIT/NLOOP.
**
** [[SQLITE_SCANSTAT_NAME]] <td>SQLITE_SCANSTAT_NAME</dt>
** <dd>The "const char *" variable pointed to by the T parameter will be set to 
** a zero-terminated UTF-8 string containing the name of the index or table used
** for the X-th loop.
**
** [[SQLITE_SCANSTAT_EXPLAIN]] <td>SQLITE_SCANSTAT_EXPLAIN</dt>
** <dd>The "const char *" variable pointed to by the T parameter will be set to 
** a zero-terminated UTF-8 string containing the [EXPLAIN QUERY PLAN] description
** for the X-th loop.
** </dl>
*/
#define SQLITE_SCANSTAT_NLOOP    0
#define SQLITE_SCANSTAT_NVISIT   1
#define SQLITE_SCANSTAT_EST      2
#define SQLITE_SCANSTAT_NAME     3
#define SQLITE_SCANSTAT_EXPLAIN  4

/*
** CAPI3REF: Prepared Statement Scan Status
**
** Return status data for a single loop within query pStmt.
**
** The "iScanStatusOp" parameter determines which status information to return.
** The "iScanStatusOp" must be one of the [scanstatus options] or the behavior of
** this interface is undefined.
** The requested measurement is written into a variable pointed to by
** the "pOut" parameter.
** Parameter "idx" identifies the specific loop to retrieve statistics for.
** Loops are numbered starting from zero. If idx is out of range - less than
** zero or greater than or equal to the total number of loops used to implement
** the statement - a non-zero value is returned and the variable that pOut
** points to is unchanged.
**
** Statistics might not be available for all loops in all statements. In cases
** where there exist loops with no available statistics, this function behaves
** as if the loop did not exist - it returns non-zero and leave the variable
** that pOut points to unchanged.
**
** This API is only available if the library is built with pre-processor
** symbol [SQLITE_ENABLE_STMT_SCANSTATUS] defined.
*/
SQLITE_EXPERIMENTAL int sqlite3_stmt_scanstatus(
  sqlite3_stmt *pStmt,      /* Prepared statement for which info desired */
  int idx,                  /* Index of loop to report on */
  int iScanStatusOp,        /* Information desired.  SQLITE_SCANSTAT_* */
  void *pOut                /* Result written here */
);     

/*
** CAPI3REF: Zero Scan-Status Counters
**
** Zero all sqlite3_stmt_scanstatus() related event counters.
**
** This API is only available if the library is built with pre-processor
** symbol [SQLITE_ENABLE_STMT_SCANSTATUS] defined.
*/
SQLITE_EXPERIMENTAL void sqlite3_stmt_scanstatus_reset(sqlite3_stmt*);


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

Changes to src/tclsqlite.c.

3637
3638
3639
3640
3641
3642
3643







































3644
3645
3646
3647
3648
3649
3650
....
3756
3757
3758
3759
3760
3761
3762



3763
3764
3765
3766
3767
3768
3769
  }

  pDb->bLegacyPrepare = bPrepare;

  Tcl_ResetResult(interp);
  return TCL_OK;
}







































#endif

/*
** Configure the interpreter passed as the first argument to have access
** to the commands and linked variables that make up:
**
**   * the [sqlite3] extension itself, 
................................................................................

    Tcl_CreateObjCommand(
        interp, "load_testfixture_extensions", init_all_cmd, 0, 0
    );
    Tcl_CreateObjCommand(
        interp, "db_use_legacy_prepare", db_use_legacy_prepare_cmd, 0, 0
    );




#ifdef SQLITE_SSE
    Sqlitetestsse_Init(interp);
#endif
  }
#endif
}







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>







3637
3638
3639
3640
3641
3642
3643
3644
3645
3646
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
....
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
  }

  pDb->bLegacyPrepare = bPrepare;

  Tcl_ResetResult(interp);
  return TCL_OK;
}

/*
** Tclcmd: db_last_stmt_ptr DB
**
**   If the statement cache associated with database DB is not empty,
**   return the text representation of the most recently used statement
**   handle.
*/
static int db_last_stmt_ptr(
  ClientData cd,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  extern int sqlite3TestMakePointerStr(Tcl_Interp*, char*, void*);
  Tcl_CmdInfo cmdInfo;
  SqliteDb *pDb;
  sqlite3_stmt *pStmt = 0;
  char zBuf[100];

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB");
    return TCL_ERROR;
  }

  if( !Tcl_GetCommandInfo(interp, Tcl_GetString(objv[1]), &cmdInfo) ){
    Tcl_AppendResult(interp, "no such db: ", Tcl_GetString(objv[1]), (char*)0);
    return TCL_ERROR;
  }
  pDb = (SqliteDb*)cmdInfo.objClientData;

  if( pDb->stmtList ) pStmt = pDb->stmtList->pStmt;
  if( sqlite3TestMakePointerStr(interp, zBuf, pStmt) ){
    return TCL_ERROR;
  }
  Tcl_SetResult(interp, zBuf, TCL_VOLATILE);

  return TCL_OK;
}
#endif

/*
** Configure the interpreter passed as the first argument to have access
** to the commands and linked variables that make up:
**
**   * the [sqlite3] extension itself, 
................................................................................

    Tcl_CreateObjCommand(
        interp, "load_testfixture_extensions", init_all_cmd, 0, 0
    );
    Tcl_CreateObjCommand(
        interp, "db_use_legacy_prepare", db_use_legacy_prepare_cmd, 0, 0
    );
    Tcl_CreateObjCommand(
        interp, "db_last_stmt_ptr", db_last_stmt_ptr, 0, 0
    );

#ifdef SQLITE_SSE
    Sqlitetestsse_Init(interp);
#endif
  }
#endif
}

Changes to src/test1.c.

2297
2298
2299
2300
2301
2302
2303







































































2304
2305
2306
2307
2308
2309
2310
....
6864
6865
6866
6867
6868
6869
6870




6871
6872
6873
6874
6875
6876
6877
  }
  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,
................................................................................
     { "sorter_test_sort4_helper", sorter_test_sort4_helper },
#ifdef SQLITE_USER_AUTHENTICATION
     { "sqlite3_user_authenticate", test_user_authenticate, 0 },
     { "sqlite3_user_add",          test_user_add,          0 },
     { "sqlite3_user_change",       test_user_change,       0 },
     { "sqlite3_user_delete",       test_user_delete,       0 },
#endif





  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_like_count;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>







2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
....
6935
6936
6937
6938
6939
6940
6941
6942
6943
6944
6945
6946
6947
6948
6949
6950
6951
6952
  }
  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;
}

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
/*
** Usage:  sqlite3_stmt_scanstatus STMT IDX
*/
static int test_stmt_scanstatus(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  sqlite3_stmt *pStmt;            /* First argument */
  int idx;                        /* Second argument */

  const char *zName;
  const char *zExplain;
  sqlite3_int64 nLoop;
  sqlite3_int64 nVisit;
  sqlite3_int64 nEst;
  int res;

  if( objc!=3 ){
    Tcl_WrongNumArgs(interp, 1, objv, "STMT IDX");
    return TCL_ERROR;
  }
  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  if( Tcl_GetIntFromObj(interp, objv[2], &idx) ) return TCL_ERROR;

  res = sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop);
  if( res==0 ){
    Tcl_Obj *pRet = Tcl_NewObj();
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nLoop", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nLoop));
    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nVisit", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nVisit));
    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EST, (void*)&nEst);
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nEst", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nEst));
    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NAME, (void*)&zName);
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zName", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zName, -1));
    sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zExplain", -1));
    Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zExplain, -1));
    Tcl_SetObjResult(interp, pRet);
  }else{
    Tcl_ResetResult(interp);
  }
  return TCL_OK;
}

/*
** Usage:  sqlite3_stmt_scanstatus_reset  STMT
*/
static int test_stmt_scanstatus_reset(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  sqlite3_stmt *pStmt;            /* First argument */
  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "STMT");
    return TCL_ERROR;
  }
  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  sqlite3_stmt_scanstatus_reset(pStmt);
  return TCL_OK;
}
#endif

/*
** Usage:  sqlite3_next_stmt  DB  STMT
**
** Return the next statment in sequence after STMT.
*/
static int test_next_stmt(
  void * clientData,
................................................................................
     { "sorter_test_sort4_helper", sorter_test_sort4_helper },
#ifdef SQLITE_USER_AUTHENTICATION
     { "sqlite3_user_authenticate", test_user_authenticate, 0 },
     { "sqlite3_user_add",          test_user_add,          0 },
     { "sqlite3_user_change",       test_user_change,       0 },
     { "sqlite3_user_delete",       test_user_delete,       0 },
#endif
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
     { "sqlite3_stmt_scanstatus",       test_stmt_scanstatus,   0 },
     { "sqlite3_stmt_scanstatus_reset", test_stmt_scanstatus_reset,   0 },
#endif

  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_like_count;

Changes to src/test_config.c.

479
480
481
482
483
484
485






486
487
488
489
490
491
492
  Tcl_SetVar2(interp, "sqlite_options", "stat4", "0", TCL_GLOBAL_ONLY);
#endif
#if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4)
  Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY);
#endif







#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
#  if defined(__APPLE__)
#    define SQLITE_ENABLE_LOCKING_STYLE 1
#  else
#    define SQLITE_ENABLE_LOCKING_STYLE 0
#  endif







>
>
>
>
>
>







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

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  Tcl_SetVar2(interp, "sqlite_options", "scanstatus", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "scanstatus", "0", TCL_GLOBAL_ONLY);
#endif

#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
#  if defined(__APPLE__)
#    define SQLITE_ENABLE_LOCKING_STYLE 1
#  else
#    define SQLITE_ENABLE_LOCKING_STYLE 0
#  endif

Changes to src/vdbe.c.

604
605
606
607
608
609
610



611
612
613
614
615
616
617
....
4484
4485
4486
4487
4488
4489
4490
4491
4492
4493
4494
4495
4496
4497
4498
4499
4500
....
5402
5403
5404
5405
5406
5407
5408



5409
5410
5411
5412
5413
5414
5415
....
5430
5431
5432
5433
5434
5435
5436



5437
5438
5439
5440
5441
5442
5443
    assert( pc>=0 && pc<p->nOp );
    if( db->mallocFailed ) goto no_mem;
#ifdef VDBE_PROFILE
    start = sqlite3Hwtime();
#endif
    nVmStep++;
    pOp = &aOp[pc];




    /* Only allow tracing if SQLITE_DEBUG is defined.
    */
#ifdef SQLITE_DEBUG
    if( db->flags & SQLITE_VdbeTrace ){
      sqlite3VdbePrintOp(stdout, pc, pOp);
    }
................................................................................
  p->aCounter[SQLITE_STMTSTATUS_SORT]++;
  /* Fall through into OP_Rewind */
}
/* Opcode: Rewind P1 P2 * * *
**
** The next use of the Rowid or Column or Next instruction for P1 
** will refer to the first entry in the database table or index.
** If the table or index is empty and P2>0, then jump immediately to P2.
** If P2 is 0 or if the table or index is not empty, fall through
** to the following instruction.
**
** This opcode leaves the cursor configured to move in forward order,
** from the beginning toward the end.  In other words, the cursor is
** configured to use Next, not Prev.
*/
case OP_Rewind: {        /* jump */
  VdbeCursor *pC;
................................................................................
    pFrame->apCsr = p->apCsr;
    pFrame->nCursor = p->nCursor;
    pFrame->aOp = p->aOp;
    pFrame->nOp = p->nOp;
    pFrame->token = pProgram->token;
    pFrame->aOnceFlag = p->aOnceFlag;
    pFrame->nOnceFlag = p->nOnceFlag;




    pEnd = &VdbeFrameMem(pFrame)[pFrame->nChildMem];
    for(pMem=VdbeFrameMem(pFrame); pMem!=pEnd; pMem++){
      pMem->flags = MEM_Undefined;
      pMem->db = db;
    }
  }else{
................................................................................
  p->nMem = pFrame->nChildMem;
  p->nCursor = (u16)pFrame->nChildCsr;
  p->apCsr = (VdbeCursor **)&aMem[p->nMem+1];
  p->aOp = aOp = pProgram->aOp;
  p->nOp = pProgram->nOp;
  p->aOnceFlag = (u8 *)&p->apCsr[p->nCursor];
  p->nOnceFlag = pProgram->nOnce;



  pc = -1;
  memset(p->aOnceFlag, 0, p->nOnceFlag);

  break;
}

/* Opcode: Param P1 P2 * * *







>
>
>







 







|
|
|







 







>
>
>







 







>
>
>







604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
....
4487
4488
4489
4490
4491
4492
4493
4494
4495
4496
4497
4498
4499
4500
4501
4502
4503
....
5405
5406
5407
5408
5409
5410
5411
5412
5413
5414
5415
5416
5417
5418
5419
5420
5421
....
5436
5437
5438
5439
5440
5441
5442
5443
5444
5445
5446
5447
5448
5449
5450
5451
5452
    assert( pc>=0 && pc<p->nOp );
    if( db->mallocFailed ) goto no_mem;
#ifdef VDBE_PROFILE
    start = sqlite3Hwtime();
#endif
    nVmStep++;
    pOp = &aOp[pc];
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
    if( p->anExec ) p->anExec[pc]++;
#endif

    /* Only allow tracing if SQLITE_DEBUG is defined.
    */
#ifdef SQLITE_DEBUG
    if( db->flags & SQLITE_VdbeTrace ){
      sqlite3VdbePrintOp(stdout, pc, pOp);
    }
................................................................................
  p->aCounter[SQLITE_STMTSTATUS_SORT]++;
  /* Fall through into OP_Rewind */
}
/* Opcode: Rewind P1 P2 * * *
**
** The next use of the Rowid or Column or Next instruction for P1 
** will refer to the first entry in the database table or index.
** If the table or index is empty, jump immediately to P2.
** If the table or index is not empty, fall through to the following 
** instruction.
**
** This opcode leaves the cursor configured to move in forward order,
** from the beginning toward the end.  In other words, the cursor is
** configured to use Next, not Prev.
*/
case OP_Rewind: {        /* jump */
  VdbeCursor *pC;
................................................................................
    pFrame->apCsr = p->apCsr;
    pFrame->nCursor = p->nCursor;
    pFrame->aOp = p->aOp;
    pFrame->nOp = p->nOp;
    pFrame->token = pProgram->token;
    pFrame->aOnceFlag = p->aOnceFlag;
    pFrame->nOnceFlag = p->nOnceFlag;
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
    pFrame->anExec = p->anExec;
#endif

    pEnd = &VdbeFrameMem(pFrame)[pFrame->nChildMem];
    for(pMem=VdbeFrameMem(pFrame); pMem!=pEnd; pMem++){
      pMem->flags = MEM_Undefined;
      pMem->db = db;
    }
  }else{
................................................................................
  p->nMem = pFrame->nChildMem;
  p->nCursor = (u16)pFrame->nChildCsr;
  p->apCsr = (VdbeCursor **)&aMem[p->nMem+1];
  p->aOp = aOp = pProgram->aOp;
  p->nOp = pProgram->nOp;
  p->aOnceFlag = (u8 *)&p->apCsr[p->nCursor];
  p->nOnceFlag = pProgram->nOnce;
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  p->anExec = 0;
#endif
  pc = -1;
  memset(p->aOnceFlag, 0, p->nOnceFlag);

  break;
}

/* Opcode: Param P1 P2 * * *

Changes to src/vdbe.h.

277
278
279
280
281
282
283
284




285


#else
# define VdbeCoverage(v)
# define VdbeCoverageIf(v,x)
# define VdbeCoverageAlwaysTaken(v)
# define VdbeCoverageNeverTaken(v)
# define VDBE_OFFSET_LINENO(x) 0
#endif





#endif










>
>
>
>

>
>
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
#else
# define VdbeCoverage(v)
# define VdbeCoverageIf(v,x)
# define VdbeCoverageAlwaysTaken(v)
# define VdbeCoverageNeverTaken(v)
# define VDBE_OFFSET_LINENO(x) 0
#endif

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
void sqlite3VdbeScanStatus(Vdbe*, int, int, int, i64, const char*);
#else
# define sqlite3VdbeScanStatus(a,b,c,d,e)
#endif

#endif

Changes to src/vdbeInt.h.

128
129
130
131
132
133
134

135
136
137
138
139
140
141
...
292
293
294
295
296
297
298









299
300
301
302
303
304
305
...
364
365
366
367
368
369
370





371
372
373
374
375
376
377
** set to NULL if the currently executing frame is the main program.
*/
typedef struct VdbeFrame VdbeFrame;
struct VdbeFrame {
  Vdbe *v;                /* VM this frame belongs to */
  VdbeFrame *pParent;     /* Parent of this frame, or NULL if parent is main */
  Op *aOp;                /* Program instructions for parent frame */

  Mem *aMem;              /* Array of memory cells for parent frame */
  u8 *aOnceFlag;          /* Array of OP_Once flags for parent frame */
  VdbeCursor **apCsr;     /* Array of Vdbe cursors for parent frame */
  void *token;            /* Copy of SubProgram.token */
  i64 lastRowid;          /* Last insert rowid (sqlite3.lastRowid) */
  int nCursor;            /* Number of entries in apCsr */
  int pc;                 /* Program Counter in parent (calling) frame */
................................................................................
};

/* A bitfield type for use inside of structures.  Always follow with :N where
** N is the number of bits.
*/
typedef unsigned bft;  /* Bit Field Type */










/*
** An instance of the virtual machine.  This structure contains the complete
** state of the virtual machine.
**
** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare()
** is really a pointer to an instance of this structure.
**
................................................................................
  VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */
  int nFrame;             /* Number of frames in pFrame list */
  u32 expmask;            /* Binding to these vars invalidates VM */
  SubProgram *pProgram;   /* Linked list of all sub-programs used by VM */
  int nOnceFlag;          /* Size of array aOnceFlag[] */
  u8 *aOnceFlag;          /* Flags for OP_Once */
  AuxData *pAuxData;      /* Linked list of auxdata allocations */





};

/*
** The following are allowed values for Vdbe.magic
*/
#define VDBE_MAGIC_INIT     0x26bceaa5    /* Building a VDBE program */
#define VDBE_MAGIC_RUN      0xbdf20da3    /* VDBE is ready to execute */







>







 







>
>
>
>
>
>
>
>
>







 







>
>
>
>
>







128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
...
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
...
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
** set to NULL if the currently executing frame is the main program.
*/
typedef struct VdbeFrame VdbeFrame;
struct VdbeFrame {
  Vdbe *v;                /* VM this frame belongs to */
  VdbeFrame *pParent;     /* Parent of this frame, or NULL if parent is main */
  Op *aOp;                /* Program instructions for parent frame */
  i64 *anExec;            /* Event counters from parent frame */
  Mem *aMem;              /* Array of memory cells for parent frame */
  u8 *aOnceFlag;          /* Array of OP_Once flags for parent frame */
  VdbeCursor **apCsr;     /* Array of Vdbe cursors for parent frame */
  void *token;            /* Copy of SubProgram.token */
  i64 lastRowid;          /* Last insert rowid (sqlite3.lastRowid) */
  int nCursor;            /* Number of entries in apCsr */
  int pc;                 /* Program Counter in parent (calling) frame */
................................................................................
};

/* A bitfield type for use inside of structures.  Always follow with :N where
** N is the number of bits.
*/
typedef unsigned bft;  /* Bit Field Type */

typedef struct ScanStatus ScanStatus;
struct ScanStatus {
  int addrExplain;                /* OP_Explain for loop */
  int addrLoop;                   /* Address of "loops" counter */
  int addrVisit;                  /* Address of "rows visited" counter */
  i64 nEst;                       /* Estimated rows per loop */
  char *zName;                    /* Name of table or index */
};

/*
** An instance of the virtual machine.  This structure contains the complete
** state of the virtual machine.
**
** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare()
** is really a pointer to an instance of this structure.
**
................................................................................
  VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */
  int nFrame;             /* Number of frames in pFrame list */
  u32 expmask;            /* Binding to these vars invalidates VM */
  SubProgram *pProgram;   /* Linked list of all sub-programs used by VM */
  int nOnceFlag;          /* Size of array aOnceFlag[] */
  u8 *aOnceFlag;          /* Flags for OP_Once */
  AuxData *pAuxData;      /* Linked list of auxdata allocations */
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  i64 *anExec;            /* Number of times each op has been executed */
  int nScan;              /* Entries in aScan[] */
  ScanStatus *aScan;      /* Scan definitions for sqlite3_stmt_scanstatus() */
#endif
};

/*
** The following are allowed values for Vdbe.magic
*/
#define VDBE_MAGIC_INIT     0x26bceaa5    /* Building a VDBE program */
#define VDBE_MAGIC_RUN      0xbdf20da3    /* VDBE is ready to execute */

Changes to src/vdbeapi.c.

1471
1472
1473
1474
1475
1476
1477























































    return 0;
  }
#endif
  v = pVdbe->aCounter[op];
  if( resetFlag ) pVdbe->aCounter[op] = 0;
  return (int)v;
}






























































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
    return 0;
  }
#endif
  v = pVdbe->aCounter[op];
  if( resetFlag ) pVdbe->aCounter[op] = 0;
  return (int)v;
}

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
/*
** Return status data for a single loop within query pStmt.
*/
int sqlite3_stmt_scanstatus(
  sqlite3_stmt *pStmt,            /* Prepared statement being queried */
  int idx,                        /* Index of loop to report on */
  int iScanStatusOp,              /* Which metric to return */
  void *pOut                      /* OUT: Write the answer here */
){
  Vdbe *p = (Vdbe*)pStmt;
  ScanStatus *pScan;
  if( idx<0 || idx>=p->nScan ) return 1;
  pScan = &p->aScan[idx];
  switch( iScanStatusOp ){
    case SQLITE_SCANSTAT_NLOOP: {
      *(sqlite3_int64*)pOut = p->anExec[pScan->addrLoop];
      break;
    }
    case SQLITE_SCANSTAT_NVISIT: {
      *(sqlite3_int64*)pOut = p->anExec[pScan->addrVisit];
      break;
    }
    case SQLITE_SCANSTAT_EST: {
      *(sqlite3_int64*)pOut = pScan->nEst;
      break;
    }
    case SQLITE_SCANSTAT_NAME: {
      *(const char**)pOut = pScan->zName;
      break;
    }
    case SQLITE_SCANSTAT_EXPLAIN: {
      if( pScan->addrExplain ){
        *(const char**)pOut = p->aOp[ pScan->addrExplain ].p4.z;
      }else{
        *(const char**)pOut = 0;
      }
      break;
    }
    default: {
      return 1;
    }
  }
  return 0;
}

/*
** Zero all counters associated with the sqlite3_stmt_scanstatus() data.
*/
void sqlite3_stmt_scanstatus_reset(sqlite3_stmt *pStmt){
  Vdbe *p = (Vdbe*)pStmt;
  memset(p->anExec, 0, p->nOp * sizeof(i64));
}
#endif /* SQLITE_ENABLE_STMT_SCANSTATUS */

Changes to src/vdbeaux.c.

592
593
594
595
596
597
598




























599
600
601
602
603
604
605
....
1691
1692
1693
1694
1695
1696
1697



1698
1699
1700
1701
1702
1703
1704
....
1758
1759
1760
1761
1762
1763
1764



1765
1766
1767
1768
1769
1770
1771
....
2681
2682
2683
2684
2685
2686
2687






2688
2689
2690
2691
2692
2693
2694
      }
#endif
    }
    p->nOp += nOp;
  }
  return addr;
}





























/*
** Change the value of the P1 operand for a specific instruction.
** This routine is useful when a large program is loaded from a
** static array using sqlite3VdbeAddOpList but we want to make a
** few minor changes to the program.
*/
................................................................................
    p->aMem = allocSpace(p->aMem, nMem*sizeof(Mem), &zCsr, zEnd, &nByte);
    p->aVar = allocSpace(p->aVar, nVar*sizeof(Mem), &zCsr, zEnd, &nByte);
    p->apArg = allocSpace(p->apArg, nArg*sizeof(Mem*), &zCsr, zEnd, &nByte);
    p->azVar = allocSpace(p->azVar, nVar*sizeof(char*), &zCsr, zEnd, &nByte);
    p->apCsr = allocSpace(p->apCsr, nCursor*sizeof(VdbeCursor*),
                          &zCsr, zEnd, &nByte);
    p->aOnceFlag = allocSpace(p->aOnceFlag, nOnce, &zCsr, zEnd, &nByte);



    if( nByte ){
      p->pFree = sqlite3DbMallocZero(db, nByte);
    }
    zCsr = p->pFree;
    zEnd = &zCsr[nByte];
  }while( nByte && !db->mallocFailed );

................................................................................
/*
** Copy the values stored in the VdbeFrame structure to its Vdbe. This
** is used, for example, when a trigger sub-program is halted to restore
** control to the main program.
*/
int sqlite3VdbeFrameRestore(VdbeFrame *pFrame){
  Vdbe *v = pFrame->v;



  v->aOnceFlag = pFrame->aOnceFlag;
  v->nOnceFlag = pFrame->nOnceFlag;
  v->aOp = pFrame->aOp;
  v->nOp = pFrame->nOp;
  v->aMem = pFrame->aMem;
  v->nMem = pFrame->nMem;
  v->apCsr = pFrame->apCsr;
................................................................................
    sqlite3DbFree(db, pSub);
  }
  for(i=p->nzVar-1; i>=0; i--) sqlite3DbFree(db, p->azVar[i]);
  vdbeFreeOpArray(db, p->aOp, p->nOp);
  sqlite3DbFree(db, p->aColName);
  sqlite3DbFree(db, p->zSql);
  sqlite3DbFree(db, p->pFree);






}

/*
** Delete an entire VDBE.
*/
void sqlite3VdbeDelete(Vdbe *p){
  sqlite3 *db;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>







 







>
>
>







 







>
>
>
>
>
>







592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
....
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
....
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
....
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
      }
#endif
    }
    p->nOp += nOp;
  }
  return addr;
}

#if defined(SQLITE_ENABLE_STMT_SCANSTATUS)
/*
** Add an entry to the array of counters managed by sqlite3_stmt_scanstatus().
*/
void sqlite3VdbeScanStatus(
  Vdbe *p,                        /* VM to add scanstatus() to */
  int addrExplain,                /* Address of OP_Explain (or 0) */
  int addrLoop,                   /* Address of loop counter */ 
  int addrVisit,                  /* Address of rows visited counter */
  i64 nEst,                       /* Estimated number of rows */
  const char *zName               /* Name of table or index being scanned */
){
  int nByte = (p->nScan+1) * sizeof(ScanStatus);
  ScanStatus *aNew;
  aNew = (ScanStatus*)sqlite3DbRealloc(p->db, p->aScan, nByte);
  if( aNew ){
    ScanStatus *pNew = &aNew[p->nScan++];
    pNew->addrExplain = addrExplain;
    pNew->addrLoop = addrLoop;
    pNew->addrVisit = addrVisit;
    pNew->nEst = nEst;
    pNew->zName = sqlite3DbStrDup(p->db, zName);
    p->aScan = aNew;
  }
}
#endif


/*
** Change the value of the P1 operand for a specific instruction.
** This routine is useful when a large program is loaded from a
** static array using sqlite3VdbeAddOpList but we want to make a
** few minor changes to the program.
*/
................................................................................
    p->aMem = allocSpace(p->aMem, nMem*sizeof(Mem), &zCsr, zEnd, &nByte);
    p->aVar = allocSpace(p->aVar, nVar*sizeof(Mem), &zCsr, zEnd, &nByte);
    p->apArg = allocSpace(p->apArg, nArg*sizeof(Mem*), &zCsr, zEnd, &nByte);
    p->azVar = allocSpace(p->azVar, nVar*sizeof(char*), &zCsr, zEnd, &nByte);
    p->apCsr = allocSpace(p->apCsr, nCursor*sizeof(VdbeCursor*),
                          &zCsr, zEnd, &nByte);
    p->aOnceFlag = allocSpace(p->aOnceFlag, nOnce, &zCsr, zEnd, &nByte);
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
    p->anExec = allocSpace(p->anExec, p->nOp*sizeof(i64), &zCsr, zEnd, &nByte);
#endif
    if( nByte ){
      p->pFree = sqlite3DbMallocZero(db, nByte);
    }
    zCsr = p->pFree;
    zEnd = &zCsr[nByte];
  }while( nByte && !db->mallocFailed );

................................................................................
/*
** Copy the values stored in the VdbeFrame structure to its Vdbe. This
** is used, for example, when a trigger sub-program is halted to restore
** control to the main program.
*/
int sqlite3VdbeFrameRestore(VdbeFrame *pFrame){
  Vdbe *v = pFrame->v;
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  v->anExec = pFrame->anExec;
#endif
  v->aOnceFlag = pFrame->aOnceFlag;
  v->nOnceFlag = pFrame->nOnceFlag;
  v->aOp = pFrame->aOp;
  v->nOp = pFrame->nOp;
  v->aMem = pFrame->aMem;
  v->nMem = pFrame->nMem;
  v->apCsr = pFrame->apCsr;
................................................................................
    sqlite3DbFree(db, pSub);
  }
  for(i=p->nzVar-1; i>=0; i--) sqlite3DbFree(db, p->azVar[i]);
  vdbeFreeOpArray(db, p->aOp, p->nOp);
  sqlite3DbFree(db, p->aColName);
  sqlite3DbFree(db, p->zSql);
  sqlite3DbFree(db, p->pFree);
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  for(i=0; i<p->nScan; i++){
    sqlite3DbFree(db, p->aScan[i].zName);
  }
  sqlite3DbFree(db, p->aScan);
#endif
}

/*
** Delete an entire VDBE.
*/
void sqlite3VdbeDelete(Vdbe *p){
  sqlite3 *db;

Changes to src/where.c.

2804
2805
2806
2807
2808
2809
2810
2811

2812
2813



2814
2815
2816
2817
2818
2819
2820
2821
2822
2823

2824
2825
2826
2827
2828
2829
2830
....
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
....
2905
2906
2907
2908
2909
2910
2911
2912
2913

2914
2915
2916
2917




































2918
2919
2920
2921
2922
2923
2924
....
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587


3588
3589
3590
3591
3592
3593
3594
....
3710
3711
3712
3713
3714
3715
3716




3717
3718
3719
3720
3721
3722
3723
....
6412
6413
6414
6415
6416
6417
6418


6419

6420
6421
6422
6423
6424
6425
6426

6427

6428
6429
6430



6431
6432
6433
6434
6435
6436
6437
    explainAppendTerm(pStr, i, z, "<");
  }
  sqlite3StrAccumAppend(pStr, ")", 1);
}

/*
** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
** command. If the query being compiled is an EXPLAIN QUERY PLAN, a single

** record is added to the output to describe the table scan strategy in 
** pLevel.



*/
static void explainOneScan(
  Parse *pParse,                  /* Parse context */
  SrcList *pTabList,              /* Table list this loop refers to */
  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
  int iLevel,                     /* Value for "level" column of output */
  int iFrom,                      /* Value for "from" column of output */
  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
){
#ifndef SQLITE_DEBUG

  if( pParse->explain==2 )
#endif
  {
    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
    Vdbe *v = pParse->pVdbe;      /* VM being constructed */
    sqlite3 *db = pParse->db;     /* Database handle */
    int iId = pParse->iSelectId;  /* Select id (left-most output column) */
................................................................................
    u32 flags;                    /* Flags that describe this loop */
    char *zMsg;                   /* Text to add to EQP output */
    StrAccum str;                 /* EQP output string */
    char zBuf[100];               /* Initial space for EQP output string */

    pLoop = pLevel->pWLoop;
    flags = pLoop->wsFlags;
    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;

    isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
            || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))
            || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));

    sqlite3StrAccumInit(&str, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
    str.db = db;
................................................................................
    if( pLoop->nOut>=10 ){
      sqlite3XPrintf(&str, 0, " (~%llu rows)", sqlite3LogEstToInt(pLoop->nOut));
    }else{
      sqlite3StrAccumAppend(&str, " (~1 row)", 9);
    }
#endif
    zMsg = sqlite3StrAccumFinish(&str);
    sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC);
  }

}
#else
# define explainOneScan(u,v,w,x,y,z)
#endif /* SQLITE_OMIT_EXPLAIN */






































/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
static Bitmask codeOneLoopStart(
................................................................................
        /* Loop through table entries that match term pOrTerm. */
        WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
                                      wctrlFlags, iCovCur);
        assert( pSubWInfo || pParse->nErr || db->mallocFailed );
        if( pSubWInfo ){
          WhereLoop *pSubLoop;
          explainOneScan(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );


          /* This is the sub-WHERE clause body.  First skip over
          ** duplicate rows from prior sub-WHERE clauses, and record the
          ** rowid (or PRIMARY KEY) for the current row so that the same
          ** row will be skipped in subsequent sub-WHERE clauses.
          */
          if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
            int r;
................................................................................
      pLevel->p1 = iCur;
      pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk);
      VdbeCoverageIf(v, bRev==0);
      VdbeCoverageIf(v, bRev!=0);
      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
    }
  }





  /* Insert code to test every subexpression that can be completely
  ** computed using the current set of tables.
  */
  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
    Expr *pE;
    testcase( pTerm->wtFlags & TERM_VIRTUAL );
................................................................................

  /* Generate the code to do the search.  Each iteration of the for
  ** loop below generates code for a single nested loop of the VM
  ** program.
  */
  notReady = ~(Bitmask)0;
  for(ii=0; ii<nTabList; ii++){


    pLevel = &pWInfo->a[ii];

#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
    if( (pLevel->pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0 ){
      constructAutomaticIndex(pParse, &pWInfo->sWC,
                &pTabList->a[pLevel->iFrom], notReady, pLevel);
      if( db->mallocFailed ) goto whereBeginError;
    }
#endif

    explainOneScan(pParse, pTabList, pLevel, ii, pLevel->iFrom, wctrlFlags);

    pLevel->addrBody = sqlite3VdbeCurrentAddr(v);
    notReady = codeOneLoopStart(pWInfo, ii, notReady);
    pWInfo->iContinue = pLevel->addrCont;



  }

  /* Done. */
  VdbeModuleComment((v, "Begin WHERE-core"));
  return pWInfo;

  /* Jump here if malloc fails */







|
>
|
<
>
>
>

|







|
>







 







|







 







|

>


|

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|


>
>







 







>
>
>
>







 







>
>

>







>
|
>



>
>
>







2804
2805
2806
2807
2808
2809
2810
2811
2812
2813

2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
....
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
....
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
....
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
....
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
....
6459
6460
6461
6462
6463
6464
6465
6466
6467
6468
6469
6470
6471
6472
6473
6474
6475
6476
6477
6478
6479
6480
6481
6482
6483
6484
6485
6486
6487
6488
6489
6490
6491
6492
    explainAppendTerm(pStr, i, z, "<");
  }
  sqlite3StrAccumAppend(pStr, ")", 1);
}

/*
** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
** command, or if either SQLITE_DEBUG or SQLITE_ENABLE_STMT_SCANSTATUS was
** defined at compile-time. If it is not a no-op, a single OP_Explain opcode 
** is added to the output to describe the table scan strategy in pLevel.

**
** If an OP_Explain opcode is added to the VM, its address is returned.
** Otherwise, if no OP_Explain is coded, zero is returned.
*/
static int explainOneScan(
  Parse *pParse,                  /* Parse context */
  SrcList *pTabList,              /* Table list this loop refers to */
  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
  int iLevel,                     /* Value for "level" column of output */
  int iFrom,                      /* Value for "from" column of output */
  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
){
  int ret = 0;
#if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS)
  if( pParse->explain==2 )
#endif
  {
    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
    Vdbe *v = pParse->pVdbe;      /* VM being constructed */
    sqlite3 *db = pParse->db;     /* Database handle */
    int iId = pParse->iSelectId;  /* Select id (left-most output column) */
................................................................................
    u32 flags;                    /* Flags that describe this loop */
    char *zMsg;                   /* Text to add to EQP output */
    StrAccum str;                 /* EQP output string */
    char zBuf[100];               /* Initial space for EQP output string */

    pLoop = pLevel->pWLoop;
    flags = pLoop->wsFlags;
    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return 0;

    isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
            || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))
            || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));

    sqlite3StrAccumInit(&str, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
    str.db = db;
................................................................................
    if( pLoop->nOut>=10 ){
      sqlite3XPrintf(&str, 0, " (~%llu rows)", sqlite3LogEstToInt(pLoop->nOut));
    }else{
      sqlite3StrAccumAppend(&str, " (~1 row)", 9);
    }
#endif
    zMsg = sqlite3StrAccumFinish(&str);
    ret = sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg,P4_DYNAMIC);
  }
  return ret;
}
#else
# define explainOneScan(u,v,w,x,y,z) 0
#endif /* SQLITE_OMIT_EXPLAIN */

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
/*
** Configure the VM passed as the first argument with an
** sqlite3_stmt_scanstatus() entry corresponding to the scan used to 
** implement level pLvl. Argument pSrclist is a pointer to the FROM 
** clause that the scan reads data from.
**
** If argument addrExplain is not 0, it must be the address of an 
** OP_Explain instruction that describes the same loop.
*/
static void addScanStatus(
  Vdbe *v,                        /* Vdbe to add scanstatus entry to */
  SrcList *pSrclist,              /* FROM clause pLvl reads data from */
  WhereLevel *pLvl,               /* Level to add scanstatus() entry for */
  int addrExplain                 /* Address of OP_Explain (or 0) */
){
  const char *zObj = 0;
  i64 nEst = 1;
  WhereLoop *pLoop = pLvl->pWLoop;
  if( (pLoop->wsFlags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
    zObj = pLoop->u.btree.pIndex->zName;
  }else{
    zObj = pSrclist->a[pLvl->iFrom].zName;
  }
  if( pLoop->nOut>=10 ){
    nEst = sqlite3LogEstToInt(pLoop->nOut);
  }
  sqlite3VdbeScanStatus(
      v, addrExplain, pLvl->addrBody, pLvl->addrVisit, nEst, zObj
  );
}
#else
# define addScanStatus(a, b, c, d) ((void)d)
#endif



/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
static Bitmask codeOneLoopStart(
................................................................................
        /* Loop through table entries that match term pOrTerm. */
        WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
                                      wctrlFlags, iCovCur);
        assert( pSubWInfo || pParse->nErr || db->mallocFailed );
        if( pSubWInfo ){
          WhereLoop *pSubLoop;
          int addrExplain = explainOneScan(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );
          addScanStatus(v, pOrTab, &pSubWInfo->a[0], addrExplain);

          /* This is the sub-WHERE clause body.  First skip over
          ** duplicate rows from prior sub-WHERE clauses, and record the
          ** rowid (or PRIMARY KEY) for the current row so that the same
          ** row will be skipped in subsequent sub-WHERE clauses.
          */
          if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
            int r;
................................................................................
      pLevel->p1 = iCur;
      pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk);
      VdbeCoverageIf(v, bRev==0);
      VdbeCoverageIf(v, bRev!=0);
      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
    }
  }

#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  pLevel->addrVisit = sqlite3VdbeCurrentAddr(v);
#endif

  /* Insert code to test every subexpression that can be completely
  ** computed using the current set of tables.
  */
  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
    Expr *pE;
    testcase( pTerm->wtFlags & TERM_VIRTUAL );
................................................................................

  /* Generate the code to do the search.  Each iteration of the for
  ** loop below generates code for a single nested loop of the VM
  ** program.
  */
  notReady = ~(Bitmask)0;
  for(ii=0; ii<nTabList; ii++){
    int addrExplain;
    int wsFlags;
    pLevel = &pWInfo->a[ii];
    wsFlags = pLevel->pWLoop->wsFlags;
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
    if( (pLevel->pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0 ){
      constructAutomaticIndex(pParse, &pWInfo->sWC,
                &pTabList->a[pLevel->iFrom], notReady, pLevel);
      if( db->mallocFailed ) goto whereBeginError;
    }
#endif
    addrExplain = explainOneScan(
        pParse, pTabList, pLevel, ii, pLevel->iFrom, wctrlFlags
    );
    pLevel->addrBody = sqlite3VdbeCurrentAddr(v);
    notReady = codeOneLoopStart(pWInfo, ii, notReady);
    pWInfo->iContinue = pLevel->addrCont;
    if( (wsFlags&WHERE_MULTI_OR)==0 && (wctrlFlags&WHERE_ONETABLE_ONLY)==0 ){
      addScanStatus(v, pTabList, pLevel, addrExplain);
    }
  }

  /* Done. */
  VdbeModuleComment((v, "Begin WHERE-core"));
  return pWInfo;

  /* Jump here if malloc fails */

Changes to src/whereInt.h.

81
82
83
84
85
86
87



88
89
90
91
92
93
94
        u8 eEndLoopOp;         /* IN Loop terminator. OP_Next or OP_Prev */
      } *aInLoop;           /* Information about each nested IN operator */
    } in;                 /* Used when pWLoop->wsFlags&WHERE_IN_ABLE */
    Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
  } u;
  struct WhereLoop *pWLoop;  /* The selected WhereLoop object */
  Bitmask notReady;          /* FROM entries not usable at this level */



};

/*
** Each instance of this object represents an algorithm for evaluating one
** term of a join.  Every term of the FROM clause will have at least
** one corresponding WhereLoop object (unless INDEXED BY constraints
** prevent a query solution - which is an error) and many terms of the







>
>
>







81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
        u8 eEndLoopOp;         /* IN Loop terminator. OP_Next or OP_Prev */
      } *aInLoop;           /* Information about each nested IN operator */
    } in;                 /* Used when pWLoop->wsFlags&WHERE_IN_ABLE */
    Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
  } u;
  struct WhereLoop *pWLoop;  /* The selected WhereLoop object */
  Bitmask notReady;          /* FROM entries not usable at this level */
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  int addrVisit;        /* Address at which row is visited */
#endif
};

/*
** Each instance of this object represents an algorithm for evaluating one
** term of a join.  Every term of the FROM clause will have at least
** one corresponding WhereLoop object (unless INDEXED BY constraints
** prevent a query solution - which is an error) and many terms of the

Added test/scanstatus.test.





























































































































































































































































































































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
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
# 2014 November 1
#
# 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.
#
#***********************************************************************
#

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

ifcapable !scanstatus {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(x, y);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);
  INSERT INTO t2 VALUES('a', 'b');
  INSERT INTO t2 VALUES('c', 'd');
  INSERT INTO t2 VALUES('e', 'f');
}

proc do_scanstatus_test {tn res} {
  set stmt [db_last_stmt_ptr db]
  set idx 0
  set ret [list]
  while {1} {
    set r [sqlite3_stmt_scanstatus $stmt $idx]
    if {[llength $r]==0} break
    lappend ret {*}$r
    incr idx
  }

  uplevel [list do_test $tn [list set {} $ret] [list {*}$res]]
}

do_execsql_test 1.1 { SELECT count(*) FROM t1, t2; } 6
do_scanstatus_test 1.2 {
  nLoop 1 nVisit 2 nEst 1048576 zName t1 zExplain {SCAN TABLE t1}
  nLoop 2 nVisit 6 nEst 1048576 zName t2 zExplain {SCAN TABLE t2}
}

do_execsql_test 1.3 {
  ANALYZE;
  SELECT count(*) FROM t1, t2;
} 6
do_scanstatus_test 1.4 {
  nLoop 1 nVisit 2 nEst 2 zName t1 zExplain {SCAN TABLE t1}
  nLoop 2 nVisit 6 nEst 3 zName t2 zExplain {SCAN TABLE t2}
}

do_execsql_test 1.5 { ANALYZE }
do_execsql_test 1.6 {
  SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
} 4
do_scanstatus_test 1.7 {
  nLoop 1 nVisit 2 nEst 2 zName t2 zExplain 
  {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
  nLoop 2 nVisit 4 nEst 2 zName t1 zExplain {SCAN TABLE t1}
}

do_execsql_test 1.8 {
  SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
} 4

do_scanstatus_test 1.9 {
  nLoop 2 nVisit 4 nEst 2 zName t2 zExplain 
  {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
  nLoop 4 nVisit 8 nEst 2 zName t1 zExplain {SCAN TABLE t1}
}

do_test 1.9 {
  sqlite3_stmt_scanstatus_reset [db_last_stmt_ptr db]
} {}

do_scanstatus_test 1.10 {
  nLoop 0 nVisit 0 nEst 2 zName t2 zExplain 
  {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
  nLoop 0 nVisit 0 nEst 2 zName t1 zExplain {SCAN TABLE t1}
}

#-------------------------------------------------------------------------
# Try a few different types of scans.
#
reset_db
do_execsql_test 2.1 {
  CREATE TABLE x1(i INTEGER PRIMARY KEY, j);
  INSERT INTO x1 VALUES(1, 'one');
  INSERT INTO x1 VALUES(2, 'two');
  INSERT INTO x1 VALUES(3, 'three');
  INSERT INTO x1 VALUES(4, 'four');
  CREATE INDEX x1j ON x1(j);

  SELECT * FROM x1 WHERE i=2;
} {2 two}

do_scanstatus_test 2.2 {
  nLoop 1 nVisit 1 nEst 1 zName x1 
  zExplain {SEARCH TABLE x1 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_execsql_test 2.3.1 {
  SELECT * FROM x1 WHERE j='two'
} {2 two}
do_scanstatus_test 2.3.2 {
  nLoop 1 nVisit 1 nEst 10 zName x1j 
  zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j=?)}
}

do_execsql_test 2.4.1 {
  SELECT * FROM x1 WHERE j<'two'
} {4 four 1 one 3 three}
do_scanstatus_test 2.4.2 {
  nLoop 1 nVisit 3 nEst 262144 zName x1j 
  zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j<?)}
}

do_execsql_test 2.5.1 {
  SELECT * FROM x1 WHERE j>='two'
} {2 two}
do_scanstatus_test 2.5.2 {
  nLoop 1 nVisit 1 nEst 262144 zName x1j 
  zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>?)}
}

do_execsql_test 2.6.1 {
  SELECT * FROM x1 WHERE j BETWEEN 'three' AND 'two'
} {3 three 2 two}
do_scanstatus_test 2.6.2 {
  nLoop 1 nVisit 2 nEst 16384 zName x1j 
  zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>? AND j<?)}
}

do_execsql_test 2.7.1 {
  CREATE TABLE x2(i INTEGER, j, k);
  INSERT INTO x2 SELECT i, j, i || ' ' || j FROM x1;
  CREATE INDEX x2j ON x2(j);
  CREATE INDEX x2ij ON x2(i, j);
  SELECT * FROM x2 WHERE j BETWEEN 'three' AND 'two'
} {3 three {3 three} 2 two {2 two}}

do_scanstatus_test 2.7.2 {
  nLoop 1 nVisit 2 nEst 16384 zName x2j 
  zExplain {SEARCH TABLE x2 USING INDEX x2j (j>? AND j<?)}
}

do_execsql_test 2.8.1 {
  SELECT * FROM x2 WHERE i=1 AND j='two'
}
do_scanstatus_test 2.8.2 {
  nLoop 1 nVisit 0 nEst 8 zName x2ij 
  zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
}

do_execsql_test 2.9.1 {
  SELECT * FROM x2 WHERE i=5 AND j='two'
}
do_scanstatus_test 2.9.2 {
  nLoop 1 nVisit 0 nEst 8 zName x2ij 
  zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
}

do_execsql_test 2.10.1 {
  SELECT * FROM x2 WHERE i=3 AND j='three'
} {3 three {3 three}}
do_scanstatus_test 2.10.2 {
  nLoop 1 nVisit 1 nEst 8 zName x2ij 
  zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)}
}

#-------------------------------------------------------------------------
# Try with queries that use the OR optimization.
#
do_execsql_test 3.1 {
  CREATE TABLE a1(a, b, c, d);
  CREATE INDEX a1a ON a1(a);
  CREATE INDEX a1bc ON a1(b, c);

  WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100)
  INSERT INTO a1 SELECT x, x, x, x FROM d;
}

do_execsql_test 3.2.1 {
  SELECT d FROM a1 WHERE (a=4 OR b=13)
} {4 13}
do_scanstatus_test 3.2.2 {
  nLoop 1 nVisit 1 nEst 10 zName a1a 
  zExplain {SEARCH TABLE a1 USING INDEX a1a (a=?)}
  nLoop 1 nVisit 1 nEst 10 zName a1bc 
  zExplain {SEARCH TABLE a1 USING INDEX a1bc (b=?)}
}

do_execsql_test 3.2.1 {
  SELECT count(*) FROM a1 WHERE (a BETWEEN 4 AND 12) OR (b BETWEEN 40 AND 60)
} {30}
do_scanstatus_test 3.2.2 {
  nLoop 1 nVisit 9 nEst 16384 zName a1a 
  zExplain {SEARCH TABLE a1 USING INDEX a1a (a>? AND a<?)}
  nLoop 1 nVisit 21 nEst 16384 zName a1bc
  zExplain {SEARCH TABLE a1 USING INDEX a1bc (b>? AND b<?)}
}

do_execsql_test 3.3.1 {
  SELECT count(*) FROM a1 AS x, a1 AS y 
  WHERE (x.a BETWEEN 4 AND 12) AND (y.b BETWEEN 1 AND 10)
} {90}
do_scanstatus_test 3.2.2 {
  nLoop 1 nVisit 10 nEst 16384 zName a1bc 
  zExplain {SEARCH TABLE a1 AS y USING COVERING INDEX a1bc (b>? AND b<?)}
  nLoop 10 nVisit 90 nEst 16384 zName a1a
  zExplain {SEARCH TABLE a1 AS x USING COVERING INDEX a1a (a>? AND a<?)}
}

do_execsql_test 3.4.1 {
  SELECT count(*) FROM a1 WHERE a IN (1, 5, 10, 15);
} {4}
do_scanstatus_test 3.4.2 {
  nLoop 1 nVisit 4 nEst 40 zName a1a 
  zExplain {SEARCH TABLE a1 USING COVERING INDEX a1a (a=?)}
}

do_execsql_test 3.4.1 {
  SELECT count(*) FROM a1 WHERE rowid IN (1, 5, 10, 15);
} {4}
do_scanstatus_test 3.4.2 {
  nLoop 1 nVisit 4 nEst 4 zName a1
  zExplain {SEARCH TABLE a1 USING INTEGER PRIMARY KEY (rowid=?)}
}

#-------------------------------------------------------------------------
# Test that scanstatus() data is not available for searches performed
# by triggers.
#
# It is available for searches performed as part of FK processing, but 
# not FK action processing.
#
do_execsql_test 4.0 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(x PRIMARY KEY, y, z);
  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
    SELECT * FROM t2 WHERE x BETWEEN 20 AND 40;
  END;
  WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100)
  INSERT INTO t2 SELECT x, x*2, x*3 FROM d;
}

do_execsql_test    4.1.1 { INSERT INTO t1 VALUES(1, 2, 3); }
do_scanstatus_test 4.1.2 { }

do_execsql_test 4.2 {
  CREATE TABLE p1(x PRIMARY KEY);
  INSERT INTO p1 VALUES(1), (2), (3), (4);
  CREATE TABLE c1(y REFERENCES p1);
  INSERT INTO c1 VALUES(1), (2), (3);
  PRAGMA foreign_keys=on;
}
do_execsql_test    4.2.1 { DELETE FROM p1 WHERE x=4 }
do_scanstatus_test 4.2.2 { 
  nLoop 1 nVisit 1 nEst 1 zName sqlite_autoindex_p1_1 
  zExplain {SEARCH TABLE p1 USING INDEX sqlite_autoindex_p1_1 (x=?)}

  nLoop 1 nVisit 3 nEst 524288 zName c1 zExplain {SCAN TABLE c1}
}

#-------------------------------------------------------------------------
# Further tests of different scan types.
#
reset_db
proc tochar {i} {
  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}
  return [lindex $alphabet [expr $i % [llength $alphabet]]]
}
db func tochar tochar
do_execsql_test 5.0 {
  CREATE TABLE t1(a PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES(0, 1, 'a');
  INSERT INTO t1 VALUES(1, 0, 'b');
  INSERT INTO t1 VALUES(2, 1, 'c');
  INSERT INTO t1 VALUES(3, 0, 'd');
  INSERT INTO t1 VALUES(4, 1, 'e');
  INSERT INTO t1 VALUES(5, 0, 'a');
  INSERT INTO t1 VALUES(6, 1, 'b');
  INSERT INTO t1 VALUES(7, 0, 'c');
  INSERT INTO t1 VALUES(8, 1, 'd');
  INSERT INTO t1 VALUES(9, 0, 'e');
  CREATE INDEX t1bc ON t1(b, c);

  CREATE TABLE t2(x, y);
  CREATE INDEX t2xy ON t2(x, y);
  WITH data(i, x, y) AS (
    SELECT 0, 0, tochar(0) 
    UNION ALL
    SELECT i+1, (i+1)%2, tochar(i+1) FROM data WHERE i<500
  ) INSERT INTO t2 SELECT x, y FROM data;

  CREATE TABLE t3(x, y);
  INSERT INTO t3 SELECT * FROM t2;

  ANALYZE;
}

do_execsql_test 5.1.1 {
  SELECT count(*) FROM t1 WHERE a IN (SELECT b FROM t1 AS ii)
} {2}
do_scanstatus_test 5.1.2 { 
  nLoop 1 nVisit 10 nEst 10 zName t1bc 
  zExplain {SCAN TABLE t1 AS ii USING COVERING INDEX t1bc}
  nLoop 1 nVisit 2 nEst 8 zName sqlite_autoindex_t1_1
  zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
}

do_execsql_test 5.2.1 {
  SELECT count(*) FROM t1 WHERE a IN (0, 1)
} {2}
do_scanstatus_test 5.2.2 { 
  nLoop 1 nVisit 2 nEst 2 zName sqlite_autoindex_t1_1
  zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
}

do_eqp_test 5.3.1 {
  SELECT count(*) FROM t2 WHERE y = 'j';
} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
do_execsql_test 5.3.2 {
  SELECT count(*) FROM t2 WHERE y = 'j';
} {19}
do_scanstatus_test 5.3.3 { 
  nLoop 1 nVisit 19 nEst 56 zName t2xy zExplain
  {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}

do_eqp_test 5.4.1 {
  SELECT count(*) FROM t1, t2 WHERE y = c;
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
  0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}
do_execsql_test 5.4.2 {
  SELECT count(*) FROM t1, t2 WHERE y = c;
} {200}
do_scanstatus_test 5.4.3 { 
  nLoop 1 nVisit 10 nEst 10 zName t1bc 
  zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
  nLoop 10 nVisit 200 nEst 56 zName t2xy 
  zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
}

do_eqp_test 5.5.1 {
  SELECT count(*) FROM t1, t3 WHERE y = c;
} {
  0 0 1 {SCAN TABLE t3} 
  0 1 0 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
}
do_execsql_test 5.5.2 {
  SELECT count(*) FROM t1, t3 WHERE y = c;
} {200}
do_scanstatus_test 5.5.3 { 
  nLoop 1 nVisit 501 nEst 480 zName t3 zExplain {SCAN TABLE t3}
  nLoop 501 nVisit 200 nEst 20 zName auto-index zExplain
  {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
}

#-------------------------------------------------------------------------
# Virtual table scans
#
ifcapable fts3 {
  do_execsql_test 6.0 {
    CREATE VIRTUAL TABLE ft1 USING fts4;
    INSERT INTO ft1 VALUES('a d c f g h e i f c');
    INSERT INTO ft1 VALUES('g c h b g b f f f g');
    INSERT INTO ft1 VALUES('h h c c h f a e d d');
    INSERT INTO ft1 VALUES('e j i j i e b c f g');
    INSERT INTO ft1 VALUES('g f b g j c h a d f');
    INSERT INTO ft1 VALUES('j i a e g f a i a c');
    INSERT INTO ft1 VALUES('f d g g j j c a h g');
    INSERT INTO ft1 VALUES('b d h a d j j j b i');
    INSERT INTO ft1 VALUES('j e a b j e c b c i');
    INSERT INTO ft1 VALUES('a d e f b j j c g d');
  }
  do_execsql_test 6.1.1 {
    SELECT count(*) FROM ft1 WHERE ft1 MATCH 'd'
  } {6}
  do_scanstatus_test 6.1.2 { 
    nLoop 1 nVisit 6 nEst 24 zName ft1 zExplain 
    {SCAN TABLE ft1 VIRTUAL TABLE INDEX 3:}
  }
}


finish_test