/ Check-in [8d8cc960]
Login

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

Overview
Comment:Minor fixes and documentation improvements for sqlite3_stmt_scanstatus().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | scanstatus
Files: files | file ages | folders
SHA1: 8d8cc9608d30bb65fffcfe488e904411cbbc7f41
User & Date: dan 2014-11-01 18:08:04
Context
2014-11-01
20:38
If SQLITE_ENABLE_STMT_SCANSTATUS is defined, record the number of times each VDBE opcode is executed. Derive the values returned by sqlite3_stmt_scanstatus() from these records on demand. check-in: 9ea37422 user: dan tags: scanstatus
18:08
Minor fixes and documentation improvements for sqlite3_stmt_scanstatus(). check-in: 8d8cc960 user: dan tags: scanstatus
2014-10-31
20:11
Add the experimental sqlite3_stmt_scanstatus() API. check-in: 6a9bab34 user: dan tags: scanstatus
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

7404
7405
7406
7407
7408
7409
7410


7411
7412
7413
7414
7415
7416
7417
7418


















7419
7420
7421
7422
7423
7424
7425
....
7432
7433
7434
7435
7436
7437
7438


7439
7440
7441
7442
7443
7444
7445
/* #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


/*


** Return status data for a single loop within query pStmt.
**
** 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. In this case the final value
** of all five output parameters is undefined. Otherwise, if idx is in range,
** the output parameters are populated and zero returned.


















**
** Statistics may not be available for all loops in all statements. In cases
** where there exist loops with no available statistics, this function ignores
** them completely.
**
** This API is only available if the library is built with pre-processor
** symbol SQLITE_ENABLE_STMT_SCANSTATUS defined.
................................................................................
  sqlite3_int64 *pnEst,           /* OUT: Number of rows estimated (per loop) */
  const char **pzName,            /* OUT: Object name (table or index) */
  const char **pzExplain          /* OUT: EQP string */
);


/*


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








>
>







|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>







7404
7405
7406
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
....
7452
7453
7454
7455
7456
7457
7458
7459
7460
7461
7462
7463
7464
7465
7466
7467
/* #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 Statuses
**
** Return status data for a single loop within query pStmt.
**
** 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. In this case the final value
** of all five output parameters is undefined. Otherwise, if idx is in range,
** zero is returned and the output parameters set as follows:
**
** <ul>
**   <li> (*pnLoop) is set to the total number of times the loop has been run.
**   <li> (*pnVisit) is set to the total number of rows visited by the loop.
**   <li> (*pnEst) is set to the estimate of the number of rows visited
**        by each run of the loop used by the SQL optimizer. Ideally, this
**        value should be close to (*pnVisit)/(*pnLoop).
**   <li> (*pzName) is set to point to a nul-terminated string containing the
**        name of the index of table used by this loop.
**   <li> (*pzExplain) is set to point to a nul-terminated string containing 
**        same text that would be returned for this loop by an EXPLAIN
**        QUERY PLAN command.
** </ul>
**
** Output parameters *pzName and *pzExplain are set to point to buffers 
** managed by the statement object. Both of these pointers may be invalidated
** by any API call on the same statement object, including an sqlite3_step()
** sqlite3_bind_*() call.
**
** Statistics may not be available for all loops in all statements. In cases
** where there exist loops with no available statistics, this function ignores
** them completely.
**
** This API is only available if the library is built with pre-processor
** symbol SQLITE_ENABLE_STMT_SCANSTATUS defined.
................................................................................
  sqlite3_int64 *pnEst,           /* OUT: Number of rows estimated (per loop) */
  const char **pzName,            /* OUT: Object name (table or index) */
  const char **pzExplain          /* OUT: EQP string */
);


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

Changes to src/vdbe.c.

3885
3886
3887
3888
3889
3890
3891

3892
3893
3894
3895
3896
3897
3898


3899
3900
3901
3902
3903
3904
3905
  assert( pC->isTable );
  assert( pC->pseudoTableReg==0 );
  pCrsr = pC->pCursor;
  assert( pCrsr!=0 );
  res = 0;
  iKey = pIn3->u.i;
  rc = sqlite3BtreeMovetoUnpacked(pCrsr, 0, iKey, 0, &res);

  pC->movetoTarget = iKey;  /* Used by OP_Delete */
  pC->nullRow = 0;
  pC->cacheStatus = CACHE_STALE;
  pC->deferredMoveto = 0;
  VdbeBranchTaken(res!=0,2);
  if( res!=0 ){
    pc = pOp->p2 - 1;


  }
  pC->seekResult = res;
  break;
}

