/ Changes On Branch analyze-empty-tables
Login

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