Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch analyze-empty-tables Excluding Merge-Ins
This is equivalent to a diff from 234ede26e3 to c81f260c2d
2017-06-07
| ||
22:32 | In SQLITE_DEBUG mode, attempt to log the page number of the database that contained the problem when SQLITE_CORRUPT errors are seen. (check-in: e39795d7d7 user: drh tags: trunk) | |
16:25 | Merge in trunk enhancements. (check-in: f8bbb608cb user: drh tags: prepare_v3) | |
10:55 | Cause the ANALYZE command to build sqlite_stat1 table entries for empty tables with the assumption that such tables really contain 10 elements. This gives better query plans for tables that are truely empty, but is likely to cause problems in legacy systems, so the change is kept off trunk. Some TCL tests fail on this check-in due to the new stat1 entries. (Leaf check-in: c81f260c2d user: drh tags: analyze-empty-tables) | |
2017-06-06
| ||
18:22 | Add the SQLITE_DEFAULT_ROWEST compile-time option for changing the estimated number of rows in tables that lack sqlite_stat1 entries. (check-in: 802b82f342 user: drh tags: branch-3.8.9) | |
18:20 | Add the SQLITE_DEFAULT_ROWEST compile-time option for changing the estimated number of rows in tables that lack sqlite_stat1 entries. (check-in: 234ede26e3 user: drh tags: trunk) | |
2017-06-05
| ||
19:20 | Fix a subtle bug in the remember UDF of the kvtest.exe utility program. (check-in: 9eea3670e7 user: drh tags: trunk) | |
Changes to src/analyze.c.
︙ | ︙ | |||
871 872 873 874 875 876 877 | char *zRet = sqlite3MallocZero( (p->nKeyCol+1)*25 ); if( zRet==0 ){ sqlite3_result_error_nomem(context); return; } | > | < | 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 | char *zRet = sqlite3MallocZero( (p->nKeyCol+1)*25 ); if( zRet==0 ){ sqlite3_result_error_nomem(context); return; } /* Never let the estimated number of rows be less than 10 */ sqlite3_snprintf(24, zRet, "%llu", MAX((u64)p->nRow, 10)); z = zRet + sqlite3Strlen30(zRet); for(i=0; i<p->nKeyCol; i++){ u64 nDistinct = p->current.anDLt[i] + 1; u64 iVal = (p->nRow + nDistinct - 1) / nDistinct; sqlite3_snprintf(24, z, " %llu", iVal); z += sqlite3Strlen30(z); } assert( z[0]=='\0' && z>zRet ); sqlite3_result_text(context, zRet, -1, sqlite3_free); } #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 else if( eCall==STAT_GET_ROWID ){ |
︙ | ︙ | |||
980 981 982 983 984 985 986 | ){ sqlite3 *db = pParse->db; /* Database handle */ Index *pIdx; /* An index to being analyzed */ int iIdxCur; /* Cursor open on index being analyzed */ int iTabCur; /* Table cursor */ Vdbe *v; /* The virtual machine being built up */ int i; /* Loop counter */ | < | 980 981 982 983 984 985 986 987 988 989 990 991 992 993 | ){ sqlite3 *db = pParse->db; /* Database handle */ Index *pIdx; /* An index to being analyzed */ int iIdxCur; /* Cursor open on index being analyzed */ int iTabCur; /* Table cursor */ Vdbe *v; /* The virtual machine being built up */ int i; /* Loop counter */ int iDb; /* Index of database containing pTab */ u8 needTableCnt = 1; /* True to count the table */ int regNewRowid = iMem++; /* Rowid for the inserted record */ int regStat4 = iMem++; /* Register to hold Stat4Accum object */ int regChng = iMem++; /* Index of changed index field */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int regRowid = iMem++; /* Rowid argument passed to stat_push() */ |
︙ | ︙ | |||
1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 | assert( regChng==(regStat4+1) ); sqlite3VdbeAddOp4(v, OP_Function0, 1, regStat4, regTemp, (char*)&statPushFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 2+IsStat34); sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, addrNextRow); VdbeCoverage(v); /* Add the entry to the stat1 table. */ callStatGet(v, regStat4, STAT_GET_STAT1, regStat1); assert( "BBB"[0]==SQLITE_AFF_TEXT ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); | > | 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 | assert( regChng==(regStat4+1) ); sqlite3VdbeAddOp4(v, OP_Function0, 1, regStat4, regTemp, (char*)&statPushFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 2+IsStat34); sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, addrNextRow); VdbeCoverage(v); /* Add the entry to the stat1 table. */ sqlite3VdbeJumpHere(v, addrRewind); callStatGet(v, regStat4, STAT_GET_STAT1, regStat1); assert( "BBB"[0]==SQLITE_AFF_TEXT ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); |
︙ | ︙ | |||
1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 | int regSampleRowid = regCol + nCol; int addrNext; int addrIsNull; u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound; pParse->nMem = MAX(pParse->nMem, regCol+nCol); addrNext = sqlite3VdbeCurrentAddr(v); callStatGet(v, regStat4, STAT_GET_ROWID, regSampleRowid); addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regSampleRowid); VdbeCoverage(v); callStatGet(v, regStat4, STAT_GET_NEQ, regEq); callStatGet(v, regStat4, STAT_GET_NLT, regLt); callStatGet(v, regStat4, STAT_GET_NDLT, regDLt); | > > | 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 | int regSampleRowid = regCol + nCol; int addrNext; int addrIsNull; u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound; pParse->nMem = MAX(pParse->nMem, regCol+nCol); addrRewind = sqlite3VdbeAddOp1(v, OP_Rewind, iIdxCur); VdbeCoverage(v); addrNext = sqlite3VdbeCurrentAddr(v); callStatGet(v, regStat4, STAT_GET_ROWID, regSampleRowid); addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regSampleRowid); VdbeCoverage(v); callStatGet(v, regStat4, STAT_GET_NEQ, regEq); callStatGet(v, regStat4, STAT_GET_NLT, regLt); callStatGet(v, regStat4, STAT_GET_NDLT, regDLt); |
︙ | ︙ | |||
1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 | sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol, regSample); #endif sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regTemp, regNewRowid); sqlite3VdbeAddOp2(v, OP_Goto, 1, addrNext); /* P1==1 for end-of-loop */ sqlite3VdbeJumpHere(v, addrIsNull); } #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ /* End of analysis */ | > < | < | 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 | sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol, regSample); #endif sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regTemp, regNewRowid); sqlite3VdbeAddOp2(v, OP_Goto, 1, addrNext); /* P1==1 for end-of-loop */ sqlite3VdbeJumpHere(v, addrIsNull); sqlite3VdbeJumpHere(v, addrRewind); } #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ /* End of analysis */ } /* Create a single sqlite_stat1 entry containing NULL as the index ** name and the row count as the content. */ if( pOnlyIdx==0 && needTableCnt ){ VdbeComment((v, "%s", pTab->zName)); sqlite3VdbeAddOp2(v, OP_Count, iTabCur, regStat1); sqlite3VdbeAddOp2(v, OP_AddImm, regStat1, 10); sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname); assert( "BBB"[0]==SQLITE_AFF_TEXT ); sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0); sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid); sqlite3VdbeChangeP5(v, OPFLAG_APPEND); } } /* ** Generate code that will cause the most recent index analysis to ** be loaded into internal hash tables where is can be used. |
︙ | ︙ |
Changes to test/analyze.test.
︙ | ︙ | |||
92 93 94 95 96 97 98 | ANALYZE main.t1; } } {0 {}} do_test analyze-1.11 { execsql { SELECT * FROM sqlite_stat1 } | | | | | | | | | 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 | ANALYZE main.t1; } } {0 {}} do_test analyze-1.11 { execsql { SELECT * FROM sqlite_stat1 } } {t1 {} 0} do_test analyze-1.12 { catchsql { ANALYZE t1; } } {0 {}} do_test analyze-1.13 { execsql { SELECT * FROM sqlite_stat1 } } {t1 {} 0} # Create some indices that can be analyzed. # Zero sqlite_stat1 entries are created. # do_test analyze-2.1 { execsql { CREATE INDEX t1i1 ON t1(a); ANALYZE main.t1; SELECT * FROM sqlite_stat1 ORDER BY idx; } } {t1 t1i1 {0 0}} do_test analyze-2.2 { execsql { CREATE INDEX t1i2 ON t1(b); ANALYZE t1; SELECT * FROM sqlite_stat1 ORDER BY idx; } } {t1 t1i1 {0 0} t1 t1i2 {0 0}} do_test analyze-2.3 { execsql { CREATE INDEX t1i3 ON t1(a,b); ANALYZE main; SELECT * FROM sqlite_stat1 ORDER BY idx; } } {t1 t1i1 {0 0} t1 t1i2 {0 0} t1 t1i3 {0 0 0}} # Start adding data to the table. Verify that the analysis # is done correctly. # do_test analyze-3.1 { execsql { INSERT INTO t1 VALUES(1,2); |
︙ | ︙ |
Changes to test/analyze3.test.
︙ | ︙ | |||
694 695 696 697 698 699 700 | CREATE TABLE t1(a,b,c); CREATE INDEX t1a ON t1(a); ANALYZE; SELECT * FROM sqlite_stat1; INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000'); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000'); ANALYZE sqlite_master; | | | 694 695 696 697 698 699 700 701 702 703 | CREATE TABLE t1(a,b,c); CREATE INDEX t1a ON t1(a); ANALYZE; SELECT * FROM sqlite_stat1; INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000'); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000'); ANALYZE sqlite_master; } {t1 t1a {0 0}} finish_test |