Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance the sqlite_stat1.stat parsing to allow additional text parameters at the end. Unrecognized parameters are silently ignored. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
ca2a5a2c770fa94cd8db1b1b241ede05 |
User & Date: | drh 2014-07-22 14:58:12.596 |
Context
2014-07-22
| ||
15:33 | Correction: The maximum SQLITE_MAX_ATTACHED value to avoid overflowing a signed 8-bit integer is 125, not 127. (check-in: 48e3780295 user: drh tags: trunk) | |
14:58 | Enhance the sqlite_stat1.stat parsing to allow additional text parameters at the end. Unrecognized parameters are silently ignored. (check-in: ca2a5a2c77 user: drh tags: trunk) | |
14:42 | Expire prepared statements after running ANALYZE. (check-in: b083a961f8 user: drh tags: trunk) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
1424 1425 1426 1427 1428 1429 1430 | if( *z==' ' ) z++; } #ifndef SQLITE_ENABLE_STAT3_OR_STAT4 assert( pIndex!=0 ); #else if( pIndex ) #endif | < > | > > | 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 | if( *z==' ' ) z++; } #ifndef SQLITE_ENABLE_STAT3_OR_STAT4 assert( pIndex!=0 ); #else if( pIndex ) #endif while( z[0] ){ if( sqlite3_strglob("unordered*", z)==0 ){ pIndex->bUnordered = 1; }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){ int v32 = 0; sqlite3GetInt32(z+3, &v32); pIndex->szIdxRow = sqlite3LogEst(v32); } while( z[0]!=0 && z[0]!=' ' ) z++; while( z[0]==' ' ) z++; } } /* ** This callback is invoked once for each index when reading the ** sqlite_stat1 table. ** |
︙ | ︙ | |||
1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 | pIndex = sqlite3PrimaryKeyIndex(pTable); }else{ pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); } z = argv[2]; if( pIndex ){ decodeIntArray((char*)z, pIndex->nKeyCol+1, 0, pIndex->aiRowLogEst, pIndex); if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0]; }else{ Index fakeIdx; fakeIdx.szIdxRow = pTable->szTabRow; decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx); pTable->szTabRow = fakeIdx.szIdxRow; | > | 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 | pIndex = sqlite3PrimaryKeyIndex(pTable); }else{ pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase); } z = argv[2]; if( pIndex ){ pIndex->bUnordered = 0; decodeIntArray((char*)z, pIndex->nKeyCol+1, 0, pIndex->aiRowLogEst, pIndex); if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0]; }else{ Index fakeIdx; fakeIdx.szIdxRow = pTable->szTabRow; decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx); pTable->szTabRow = fakeIdx.szIdxRow; |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
5790 5791 5792 5793 5794 5795 5796 | } break; } #endif /* Opcode: Expire P1 * * * * ** | | < | > > | | 5790 5791 5792 5793 5794 5795 5796 5797 5798 5799 5800 5801 5802 5803 5804 5805 5806 5807 5808 5809 5810 | } break; } #endif /* Opcode: Expire P1 * * * * ** ** Cause precompiled statements to expire. When an expired statement ** is executed using sqlite3_step() it will either automatically ** reprepare itself (if it was originally created using sqlite3_prepare_v2()) ** or it will fail with SQLITE_SCHEMA. ** ** If P1 is 0, then all SQL statements become expired. If P1 is non-zero, ** then only the currently executing statement is expired. */ case OP_Expire: { if( !pOp->p1 ){ sqlite3ExpirePreparedStatements(db); }else{ p->expired = 1; } |
︙ | ︙ |
Changes to test/analyze9.test.
︙ | ︙ | |||
1085 1086 1087 1088 1089 1090 1091 | 3 "d=0 AND e<300" {/*t5d (d=?)*/} 4 "d=0 AND e<200" {/*t5e (e<?)*/} } { do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp } finish_test | < < | 1085 1086 1087 1088 1089 1090 1091 | 3 "d=0 AND e<300" {/*t5d (d=?)*/} 4 "d=0 AND e<200" {/*t5e (e<?)*/} } { do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp } finish_test |
Added test/analyzeC.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 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 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 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 | # 2014-07-22 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file contains automated tests used to verify that the text terms # at the end of sqlite_stat1.stat are processed correctly. # # (1) "unordered" means that the index cannot be used for ORDER BY # or for range queries # # (2) "sz=NNN" sets the relative size of the index entries # # (3) All other fields are silently ignored # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix analyzeC # Baseline case. Range queries work OK. Indexes can be used for # ORDER BY. # do_execsql_test 1.0 { CREATE TABLE t1(a,b,c); INSERT INTO t1(a,b,c) VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111); CREATE INDEX t1a ON t1(a); CREATE INDEX t1b ON t1(b); ANALYZE; DELETE FROM sqlite_stat1; INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4'); ANALYZE sqlite_master; SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; } {4 5 6 # 7 8 9 # 4 8 12 #} do_execsql_test 1.1 { EXPLAIN QUERY PLAN SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; } {/.* USING INDEX t1a .a>. AND a<...*/} do_execsql_test 1.2 { SELECT c FROM t1 ORDER BY a; } {3 111 6 12 9 12} do_execsql_test 1.3 { EXPLAIN QUERY PLAN SELECT c FROM t1 ORDER BY a; } {/.*SCAN TABLE t1 USING INDEX t1a.*/} do_execsql_test 1.3x { EXPLAIN QUERY PLAN SELECT c FROM t1 ORDER BY a; } {~/.*B-TREE FOR ORDER BY.*/} # Now mark the t1a index as "unordered". Range queries and ORDER BY no # longer use the index, but equality queries do. # do_execsql_test 2.0 { UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a'; ANALYZE sqlite_master; SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; } {4 5 6 # 7 8 9 # 4 8 12 #} do_execsql_test 2.1 { EXPLAIN QUERY PLAN SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; } {~/.*USING INDEX.*/} do_execsql_test 2.2 { SELECT c FROM t1 ORDER BY a; } {3 111 6 12 9 12} do_execsql_test 2.3 { EXPLAIN QUERY PLAN SELECT c FROM t1 ORDER BY a; } {~/.*USING INDEX.*/} do_execsql_test 2.3x { EXPLAIN QUERY PLAN SELECT c FROM t1 ORDER BY a; } {/.*B-TREE FOR ORDER BY.*/} # Ignore extraneous text parameters in the sqlite_stat1.stat field. # do_execsql_test 3.0 { UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11' WHERE idx='t1a'; ANALYZE sqlite_master; SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; } {4 5 6 # 7 8 9 # 4 8 12 #} do_execsql_test 3.1 { EXPLAIN QUERY PLAN SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; } {~/.*USING INDEX.*/} do_execsql_test 3.2 { SELECT c FROM t1 ORDER BY a; } {3 111 6 12 9 12} do_execsql_test 3.3 { EXPLAIN QUERY PLAN SELECT c FROM t1 ORDER BY a; } {~/.*USING INDEX.*/} do_execsql_test 3.3x { EXPLAIN QUERY PLAN SELECT c FROM t1 ORDER BY a; } {/.*B-TREE FOR ORDER BY.*/} # The sz=NNN parameter determines which index to scan # do_execsql_test 4.0 { DROP INDEX t1a; CREATE INDEX t1ab ON t1(a,b); CREATE INDEX t1ca ON t1(c,a); DELETE FROM sqlite_stat1; INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20'); ANALYZE sqlite_master; SELECT count(a) FROM t1; } {6} do_execsql_test 4.1 { EXPLAIN QUERY PLAN SELECT count(a) FROM t1; } {/.*INDEX t1ab.*/} do_execsql_test 4.2 { DELETE FROM sqlite_stat1; INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10'); ANALYZE sqlite_master; SELECT count(a) FROM t1; } {6} do_execsql_test 4.3 { EXPLAIN QUERY PLAN SELECT count(a) FROM t1; } {/.*INDEX t1ca.*/} # The sz=NNN parameter works even if there is other extraneous text # in the sqlite_stat1.stat column. # do_execsql_test 5.0 { DELETE FROM sqlite_stat1; INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'), ('t1','t1ca','12345 3 2 whatever sz=20 junk'); ANALYZE sqlite_master; SELECT count(a) FROM t1; } {6} do_execsql_test 5.1 { EXPLAIN QUERY PLAN SELECT count(a) FROM t1; } {/.*INDEX t1ab.*/} do_execsql_test 5.2 { DELETE FROM sqlite_stat1; INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'), ('t1','t1ab','12345 3 2 whatever sz=20 junk'); ANALYZE sqlite_master; SELECT count(a) FROM t1; } {6} do_execsql_test 5.3 { EXPLAIN QUERY PLAN SELECT count(a) FROM t1; } {/.*INDEX t1ca.*/} finish_test |