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

Overview
Comment:Fix another bug in sqlite_stat3 related code.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bad9060b5b98f4af8f6e455d794b77e67da314be
User & Date: dan 2013-06-24 20:06:42.454
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
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/analyze.c.
442
443
444
445
446
447
448

449
450
451
452
453
454
455
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456







+







  int regNumDLt = iMem++;      /* Number of distinct keys less than regSample */
  int regSample = iMem++;      /* The next sample value */
  int regAccum = iMem++;       /* Register to hold Stat3Accum object */
  int regLoop = iMem++;        /* Loop counter */
  int regCount = iMem++;       /* Number of rows in the table or index */
  int regTemp1 = iMem++;       /* Intermediate register */
  int regTemp2 = iMem++;       /* Intermediate register */
  int regNewSample = iMem++;
  int once = 1;                /* One-time initialization */
  int iTabCur = pParse->nTab++; /* Table cursor */
  int addrEq;
#endif
  int regRec = iMem++;         /* Register holding completed record */
  int regTemp = iMem++;        /* Temporary use register */
  int regNewRowid = iMem++;    /* Rowid for the inserted record */
543
544
545
546
547
548
549

550
551

552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567





568
569
570
571
572
573
574
575
576
577
578


579

580

581
582
583
584
585
586
587
588
589

590
591
592
593
594
595
596
544
545
546
547
548
549
550
551
552

553
554
555
556
557
558
559
560
561



562
563
564


565
566
567
568
569
570
571
572


573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591

592
593
594
595
596
597
598
599
600







+

-
+








-
-
-



-
-
+
+
+
+
+



-
-






+
+

+

+







-

+







    */
    regCnt = iMem;
    regPrev = iMem+1;
    aregCard = iMem+2;

    sqlite4VdbeAddOp2(v, OP_Integer, 0, regCnt);
    sqlite4VdbeAddOp2(v, OP_Null, 0, regPrev);
    sqlite4VdbeAddOp2(v, OP_Null, 0, regSample);
    for(i=0; i<nCol; i++){
      sqlite4VdbeAddOp2(v, OP_Integer, 0, aregCard+i);
      sqlite4VdbeAddOp2(v, OP_Integer, 1, aregCard+i);
    }

    /* Start the analysis loop. This loop runs through all the entries in
    ** the index b-tree.  */
    endOfLoop = sqlite4VdbeMakeLabel(v);
    sqlite4VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
    topOfLoop = sqlite4VdbeCurrentAddr(v);
    sqlite4VdbeAddOp2(v, OP_AddImm, regCnt, 1);  /* Increment row counter */
#ifdef SQLITE4_ENABLE_STAT3
    sqlite4VdbeAddOp2(v, OP_Copy, aregCard, regTemp1);
#endif
    sqlite4VdbeAddOp4Int(v, OP_AnalyzeKey, iIdxCur, regPrev, aregCard, nCol);

#ifdef SQLITE4_ENABLE_STAT3
    sqlite4VdbeAddOp2(v, OP_AddImm, regNumEq, 1);
    addrEq = sqlite4VdbeAddOp3(v, OP_Eq, aregCard, 0,regTemp1);
    sqlite4VdbeAddOp2(v, OP_RowKey, iIdxCur, regNewSample);
    sqlite4VdbeChangeP5(v, 1);
    addrEq = sqlite4VdbeAddOp3(v, OP_Eq, regNewSample, 0, regSample);
    addrIsnull = sqlite4VdbeAddOp2(v, OP_IsNull, regSample, 0);

    assert( regNumEq==regNumLt-1  && regNumEq==regNumDLt-2
         && regNumEq==regSample-3 && regNumEq==regAccum-4
    );
    sqlite4VdbeAddOp2(v, OP_RowKey, iIdxCur, regSample);
    sqlite4VdbeChangeP5(v, 1);
    sqlite4VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2, 
        (char*)&stat3PushFuncdef, P4_FUNCDEF
    );
    sqlite4VdbeChangeP5(v, 5);
    sqlite4VdbeAddOp3(v, OP_Add, regNumEq, regNumLt, regNumLt);
    sqlite4VdbeAddOp2(v, OP_AddImm, regNumDLt, 1);

    sqlite4VdbeJumpHere(v, addrIsnull);
    sqlite4VdbeAddOp2(v, OP_Integer, 0, regNumEq);
    sqlite4VdbeAddOp2(v, OP_Copy, regNewSample, regSample);
    sqlite4VdbeJumpHere(v, addrEq);
    sqlite4VdbeAddOp2(v, OP_AddImm, regNumEq, 1);
#endif

    /* Always jump here after updating the iMem+1...iMem+1+nCol counters */
    sqlite4VdbeResolveLabel(v, endOfLoop);

    sqlite4VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
    sqlite4VdbeAddOp1(v, OP_Close, iIdxCur);
#ifdef SQLITE4_ENABLE_STAT3

