SQLite4
Check-in [bad9060b5b]
Not logged in

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

Overview
SHA1 Hash:bad9060b5b98f4af8f6e455d794b77e67da314be
Date: 2013-06-24 20:06:42
User: dan
Comment:Fix another bug in sqlite_stat3 related code.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c

442
443
444
445
446
447
448

449
450
451
452
453
454
455
...
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
  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 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 */
................................................................................
    */
    regCnt = iMem;
    regPrev = iMem+1;
    aregCard = iMem+2;

    sqlite4VdbeAddOp2(v, OP_Integer, 0, regCnt);
    sqlite4VdbeAddOp2(v, OP_Null, 0, regPrev);

    for(i=0; i<nCol; i++){
      sqlite4VdbeAddOp2(v, OP_Integer, 0, 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);


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


    sqlite4VdbeAddOp2(v, OP_Integer, 0, regNumEq);

    sqlite4VdbeJumpHere(v, addrEq);

#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


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







>







 







>

|








<
<
<



|
>
|
>
>



<
<






>
>

>

>







<

>







442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
...
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
  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 */
................................................................................
    */
    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, 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 */



    sqlite4VdbeAddOp4Int(v, OP_AnalyzeKey, iIdxCur, regPrev, aregCard, nCol);

#ifdef SQLITE4_ENABLE_STAT3
    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
    );


    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
    /* 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
....
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
/* 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.
**
** 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.
................................................................................
    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;
    }

    /* Increment nTotal-nEq registers */
    for(i=nEq; i<nTotal; i++){
      memAboutToChange(p, &aIncr[i]);
      sqlite4VdbeMemIntegerify(&aIncr[i]);
      aIncr[i].u.num = sqlite4_num_add(







|







 







|







3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
....
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
/* 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 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.
................................................................................
    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 = 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
..
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
...
102
103
104
105
106
107
108
109
110
111
# 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}}

# 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}}

# 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 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}}

# Verify that collating sequences are taken into account when computing
# ANALYZE statistics.
#
do_test analyze4-2.0 {
  db eval {
    CREATE TABLE t2(
................................................................................
    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}}

finish_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
..
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
...
102
103
104
105
106
107
108
109
110
111
# 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;
  }
} {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;
  }
} {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 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;
  }
} {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(
................................................................................
    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;
  }
} {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
  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
  auth.test auth2.test auth3.test auth4.test
  aggerror.test
  attach.test
  autoindex1.test
  badutf.test
  between.test
  bigrow.test







|







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 analyze4.test
  auth.test auth2.test auth3.test auth4.test
  aggerror.test
  attach.test
  autoindex1.test
  badutf.test
  between.test
  bigrow.test