/ Check-in [e93c248c]
Login

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

Overview
Comment:Bug fixes to the sample-count logic for STAT2. A few test cases added.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | query-planner-tweaks
Files: files | file ages | folders
SHA1: e93c248c8470622944cf7f9e5af4f10c4e4809e3
User & Date: drh 2011-08-05 22:31:19
Context
2011-08-06
01:22
Fix a compiler warning when STAT2 is off. More test cases. Fix legacy tests to deal with the new STAT2 logic. check-in: 7e914aa9 user: drh tags: query-planner-tweaks
2011-08-05
22:31
Bug fixes to the sample-count logic for STAT2. A few test cases added. check-in: e93c248c user: drh tags: query-planner-tweaks
21:13
Add a column to the sqlite_stat2 table that contains the number of entries with exactly the same key as the sample. We do not yet do anything with this extra value. Some tests in analyze2.test are failing. check-in: eb434228 user: drh tags: query-planner-tweaks
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   263    263       topOfLoop = sqlite3VdbeCurrentAddr(v);
   264    264       sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);  /* Increment row counter */
   265    265   
   266    266       for(i=0; i<nCol; i++){
   267    267         CollSeq *pColl;
   268    268         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
   269    269         if( i==0 ){
   270         -#ifdef SQLITE_ENABLE_STAT2
   271         -        /* Check if the record that cursor iIdxCur points to contains a
   272         -        ** value that should be stored in the sqlite_stat2 table. If so,
   273         -        ** store it.  */
   274         -        int ne = sqlite3VdbeAddOp3(v, OP_Ne, iMem, 0, regNext);
   275         -        VdbeComment((v, "jump if not a sample"));
   276         -        sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2);
   277         -        sqlite3VdbeAddOp2(v, OP_Copy, regCol, regSample);
   278         -        sqlite3VdbeAddOp2(v, OP_AddImm, regReady, 1);
   279         -
   280         -        /* Calculate new values for regNextSample.  Where N is the number
   281         -        ** of rows in the table and S is the number of samples to take:
   282         -        **
   283         -        **   nextSample = (sampleNumber*N*2 + N)/(2*S)
   284         -        */
   285         -        sqlite3VdbeAddOp2(v, OP_AddImm, regSampleIdx, 1);
   286         -        sqlite3VdbeAddOp3(v, OP_Multiply, regSampleIdx, regCount2, regNext);
   287         -        sqlite3VdbeAddOp3(v, OP_Add, regNext, regCount, regNext);
   288         -        sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regNext, regNext);
   289         -        sqlite3VdbeJumpHere(v, ne);
   290         -#endif
   291         -
   292    270           /* Always record the very first row */
   293    271           addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
   294    272         }
   295    273         assert( pIdx->azColl!=0 );
   296    274         assert( pIdx->azColl[i]!=0 );
   297    275         pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
   298    276         aChngAddr[i] = sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
