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.
FossilOrigin-Name: c81f260c2d4791e40f74c86927a4e9efb79a9d241a66476e4af35c7bbe37a49c
diff --git a/manifest b/manifest
index bb8b355..6a1735f 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Add\sthe\sSQLITE_DEFAULT_ROWEST\scompile-time\soption\sfor\schanging\sthe\sestimated\nnumber\sof\srows\sin\stables\sthat\slack\ssqlite_stat1\sentries.
-D 2017-06-06T18:20:43.972
+C Cause\sthe\sANALYZE\scommand\sto\sbuild\ssqlite_stat1\stable\sentries\sfor\sempty\stables\nwith\sthe\sassumption\sthat\ssuch\stables\sreally\scontain\s10\selements.\s\sThis\sgives\nbetter\squery\splans\sfor\stables\sthat\sare\struely\sempty,\sbut\sis\slikely\sto\scause\nproblems\sin\slegacy\ssystems,\sso\sthe\schange\sis\skept\soff\strunk.\s\sSome\sTCL\stests\nfail\son\sthis\scheck-in\sdue\sto\sthe\snew\sstat1\sentries.
+D 2017-06-07T10:55:02.135
F Makefile.in 1cc758ce3374a32425e4d130c2fe7b026b20de5b8843243de75f087c0a2661fb
F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434
F Makefile.msc 8eeb80162074004e906b53d7340a12a14c471a83743aab975947e95ce061efcc
@@ -342,7 +342,7 @@
F sqlite3.1 fc7ad8990fc8409983309bb80de8c811a7506786
F sqlite3.pc.in 48fed132e7cb71ab676105d2a4dc77127d8c1f3a
F src/alter.c 3b23977620ce9662ac54443f65b87ba996e36121
-F src/analyze.c 0d0ccf7520a201d8747ea2f02c92c26e26f801bc161f714f27b9f7630dde0421
+F src/analyze.c 62a35801482e72faf30f5ccfc690837d5873ad358f93459e1aff1f13e0dbfd8d
F src/attach.c 8c476f8bd5d2afe11d925f890d30e527e5b0ce43
F src/auth.c 79f96c6f33bf0e5da8d1c282cee5ebb1852bb8a6ccca3e485d7c459b035d9c3c
F src/backup.c faf17e60b43233c214aae6a8179d24503a61e83b
@@ -503,8 +503,8 @@
F test/alter4.test b6d7b86860111864f6cddb54af313f5862dda23b
F test/altermalloc.test e81ac9657ed25c6c5bb09bebfa5a047cd8e4acfc
F test/amatch1.test b5ae7065f042b7f4c1c922933f4700add50cdb9f
-F test/analyze.test 3eb35a4af972f98422e5dc0586501b17d103d321
-F test/analyze3.test 1dccda46a6c374018af617fba00bfe297a61d442
+F test/analyze.test 0e043e6595e404871327a75cb017b671098ad4406d298665d83e8e3b575ee6c7
+F test/analyze3.test d35cffa940a742c8102f128e945084580afd673c0b73ad29984e1fa7ab737c03
F test/analyze4.test eff2df19b8dd84529966420f29ea52edc6b56213
F test/analyze5.test 765c4e284aa69ca172772aa940946f55629bc8c4
F test/analyze6.test f1c552ce39cca4ec922a7e4e0e5d0203d6b3281f
@@ -1582,7 +1582,10 @@
F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 9eea3670e77e2f831b7c00ae2d65a5583f9cad626d9ab286f92582ef29ecd4e3
-R de51f7d1edc1449c33df63cd75f44fee
+P 234ede26e30f20e6c33002739ed8be35dbfb5c77700bd857ff31072b9b7df347
+R 084bdc3fdc2ada1f3649d659e3fd2491
+T *branch * analyze-empty-tables
+T *sym-analyze-empty-tables *
+T -sym-trunk *
U drh
-Z db2906277b1736b6942b7d17ecf808c4
+Z 01ebfe5fb16bb5cd6e6a41ca8b6d6232
diff --git a/manifest.uuid b/manifest.uuid
index 914bd0d..8bb7fcd 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-234ede26e30f20e6c33002739ed8be35dbfb5c77700bd857ff31072b9b7df347
\ No newline at end of file
+c81f260c2d4791e40f74c86927a4e9efb79a9d241a66476e4af35c7bbe37a49c
\ No newline at end of file
diff --git a/src/analyze.c b/src/analyze.c
index 495cc95..11e3be2 100644
--- a/src/analyze.c
+++ b/src/analyze.c
@@ -875,14 +875,14 @@
return;
}
- sqlite3_snprintf(24, zRet, "%llu", (u64)p->nRow);
+ /* 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( p->current.anEq[i] );
}
assert( z[0]=='\0' && z>zRet );
@@ -984,7 +984,6 @@
int iTabCur; /* Table cursor */
Vdbe *v; /* The virtual machine being built up */
int i; /* Loop counter */
- 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 regNewRowid = iMem++; /* Rowid for the inserted record */
@@ -1219,6 +1218,7 @@
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);
@@ -1241,6 +1241,8 @@
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);
@@ -1266,11 +1268,11 @@
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 */
- sqlite3VdbeJumpHere(v, addrRewind);
}
@@ -1280,14 +1282,13 @@
if( pOnlyIdx==0 && needTableCnt ){
VdbeComment((v, "%s", pTab->zName));
sqlite3VdbeAddOp2(v, OP_Count, iTabCur, regStat1);
- jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1); VdbeCoverage(v);
+ 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);
- sqlite3VdbeJumpHere(v, jZeroRows);
}
}
diff --git a/test/analyze.test b/test/analyze.test
index af277cc..87f0dc8 100644
--- a/test/analyze.test
+++ b/test/analyze.test
@@ -96,7 +96,7 @@
execsql {
SELECT * FROM sqlite_stat1
}
-} {}
+} {t1 {} 0}
do_test analyze-1.12 {
catchsql {
ANALYZE t1;
@@ -106,10 +106,10 @@
execsql {
SELECT * FROM sqlite_stat1
}
-} {}
+} {t1 {} 0}
-# Create some indices that can be analyzed. But do not yet add
-# data. Without data in the tables, no analysis is done.
+# Create some indices that can be analyzed.
+# Zero sqlite_stat1 entries are created.
#
do_test analyze-2.1 {
execsql {
@@ -117,21 +117,21 @@
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.
diff --git a/test/analyze3.test b/test/analyze3.test
index 2fb558d..eebc77b 100644
--- a/test/analyze3.test
+++ b/test/analyze3.test
@@ -698,6 +698,6 @@
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