/ Check-in [ded9dec6]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Change the sqlite_stat2 schema to be more flexible.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ded9dec6459baf21e01f63250db5ace57f390e7a
User & Date: dan 2009-08-18 16:24:59
Context
2009-08-19
08:18
Add the SQLITE_ENABLE_STAT2 macro. If this is not defined at build-time, the stat2 table is not created, populated, or used. check-in: 362665e8 user: dan tags: trunk
2009-08-18
16:24
Change the sqlite_stat2 schema to be more flexible. check-in: ded9dec6 user: dan tags: trunk
2009-08-17
17:06
First version of sqlite_stat2 (schema forces exactly 10 samples). check-in: dd96bda2 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

    28     28   static void openStatTable(
    29     29     Parse *pParse,          /* Parsing context */
    30     30     int iDb,                /* The database we are looking in */
    31     31     int iStatCur,           /* Open the sqlite_stat1 table on this cursor */
    32     32     const char *zWhere      /* Delete entries associated with this table */
    33     33   ){
    34     34     const char *aName[] = { "sqlite_stat1", "sqlite_stat2" };
    35         -  const char *aCols[] = { "tbl,idx,stat", "tbl,idx," SQLITE_INDEX_SAMPLE_COLS };
           35  +  const char *aCols[] = { "tbl,idx,stat", "tbl,idx,sampleno,sample" };
    36     36     int aRoot[] = {0, 0};
    37     37     int aCreateTbl[] = {0, 0};
    38     38   
    39     39     int i;
    40     40     sqlite3 *db = pParse->db;
    41     41     Db *pDb;
    42     42     Vdbe *v = sqlite3GetVdbe(pParse);
................................................................................
    90     90     Parse *pParse,   /* Parser context */
    91     91     Table *pTab,     /* Table whose indices are to be analyzed */
    92     92     int iStatCur,    /* Index of VdbeCursor that writes the sqlite_stat1 table */
    93     93     int iMem         /* Available memory locations begin here */
    94     94   ){
    95     95     Index *pIdx;     /* An index to being analyzed */
    96     96     int iIdxCur;     /* Index of VdbeCursor for index being analyzed */
    97         -  int nCol;        /* Number of columns in the index */
    98     97     Vdbe *v;         /* The virtual machine being built up */
    99     98     int i;           /* Loop counter */
   100     99     int topOfLoop;   /* The top of the loop */
   101    100     int endOfLoop;   /* The end of the loop */
   102    101     int addr;        /* The address of an instruction */
   103    102     int iDb;         /* Index of database containing pTab */
   104    103   
          104  +
          105  +  /* Assign the required registers. */
          106  +  int regTabname = iMem++;     /* Register containing table name */
          107  +  int regIdxname = iMem++;     /* Register containing index name */
          108  +  int regSampleno = iMem++;    /* Register containing next sample number */
          109  +  int regCol = iMem++;         /* Content of a column analyzed table */
          110  +
          111  +  int regSamplerecno = iMem++; /* Next sample index record number */
          112  +  int regRecno = iMem++;       /* Register next index record number */
          113  +  int regRec = iMem++;         /* Register holding completed record */
          114  +  int regTemp = iMem++;        /* Temporary use register */
          115  +  int regTemp2 = iMem++;        /* Temporary use register */
          116  +  int regRowid = iMem++;       /* Rowid for the inserted record */
          117  +  int regCount = iMem++;       /* Total number of records in table */
          118  +
   105    119     v = sqlite3GetVdbe(pParse);
   106    120     if( v==0 || NEVER(pTab==0) || pTab->pIndex==0 ){
   107    121       /* Do no analysis for tables that have no indices */
   108    122       return;
   109    123     }
   110    124     assert( sqlite3BtreeHoldsAllMutexes(pParse->db) );
   111    125     iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
................................................................................
   116    130       return;
   117    131     }
   118    132   #endif
   119    133   
   120    134     /* Establish a read-lock on the table at the shared-cache level. */
   121    135     sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
   122    136   
   123         -  iMem += 3;
   124    137     iIdxCur = pParse->nTab++;
   125    138     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          139  +    int nCol = pIdx->nColumn;
   126    140       KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
   127         -    int regFields;    /* Register block for building records */
   128         -    int regRec;       /* Register holding completed record */
   129         -    int regTemp;      /* Temporary use register */
   130         -    int regCol;       /* Content of a column from the table being analyzed */
   131         -    int regRowid;     /* Rowid for the inserted record */
   132         -    int regF2;
   133         -    int regStat2;
   134    141   
   135         -    /* Open a cursor to the index to be analyzed
   136         -    */
          142  +    if( iMem+1+(nCol*2)>pParse->nMem ){
          143  +      pParse->nMem = iMem+1+(nCol*2);
          144  +    }
          145  +
          146  +    /* Open a cursor to the index to be analyzed. */
   137    147       assert( iDb==sqlite3SchemaToIndex(pParse->db, pIdx->pSchema) );
   138         -    nCol = pIdx->nColumn;
   139    148       sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb,
   140    149           (char *)pKey, P4_KEYINFO_HANDOFF);
   141    150       VdbeComment((v, "%s", pIdx->zName));
   142         -    regStat2 = iMem+nCol*2+1;
   143         -    regFields = regStat2+2+SQLITE_INDEX_SAMPLES;
   144         -    regTemp = regRowid = regCol = regFields+3;
   145         -    regRec = regCol+1;
   146         -    if( regRec>pParse->nMem ){
   147         -      pParse->nMem = regRec;
          151  +
          152  +    /* If this iteration of the loop is generating code to analyze the
          153  +    ** first index in the pTab->pIndex list, then register regCount has
          154  +    ** not been populated. In this case populate it now.  */
          155  +    if( pTab->pIndex==pIdx ){
          156  +      sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regCount);
          157  +      sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0);
   148    158       }
          159  +    sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0);
   149    160   
   150         -    /* Fill in the register with the total number of rows. */
   151         -    if( pTab->pIndex==pIdx ){
   152         -      sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, iMem-3);
   153         -    }
   154         -    sqlite3VdbeAddOp2(v, OP_Integer, 0, iMem-2);
   155         -    sqlite3VdbeAddOp2(v, OP_Integer, 1, iMem-1);
          161  +    /* Zero the regSampleno and regRecno registers. */
          162  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno);
          163  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRecno);
          164  +
          165  +    /* If there are less than INDEX_SAMPLES records in the index, then
          166  +    ** set the contents of regSampleRecno to integer value INDEX_SAMPLES.
          167  +    ** Otherwise, set it to zero. This is to ensure that if there are 
          168  +    ** less than the said number of entries in the index, no samples at
          169  +    ** all are collected.  */
          170  +    sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regSamplerecno);
          171  +    sqlite3VdbeAddOp3(v, OP_Lt, regSamplerecno, sqlite3VdbeCurrentAddr(v)+2,
          172  +        regCount);
          173  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSamplerecno);
   156    174   
   157    175       /* Memory cells are used as follows. All memory cell addresses are
   158    176       ** offset by iMem. That is, cell 0 below is actually cell iMem, cell
   159    177       ** 1 is cell 1+iMem, etc.
   160    178       **
   161    179       **    0:               The total number of rows in the table.
   162    180       **
................................................................................
   163    181       **    1..nCol:         Number of distinct entries in index considering the
   164    182       **                     left-most N columns, where N is the same as the 
   165    183       **                     memory cell number.
   166    184       **
   167    185       **    nCol+1..2*nCol:  Previous value of indexed columns, from left to
   168    186       **                     right.
   169    187       **
   170         -    **    2*nCol+1..2*nCol+10: 10 evenly spaced samples.
   171         -    **
   172    188       ** Cells iMem through iMem+nCol are initialized to 0.  The others
   173    189       ** are initialized to NULL.
   174    190       */
   175    191       for(i=0; i<=nCol; i++){
   176    192         sqlite3VdbeAddOp2(v, OP_Integer, 0, iMem+i);
   177    193       }
   178    194       for(i=0; i<nCol; i++){
   179    195         sqlite3VdbeAddOp2(v, OP_Null, 0, iMem+nCol+i+1);
   180    196       }
   181    197   
   182         -    /* Start the analysis loop. This loop runs through all the entries inof
          198  +    /* Start the analysis loop. This loop runs through all the entries in
   183    199       ** the index b-tree.  */
   184    200       endOfLoop = sqlite3VdbeMakeLabel(v);
   185    201       sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
   186    202       topOfLoop = sqlite3VdbeCurrentAddr(v);
   187    203       sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);
   188    204   
   189    205       for(i=0; i<nCol; i++){
   190    206         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
   191    207         if( i==0 ){
   192         -        sqlite3VdbeAddOp3(v, OP_Sample, iMem-3, regCol, regStat2+2);
          208  +
          209  +        /* Check if the record that cursor iIdxCur points to contains a
          210  +        ** value that should be stored in the sqlite_stat2 table. If so,
          211  +        ** store it.  */
          212  +        int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno);
          213  +        assert( regTabname+1==regIdxname 
          214  +             && regTabname+2==regSampleno
          215  +             && regTabname+3==regCol
          216  +        );
          217  +        sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 4, regRec, "aaab", 0);
          218  +        sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid);
          219  +        sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid);
          220  +
          221  +        /* Calculate new values for regSamplerecno and regSampleno.
          222  +        **
          223  +        **   sampleno = sampleno + 1
          224  +        **   samplerecno = samplerecno+(remaining records)/(remaining samples)
          225  +        */
          226  +        sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1);
          227  +        sqlite3VdbeAddOp3(v, OP_Subtract, regRecno, regCount, regTemp);
          228  +        sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1);
          229  +        sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2);
          230  +        sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2);
          231  +        sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp);
          232  +        sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno);
          233  +
          234  +        sqlite3VdbeJumpHere(v, ne);
          235  +        sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1);
   193    236         }
          237  +
          238  +      assert( sqlite3VdbeCurrentAddr(v)==(topOfLoop+14+2*i) );
   194    239         sqlite3VdbeAddOp3(v, OP_Ne, regCol, 0, iMem+nCol+i+1);
          240  +
   195    241         /**** TODO:  add collating sequence *****/
   196    242         sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
   197    243       }
   198    244       sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop);
   199    245       for(i=0; i<nCol; i++){
   200         -      sqlite3VdbeJumpHere(v, topOfLoop + 1 + 2*(i + 1));
          246  +      sqlite3VdbeJumpHere(v, topOfLoop+14+2*i);
   201    247         sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
   202    248         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
   203    249       }
   204    250   
   205    251       /* End of the analysis loop. */
   206    252       sqlite3VdbeResolveLabel(v, endOfLoop);
   207    253       sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