/* Opcode: Sequence P1 P2 * * *
** Synopsis: r[P2]=cursor[P1].ctr++







>







>
>







3885
3886
3887
3888
3889
3890
3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
  assert( pC->isTable );
  assert( pC->pseudoTableReg==0 );
  pCrsr = pC->pCursor;
  assert( pCrsr!=0 );
  res = 0;
  iKey = pIn3->u.i;
  rc = sqlite3BtreeMovetoUnpacked(pCrsr, 0, iKey, 0, &res);
  IncrementExplainCounter(pC, nLoop);
  pC->movetoTarget = iKey;  /* Used by OP_Delete */
  pC->nullRow = 0;
  pC->cacheStatus = CACHE_STALE;
  pC->deferredMoveto = 0;
  VdbeBranchTaken(res!=0,2);
  if( res!=0 ){
    pc = pOp->p2 - 1;
  }else{
    IncrementExplainCounter(pC, nVisit);
  }
  pC->seekResult = res;
  break;
}

/* Opcode: Sequence P1 P2 * * *
** Synopsis: r[P2]=cursor[P1].ctr++

Changes to src/where.c.

2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
....
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826

2827
2828
2829

2830



2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
....
2861
2862
2863
2864
2865
2866
2867



2868

2869
2870
2871
2872
2873
2874
2875
....
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
....
3609
3610
3611
3612
3613
3614
3615
3616

3617
3618







3619
3620
3621
3622
3623
3624
3625
....
6451
6452
6453
6454
6455
6456
6457
6458
6459
6460
6461
6462
6463
6464
6465
      }
    }
  }
  *pzAff = zAff;
  return regBase;
}

#ifndef SQLITE_OMIT_EXPLAIN
/*
** This routine is a helper for explainIndexRange() below
**
** pStr holds the text of an expression that we are building up one term
** at a time.  This routine adds a new term to the end of the expression.
** Terms are separated by AND so add the "AND" text for second and subsequent
** terms only.
................................................................................
** 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() */
){
#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) */



    int isSearch;                 /* True for a SEARCH. False for SCAN. */
    WhereLoop *pLoop;             /* The controlling WhereLoop object */
    u32 flags;                    /* Flags that describe this loop */
    StrAccum str;                 /* EQP output string */
    char zBuf[100];               /* Initial space for EQP output string */
    const char *zObj;
    ExplainArg *pExplain;
    i64 nEstRow;                  /* Estimated rows per scan of pLevel */

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

    sqlite3StrAccumInit(&str, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
    str.db = db;

    /* Reserve space at the start of the buffer managed by the StrAccum
    ** object for *pExplain.  */
    assert( sizeof(*pExplain)<=sizeof(zBuf) );
................................................................................

    /* Append the EQP text to the buffer */
    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));
    sqlite3StrAccumAppendAll(&str, isSearch ? "SEARCH" : "SCAN");
    if( pItem->pSelect ){



      sqlite3XPrintf(&str, 0, " SUBQUERY %d", pItem->iSelectId);

    }else{
      sqlite3XPrintf(&str, 0, " TABLE %s", pItem->zName);
    }

    if( pItem->zAlias ){
      sqlite3XPrintf(&str, 0, " AS %s", pItem->zAlias);
    }
................................................................................
        pExplain->iCsr = pLevel->iTabCur;
      }else{
        pExplain->iCsr = -1;
      }
      pExplain->nEst = nEstRow;
      pExplain->zName = (const char*)&pExplain[1];
      pExplain->zExplain = &pExplain->zName[sqlite3Strlen30(pExplain->zName)+1];
      sqlite3VdbeAddOp4(v, OP_Explain, 
          iId, iLevel, iFrom, (char*)pExplain,P4_EXPLAIN
      );
    }
  }
}
#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;
................................................................................
#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"));







|







 







|

<






>



>
|
>
>
>











|







 







>
>
>

>







 







|
|





|
|







 







<
>
|
<
>
>
>
>
>
>
>







 







|







2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
....
2811
2812
2813
2814
2815
2816
2817
2818
2819

2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
....
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
....
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
....
3617
3618
3619
3620
3621
3622
3623

3624
3625

3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
3638
3639
....
6465
6466
6467
6468
6469
6470
6471
6472
6473
6474
6475
6476
6477
6478
6479
      }
    }
  }
  *pzAff = zAff;
  return regBase;
}

