SQLite4
Check-in [bad9060b5b]
Not logged in

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

Overview
Comment:Fix another bug in sqlite_stat3 related code.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bad9060b5b98f4af8f6e455d794b77e67da314be
User & Date: dan 2013-06-24 20:06:42
Context
2013-06-25
11:58
Run test files analyze5.test, analyze6.test and analyze7.test as part of src4.test. check-in: 8eb90b6647 user: dan tags: trunk
2013-06-24
20:06
Fix another bug in sqlite_stat3 related code. check-in: bad9060b5b user: dan tags: trunk
19:03
Fixes for the sqlite_stat3 related ANALYZE functionality. check-in: f93e75f2b1 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   442    442     int regNumDLt = iMem++;      /* Number of distinct keys less than regSample */
   443    443     int regSample = iMem++;      /* The next sample value */
   444    444     int regAccum = iMem++;       /* Register to hold Stat3Accum object */
   445    445     int regLoop = iMem++;        /* Loop counter */
   446    446     int regCount = iMem++;       /* Number of rows in the table or index */
   447    447     int regTemp1 = iMem++;       /* Intermediate register */
   448    448     int regTemp2 = iMem++;       /* Intermediate register */
          449  +  int regNewSample = iMem++;
   449    450     int once = 1;                /* One-time initialization */
   450    451     int iTabCur = pParse->nTab++; /* Table cursor */
   451    452     int addrEq;
   452    453   #endif
   453    454     int regRec = iMem++;         /* Register holding completed record */
   454    455     int regTemp = iMem++;        /* Temporary use register */
   455    456     int regNewRowid = iMem++;    /* Rowid for the inserted record */