................................................................................
   222    268       **        I = (K+D-1)/D
   223    269       **
   224    270       ** If K==0 then no entry is made into the sqlite_stat1 table.  
   225    271       ** If K>0 then it is always the case the D>0 so division by zero
   226    272       ** is never possible.
   227    273       */
   228    274       addr = sqlite3VdbeAddOp1(v, OP_IfNot, iMem);
   229         -    sqlite3VdbeAddOp4(v, OP_String8, 0, regFields, 0, pTab->zName, 0);
   230         -    sqlite3VdbeAddOp4(v, OP_String8, 0, regFields+1, 0, pIdx->zName, 0);
   231         -    regF2 = regFields+2;
   232         -    sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regF2);
          275  +    sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regSampleno);
   233    276       for(i=0; i<nCol; i++){
   234    277         sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0);
   235         -      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regF2, regF2);
          278  +      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno);
   236    279         sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp);
   237    280         sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1);
   238    281         sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp);
   239    282         sqlite3VdbeAddOp1(v, OP_ToInt, regTemp);
   240         -      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regF2, regF2);
          283  +      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno);
   241    284       }
   242         -    sqlite3VdbeAddOp4(v, OP_MakeRecord, regFields, 3, regRec, "aaa", 0);
          285  +    sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
   243    286       sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid);
   244    287       sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid);
   245    288       sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
   246         -
   247         -    /* Store the results in sqlite_stat2. */
   248         -    sqlite3VdbeAddOp4(v, OP_String8, 0, regStat2, 0, pTab->zName, 0);
   249         -    sqlite3VdbeAddOp4(v, OP_String8, 0, regStat2+1, 0, pIdx->zName, 0);
   250         -    sqlite3VdbeAddOp4(v, OP_MakeRecord, regStat2, SQLITE_INDEX_SAMPLES+2,
   251         -	regRec, "aabbbbbbbbbb", 0
   252         -    );
   253         -    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid);
   254         -    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid);
   255         -
   256    289       sqlite3VdbeJumpHere(v, addr);
   257    290     }
   258    291   }
   259    292   
   260    293   /*
   261    294   ** Generate code that will cause the most recent index analysis to
   262    295   ** be laoded into internal hash tables where is can be used.
................................................................................
   457    490     }else{
   458    491       (void)sqlite3SafetyOff(db);
   459    492       rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
   460    493       (void)sqlite3SafetyOn(db);
   461    494       sqlite3DbFree(db, zSql);
   462    495     }
   463    496   
   464         -  /* Load the statistics from the sqlite_stat2 table */
          497  +  /* Load the statistics from the sqlite_stat2 table. */
   465    498     if( rc==SQLITE_OK ){
          499  +    sqlite3_stmt *pStmt = 0;
          500  +
   466    501       zSql = sqlite3MPrintf(db, 
   467         -	"SELECT idx," SQLITE_INDEX_SAMPLE_COLS " FROM %Q.sqlite_stat2",
   468         -        sInfo.zDatabase
          502  +        "SELECT idx,sampleno,sample FROM %Q.sqlite_stat2", sInfo.zDatabase
   469    503       );
   470         -    if( zSql ){
   471         -      sqlite3_stmt *pStmt = 0;
   472         -      (void)sqlite3SafetyOff(db);
   473         -      rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
   474         -      if( rc==SQLITE_OK ){
   475         -	while( SQLITE_ROW==sqlite3_step(pStmt) ){
   476         -	  char *zIndex = (char *)sqlite3_column_text(pStmt, 0);
   477         -	  Index *pIdx;
   478         -          pIdx = sqlite3FindIndex(db, zIndex, sInfo.zDatabase);
   479         -	  if( pIdx ){
   480         -	    char *pSpace;
   481         -	    IndexSample *pSample;
   482         -	    int iCol;
   483         -	    int nAlloc = SQLITE_INDEX_SAMPLES * sizeof(IndexSample);
   484         -	    for(iCol=1; iCol<=SQLITE_INDEX_SAMPLES; iCol++){
   485         -	      int eType = sqlite3_column_type(pStmt, iCol);
   486         -	      if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){
   487         -	        nAlloc += sqlite3_column_bytes(pStmt, iCol);
          504  +    if( !zSql ){
          505  +      return SQLITE_NOMEM;
          506  +    }
          507  +
          508  +    (void)sqlite3SafetyOff(db);
          509  +    rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          510  +    assert( rc!=SQLITE_MISUSE );
          511  +    (void)sqlite3SafetyOn(db);
          512  +    sqlite3DbFree(db, zSql);
          513  +    (void)sqlite3SafetyOff(db);
          514  +
          515  +    if( rc==SQLITE_OK ){
          516  +      while( sqlite3_step(pStmt)==SQLITE_ROW ){
          517  +        char *zIndex = (char *)sqlite3_column_text(pStmt, 0);
          518  +        Index *pIdx = sqlite3FindIndex(db, zIndex, sInfo.zDatabase);
          519  +        if( pIdx ){
          520  +          int iSample = sqlite3_column_int(pStmt, 1);
          521  +          if( iSample<SQLITE_INDEX_SAMPLES && iSample>=0 ){
          522  +            int eType = sqlite3_column_type(pStmt, 2);
          523  +
          524  +            if( pIdx->aSample==0 ){
          525  +              pIdx->aSample = (IndexSample *)sqlite3DbMallocZero(db, 
          526  +                  sizeof(IndexSample)*SQLITE_INDEX_SAMPLES
          527  +              );
          528  +	      if( pIdx->aSample==0 ){
          529  +	       	break;
   488    530   	      }
   489         -	    }
   490         -	    pSample = sqlite3DbMallocRaw(db, nAlloc);
   491         -	    if( !pSample ){
   492         -	      rc = SQLITE_NOMEM;
   493         -	      break;
   494         -	    }
   495         -	    sqlite3DbFree(db, pIdx->aSample);
   496         -	    pIdx->aSample = pSample;
   497         -	    pSpace = (char *)&pSample[SQLITE_INDEX_SAMPLES];
   498         -	    for(iCol=1; iCol<=SQLITE_INDEX_SAMPLES; iCol++){
   499         -	      int eType = sqlite3_column_type(pStmt, iCol);
   500         -	      pSample[iCol-1].eType = eType;
   501         -	      switch( eType ){
   502         -                case SQLITE_BLOB:
   503         -                case SQLITE_TEXT: {
   504         -                  const char *z = (const char *)(
   505         -		      (eType==SQLITE_BLOB) ?
   506         -                      sqlite3_column_blob(pStmt, iCol):
   507         -                      sqlite3_column_text(pStmt, iCol)
   508         -		  );
   509         -                  int n = sqlite3_column_bytes(pStmt, iCol);
   510         -		  if( n>24 ){
   511         -		    n = 24;
   512         -		  }
   513         -		  pSample[iCol-1].nByte = n;
   514         -		  pSample[iCol-1].u.z = pSpace;
   515         -		  memcpy(pSpace, z, n);
   516         -		  pSpace += n;
   517         -		  break;
          531  +            }
          532  +
          533  +            if( pIdx->aSample ){
          534  +              IndexSample *pSample = &pIdx->aSample[iSample];
          535  +              if( pSample->eType==SQLITE_TEXT || pSample->eType==SQLITE_BLOB ){
          536  +                sqlite3DbFree(db, pSample->u.z);
          537  +              }
          538  +	      pSample->eType = eType;
          539  +	      if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
          540  +                pSample->u.r = sqlite3_column_double(pStmt, 2);
          541  +	      }else if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){
          542  +                const char *z = (const char *)(
          543  +                    (eType==SQLITE_BLOB) ?
          544  +                    sqlite3_column_blob(pStmt, 2):
          545  +                    sqlite3_column_text(pStmt, 2)
          546  +                );
          547  +                int n = sqlite3_column_bytes(pStmt, 2);
          548  +                if( n>24 ){
          549  +                  n = 24;
   518    550                   }
   519         -                case SQLITE_INTEGER:
   520         -                case SQLITE_FLOAT:
   521         -		  pSample[iCol-1].u.r = sqlite3_column_double(pStmt, iCol);
   522         -		  break;
   523         -                case SQLITE_NULL:
          551  +                pSample->nByte = n;
          552  +                pSample->u.z = sqlite3DbMallocRaw(db, n);
          553  +                if( pSample->u.z ){
          554  +                  memcpy(pSample->u.z, z, n);
          555  +                }else{
   524    556   		  break;
   525         -	      }
   526         -	    }
   527         -	  }
   528         -	}
   529         -	if( rc==SQLITE_NOMEM ){
   530         -	  sqlite3_finalize(pStmt);
   531         -	}else{
   532         -	  rc = sqlite3_finalize(pStmt);
   533         -	}
          557  +		}
          558  +              }
          559  +            }
          560  +          }
          561  +        }
   534    562         }
   535         -      (void)sqlite3SafetyOn(db);
   536         -      sqlite3DbFree(db, zSql);
   537         -    }else{
   538         -      rc = SQLITE_NOMEM;
          563  +      rc = sqlite3_finalize(pStmt);
   539    564       }
          565  +    (void)sqlite3SafetyOn(db);
   540    566     }
   541    567   
   542         -  if( rc==SQLITE_NOMEM ) db->mallocFailed = 1;
          568  +  if( rc==SQLITE_NOMEM ){
          569  +    db->mallocFailed = 1;
          570  +  }
   543    571     return rc;
   544    572   }
   545    573   
   546    574   
   547    575   #endif /* SQLITE_OMIT_ANALYZE */

