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: |
bad9060b5b98f4af8f6e455d794b77e6 |
| 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
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