From dd6e1f193e4efdebc226defbaa217be157b81de5 Mon Sep 17 00:00:00 2001 From: dan Date: Sat, 10 Aug 2013 19:08:30 +0000 Subject: [PATCH] Add the rowid field to the end of sample records stored in the sqlite_stat4 table. FossilOrigin-Name: 3a5e8ab7ddbe1d943b35ef329fe4e5a1bfdb0d9d --- manifest | 24 ++--- manifest.uuid | 2 +- src/analyze.c | 209 ++++++++++++++++++++++++--------------- src/vdbemem.c | 9 +- src/where.c | 4 +- test/analyze3.test | 6 +- test/analyze5.test | 11 ++- test/analyze9.test | 62 ++++++++---- test/tkt-cbd054fa6b.test | 4 +- 9 files changed, 209 insertions(+), 122 deletions(-) diff --git a/manifest b/manifest index 004ced59a3..b5c7ade0f5 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\sa\scouple\sof\stypos\sin\sa\scomment\sin\sanalyze.c.\sNo\scode\schanges. -D 2013-08-09T19:04:07.214 +C Add\sthe\srowid\sfield\sto\sthe\send\sof\ssample\srecords\sstored\sin\sthe\ssqlite_stat4\stable. +D 2013-08-10T19:08:30.794 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 5e41da95d92656a5004b03d3576e8b226858a28e F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -157,7 +157,7 @@ F sqlite.pc.in 42b7bf0d02e08b9e77734a47798d1a55a9e0716b F sqlite3.1 6be1ad09113570e1fc8dcaff84c9b0b337db5ffc F sqlite3.pc.in ae6f59a76e862f5c561eb32a380228a02afc3cad F src/alter.c 2af0330bb1b601af7a7789bf7229675fd772a083 -F src/analyze.c aba0749f8900cef07bb05324b624fd4eb19bf7c6 +F src/analyze.c 178c33a77551d4a0d72831c71c9b93908f7c6a20 F src/attach.c 1816f5a9eea8d2010fc2b22b44f0f63eb3a62704 F src/auth.c 523da7fb4979469955d822ff9298352d6b31de34 F src/backup.c 43b348822db3e4cef48b2ae5a445fbeb6c73a165 @@ -283,14 +283,14 @@ F src/vdbeInt.h e9b7c6b165a31a4715c5aa97223d20d265515231 F src/vdbeapi.c 4d13580bd058b39623e8fcfc233b7df4b8191e8b F src/vdbeaux.c a6ea36a9dc714e1128a0173249a0532ddcab0489 F src/vdbeblob.c 5dc79627775bd9a9b494dd956e26297946417d69 -F src/vdbemem.c 4aff02f52e95cad546b47c15a7145a9940a61b67 +F src/vdbemem.c b16ba7b3d1ead79c081d1f79e157e6b2efd13ca5 F src/vdbesort.c 3937e06b2a0e354500e17dc206ef4c35770a5017 F src/vdbetrace.c e7ec40e1999ff3c6414424365d5941178966dcbc F src/vtab.c 2e8b489db47e20ae36cd247932dc671c9ded0624 F src/wal.c 7dc3966ef98b74422267e7e6e46e07ff6c6eb1b4 F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4 F src/walker.c 4fa43583d0a84b48f93b1e88f11adf2065be4e73 -F src/where.c 0e058c33d0f4e2616ecd0996c04f3f1b7e3f7afa +F src/where.c adf476146fcd78af6ebc7dea50853bcbb14ba2b6 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/aggnested.test 45c0201e28045ad38a530b5a144b73cd4aa2cfd6 @@ -302,13 +302,13 @@ F test/alter3.test 49c9d9fba2b8fcdce2dedeca97bbf1f369cc548d F test/alter4.test 8e93bf7a7e6919b14b0c9a6c1e4908bcf21b0165 F test/altermalloc.test e81ac9657ed25c6c5bb09bebfa5a047cd8e4acfc F test/analyze.test 4d08a739c5ec28db93e0465e3b5a468befdf145f -F test/analyze3.test 4532e5475d2aa68d752627548bdcaf70aff51010 +F test/analyze3.test ea4cba3277eb89d16dfeada7259ea437e7b00f3b F test/analyze4.test eff2df19b8dd84529966420f29ea52edc6b56213 -F test/analyze5.test e3eece09761c935ec0b85dc4ed70dbf6cac1ed77 +F test/analyze5.test 96ac783a56142bbbedb58a7c1eebd1808b49cfae F test/analyze6.test 3c01e084309706a1033f850330ea24f6f7846297 F test/analyze7.test c0af22c5e0140e2e4ac556a21c2b6fff58229c98 F test/analyze8.test 8d1f76ff1e47c4093bb7be3971ba08fa56dc470d -F test/analyze9.test 1ed4e7d95d8e1e1923766281b20870d61730450c +F test/analyze9.test 3e1bd0209354bb987832fba580c754cf77dc6ba3 F test/async.test 1d0e056ba1bb9729283a0f22718d3a25e82c277b F test/async2.test c0a9bd20816d7d6a2ceca7b8c03d3d69c28ffb8b F test/async3.test d73a062002376d7edc1fe3edff493edbec1fc2f7 @@ -865,7 +865,7 @@ F test/tkt-b72787b1.test a95e8cdad0b98af1853ac7f0afd4ab27b77bf5f3 F test/tkt-bd484a090c.test 60460bf946f79a79712b71f202eda501ca99b898 F test/tkt-bdc6bbbb38.test fc38bb09bdd440e3513a1f5f98fc60a075182d7d F test/tkt-c48d99d690.test ba61977d62ab612fc515b3c488a6fbd6464a2447 -F test/tkt-cbd054fa6b.test 2fd674fda943346a31cd020883f70bf6c037e98c +F test/tkt-cbd054fa6b.test 6595eac9c561b0aa46c1e4f4bae3f876acf38ce3 F test/tkt-d11f09d36e.test d999b548fef885d1d1afa49a0e8544ecf436869d F test/tkt-d635236375.test 9d37e988b47d87505bc9445be0ca447002df5d09 F test/tkt-d82e3f3721.test bcc0dfba658d15bab30fd4a9320c9e35d214ce30 @@ -1106,7 +1106,7 @@ F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh fbc018d67fd7395f440c28f33ef0f94420226381 F tool/wherecosttest.c f407dc4c79786982a475261866a161cd007947ae F tool/win/sqlite.vsix 97894c2790eda7b5bce3cc79cb2a8ec2fde9b3ac -P 4d97809d6b29809f12d753043bda1976bdb1bd3b -R b0844c4d270fc066470bde75f744fcb8 +P 5bcccb93df98f5dfee0ea4d797b07fe0257258a9 +R 96e0779963a0fd7aab46f6c8c7627c84 U dan -Z 863ef3ffe27da5a538c7139e648347ba +Z ee31c385e2b881dc7d9f322f79a33eaa diff --git a/manifest.uuid b/manifest.uuid index 0857e7e7bf..84bca67c98 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -5bcccb93df98f5dfee0ea4d797b07fe0257258a9 \ No newline at end of file +3a5e8ab7ddbe1d943b35ef329fe4e5a1bfdb0d9d \ No newline at end of file diff --git a/src/analyze.c b/src/analyze.c index 7ce3dceb81..89d7dee958 100644 --- a/src/analyze.c +++ b/src/analyze.c @@ -237,6 +237,16 @@ static void openStatTable( ** Three SQL functions - stat4_init(), stat4_push(), and stat4_pop() - ** share an instance of the following structure to hold their state ** information. +** +** bHaveP, bHaveNonP: +** The stat4_push() user-defined-function may be invoked multiple +** times with index keys that are identical except for the rowid +** field. An argument is passed to stat4_push() to indicate if this +** is the case or not. +** +** bHaveP is set to true if a periodic sample corresponding to the +** current index key has already been added. bHaveNonP is true if a +** non-periodic sample has been added. */ typedef struct Stat4Accum Stat4Accum; struct Stat4Accum { @@ -245,8 +255,10 @@ struct Stat4Accum { int iMin; /* Index of entry with minimum nEq and hash */ int mxSample; /* Maximum number of samples to accumulate */ int nSample; /* Current number of samples */ - int nCol; /* Number of columns in the index */ + int nCol; /* Number of columns in the index including rowid */ u32 iPrn; /* Pseudo-random number used for sampling */ + int bHaveP; + int bHaveNonP; struct Stat4Sample { i64 iRowid; /* Rowid in main table of the key */ tRowcnt *anEq; /* sqlite_stat4.nEq */ @@ -285,7 +297,7 @@ static void stat4Init( nRow = (tRowcnt)sqlite3_value_int64(argv[0]); nCol = sqlite3_value_int(argv[1]); mxSample = sqlite3_value_int(argv[2]); - assert( nCol>0 ); + assert( nCol>1 ); /* >1 because it includes the rowid column */ /* Allocate the space required for the Stat4Accum object */ n = sizeof(*p) + (sizeof(p->a[0]) + 3*sizeof(tRowcnt)*nCol)*mxSample; @@ -351,51 +363,65 @@ static void stat4Push( ){ Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]); i64 rowid = sqlite3_value_int64(argv[1]); + int bNewKey = sqlite3_value_int(argv[2]); struct Stat4Sample *pSample; - u32 h; + u32 h; /* Hash value for this key */ int iMin = p->iMin; int i; u8 isPSample = 0; u8 doInsert = 0; - sqlite3_value **aEq = &argv[2]; - sqlite3_value **aLt = &argv[2+p->nCol]; - sqlite3_value **aDLt = &argv[2+p->nCol+p->nCol]; + sqlite3_value **aEq = &argv[3]; + sqlite3_value **aLt = &argv[3+p->nCol]; + sqlite3_value **aDLt = &argv[3+p->nCol+p->nCol]; - i64 nEq = sqlite3_value_int64(aEq[p->nCol-1]); i64 nLt = sqlite3_value_int64(aLt[p->nCol-1]); UNUSED_PARAMETER(context); UNUSED_PARAMETER(argc); - assert( p->nCol>0 ); - assert( argc==(2 + 3*p->nCol) ); - - /* Figure out if this sample will be used. There are three reasons a - ** sample may be used: - ** - ** 1. It may be a periodic sample. In this case set isPSample to true - ** as well. Or, - ** - ** 2. Less than p->mxSample samples have been collected so far, or - ** - ** 3. It is more desirable than some other non-periodic sample that has - ** already been collected. Samples are compared based on the values - ** in the anEq array, starting from last (right-most index column) - ** to first (left-most index column). If all elements of the anEq - ** array are equal, samples are compared by hash value. - ** - ** For both the contents of the anEq[] array and the hash value, - ** larger values are considered more desirable. - */ + assert( argc==(3 + 3*p->nCol) ); + assert( p->bHaveNonP==0 || p->bHaveP==0 ); + + if( bNewKey ){ + p->bHaveP = 0; + p->bHaveNonP = 0; + } h = p->iPrn = p->iPrn*1103515245 + 12345; - if( (nLt/p->nPSample)!=((nEq+nLt)/p->nPSample) ){ + + /* Check if this should be a periodic sample. If this is a periodic + ** sample and there is already a non-periodic sample for this key, + ** replace it. */ + if( (nLt/p->nPSample) != (nLt+1)/p->nPSample ){ doInsert = isPSample = 1; + if( p->bHaveNonP ){ + p->nSample--; + p->bHaveNonP = 0; + p->bHaveP = 1; + assert( p->nSamplemxSample ); + assert( p->a[p->nSample].isPSample==0 ); + } + + /* Or, if this is not a periodic sample, and there is already at least one + ** periodic sample, return early. */ + }else if( p->bHaveP ){ + /* no-op */ + + /* If there is already a non-periodic sample for the key, but this one + ** has a higher hash score, replace the existing sample. */ + }else if( p->bHaveNonP ){ + if( p->a[p->nSample-1].iHashnSample--; + doInsert = 1; + } + + /* Finally, check if this should be added as a non-periodic sample. */ }else if( p->nSamplemxSample ){ doInsert = 1; + p->bHaveNonP = 1; }else{ tRowcnt *aMinEq = p->a[iMin].anEq; - for(i=p->nCol-1; i>=0; i--){ + for(i=p->nCol-2; i>=0; i--){ i64 nEq = sqlite3_value_int64(aEq[i]); if( nEqaMinEq[i] ){ @@ -406,8 +432,9 @@ static void stat4Push( if( i<0 && h>p->a[iMin].iHash ){ doInsert = 1; } + p->bHaveNonP = doInsert; } - if( !doInsert ) return; + if( doInsert==0 ) return; /* Fill in the new Stat4Sample object. */ if( p->nSample==p->mxSample ){ @@ -579,8 +606,9 @@ static void analyzeOneTable( int regStat4 = iMem++; /* Register to hold Stat4Accum object */ int regRowid = iMem++; /* Rowid argument passed to stat4_push() */ + int regKeychng = iMem++; /* True if key has changed */ - pParse->nMem = MAX(pParse->nMem, regRowid); + pParse->nMem = MAX(pParse->nMem, regKeychng); v = sqlite3GetVdbe(pParse); if( v==0 || NEVER(pTab==0) ){ return; @@ -627,7 +655,7 @@ static void analyzeOneTable( if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0; VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName)); nCol = pIdx->nColumn; - aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*nCol); + aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*(nCol+1)); if( aChngAddr==0 ) continue; pKey = sqlite3IndexKeyinfo(pParse, pIdx); @@ -640,12 +668,14 @@ static void analyzeOneTable( ** entry. The code below is for an index with 2 columns. The actual ** VM code generated may be for any number of columns. ** - ** One cursor is opened for each column in the index (nCol). All cursors - ** scan concurrently the index from start to end. All variables used in - ** the pseudo-code are initialized to zero. + ** One cursor is opened for each column in the index and one for the + ** rowid column (nCol+1 in total). All cursors scan concurrently the + ** index from start to end. All variables used in the pseudo-code are + ** initialized to zero. ** ** Rewind csr(0) ** Rewind csr(1) + ** Rewind csr(2) ** ** next_0: ** regPrev(0) = csr(0)[0] @@ -662,18 +692,26 @@ static void analyzeOneTable( ** regDLte(1) += 1 ** regLt(1) += regEq(1) ** regEq(1) = 0 - ** regRowid = csr(1)[rowid] // innermost cursor only ** do { ** regEq(1) += 1 - ** regCnt += 1 // innermost cursor only ** Next csr(1) ** }while ( csr(1)[0..1] == regPrev(0..1) ) ** - ** stat4_push(regRowid, regEq, regLt, regDLte); + ** regKeychng = 1 + ** next_row: + ** regRowid = csr(2)[rowid] + ** regEq(2) = 1 + ** regLt(2) = regCnt + ** regCnt += 1 + ** regDLte(2) = regCnt + ** stat4_push(regRowid, regKeychng, regEq, regLt, regDLte); + ** regKeychng = 0 + ** Next csr(2) + ** if( eof( csr(2) ) ) goto endOfScan ** - ** if( eof( csr(1) ) ) goto endOfScan - ** if( csr(1)[0] != regPrev(0) ) goto next_0 - ** goto next_1 + ** if( csr(2)[0] != regPrev(0) ) goto next_0 + ** if( csr(2)[1] != regPrev(1) ) goto next_1 + ** goto next_row ** ** endOfScan: ** // done! @@ -683,32 +721,31 @@ static void analyzeOneTable( ** of the corresponding length. As required to calculate the contents ** of the sqlite_stat1 entry. ** - ** Currently, the last memory cell allocated (that with the largest - ** integer identifier) is regStat4. Immediately following regStat4 + ** At this point, the last memory cell allocated (that with the largest + ** integer identifier) is regKeychng. Immediately following regKeychng ** we allocate the following: ** ** regEq - nCol registers - ** regLt - nCol registers - ** regDLte - nCol registers - ** regPrev - nCol registers + ** regLt - nCol+1 registers + ** regDLte - nCol+1 registers + ** regPrev - nCol+1 registers ** - ** The regRowid, regEq, regLt and regDLte registers must be positioned in - ** that order immediately following regStat4 so that they can be passed - ** to the stat4_push() function. + ** can be passed to the stat4_push() function. ** ** All of the above are initialized to contain integer value 0. */ - regEq = regRowid+1; /* First in array of nEq value registers */ - regLt = regEq+nCol; /* First in array of nLt value registers */ - regDLte = regLt+nCol; /* First in array of nDLt value registers */ - regPrev = regDLte+nCol; /* First in array of prev. value registers */ + regEq = regKeychng+1; /* First in array of nEq value registers */ + regLt = regEq+nCol+1; /* First in array of nLt value registers */ + regDLte = regLt+nCol+1; /* First in array of nDLt value registers */ + regPrev = regDLte+nCol+1; /* First in array of prev. value registers */ pParse->nMem = MAX(pParse->nMem, regPrev+nCol); - /* Open a read-only cursor for each column of the index. */ + /* Open a read-only cursor for each column of the index. And one for + ** the rowid column. A total of (nCol+1) cursors. */ assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) ); iIdxCur = iTab; - pParse->nTab = MAX(pParse->nTab, iTab+nCol); - for(i=0; inTab = MAX(pParse->nTab, iTab+nCol+1); + for(i=0; i<(nCol+1); i++){ int iMode = (i==0 ? P4_KEYINFO_HANDOFF : P4_KEYINFO); sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur+i, pIdx->tnum, iDb); sqlite3VdbeChangeP4(v, -1, (char*)pKey, iMode); @@ -719,11 +756,11 @@ static void analyzeOneTable( /* Invoke the stat4_init() function. The arguments are: ** ** * the number of rows in the index, - ** * the number of columns in the index, + ** * the number of columns in the index including the rowid, ** * the recommended number of samples for the stat4 table. */ sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat4+1); - sqlite3VdbeAddOp2(v, OP_Integer, nCol, regStat4+2); + sqlite3VdbeAddOp2(v, OP_Integer, nCol+1, regStat4+2); sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_STAT4_SAMPLES, regStat4+3); sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4+1, regStat4); sqlite3VdbeChangeP4(v, -1, (char*)&stat4InitFuncdef, P4_FUNCDEF); @@ -740,7 +777,7 @@ static void analyzeOneTable( /* Rewind all cursors open on the index. If the table is entry, this ** will cause control to jump to address endOfScan immediately. */ endOfScan = sqlite3VdbeMakeLabel(v); - for(i=0; iazColl[i]); - sqlite3VdbeAddOp3(v, OP_Column, iIdxCur+nCol-1, i, regCol); + sqlite3VdbeAddOp3(v, OP_Column, iIdxCur+nCol, i, regCol); sqlite3VdbeAddOp3(v, OP_Ne, regCol, aChngAddr[i], regPrev+i); sqlite3VdbeChangeP4(v, -1, pColl, P4_COLLSEQ); sqlite3VdbeChangeP5(v, SQLITE_NULLEQ); } - sqlite3VdbeAddOp2(v, OP_Goto, 0, aChngAddr[nCol-1]); + sqlite3VdbeAddOp2(v, OP_Goto, 0, aChngAddr[nCol]); sqlite3DbFree(db, aChngAddr); sqlite3VdbeResolveLabel(v, endOfScan); @@ -827,17 +878,17 @@ static void analyzeOneTable( regLoop = regStat4+1; sqlite3VdbeAddOp2(v, OP_Integer, -1, regLoop); shortJump = sqlite3VdbeAddOp2(v, OP_AddImm, regLoop, 1); - sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4, regTemp); + sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4, regEq+nCol); sqlite3VdbeChangeP4(v, -1, (char*)&stat4GetFuncdef, P4_FUNCDEF); sqlite3VdbeChangeP5(v, 2); - sqlite3VdbeAddOp1(v, OP_IsNull, regTemp); + sqlite3VdbeAddOp1(v, OP_IsNull, regEq+nCol); - sqlite3VdbeAddOp3(v, OP_NotExists, iTabCur, shortJump, regTemp); + sqlite3VdbeAddOp3(v, OP_NotExists, iTabCur, shortJump, regEq+nCol); for(i=0; iaiColumn[i]; - sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regPrev+i); + sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regEq+i); } - sqlite3VdbeAddOp3(v, OP_MakeRecord, regPrev, nCol, regSample); + sqlite3VdbeAddOp3(v, OP_MakeRecord, regEq, nCol+1, regSample); sqlite3VdbeChangeP4(v, -1, pIdx->zColAff, 0); sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regNumEq); diff --git a/src/vdbemem.c b/src/vdbemem.c index de0361e64c..8eef88ed3b 100644 --- a/src/vdbemem.c +++ b/src/vdbemem.c @@ -1182,16 +1182,18 @@ static sqlite3_value *valueNewStat4(void *pCtx){ Index *pIdx = p->pIdx; /* Index being probed */ int nByte; /* Bytes of space to allocate */ int i; /* Counter variable */ + int nCol = pIdx->nColumn+1; /* Number of index columns including rowid */ - nByte = sizeof(Mem) * pIdx->nColumn + sizeof(UnpackedRecord); + nByte = sizeof(Mem) * nCol + sizeof(UnpackedRecord); pRec = (UnpackedRecord*)sqlite3DbMallocZero(db, nByte); if( pRec ){ pRec->pKeyInfo = sqlite3IndexKeyinfo(p->pParse, pIdx); if( pRec->pKeyInfo ){ + assert( pRec->pKeyInfo->nField+1==nCol ); pRec->pKeyInfo->enc = ENC(db); pRec->flags = UNPACKED_PREFIX_MATCH; pRec->aMem = (Mem *)&pRec[1]; - for(i=0; inColumn; i++){ + for(i=0; iaMem[i].flags = MEM_Null; pRec->aMem[i].type = SQLITE_NULL; pRec->aMem[i].db = db; @@ -1307,9 +1309,10 @@ int sqlite3Stat4ProbeSetValue( void sqlite3Stat4ProbeFree(UnpackedRecord *pRec){ if( pRec ){ int i; + int nCol = pRec->pKeyInfo->nField+1; Mem *aMem = pRec->aMem; sqlite3 *db = aMem[0].db; - for(i=0; ipKeyInfo->nField; i++){ + for(i=0; ipKeyInfo); diff --git a/src/where.c b/src/where.c index cde57a1687..a33605954a 100644 --- a/src/where.c +++ b/src/where.c @@ -2422,7 +2422,7 @@ static void whereKeyStats( int res; /* Result of comparison operation */ assert( pIdx->nSample>0 ); - assert( pRec->nField>0 && iColnColumn ); + assert( pRec->nField>0 && iCol<=pIdx->nColumn ); do{ iTest = (iMin+i)/2; res = sqlite3VdbeRecordCompare(aSample[iTest].n, aSample[iTest].p, pRec); @@ -2684,6 +2684,8 @@ static int whereEqualScanEst( return SQLITE_NOTFOUND; } + /* This is an optimization only. The call to sqlite3Stat4ProbeSetValue() + ** below would return the same value. */ if( nEq>p->nColumn ){ *pnRow = 1; return SQLITE_OK; diff --git a/test/analyze3.test b/test/analyze3.test index df9e548828..394d321b97 100644 --- a/test/analyze3.test +++ b/test/analyze3.test @@ -95,7 +95,10 @@ do_test analyze3-1.1.1 { COMMIT; ANALYZE; } -} {} + execsql { + SELECT count(*)>0 FROM sqlite_stat4; + } +} {1} do_eqp_test analyze3-1.1.2 { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 @@ -312,7 +315,6 @@ do_test analyze3-3.1 { execsql COMMIT execsql ANALYZE } {} - do_test analyze3-3.2.1 { set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy] sqlite3_expired $S diff --git a/test/analyze5.test b/test/analyze5.test index 9972695abf..048e6a5ef7 100644 --- a/test/analyze5.test +++ b/test/analyze5.test @@ -37,6 +37,8 @@ proc alpha {blob} { } db func alpha alpha +db func lindex lindex + unset -nocomplain i t u v w x y z do_test analyze5-1.0 { db eval {CREATE TABLE t1(t,u,v TEXT COLLATE nocase,w,x,y,z)} @@ -64,19 +66,20 @@ do_test analyze5-1.0 { 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; - SELECT alpha(sample) FROM sqlite_stat4 WHERE idx='t1u' ORDER BY nlt; + 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 { db eval { - SELECT DISTINCT lower(alpha(sample)) FROM sqlite_stat4 WHERE idx='t1v' - ORDER BY 1 + SELECT DISTINCT lower(lindex(test_decode(sample), 0)) + FROM sqlite_stat4 WHERE idx='t1v' ORDER BY 1 } } {alpha bravo charlie delta} do_test analyze5-1.2 { db eval {SELECT idx, count(*) FROM sqlite_stat4 GROUP BY 1 ORDER BY 1} -} {t1t 4 t1u 4 t1v 4 t1w 4 t1x 4 t1y 2 t1z 4} +} {t1t 8 t1u 8 t1v 8 t1w 8 t1x 8 t1y 9 t1z 8} # Verify that range queries generate the correct row count estimates # diff --git a/test/analyze9.test b/test/analyze9.test index 1e0171c03b..0323b860ba 100644 --- a/test/analyze9.test +++ b/test/analyze9.test @@ -34,12 +34,14 @@ proc s {blob} { } db function s s -do_test 1.0 { - execsql { CREATE TABLE t1(a TEXT, b TEXT); } - for {set i 0} {$i < 5} {incr i} { - execsql {INSERT INTO t1 VALUES ('('||($i%10)||')', '('||($i%7)||')')} - } - execsql { CREATE INDEX i1 ON t1(a, b) } +do_execsql_test 1.0 { + CREATE TABLE t1(a TEXT, b TEXT); + INSERT INTO t1 VALUES('(0)', '(0)'); + INSERT INTO t1 VALUES('(1)', '(1)'); + INSERT INTO t1 VALUES('(2)', '(2)'); + INSERT INTO t1 VALUES('(3)', '(3)'); + INSERT INTO t1 VALUES('(4)', '(4)'); + CREATE INDEX i1 ON t1(a, b); } {} @@ -50,21 +52,21 @@ do_execsql_test 1.1 { do_execsql_test 1.3 { SELECT tbl,idx,nEq,nLt,nDLt,test_decode(sample) FROM sqlite_stat4; } { - t1 i1 {1 1} {0 0} {0 0} {(0) (0)} - t1 i1 {1 1} {1 1} {1 1} {(1) (1)} - t1 i1 {1 1} {2 2} {2 2} {(2) (2)} - t1 i1 {1 1} {3 3} {3 3} {(3) (3)} - t1 i1 {1 1} {4 4} {4 4} {(4) (4)} + t1 i1 {1 1 1} {0 0 0} {0 0 0} {(0) (0) 1} + t1 i1 {1 1 1} {1 1 1} {1 1 1} {(1) (1) 2} + t1 i1 {1 1 1} {2 2 2} {2 2 2} {(2) (2) 3} + t1 i1 {1 1 1} {3 3 3} {3 3 3} {(3) (3) 4} + t1 i1 {1 1 1} {4 4 4} {4 4 4} {(4) (4) 5} } do_execsql_test 1.2 { SELECT tbl,idx,nEq,nLt,nDLt,s(sample) FROM sqlite_stat4; } { - t1 i1 {1 1} {0 0} {0 0} ...(0)(0) - t1 i1 {1 1} {1 1} {1 1} ...(1)(1) - t1 i1 {1 1} {2 2} {2 2} ...(2)(2) - t1 i1 {1 1} {3 3} {3 3} ...(3)(3) - t1 i1 {1 1} {4 4} {4 4} ...(4)(4) + t1 i1 {1 1 1} {0 0 0} {0 0 0} ....(0)(0) + t1 i1 {1 1 1} {1 1 1} {1 1 1} ....(1)(1). + t1 i1 {1 1 1} {2 2 2} {2 2 2} ....(2)(2). + t1 i1 {1 1 1} {3 3 3} {3 3 3} ....(3)(3). + t1 i1 {1 1 1} {4 4 4} {4 4 4} ....(4)(4). } @@ -80,8 +82,8 @@ do_execsql_test 2.1 { ANALYZE; SELECT test_decode(sample) FROM sqlite_stat4; } { - {22.0 NULL x'656667'} - {{some text} 14 NULL} + {22.0 NULL x'656667' 2} + {{some text} 14 NULL 1} } #------------------------------------------------------------------------- @@ -117,6 +119,30 @@ do_execsql_test 3.3.2 { SELECT lindex(nEq, 0) FROM sqlite_stat4; } [lrange [string repeat "10 " 100] 0 23] +#------------------------------------------------------------------------- +# +do_execsql_test 3.4 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + INSERT INTO t1 VALUES(1, 1, 'one-a'); + INSERT INTO t1 VALUES(11, 1, 'one-b'); + INSERT INTO t1 VALUES(21, 1, 'one-c'); + INSERT INTO t1 VALUES(31, 1, 'one-d'); + INSERT INTO t1 VALUES(41, 1, 'one-e'); + INSERT INTO t1 VALUES(51, 1, 'one-f'); + INSERT INTO t1 VALUES(61, 1, 'one-g'); + INSERT INTO t1 VALUES(71, 1, 'one-h'); + INSERT INTO t1 VALUES(81, 1, 'one-i'); + INSERT INTO t1 VALUES(91, 1, 'one-j'); + INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1; + INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; + INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; + INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; + INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; + CREATE INDEX t1b ON t1(b); + ANALYZE; + SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60; +} {three-d three-e three-f} finish_test diff --git a/test/tkt-cbd054fa6b.test b/test/tkt-cbd054fa6b.test index 8f16964447..92b367e700 100644 --- a/test/tkt-cbd054fa6b.test +++ b/test/tkt-cbd054fa6b.test @@ -65,7 +65,7 @@ do_test tkt-cbd05-1.3 { WHERE idx = 't1_x' GROUP BY tbl,idx } -} {t1 t1_x {.. ..A ..B ..C ..D ..E ..F ..G ..H ..I}} +} {t1 t1_x {... ...A. ...B. ...C. ...D. ...E. ...F. ...G. ...H. ...I.}} do_test tkt-cbd05-2.1 { db eval { @@ -97,6 +97,6 @@ do_test tkt-cbd05-2.3 { WHERE idx = 't1_x' GROUP BY tbl,idx } -} {t1 t1_x {.. ..A ..B ..C ..D ..E ..F ..G ..H ..I}} +} {t1 t1_x {... ...A. ...B. ...C. ...D. ...E. ...F. ...G. ...H. ...I.}} finish_test -- 2.47.2