#if !defined(SQLITE_OMIT_EXPLAIN) || defined(SQLITE_ENABLE_STMT_SCANSTATUS)
/*
** This routine is a helper for explainIndexRange() below
**
** pStr holds the text of an expression that we are building up one term
** at a time.  This routine adds a new term to the end of the expression.
** Terms are separated by AND so add the "AND" text for second and subsequent
** terms only.
................................................................................
** 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 */
  WhereInfo *pWInfo,              /* WHERE clause this loop belongs to */
  int iLevel,                     /* Value for "level" column of output */

  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
){
#if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS)
  if( pParse->explain==2 )
#endif
  {
    WhereLevel *pLevel = &pWInfo->a[iLevel];
    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
    Vdbe *v = pParse->pVdbe;      /* VM being constructed */
    sqlite3 *db = pParse->db;     /* Database handle */
#ifdef SQLITE_OMIT_EXPLAIN
    int iId = 0;                  /* Select id (left-most output column) */
#else
    int iId = pParse->iSelectId;   /* Select id (left-most output column) */
#endif
    int isSearch;                 /* True for a SEARCH. False for SCAN. */
    WhereLoop *pLoop;             /* The controlling WhereLoop object */
    u32 flags;                    /* Flags that describe this loop */
    StrAccum str;                 /* EQP output string */
    char zBuf[100];               /* Initial space for EQP output string */
    const char *zObj;
    ExplainArg *pExplain;
    i64 nEstRow;                  /* Estimated rows per scan of pLevel */

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

    sqlite3StrAccumInit(&str, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
    str.db = db;

    /* Reserve space at the start of the buffer managed by the StrAccum
    ** object for *pExplain.  */
    assert( sizeof(*pExplain)<=sizeof(zBuf) );
................................................................................

    /* Append the EQP text to the buffer */
    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));
    sqlite3StrAccumAppendAll(&str, isSearch ? "SEARCH" : "SCAN");
    if( pItem->pSelect ){
#ifdef SQLITE_OMIT_EXPLAIN
      sqlite3XPrintf(&str, 0, " SUBQUERY 0");
#else
      sqlite3XPrintf(&str, 0, " SUBQUERY %d", pItem->iSelectId);
#endif
    }else{
      sqlite3XPrintf(&str, 0, " TABLE %s", pItem->zName);
    }

    if( pItem->zAlias ){
      sqlite3XPrintf(&str, 0, " AS %s", pItem->zAlias);
    }
................................................................................
        pExplain->iCsr = pLevel->iTabCur;
      }else{
        pExplain->iCsr = -1;
      }
      pExplain->nEst = nEstRow;
      pExplain->zName = (const char*)&pExplain[1];
      pExplain->zExplain = &pExplain->zName[sqlite3Strlen30(pExplain->zName)+1];
      pWInfo->iExplain = sqlite3VdbeAddOp4(v, OP_Explain, 
          iId, iLevel, pLevel->iFrom, (char*)pExplain,P4_EXPLAIN
      );
    }
  }
}
#else
# define explainOneScan(v,w,x,y,z)
#endif /* !SQLITE_OMIT_EXPLAIN || SQLITE_ENABLE_STMT_SCANSTATUS */


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


          /* If an OP_Explain was added for this sub-loop, fix the P2 and

          ** P3 parameters to it so that they are relative to the current
          ** context.  */
          if( pSubWInfo->iExplain!=0 ){
            sqlite3VdbeChangeP2(v, pSubWInfo->iExplain, iLevel);
            sqlite3VdbeChangeP3(v, pSubWInfo->iExplain, pLevel->iFrom);
          }

          /* 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;
................................................................................
#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, pWInfo, ii, wctrlFlags);
    pLevel->addrBody = sqlite3VdbeCurrentAddr(v);
    notReady = codeOneLoopStart(pWInfo, ii, notReady);
    pWInfo->iContinue = pLevel->addrCont;
  }

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

Changes to src/whereInt.h.

403
404
405
406
407
408
409

410
411
412
413
414
415
416
  u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
  u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
  u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
  u8 nLevel;                /* Number of nested loop */
  int iTop;                 /* The very beginning of the WHERE loop */
  int iContinue;            /* Jump here to continue with next record */
  int iBreak;               /* Jump here to break out of the loop */

  int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
  int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
  WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
  WhereClause sWC;          /* Decomposition of the WHERE clause */
  WhereLevel a[1];          /* Information about each nest loop in WHERE */
};








>







403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
  u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
  u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
  u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
  u8 nLevel;                /* Number of nested loop */
  int iTop;                 /* The very beginning of the WHERE loop */
  int iContinue;            /* Jump here to continue with next record */
  int iBreak;               /* Jump here to break out of the loop */
  int iExplain;             /* Address of OP_Explain (if WHERE_ONETABLE_ONLY) */
  int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
  int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
  WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
  WhereClause sWC;          /* Decomposition of the WHERE clause */
  WhereLevel a[1];          /* Information about each nest loop in WHERE */
};

Changes to test/scanstatus.test.

49
50
51
52
53
54
55

56
57
58
59
60
61
62
63
64
65



66




67
68





























































































































69
  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;
  SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
} 4
do_scanstatus_test 1.6 {
  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}
}








































































































































finish_test







>
|
<


|





>
>
>

>
>
>
>
|

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

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
  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 4 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 1 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 2 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 2.4 {
  nLoop 1 nVisit 2 nEst 10 zName a1a 
  zExplain {SEARCH TABLE a1 USING INDEX a1a (a=?)}

  nLoop 1 nVisit 2 nEst 10 zName a1bc 
  zExplain {SEARCH TABLE a1 USING INDEX a1bc (b=?)}
}



finish_test