................................................................................
   543    544       */
   544    545       regCnt = iMem;
   545    546       regPrev = iMem+1;
   546    547       aregCard = iMem+2;
   547    548   
   548    549       sqlite4VdbeAddOp2(v, OP_Integer, 0, regCnt);
   549    550       sqlite4VdbeAddOp2(v, OP_Null, 0, regPrev);
          551  +    sqlite4VdbeAddOp2(v, OP_Null, 0, regSample);
   550    552       for(i=0; i<nCol; i++){
   551         -      sqlite4VdbeAddOp2(v, OP_Integer, 0, aregCard+i);
          553  +      sqlite4VdbeAddOp2(v, OP_Integer, 1, aregCard+i);
   552    554       }
   553    555   
   554    556       /* Start the analysis loop. This loop runs through all the entries in
   555    557       ** the index b-tree.  */
   556    558       endOfLoop = sqlite4VdbeMakeLabel(v);
   557    559       sqlite4VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
   558    560       topOfLoop = sqlite4VdbeCurrentAddr(v);
   559    561       sqlite4VdbeAddOp2(v, OP_AddImm, regCnt, 1);  /* Increment row counter */
   560         -#ifdef SQLITE4_ENABLE_STAT3
   561         -    sqlite4VdbeAddOp2(v, OP_Copy, aregCard, regTemp1);
   562         -#endif
   563    562       sqlite4VdbeAddOp4Int(v, OP_AnalyzeKey, iIdxCur, regPrev, aregCard, nCol);
   564    563   
   565    564   #ifdef SQLITE4_ENABLE_STAT3
   566         -    sqlite4VdbeAddOp2(v, OP_AddImm, regNumEq, 1);
   567         -    addrEq = sqlite4VdbeAddOp3(v, OP_Eq, aregCard, 0,regTemp1);
          565  +    sqlite4VdbeAddOp2(v, OP_RowKey, iIdxCur, regNewSample);
          566  +    sqlite4VdbeChangeP5(v, 1);
          567  +    addrEq = sqlite4VdbeAddOp3(v, OP_Eq, regNewSample, 0, regSample);
          568  +    addrIsnull = sqlite4VdbeAddOp2(v, OP_IsNull, regSample, 0);
          569  +
   568    570       assert( regNumEq==regNumLt-1  && regNumEq==regNumDLt-2
   569    571            && regNumEq==regSample-3 && regNumEq==regAccum-4
   570    572       );
   571         -    sqlite4VdbeAddOp2(v, OP_RowKey, iIdxCur, regSample);
   572         -    sqlite4VdbeChangeP5(v, 1);
   573    573       sqlite4VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2, 
   574    574           (char*)&stat3PushFuncdef, P4_FUNCDEF
   575    575       );
   576    576       sqlite4VdbeChangeP5(v, 5);
   577    577       sqlite4VdbeAddOp3(v, OP_Add, regNumEq, regNumLt, regNumLt);
   578    578       sqlite4VdbeAddOp2(v, OP_AddImm, regNumDLt, 1);
          579  +
          580  +    sqlite4VdbeJumpHere(v, addrIsnull);
   579    581       sqlite4VdbeAddOp2(v, OP_Integer, 0, regNumEq);
          582  +    sqlite4VdbeAddOp2(v, OP_Copy, regNewSample, regSample);
   580    583       sqlite4VdbeJumpHere(v, addrEq);
          584  +    sqlite4VdbeAddOp2(v, OP_AddImm, regNumEq, 1);
   581    585   #endif
   582    586   
   583    587       /* Always jump here after updating the iMem+1...iMem+1+nCol counters */
   584    588       sqlite4VdbeResolveLabel(v, endOfLoop);
   585    589   
   586    590       sqlite4VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
   587    591       sqlite4VdbeAddOp1(v, OP_Close, iIdxCur);
          592  +
   588    593   #ifdef SQLITE4_ENABLE_STAT3
   589         -
   590    594       /* Push the last record (if any) to the accumulator. */
   591    595       sqlite4VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2,
   592    596                         (char*)&stat3PushFuncdef, P4_FUNCDEF);
   593    597       sqlite4VdbeChangeP5(v, 5);
   594    598   
   595    599       /* This block codes a loop that iterates through all entries stored
   596    600       ** by the accumulator (the Stat3Accum object). 

Changes to src/vdbe.c.

  3608   3608   /* Opcode: AnalyzeKey P1 P2 P3 P4
  3609   3609   **
  3610   3610   ** P1 is an open cursor that currently points to a valid row. P2 is a 
  3611   3611   ** register that contains either a NULL value, or an index key. If it is 
  3612   3612   ** not NULL, this opcode compares the key in register P2 with the key of 
  3613   3613   ** the row P1 currently points to and determines the number of fields in
  3614   3614   ** the prefix that the two keys share in common (which may be zero).
  3615         -** Call this value N. If P2 is NULL, set N to zero.
         3615  +** Call this value N. If P2 is NULL, set N to P4.
  3616   3616   **
  3617   3617   ** P3 is the first in an array of P4 registers containing integer values.
  3618   3618   ** The first N of these are left as is by this instruction. The remaining
  3619   3619   ** (P4-N) are incremented.
  3620   3620   **
  3621   3621   ** Finally, the key belonging to the current row of cursor P1 is copied
  3622   3622   ** into register P2.
................................................................................
  3647   3647       if( pKey->flags & MEM_Blob ){
  3648   3648         for(i=0; i<nNew && i<pKey->n && pNew[i]==(KVByteArray)pKey->z[i]; i++);
  3649   3649   
  3650   3650         /* The two keys share i bytes in common. Figure out how many fields
  3651   3651         ** this corresponds to. Store said value in variable nEq. */
  3652   3652         sqlite4VdbeShortKey(pNew, i, LARGEST_INT32, &nEq);
  3653   3653       }else{
  3654         -      nEq = 0;
         3654  +      nEq = nTotal;
  3655   3655       }
  3656   3656   
  3657   3657       /* Increment nTotal-nEq registers */
  3658   3658       for(i=nEq; i<nTotal; i++){
  3659   3659         memAboutToChange(p, &aIncr[i]);
  3660   3660         sqlite4VdbeMemIntegerify(&aIncr[i]);
  3661   3661         aIncr[i].u.num = sqlite4_num_add(

Changes to test/analyze4.test.

    43     43   # Verify that the t1b index shows that it does not narrow down the
    44     44   # search any at all.
    45     45   #
    46     46   do_test analyze4-1.1 {
    47     47     db eval {
    48     48       SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
    49     49     }
    50         -} {t1a {128 1} t1b {128 128}}
           50  +} {t1 {128 1} t1a {128 1} t1b {128 128}}
    51     51   
    52     52   # Change half of the b values from NULL to a constant.  Verify
    53     53   # that the number of rows selected in stat1 is half the total 
    54     54   # number of rows.
    55     55   #
    56     56   do_test analyze4-1.2 {
    57     57     db eval {
    58     58       UPDATE t1 SET b='x' WHERE a%2;
    59     59       ANALYZE;
    60     60       SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
    61     61     }
    62         -} {t1a {128 1} t1b {128 64}}
           62  +} {t1 {128 1} t1a {128 1} t1b {128 64}}
    63     63   
    64     64   # Change the t1.b values all back to NULL.  Add columns t1.c and t1.d.
    65     65   # Create a multi-column indices using t1.b and verify that ANALYZE 
    66     66   # processes them correctly.
    67     67   #
    68     68   do_test analyze4-1.3 {
    69     69     db eval {
................................................................................
    73     73       UPDATE t1 SET c=a/4, d=a/2;
    74     74       CREATE INDEX t1bcd ON t1(b,c,d);
    75     75       CREATE INDEX t1cdb ON t1(c,d,b);
    76     76       CREATE INDEX t1cbd ON t1(c,b,d);
    77     77       ANALYZE;
    78     78       SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
    79     79     }
    80         -} {t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}}
           80  +} {t1 {128 1} t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}}
    81     81   
    82     82   # Verify that collating sequences are taken into account when computing
    83     83   # ANALYZE statistics.
    84     84   #
    85     85   do_test analyze4-2.0 {
    86     86     db eval {
    87     87       CREATE TABLE t2(
................................................................................
   102    102       INSERT INTO t2 VALUES(5, 'aBc', 'aBc', 'aBc');
   103    103       INSERT INTO t2 VALUES(6, 'aBC', 'aBC', 'aBC');
   104    104       INSERT INTO t2 VALUES(7, 'aBc ', 'aBc ', 'aBc ');
   105    105       INSERT INTO t2 VALUES(8, 'aBC ', 'aBC ', 'aBC ');
   106    106       ANALYZE;
   107    107       SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t2' ORDER BY idx;
   108    108     }
   109         -} {t2a {8 4} t2b {8 2} t2c {8 1} t2c2 {8 4} t2c3 {8 2}}
          109  +} {t2 {8 1} t2a {8 4} t2b {8 2} t2c {8 1} t2c2 {8 4} t2c3 {8 2}}
   110    110   
   111    111   finish_test

Changes to test/permutations.test.

   137    137     lsm1.test lsm2.test lsm3.test lsm4.test lsm5.test
   138    138     csr1.test
   139    139     ckpt1.test
   140    140     mc1.test
   141    141     fts5expr1.test fts5query1.test fts5rnd1.test fts5create.test
   142    142     fts5snippet.test
   143    143   
   144         -  analyze.test analyze3.test
          144  +  analyze.test analyze3.test analyze4.test
   145    145     auth.test auth2.test auth3.test auth4.test
   146    146     aggerror.test
   147    147     attach.test
   148    148     autoindex1.test
   149    149     badutf.test
   150    150     between.test
   151    151     bigrow.test