/ Check-in [a2303c71]
Login

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

Overview
Comment:Add further tests. Fixes so that compilation without ENABLE_STMT_SCANSTATUS works.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | scanstatus
Files: files | file ages | folders
SHA1: a2303c719222f1effb51acc6b37930561148c00c
User & Date: dan 2014-11-03 15:33:17
Context
2014-11-03
16:35
Refactor the interface to make it more easily extensible. check-in: 7955342d user: drh tags: scanstatus
15:33
Add further tests. Fixes so that compilation without ENABLE_STMT_SCANSTATUS works. check-in: a2303c71 user: dan tags: scanstatus
11:25
Remove unused variable from struct WhereInfo. Add some explanatory comments to new code. check-in: f5313e0c user: dan tags: scanstatus
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/test_config.c.

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

Changes to src/vdbe.c.

  5405   5405       pFrame->apCsr = p->apCsr;
  5406   5406       pFrame->nCursor = p->nCursor;
  5407   5407       pFrame->aOp = p->aOp;
  5408   5408       pFrame->nOp = p->nOp;
  5409   5409       pFrame->token = pProgram->token;
  5410   5410       pFrame->aOnceFlag = p->aOnceFlag;
  5411   5411       pFrame->nOnceFlag = p->nOnceFlag;
         5412  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  5412   5413       pFrame->anExec = p->anExec;
         5414  +#endif
  5413   5415   
  5414   5416       pEnd = &VdbeFrameMem(pFrame)[pFrame->nChildMem];
  5415   5417       for(pMem=VdbeFrameMem(pFrame); pMem!=pEnd; pMem++){
  5416   5418         pMem->flags = MEM_Undefined;
  5417   5419         pMem->db = db;
  5418   5420       }
  5419   5421     }else{
................................................................................
  5434   5436     p->nMem = pFrame->nChildMem;
  5435   5437     p->nCursor = (u16)pFrame->nChildCsr;
  5436   5438     p->apCsr = (VdbeCursor **)&aMem[p->nMem+1];
  5437   5439     p->aOp = aOp = pProgram->aOp;
  5438   5440     p->nOp = pProgram->nOp;
  5439   5441     p->aOnceFlag = (u8 *)&p->apCsr[p->nCursor];
  5440   5442     p->nOnceFlag = pProgram->nOnce;
         5443  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  5441   5444     p->anExec = 0;
         5445  +#endif
  5442   5446     pc = -1;
  5443   5447     memset(p->aOnceFlag, 0, p->nOnceFlag);
  5444   5448   
  5445   5449     break;
  5446   5450   }
  5447   5451   
  5448   5452   /* Opcode: Param P1 P2 * * *

Changes to src/vdbeaux.c.

  1719   1719       p->aMem = allocSpace(p->aMem, nMem*sizeof(Mem), &zCsr, zEnd, &nByte);
  1720   1720       p->aVar = allocSpace(p->aVar, nVar*sizeof(Mem), &zCsr, zEnd, &nByte);
  1721   1721       p->apArg = allocSpace(p->apArg, nArg*sizeof(Mem*), &zCsr, zEnd, &nByte);
  1722   1722       p->azVar = allocSpace(p->azVar, nVar*sizeof(char*), &zCsr, zEnd, &nByte);
  1723   1723       p->apCsr = allocSpace(p->apCsr, nCursor*sizeof(VdbeCursor*),
  1724   1724                             &zCsr, zEnd, &nByte);
  1725   1725       p->aOnceFlag = allocSpace(p->aOnceFlag, nOnce, &zCsr, zEnd, &nByte);
         1726  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  1726   1727       p->anExec = allocSpace(p->anExec, p->nOp*sizeof(i64), &zCsr, zEnd, &nByte);
         1728  +#endif
  1727   1729       if( nByte ){
  1728   1730         p->pFree = sqlite3DbMallocZero(db, nByte);
  1729   1731       }
  1730   1732       zCsr = p->pFree;
  1731   1733       zEnd = &zCsr[nByte];
  1732   1734     }while( nByte && !db->mallocFailed );
  1733   1735   
................................................................................
  1787   1789   /*
  1788   1790   ** Copy the values stored in the VdbeFrame structure to its Vdbe. This
  1789   1791   ** is used, for example, when a trigger sub-program is halted to restore
  1790   1792   ** control to the main program.
  1791   1793   */
  1792   1794   int sqlite3VdbeFrameRestore(VdbeFrame *pFrame){
  1793   1795     Vdbe *v = pFrame->v;
         1796  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  1794   1797     v->anExec = pFrame->anExec;
         1798  +#endif
  1795   1799     v->aOnceFlag = pFrame->aOnceFlag;
  1796   1800     v->nOnceFlag = pFrame->nOnceFlag;
  1797   1801     v->aOp = pFrame->aOp;
  1798   1802     v->nOp = pFrame->nOp;
  1799   1803     v->aMem = pFrame->aMem;
  1800   1804     v->nMem = pFrame->nMem;
  1801   1805     v->apCsr = pFrame->apCsr;

Changes to src/where.c.

  2949   2949       nEst = sqlite3LogEstToInt(pLoop->nOut);
  2950   2950     }
  2951   2951     sqlite3VdbeScanStatus(
  2952   2952         v, addrExplain, pLvl->addrBody, pLvl->addrVisit, nEst, zObj
  2953   2953     );
  2954   2954   }
  2955   2955   #else
  2956         -# define addScanStatus(a, b, c, d)
         2956  +# define addScanStatus(a, b, c, d) ((void)d)
  2957   2957   #endif
  2958   2958   
  2959   2959   
  2960   2960   
  2961   2961   /*
  2962   2962   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  2963   2963   ** implementation described by pWInfo.

Changes to test/scanstatus.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12     12   
    13     13   set testdir [file dirname $argv0]
    14     14   source $testdir/tester.tcl
    15     15   set testprefix scanstatus
           16  +
           17  +ifcapable !scanstatus {
           18  +  finish_test
           19  +  return
           20  +}
    16     21   
    17     22   do_execsql_test 1.0 {
    18     23     CREATE TABLE t1(a, b);
    19     24     CREATE TABLE t2(x, y);
    20     25     INSERT INTO t1 VALUES(1, 2);
    21     26     INSERT INTO t1 VALUES(3, 4);
    22     27     INSERT INTO t2 VALUES('a', 'b');
................................................................................
   261    266   do_execsql_test    4.2.1 { DELETE FROM p1 WHERE x=4 }
   262    267   do_scanstatus_test 4.2.2 { 
   263    268     nLoop 1 nVisit 1 nEst 1 zName sqlite_autoindex_p1_1 
   264    269     zExplain {SEARCH TABLE p1 USING INDEX sqlite_autoindex_p1_1 (x=?)}
   265    270   
   266    271     nLoop 1 nVisit 3 nEst 524288 zName c1 zExplain {SCAN TABLE c1}
   267    272   }
          273  +
          274  +#-------------------------------------------------------------------------
          275  +# Further tests of different scan types.
          276  +#
          277  +reset_db
          278  +proc tochar {i} {
          279  +  set alphabet {a b c d e f g h i j k l m n o p q r s t u v w x y z}
          280  +  return [lindex $alphabet [expr $i % [llength $alphabet]]]
          281  +}
          282  +db func tochar tochar
          283  +do_execsql_test 5.0 {
          284  +  CREATE TABLE t1(a PRIMARY KEY, b, c);
          285  +  INSERT INTO t1 VALUES(0, 1, 'a');
          286  +  INSERT INTO t1 VALUES(1, 0, 'b');
          287  +  INSERT INTO t1 VALUES(2, 1, 'c');
          288  +  INSERT INTO t1 VALUES(3, 0, 'd');
          289  +  INSERT INTO t1 VALUES(4, 1, 'e');
          290  +  INSERT INTO t1 VALUES(5, 0, 'a');
          291  +  INSERT INTO t1 VALUES(6, 1, 'b');
          292  +  INSERT INTO t1 VALUES(7, 0, 'c');
          293  +  INSERT INTO t1 VALUES(8, 1, 'd');
          294  +  INSERT INTO t1 VALUES(9, 0, 'e');
          295  +  CREATE INDEX t1bc ON t1(b, c);
          296  +
          297  +  CREATE TABLE t2(x, y);
          298  +  CREATE INDEX t2xy ON t2(x, y);
          299  +  WITH data(i, x, y) AS (
          300  +    SELECT 0, 0, tochar(0) 
          301  +    UNION ALL
          302  +    SELECT i+1, (i+1)%2, tochar(i+1) FROM data WHERE i<500
          303  +  ) INSERT INTO t2 SELECT x, y FROM data;
          304  +
          305  +  CREATE TABLE t3(x, y);
          306  +  INSERT INTO t3 SELECT * FROM t2;
          307  +
          308  +  ANALYZE;
          309  +}
          310  +
          311  +do_execsql_test 5.1.1 {
          312  +  SELECT count(*) FROM t1 WHERE a IN (SELECT b FROM t1 AS ii)
          313  +} {2}
          314  +do_scanstatus_test 5.1.2 { 
          315  +  nLoop 1 nVisit 10 nEst 10 zName t1bc 
          316  +  zExplain {SCAN TABLE t1 AS ii USING COVERING INDEX t1bc}
          317  +  nLoop 1 nVisit 2 nEst 8 zName sqlite_autoindex_t1_1
          318  +  zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
          319  +}
          320  +
          321  +do_execsql_test 5.2.1 {
          322  +  SELECT count(*) FROM t1 WHERE a IN (0, 1)
          323  +} {2}
          324  +do_scanstatus_test 5.2.2 { 
          325  +  nLoop 1 nVisit 2 nEst 2 zName sqlite_autoindex_t1_1
          326  +  zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
          327  +}
          328  +
          329  +do_eqp_test 5.3.1 {
          330  +  SELECT count(*) FROM t2 WHERE y = 'j';
          331  +} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
          332  +do_execsql_test 5.3.2 {
          333  +  SELECT count(*) FROM t2 WHERE y = 'j';
          334  +} {19}
          335  +do_scanstatus_test 5.3.3 { 
          336  +  nLoop 1 nVisit 19 nEst 56 zName t2xy zExplain
          337  +  {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
          338  +}
          339  +
          340  +do_eqp_test 5.4.1 {
          341  +  SELECT count(*) FROM t1, t2 WHERE y = c;
          342  +} {
          343  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
          344  +  0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
          345  +}
          346  +do_execsql_test 5.4.2 {
          347  +  SELECT count(*) FROM t1, t2 WHERE y = c;
          348  +} {200}
          349  +do_scanstatus_test 5.4.3 { 
          350  +  nLoop 1 nVisit 10 nEst 10 zName t1bc 
          351  +  zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
          352  +  nLoop 10 nVisit 200 nEst 56 zName t2xy 
          353  +  zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
          354  +}
          355  +
          356  +do_eqp_test 5.5.1 {
          357  +  SELECT count(*) FROM t1, t3 WHERE y = c;
          358  +} {
          359  +  0 0 1 {SCAN TABLE t3} 
          360  +  0 1 0 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
          361  +}
          362  +do_execsql_test 5.5.2 {
          363  +  SELECT count(*) FROM t1, t3 WHERE y = c;
          364  +} {200}
          365  +do_scanstatus_test 5.5.3 { 
          366  +  nLoop 1 nVisit 501 nEst 480 zName t3 zExplain {SCAN TABLE t3}
          367  +  nLoop 501 nVisit 200 nEst 20 zName auto-index zExplain
          368  +  {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
          369  +}
          370  +
          371  +#-------------------------------------------------------------------------
          372  +# Virtual table scans
          373  +#
          374  +ifcapable fts3 {
          375  +  do_execsql_test 6.0 {
          376  +    CREATE VIRTUAL TABLE ft1 USING fts4;
          377  +    INSERT INTO ft1 VALUES('a d c f g h e i f c');
          378  +    INSERT INTO ft1 VALUES('g c h b g b f f f g');
          379  +    INSERT INTO ft1 VALUES('h h c c h f a e d d');
          380  +    INSERT INTO ft1 VALUES('e j i j i e b c f g');
          381  +    INSERT INTO ft1 VALUES('g f b g j c h a d f');
          382  +    INSERT INTO ft1 VALUES('j i a e g f a i a c');
          383  +    INSERT INTO ft1 VALUES('f d g g j j c a h g');
          384  +    INSERT INTO ft1 VALUES('b d h a d j j j b i');
          385  +    INSERT INTO ft1 VALUES('j e a b j e c b c i');
          386  +    INSERT INTO ft1 VALUES('a d e f b j j c g d');
          387  +  }
          388  +  do_execsql_test 6.1.1 {
          389  +    SELECT count(*) FROM ft1 WHERE ft1 MATCH 'd'
          390  +  } {6}
          391  +  do_scanstatus_test 6.1.2 { 
          392  +    nLoop 1 nVisit 6 nEst 24 zName ft1 zExplain 
          393  +    {SCAN TABLE ft1 VIRTUAL TABLE INDEX 3:}
          394  +  }
          395  +}
          396  +
   268    397   
   269    398   finish_test