Changes to src/build.c.

   339    339   
   340    340   /*
   341    341   ** Reclaim the memory used by an index
   342    342   */
   343    343   static void freeIndex(Index *p){
   344    344     sqlite3 *db = p->pTable->dbMem;
   345    345     /* testcase( db==0 ); */
          346  +  if( p->aSample ){
          347  +    int i;
          348  +    for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
          349  +      int e = p->aSample[i].eType;
          350  +      if( e==SQLITE_BLOB || e==SQLITE_TEXT ){
          351  +        sqlite3DbFree(db, p->aSample[i].u.z);
          352  +      }
          353  +    }
          354  +  }
   346    355     sqlite3DbFree(db, p->aSample);
   347    356     sqlite3DbFree(db, p->zColAff);
   348    357     sqlite3DbFree(db, p);
   349    358   }
   350    359   
   351    360   /*
   352    361   ** Remove the given index from the index hash table, and free

Changes to src/sqliteInt.h.

    74     74   #include <stdint.h>
    75     75   #endif
    76     76   #ifdef HAVE_INTTYPES_H
    77     77   #include <inttypes.h>
    78     78   #endif
    79     79   
    80     80   #define SQLITE_INDEX_SAMPLES 10
    81         -#define SQLITE_INDEX_SAMPLE_COLS "s1,s2,s3,s4,s5,s6,s7,s8,s9,s10"
    82     81   
    83     82   /*
    84     83   ** This macro is used to "hide" some ugliness in casting an int
    85     84   ** value to a ptr value under the MSVC 64-bit compiler.   Casting
    86     85   ** non 64-bit values to ptr types results in a "hard" error with 
    87     86   ** the MSVC 64-bit compiler which this attempts to avoid.  
    88     87   **

Changes to src/vdbe.c.

  1201   1201   ** Subtract the value in register P1 from the value in register P2
  1202   1202   ** and store the result in register P3.
  1203   1203   ** If either input is NULL, the result is NULL.
  1204   1204   */
  1205   1205   /* Opcode: Divide P1 P2 P3 * *
  1206   1206   **
  1207   1207   ** Divide the value in register P1 by the value in register P2
  1208         -** and store the result in register P3.  If the value in register P2
  1209         -** is zero, then the result is NULL.
  1210         -** If either input is NULL, the result is NULL.
         1208  +** and store the result in register P3 (P3=P2/P1). If the value in 
         1209  +** register P1 is zero, then the result is NULL. If either input is 
         1210  +** NULL, the result is NULL.
  1211   1211   */
  1212   1212   /* Opcode: Remainder P1 P2 P3 * *
  1213   1213   **
  1214   1214   ** Compute the remainder after integer division of the value in
  1215   1215   ** register P1 by the value in register P2 and store the result in P3. 
  1216   1216   ** If the value in register P2 is zero the result is NULL.
  1217   1217   ** If either operand is NULL, the result is NULL.
................................................................................
  4967   4967   */
  4968   4968   case OP_Expire: {
  4969   4969     if( !pOp->p1 ){
  4970   4970       sqlite3ExpirePreparedStatements(db);
  4971   4971     }else{
  4972   4972       p->expired = 1;
  4973   4973     }
  4974         -  break;
  4975         -}
  4976         -
  4977         -
  4978         -/* Opcode: Sample P1 P2 P3 * *
  4979         -**
  4980         -** Register P1 contains the total number of rows in the index being 
  4981         -** analyzed. Register P1+1 contains an integer between 0 and 9, the
  4982         -** index of the next sample required. Register P1+2 contains an index
  4983         -** between 1 and *P1, the number of the next sample required. Register
  4984         -** P1+3 contains the current row index.
  4985         -**
  4986         -** If the integer in register P1+3 is the same as the integer in register
  4987         -** P1+1, then the following takes place:
  4988         -**
  4989         -**   (a) the contents of register P1+1 is incremented.
  4990         -**
  4991         -**   (b) the contents of the register identified by parameter P2 is 
  4992         -**       copied to register number (P3 + X), where X is the newly
  4993         -**       incremented value of register P1+1.
  4994         -**
  4995         -**   (c) register P1+2 is set to the index of the next sample required.
  4996         -*/
  4997         -case OP_Sample: {
  4998         -  int p1 = pOp->p1;
  4999         -  i64 iReq = p->aMem[p1+2].u.i;
  5000         -  i64 iRow = p->aMem[p1+3].u.i;
  5001         -
  5002         -  while( iReq==iRow ){
  5003         -    i64 nRow = p->aMem[p1].u.i;
  5004         -    int iSample = ++p->aMem[p1+1].u.i;
  5005         -    Mem *pReg = &p->aMem[pOp->p3 + iSample - 1];
  5006         -
  5007         -    assert( pReg<&p->aMem[p->nMem] );
  5008         -    sqlite3VdbeMemShallowCopy(pReg, &p->aMem[pOp->p2], MEM_Ephem);
  5009         -    Deephemeralize(pReg);
  5010         -    if( iSample==SQLITE_INDEX_SAMPLES ){
  5011         -      iReq = 0;
  5012         -    }else{
  5013         -      iReq = iRow + (nRow-iRow)/(SQLITE_INDEX_SAMPLES - iSample);
  5014         -      p->aMem[p1+2].u.i = iReq;
  5015         -    }
  5016         -  }
  5017   4974     break;
  5018   4975   }
  5019   4976   
  5020   4977   #ifndef SQLITE_OMIT_SHARED_CACHE
  5021   4978   /* Opcode: TableLock P1 P2 P3 P4 *
  5022   4979   **
  5023   4980   ** Obtain a lock on a particular table. This instruction is only used when

Changes to src/where.c.

  1925   1925         sqlite3 *db = pParse->db;
  1926   1926         CollSeq *pColl;
  1927   1927         const u8 *z;
  1928   1928         int n;
  1929   1929         if( eType==SQLITE_BLOB ){
  1930   1930           z = (const u8 *)sqlite3_value_blob(pVal);
  1931   1931           pColl = db->pDfltColl;
  1932         -	assert( pColl->enc==SQLITE_UTF8 );
         1932  +        assert( pColl->enc==SQLITE_UTF8 );
  1933   1933         }else{
  1934         -	pColl = sqlite3FindCollSeq(db, SQLITE_UTF8, *pIdx->azColl, 0);
  1935         -	if( sqlite3CheckCollSeq(pParse, pColl) ){
  1936         -	  return SQLITE_ERROR;
  1937         -	}
         1934  +        pColl = sqlite3FindCollSeq(db, SQLITE_UTF8, *pIdx->azColl, 0);
         1935  +        if( sqlite3CheckCollSeq(pParse, pColl) ){
         1936  +          return SQLITE_ERROR;
         1937  +        }
  1938   1938           z = (const u8 *)sqlite3ValueText(pVal, pColl->enc);
  1939         -	if( !z ){
  1940         -	  return SQLITE_NOMEM;
  1941         -	}
         1939  +        if( !z ){
         1940  +          return SQLITE_NOMEM;
         1941  +        }
  1942   1942           assert( z && pColl && pColl->xCmp );
  1943   1943         }
  1944   1944         n = sqlite3ValueBytes(pVal, pColl->enc);
  1945   1945   
  1946   1946         for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
  1947         -	int r;
         1947  +        int r;
  1948   1948           int eSampletype = aSample[i].eType;
  1949   1949           if( eSampletype==SQLITE_NULL || eSampletype<eType ) continue;
  1950   1950           if( (eSampletype!=eType) ) break;
  1951   1951           if( pColl->enc==SQLITE_UTF8 ){
  1952         -	  r = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z);
         1952  +          r = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z);
  1953   1953           }else{
  1954         -	  int nSample;
  1955         -	  char *zSample = sqlite3Utf8to16(
         1954  +          int nSample;
         1955  +          char *zSample = sqlite3Utf8to16(
  1956   1956                 db, pColl->enc, aSample[i].u.z, aSample[i].nByte, &nSample
  1957   1957             );
  1958         -	  if( !zSample ){
  1959         -	    assert( db->mallocFailed );
  1960         -	    return SQLITE_NOMEM;
  1961         -	  }
  1962         -	  r = pColl->xCmp(pColl->pUser, nSample, zSample, n, z);
  1963         -	  sqlite3DbFree(db, zSample);
         1958  +          if( !zSample ){
         1959  +            assert( db->mallocFailed );
         1960  +            return SQLITE_NOMEM;
         1961  +          }
         1962  +          r = pColl->xCmp(pColl->pUser, nSample, zSample, n, z);
         1963  +          sqlite3DbFree(db, zSample);
  1964   1964           }
  1965         -	if( r>0 ) break;
         1965  +        if( r>0 ) break;
  1966   1966         }
  1967   1967       }
  1968   1968   
  1969   1969       *piRegion = i;
  1970   1970     }
  1971   1971     return SQLITE_OK;
  1972   1972   }
................................................................................
  2242   2242   
  2243   2243       /* Determine the value of nBound. */
  2244   2244       if( nEq<pProbe->nColumn ){
  2245   2245         int j = pProbe->aiColumn[nEq];
  2246   2246         if( findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pIdx) ){
  2247   2247           WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE, pIdx);
  2248   2248           WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT|WO_GE, pIdx);
  2249         -	whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &nBound);
         2249  +        whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &nBound);
  2250   2250           if( pTop ){
  2251   2251             wsFlags |= WHERE_TOP_LIMIT;
  2252   2252             used |= pTop->prereqRight;
  2253   2253           }
  2254   2254           if( pBtm ){
  2255   2255             wsFlags |= WHERE_BTM_LIMIT;
  2256   2256             used |= pBtm->prereqRight;

Added test/analyze2.test.

            1  +# 2009 August 06
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# $Id: analyze.test,v 1.9 2008/08/11 18:44:58 drh Exp $
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +
           17  +sqlite3_db_config_lookaside db 0 0 0
           18  +
           19  +do_test analyze2-0.1 {
           20  +  execsql { CREATE TABLE t1(x PRIMARY KEY) }
           21  +  for {set i 0} {$i < 1000} {incr i} {
           22  +    execsql { INSERT INTO t1 VALUES($i) }
           23  +  }
           24  +  execsql { 
           25  +    ANALYZE;
           26  +    SELECT * FROM sqlite_stat2;
           27  +  }
           28  +} [list t1 sqlite_autoindex_t1_1 0 0   \
           29  +        t1 sqlite_autoindex_t1_1 1 111 \
           30  +        t1 sqlite_autoindex_t1_1 2 222 \
           31  +        t1 sqlite_autoindex_t1_1 3 333 \
           32  +        t1 sqlite_autoindex_t1_1 4 444 \
           33  +        t1 sqlite_autoindex_t1_1 5 555 \
           34  +        t1 sqlite_autoindex_t1_1 6 666 \
           35  +        t1 sqlite_autoindex_t1_1 7 777 \
           36  +        t1 sqlite_autoindex_t1_1 8 888 \
           37  +        t1 sqlite_autoindex_t1_1 9 999 \
           38  +]
           39  +
           40  +do_test analyze2-0.2 {
           41  +  execsql {
           42  +    DELETE FROM t1 WHERe x>9;
           43  +    ANALYZE;
           44  +    SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2;
           45  +  }
           46  +} {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}}
           47  +
           48  +do_test analyze2-0.3 {
           49  +  execsql {
           50  +    DELETE FROM t1 WHERE x>5;
           51  +    ANALYZE;
           52  +    SELECT * FROM sqlite_stat2;
           53  +  }
           54  +} {}
           55  +
           56  +do_test analyze2-0.4 {
           57  +  execsql {
           58  +    DELETE FROM t1;
           59  +    ANALYZE;
           60  +    SELECT * FROM sqlite_stat2;
           61  +  }
           62  +} {}
           63  +
           64  +proc eqp sql {
           65  +  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"]
           66  +}
           67  +
           68  +do_test analyze2-1.1 {
           69  +  execsql { 
           70  +    DROP TABLE t1;
           71  +    CREATE TABLE t1(x, y);
           72  +    CREATE INDEX t1_x ON t1(x);
           73  +    CREATE INDEX t1_y ON t1(y);
           74  +  }
           75  +
           76  +  for {set i 0} {$i < 1000} {incr i} {
           77  +    execsql { INSERT INTO t1 VALUES($i, $i) }
           78  +  }
           79  +  execsql ANALYZE
           80  +} {}
           81  +do_test analyze2-1.2 {
           82  +  execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE x>500 AND y>700 }
           83  +} {0 0 {TABLE t1 WITH INDEX t1_y}}
           84  +
           85  +do_test analyze2-1.3 {
           86  +  execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE x>700 AND y>500 }
           87  +} {0 0 {TABLE t1 WITH INDEX t1_x}}
           88  +
           89  +do_test analyze2-1.3 {
           90  +  execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE y>700 AND x>500 }
           91  +} {0 0 {TABLE t1 WITH INDEX t1_y}}
           92  +do_test analyze2-1.4 {
           93  +  execsql { EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE y>500 AND x>700 }
           94  +} {0 0 {TABLE t1 WITH INDEX t1_x}}
           95  +
           96  +do_test analyze2-2.1 {
           97  +  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700"
           98  +} {0 0 {TABLE t1 WITH INDEX t1_x}}
           99  +do_test analyze2-2.2 {
          100  +  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700"
          101  +} {0 0 {TABLE t1 WITH INDEX t1_y}}
          102  +do_test analyze2-2.3 {
          103  +  eqp "SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300"
          104  +} {0 0 {TABLE t1 WITH INDEX t1_x}}
          105  +do_test analyze2-2.4 {
          106  +  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300"
          107  +} {0 0 {TABLE t1 WITH INDEX t1_y}}
          108  +
          109  +do_test analyze2-3.1 {
          110  +  eqp "SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300"
          111  +} {0 0 {TABLE t1 WITH INDEX t1_x}}
          112  +do_test analyze2-3.2 {
          113  +  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100"
          114  +} {0 0 {TABLE t1 WITH INDEX t1_y}}
          115  +
          116  +do_test analyze2-4.1 {
          117  +  set alphabet [list a b c d e f g h i j]
          118  +  for {set i 0} {$i < 1000} {incr i} {
          119  +    set str    [lindex $alphabet [expr ($i/100)%10]] 
          120  +    append str [lindex $alphabet [expr ($i/ 10)%10]]
          121  +    append str [lindex $alphabet [expr ($i/  1)%10]]
          122  +    execsql { INSERT INTO t1 VALUES($str, $str) }
          123  +  }
          124  +  execsql ANALYZE
          125  +  execsql { 
          126  +    SELECT tbl,idx,group_concat(sample,' ') 
          127  +    FROM sqlite_stat2 
          128  +    WHERE idx = 't1_x' 
          129  +    GROUP BY tbl,idx
          130  +  }
          131  +} {t1 t1_x {0 222 444 666 888 bba ddc ffe hhg jjj}}
          132  +do_test analyze2-4.2 {
          133  +  execsql { 
          134  +    SELECT tbl,idx,group_concat(sample,' ') 
          135  +    FROM sqlite_stat2 
          136  +    WHERE idx = 't1_y' 
          137  +    GROUP BY tbl,idx
          138  +  }
          139  +} {t1 t1_y {0 222 444 666 888 bba ddc ffe hhg jjj}}
          140  +
          141  +do_test analyze2-4.3 {
          142  +  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'"
          143  +} {0 0 {TABLE t1 WITH INDEX t1_y}}
          144  +do_test analyze2-4.4 {
          145  +  eqp "SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'"
          146  +} {0 0 {TABLE t1 WITH INDEX t1_x}}
          147  +do_test analyze2-4.5 {
          148  +  eqp "SELECT * FROM t1 WHERE x<'a' AND y>'h'"
          149  +} {0 0 {TABLE t1 WITH INDEX t1_y}}
          150  +do_test analyze2-4.6 {
          151  +  eqp "SELECT * FROM t1 WHERE x<444 AND y>'h'"
          152  +} {0 0 {TABLE t1 WITH INDEX t1_y}}
          153  +do_test analyze2-4.7 {
          154  +  eqp "SELECT * FROM t1 WHERE x<221 AND y>'h'"
          155  +} {0 0 {TABLE t1 WITH INDEX t1_x}}
          156  +
          157  +do_test analyze2-5.1 {
          158  +  execsql { CREATE TABLE t3(a COLLATE nocase, b) }
          159  +  execsql { CREATE INDEX t3a ON t3(a) }
          160  +  execsql { CREATE INDEX t3b ON t3(b) }
          161  +  set alphabet [list A b C d E f G h I j]
          162  +  for {set i 0} {$i < 1000} {incr i} {
          163  +    set str    [lindex $alphabet [expr ($i/100)%10]] 
          164  +    append str [lindex $alphabet [expr ($i/ 10)%10]]
          165  +    append str [lindex $alphabet [expr ($i/  1)%10]]
          166  +    execsql { INSERT INTO t3 VALUES($str, $str) }
          167  +  }
          168  +  execsql ANALYZE
          169  +} {}
          170  +do_test analyze2-5.2 {
          171  +  execsql { 
          172  +    SELECT tbl,idx,group_concat(sample,' ') 
          173  +    FROM sqlite_stat2 
          174  +    WHERE idx = 't3a' 
          175  +    GROUP BY tbl,idx
          176  +  }
          177  +} {t3 t3a {AAA bbb CCC ddd EEE fff GGG hhh III jjj}}
          178  +do_test analyze2-5.3 {
          179  +  execsql { 
          180  +    SELECT tbl,idx,group_concat(sample,' ') 
          181  +    FROM sqlite_stat2 
          182  +    WHERE idx = 't3b' 
          183  +    GROUP BY tbl,idx
          184  +  }
          185  +} {t3 t3b {AAA CCC EEE GGG III bbb ddd fff hhh jjj}}
          186  +
          187  +do_test analyze2-5.4 {
          188  +  eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'"
          189  +} {0 0 {TABLE t3 WITH INDEX t3b}}
          190  +do_test analyze2-5.5 {
          191  +  eqp "SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'"
          192  +} {0 0 {TABLE t3 WITH INDEX t3a}}
          193  +
          194  +proc test_collate {enc lhs rhs} {
          195  +  # puts $enc
          196  +  return [string compare $lhs $rhs]
          197  +}
          198  +
          199  +do_test analyze2-6.1 {
          200  +  add_test_collate db 0 0 1
          201  +  execsql { CREATE TABLE t4(x COLLATE test_collate) }
          202  +  execsql { CREATE INDEX t4x ON t4(x) }
          203  +  set alphabet [list a b c d e f g h i j]
          204  +  for {set i 0} {$i < 1000} {incr i} {
          205  +    set str    [lindex $alphabet [expr ($i/100)%10]] 
          206  +    append str [lindex $alphabet [expr ($i/ 10)%10]]
          207  +    append str [lindex $alphabet [expr ($i/  1)%10]]
          208  +    execsql { INSERT INTO t4 VALUES($str) }
          209  +  }
          210  +  execsql ANALYZE
          211  +} {}
          212  +do_test analyze2-6.2 {
          213  +  execsql { 
          214  +    SELECT tbl,idx,group_concat(sample,' ') 
          215  +    FROM sqlite_stat2 
          216  +    WHERE tbl = 't4' 
          217  +    GROUP BY tbl,idx
          218  +  }
          219  +} {t4 t4x {aaa bbb ccc ddd eee fff ggg hhh iii jjj}}
          220  +do_test analyze2-6.3 {
          221  +  eqp "SELECT * FROM t4 WHERE x>'ccc'"
          222  +} {0 0 {TABLE t4 WITH INDEX t4x}}
          223  +do_test analyze2-6.4 {
          224  +  eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'"
          225  +} {0 1 {TABLE t4 AS t42 WITH INDEX t4x} 1 0 {TABLE t4 AS t41 WITH INDEX t4x}}
          226  +do_test analyze2-6.5 {
          227  +  eqp "SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'"
          228  +} {0 0 {TABLE t4 AS t41 WITH INDEX t4x} 1 1 {TABLE t4 AS t42 WITH INDEX t4x}}
          229  +
          230  +ifcapable memdebug {
          231  +  execsql { DELETE FROM t4 }
          232  +  db close
          233  +  source $testdir/malloc_common.tcl
          234  +  file copy -force test.db bak.db
          235  +
          236  +  do_malloc_test analyze2-oom -tclprep {
          237  +    db close
          238  +    file copy -force bak.db test.db
          239  +    sqlite3 db test.db
          240  +    sqlite3_db_config_lookaside db 0 0 0
          241  +    add_test_collate db 0 0 1
          242  +  } -sqlbody {
          243  +    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'
          244  +  }
          245  +}
          246  +
          247  +finish_test