Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -25,17 +25,17 @@ ** Additional tables might be added in future releases of SQLite. ** The sqlite_stat2 table is not created or used unless the SQLite version ** is between 3.6.18 and 3.7.8, inclusive, and unless SQLite is compiled ** with SQLITE_ENABLE_STAT2. The sqlite_stat2 table is deprecated. ** The sqlite_stat2 table is superseded by sqlite_stat3, which is only -** created and used by SQLite versions 3.7.9 and later and with +** created and used by SQLite versions 3.7.9 through 3.29.0 when ** SQLITE_ENABLE_STAT3 defined. The functionality of sqlite_stat3 -** is a superset of sqlite_stat2. The sqlite_stat4 is an enhanced -** version of sqlite_stat3 and is only available when compiled with -** SQLITE_ENABLE_STAT4 and in SQLite versions 3.8.1 and later. It is -** not possible to enable both STAT3 and STAT4 at the same time. If they -** are both enabled, then STAT4 takes precedence. +** is a superset of sqlite_stat2 and is also now deprecated. The +** sqlite_stat4 is an enhanced version of sqlite_stat3 and is only +** available when compiled with SQLITE_ENABLE_STAT4 and in SQLite +** versions 3.8.1 and later. STAT4 is the only variant that is still +** supported. ** ** For most applications, sqlite_stat1 provides all the statistics required ** for the query planner to make good choices. ** ** Format of sqlite_stat1: @@ -142,21 +142,15 @@ #ifndef SQLITE_OMIT_ANALYZE #include "sqliteInt.h" #if defined(SQLITE_ENABLE_STAT4) # define IsStat4 1 -# define IsStat3 0 -#elif defined(SQLITE_ENABLE_STAT3) -# define IsStat4 0 -# define IsStat3 1 #else # define IsStat4 0 -# define IsStat3 0 # undef SQLITE_STAT4_SAMPLES # define SQLITE_STAT4_SAMPLES 1 #endif -#define IsStat34 (IsStat3+IsStat4) /* 1 for STAT3 or STAT4. 0 otherwise */ /* ** This routine generates code that opens the sqlite_statN tables. ** The sqlite_stat1 table is always relevant. sqlite_stat2 is now ** obsolete. sqlite_stat3 and sqlite_stat4 are only opened when @@ -181,18 +175,14 @@ const char *zCols; } aTable[] = { { "sqlite_stat1", "tbl,idx,stat" }, #if defined(SQLITE_ENABLE_STAT4) { "sqlite_stat4", "tbl,idx,neq,nlt,ndlt,sample" }, - { "sqlite_stat3", 0 }, -#elif defined(SQLITE_ENABLE_STAT3) - { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" }, - { "sqlite_stat4", 0 }, #else - { "sqlite_stat3", 0 }, { "sqlite_stat4", 0 }, #endif + { "sqlite_stat3", 0 }, }; int i; sqlite3 *db = pParse->db; Db *pDb; Vdbe *v = sqlite3GetVdbe(pParse); @@ -269,11 +259,11 @@ typedef struct Stat4Accum Stat4Accum; typedef struct Stat4Sample Stat4Sample; struct Stat4Sample { tRowcnt *anEq; /* sqlite_stat4.nEq */ tRowcnt *anDLt; /* sqlite_stat4.nDLt */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 tRowcnt *anLt; /* sqlite_stat4.nLt */ union { i64 iRowid; /* Rowid in main table of the key */ u8 *aRowid; /* Key for WITHOUT ROWID tables */ } u; @@ -300,11 +290,11 @@ sqlite3 *db; /* Database connection, for malloc() */ }; /* Reclaim memory used by a Stat4Sample */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 static void sampleClear(sqlite3 *db, Stat4Sample *p){ assert( db!=0 ); if( p->nRowid ){ sqlite3DbFree(db, p->u.aRowid); p->nRowid = 0; @@ -312,11 +302,11 @@ } #endif /* Initialize the BLOB value of a ROWID */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 static void sampleSetRowid(sqlite3 *db, Stat4Sample *p, int n, const u8 *pData){ assert( db!=0 ); if( p->nRowid ) sqlite3DbFree(db, p->u.aRowid); p->u.aRowid = sqlite3DbMallocRawNN(db, n); if( p->u.aRowid ){ @@ -328,11 +318,11 @@ } #endif /* Initialize the INTEGER value of a ROWID. */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 static void sampleSetRowidInt64(sqlite3 *db, Stat4Sample *p, i64 iRowid){ assert( db!=0 ); if( p->nRowid ) sqlite3DbFree(db, p->u.aRowid); p->nRowid = 0; p->u.iRowid = iRowid; @@ -341,11 +331,11 @@ /* ** Copy the contents of object (*pFrom) into (*pTo). */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 static void sampleCopy(Stat4Accum *p, Stat4Sample *pTo, Stat4Sample *pFrom){ pTo->isPSample = pFrom->isPSample; pTo->iCol = pFrom->iCol; pTo->iHash = pFrom->iHash; memcpy(pTo->anEq, pFrom->anEq, sizeof(tRowcnt)*p->nCol); @@ -362,11 +352,11 @@ /* ** Reclaim all memory of a Stat4Accum structure. */ static void stat4Destructor(void *pOld){ Stat4Accum *p = (Stat4Accum*)pOld; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 int i; for(i=0; inCol; i++) sampleClear(p->db, p->aBest+i); for(i=0; imxSample; i++) sampleClear(p->db, p->a+i); sampleClear(p->db, &p->current); #endif @@ -382,11 +372,11 @@ ** ** Note 1: In the special case of the covering index that implements a ** WITHOUT ROWID table, N is the number of PRIMARY KEY columns, not the ** total number of columns in the table. ** -** Note 2: C is only used for STAT3 and STAT4. +** Note 2: C is only used for STAT4. ** ** For indexes on ordinary rowid tables, N==K+1. But for indexes on ** WITHOUT ROWID tables, N=K+P where P is the number of columns in the ** PRIMARY KEY of the table. The covering index that implements the ** original WITHOUT ROWID table as N==K as a special case. @@ -405,11 +395,11 @@ int nCol; /* Number of columns in index being sampled */ int nKeyCol; /* Number of key columns */ int nColUp; /* nCol rounded up for alignment */ int n; /* Bytes of space to allocate */ sqlite3 *db; /* Database connection */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 int mxSample = SQLITE_STAT4_SAMPLES; #endif /* Decode the three function arguments */ UNUSED_PARAMETER(argc); @@ -422,11 +412,11 @@ /* Allocate the space required for the Stat4Accum object */ n = sizeof(*p) + sizeof(tRowcnt)*nColUp /* Stat4Accum.anEq */ + sizeof(tRowcnt)*nColUp /* Stat4Accum.anDLt */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 + sizeof(tRowcnt)*nColUp /* Stat4Accum.anLt */ + sizeof(Stat4Sample)*(nCol+mxSample) /* Stat4Accum.aBest[], a[] */ + sizeof(tRowcnt)*3*nColUp*(nCol+mxSample) #endif ; @@ -442,11 +432,11 @@ p->nCol = nCol; p->nKeyCol = nKeyCol; p->current.anDLt = (tRowcnt*)&p[1]; p->current.anEq = &p->current.anDLt[nColUp]; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 { u8 *pSpace; /* Allocated space not yet assigned */ int i; /* Used to iterate through p->aSample[] */ p->iGet = -1; @@ -477,11 +467,11 @@ ** (given by the 3rd parameter) is never used and can be any positive ** value. */ sqlite3_result_blob(context, p, sizeof(*p), stat4Destructor); } static const FuncDef statInitFuncdef = { - 2+IsStat34, /* nArg */ + 2+IsStat4, /* nArg */ SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ statInit, /* xSFunc */ 0, /* xFinalize */ @@ -517,11 +507,11 @@ if( pNew->iHash>pOld->iHash ) return 1; return 0; } #endif -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 /* ** Return true if pNew is to be preferred over pOld. ** ** This function assumes that for each argument sample, the contents of ** the anEq[] array from pSample->anEq[pSample->iCol] onwards are valid. @@ -536,19 +526,15 @@ assert( pOld->isPSample==0 && pNew->isPSample==0 ); assert( IsStat4 || (pNew->iCol==0 && pOld->iCol==0) ); if( (nEqNew>nEqOld) ) return 1; -#ifdef SQLITE_ENABLE_STAT4 if( nEqNew==nEqOld ){ if( pNew->iColiCol ) return 1; return (pNew->iCol==pOld->iCol && sampleIsBetterPost(pAccum, pNew, pOld)); } return 0; -#else - return (nEqNew==nEqOld && pNew->iHash>pOld->iHash); -#endif } /* ** Copy the contents of sample *pNew into the p->a[] array. If necessary, ** remove the least desirable sample from p->a[] to make room. @@ -557,11 +543,10 @@ Stat4Sample *pSample = 0; int i; assert( IsStat4 || nEqZero==0 ); -#ifdef SQLITE_ENABLE_STAT4 /* Stat4Accum.nMaxEqZero is set to the maximum number of leading 0 ** values in the anEq[] array of any sample in Stat4Accum.a[]. In ** other words, if nMaxEqZero is n, then it is guaranteed that there ** are no samples with Stat4Sample.anEq[m]==0 for (m>=n). */ if( nEqZero>p->nMaxEqZero ){ @@ -591,11 +576,10 @@ pUpgrade->iCol = pNew->iCol; pUpgrade->anEq[pUpgrade->iCol] = pNew->anEq[pUpgrade->iCol]; goto find_new_min; } } -#endif /* If necessary, remove sample iMin to make room for the new sample. */ if( p->nSample>=p->mxSample ){ Stat4Sample *pMin = &p->a[p->iMin]; tRowcnt *anEq = pMin->anEq; @@ -612,26 +596,22 @@ } /* The "rows less-than" for the rowid column must be greater than that ** for the last sample in the p->a[] array. Otherwise, the samples would ** be out of order. */ -#ifdef SQLITE_ENABLE_STAT4 assert( p->nSample==0 || pNew->anLt[p->nCol-1] > p->a[p->nSample-1].anLt[p->nCol-1] ); -#endif /* Insert the new sample */ pSample = &p->a[p->nSample]; sampleCopy(p, pSample, pNew); p->nSample++; /* Zero the first nEqZero entries in the anEq[] array. */ memset(pSample->anEq, 0, sizeof(tRowcnt)*nEqZero); -#ifdef SQLITE_ENABLE_STAT4 - find_new_min: -#endif +find_new_min: if( p->nSample>=p->mxSample ){ int iMin = -1; for(i=0; imxSample; i++){ if( p->a[i].isPSample ) continue; if( iMin<0 || sampleIsBetter(p, &p->a[iMin], &p->a[i]) ){ @@ -640,11 +620,11 @@ } assert( iMin>=0 ); p->iMin = iMin; } } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ /* ** Field iChng of the index being scanned has changed. So at this point ** p->current contains a sample that reflects the previous row of the ** index. The value of anEq[iChng] and subsequent anEq[] elements are @@ -681,32 +661,11 @@ } p->nMaxEqZero = iChng; } #endif -#if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4) - if( iChng==0 ){ - tRowcnt nLt = p->current.anLt[0]; - tRowcnt nEq = p->current.anEq[0]; - - /* Check if this is to be a periodic sample. If so, add it. */ - if( (nLt/p->nPSample)!=(nLt+nEq)/p->nPSample ){ - p->current.isPSample = 1; - sampleInsert(p, &p->current, 0); - p->current.isPSample = 0; - }else - - /* Or if it is a non-periodic sample. Add it in this case too. */ - if( p->nSamplemxSample - || sampleIsBetter(p, &p->current, &p->a[p->iMin]) - ){ - sampleInsert(p, &p->current, 0); - } - } -#endif - -#ifndef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifndef SQLITE_ENABLE_STAT4 UNUSED_PARAMETER( p ); UNUSED_PARAMETER( iChng ); #endif } @@ -722,11 +681,11 @@ ** This SQL function always returns NULL. It's purpose it to accumulate ** statistical data and/or samples in the Stat4Accum object about the ** index being analyzed. The stat_get() SQL function will later be used to ** extract relevant information for constructing the sqlite_statN tables. ** -** The R parameter is only used for STAT3 and STAT4 +** The R parameter is only used for STAT4 */ static void statPush( sqlite3_context *context, int argc, sqlite3_value **argv @@ -754,18 +713,18 @@ for(i=0; icurrent.anEq[i]++; } for(i=iChng; inCol; i++){ p->current.anDLt[i]++; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 p->current.anLt[i] += p->current.anEq[i]; #endif p->current.anEq[i] = 1; } } p->nRow++; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( sqlite3_value_type(argv[2])==SQLITE_INTEGER ){ sampleSetRowidInt64(p->db, &p->current, sqlite3_value_int64(argv[2])); }else{ sampleSetRowid(p->db, &p->current, sqlite3_value_bytes(argv[2]), sqlite3_value_blob(argv[2])); @@ -794,11 +753,11 @@ } } #endif } static const FuncDef statPushFuncdef = { - 2+IsStat34, /* nArg */ + 2+IsStat4, /* nArg */ SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ statPush, /* xSFunc */ 0, /* xFinalize */ @@ -825,11 +784,11 @@ ** inserted as part of a manually constructed bytecode program. (See ** the callStatGet() routine below.) It is guaranteed that the P ** parameter will always be a poiner to a Stat4Accum object, never a ** NULL. ** -** If neither STAT3 nor STAT4 are enabled, then J is always +** If STAT4 is not enabled, then J is always ** STAT_GET_STAT1 and is hence omitted and this routine becomes ** a one-parameter function, stat_get(P), that always returns the ** stat1 table entry information. */ static void statGet( @@ -836,12 +795,12 @@ sqlite3_context *context, int argc, sqlite3_value **argv ){ Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]); -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 - /* STAT3 and STAT4 have a parameter on this routine. */ +#ifdef SQLITE_ENABLE_STAT4 + /* STAT4 has a parameter on this routine. */ int eCall = sqlite3_value_int(argv[1]); assert( argc==2 ); assert( eCall==STAT_GET_STAT1 || eCall==STAT_GET_NEQ || eCall==STAT_GET_ROWID || eCall==STAT_GET_NLT || eCall==STAT_GET_NDLT @@ -892,11 +851,11 @@ } assert( z[0]=='\0' && z>zRet ); sqlite3_result_text(context, zRet, -1, sqlite3_free); } -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 else if( eCall==STAT_GET_ROWID ){ if( p->iGet<0 ){ samplePushPrevious(p, 0); p->iGet = 0; } @@ -921,13 +880,11 @@ p->iGet++; break; } } - if( IsStat3 ){ - sqlite3_result_int64(context, (i64)aCnt[0]); - }else{ + { char *zRet = sqlite3MallocZero(p->nCol * 25); if( zRet==0 ){ sqlite3_result_error_nomem(context); }else{ int i; @@ -940,17 +897,17 @@ z[-1] = '\0'; sqlite3_result_text(context, zRet, -1, sqlite3_free); } } } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ #ifndef SQLITE_DEBUG UNUSED_PARAMETER( argc ); #endif } static const FuncDef statGetFuncdef = { - 1+IsStat34, /* nArg */ + 1+IsStat4, /* nArg */ SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ statGet, /* xSFunc */ 0, /* xFinalize */ @@ -959,20 +916,20 @@ {0} }; static void callStatGet(Vdbe *v, int regStat4, int iParam, int regOut){ assert( regOut!=regStat4 && regOut!=regStat4+1 ); -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 sqlite3VdbeAddOp2(v, OP_Integer, iParam, regStat4+1); #elif SQLITE_DEBUG assert( iParam==STAT_GET_STAT1 ); #else UNUSED_PARAMETER( iParam ); #endif sqlite3VdbeAddOp4(v, OP_Function0, 0, regStat4, regOut, (char*)&statGetFuncdef, P4_FUNCDEF); - sqlite3VdbeChangeP5(v, 1 + IsStat34); + sqlite3VdbeChangeP5(v, 1 + IsStat4); } /* ** Generate code to do an analysis of all indices associated with ** a single table. @@ -995,11 +952,11 @@ 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 +#ifdef SQLITE_ENABLE_STAT4 int regRowid = iMem++; /* Rowid argument passed to stat_push() */ #endif int regTemp = iMem++; /* Temporary use register */ int regTabname = iMem++; /* Register containing table name */ int regIdxname = iMem++; /* Register containing index name */ @@ -1129,20 +1086,20 @@ ** (or for a WITHOUT ROWID table, the number of PK columns), ** (2) the number of columns in the key without the rowid/pk ** (3) the number of rows in the index, ** ** - ** The third argument is only used for STAT3 and STAT4 + ** The third argument is only used for STAT4 */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat4+3); #endif sqlite3VdbeAddOp2(v, OP_Integer, nCol, regStat4+1); sqlite3VdbeAddOp2(v, OP_Integer, pIdx->nKeyCol, regStat4+2); sqlite3VdbeAddOp4(v, OP_Function0, 0, regStat4+1, regStat4, (char*)&statInitFuncdef, P4_FUNCDEF); - sqlite3VdbeChangeP5(v, 2+IsStat34); + sqlite3VdbeChangeP5(v, 2+IsStat4); /* Implementation of the following: ** ** Rewind csr ** if eof(csr) goto end_of_scan; @@ -1209,16 +1166,16 @@ sqlite3DbFree(db, aGotoChng); } /* ** chng_addr_N: - ** regRowid = idx(rowid) // STAT34 only - ** stat_push(P, regChng, regRowid) // 3rd parameter STAT34 only + ** regRowid = idx(rowid) // STAT4 only + ** stat_push(P, regChng, regRowid) // 3rd parameter STAT4 only ** Next csr ** if !eof(csr) goto next_row; */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 assert( regRowid==(regStat4+2) ); if( HasRowid(pTab) ){ sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, regRowid); }else{ Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable); @@ -1235,11 +1192,11 @@ } #endif assert( regChng==(regStat4+1) ); sqlite3VdbeAddOp4(v, OP_Function0, 1, regStat4, regTemp, (char*)&statPushFuncdef, P4_FUNCDEF); - sqlite3VdbeChangeP5(v, 2+IsStat34); + sqlite3VdbeChangeP5(v, 2+IsStat4); 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 ); @@ -1249,12 +1206,12 @@ #ifdef SQLITE_ENABLE_PREUPDATE_HOOK sqlite3VdbeChangeP4(v, -1, (char*)pStat1, P4_TABLE); #endif sqlite3VdbeChangeP5(v, OPFLAG_APPEND); - /* Add the entries to the stat3 or stat4 table. */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 + /* Add the entries to the stat4 table. */ +#ifdef SQLITE_ENABLE_STAT4 { int regEq = regStat1; int regLt = regStat1+1; int regDLt = regStat1+2; int regSample = regStat1+3; @@ -1273,25 +1230,21 @@ callStatGet(v, regStat4, STAT_GET_NEQ, regEq); callStatGet(v, regStat4, STAT_GET_NLT, regLt); callStatGet(v, regStat4, STAT_GET_NDLT, regDLt); sqlite3VdbeAddOp4Int(v, seekOp, iTabCur, addrNext, regSampleRowid, 0); VdbeCoverage(v); -#ifdef SQLITE_ENABLE_STAT3 - sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iTabCur, 0, regSample); -#else for(i=0; i='0' && c<='9' ){ v = v*10 + c - '0'; z++; } -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( aOut ) aOut[i] = v; if( aLog ) aLog[i] = sqlite3LogEst(v); #else assert( aOut==0 ); UNUSED_PARAMETER(aOut); @@ -1484,11 +1437,11 @@ assert( aLog!=0 ); aLog[i] = sqlite3LogEst(v); #endif if( *z==' ' ) z++; } -#ifndef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifndef SQLITE_ENABLE_STAT4 assert( pIndex!=0 ); { #else if( pIndex ){ #endif pIndex->bUnordered = 0; @@ -1549,11 +1502,11 @@ z = argv[2]; if( pIndex ){ tRowcnt *aiRowEst = 0; int nCol = pIndex->nKeyCol+1; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 /* Index.aiRowEst may already be set here if there are duplicate ** sqlite_stat1 entries for this index. In that case just clobber ** the old data with the new instead of allocating a new array. */ if( pIndex->aiRowEst==0 ){ pIndex->aiRowEst = (tRowcnt*)sqlite3MallocZero(sizeof(tRowcnt) * nCol); @@ -1585,11 +1538,11 @@ /* ** If the Index.aSample variable is not NULL, delete the aSample[] array ** and its contents. */ void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( pIdx->aSample ){ int j; for(j=0; jnSample; j++){ IndexSample *p = &pIdx->aSample[j]; sqlite3DbFree(db, p->p); @@ -1601,14 +1554,14 @@ pIdx->aSample = 0; } #else UNUSED_PARAMETER(db); UNUSED_PARAMETER(pIdx); -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ } -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 /* ** Populate the pIdx->aAvgEq[] array based on the samples currently ** stored in pIdx->aSample[]. */ static void initAvgEq(Index *pIdx){ @@ -1682,25 +1635,23 @@ } return pIdx; } /* -** Load the content from either the sqlite_stat4 or sqlite_stat3 table +** Load the content from either the sqlite_stat4 ** into the relevant Index.aSample[] arrays. ** ** Arguments zSql1 and zSql2 must point to SQL statements that return -** data equivalent to the following (statements are different for stat3, -** see the caller of this function for details): +** data equivalent to the following: ** ** zSql1: SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx ** zSql2: SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4 ** ** where %Q is replaced with the database name before the SQL is executed. */ static int loadStatTbl( sqlite3 *db, /* Database handle */ - int bStat3, /* Assume single column records only */ const char *zSql1, /* SQL statement 1 (see above) */ const char *zSql2, /* SQL statement 2 (see above) */ const char *zDb /* Database name (e.g. "main") */ ){ int rc; /* Result codes from subroutines */ @@ -1730,21 +1681,17 @@ zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; nSample = sqlite3_column_int(pStmt, 1); pIdx = findIndexOrPrimaryKey(db, zIndex, zDb); - assert( pIdx==0 || bStat3 || pIdx->nSample==0 ); - /* Index.nSample is non-zero at this point if data has already been - ** loaded from the stat4 table. In this case ignore stat3 data. */ - if( pIdx==0 || pIdx->nSample ) continue; - if( bStat3==0 ){ - assert( !HasRowid(pIdx->pTable) || pIdx->nColumn==pIdx->nKeyCol+1 ); - if( !HasRowid(pIdx->pTable) && IsPrimaryKeyIndex(pIdx) ){ - nIdxCol = pIdx->nKeyCol; - }else{ - nIdxCol = pIdx->nColumn; - } + assert( pIdx==0 || pIdx->nSample==0 ); + if( pIdx==0 ) continue; + assert( !HasRowid(pIdx->pTable) || pIdx->nColumn==pIdx->nKeyCol+1 ); + if( !HasRowid(pIdx->pTable) && IsPrimaryKeyIndex(pIdx) ){ + nIdxCol = pIdx->nKeyCol; + }else{ + nIdxCol = pIdx->nColumn; } pIdx->nSampleCol = nIdxCol; nByte = sizeof(IndexSample) * nSample; nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample; nByte += nIdxCol * sizeof(tRowcnt); /* Space for Index.aAvgEq[] */ @@ -1782,13 +1729,12 @@ zIndex = (char *)sqlite3_column_text(pStmt, 0); if( zIndex==0 ) continue; pIdx = findIndexOrPrimaryKey(db, zIndex, zDb); if( pIdx==0 ) continue; /* This next condition is true if data has already been loaded from - ** the sqlite_stat4 table. In this case ignore stat3 data. */ + ** the sqlite_stat4 table. */ nCol = pIdx->nSampleCol; - if( bStat3 && nCol>1 ) continue; if( pIdx!=pPrevIdx ){ initAvgEq(pPrevIdx); pPrevIdx = pIdx; } pSample = &pIdx->aSample[pIdx->nSample]; @@ -1817,49 +1763,40 @@ if( rc==SQLITE_OK ) initAvgEq(pPrevIdx); return rc; } /* -** Load content from the sqlite_stat4 and sqlite_stat3 tables into +** Load content from the sqlite_stat4 table into ** the Index.aSample[] arrays of all indices. */ static int loadStat4(sqlite3 *db, const char *zDb){ int rc = SQLITE_OK; /* Result codes from subroutines */ assert( db->lookaside.bDisable ); if( sqlite3FindTable(db, "sqlite_stat4", zDb) ){ - rc = loadStatTbl(db, 0, + rc = loadStatTbl(db, "SELECT idx,count(*) FROM %Q.sqlite_stat4 GROUP BY idx", "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat4", zDb ); } - - if( rc==SQLITE_OK && sqlite3FindTable(db, "sqlite_stat3", zDb) ){ - rc = loadStatTbl(db, 1, - "SELECT idx,count(*) FROM %Q.sqlite_stat3 GROUP BY idx", - "SELECT idx,neq,nlt,ndlt,sqlite_record(sample) FROM %Q.sqlite_stat3", - zDb - ); - } - return rc; } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ /* -** Load the content of the sqlite_stat1 and sqlite_stat3/4 tables. The +** Load the content of the sqlite_stat1 and sqlite_stat4 tables. The ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[] -** arrays. The contents of sqlite_stat3/4 are used to populate the +** arrays. The contents of sqlite_stat4 are used to populate the ** Index.aSample[] arrays. ** ** If the sqlite_stat1 table is not present in the database, SQLITE_ERROR -** is returned. In this case, even if SQLITE_ENABLE_STAT3/4 was defined -** during compilation and the sqlite_stat3/4 table is present, no data is +** is returned. In this case, even if SQLITE_ENABLE_STAT4 was defined +** during compilation and the sqlite_stat4 table is present, no data is ** read from it. ** -** If SQLITE_ENABLE_STAT3/4 was defined during compilation and the +** If SQLITE_ENABLE_STAT4 was defined during compilation and the ** sqlite_stat4 table is not present in the database, SQLITE_ERROR is ** returned. However, in this case, data is read from the sqlite_stat1 ** table (if it is present) before returning. ** ** If an OOM error occurs, this function always sets db->mallocFailed. @@ -1883,11 +1820,11 @@ pTab->tabFlags &= ~TF_HasStat1; } for(i=sqliteHashFirst(&pSchema->idxHash); i; i=sqliteHashNext(i)){ Index *pIdx = sqliteHashData(i); pIdx->hasStat1 = 0; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 sqlite3DeleteIndexSamples(db, pIdx); pIdx->aSample = 0; #endif } @@ -1911,11 +1848,11 @@ Index *pIdx = sqliteHashData(i); if( !pIdx->hasStat1 ) sqlite3DefaultRowEst(pIdx); } /* Load the statistics from the sqlite_stat4 table. */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( rc==SQLITE_OK ){ db->lookaside.bDisable++; rc = loadStat4(db, sInfo.zDatabase); db->lookaside.bDisable--; } Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -454,11 +454,11 @@ #endif sqlite3ExprDelete(db, p->pPartIdxWhere); sqlite3ExprListDelete(db, p->aColExpr); sqlite3DbFree(db, p->zColAff); if( p->isResized ) sqlite3DbFree(db, (void *)p->azColl); -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 sqlite3_free(p->aiRowEst); #endif sqlite3DbFree(db, p); } Index: src/ctime.c ================================================================== --- src/ctime.c +++ src/ctime.c @@ -304,12 +304,10 @@ #if SQLITE_ENABLE_SQLLOG "ENABLE_SQLLOG", #endif #if defined(SQLITE_ENABLE_STAT4) "ENABLE_STAT4", -#elif defined(SQLITE_ENABLE_STAT3) - "ENABLE_STAT3", #endif #if SQLITE_ENABLE_STMTVTAB "ENABLE_STMTVTAB", #endif #if SQLITE_ENABLE_STMT_SCANSTATUS Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -1984,13 +1984,10 @@ }; #ifndef SQLITE_OMIT_ALTERTABLE sqlite3AlterFunctions(); #endif sqlite3WindowFunctions(); -#if defined(SQLITE_ENABLE_STAT3) || defined(SQLITE_ENABLE_STAT4) - sqlite3AnalyzeFunctions(); -#endif sqlite3RegisterDateTimeFunctions(); sqlite3InsertBuiltinFuncs(aBuiltinFunc, ArraySize(aBuiltinFunc)); #if 0 /* Enable to print out how the built-in functions are hashed */ { Index: src/shell.c.in ================================================================== --- src/shell.c.in +++ src/shell.c.in @@ -7505,12 +7505,10 @@ sqlite3_exec(p->db, "SELECT 'ANALYZE sqlite_master'", callback, &data, &zErrMsg); data.cMode = data.mode = MODE_Insert; data.zDestTable = "sqlite_stat1"; shell_exec(&data, "SELECT * FROM sqlite_stat1", &zErrMsg); - data.zDestTable = "sqlite_stat3"; - shell_exec(&data, "SELECT * FROM sqlite_stat3", &zErrMsg); data.zDestTable = "sqlite_stat4"; shell_exec(&data, "SELECT * FROM sqlite_stat4", &zErrMsg); raw_printf(p->out, "ANALYZE sqlite_master;\n"); } }else @@ -8927,12 +8925,11 @@ appendText(&sQuery,"SELECT name,seq FROM sqlite_sequence" " ORDER BY name;", 0); }else if( strcmp(zTab, "sqlite_stat1")==0 ){ appendText(&sQuery,"SELECT tbl,idx,stat FROM sqlite_stat1" " ORDER BY tbl,idx;", 0); - }else if( strcmp(zTab, "sqlite_stat3")==0 - || strcmp(zTab, "sqlite_stat4")==0 ){ + }else if( strcmp(zTab, "sqlite_stat4")==0 ){ appendText(&sQuery, "SELECT * FROM ", 0); appendText(&sQuery, zTab, 0); appendText(&sQuery, " ORDER BY tbl, idx, rowid;\n", 0); } appendText(&sSql, zSep, 0); Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -3812,11 +3812,11 @@ ** a schema change, on the first [sqlite3_step()] call following any change ** to the [sqlite3_bind_text | bindings] of that [parameter]. ** ^The specific value of WHERE-clause [parameter] might influence the ** choice of query plan if the parameter is the left-hand side of a [LIKE] ** or [GLOB] operator or if the parameter is compared to an indexed column -** and the [SQLITE_ENABLE_STAT3] compile-time option is enabled. +** and the [SQLITE_ENABLE_STAT4] compile-time option is enabled. ** ** ** **

