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: |
bad9060b5b98f4af8f6e455d794b77e6 |
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
Changes to src/analyze.c.
︙ | ︙ | |||
442 443 444 445 446 447 448 449 450 451 452 453 454 455 | 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 */ | > | 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 | */ 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++){ | > | < < < | > | > > < < > > > > < > | 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, 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 | /* 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). | | | 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 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 | 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{ | | | 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 = 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 | # 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; } | | | | | 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; } } {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 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; } } {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 | 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; } | | | 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; } } {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 | 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 | | | 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 |
︙ | ︙ |