................................................................................
   317    295   #endif        
   318    296         }
   319    297         sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
   320    298         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
   321    299       }
   322    300       sqlite3DbFree(db, aChngAddr);
   323    301   
   324         -    /* End of the analysis loop. */
          302  +    /* Always jump here after updating the iMem+1...iMem+1+nCol counters */
   325    303       sqlite3VdbeResolveLabel(v, endOfLoop);
          304  +
          305  +#ifdef SQLITE_ENABLE_STAT2
          306  +    /* Check if the record that cursor iIdxCur points to contains a
          307  +    ** value that should be stored in the sqlite_stat2 table. If so,
          308  +    ** store it. 
          309  +    */
          310  +    int ne = sqlite3VdbeAddOp3(v, OP_Le, regNext, 0, iMem);
          311  +    VdbeComment((v, "jump if not a sample"));
          312  +    shortJump = sqlite3VdbeAddOp1(v, OP_If, regReady);
          313  +    sqlite3VdbeAddOp2(v, OP_Copy, iMem+nCol+1, regSample);
          314  +    sqlite3VdbeJumpHere(v, shortJump);
          315  +    sqlite3VdbeAddOp2(v, OP_AddImm, regReady, 1);
          316  +
          317  +    /* Calculate new values for regNextSample.  Where N is the number
          318  +    ** of rows in the table and S is the number of samples to take:
          319  +    **
          320  +    **   nextSample = (sampleNumber*N*2 + N)/(2*S)
          321  +    */
          322  +    sqlite3VdbeAddOp2(v, OP_AddImm, regSampleIdx, 1);
          323  +    sqlite3VdbeAddOp3(v, OP_Multiply, regSampleIdx, regCount2, regNext);
          324  +    sqlite3VdbeAddOp3(v, OP_Add, regNext, regCount, regNext);
          325  +    sqlite3VdbeAddOp3(v, OP_Divide, regSample2, regNext, regNext);
          326  +    sqlite3VdbeJumpHere(v, ne);
          327  +#endif
          328  +
   326    329       sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
   327    330       sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
   328    331   #ifdef SQLITE_ENABLE_STAT2
   329    332       sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrStoreStat2);
   330    333   #endif        
   331    334   
   332    335       /* Store the results in sqlite_stat1.

Changes to src/vdbe.c.

  2464   2464     memAboutToChange(p, pOut);
  2465   2465   
  2466   2466     /* Loop through the elements that will make up the record to figure
  2467   2467     ** out how much space is required for the new record.
  2468   2468     */
  2469   2469     for(pRec=pData0; pRec<=pLast; pRec++){
  2470   2470       assert( memIsValid(pRec) );
         2471  +    REGISTER_TRACE((pRec-pData0)+pOp->p1, pRec);
  2471   2472       if( zAffinity ){
  2472   2473         applyAffinity(pRec, zAffinity[pRec-pData0], encoding);
  2473   2474       }
  2474   2475       if( pRec->flags&MEM_Zero && pRec->n>0 ){
  2475   2476         sqlite3VdbeMemExpandBlob(pRec);
  2476   2477       }
  2477   2478       serial_type = sqlite3VdbeSerialType(pRec, file_format);

Added test/analyze8.test.

            1  +# 2011 August 5
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +# This file implements tests for the ANALYZE command under STAT2.
           13  +# Testing the logic that computes the number of copies of each sample.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +# There is nothing to test if ANALYZE is disable for this build.
           20  +#
           21  +ifcapable {!analyze||!vtab||!stat2} {
           22  +  finish_test
           23  +  return
           24  +}
           25  +
           26  +# Generate some test data
           27  +#
           28  +do_test analyze8-1.0 {
           29  +  set x 100
           30  +  set y 1
           31  +  set ycnt 0
           32  +  set yinc 10
           33  +  execsql {
           34  +    CREATE TABLE t1(x,y);
           35  +    ANALYZE;
           36  +    BEGIN;
           37  +    CREATE INDEX t1x ON t1(x);
           38  +    CREATE INDEX t1y ON t1(y);
           39  +  }
           40  +  for {set i 0} {$i<20} {incr i} {
           41  +    for {set j 0} {$j<300} {incr j} {
           42  +      execsql {INSERT INTO t1 VALUES($x,$y)}
           43  +      incr ycnt
           44  +      if {$ycnt>=$yinc} {set ycnt 0; incr y}
           45  +    }
           46  +    for {set j 0} {$j<100} {incr j} {
           47  +      incr x
           48  +      execsql {INSERT INTO t1 VALUES($x,$y)}
           49  +      incr ycnt
           50  +      if {$ycnt>=$yinc} {set ycnt 0; incr y}
           51  +    }
           52  +  }
           53  +  execsql {
           54  +    COMMIT;
           55  +    ANALYZE;
           56  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
           57  +  }
           58  +} {200 301 400 301 600 301 800 301 1000 301 1200 301 1400 301 1600 301 1800 301 2000 301}
           59  +do_test analyze8-1.1 {
           60  +  execsql {
           61  +    SELECT count(*) FROM t1 WHERE x=200;
           62  +  }
           63  +} {301}
           64  +
           65  +do_test analyze8-2.0 {
           66  +  execsql {
           67  +    BEGIN;
           68  +    DELETE FROM t1;
           69  +  }
           70  +  for {set x 1} {$x<200} {incr x} {
           71  +    execsql {INSERT INTO t1 VALUES($x,$x)}
           72  +  }
           73  +  for {set i 0} {$i<200} {incr i} {
           74  +    execsql {INSERT INTO t1 VALUES(999,999)}
           75  +  }
           76  +  execsql {
           77  +    COMMIT;
           78  +    ANALYZE;
           79  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
           80  +  }
           81  +} {20 1 60 1 100 1 140 1 180 1 999 200 999 200 999 200 999 200 999 200}
           82  +do_test analyze8-2.1 {
           83  +  for {set i 0} {$i<200} {incr i} {
           84  +    execsql {INSERT INTO t1 VALUES(0,999)}
           85  +  }
           86  +  execsql {
           87  +    ANALYZE;
           88  +    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
           89  +  }
           90  +} {0 200 0 200 0 200 10 1 70 1 130 1 190 1 999 200 999 200 999 200}