^sqlite3_prepare_v3() differs from sqlite3_prepare_v2() only in having ** the extra prepFlags parameter, which is a bit array consisting of zero or Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -933,24 +933,10 @@ #if SQLITE_DEFAULT_MMAP_SIZE>SQLITE_MAX_MMAP_SIZE # undef SQLITE_DEFAULT_MMAP_SIZE # define SQLITE_DEFAULT_MMAP_SIZE SQLITE_MAX_MMAP_SIZE #endif -/* -** Only one of SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4 can be defined. -** Priority is given to SQLITE_ENABLE_STAT4. If either are defined, also -** define SQLITE_ENABLE_STAT3_OR_STAT4 -*/ -#ifdef SQLITE_ENABLE_STAT4 -# undef SQLITE_ENABLE_STAT3 -# define SQLITE_ENABLE_STAT3_OR_STAT4 1 -#elif SQLITE_ENABLE_STAT3 -# define SQLITE_ENABLE_STAT3_OR_STAT4 1 -#elif SQLITE_ENABLE_STAT3_OR_STAT4 -# undef SQLITE_ENABLE_STAT3_OR_STAT4 -#endif - /* ** SELECTTRACE_ENABLED will be either 1 or 0 depending on whether or not ** the Select query generator tracing logic is turned on. */ #if defined(SQLITE_ENABLE_SELECTTRACE) @@ -1585,12 +1571,12 @@ #define SQLITE_OrderByIdxJoin 0x0040 /* ORDER BY of joins via index */ #define SQLITE_Transitive 0x0080 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0100 /* Omit unused tables in joins */ #define SQLITE_CountOfView 0x0200 /* The count-of-view optimization */ #define SQLITE_CursorHints 0x0400 /* Add OP_CursorHint opcodes */ -#define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */ - /* TH3 expects the Stat34 ^^^^^^ value to be 0x0800. Don't change it */ +#define SQLITE_Stat4 0x0800 /* Use STAT4 data */ + /* TH3 expects the Stat4 ^^^^^^ value to be 0x0800. Don't change it */ #define SQLITE_PushDown 0x1000 /* The push-down optimization */ #define SQLITE_SimplifyJoin 0x2000 /* Convert LEFT JOIN to JOIN */ #define SQLITE_SkipScan 0x4000 /* Skip-scans */ #define SQLITE_PropagateConst 0x8000 /* The constant propagation opt */ #define SQLITE_AllOpts 0xffff /* All optimizations */ @@ -2256,11 +2242,11 @@ unsigned isCovering:1; /* True if this is a covering index */ unsigned noSkipScan:1; /* Do not try to use skip-scan if true */ unsigned hasStat1:1; /* aiRowLogEst values come from sqlite_stat1 */ unsigned bNoQuery:1; /* Do not use this index to optimize queries */ unsigned bAscKeyBug:1; /* True if the bba7b69f9849b5bf bug applies */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 int nSample; /* Number of elements in aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ tRowcnt *aAvgEq; /* Average nEq values for keys not in aSample */ IndexSample *aSample; /* Samples of the left-most key */ tRowcnt *aiRowEst; /* Non-logarithmic stat1 data for this index */ @@ -2288,11 +2274,11 @@ */ #define XN_ROWID (-1) /* Indexed column is the rowid */ #define XN_EXPR (-2) /* Indexed column is an expression */ /* -** Each sample stored in the sqlite_stat3 table is represented in memory +** Each sample stored in the sqlite_stat4 table is represented in memory ** using a structure of this type. See documentation at the top of the ** analyze.c source file for additional information. */ struct IndexSample { void *p; /* Pointer to sampled record */ @@ -4212,11 +4198,11 @@ LogEst sqlite3LogEstAdd(LogEst,LogEst); #ifndef SQLITE_OMIT_VIRTUALTABLE LogEst sqlite3LogEstFromDouble(double); #endif #if defined(SQLITE_ENABLE_STMT_SCANSTATUS) || \ - defined(SQLITE_ENABLE_STAT3_OR_STAT4) || \ + defined(SQLITE_ENABLE_STAT4) || \ defined(SQLITE_EXPLAIN_ESTIMATED_ROWS) u64 sqlite3LogEstToInt(LogEst); #endif VList *sqlite3VListAdd(sqlite3*,VList*,const char*,int,int); const char *sqlite3VListNumToName(VList*,int); @@ -4399,12 +4385,11 @@ int sqlite3ExprCheckIN(Parse*, Expr*); #else # define sqlite3ExprCheckIN(x,y) SQLITE_OK #endif -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 -void sqlite3AnalyzeFunctions(void); +#ifdef SQLITE_ENABLE_STAT4 int sqlite3Stat4ProbeSetValue( Parse*,Index*,UnpackedRecord**,Expr*,int,int,int*); int sqlite3Stat4ValueFromExpr(Parse*, Expr*, u8, sqlite3_value**); void sqlite3Stat4ProbeFree(UnpackedRecord*); int sqlite3Stat4Column(sqlite3*, const void*, int, int, sqlite3_value**); Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -7171,12 +7171,11 @@ { "distinct-opt", SQLITE_DistinctOpt }, { "cover-idx-scan", SQLITE_CoverIdxScan }, { "order-by-idx-join", SQLITE_OrderByIdxJoin }, { "transitive", SQLITE_Transitive }, { "omit-noop-join", SQLITE_OmitNoopJoin }, - { "stat3", SQLITE_Stat34 }, - { "stat4", SQLITE_Stat34 }, + { "stat4", SQLITE_Stat4 }, { "skip-scan", SQLITE_SkipScan }, }; if( objc!=4 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN"); Index: src/test_config.c ================================================================== --- src/test_config.c +++ src/test_config.c @@ -583,16 +583,10 @@ #ifdef SQLITE_ENABLE_STAT4 Tcl_SetVar2(interp, "sqlite_options", "stat4", "1", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "stat4", "0", TCL_GLOBAL_ONLY); #endif -#if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4) - Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY); -#else - Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY); -#endif - #if defined(SQLITE_ENABLE_STMTVTAB) && !defined(SQLITE_OMIT_VIRTUALTABLE) Tcl_SetVar2(interp, "sqlite_options", "stmtvtab", "1", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "stmtvtab", "0", TCL_GLOBAL_ONLY); #endif Index: src/util.c ================================================================== --- src/util.c +++ src/util.c @@ -1496,11 +1496,11 @@ return e*10; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ #if defined(SQLITE_ENABLE_STMT_SCANSTATUS) || \ - defined(SQLITE_ENABLE_STAT3_OR_STAT4) || \ + defined(SQLITE_ENABLE_STAT4) || \ defined(SQLITE_EXPLAIN_ESTIMATED_ROWS) /* ** Convert a LogEst into an integer. ** ** Note that this routine is only used when one or more of various @@ -1514,11 +1514,11 @@ else if( n>=1 ) n -= 1; #if defined(SQLITE_ENABLE_STMT_SCANSTATUS) || \ defined(SQLITE_EXPLAIN_ESTIMATED_ROWS) if( x>60 ) return (u64)LARGEST_INT64; #else - /* If only SQLITE_ENABLE_STAT3_OR_STAT4 is on, then the largest input + /* If only SQLITE_ENABLE_STAT4 is on, then the largest input ** possible to this routine is 310, resulting in a maximum x of 31 */ assert( x<=60 ); #endif return x>=3 ? (n+8)<<(x-3) : (n+8)>>(3-x); } Index: src/vdbeInt.h ================================================================== --- src/vdbeInt.h +++ src/vdbeInt.h @@ -484,13 +484,10 @@ void sqliteVdbePopStack(Vdbe*,int); int sqlite3VdbeCursorMoveto(VdbeCursor**, int*); int sqlite3VdbeCursorRestore(VdbeCursor*); u32 sqlite3VdbeSerialTypeLen(u32); u8 sqlite3VdbeOneByteSerialTypeLen(u8); -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 -u32 sqlite3VdbeSerialType(Mem*, int, u32*); -#endif u32 sqlite3VdbeSerialPut(unsigned char*, Mem*, u32); u32 sqlite3VdbeSerialGet(const unsigned char*, u32, Mem*); void sqlite3VdbeDeleteAuxData(sqlite3*, AuxData**, int, int); int sqlite2BtreeKeyCompare(BtCursor *, const void *, int, int, int *); Index: src/vdbeapi.c ================================================================== --- src/vdbeapi.c +++ src/vdbeapi.c @@ -842,11 +842,11 @@ ** of the amount of time that elapses between invocations. In other words, ** the time returned is always the time of the first call. */ sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context *p){ int rc; -#ifndef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifndef SQLITE_ENABLE_STAT4 sqlite3_int64 *piTime = &p->pVdbe->iCurrentTime; assert( p->pVdbe!=0 ); #else sqlite3_int64 iTime = 0; sqlite3_int64 *piTime = p->pVdbe!=0 ? &p->pVdbe->iCurrentTime : &iTime; @@ -907,11 +907,11 @@ */ void *sqlite3_get_auxdata(sqlite3_context *pCtx, int iArg){ AuxData *pAuxData; assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) ); -#if SQLITE_ENABLE_STAT3_OR_STAT4 +#if SQLITE_ENABLE_STAT4 if( pCtx->pVdbe==0 ) return 0; #else assert( pCtx->pVdbe!=0 ); #endif for(pAuxData=pCtx->pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNextAux){ @@ -941,11 +941,11 @@ ){ AuxData *pAuxData; Vdbe *pVdbe = pCtx->pVdbe; assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) ); -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( pVdbe==0 ) goto failed; #else assert( pVdbe!=0 ); #endif Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -3431,20 +3431,21 @@ ** ** The 8 and 9 types were added in 3.3.0, file format 4. Prior versions ** of SQLite will not understand those serial types. */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#if 0 /* Inlined into the OP_MakeRecord opcode */ /* ** Return the serial-type for the value stored in pMem. ** ** This routine might convert a large MEM_IntReal value into MEM_Real. ** ** 2019-07-11: The primary user of this subroutine was the OP_MakeRecord ** opcode in the byte-code engine. But by moving this routine in-line, we ** can omit some redundant tests and make that opcode a lot faster. So -** this routine is now only used by the STAT3/4 logic. +** this routine is now only used by the STAT3 logic and STAT3 support has +** ended. The code is kept here for historical reference only. */ u32 sqlite3VdbeSerialType(Mem *pMem, int file_format, u32 *pLen){ int flags = pMem->flags; u32 n; @@ -3501,11 +3502,11 @@ n += pMem->u.nZero; } *pLen = n; return ((n*2) + 12 + ((flags&MEM_Str)!=0)); } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* inlined into OP_MakeRecord */ /* ** The sizes for serial types less than 128 */ static const u8 sqlite3SmallTypeSizes[] = { @@ -4906,11 +4907,11 @@ ** throw an error if it is given inputs that would make it non-deterministic. ** This routine is invoked by date/time functions that use non-deterministic ** features such as 'now'. */ int sqlite3NotPureFunc(sqlite3_context *pCtx){ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( pCtx->pVdbe==0 ) return 1; #endif if( pCtx->pVdbe->aOp[pCtx->iOp].opcode==OP_PureFunc ){ sqlite3_result_error(pCtx, "non-deterministic function in index expression or CHECK constraint", Index: src/vdbemem.c ================================================================== --- src/vdbemem.c +++ src/vdbemem.c @@ -1301,11 +1301,11 @@ ** already been allocated, allocate the UnpackedRecord structure that ** that function will return to its caller here. Then return a pointer to ** an sqlite3_value within the UnpackedRecord.a[] array. */ static sqlite3_value *valueNew(sqlite3 *db, struct ValueNewStat4Ctx *p){ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( p ){ UnpackedRecord *pRec = p->ppRec[0]; if( pRec==0 ){ Index *pIdx = p->pIdx; /* Index being probed */ @@ -1337,11 +1337,11 @@ pRec->nField = p->iVal+1; return &pRec->aMem[p->iVal]; } #else UNUSED_PARAMETER(p); -#endif /* defined(SQLITE_ENABLE_STAT3_OR_STAT4) */ +#endif /* defined(SQLITE_ENABLE_STAT4) */ return sqlite3ValueNew(db); } /* ** The expression object indicated by the second argument is guaranteed @@ -1361,11 +1361,11 @@ ** ** If the conditions above are not met, this function returns SQLITE_OK ** and sets (*ppVal) to NULL. Or, if an error occurs, (*ppVal) is set to ** NULL and an SQLite error code returned. */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 static int valueFromFunction( sqlite3 *db, /* The database connection */ Expr *p, /* The expression to evaluate */ u8 enc, /* Encoding to use */ u8 aff, /* Affinity to use */ @@ -1444,11 +1444,11 @@ *ppVal = pVal; return rc; } #else # define valueFromFunction(a,b,c,d,e,f) SQLITE_OK -#endif /* defined(SQLITE_ENABLE_STAT3_OR_STAT4) */ +#endif /* defined(SQLITE_ENABLE_STAT4) */ /* ** Extract a value from the supplied expression in the manner described ** above sqlite3ValueFromExpr(). Allocate the sqlite3_value object ** using valueNew(). @@ -1473,11 +1473,11 @@ const char *zNeg = ""; int rc = SQLITE_OK; assert( pExpr!=0 ); while( (op = pExpr->op)==TK_UPLUS || op==TK_SPAN ) pExpr = pExpr->pLeft; -#if defined(SQLITE_ENABLE_STAT3_OR_STAT4) +#if defined(SQLITE_ENABLE_STAT4) if( op==TK_REGISTER ) op = pExpr->op2; #else if( NEVER(op==TK_REGISTER) ) op = pExpr->op2; #endif @@ -1566,11 +1566,11 @@ assert( zVal[nVal]=='\'' ); sqlite3VdbeMemSetStr(pVal, sqlite3HexToBlob(db, zVal, nVal), nVal/2, 0, SQLITE_DYNAMIC); } #endif -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 else if( op==TK_FUNCTION && pCtx!=0 ){ rc = valueFromFunction(db, pExpr, enc, affinity, &pVal, pCtx); } #endif else if( op==TK_TRUEFALSE ){ @@ -1583,17 +1583,17 @@ *ppVal = pVal; return rc; no_mem: -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( pCtx==0 || pCtx->pParse->nErr==0 ) #endif sqlite3OomFault(db); sqlite3DbFree(db, zVal); assert( *ppVal==0 ); -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 if( pCtx==0 ) sqlite3ValueFree(pVal); #else assert( pCtx==0 ); sqlite3ValueFree(pVal); #endif return SQLITE_NOMEM_BKPT; @@ -1617,60 +1617,11 @@ sqlite3_value **ppVal /* Write the new value here */ ){ return pExpr ? valueFromExpr(db, pExpr, enc, affinity, ppVal, 0) : 0; } -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 -/* -** The implementation of the sqlite_record() function. This function accepts -** a single argument of any type. The return value is a formatted database -** record (a blob) containing the argument value. -** -** This is used to convert the value stored in the 'sample' column of the -** sqlite_stat3 table to the record format SQLite uses internally. -*/ -static void recordFunc( - sqlite3_context *context, - int argc, - sqlite3_value **argv -){ - const int file_format = 1; - u32 iSerial; /* Serial type */ - int nSerial; /* Bytes of space for iSerial as varint */ - u32 nVal; /* Bytes of space required for argv[0] */ - int nRet; - sqlite3 *db; - u8 *aRet; - - UNUSED_PARAMETER( argc ); - iSerial = sqlite3VdbeSerialType(argv[0], file_format, &nVal); - nSerial = sqlite3VarintLen(iSerial); - db = sqlite3_context_db_handle(context); - - nRet = 1 + nSerial + nVal; - aRet = sqlite3DbMallocRawNN(db, nRet); - if( aRet==0 ){ - sqlite3_result_error_nomem(context); - }else{ - aRet[0] = nSerial+1; - putVarint32(&aRet[1], iSerial); - sqlite3VdbeSerialPut(&aRet[1+nSerial], argv[0], iSerial); - sqlite3_result_blob(context, aRet, nRet, SQLITE_TRANSIENT); - sqlite3DbFreeNN(db, aRet); - } -} - -/* -** Register built-in functions used to help read ANALYZE data. -*/ -void sqlite3AnalyzeFunctions(void){ - static FuncDef aAnalyzeTableFuncs[] = { - FUNCTION(sqlite_record, 1, 0, 0, recordFunc), - }; - sqlite3InsertBuiltinFuncs(aAnalyzeTableFuncs, ArraySize(aAnalyzeTableFuncs)); -} - +#ifdef SQLITE_ENABLE_STAT4 /* ** Attempt to extract a value from pExpr and use it to construct *ppVal. ** ** If pAlloc is not NULL, then an UnpackedRecord object is created for ** pAlloc if one does not exist and the new value is added to the Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -1075,11 +1075,11 @@ pVtab->zErrMsg = 0; return rc; } #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 /* ** Estimate the location of a particular key among all keys in an ** index. Store the results in aStat as follows: ** ** aStat[0] Est. number of rows less than pRec @@ -1268,11 +1268,11 @@ /* Restore the pRec->nField value before returning. */ pRec->nField = nField; return i; } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ /* ** If it is not NULL, pTerm is a term that provides an upper or lower ** bound on a range scan. Without considering pTerm, it is estimated ** that the scan will visit nNew rows. This function returns the number @@ -1294,11 +1294,11 @@ } return nRet; } -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 /* ** Return the affinity for a single column of an index. */ char sqlite3IndexColumnAffinity(sqlite3 *db, Index *pIdx, int iCol){ assert( iCol>=0 && iColnColumn ); @@ -1309,11 +1309,11 @@ return pIdx->zColAff[iCol]; } #endif -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 /* ** This function is called to estimate the number of rows visited by a ** range-scan on a skip-scan index. For example: ** ** CREATE INDEX i1 ON t1(a, b, c); @@ -1415,11 +1415,11 @@ sqlite3ValueFree(p2); sqlite3ValueFree(pVal); return rc; } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ /* ** This function is used to estimate the number of rows that will be visited ** by scanning an index for a range of values. The range may have an upper ** bound, a lower bound, or both. The WHERE clause terms that set the upper @@ -1468,16 +1468,16 @@ ){ int rc = SQLITE_OK; int nOut = pLoop->nOut; LogEst nNew; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 Index *p = pLoop->u.btree.pIndex; int nEq = pLoop->u.btree.nEq; - if( p->nSample>0 && nEqnSampleCol - && OptimizationEnabled(pParse->db, SQLITE_Stat34) + if( p->nSample>0 && ALWAYS(nEqnSampleCol) + && OptimizationEnabled(pParse->db, SQLITE_Stat4) ){ if( nEq==pBuilder->nRecValid ){ UnpackedRecord *pRec = pBuilder->pRec; tRowcnt a[2]; int nBtm = pLoop->u.btree.nBtm; @@ -1571,11 +1571,11 @@ if( iUpper>iLower ){ nNew = sqlite3LogEst(iUpper - iLower); /* TUNING: If both iUpper and iLower are derived from the same ** sample, then assume they are 4x more selective. This brings ** the estimated selectivity more in line with what it would be - ** if estimated without the use of STAT3/4 tables. */ + ** if estimated without the use of STAT4 tables. */ if( iLwrIdx==iUprIdx ) nNew -= 20; assert( 20==sqlite3LogEst(4) ); }else{ nNew = 10; assert( 10==sqlite3LogEst(2) ); } if( nNewnOut = (LogEst)nOut; return rc; } -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 /* ** Estimate the number of rows that will be returned based on ** an equality constraint x=VALUE and where that VALUE occurs in ** the histogram data. This only works when x is the left-most -** column of an index and sqlite_stat3 histogram data is available +** column of an index and sqlite_stat4 histogram data is available ** for that index. When pExpr==NULL that means the constraint is ** "x IS NULL" instead of "x=VALUE". ** ** Write the estimated row count into *pnRow and return SQLITE_OK. ** If unable to make an estimate, leave *pnRow unchanged and return @@ -1683,13 +1683,13 @@ p->zName, nEq-1, (int)a[1])); *pnRow = a[1]; return rc; } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 /* ** Estimate the number of rows that will be returned based on ** an IN constraint where the right-hand side of the IN operator ** is a list of values. Example: ** @@ -1732,11 +1732,11 @@ WHERETRACE(0x10,("IN row estimate: est=%d\n", nRowEst)); } assert( pBuilder->nRecValid==nRecValid ); return rc; } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ #ifdef WHERETRACE_ENABLED /* ** Print the content of a WhereTerm object @@ -2453,11 +2453,11 @@ for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){ u16 eOp = pTerm->eOperator; /* Shorthand for pTerm->eOperator */ LogEst rCostIdx; LogEst nOutUnadjusted; /* nOut before IN() and WHERE adjustments */ int nIn = 0; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 int nRecValid = pBuilder->nRecValid; #endif if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0) && indexColumnNotNull(pProbe, saved_nEq) ){ @@ -2611,11 +2611,11 @@ ** values of nIn and nInMul. In other words, assuming that all ** "x IN(...)" terms are replaced with "x = ?". This block updates ** the value of pNew->nOut to account for pTerm (but not nIn/nInMul). */ assert( pNew->nOut==saved_nOut ); if( pNew->wsFlags & WHERE_COLUMN_RANGE ){ - /* Adjust nOut using stat3/stat4 data. Or, if there is no stat3/stat4 + /* Adjust nOut using stat4 data. Or, if there is no stat4 ** data, using some other estimate. */ whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew); }else{ int nEq = ++pNew->u.btree.nEq; assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) ); @@ -2625,17 +2625,17 @@ assert( (eOp & WO_IN) || nIn==0 ); testcase( eOp & WO_IN ); pNew->nOut += pTerm->truthProb; pNew->nOut -= nIn; }else{ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 tRowcnt nOut = 0; if( nInMul==0 && pProbe->nSample && pNew->u.btree.nEq<=pProbe->nSampleCol && ((eOp & WO_IN)==0 || !ExprHasProperty(pTerm->pExpr, EP_xIsSelect)) - && OptimizationEnabled(db, SQLITE_Stat34) + && OptimizationEnabled(db, SQLITE_Stat4) ){ Expr *pExpr = pTerm->pExpr; if( (eOp & (WO_EQ|WO_ISNULL|WO_IS))!=0 ){ testcase( eOp & WO_EQ ); testcase( eOp & WO_IS ); @@ -2693,11 +2693,11 @@ && pNew->u.btree.nEqnColumn ){ whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn); } pNew->nOut = saved_nOut; -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 pBuilder->nRecValid = nRecValid; #endif } pNew->prereq = saved_prereq; pNew->u.btree.nEq = saved_nEq; @@ -3066,11 +3066,11 @@ ** unique index is used (making the index functionally non-unique) ** then the sqlite_stat1 data becomes important for scoring the ** plan */ pTab->tabFlags |= TF_StatsUsed; } -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 sqlite3Stat4ProbeFree(pBuilder->pRec); pBuilder->nRecValid = 0; pBuilder->pRec = 0; #endif } Index: src/whereInt.h ================================================================== --- src/whereInt.h +++ src/whereInt.h @@ -277,14 +277,14 @@ #define TERM_CODED 0x04 /* This term is already coded */ #define TERM_COPIED 0x08 /* Has a child */ #define TERM_ORINFO 0x10 /* Need to free the WhereTerm.u.pOrInfo object */ #define TERM_ANDINFO 0x20 /* Need to free the WhereTerm.u.pAndInfo obj */ #define TERM_OR_OK 0x40 /* Used during OR-clause processing */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 # define TERM_VNULL 0x80 /* Manufactured x>NULL or x<=NULL term */ #else -# define TERM_VNULL 0x00 /* Disabled if not using stat3 */ +# define TERM_VNULL 0x00 /* Disabled if not using stat4 */ #endif #define TERM_LIKEOPT 0x100 /* Virtual terms from the LIKE optimization */ #define TERM_LIKECOND 0x200 /* Conditionally this LIKE operator term */ #define TERM_LIKE 0x400 /* The original LIKE operator */ #define TERM_IS 0x800 /* Term.pExpr is an IS operator */ @@ -397,11 +397,11 @@ WhereInfo *pWInfo; /* Information about this WHERE */ WhereClause *pWC; /* WHERE clause terms */ ExprList *pOrderBy; /* ORDER BY clause */ WhereLoop *pNew; /* Template WhereLoop */ WhereOrSet *pOrSet; /* Record best loops here, if not NULL */ -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 +#ifdef SQLITE_ENABLE_STAT4 UnpackedRecord *pRec; /* Probe for stat4 (if required) */ int nRecValid; /* Number of valid fields currently in pRec */ #endif unsigned int bldFlags; /* SQLITE_BLDF_* flags */ unsigned int iPlanLimit; /* Search limiter */ Index: src/whereexpr.c ================================================================== --- src/whereexpr.c +++ src/whereexpr.c @@ -1379,12 +1379,12 @@ exprAnalyze(pSrc, pWC, idxNew); markTermAsChild(pWC, idxNew, idxTerm); } } -#ifdef SQLITE_ENABLE_STAT3_OR_STAT4 - /* When sqlite_stat3 histogram data is available an operator of the +#ifdef SQLITE_ENABLE_STAT4 + /* When sqlite_stat4 histogram data is available an operator of the ** form "x IS NOT NULL" can sometimes be evaluated more efficiently ** as "x>NULL" if x is not an INTEGER PRIMARY KEY. So construct a ** virtual term of that form. ** ** Note that the virtual term must be tagged with TERM_VNULL. @@ -1391,11 +1391,11 @@ */ if( pExpr->op==TK_NOTNULL && pExpr->pLeft->op==TK_COLUMN && pExpr->pLeft->iColumn>=0 && !ExprHasProperty(pExpr, EP_FromJoin) - && OptimizationEnabled(db, SQLITE_Stat34) + && OptimizationEnabled(db, SQLITE_Stat4) ){ Expr *pNewExpr; Expr *pLeft = pExpr->pLeft; int idxNew; WhereTerm *pNewTerm; @@ -1416,11 +1416,11 @@ pTerm = &pWC->a[idxTerm]; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } -#endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ +#endif /* SQLITE_ENABLE_STAT4 */ /* Prevent ON clause terms of a LEFT JOIN from being used to drive ** an index for tables to the left of the join. */ testcase( pTerm!=&pWC->a[idxTerm] ); Index: test/alter.test ================================================================== --- test/alter.test +++ test/alter.test @@ -854,11 +854,10 @@ # Test that it is not possible to use ALTER TABLE on any system table. # set system_table_list {1 sqlite_master} catchsql ANALYZE ifcapable analyze { lappend system_table_list 2 sqlite_stat1 } -ifcapable stat3 { lappend system_table_list 3 sqlite_stat3 } ifcapable stat4 { lappend system_table_list 4 sqlite_stat4 } foreach {tn tbl} $system_table_list { do_test alter-15.$tn.1 { catchsql "ALTER TABLE $tbl RENAME TO xyz" Index: test/altertab.test ================================================================== --- test/altertab.test +++ test/altertab.test @@ -592,6 +592,5 @@ do_execsql_test 18.2.2 { SELECT sql FROM sqlite_master; } {{CREATE TABLE t0 (c1 INTEGER, PRIMARY KEY(c1))}} finish_test - Index: test/altertab3.test ================================================================== --- test/altertab3.test +++ test/altertab3.test @@ -381,6 +381,5 @@ END} } finish_test - Index: test/analyze.test ================================================================== --- test/analyze.test +++ test/analyze.test @@ -286,11 +286,11 @@ SELECT * FROM t4 WHERE x=1234; } } {} # Verify that DROP TABLE and DROP INDEX remove entries from the -# sqlite_stat1, sqlite_stat3 and sqlite_stat4 tables. +# sqlite_stat1 and sqlite_stat4 tables. # do_test analyze-5.0 { execsql { DELETE FROM t3; DELETE FROM t4; @@ -304,47 +304,46 @@ ANALYZE; SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1; } } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4} -ifcapable stat4||stat3 { - ifcapable stat4 {set stat sqlite_stat4} else {set stat sqlite_stat3} +ifcapable stat4 { do_test analyze-5.1 { - execsql " - SELECT DISTINCT idx FROM $stat ORDER BY 1; - SELECT DISTINCT tbl FROM $stat ORDER BY 1; - " + execsql { + SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1; + SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1; + } } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4} } do_test analyze-5.2 { execsql { DROP INDEX t3i2; SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1; } } {t3i1 t3i3 t4i1 t4i2 t3 t4} -ifcapable stat4||stat3 { +ifcapable stat4 { do_test analyze-5.3 { - execsql " - SELECT DISTINCT idx FROM $stat ORDER BY 1; - SELECT DISTINCT tbl FROM $stat ORDER BY 1; - " + execsql { + SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1; + SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1; + } } {t3i1 t3i3 t4i1 t4i2 t3 t4} } do_test analyze-5.4 { execsql { DROP TABLE t3; SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1; SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1; } } {t4i1 t4i2 t4} -ifcapable stat4||stat3 { +ifcapable stat4 { do_test analyze-5.5 { - execsql " - SELECT DISTINCT idx FROM $stat ORDER BY 1; - SELECT DISTINCT tbl FROM $stat ORDER BY 1; - " + execsql { + SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1; + SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1; + } } {t4i1 t4i2 t4} } # This test corrupts the database file so it must be the last test # in the series. Index: test/analyze3.test ================================================================== --- test/analyze3.test +++ test/analyze3.test @@ -16,11 +16,11 @@ set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix analyze3 -ifcapable !stat4&&!stat3 { +ifcapable !stat4 { finish_test return } #---------------------------------------------------------------------- @@ -98,15 +98,11 @@ execsql { COMMIT; ANALYZE; } - ifcapable stat4 { - execsql { SELECT count(*)>0 FROM sqlite_stat4; } - } else { - execsql { SELECT count(*)>0 FROM sqlite_stat3; } - } + execsql { SELECT count(*)>0 FROM sqlite_stat4; } } {1} do_execsql_test analyze3-1.1.x { SELECT count(*) FROM t1 WHERE x>200 AND x<300; SELECT count(*) FROM t1 WHERE x>0 AND x<1100; Index: test/analyze5.test ================================================================== --- test/analyze5.test +++ test/analyze5.test @@ -15,11 +15,11 @@ # set testdir [file dirname $argv0] source $testdir/tester.tcl -ifcapable !stat4&&!stat3 { +ifcapable !stat4 { finish_test return } set testprefix analyze5 @@ -65,43 +65,25 @@ CREATE INDEX t1x ON t1(x); -- integers 1, 2, 3 and many NULLs CREATE INDEX t1y ON t1(y); -- integers 0 and very few 1s CREATE INDEX t1z ON t1(z); -- integers 0, 1, 2, and 3 ANALYZE; } - ifcapable stat4 { - db eval { - SELECT DISTINCT lindex(test_decode(sample),0) - FROM sqlite_stat4 WHERE idx='t1u' ORDER BY nlt; - } - } else { - db eval { - SELECT sample FROM sqlite_stat3 WHERE idx='t1u' ORDER BY nlt; - } + db eval { + SELECT DISTINCT lindex(test_decode(sample),0) + FROM sqlite_stat4 WHERE idx='t1u' ORDER BY nlt; } } {alpha bravo charlie delta} do_test analyze5-1.1 { - ifcapable stat4 { - db eval { - SELECT DISTINCT lower(lindex(test_decode(sample), 0)) - FROM sqlite_stat4 WHERE idx='t1v' ORDER BY 1 - } - } else { - db eval { - SELECT lower(sample) FROM sqlite_stat3 WHERE idx='t1v' ORDER BY 1 - } + db eval { + SELECT DISTINCT lower(lindex(test_decode(sample), 0)) + FROM sqlite_stat4 WHERE idx='t1v' ORDER BY 1 } } {alpha bravo charlie delta} -ifcapable stat4 { - do_test analyze5-1.2 { - db eval {SELECT idx, count(*) FROM sqlite_stat4 GROUP BY 1 ORDER BY 1} - } {t1t 8 t1u 8 t1v 8 t1w 8 t1x 8 t1y 9 t1z 8} -} else { - do_test analyze5-1.2 { - db eval {SELECT idx, count(*) FROM sqlite_stat3 GROUP BY 1 ORDER BY 1} - } {t1t 4 t1u 4 t1v 4 t1w 4 t1x 4 t1y 2 t1z 4} -} +do_test analyze5-1.2 { + db eval {SELECT idx, count(*) FROM sqlite_stat4 GROUP BY 1 ORDER BY 1} +} {t1t 8 t1u 8 t1v 8 t1w 8 t1x 8 t1y 9 t1z 8} # Verify that range queries generate the correct row count estimates # foreach {testid where index rows} { 1 {z>=0 AND z<=0} t1z 400 Index: test/analyze6.test ================================================================== --- test/analyze6.test +++ test/analyze6.test @@ -15,11 +15,11 @@ # set testdir [file dirname $argv0] source $testdir/tester.tcl -ifcapable !stat4&&!stat3 { +ifcapable !stat4 { finish_test return } set testprefix analyze6 Index: test/analyze7.test ================================================================== --- test/analyze7.test +++ test/analyze7.test @@ -80,11 +80,11 @@ execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;} } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/} do_test analyze7-3.2.1 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;} } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/} -ifcapable stat4||stat3 { +ifcapable stat4 { # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated # row count for (c=2) than it does for (c=?). do_test analyze7-3.2.2 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;} } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/} @@ -97,11 +97,11 @@ } do_test analyze7-3.3 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123} } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/} -ifcapable {!stat4 && !stat3} { +ifcapable {!stat4} { do_test analyze7-3.4 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123} } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/} do_test analyze7-3.5 { execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123} Index: test/analyze8.test ================================================================== --- test/analyze8.test +++ test/analyze8.test @@ -8,17 +8,17 @@ # May you share freely, never taking more than you give. # #*********************************************************************** # # This file implements tests for SQLite library. The focus of the tests -# in this file is testing the capabilities of sqlite_stat3. +# in this file is testing the capabilities of sqlite_stat4. # set testdir [file dirname $argv0] source $testdir/tester.tcl -ifcapable !stat4&&!stat3 { +ifcapable !stat4 { finish_test return } set testprefix analyze8 DELETED test/analyzeA.test Index: test/analyzeA.test ================================================================== --- test/analyzeA.test +++ /dev/null @@ -1,186 +0,0 @@ -# 2013 August 3 -# -# The author disclaims copyright to this source code. In place of -# a legal notice, here is a blessing: -# -# May you do good and not evil. -# May you find forgiveness for yourself and forgive others. -# May you share freely, never taking more than you give. -# -#*********************************************************************** -# -# This file contains automated tests used to verify that the current build -# (which must be either ENABLE_STAT3 or ENABLE_STAT4) works with both stat3 -# and stat4 data. -# - -set testdir [file dirname $argv0] -source $testdir/tester.tcl -set testprefix analyzeA - -ifcapable !stat4&&!stat3 { - finish_test - return -} - -# Populate the stat3 table according to the current contents of the db -# -proc populate_stat3 {{bDropTable 1}} { - # Open a second connection on database "test.db" and run ANALYZE. If this - # is an ENABLE_STAT3 build, this is all that is required to create and - # populate the sqlite_stat3 table. - # - sqlite3 db2 test.db - execsql { ANALYZE } - - # Now, if this is an ENABLE_STAT4 build, create and populate the - # sqlite_stat3 table based on the stat4 data gathered by the ANALYZE - # above. Then drop the sqlite_stat4 table. - # - ifcapable stat4 { - db2 func lindex lindex - execsql { - PRAGMA writable_schema = on; - CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample); - INSERT INTO sqlite_stat3 - SELECT DISTINCT tbl, idx, - lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0) - FROM sqlite_stat4; - } db2 - if {$bDropTable} { execsql {DROP TABLE sqlite_stat4} db2 } - execsql { PRAGMA writable_schema = off } - } - - # Modify the database schema cookie to ensure that the other connection - # reloads the schema. - # - execsql { - CREATE TABLE obscure_tbl_nm(x); - DROP TABLE obscure_tbl_nm; - } db2 - db2 close -} - -# Populate the stat4 table according to the current contents of the db -# -proc populate_stat4 {{bDropTable 1}} { - sqlite3 db2 test.db - execsql { ANALYZE } - - ifcapable stat3 { - execsql { - PRAGMA writable_schema = on; - CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample); - INSERT INTO sqlite_stat4 - SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample) - FROM sqlite_stat3; - } db2 - if {$bDropTable} { execsql {DROP TABLE sqlite_stat3} db2 } - execsql { PRAGMA writable_schema = off } - } - - # Modify the database schema cookie to ensure that the other connection - # reloads the schema. - # - execsql { - CREATE TABLE obscure_tbl_nm(x); - DROP TABLE obscure_tbl_nm; - } db2 - db2 close -} - -# Populate the stat4 table according to the current contents of the db. -# Leave deceptive data in the stat3 table. This data should be ignored -# in favour of that from the stat4 table. -# -proc populate_both {} { - ifcapable stat4 { populate_stat3 0 } - ifcapable stat3 { populate_stat4 0 } - - sqlite3 db2 test.db - execsql { - PRAGMA writable_schema = on; - UPDATE sqlite_stat3 SET idx = - CASE idx WHEN 't1b' THEN 't1c' ELSE 't1b' - END; - PRAGMA writable_schema = off; - CREATE TABLE obscure_tbl_nm(x); - DROP TABLE obscure_tbl_nm; - } db2 - db2 close -} - -foreach {tn analyze_cmd} { - 1 populate_stat4 - 2 populate_stat3 - 3 populate_both -} { - reset_db - do_test 1.$tn.1 { - execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT) } - for {set i 0} {$i < 100} {incr i} { - set c [expr int(pow(1.1,$i)/100)] - set b [expr 125 - int(pow(1.1,99-$i))/100] - execsql {INSERT INTO t1 VALUES($i, $b, $c)} - } - } {} - - execsql { CREATE INDEX t1b ON t1(b) } - execsql { CREATE INDEX t1c ON t1(c) } - $analyze_cmd - - do_execsql_test 1.$tn.2.1 { SELECT count(*) FROM t1 WHERE b=31 } 1 - do_execsql_test 1.$tn.2.2 { SELECT count(*) FROM t1 WHERE c=0 } 49 - do_execsql_test 1.$tn.2.3 { SELECT count(*) FROM t1 WHERE b=125 } 49 - do_execsql_test 1.$tn.2.4 { SELECT count(*) FROM t1 WHERE c=16 } 1 - - do_eqp_test 1.$tn.2.5 { - SELECT * FROM t1 WHERE b = 31 AND c = 0; - } {SEARCH TABLE t1 USING INDEX t1b (b=?)} - do_eqp_test 1.$tn.2.6 { - SELECT * FROM t1 WHERE b = 125 AND c = 16; - } {SEARCH TABLE t1 USING INDEX t1c (c=?)} - - do_execsql_test 1.$tn.3.1 { - SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50 - } {6} - do_execsql_test 1.$tn.3.2 { - SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50 - } {90} - do_execsql_test 1.$tn.3.3 { - SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125 - } {90} - do_execsql_test 1.$tn.3.4 { - SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125 - } {6} - - do_eqp_test 1.$tn.3.5 { - SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50 - } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b? AND c? AND b? AND b? AND c? AND c90} { set a $i } else { set a NULL } - set b [expr $i % 5] - execsql "INSERT INTO t3 VALUES($a, $b)" - } - execsql ANALYZE -} {} -do_eqp_test 10.1.3 { - SELECT * FROM t3 WHERE a IS NULL AND b = 2 -} {/t3 USING INDEX t3b/} -do_eqp_test 10.1.4 { - SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2 -} {/t3 USING INDEX t3a/} - -#------------------------------------------------------------------------- -# Check that stat3 data is used correctly with non-default collation -# sequences. -# -foreach {tn schema} { - 1 { - CREATE TABLE t4(a COLLATE nocase, b); - CREATE INDEX t4a ON t4(a); - CREATE INDEX t4b ON t4(b); - } - 2 { - CREATE TABLE t4(a, b); - CREATE INDEX t4a ON t4(a COLLATE nocase); - CREATE INDEX t4b ON t4(b); - } -} { - drop_all_tables - do_test 11.$tn.1 { execsql $schema } {} - - do_test 11.$tn.2 { - for {set i 0} {$i < 100} {incr i} { - if { ($i % 10)==0 } { set a ABC } else { set a DEF } - set b [expr $i % 5] - execsql { INSERT INTO t4 VALUES($a, $b) } - } - execsql ANALYZE - } {} - - do_eqp_test 11.$tn.3 { - SELECT * FROM t4 WHERE a = 'def' AND b = 3; - } {/t4 USING INDEX t4b/} - - if {$tn==1} { - set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;" - do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/} - } else { - - set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;" - do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/} - - set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;" - do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/} - } -} - -#------------------------------------------------------------------------- -# Test that nothing untoward happens if the stat3 table contains entries -# for indexes that do not exist. Or NULL values in the idx column. -# Or NULL values in any of the other columns. -# -drop_all_tables -do_execsql_test 15.1 { - CREATE TABLE x1(a, b, UNIQUE(a, b)); - INSERT INTO x1 VALUES(1, 2); - INSERT INTO x1 VALUES(3, 4); - INSERT INTO x1 VALUES(5, 6); - ANALYZE; - INSERT INTO sqlite_stat3 VALUES(NULL, NULL, NULL, NULL, NULL, NULL); -} -db close -sqlite3 db test.db -do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6} - -do_execsql_test 15.3 { - INSERT INTO sqlite_stat3 VALUES(42, 42, 42, 42, 42, 42); -} -db close -sqlite3 db test.db -do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6} - -do_execsql_test 15.5 { - UPDATE sqlite_stat1 SET stat = NULL; -} -db close -sqlite3 db test.db -do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6} - -do_execsql_test 15.7 { - ANALYZE; - UPDATE sqlite_stat1 SET tbl = 'no such tbl'; -} -db close -sqlite3 db test.db -do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6} - -do_execsql_test 15.9 { - ANALYZE; - UPDATE sqlite_stat3 SET neq = NULL, nlt=NULL, ndlt=NULL; -} -db close -sqlite3 db test.db -do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6} - -# This is just for coverage.... -do_execsql_test 15.11 { - ANALYZE; - UPDATE sqlite_stat1 SET stat = stat || ' unordered'; -} -db close -sqlite3 db test.db -do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6} - -#------------------------------------------------------------------------- -# Test that allocations used for sqlite_stat3 samples are included in -# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED. -# -set one [string repeat x 1000] -set two [string repeat x 2000] -do_test 16.1 { - reset_db - execsql { - CREATE TABLE t1(a, UNIQUE(a)); - INSERT INTO t1 VALUES($one); - ANALYZE; - } - set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] - - reset_db - execsql { - CREATE TABLE t1(a, UNIQUE(a)); - INSERT INTO t1 VALUES($two); - ANALYZE; - } - set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] - - expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050} -} {1} - -#------------------------------------------------------------------------- -# Test that stat3 data may be used with partial indexes. -# -do_test 17.1 { - reset_db - execsql { - CREATE TABLE t1(a, b, c, d); - CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL; - INSERT INTO t1 VALUES(-1, -1, -1, NULL); - INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; - INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; - INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; - INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; - INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; - INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; - } - - for {set i 0} {$i < 32} {incr i} { - execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') } - } - execsql {ANALYZE main.t1} -} {} - -do_catchsql_test 17.1.2 { - ANALYZE temp.t1; -} {1 {no such table: temp.t1}} - -do_eqp_test 17.2 { - SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; -} {/USING INDEX i1/} -do_eqp_test 17.3 { - SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; -} {/USING INDEX i1/} - -do_execsql_test 17.4 { - CREATE INDEX i2 ON t1(c) WHERE d IS NOT NULL; - ANALYZE main.i2; -} -do_eqp_test 17.5 { - SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; -} {/USING INDEX i1/} -do_eqp_test 17.6 { - SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; -} {/USING INDEX i2/} - -#------------------------------------------------------------------------- -# -do_test 18.1 { - reset_db - execsql { - CREATE TABLE t1(a, b); - CREATE INDEX i1 ON t1(a, b); - } - for {set i 0} {$i < 9} {incr i} { - execsql { - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - INSERT INTO t1 VALUES($i, 0); - } - } - execsql ANALYZE - execsql { SELECT count(*) FROM sqlite_stat3 } -} {9} - -#------------------------------------------------------------------------- -# For coverage. -# -ifcapable view { - do_test 19.1 { - reset_db - execsql { - CREATE TABLE t1(x, y); - CREATE INDEX i1 ON t1(x, y); - CREATE VIEW v1 AS SELECT * FROM t1; - ANALYZE; - } - } {} -} -ifcapable auth { - proc authproc {op args} { - if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" } - return "SQLITE_OK" - } - do_test 19.2 { - reset_db - db auth authproc - execsql { - CREATE TABLE t1(x, y); - CREATE VIEW v1 AS SELECT * FROM t1; - } - catchsql ANALYZE - } {1 {not authorized}} -} - -#------------------------------------------------------------------------- -# -reset_db -proc r {args} { expr rand() } -db func r r -db func lrange lrange -do_test 20.1 { - execsql { - CREATE TABLE t1(a,b,c,d); - CREATE INDEX i1 ON t1(a,b,c,d); - } - for {set i 0} {$i < 16} {incr i} { - execsql { - INSERT INTO t1 VALUES($i, r(), r(), r()); - INSERT INTO t1 VALUES($i, $i, r(), r()); - INSERT INTO t1 VALUES($i, $i, $i, r()); - INSERT INTO t1 VALUES($i, $i, $i, $i); - INSERT INTO t1 VALUES($i, $i, $i, $i); - INSERT INTO t1 VALUES($i, $i, $i, r()); - INSERT INTO t1 VALUES($i, $i, r(), r()); - INSERT INTO t1 VALUES($i, r(), r(), r()); - } - } -} {} -do_execsql_test 20.2 { ANALYZE } -for {set i 0} {$i<16} {incr i} { - set val $i - do_execsql_test 20.3.$i { - SELECT count(*) FROM sqlite_stat3 WHERE sample=$val - } {1} -} - -finish_test Index: test/auth.test ================================================================== --- test/auth.test +++ test/auth.test @@ -2448,15 +2448,11 @@ } } ifcapable stat4 { set stat4 "sqlite_stat4 " } else { - ifcapable stat3 { - set stat4 "sqlite_stat3 " - } else { - set stat4 "" - } + set stat4 "" } do_test auth-5.2 { execsql { SELECT name FROM ( SELECT * FROM sqlite_master UNION ALL SELECT * FROM temp.sqlite_master) Index: test/dbstatus.test ================================================================== --- test/dbstatus.test +++ test/dbstatus.test @@ -61,11 +61,11 @@ expr { $::lookaside_buffer_size * [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1] } } -ifcapable stat4||stat3 { +ifcapable stat4 { set STAT3 1 } else { set STAT3 0 } Index: test/filter1.test ================================================================== --- test/filter1.test +++ test/filter1.test @@ -100,7 +100,5 @@ do_catchsql_test 2.3 { SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1 } {1 {misuse of aggregate function count()}} finish_test - - Index: test/fkey8.test ================================================================== --- test/fkey8.test +++ test/fkey8.test @@ -227,6 +227,5 @@ do_execsql_test 5.3 { PRAGMA integrity_check; } {ok} finish_test - Index: test/fts3corrupt4.test ================================================================== --- test/fts3corrupt4.test +++ test/fts3corrupt4.test @@ -5317,6 +5317,5 @@ INSERT INTO t1(b) VALUES(x'78'); INSERT INTO t1(t1) SELECT x FROM t2; } {1 {database disk image is malformed}} finish_test - Index: test/fts3corrupt5.test ================================================================== --- test/fts3corrupt5.test +++ test/fts3corrupt5.test @@ -55,6 +55,5 @@ SELECT * FROM ft WHERE ft MATCH $q } $res } finish_test - Index: test/fts3expr5.test ================================================================== --- test/fts3expr5.test +++ test/fts3expr5.test @@ -62,6 +62,5 @@ do_test 2.2 { list [catch { test_fts3expr {"123" AND ( )} } msg] $msg } {1 {Error parsing expression}} finish_test - Index: test/fts4rename.test ================================================================== --- test/fts4rename.test +++ test/fts4rename.test @@ -39,6 +39,5 @@ ROLLBACK; DROP TABLE t1; } {0 {}} finish_test - Index: test/index6.test ================================================================== --- test/index6.test +++ test/index6.test @@ -157,11 +157,11 @@ execsql { EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE a=5; } } {/.* TABLE t2 USING INDEX t2a1 .*/} -ifcapable stat4||stat3 { +ifcapable stat4 { execsql ANALYZE do_test index6-2.3stat4 { execsql { EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE a IS NOT NULL; @@ -436,6 +436,5 @@ do_execsql_test index6-14.2 { SELECT * FROM t0 WHERE CASE c0 WHEN 0 THEN 0 ELSE 1 END; } {{} row} finish_test - Index: test/index7.test ================================================================== --- test/index7.test +++ test/index7.test @@ -201,11 +201,11 @@ execsql { EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE a=5; } } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/} -ifcapable stat4||stat3 { +ifcapable stat4 { do_test index7-2.3stat4 { execsql { EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE a IS NOT NULL; } Index: test/json104.test ================================================================== --- test/json104.test +++ test/json104.test @@ -151,7 +151,5 @@ SELECT json_extract(x, '$."d"') FROM obj; } {4} finish_test - - Index: test/like.test ================================================================== --- test/like.test +++ test/like.test @@ -1112,6 +1112,5 @@ do_execsql_test 16.2 { SELECT * FROM t1 WHERE a LIKE ' 1-'; } {{ 1-}} finish_test - Index: test/mallocA.test ================================================================== --- test/mallocA.test +++ test/mallocA.test @@ -94,28 +94,10 @@ do_faultsim_test 6.2 -faults oom* -body { execsql { SELECT rowid FROM t1 WHERE a='abc' AND b<'y' } } -test { faultsim_test_result [list 0 {1 2}] } -ifcapable stat3 { - do_test 6.3-prep { - execsql { - PRAGMA writable_schema = 1; - CREATE TABLE sqlite_stat4 AS - SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample) AS sample - FROM sqlite_stat3; - } - } {} - do_faultsim_test 6.3 -faults oom* -body { - execsql { - ANALYZE sqlite_master; - SELECT rowid FROM t1 WHERE a='abc' AND b<'y'; - } - } -test { - faultsim_test_result [list 0 {1 2}] - } -} do_execsql_test 7.0 { PRAGMA cache_size = 5; } do_faultsim_test 7 -faults oom-trans* -prep { Index: test/minmax4.test ================================================================== --- test/minmax4.test +++ test/minmax4.test @@ -199,6 +199,5 @@ do_execsql_test 5.1 { SELECT MIN(a) FROM t1 WHERE a=123; } {123} finish_test - Index: test/skipscan1.test ================================================================== --- test/skipscan1.test +++ test/skipscan1.test @@ -232,11 +232,10 @@ } {/.*COVERING INDEX t5i1 .*/} do_execsql_test skipscan1-5.2 { ANALYZE; DELETE FROM sqlite_stat1; DROP TABLE IF EXISTS sqlite_stat4; - DROP TABLE IF EXISTS sqlite_stat3; INSERT INTO sqlite_stat1 VALUES('t5','t5i1','2702931 3 2 2 2 2'); INSERT INTO sqlite_stat1 VALUES('t5','t5i2','2702931 686 2 2 2'); ANALYZE sqlite_master; } {} db cache flush Index: test/tempdb2.test ================================================================== --- test/tempdb2.test +++ test/tempdb2.test @@ -95,6 +95,5 @@ do_execsql_test 2.2 { SELECT b FROM t1 WHERE a = 10001; } "[int2str 1001][int2str 1001][int2str 1001]" finish_test - Index: test/tkt-cbd054fa6b.test ================================================================== --- test/tkt-cbd054fa6b.test +++ test/tkt-cbd054fa6b.test @@ -14,11 +14,11 @@ # set testdir [file dirname $argv0] source $testdir/tester.tcl -ifcapable !stat4&&!stat3 { +ifcapable !stat4 { finish_test return } proc s {blob} { @@ -53,23 +53,16 @@ SELECT count(*) FROM t1; } } {10} do_test tkt-cbd05-1.2 { db eval { ANALYZE; } - ifcapable stat4 { - db eval { - PRAGMA writable_schema = 1; - CREATE VIEW vvv AS - SELECT tbl,idx,neq,nlt,ndlt,test_extract(sample,0) AS sample - FROM sqlite_stat4; - PRAGMA writable_schema = 0; - } - } else { - db eval { - CREATE VIEW vvv AS - SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat3; - } + db eval { + PRAGMA writable_schema = 1; + CREATE VIEW vvv AS + SELECT tbl,idx,neq,nlt,ndlt,test_extract(sample,0) AS sample + FROM sqlite_stat4; + PRAGMA writable_schema = 0; } } {} do_test tkt-cbd05-1.3 { execsql { SELECT tbl,idx,group_concat(s(sample),' ') Index: test/triggerC.test ================================================================== --- test/triggerC.test +++ test/triggerC.test @@ -1070,6 +1070,5 @@ INSERT INTO xyz VALUES('hello', 2, 3); } {1 {datatype mismatch}} finish_test - Index: test/walvfs.test ================================================================== --- test/walvfs.test +++ test/walvfs.test @@ -424,6 +424,5 @@ db close db2 close tvfs delete finish_test - Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -1537,6 +1537,5 @@ INSERT INTO t1 VALUES(4, 'four', 'iii') ON CONFLICT(c) DO UPDATE SET b=NULL } {1 {corrupt database}} finish_test - Index: test/where9.test ================================================================== --- test/where9.test +++ test/where9.test @@ -785,11 +785,11 @@ OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {1 {no query solution}} set solution_possible 0 -ifcapable stat4||stat3 { +ifcapable stat4 { if {[permutation] != "no_optimization"} { set solution_possible 1 } } if $solution_possible { # When STAT3 is enabled, the "b NOT NULL" terms get translated # into b>NULL, which can be satified by the index t1b. It is a very @@ -858,15 +858,10 @@ CREATE INDEX t5yg ON t5(y, g); CREATE TABLE t6(a, b, c, e, d, f, g, x, y); INSERT INTO t6 SELECT * FROM t5; ANALYZE t5; } - ifcapable stat3 { - sqlite3 db2 test.db - db2 eval { DROP TABLE IF EXISTS sqlite_stat3 } - db2 close - } } {} do_test where9-7.1.1 { count_steps { SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a; } DELETED test/wild001.test Index: test/wild001.test ================================================================== --- test/wild001.test +++ /dev/null @@ -1,311 +0,0 @@ -# 2013-07-01 -# -# The author disclaims copyright to this source code. In place of -# a legal notice, here is a blessing: -# -# May you do good and not evil. -# May you find forgiveness for yourself and forgive others. -# May you share freely, never taking more than you give. -# -#*********************************************************************** -# -# This is a test case from content taken "from the wild". In this -# particular instance, the query was provided with permission by -# Elan Feingold on 2013-06-27. His message on the SQLite mailing list -# on that date reads: -# -#------------------------------------------------------------------------------ -# > Can you send (1) the schema (2) the query that is giving problems, and (3) -# > the content of the sqlite_stat1 table after you have run ANALYZE? If you -# > can combine all of the above into a script, that would be great! -# > -# > If you send (1..3) above and you give us written permission to include the -# > query in our test suite, that would be off-the-chain terrific. -# -# Please find items 1..3 in this file: http://www.plexapp.com/elan/sqlite_bug.txt -# -# You have our permission to include the query in your test suite. -# -# Thanks for an amazing product. -#----------------------------------------------------------------------------- -# -# This test case merely creates the schema and populates SQLITE_STAT1 and -# SQLITE_STAT3 then runs an EXPLAIN QUERY PLAN to ensure that the right plan -# is discovered. This test case may need to be adjusted for future revisions -# of the query planner manage to select a better query plan. The query plan -# shown here is known to be very fast with the original data. -# -# This test should work the same with and without SQLITE_ENABLE_STAT3 -# -############################################################################### - -set testdir [file dirname $argv0] -source $testdir/tester.tcl - -ifcapable !stat3 { - finish_test - return -} - -do_execsql_test wild001.01 { - CREATE TABLE "items" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "secid" integer, "parent_id" integer, "metadata_type" integer, "guid" varchar(255), "media_item_count" integer, "title" varchar(255), "title_sort" varchar(255) COLLATE NOCASE, "original_title" varchar(255), "studio" varchar(255), "rating" float, "rating_count" integer, "tagline" varchar(255), "summary" text, "trivia" text, "quotes" text, "content_rating" varchar(255), "content_rating_age" integer, "index" integer, "absolute_index" integer, "duration" integer, "user_thumb_url" varchar(255), "user_art_url" varchar(255), "user_banner_url" varchar(255), "user_music_url" varchar(255), "user_fields" varchar(255), "tags_genre" varchar(255), "tags_collection" varchar(255), "tags_director" varchar(255), "tags_writer" varchar(255), "tags_star" varchar(255), "originally_available_at" datetime, "available_at" datetime, "expires_at" datetime, "refreshed_at" datetime, "year" integer, "added_at" datetime, "created_at" datetime, "updated_at" datetime, "deleted_at" datetime, "tags_country" varchar(255), "extra_data" varchar(255), "hash" varchar(255)); - CREATE INDEX "i_secid" ON "items" ("secid" ); - CREATE INDEX "i_parent_id" ON "items" ("parent_id" ); - CREATE INDEX "i_created_at" ON "items" ("created_at" ); - CREATE INDEX "i_index" ON "items" ("index" ); - CREATE INDEX "i_title" ON "items" ("title" ); - CREATE INDEX "i_title_sort" ON "items" ("title_sort" ); - CREATE INDEX "i_guid" ON "items" ("guid" ); - CREATE INDEX "i_metadata_type" ON "items" ("metadata_type" ); - CREATE INDEX "i_deleted_at" ON "items" ("deleted_at" ); - CREATE INDEX "i_secid_ex1" ON "items" ("secid", "metadata_type", "added_at" ); - CREATE INDEX "i_hash" ON "items" ("hash" ); - CREATE TABLE "settings" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "account_id" integer, "guid" varchar(255), "rating" float, "view_offset" integer, "view_count" integer, "last_viewed_at" datetime, "created_at" datetime, "updated_at" datetime); - CREATE INDEX "s_account_id" ON "settings" ("account_id" ); - CREATE INDEX "s_guid" ON "settings" ("guid" ); - ANALYZE; - INSERT INTO sqlite_stat1 VALUES('settings','s_guid','4740 1'); - INSERT INTO sqlite_stat1 VALUES('settings','s_account_id','4740 4740'); - INSERT INTO sqlite_stat1 VALUES('items','i_hash','27316 2'); - INSERT INTO sqlite_stat1 VALUES('items','i_secid_ex1','27316 6829 4553 3'); - INSERT INTO sqlite_stat1 VALUES('items','i_deleted_at','27316 27316'); - INSERT INTO sqlite_stat1 VALUES('items','i_metadata_type','27316 6829'); - INSERT INTO sqlite_stat1 VALUES('items','i_guid','27316 2'); - INSERT INTO sqlite_stat1 VALUES('items','i_title_sort','27316 2'); - INSERT INTO sqlite_stat1 VALUES('items','i_title','27316 2'); - INSERT INTO sqlite_stat1 VALUES('items','i_index','27316 144'); - INSERT INTO sqlite_stat1 VALUES('items','i_created_at','27316 2'); - INSERT INTO sqlite_stat1 VALUES('items','i_parent_id','27316 15'); - INSERT INTO sqlite_stat1 VALUES('items','i_secid','27316 6829'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,150,150,'com.plexapp.agents.thetvdb://153021/2/9?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,198,198,'com.plexapp.agents.thetvdb://194031/1/10?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,526,526,'com.plexapp.agents.thetvdb://71256/12/92?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,923,923,'com.plexapp.agents.thetvdb://71256/15/16?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1008,1008,'com.plexapp.agents.thetvdb://71256/15/93?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1053,1053,'com.plexapp.agents.thetvdb://71256/16/21?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1068,1068,'com.plexapp.agents.thetvdb://71256/16/35?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1235,1235,'com.plexapp.agents.thetvdb://71256/17/44?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1255,1255,'com.plexapp.agents.thetvdb://71256/17/62?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1573,1573,'com.plexapp.agents.thetvdb://71663/20/9?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,1580,1580,'com.plexapp.agents.thetvdb://71663/21/16?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2000,2000,'com.plexapp.agents.thetvdb://73141/9/8?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2107,2107,'com.plexapp.agents.thetvdb://73244/6/17?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2256,2256,'com.plexapp.agents.thetvdb://74845/4/7?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2408,2408,'com.plexapp.agents.thetvdb://75978/2/21?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2634,2634,'com.plexapp.agents.thetvdb://79126/1/1?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,2962,2962,'com.plexapp.agents.thetvdb://79274/3/94?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,3160,3160,'com.plexapp.agents.thetvdb://79274/5/129?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,3161,3161,'com.plexapp.agents.thetvdb://79274/5/12?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,3688,3688,'com.plexapp.agents.thetvdb://79274/8/62?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,3714,3714,'com.plexapp.agents.thetvdb://79274/8/86?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,4002,4002,'com.plexapp.agents.thetvdb://79590/13/17?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,4215,4215,'com.plexapp.agents.thetvdb://80727/3/6?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_guid',1,4381,4381,'com.plexapp.agents.thetvdb://83462/3/24?lang=en'); - INSERT INTO sqlite_stat3 VALUES('settings','s_account_id',4740,0,0,1); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,1879,1879,'1113f632ccd52ec8b8d7ca3d6d56da4701e48018'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,2721,2721,'1936154b97bb5567163edaebc2806830ae419ccf'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,3035,3035,'1c122331d4b7bfa0dc2c003ab5fb4f7152b9987a'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,3393,3393,'1f81bdbc9acc3321dc592b1a109ca075731b549a'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,6071,6070,'393cf7713efb4519c7a3d1d5403f0d945d15a16a'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,7462,7461,'4677dd37011f8bd9ae7fbbdd3af6dcd8a5b4ab2d'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,8435,8434,'4ffa339485334e81a5e12e03a63b6508d76401cf'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,8716,8714,'52a093852e6599dd5004857b7ff5b5b82c7cdb25'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,9107,9104,'561183e39f866d97ec728e9ff16ac4ad01466111'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,10942,10939,'66e99b72e29610f49499ae09ee04a376210d1f08'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,12143,12139,'71f0602427e173dc2c551535f73fdb6885fe4302'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,14962,14958,'8ca8e4dfba696019830c19ab8a32c7ece9d8534b'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,15179,15174,'8ebf1a5cf33f8ada1fc5853ac06ac4d7e074f825'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,15375,15370,'908bc211bebdf21c79d2d2b54ebaa442ac1f5cae'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,18215,18210,'ab29e4e18ec5a14fef95aa713d69e31c045a22c1'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,18615,18610,'ae84c008cc0c338bf4f28d798a88575746452f6d'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,18649,18644,'aec7c901353e115aa5307e94018ba7507bec3a45'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,19517,19512,'b75025fbf2e9c504e3c1197ff1b69250402a31f8'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,21251,21245,'c7d32f0e3a8f3a0a3dbd00833833d2ccee62f0fd'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,23616,23610,'dd5ff61479a9bd4100de802515d9dcf72d46f07a'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,24287,24280,'e3db00034301b7555419d4ef6f64769298d5845e'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,24949,24942,'ea336abd197ecd7013854a25a4f4eb9dea7927c6'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',1,25574,25567,'f018ea5182ec3f32768ca1c3cefbf3ad160ec20b'); - INSERT INTO sqlite_stat3 VALUES('items','i_hash',2,26139,26132,'f53709a8d81c12cb0f4f8d58004a25dd063de67c'); - INSERT INTO sqlite_stat3 VALUES('items','i_secid_ex1',25167,0,0,2); - INSERT INTO sqlite_stat3 VALUES('items','i_secid_ex1',736,25167,1,3); - INSERT INTO sqlite_stat3 VALUES('items','i_secid_ex1',15,25903,2,4); - INSERT INTO sqlite_stat3 VALUES('items','i_secid_ex1',1398,25918,3,5); - INSERT INTO sqlite_stat3 VALUES('items','i_deleted_at',27316,0,0,NULL); - INSERT INTO sqlite_stat3 VALUES('items','i_metadata_type',2149,0,0,1); - INSERT INTO sqlite_stat3 VALUES('items','i_metadata_type',411,2149,1,2); - INSERT INTO sqlite_stat3 VALUES('items','i_metadata_type',1440,2560,2,3); - INSERT INTO sqlite_stat3 VALUES('items','i_metadata_type',23316,4000,3,4); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,215,215,'com.plexapp.agents.imdb://tt0065702?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,711,711,'com.plexapp.agents.imdb://tt0198781?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,987,986,'com.plexapp.agents.imdb://tt0454876?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1004,1002,'com.plexapp.agents.imdb://tt0464154?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1056,1053,'com.plexapp.agents.imdb://tt0499549?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1120,1116,'com.plexapp.agents.imdb://tt0903624?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1250,1245,'com.plexapp.agents.imdb://tt1268799?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1270,1264,'com.plexapp.agents.imdb://tt1320261?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',2,1376,1369,'com.plexapp.agents.imdb://tt1772341?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,3035,3027,'com.plexapp.agents.thetvdb://153021/3/14?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,6071,6063,'com.plexapp.agents.thetvdb://71173/1/18?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,6342,6334,'com.plexapp.agents.thetvdb://71256/13/4?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,9107,9099,'com.plexapp.agents.thetvdb://72389/2/19?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,11740,11732,'com.plexapp.agents.thetvdb://73893/2/13?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,12143,12135,'com.plexapp.agents.thetvdb://73976/4/23?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,15179,15171,'com.plexapp.agents.thetvdb://75897/16/12?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,17408,17400,'com.plexapp.agents.thetvdb://76808/2/16?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,17984,17976,'com.plexapp.agents.thetvdb://77068/1/16?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,18215,18207,'com.plexapp.agents.thetvdb://77259/1/1?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,21251,21243,'com.plexapp.agents.thetvdb://78957/8/2?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,24287,24279,'com.plexapp.agents.thetvdb://80337/5/8?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,25513,25505,'com.plexapp.agents.thetvdb://82226/6?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,25548,25540,'com.plexapp.agents.thetvdb://82339/2/10?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_guid',1,26770,26762,'com.plexapp.agents.thetvdb://86901/1/3?lang=en'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1524,0,0,''); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',2,3034,1391,'Attack of the Giant Squid'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',51,4742,2895,'Brad Sherwood'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',11,4912,2996,'Brian Williams'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',39,5847,3857,'Chip Esten'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1,6071,4015,'Chuck Versus the DeLorean'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',12,7625,5436,'Denny Siegel'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',30,8924,6618,'Episode 1'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',29,9015,6629,'Episode 2'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',32,9082,6643,'Episode 3'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',28,9135,6654,'Episode 4'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',26,9183,6665,'Episode 5'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',27,9229,6677,'Episode 6'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',22,9266,6688,'Episode 7'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',20,9298,6699,'Episode 8'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',55,11750,8817,'Greg Proops'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1,12143,9120,'Hardware Jungle'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',33,14712,11435,'Kathy Greenwood'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',3,15179,11840,'Last Call'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1,18215,14601,'Nature or Nurture?'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',12,18241,14623,'Neil DeGrasse Tyson'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',68,19918,16144,'Pilot'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',7,21251,17298,'Reza Aslan'); - INSERT INTO sqlite_stat3 VALUES('items','i_title_sort',1,24287,20035,'Technoviking'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',1524,0,0,''); - INSERT INTO sqlite_stat3 VALUES('items','i_title',1,3035,1429,'Anderson Can''t Dance'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',51,4782,2991,'Brad Sherwood'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',11,4936,3079,'Brian Williams'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',39,5694,3783,'Chip Esten'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',1,6071,4100,'Clive Warren'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',12,7144,5078,'Denny Siegel'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',30,8249,6097,'Episode 1'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',29,8340,6108,'Episode 2'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',32,8407,6122,'Episode 3'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',28,8460,6133,'Episode 4'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',26,8508,6144,'Episode 5'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',27,8554,6156,'Episode 6'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',22,8591,6167,'Episode 7'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',20,8623,6178,'Episode 8'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',1,9107,6537,'Fat Albert and the Cosby Kids'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',55,10539,7843,'Greg Proops'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',1,12143,9276,'Iron Age Remains'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',33,13118,10143,'Kathy Greenwood'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',1,15179,11972,'Mink'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',68,17411,14035,'Pilot'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',2,18214,14727,'Reflections'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',4,21250,17481,'The Apartment'); - INSERT INTO sqlite_stat3 VALUES('items','i_title',1,24287,20283,'The Simpsons Already Did It'); - INSERT INTO sqlite_stat3 VALUES('items','i_index',4315,95,2,1); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1553,4410,3,2); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1485,5963,4,3); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1414,7448,5,4); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1367,8862,6,5); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1328,10229,7,6); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1161,11557,8,7); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1108,12718,9,8); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1033,13826,10,9); - INSERT INTO sqlite_stat3 VALUES('items','i_index',1014,14859,11,10); - INSERT INTO sqlite_stat3 VALUES('items','i_index',929,15873,12,11); - INSERT INTO sqlite_stat3 VALUES('items','i_index',906,16802,13,12); - INSERT INTO sqlite_stat3 VALUES('items','i_index',844,17708,14,13); - INSERT INTO sqlite_stat3 VALUES('items','i_index',690,18552,15,14); - INSERT INTO sqlite_stat3 VALUES('items','i_index',655,19242,16,15); - INSERT INTO sqlite_stat3 VALUES('items','i_index',625,19897,17,16); - INSERT INTO sqlite_stat3 VALUES('items','i_index',579,20522,18,17); - INSERT INTO sqlite_stat3 VALUES('items','i_index',555,21101,19,18); - INSERT INTO sqlite_stat3 VALUES('items','i_index',526,21656,20,19); - INSERT INTO sqlite_stat3 VALUES('items','i_index',501,22182,21,20); - INSERT INTO sqlite_stat3 VALUES('items','i_index',459,22683,22,21); - INSERT INTO sqlite_stat3 VALUES('items','i_index',439,23142,23,22); - INSERT INTO sqlite_stat3 VALUES('items','i_index',315,23581,24,23); - INSERT INTO sqlite_stat3 VALUES('items','i_index',192,24177,26,25); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1851,0,0,NULL); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',373,1857,2,'2011-10-22 14:54:39'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',595,2230,3,'2011-10-22 14:54:41'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',337,2825,4,'2011-10-22 14:54:43'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',361,3378,8,'2011-10-22 14:54:54'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',160,3739,9,'2011-10-22 14:54:56'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',315,4000,11,'2011-10-22 14:54:59'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',321,4334,13,'2011-10-22 14:55:02'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1292,4723,16,'2011-10-22 14:55:06'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',161,6015,17,'2011-10-22 14:55:07'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1,9107,2677,'2012-09-04 18:07:50'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',313,9717,3270,'2012-10-18 16:50:21'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',450,10030,3271,'2012-10-18 16:50:22'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',389,10668,3275,'2012-10-18 16:50:26'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',796,11057,3276,'2012-10-18 16:51:06'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',161,12041,3280,'2012-10-19 19:52:37'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',135,13281,4186,'2013-02-19 00:56:10'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1063,13416,4187,'2013-02-19 00:56:11'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',797,14479,4188,'2013-02-19 00:56:13'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',147,15276,4189,'2013-02-19 00:56:15'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',346,15423,4190,'2013-02-19 00:56:16'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1,18215,6436,'2013-05-05 14:09:54'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',2,21251,8122,'2013-05-24 15:25:45'); - INSERT INTO sqlite_stat3 VALUES('items','i_created_at',1,24287,11116,'2013-05-26 14:17:39'); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',2560,0,0,NULL); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',18,3022,31,2350); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',10,6068,285,8150); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',158,6346,315,8949); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',34,9094,562,18831); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',20,12139,794,22838); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',134,14033,886,24739); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',159,14167,887,24740); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,14326,888,24741); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,14487,889,24742); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',124,14648,890,24743); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',157,14772,891,24744); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',126,15043,894,24747); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',40,15169,895,24748); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,15243,898,24753); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',138,15404,899,24754); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',160,15542,900,24755); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,15702,901,24756); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',161,15863,902,24757); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',124,16024,903,24758); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',155,16148,904,24759); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',26,18208,1043,29704); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',2,21251,1282,32952); - INSERT INTO sqlite_stat3 VALUES('items','i_parent_id',13,24279,1583,36068); - INSERT INTO sqlite_stat3 VALUES('items','i_secid',25167,0,0,2); - INSERT INTO sqlite_stat3 VALUES('items','i_secid',736,25167,1,3); - INSERT INTO sqlite_stat3 VALUES('items','i_secid',15,25903,2,4); - INSERT INTO sqlite_stat3 VALUES('items','i_secid',1398,25918,3,5); - ANALYZE sqlite_master; - - explain query plan - select items.title - from items - join items as child on child.parent_id=items.id - join items as grandchild on grandchild.parent_id=child.id - join settings - on settings.guid=grandchild.guid - and settings.account_id=1 - where items.metadata_type=2 - and items.secid=2 - and settings.last_viewed_at is not null - group by items.id - order by settings.last_viewed_at desc - limit 10; -} [list \ - 0 0 3 {SEARCH TABLE settings USING INDEX s_account_id (account_id=?)} \ - 0 1 2 {SEARCH TABLE items AS grandchild USING INDEX i_guid (guid=?)} \ - 0 2 1 {SEARCH TABLE items AS child USING INTEGER PRIMARY KEY (rowid=?)} \ - 0 3 0 {SEARCH TABLE items USING INTEGER PRIMARY KEY (rowid=?)} \ - 0 0 0 {USE TEMP B-TREE FOR GROUP BY} \ - 0 0 0 {USE TEMP B-TREE FOR ORDER BY}] - - -finish_test Index: test/window6.test ================================================================== --- test/window6.test +++ test/window6.test @@ -366,6 +366,5 @@ ten fifteen.ten thirty fifteen.ten.thirty } finish_test - Index: test/window9.test ================================================================== --- test/window9.test +++ test/window9.test @@ -172,6 +172,5 @@ } finish_test - Index: test/without_rowid1.test ================================================================== --- test/without_rowid1.test +++ test/without_rowid1.test @@ -100,15 +100,10 @@ # do_execsql_test without_rowid1-1.50 { ANALYZE; SELECT * FROM sqlite_stat1 ORDER BY idx; } {t1 t1 {4 2 1} t1 t1bd {4 2 2}} -ifcapable stat3 { - do_execsql_test without_rowid1-1.51 { - SELECT DISTINCT tbl, idx FROM sqlite_stat3 ORDER BY idx; - } {t1 t1 t1 t1bd} -} ifcapable stat4 { do_execsql_test without_rowid1-1.52 { SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx; } {t1 t1 t1 t1bd} }