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 871 872 872 char *zRet = sqlite3MallocZero( (p->nKeyCol+1)*25 ); 873 873 if( zRet==0 ){ 874 874 sqlite3_result_error_nomem(context); 875 875 return; 876 876 } 877 877 878 - sqlite3_snprintf(24, zRet, "%llu", (u64)p->nRow); 878 + /* Never let the estimated number of rows be less than 10 */ 879 + sqlite3_snprintf(24, zRet, "%llu", MAX((u64)p->nRow, 10)); 879 880 z = zRet + sqlite3Strlen30(zRet); 880 881 for(i=0; i<p->nKeyCol; i++){ 881 882 u64 nDistinct = p->current.anDLt[i] + 1; 882 883 u64 iVal = (p->nRow + nDistinct - 1) / nDistinct; 883 884 sqlite3_snprintf(24, z, " %llu", iVal); 884 885 z += sqlite3Strlen30(z); 885 - assert( p->current.anEq[i] ); 886 886 } 887 887 assert( z[0]=='\0' && z>zRet ); 888 888 889 889 sqlite3_result_text(context, zRet, -1, sqlite3_free); 890 890 } 891 891 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 892 892 else if( eCall==STAT_GET_ROWID ){ ................................................................................ 980 980 ){ 981 981 sqlite3 *db = pParse->db; /* Database handle */ 982 982 Index *pIdx; /* An index to being analyzed */ 983 983 int iIdxCur; /* Cursor open on index being analyzed */ 984 984 int iTabCur; /* Table cursor */ 985 985 Vdbe *v; /* The virtual machine being built up */ 986 986 int i; /* Loop counter */ 987 - int jZeroRows = -1; /* Jump from here if number of rows is zero */ 988 987 int iDb; /* Index of database containing pTab */ 989 988 u8 needTableCnt = 1; /* True to count the table */ 990 989 int regNewRowid = iMem++; /* Rowid for the inserted record */ 991 990 int regStat4 = iMem++; /* Register to hold Stat4Accum object */ 992 991 int regChng = iMem++; /* Index of changed index field */ 993 992 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 994 993 int regRowid = iMem++; /* Rowid argument passed to stat_push() */ ................................................................................ 1215 1214 assert( regChng==(regStat4+1) ); 1216 1215 sqlite3VdbeAddOp4(v, OP_Function0, 1, regStat4, regTemp, 1217 1216 (char*)&statPushFuncdef, P4_FUNCDEF); 1218 1217 sqlite3VdbeChangeP5(v, 2+IsStat34); 1219 1218 sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, addrNextRow); VdbeCoverage(v); 1220 1219 1221 1220 /* Add the entry to the stat1 table. */ 1221 + sqlite3VdbeJumpHere(v, addrRewind); 1222 1222 callStatGet(v, regStat4, STAT_GET_STAT1, regStat1); 1223 1223 assert( "BBB"[0]==SQLITE_AFF_TEXT ); 1224 1224 sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0); 1225 1225 sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); 1226 1226 sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid); 1227 1227 sqlite3VdbeChangeP5(v, OPFLAG_APPEND); 1228 1228 ................................................................................ 1237 1237 int regSampleRowid = regCol + nCol; 1238 1238 int addrNext; 1239 1239 int addrIsNull; 1240 1240 u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound; 1241 1241 1242 1242 pParse->nMem = MAX(pParse->nMem, regCol+nCol); 1243 1243 1244 + addrRewind = sqlite3VdbeAddOp1(v, OP_Rewind, iIdxCur); 1245 + VdbeCoverage(v); 1244 1246 addrNext = sqlite3VdbeCurrentAddr(v); 1245 1247 callStatGet(v, regStat4, STAT_GET_ROWID, regSampleRowid); 1246 1248 addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regSampleRowid); 1247 1249 VdbeCoverage(v); 1248 1250 callStatGet(v, regStat4, STAT_GET_NEQ, regEq); 1249 1251 callStatGet(v, regStat4, STAT_GET_NLT, regLt); 1250 1252 callStatGet(v, regStat4, STAT_GET_NDLT, regDLt); ................................................................................ 1262 1264 sqlite3VdbeAddOp3(v, OP_MakeRecord, regCol, nCol, regSample); 1263 1265 #endif 1264 1266 sqlite3VdbeAddOp3(v, OP_MakeRecord, regTabname, 6, regTemp); 1265 1267 sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid); 1266 1268 sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regTemp, regNewRowid); 1267 1269 sqlite3VdbeAddOp2(v, OP_Goto, 1, addrNext); /* P1==1 for end-of-loop */ 1268 1270 sqlite3VdbeJumpHere(v, addrIsNull); 1271 + sqlite3VdbeJumpHere(v, addrRewind); 1269 1272 } 1270 1273 #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ 1271 1274 1272 1275 /* End of analysis */ 1273 - sqlite3VdbeJumpHere(v, addrRewind); 1274 1276 } 1275 1277 1276 1278 1277 1279 /* Create a single sqlite_stat1 entry containing NULL as the index 1278 1280 ** name and the row count as the content. 1279 1281 */ 1280 1282 if( pOnlyIdx==0 && needTableCnt ){ 1281 1283 VdbeComment((v, "%s", pTab->zName)); 1282 1284 sqlite3VdbeAddOp2(v, OP_Count, iTabCur, regStat1); 1283 - jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1); VdbeCoverage(v); 1285 + sqlite3VdbeAddOp2(v, OP_AddImm, regStat1, 10); 1284 1286 sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname); 1285 1287 assert( "BBB"[0]==SQLITE_AFF_TEXT ); 1286 1288 sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0); 1287 1289 sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid); 1288 1290 sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid); 1289 1291 sqlite3VdbeChangeP5(v, OPFLAG_APPEND); 1290 - sqlite3VdbeJumpHere(v, jZeroRows); 1291 1292 } 1292 1293 } 1293 1294 1294 1295 1295 1296 /* 1296 1297 ** Generate code that will cause the most recent index analysis to 1297 1298 ** be loaded into internal hash tables where is can be used.
Changes to test/analyze.test.
92 92 ANALYZE main.t1; 93 93 } 94 94 } {0 {}} 95 95 do_test analyze-1.11 { 96 96 execsql { 97 97 SELECT * FROM sqlite_stat1 98 98 } 99 -} {} 99 +} {t1 {} 0} 100 100 do_test analyze-1.12 { 101 101 catchsql { 102 102 ANALYZE t1; 103 103 } 104 104 } {0 {}} 105 105 do_test analyze-1.13 { 106 106 execsql { 107 107 SELECT * FROM sqlite_stat1 108 108 } 109 -} {} 109 +} {t1 {} 0} 110 110 111 -# Create some indices that can be analyzed. But do not yet add 112 -# data. Without data in the tables, no analysis is done. 111 +# Create some indices that can be analyzed. 112 +# Zero sqlite_stat1 entries are created. 113 113 # 114 114 do_test analyze-2.1 { 115 115 execsql { 116 116 CREATE INDEX t1i1 ON t1(a); 117 117 ANALYZE main.t1; 118 118 SELECT * FROM sqlite_stat1 ORDER BY idx; 119 119 } 120 -} {} 120 +} {t1 t1i1 {0 0}} 121 121 do_test analyze-2.2 { 122 122 execsql { 123 123 CREATE INDEX t1i2 ON t1(b); 124 124 ANALYZE t1; 125 125 SELECT * FROM sqlite_stat1 ORDER BY idx; 126 126 } 127 -} {} 127 +} {t1 t1i1 {0 0} t1 t1i2 {0 0}} 128 128 do_test analyze-2.3 { 129 129 execsql { 130 130 CREATE INDEX t1i3 ON t1(a,b); 131 131 ANALYZE main; 132 132 SELECT * FROM sqlite_stat1 ORDER BY idx; 133 133 } 134 -} {} 134 +} {t1 t1i1 {0 0} t1 t1i2 {0 0} t1 t1i3 {0 0 0}} 135 135 136 136 # Start adding data to the table. Verify that the analysis 137 137 # is done correctly. 138 138 # 139 139 do_test analyze-3.1 { 140 140 execsql { 141 141 INSERT INTO t1 VALUES(1,2);
Changes to test/analyze3.test.
694 694 CREATE TABLE t1(a,b,c); 695 695 CREATE INDEX t1a ON t1(a); 696 696 ANALYZE; 697 697 SELECT * FROM sqlite_stat1; 698 698 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000'); 699 699 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1a','12000'); 700 700 ANALYZE sqlite_master; 701 -} 701 +} {t1 t1a {0 0}} 702 702 703 703 finish_test