#ifdef SQLITE4_ENABLE_STAT3
    /* Push the last record (if any) to the accumulator. */
    sqlite4VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2,
                      (char*)&stat3PushFuncdef, P4_FUNCDEF);
    sqlite4VdbeChangeP5(v, 5);

    /* This block codes a loop that iterates through all entries stored
    ** by the accumulator (the Stat3Accum object). 
Changes to src/vdbe.c.
3608
3609
3610
3611
3612
3613
3614
3615

3616
3617
3618
3619
3620
3621
3622
3608
3609
3610
3611
3612
3613
3614

3615
3616
3617
3618
3619
3620
3621
3622







-
+







/* Opcode: AnalyzeKey P1 P2 P3 P4
**
** P1 is an open cursor that currently points to a valid row. P2 is a 
** register that contains either a NULL value, or an index key. If it is 
** not NULL, this opcode compares the key in register P2 with the key of 
** the row P1 currently points to and determines the number of fields in
** the prefix that the two keys share in common (which may be zero).
** Call this value N. If P2 is NULL, set N to zero.
** Call this value N. If P2 is NULL, set N to P4.
**
** P3 is the first in an array of P4 registers containing integer values.
** The first N of these are left as is by this instruction. The remaining
** (P4-N) are incremented.
**
** Finally, the key belonging to the current row of cursor P1 is copied
** into register P2.
3647
3648
3649
3650
3651
3652
3653
3654

3655
3656
3657
3658
3659
3660
3661
3647
3648
3649
3650
3651
3652
3653

3654
3655
3656
3657
3658
3659
3660
3661







-
+







    if( pKey->flags & MEM_Blob ){
      for(i=0; i<nNew && i<pKey->n && pNew[i]==(KVByteArray)pKey->z[i]; i++);

      /* The two keys share i bytes in common. Figure out how many fields
      ** this corresponds to. Store said value in variable nEq. */
      sqlite4VdbeShortKey(pNew, i, LARGEST_INT32, &nEq);
    }else{
      nEq = 0;
      nEq = nTotal;
    }

    /* Increment nTotal-nEq registers */
    for(i=nEq; i<nTotal; i++){
      memAboutToChange(p, &aIncr[i]);
      sqlite4VdbeMemIntegerify(&aIncr[i]);
      aIncr[i].u.num = sqlite4_num_add(
Changes to test/analyze4.test.
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
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







-
+











-
+

















-
+







# Verify that the t1b index shows that it does not narrow down the
# search any at all.
#
do_test analyze4-1.1 {
  db eval {
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
  }
} {t1a {128 1} t1b {128 128}}
} {t1 {128 1} t1a {128 1} t1b {128 128}}

# Change half of the b values from NULL to a constant.  Verify
# that the number of rows selected in stat1 is half the total 
# number of rows.
#
do_test analyze4-1.2 {
  db eval {
    UPDATE t1 SET b='x' WHERE a%2;
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
  }
} {t1a {128 1} t1b {128 64}}
} {t1 {128 1} t1a {128 1} t1b {128 64}}

# Change the t1.b values all back to NULL.  Add columns t1.c and t1.d.
# Create a multi-column indices using t1.b and verify that ANALYZE 
# processes them correctly.
#
do_test analyze4-1.3 {
  db eval {
    UPDATE t1 SET b=NULL;
    ALTER TABLE t1 ADD COLUMN c;
    ALTER TABLE t1 ADD COLUMN d;
    UPDATE t1 SET c=a/4, d=a/2;
    CREATE INDEX t1bcd ON t1(b,c,d);
    CREATE INDEX t1cdb ON t1(c,d,b);
    CREATE INDEX t1cbd ON t1(c,b,d);
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
  }
} {t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}}
} {t1 {128 1} t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}}

# Verify that collating sequences are taken into account when computing
# ANALYZE statistics.
#
do_test analyze4-2.0 {
  db eval {
    CREATE TABLE t2(
102
103
104
105
106
107
108
109

110
111
102
103
104
105
106
107
108

109
110
111







-
+


    INSERT INTO t2 VALUES(5, 'aBc', 'aBc', 'aBc');
    INSERT INTO t2 VALUES(6, 'aBC', 'aBC', 'aBC');
    INSERT INTO t2 VALUES(7, 'aBc ', 'aBc ', 'aBc ');
    INSERT INTO t2 VALUES(8, 'aBC ', 'aBC ', 'aBC ');
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t2' ORDER BY idx;
  }
} {t2a {8 4} t2b {8 2} t2c {8 1} t2c2 {8 4} t2c3 {8 2}}
} {t2 {8 1} t2a {8 4} t2b {8 2} t2c {8 1} t2c2 {8 4} t2c3 {8 2}}

finish_test
Changes to test/permutations.test.
137
138
139
140
141
142
143
144

145
146
147
148
149
150
151
137
138
139
140
141
142
143

144
145
146
147
148
149
150
151







-
+







  lsm1.test lsm2.test lsm3.test lsm4.test lsm5.test
  csr1.test
  ckpt1.test
  mc1.test
  fts5expr1.test fts5query1.test fts5rnd1.test fts5create.test
  fts5snippet.test

  analyze.test analyze3.test
  analyze.test analyze3.test analyze4.test
  auth.test auth2.test auth3.test auth4.test
  aggerror.test
  attach.test
  autoindex1.test
  badutf.test
  between.test
  bigrow.test