Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Re-enable reading from the sqlite_stat3 table (as well as sqlite_stat4). |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | sqlite_stat4 |
Files: | files | file ages | folders |
SHA1: |
6d45078e621526fc2bac0eaefbb0f960 |
User & Date: | dan 2013-08-12 16:34:32.514 |
Context
2013-08-12
| ||
17:00 | Handle a NULL input to decodeIntArray() that can result from a prior OOM. (check-in: fa1588adab user: drh tags: sqlite_stat4) | |
16:34 | Re-enable reading from the sqlite_stat3 table (as well as sqlite_stat4). (check-in: 6d45078e62 user: dan tags: sqlite_stat4) | |
11:21 | Fix a bug in calculating the average number of entries for keys not present in the sqlite_stat4 table. (check-in: ec3ffb1748 user: dan tags: sqlite_stat4) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
164 165 166 167 168 169 170 | const char *zWhereType /* Either "tbl" or "idx" */ ){ static const struct { const char *zName; const char *zCols; } aTable[] = { { "sqlite_stat1", "tbl,idx,stat" }, | | > > | 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | const char *zWhereType /* Either "tbl" or "idx" */ ){ static const struct { const char *zName; const char *zCols; } aTable[] = { { "sqlite_stat1", "tbl,idx,stat" }, #if defined(SQLITE_ENABLE_STAT4) { "sqlite_stat4", "tbl,idx,neq,nlt,ndlt,sample" }, #elif defined(SQLITE_ENABLE_STAT3) { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" }, #endif }; int aRoot[] = {0, 0}; u8 aCreateTbl[] = {0, 0}; int i; |
︙ | ︙ | |||
1202 1203 1204 1205 1206 1207 1208 1209 | #else UNUSED_PARAMETER(db); UNUSED_PARAMETER(pIdx); #endif } #ifdef SQLITE_ENABLE_STAT4 /* | > > > > > > > > > > > > > > > > > > > > | > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > < < < < | < < > > > > > > > | | | | | | | < | | | | > | > | 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 | #else UNUSED_PARAMETER(db); UNUSED_PARAMETER(pIdx); #endif } #ifdef SQLITE_ENABLE_STAT4 /* ** The implementation of the sqlite_record() function. This function accepts ** a single argument of any type. The return value is a formatted database ** record (a blob) containing the argument value. ** ** This is used to convert the value stored in the 'sample' column of the ** sqlite_stat3 table to the record format SQLite uses internally. */ static void recordFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ const int file_format = 1; int iSerial; /* Serial type */ int nSerial; /* Bytes of space for iSerial as varint */ int nVal; /* Bytes of space required for argv[0] */ int nRet; sqlite3 *db; u8 *aRet; iSerial = sqlite3VdbeSerialType(argv[0], file_format); nSerial = sqlite3VarintLen(iSerial); nVal = sqlite3VdbeSerialTypeLen(iSerial); db = sqlite3_context_db_handle(context); nRet = 1 + nSerial + nVal; aRet = sqlite3DbMallocRaw(db, nRet); if( aRet==0 ){ sqlite3_result_error_nomem(context); }else{ aRet[0] = nSerial+1; sqlite3PutVarint(&aRet[1], iSerial); sqlite3VdbeSerialPut(&aRet[1+nSerial], nVal, argv[0], file_format); sqlite3_result_blob(context, aRet, nRet, SQLITE_TRANSIENT); sqlite3DbFree(db, aRet); } } /* ** Register built-in functions used to help read ANALYZE data. */ void sqlite3AnalyzeFunctions(void){ static SQLITE_WSD FuncDef aAnalyzeTableFuncs[] = { FUNCTION(sqlite_record, 1, 0, 0, recordFunc), }; int i; FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions); FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aAnalyzeTableFuncs); for(i=0; i<ArraySize(aAnalyzeTableFuncs); i++){ sqlite3FuncDefInsert(pHash, &aFunc[i]); } } /* ** Load the content from either the sqlite_stat4 or sqlite_stat3 table ** into the relevant Index.aSample[] arrays. ** ** Arguments zSql1 and zSql2 must point to SQL statements that return ** data equivalent to the following (statements are different for stat3, ** see the caller of this function for details): ** ** zSql1: SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx ** zSql2: SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4 ** ** where %Q is replaced with the database name before the SQL is executed. */ static int loadStatTbl( sqlite3 *db, /* Database handle */ int bStat3, /* Assume single column records only */ const char *zSql1, /* SQL statement 1 (see above) */ const char *zSql2, /* SQL statement 2 (see above) */ const char *zDb /* Database name (e.g. "main") */ ){ int rc; /* Result codes from subroutines */ sqlite3_stmt *pStmt = 0; /* An SQL statement being run */ char *zSql; /* Text of the SQL statement */ Index *pPrevIdx = 0; /* Previous index in the loop */ int idx = 0; /* slot in pIdx->aSample[] for next sample */ IndexSample *pSample; /* A slot in pIdx->aSample[] */ assert( db->lookaside.bEnabled==0 ); zSql = sqlite3MPrintf(db, zSql1, zDb); if( !zSql ){ return SQLITE_NOMEM; } rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); sqlite3DbFree(db, zSql); if( rc ) return rc; while( sqlite3_step(pStmt)==SQLITE_ROW ){ int nIdxCol = 1; /* Number of columns in stat4 records */ int nAvgCol = 1; /* Number of entries in Index.aAvgEq */ char *zIndex; /* Index name */ Index *pIdx; /* Pointer to the index object */ int nSample; /* Number of samples */ int nByte; /* Bytes of space required */ int i; /* Bytes of space required */ tRowcnt *pSpace; zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; nSample = sqlite3_column_int(pStmt, 1); pIdx = sqlite3FindIndex(db, zIndex, zDb); if( pIdx==0 ) continue; assert( pIdx->nSample==0 ); if( bStat3==0 ){ nIdxCol = pIdx->nColumn+1; nAvgCol = pIdx->nColumn; } pIdx->nSample = nSample; nByte = sizeof(IndexSample) * nSample; nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample; nByte += nAvgCol * sizeof(tRowcnt); /* Space for Index.aAvgEq[] */ pIdx->aSample = sqlite3DbMallocZero(db, nByte); if( pIdx->aSample==0 ){ sqlite3_finalize(pStmt); return SQLITE_NOMEM; } pSpace = (tRowcnt*)&pIdx->aSample[nSample]; pIdx->aAvgEq = pSpace; pSpace += nAvgCol; for(i=0; i<pIdx->nSample; i++){ pIdx->aSample[i].anEq = pSpace; pSpace += nIdxCol; pIdx->aSample[i].anLt = pSpace; pSpace += nIdxCol; pIdx->aSample[i].anDLt = pSpace; pSpace += nIdxCol; } assert( ((u8*)pSpace)-nByte==(u8*)(pIdx->aSample) ); } rc = sqlite3_finalize(pStmt); if( rc ) return rc; zSql = sqlite3MPrintf(db, zSql2, zDb); if( !zSql ){ return SQLITE_NOMEM; } rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0); sqlite3DbFree(db, zSql); if( rc ) return rc; while( sqlite3_step(pStmt)==SQLITE_ROW ){ char *zIndex; /* Index name */ Index *pIdx; /* Pointer to the index object */ int i; /* Loop counter */ int nCol = 1; /* Number of columns in index */ zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; pIdx = sqlite3FindIndex(db, zIndex, zDb); if( pIdx==0 ) continue; if( pIdx==pPrevIdx ){ idx++; }else{ pPrevIdx = pIdx; idx = 0; } assert( idx<pIdx->nSample ); pSample = &pIdx->aSample[idx]; if( bStat3==0 ){ nCol = pIdx->nColumn+1; } decodeIntArray((char*)sqlite3_column_text(pStmt,1), nCol, pSample->anEq, 0); decodeIntArray((char*)sqlite3_column_text(pStmt,2), nCol, pSample->anLt, 0); decodeIntArray((char*)sqlite3_column_text(pStmt,3), nCol, pSample->anDLt,0); if( idx==pIdx->nSample-1 ){ IndexSample *aSample = pIdx->aSample; int iCol; |
︙ | ︙ | |||
1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 | } } if( nDLt>nSum ){ avgEq = (pSample->anLt[iCol] - sumEq)/(nDLt - nSum); } if( avgEq==0 ) avgEq = 1; pIdx->aAvgEq[iCol] = avgEq; } } pSample->n = sqlite3_column_bytes(pStmt, 4); pSample->p = sqlite3DbMallocZero(db, pSample->n); if( pSample->p==0 ){ sqlite3_finalize(pStmt); return SQLITE_NOMEM; } memcpy(pSample->p, sqlite3_column_blob(pStmt, 4), pSample->n); } return sqlite3_finalize(pStmt); } #endif /* SQLITE_ENABLE_STAT4 */ /* ** Load the content of the sqlite_stat1 and sqlite_stat4 tables. The ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[] ** arrays. The contents of sqlite_stat4 are used to populate the ** Index.aSample[] arrays. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 | } } if( nDLt>nSum ){ avgEq = (pSample->anLt[iCol] - sumEq)/(nDLt - nSum); } if( avgEq==0 ) avgEq = 1; pIdx->aAvgEq[iCol] = avgEq; if( bStat3 ) break; } } pSample->n = sqlite3_column_bytes(pStmt, 4); pSample->p = sqlite3DbMallocZero(db, pSample->n); if( pSample->p==0 ){ sqlite3_finalize(pStmt); return SQLITE_NOMEM; } memcpy(pSample->p, sqlite3_column_blob(pStmt, 4), pSample->n); } return sqlite3_finalize(pStmt); } /* ** Load content from the sqlite_stat4 and sqlite_stat3 tables into ** the Index.aSample[] arrays of all indices. */ static int loadStat4(sqlite3 *db, const char *zDb){ int rc = SQLITE_OK; /* Result codes from subroutines */ assert( db->lookaside.bEnabled==0 ); if( sqlite3FindTable(db, "sqlite_stat4", zDb) ){ rc = loadStatTbl(db, 0, "SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx", "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4", zDb ); } if( rc==SQLITE_OK && sqlite3FindTable(db, "sqlite_stat3", zDb) ){ rc = loadStatTbl(db, 1, "SELECT idx,count(*) FROM %Q.sqlite_stat3 GROUP BY idx", "SELECT idx,neq,nlt,ndlt,sqlite_record(sample) FROM %Q.sqlite_stat3", zDb ); } return rc; } #endif /* SQLITE_ENABLE_STAT4 */ /* ** Load the content of the sqlite_stat1 and sqlite_stat4 tables. The ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[] ** arrays. The contents of sqlite_stat4 are used to populate the ** Index.aSample[] arrays. |
︙ | ︙ |
Changes to src/func.c.
︙ | ︙ | |||
1710 1711 1712 1713 1714 1715 1716 1717 1718 | for(i=0; i<ArraySize(aBuiltinFunc); i++){ sqlite3FuncDefInsert(pHash, &aFunc[i]); } sqlite3RegisterDateTimeFunctions(); #ifndef SQLITE_OMIT_ALTERTABLE sqlite3AlterFunctions(); #endif } | > > > | 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 | for(i=0; i<ArraySize(aBuiltinFunc); i++){ sqlite3FuncDefInsert(pHash, &aFunc[i]); } sqlite3RegisterDateTimeFunctions(); #ifndef SQLITE_OMIT_ALTERTABLE sqlite3AlterFunctions(); #endif #if defined(SQLITE_ENABLE_STAT3) || defined(SQLITE_ENABLE_STAT4) sqlite3AnalyzeFunctions(); #endif } |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 3063 3064 | #ifndef SQLITE_OMIT_WSD extern int sqlite3PendingByte; #endif #endif void sqlite3RootPageMoved(sqlite3*, int, int, int); void sqlite3Reindex(Parse*, Token*, Token*); void sqlite3AlterFunctions(void); void sqlite3AlterRenameTable(Parse*, SrcList*, Token*); int sqlite3GetToken(const unsigned char *, int *); void sqlite3NestedParse(Parse*, const char*, ...); void sqlite3ExpirePreparedStatements(sqlite3*); int sqlite3CodeSubselect(Parse *, Expr *, int, int); void sqlite3SelectPrep(Parse*, Select*, NameContext*); int sqlite3MatchSpanName(const char*, const char*, const char*, const char*); | > | 3051 3052 3053 3054 3055 3056 3057 3058 3059 3060 3061 3062 3063 3064 3065 | #ifndef SQLITE_OMIT_WSD extern int sqlite3PendingByte; #endif #endif void sqlite3RootPageMoved(sqlite3*, int, int, int); void sqlite3Reindex(Parse*, Token*, Token*); void sqlite3AlterFunctions(void); void sqlite3AnalyzeFunctions(void); void sqlite3AlterRenameTable(Parse*, SrcList*, Token*); int sqlite3GetToken(const unsigned char *, int *); void sqlite3NestedParse(Parse*, const char*, ...); void sqlite3ExpirePreparedStatements(sqlite3*); int sqlite3CodeSubselect(Parse *, Expr *, int, int); void sqlite3SelectPrep(Parse*, Select*, NameContext*); int sqlite3MatchSpanName(const char*, const char*, const char*, const char*); |
︙ | ︙ |
Changes to src/test_func.c.
︙ | ︙ | |||
456 457 458 459 460 461 462 463 464 465 466 467 468 469 | zOut[14-i*2] = "0123456789abcdef"[v.x[i]>>4]; zOut[14-i*2+1] = "0123456789abcdef"[v.x[i]&0xf]; } } zOut[16] = 0; sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT); } /* ** tclcmd: test_decode(record) ** ** This function implements an SQL user-function that accepts a blob ** containing a formatted database record as its only argument. It returns ** a tcl list (type SQLITE_TEXT) containing each of the values stored | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 | zOut[14-i*2] = "0123456789abcdef"[v.x[i]>>4]; zOut[14-i*2+1] = "0123456789abcdef"[v.x[i]&0xf]; } } zOut[16] = 0; sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT); } /* ** tclcmd: test_extract(record, field) ** ** This function implements an SQL user-function that accepts a blob ** containing a formatted database record as the first argument. The ** second argument is the index of the field within that record to ** extract and return. */ static void test_extract( sqlite3_context *context, int argc, sqlite3_value **argv ){ sqlite3 *db = sqlite3_context_db_handle(context); u8 *pRec; u8 *pEndHdr; /* Points to one byte past record header */ u8 *pHdr; /* Current point in record header */ u8 *pBody; /* Current point in record data */ u64 nHdr; /* Bytes in record header */ int iIdx; /* Required field */ int iCurrent = 0; /* Current field */ assert( argc==2 ); pRec = (u8*)sqlite3_value_blob(argv[0]); iIdx = sqlite3_value_int(argv[1]); pHdr = pRec + sqlite3GetVarint(pRec, &nHdr); pBody = pEndHdr = &pRec[nHdr]; for(iCurrent=0; pHdr<pEndHdr && iCurrent<=iIdx; iCurrent++){ u64 iSerialType; Mem mem; memset(&mem, 0, sizeof(mem)); mem.db = db; mem.enc = SQLITE_UTF8; pHdr += sqlite3GetVarint(pHdr, &iSerialType); pBody += sqlite3VdbeSerialGet(pBody, (u32)iSerialType, &mem); sqlite3VdbeMemStoreType(&mem); if( iCurrent==iIdx ){ sqlite3_result_value(context, &mem); } sqlite3DbFree(db, mem.zMalloc); } } /* ** tclcmd: test_decode(record) ** ** This function implements an SQL user-function that accepts a blob ** containing a formatted database record as its only argument. It returns ** a tcl list (type SQLITE_TEXT) containing each of the values stored |
︙ | ︙ | |||
575 576 577 578 579 580 581 582 583 584 585 586 587 588 | { "test_error", 1, SQLITE_UTF8, test_error}, { "test_error", 2, SQLITE_UTF8, test_error}, { "test_eval", 1, SQLITE_UTF8, test_eval}, { "test_isolation", 2, SQLITE_UTF8, test_isolation}, { "test_counter", 1, SQLITE_UTF8, counterFunc}, { "real2hex", 1, SQLITE_UTF8, real2hex}, { "test_decode", 1, SQLITE_UTF8, test_decode}, }; int i; for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg, aFuncs[i].eTextRep, 0, aFuncs[i].xFunc, 0, 0); } | > | 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 | { "test_error", 1, SQLITE_UTF8, test_error}, { "test_error", 2, SQLITE_UTF8, test_error}, { "test_eval", 1, SQLITE_UTF8, test_eval}, { "test_isolation", 2, SQLITE_UTF8, test_isolation}, { "test_counter", 1, SQLITE_UTF8, counterFunc}, { "real2hex", 1, SQLITE_UTF8, real2hex}, { "test_decode", 1, SQLITE_UTF8, test_decode}, { "test_extract", 2, SQLITE_UTF8, test_extract}, }; int i; for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg, aFuncs[i].eTextRep, 0, aFuncs[i].xFunc, 0, 0); } |
︙ | ︙ |
Changes to test/analyze9.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | # 2013 August 3 # # 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. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix analyze9 ifcapable !stat4 { | > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # 2013 August 3 # # 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 sqlite_stat4 # functionality is working. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix analyze9 ifcapable !stat4 { |
︙ | ︙ |
Added test/analyzeA.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 | # 2013 August 3 # # 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 current build # (which must be either ENABLE_STAT3 or ENABLE_STAT4) works with both stat3 # and stat4 data. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix analyzeA ifcapable !stat4&&!stat3 { finish_test return } proc populate_stat3 {} { # Open a second connection on database "test.db" and run ANALYZE. If this # is an ENABLE_STAT3 build, this is all that is required to create and # populate the sqlite_stat3 table. # sqlite3 db2 test.db execsql { ANALYZE } # Now, if this is an ENABLE_STAT4 build, create and populate the # sqlite_stat3 table based on the stat4 data gathered by the ANALYZE # above. Then drop the sqlite_stat4 table. # ifcapable stat4 { db2 func lindex lindex execsql { PRAGMA writable_schema = on; CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample); INSERT INTO sqlite_stat3 SELECT DISTINCT tbl, idx, lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0) FROM sqlite_stat4; DROP TABLE sqlite_stat4; PRAGMA writable_schema = off; } db2 } # Modify the database schema cookie to ensure that the other connection # reloads the schema. # execsql { CREATE TABLE obscure_tbl_nm(x); DROP TABLE obscure_tbl_nm; } db2 db2 close } proc populate_stat4 {} { execsql { ANALYZE } # ifcapable stat3 { # execsql { # PRAGMA writable_schema = on; # CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); # INSERT INTO sqlite_stat4 SELECT # tbl, idx, nlt, neq, ndlt, # test_extract(sample, 1) # FROM sqlite_stat4; # DROP TABLE sqlite_stat4; # PRAGMA writable_schema = off; # ANALYZE sqlite_master; # } # } } foreach {tn analyze_cmd} {1 populate_stat4 2 populate_stat3} { reset_db do_test 1.$tn.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) } for {set i 0} {$i < 100} {incr i} { set c [expr int(pow(1.1,$i)/100)] set b [expr 125 - int(pow(1.1,99-$i))/100] execsql {INSERT INTO t1 VALUES($i, $b, $c)} } } {} execsql { CREATE INDEX t1b ON t1(b) } execsql { CREATE INDEX t1c ON t1(c) } $analyze_cmd do_execsql_test 1.$tn.2.1 { SELECT count(*) FROM t1 WHERE b=31 } 1 do_execsql_test 1.$tn.2.2 { SELECT count(*) FROM t1 WHERE c=0 } 49 do_execsql_test 1.$tn.2.3 { SELECT count(*) FROM t1 WHERE b=125 } 49 do_execsql_test 1.$tn.2.4 { SELECT count(*) FROM t1 WHERE c=16 } 1 do_eqp_test 1.$tn.2.5 { SELECT * FROM t1 WHERE b = 31 AND c = 0; } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} do_eqp_test 1.$tn.2.6 { SELECT * FROM t1 WHERE b = 125 AND c = 16; } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)}} do_execsql_test 1.$tn.3.1 { SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50 } {6} do_execsql_test 1.$tn.3.2 { SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50 } {90} do_execsql_test 1.$tn.3.3 { SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125 } {90} do_execsql_test 1.$tn.3.4 { SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125 } {6} do_eqp_test 1.$tn.3.5 { SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} do_eqp_test 1.$tn.3.6 { SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} } finish_test |