Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the ANALYZE command to work with partial indices. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | partial-indices |
Files: | files | file ages | folders |
SHA1: |
60353124f4e965393ecd864019bdbca1 |
User & Date: | drh 2013-08-01 04:39:17.756 |
Context
2013-08-01
| ||
12:21 | Refactor internal function name sqlite3VdbeGetValue() to sqlite3VdbeGetBoundValue(). (check-in: 81834c3023 user: drh tags: partial-indices) | |
04:39 | Fix the ANALYZE command to work with partial indices. (check-in: 60353124f4 user: drh tags: partial-indices) | |
03:36 | Test cases and bug fixes for the partial index logic. (check-in: 6b73ae7c12 user: drh tags: partial-indices) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
437 438 439 440 441 442 443 444 445 446 447 448 449 450 | int iIdxCur; /* Cursor open on index being analyzed */ Vdbe *v; /* The virtual machine being built up */ int i; /* Loop counter */ int topOfLoop; /* The top of the loop */ int endOfLoop; /* The end of the loop */ int jZeroRows = -1; /* Jump from here if number of rows is zero */ int iDb; /* Index of database containing pTab */ int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ int regStat1 = iMem++; /* The stat column of sqlite_stat1 */ #ifdef SQLITE_ENABLE_STAT3 int regNumEq = regStat1; /* Number of instances. Same as regStat1 */ int regNumLt = iMem++; /* Number of keys less than regSample */ int regNumDLt = iMem++; /* Number of distinct keys less than regSample */ | > | 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 | int iIdxCur; /* Cursor open on index being analyzed */ Vdbe *v; /* The virtual machine being built up */ int i; /* Loop counter */ int topOfLoop; /* The top of the loop */ int endOfLoop; /* The end of the loop */ int jZeroRows = -1; /* Jump from here if number of rows is zero */ int iDb; /* Index of database containing pTab */ u8 needTableCnt = 1; /* True to count the table */ int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ int regStat1 = iMem++; /* The stat column of sqlite_stat1 */ #ifdef SQLITE_ENABLE_STAT3 int regNumEq = regStat1; /* Number of instances. Same as regStat1 */ int regNumLt = iMem++; /* Number of keys less than regSample */ int regNumDLt = iMem++; /* Number of distinct keys less than regSample */ |
︙ | ︙ | |||
496 497 498 499 500 501 502 503 504 505 506 507 508 509 | for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int nCol; KeyInfo *pKey; int addrIfNot = 0; /* address of OP_IfNot */ int *aChngAddr; /* Array of jump instruction addresses */ if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName)); nCol = pIdx->nColumn; aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*nCol); if( aChngAddr==0 ) continue; pKey = sqlite3IndexKeyinfo(pParse, pIdx); if( iMem+1+(nCol*2)>pParse->nMem ){ pParse->nMem = iMem+1+(nCol*2); | > | 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 | for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int nCol; KeyInfo *pKey; int addrIfNot = 0; /* address of OP_IfNot */ int *aChngAddr; /* Array of jump instruction addresses */ if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0; VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName)); nCol = pIdx->nColumn; aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*nCol); if( aChngAddr==0 ) continue; pKey = sqlite3IndexKeyinfo(pParse, pIdx); if( iMem+1+(nCol*2)>pParse->nMem ){ pParse->nMem = iMem+1+(nCol*2); |
︙ | ︙ | |||
655 656 657 658 659 660 661 | ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regStat1); | < | < > > | | | < < < < | | | | | < | > > | 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 | ** I = (K+D-1)/D ** ** If K==0 then no entry is made into the sqlite_stat1 table. ** If K>0 then it is always the case the D>0 so division by zero ** is never possible. */ sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regStat1); jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem); for(i=0; i<nCol; i++){ sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0); sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1); sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp); sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1); sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp); sqlite3VdbeAddOp1(v, OP_ToInt, regTemp); sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1); } if( pIdx->pPartIdxWhere!=0 ) sqlite3VdbeJumpHere(v, jZeroRows); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); if( pIdx->pPartIdxWhere==0 ) sqlite3VdbeJumpHere(v, jZeroRows); } /* Create a single sqlite_stat1 entry containing NULL as the index ** name and the row count as the content. */ if( pOnlyIdx==0 && needTableCnt ){ sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb); VdbeComment((v, "%s", pTab->zName)); sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat1); sqlite3VdbeAddOp1(v, OP_Close, iIdxCur); jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1); sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); sqlite3VdbeJumpHere(v, jZeroRows); } if( pParse->nMem<regRec ) pParse->nMem = regRec; } /* ** Generate code that will cause the most recent index analysis to ** be loaded into internal hash tables where is can be used. */ |
︙ | ︙ | |||
875 876 877 878 879 880 881 | z = argv[2]; for(i=0; *z && i<=n; i++){ v = 0; while( (c=z[0])>='0' && c<='9' ){ v = v*10 + c - '0'; z++; } | > | | > | 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 | z = argv[2]; for(i=0; *z && i<=n; i++){ v = 0; while( (c=z[0])>='0' && c<='9' ){ v = v*10 + c - '0'; z++; } if( i==0 && (pIndex==0 || pIndex->pPartIdxWhere==0) ){ if( v>0 ) pTable->nRowEst = v; if( pIndex==0 ) break; } pIndex->aiRowEst[i] = v; if( *z==' ' ) z++; if( strcmp(z, "unordered")==0 ){ pIndex->bUnordered = 1; break; } } |
︙ | ︙ |
Changes to test/index6.test.
︙ | ︙ | |||
15 16 17 18 19 20 21 | set testdir [file dirname $argv0] source $testdir/tester.tcl load_static_extension db wholenumber; do_test index6-1.1 { execsql { | | | | | | | 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | set testdir [file dirname $argv0] source $testdir/tester.tcl load_static_extension db wholenumber; do_test index6-1.1 { execsql { CREATE TABLE t1(a,b,c); CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL; CREATE INDEX t1b ON t1(b) WHERE b>10; CREATE VIRTUAL TABLE nums USING wholenumber; INSERT INTO t1(a,b,c) SELECT CASE WHEN value%3!=0 THEN value END, value, value FROM nums WHERE value<=20; SELECT count(a), count(b) FROM t1; } } {14 20} do_test index6-1.2 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL; } } {1 {no such column: x}} do_test index6-1.3 { catchsql { CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1); } } {1 {subqueries prohibited in partial index WHERE clauses}} do_test index6-1.4 { catchsql { |
︙ | ︙ | |||
58 59 60 61 62 63 64 | do_test index6-1.10 { execsql { ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } | | | | | | > > > > > > > > > | | 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 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | do_test index6-1.10 { execsql { ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {{} 20 t1a {14 1} t1b {10 1} ok} do_test index6-1.11 { execsql { UPDATE t1 SET a=b; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {{} 20 t1a {20 1} t1b {10 1} ok} do_test index6-1.11 { execsql { UPDATE t1 SET a=NULL WHERE b%3!=0; UPDATE t1 SET b=b+100; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {{} 20 t1a {6 1} t1b {20 1} ok} do_test index6-1.12 { execsql { UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END; UPDATE t1 SET b=b-100; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {{} 20 t1a {13 1} t1b {10 1} ok} do_test index6-1.13 { execsql { DELETE FROM t1 WHERE b BETWEEN 8 AND 12; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {{} 15 t1a {10 1} t1b {8 1} ok} do_test index6-1.14 { execsql { REINDEX; ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {{} 15 t1a {10 1} t1b {8 1} ok} do_test index6-1.15 { execsql { CREATE INDEX t1c ON t1(c); ANALYZE; SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; PRAGMA integrity_check; } } {t1a {10 1} t1b {8 1} t1c {15 1} ok} finish_test |