Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -369,40 +369,78 @@ /* Return a pointer to the allocated object to the caller */ sqlite3_result_blob(context, p, sizeof(p), sqlite3_free); } static const FuncDef statInitFuncdef = { 1+IsStat34, /* nArg */ - SQLITE_UTF8, /* iPrefEnc */ - 0, /* flags */ + SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ statInit, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ "stat_init", /* zName */ 0, /* pHash */ 0 /* pDestructor */ }; + +#ifdef SQLITE_ENABLE_STAT4 +/* +** pNew and pOld are both candidate non-periodic samples selected for +** the same column (pNew->iCol==pOld->iCol). Ignoring this column and +** considering only any trailing columns and the sample hash value, this +** function returns true if sample pNew is to be preferred over pOld. +** In other words, if we assume that the cardinalities of the selected +** column for pNew and pOld are equal, is pNew to be preferred over pOld. +** +** This function assumes that for each argument sample, the contents of +** the anEq[] array from pSample->anEq[pSample->iCol+1] onwards are valid. +*/ +static int sampleIsBetterPost( + Stat4Accum *pAccum, + Stat4Sample *pNew, + Stat4Sample *pOld +){ + int nCol = pAccum->nCol; + int i; + assert( pNew->iCol==pOld->iCol ); + for(i=pNew->iCol+1; ianEq[i]>pOld->anEq[i] ) return 1; + if( pNew->anEq[i]anEq[i] ) return 0; + } + if( pNew->iHash>pOld->iHash ) return 1; + return 0; +} +#endif #ifdef SQLITE_ENABLE_STAT3_OR_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. */ -static int sampleIsBetter(Stat4Sample *pNew, Stat4Sample *pOld){ +static int sampleIsBetter( + Stat4Accum *pAccum, + Stat4Sample *pNew, + Stat4Sample *pOld +){ tRowcnt nEqNew = pNew->anEq[pNew->iCol]; tRowcnt nEqOld = pOld->anEq[pOld->iCol]; assert( pOld->isPSample==0 && pNew->isPSample==0 ); assert( IsStat4 || (pNew->iCol==0 && pOld->iCol==0) ); - if( (nEqNew>nEqOld) - || (nEqNew==nEqOld && pNew->iColiCol) - || (nEqNew==nEqOld && pNew->iCol==pOld->iCol && pNew->iHash>pOld->iHash) - ){ - return 1; + 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 object (*pFrom) into (*pTo). */ @@ -421,15 +459,14 @@ ** remove the least desirable sample from p->a[] to make room. */ static void sampleInsert(Stat4Accum *p, Stat4Sample *pNew, int nEqZero){ Stat4Sample *pSample; int i; - i64 iSeq; - int iPos; assert( IsStat4 || nEqZero==0 ); +#ifdef SQLITE_ENABLE_STAT4 if( pNew->isPSample==0 ){ Stat4Sample *pUpgrade = 0; assert( pNew->anEq[pNew->iCol]>0 ); /* This sample is being added because the prefix that ends in column @@ -439,12 +476,13 @@ ** existing sample that shares this prefix. */ for(i=p->nSample-1; i>=0; i--){ Stat4Sample *pOld = &p->a[i]; if( pOld->anEq[pNew->iCol]==0 ){ if( pOld->isPSample ) return; - assert( sampleIsBetter(pNew, pOld) ); - if( pUpgrade==0 || sampleIsBetter(pOld, pUpgrade) ){ + assert( pOld->iCol>pNew->iCol ); + assert( sampleIsBetter(p, pNew, pOld) ); + if( pUpgrade==0 || sampleIsBetter(p, pOld, pUpgrade) ){ pUpgrade = pOld; } } } if( pUpgrade ){ @@ -451,10 +489,11 @@ 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; @@ -466,40 +505,34 @@ pSample->anDLt = anDLt; pSample->anLt = anLt; p->nSample = p->mxSample-1; } - /* Figure out where in the a[] array the new sample should be inserted. */ - iSeq = pNew->anLt[p->nCol-1]; - for(iPos=p->nSample; iPos>0; iPos--){ - if( iSeq>p->a[iPos-1].anLt[p->nCol-1] ) break; - } + /* 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[iPos]; - if( iPos!=p->nSample ){ - Stat4Sample *pEnd = &p->a[p->nSample]; - tRowcnt *anEq = pEnd->anEq; - tRowcnt *anLt = pEnd->anLt; - tRowcnt *anDLt = pEnd->anDLt; - memmove(&p->a[iPos], &p->a[iPos+1], (p->nSample-iPos)*sizeof(p->a[0])); - pSample->anEq = anEq; - pSample->anDLt = anDLt; - pSample->anLt = anLt; - } - p->nSample++; + 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 if( p->nSample>=p->mxSample ){ int iMin = -1; for(i=0; imxSample; i++){ if( p->a[i].isPSample ) continue; - if( iMin<0 || sampleIsBetter(&p->a[iMin], &p->a[i]) ){ + if( iMin<0 || sampleIsBetter(p, &p->a[iMin], &p->a[i]) ){ iMin = i; } } assert( iMin>=0 ); p->iMin = iMin; @@ -519,13 +552,12 @@ /* Check if any samples from the aBest[] array should be pushed ** into IndexSample.a[] at this point. */ for(i=(p->nCol-2); i>=iChng; i--){ Stat4Sample *pBest = &p->aBest[i]; - if( p->nSamplemxSample - || sampleIsBetter(pBest, &p->a[p->iMin]) - ){ + pBest->anEq[i] = p->current.anEq[i]; + if( p->nSamplemxSample || sampleIsBetter(p, pBest, &p->a[p->iMin]) ){ sampleInsert(p, pBest, i); } } /* Update the anEq[] fields of any samples already collected. */ @@ -548,11 +580,13 @@ 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->current, &p->a[p->iMin]) ){ + if( p->nSamplemxSample + || sampleIsBetter(p, &p->current, &p->a[p->iMin]) + ){ sampleInsert(p, &p->current, 0); } } #endif } @@ -582,12 +616,11 @@ assert( p->nCol>1 ); /* Includes rowid field */ assert( iChngnCol ); if( p->nRow==0 ){ - /* anEq[0] is only zero for the very first call to this function. Do - ** appropriate initialization */ + /* This is the first call to this function. Do initialization. */ for(i=0; inCol; i++) p->current.anEq[i] = 1; }else{ /* Second and subsequent calls get processed here */ samplePushPrevious(p, iChng); @@ -623,21 +656,20 @@ } /* Update the aBest[] array. */ for(i=0; i<(p->nCol-1); i++){ p->current.iCol = i; - if( i>=iChng || sampleIsBetter(&p->current, &p->aBest[i]) ){ + if( i>=iChng || sampleIsBetterPost(p, &p->current, &p->aBest[i]) ){ sampleCopy(p, &p->aBest[i], &p->current); } } } #endif } static const FuncDef statPushFuncdef = { 2+IsStat34, /* nArg */ - SQLITE_UTF8, /* iPrefEnc */ - 0, /* flags */ + SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ statPush, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ @@ -768,12 +800,11 @@ } #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ } static const FuncDef statGetFuncdef = { 1+IsStat34, /* nArg */ - SQLITE_UTF8, /* iPrefEnc */ - 0, /* flags */ + SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ statGet, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ Index: src/attach.c ================================================================== --- src/attach.c +++ src/attach.c @@ -377,12 +377,11 @@ ** DETACH pDbname */ void sqlite3Detach(Parse *pParse, Expr *pDbname){ static const FuncDef detach_func = { 1, /* nArg */ - SQLITE_UTF8, /* iPrefEnc */ - 0, /* flags */ + SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ detachFunc, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ @@ -399,12 +398,11 @@ ** ATTACH p AS pDbname KEY pKey */ void sqlite3Attach(Parse *pParse, Expr *p, Expr *pDbname, Expr *pKey){ static const FuncDef attach_func = { 3, /* nArg */ - SQLITE_UTF8, /* iPrefEnc */ - 0, /* flags */ + SQLITE_UTF8, /* funcFlags */ 0, /* pUserData */ 0, /* pNext */ attachFunc, /* xFunc */ 0, /* xStep */ 0, /* xFinalize */ Index: src/callback.c ================================================================== --- src/callback.c +++ src/callback.c @@ -268,13 +268,13 @@ }else{ match = 1; } /* Bonus points if the text encoding matches */ - if( enc==p->iPrefEnc ){ + if( enc==(p->funcFlags & SQLITE_FUNC_ENCMASK) ){ match += 2; /* Exact encoding match */ - }else if( (enc & p->iPrefEnc & 2)!=0 ){ + }else if( (enc & p->funcFlags & 2)!=0 ){ match += 1; /* Both are UTF16, but with different byte orders */ } return match; } @@ -404,11 +404,11 @@ */ if( createFlag && bestScorezName = (char *)&pBest[1]; pBest->nArg = (u16)nArg; - pBest->iPrefEnc = enc; + pBest->funcFlags = enc; memcpy(pBest->zName, zName, nName); pBest->zName[nName] = 0; sqlite3FuncDefInsert(&db->aFunc, pBest); } Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -534,11 +534,11 @@ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); /* Do FK processing. This call checks that any FK constraints that ** refer to this table (i.e. constraints attached to other tables) ** are not violated by deleting this row. */ - sqlite3FkCheck(pParse, pTab, iOld, 0); + sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0); } /* Delete the index and table entries. Skip this step if pTab is really ** a view (in which case the only effect of the DELETE statement is to ** fire the INSTEAD OF triggers). */ @@ -551,11 +551,11 @@ } /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key ** to the row just deleted. */ - sqlite3FkActions(pParse, pTab, 0, iOld); + sqlite3FkActions(pParse, pTab, 0, iOld, 0, 0); /* Invoke AFTER DELETE trigger programs. */ sqlite3CodeRowTrigger(pParse, pTrigger, TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel ); Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -2632,11 +2632,11 @@ /* Attempt a direct implementation of the built-in COALESCE() and ** IFNULL() functions. This avoids unnecessary evalation of ** arguments past the first non-NULL argument. */ - if( pDef->flags & SQLITE_FUNC_COALESCE ){ + if( pDef->funcFlags & SQLITE_FUNC_COALESCE ){ int endCoalesce = sqlite3VdbeMakeLabel(v); assert( nFarg>=2 ); sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target); for(i=1; iflags & (SQLITE_FUNC_LENGTH|SQLITE_FUNC_TYPEOF))!=0 ){ + if( (pDef->funcFlags & (SQLITE_FUNC_LENGTH|SQLITE_FUNC_TYPEOF))!=0 ){ u8 exprOp; assert( nFarg==1 ); assert( pFarg->a[0].pExpr!=0 ); exprOp = pFarg->a[0].pExpr->op; if( exprOp==TK_COLUMN || exprOp==TK_AGG_COLUMN ){ assert( SQLITE_FUNC_LENGTH==OPFLAG_LENGTHARG ); assert( SQLITE_FUNC_TYPEOF==OPFLAG_TYPEOFARG ); - testcase( pDef->flags==SQLITE_FUNC_LENGTH ); - pFarg->a[0].pExpr->op2 = pDef->flags; + testcase( (pDef->funcFlags&~SQLITE_FUNC_ENCMASK) + ==SQLITE_FUNC_LENGTH ); + pFarg->a[0].pExpr->op2 = pDef->funcFlags&~SQLITE_FUNC_ENCMASK; } } sqlite3ExprCachePush(pParse); /* Ticket 2ea2425d34be */ sqlite3ExprCodeExprList(pParse, pFarg, r1, 1); @@ -2698,15 +2699,15 @@ #endif for(i=0; ia[i].pExpr) ){ constMask |= (1<flags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){ + if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){ pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr); } } - if( pDef->flags & SQLITE_FUNC_NEEDCOLL ){ + if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){ if( !pColl ) pColl = db->pDfltColl; sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ); } sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target, (char*)pDef, P4_FUNCDEF); Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -680,10 +680,74 @@ sqlite3VdbeResolveLabel(v, iSkip); } } } + +/* +** The second argument points to an FKey object representing a foreign key +** for which pTab is the child table. An UPDATE statement against pTab +** is currently being processed. For each column of the table that is +** actually updated, the corresponding element in the aChange[] array +** is zero or greater (if a column is unmodified the corresponding element +** is set to -1). If the rowid column is modified by the UPDATE statement +** the bChngRowid argument is non-zero. +** +** This function returns true if any of the columns that are part of the +** child key for FK constraint *p are modified. +*/ +static int fkChildIsModified( + Table *pTab, /* Table being updated */ + FKey *p, /* Foreign key for which pTab is the child */ + int *aChange, /* Array indicating modified columns */ + int bChngRowid /* True if rowid is modified by this update */ +){ + int i; + for(i=0; inCol; i++){ + int iChildKey = p->aCol[i].iFrom; + if( aChange[iChildKey]>=0 ) return 1; + if( iChildKey==pTab->iPKey && bChngRowid ) return 1; + } + return 0; +} + +/* +** The second argument points to an FKey object representing a foreign key +** for which pTab is the parent table. An UPDATE statement against pTab +** is currently being processed. For each column of the table that is +** actually updated, the corresponding element in the aChange[] array +** is zero or greater (if a column is unmodified the corresponding element +** is set to -1). If the rowid column is modified by the UPDATE statement +** the bChngRowid argument is non-zero. +** +** This function returns true if any of the columns that are part of the +** parent key for FK constraint *p are modified. +*/ +static int fkParentIsModified( + Table *pTab, + FKey *p, + int *aChange, + int bChngRowid +){ + int i; + for(i=0; inCol; i++){ + char *zKey = p->aCol[i].zCol; + int iKey; + for(iKey=0; iKeynCol; iKey++){ + if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){ + Column *pCol = &pTab->aCol[iKey]; + if( zKey ){ + if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1; + }else if( pCol->colFlags & COLFLAG_PRIMKEY ){ + return 1; + } + } + } + } + return 0; +} + /* ** This function is called when inserting, deleting or updating a row of ** table pTab to generate VDBE code to perform foreign key constraint ** processing for the operation. ** @@ -704,11 +768,13 @@ */ void sqlite3FkCheck( Parse *pParse, /* Parse context */ Table *pTab, /* Row is being deleted from this table */ int regOld, /* Previous row data is stored here */ - int regNew /* New row data is stored here */ + int regNew, /* New row data is stored here */ + int *aChange, /* Array indicating UPDATEd columns (or 0) */ + int bChngRowid /* True if rowid is UPDATEd */ ){ sqlite3 *db = pParse->db; /* Database handle */ FKey *pFKey; /* Used to iterate through FKs */ int iDb; /* Index of database containing pTab */ const char *zDb; /* Name of database containing pTab */ @@ -731,10 +797,17 @@ int *aiFree = 0; int *aiCol; int iCol; int i; int isIgnore = 0; + + if( aChange + && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0 + && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 + ){ + continue; + } /* Find the parent table of this foreign key. Also find a unique index ** on the parent key columns in the parent table. If either of these ** schema items cannot be located, set an error in pParse and return ** early. */ @@ -813,10 +886,14 @@ /* Loop through all the foreign key constraints that refer to this table */ for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ Index *pIdx = 0; /* Foreign key index for pFKey */ SrcList *pSrc; int *aiCol = 0; + + if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){ + continue; + } if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) && !pParse->pToplevel && !pParse->isMultiWrite ){ assert( regOld==0 && regNew!=0 ); @@ -886,10 +963,11 @@ } } } return mask; } + /* ** This function is called before generating code to update or delete a ** row contained in table pTab. If the operation is a DELETE, then ** parameter aChange is passed a NULL value. For an UPDATE, aChange points @@ -916,36 +994,20 @@ ** foreign key constraint. */ return (sqlite3FkReferences(pTab) || pTab->pFKey); }else{ /* This is an UPDATE. Foreign key processing is only required if the ** operation modifies one or more child or parent key columns. */ - int i; FKey *p; /* Check if any child key columns are being modified. */ for(p=pTab->pFKey; p; p=p->pNextFrom){ - for(i=0; inCol; i++){ - int iChildKey = p->aCol[i].iFrom; - if( aChange[iChildKey]>=0 ) return 1; - if( iChildKey==pTab->iPKey && chngRowid ) return 1; - } + if( fkChildIsModified(pTab, p, aChange, chngRowid) ) return 1; } /* Check if any parent key columns are being modified. */ for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ - for(i=0; inCol; i++){ - char *zKey = p->aCol[i].zCol; - int iKey; - for(iKey=0; iKeynCol; iKey++){ - Column *pCol = &pTab->aCol[iKey]; - if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey) - : (pCol->colFlags & COLFLAG_PRIMKEY)!=0) ){ - if( aChange[iKey]>=0 ) return 1; - if( iKey==pTab->iPKey && chngRowid ) return 1; - } - } - } + if( fkParentIsModified(pTab, p, aChange, chngRowid) ) return 1; } } } return 0; } @@ -1167,22 +1229,26 @@ */ void sqlite3FkActions( Parse *pParse, /* Parse context */ Table *pTab, /* Table being updated or deleted from */ ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */ - int regOld /* Address of array containing old row */ + int regOld, /* Address of array containing old row */ + int *aChange, /* Array indicating UPDATEd columns (or 0) */ + int bChngRowid /* True if rowid is UPDATEd */ ){ /* If foreign-key support is enabled, iterate through all FKs that ** refer to table pTab. If there is an action associated with the FK ** for this operation (either update or delete), invoke the associated ** trigger sub-program. */ if( pParse->db->flags&SQLITE_ForeignKeys ){ FKey *pFKey; /* Iterator variable */ for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ - Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges); - if( pAction ){ - sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0); + if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){ + Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges); + if( pAct ){ + sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0); + } } } } } Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -1691,11 +1691,11 @@ static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){ FuncDef *pDef; pDef = sqlite3FindFunction(db, zName, sqlite3Strlen30(zName), 2, SQLITE_UTF8, 0); if( ALWAYS(pDef) ){ - pDef->flags = flagVal; + pDef->funcFlags |= flagVal; } } /* ** Register the built-in LIKE and GLOB functions. The caseSensitive @@ -1735,11 +1735,11 @@ } assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); pDef = sqlite3FindFunction(db, pExpr->u.zToken, sqlite3Strlen30(pExpr->u.zToken), 2, SQLITE_UTF8, 0); - if( NEVER(pDef==0) || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){ + if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){ return 0; } /* The memcpy() statement assumes that the wildcard characters are ** the first three statements in the compareInfo structure. The @@ -1747,11 +1747,11 @@ */ memcpy(aWc, pDef->pUserData, 3); assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll ); assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne ); assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet ); - *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0; + *pIsNocase = (pDef->funcFlags & SQLITE_FUNC_CASE)==0; return 1; } /* ** All all of the FuncDef structures in the aBuiltinFunc[] array above @@ -1828,11 +1828,11 @@ #endif AGGREGATE(sum, 1, 0, 0, sumStep, sumFinalize ), AGGREGATE(total, 1, 0, 0, sumStep, totalFinalize ), AGGREGATE(avg, 1, 0, 0, sumStep, avgFinalize ), /* AGGREGATE(count, 0, 0, 0, countStep, countFinalize ), */ - {0,SQLITE_UTF8,SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0}, + {0,SQLITE_UTF8|SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0}, AGGREGATE(count, 1, 0, 0, countStep, countFinalize ), AGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize), AGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize), LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -1029,11 +1029,11 @@ { int isReplace; /* Set to true if constraints may cause a replace */ sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx, keyColumn>=0, 0, onError, endOfLoop, &isReplace ); - sqlite3FkCheck(pParse, pTab, 0, regIns); + sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); sqlite3CompleteInsertion( pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0 ); } } Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -1404,11 +1404,11 @@ ** and there are active VMs, then return SQLITE_BUSY. If a function ** is being overridden/deleted but there are no active VMs, allow the ** operation to continue but invalidate all precompiled statements. */ p = sqlite3FindFunction(db, zFunctionName, nName, nArg, (u8)enc, 0); - if( p && p->iPrefEnc==enc && p->nArg==nArg ){ + if( p && (p->funcFlags & SQLITE_FUNC_ENCMASK)==enc && p->nArg==nArg ){ if( db->nVdbeActive ){ sqlite3Error(db, SQLITE_BUSY, "unable to delete/modify user-function due to active statements"); assert( !db->mallocFailed ); return SQLITE_BUSY; @@ -1429,11 +1429,11 @@ if( pDestructor ){ pDestructor->nRef++; } p->pDestructor = pDestructor; - p->flags = 0; + p->funcFlags &= SQLITE_FUNC_ENCMASK; p->xFunc = xFunc; p->xStep = xStep; p->xFinalize = xFinal; p->pUserData = pUserData; p->nArg = (u16)nArg; Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3226,11 +3226,11 @@ assert( pTab && !pTab->pSelect && pExpr ); if( IsVirtual(pTab) ) return 0; if( pExpr->op!=TK_AGG_FUNCTION ) return 0; if( NEVER(pAggInfo->nFunc==0) ) return 0; - if( (pAggInfo->aFunc[0].pFunc->flags&SQLITE_FUNC_COUNT)==0 ) return 0; + if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0; if( pExpr->flags&EP_Distinct ) return 0; return pTab; } @@ -3823,11 +3823,11 @@ if( pF->iDistinct>=0 ){ addrNext = sqlite3VdbeMakeLabel(v); assert( nArg==1 ); codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg); } - if( pF->pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ + if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ CollSeq *pColl = 0; struct ExprList_item *pItem; int j; assert( pList!=0 ); /* pList!=0 if pF->pFunc has NEEDCOLL */ for(j=0, pItem=pList->a; !pColl && jmode==MODE_Insert ){ + azVals[i] = ""; + }else{ + azVals[i] = (char*)sqlite3_column_text(pStmt, i); + } if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){ rc = SQLITE_NOMEM; break; /* from for */ } } /* end for */ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1063,12 +1063,11 @@ ** hash table. When multiple functions have the same name, the hash table ** points to a linked list of these structures. */ struct FuncDef { i16 nArg; /* Number of arguments. -1 means unlimited */ - u8 iPrefEnc; /* Preferred text encoding (SQLITE_UTF8, 16LE, 16BE) */ - u8 flags; /* Some combination of SQLITE_FUNC_* */ + u16 funcFlags; /* Some combination of SQLITE_FUNC_* */ void *pUserData; /* User data parameter */ FuncDef *pNext; /* Next function with same name */ void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */ void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */ void (*xFinalize)(sqlite3_context*); /* Aggregate finalizer */ @@ -1100,18 +1099,20 @@ /* ** Possible values for FuncDef.flags. Note that the _LENGTH and _TYPEOF ** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG. There ** are assert() statements in the code to verify this. */ -#define SQLITE_FUNC_LIKE 0x01 /* Candidate for the LIKE optimization */ -#define SQLITE_FUNC_CASE 0x02 /* Case-sensitive LIKE-type function */ -#define SQLITE_FUNC_EPHEM 0x04 /* Ephemeral. Delete with VDBE */ -#define SQLITE_FUNC_NEEDCOLL 0x08 /* sqlite3GetFuncCollSeq() might be called */ -#define SQLITE_FUNC_COUNT 0x10 /* Built-in count(*) aggregate */ -#define SQLITE_FUNC_COALESCE 0x20 /* Built-in coalesce() or ifnull() function */ -#define SQLITE_FUNC_LENGTH 0x40 /* Built-in length() function */ -#define SQLITE_FUNC_TYPEOF 0x80 /* Built-in typeof() function */ +#define SQLITE_FUNC_ENCMASK 0x003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */ +#define SQLITE_FUNC_LIKE 0x004 /* Candidate for the LIKE optimization */ +#define SQLITE_FUNC_CASE 0x008 /* Case-sensitive LIKE-type function */ +#define SQLITE_FUNC_EPHEM 0x010 /* Ephemeral. Delete with VDBE */ +#define SQLITE_FUNC_NEEDCOLL 0x020 /* sqlite3GetFuncCollSeq() might be called */ +#define SQLITE_FUNC_LENGTH 0x040 /* Built-in length() function */ +#define SQLITE_FUNC_TYPEOF 0x080 /* Built-in typeof() function */ +#define SQLITE_FUNC_COUNT 0x100 /* Built-in count(*) aggregate */ +#define SQLITE_FUNC_COALESCE 0x200 /* Built-in coalesce() or ifnull() */ +#define SQLITE_FUNC_UNLIKELY 0x400 /* Built-in unlikely() function */ /* ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are ** used to create the initializers for the FuncDef structures. ** @@ -1135,22 +1136,22 @@ ** available as the function user-data (sqlite3_user_data()). The ** FuncDef.flags variable is set to the value passed as the flags ** parameter. */ #define FUNCTION(zName, nArg, iArg, bNC, xFunc) \ - {nArg, SQLITE_UTF8, (bNC*SQLITE_FUNC_NEEDCOLL), \ + {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \ - {nArg, SQLITE_UTF8, (bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags, \ + {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags, \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \ - {nArg, SQLITE_UTF8, bNC*SQLITE_FUNC_NEEDCOLL, \ + {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ pArg, 0, xFunc, 0, 0, #zName, 0, 0} #define LIKEFUNC(zName, nArg, arg, flags) \ - {nArg, SQLITE_UTF8, flags, (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0} + {nArg, SQLITE_UTF8|flags, (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0} #define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \ - {nArg, SQLITE_UTF8, nc*SQLITE_FUNC_NEEDCOLL, \ + {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \ SQLITE_INT_TO_PTR(arg), 0, 0, xStep,xFinal,#zName,0,0} /* ** All current savepoints are stored in a linked list starting at ** sqlite3.pSavepoint. The first element in the list is the most recently @@ -3207,22 +3208,22 @@ ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In ** this case foreign keys are parsed, but no other functionality is ** provided (enforcement of FK constraints requires the triggers sub-system). */ #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) - void sqlite3FkCheck(Parse*, Table*, int, int); + void sqlite3FkCheck(Parse*, Table*, int, int, int*, int); void sqlite3FkDropTable(Parse*, SrcList *, Table*); - void sqlite3FkActions(Parse*, Table*, ExprList*, int); + void sqlite3FkActions(Parse*, Table*, ExprList*, int, int*, int); int sqlite3FkRequired(Parse*, Table*, int*, int); u32 sqlite3FkOldmask(Parse*, Table*); FKey *sqlite3FkReferences(Table *); #else - #define sqlite3FkActions(a,b,c,d) + #define sqlite3FkActions(a,b,c,d,e,f) #define sqlite3FkCheck(a,b,c,d) #define sqlite3FkDropTable(a,b,c) - #define sqlite3FkOldmask(a,b) 0 - #define sqlite3FkRequired(a,b,c,d) 0 + #define sqlite3FkOldmask(a,b) 0 + #define sqlite3FkRequired(a,b,c,d,e,f) 0 #endif #ifndef SQLITE_OMIT_FOREIGN_KEY void sqlite3FkDelete(sqlite3 *, Table*); int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**); #else Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -486,11 +486,11 @@ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); /* Do FK constraint checks. */ if( hasFK ){ - sqlite3FkCheck(pParse, pTab, regOldRowid, 0); + sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngRowid); } /* Delete the index entries associated with the current record. */ j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); @@ -500,21 +500,21 @@ sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); } sqlite3VdbeJumpHere(v, j1); if( hasFK ){ - sqlite3FkCheck(pParse, pTab, 0, regNewRowid); + sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngRowid); } /* Insert the new index entries and the new record. */ sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0); /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to ** handle rows (possibly in other tables) that refer via a foreign key ** to the row just updated. */ if( hasFK ){ - sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); + sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngRowid); } } /* Increment the row counter */ Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -1435,11 +1435,11 @@ */ sqlite3VdbeMemMove(&ctx.s, pOut); MemSetTypeFlag(&ctx.s, MEM_Null); ctx.fErrorOrAux = 0; - if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ + if( ctx.pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ assert( pOp>aOp ); assert( pOp[-1].p4type==P4_COLLSEQ ); assert( pOp[-1].opcode==OP_CollSeq ); ctx.pColl = pOp[-1].p4.pColl; } @@ -5435,11 +5435,11 @@ ctx.s.xDel = 0; ctx.s.db = db; ctx.isError = 0; ctx.pColl = 0; ctx.skipFlag = 0; - if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ + if( ctx.pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ assert( pOp>p->aOp ); assert( pOp[-1].p4type==P4_COLLSEQ ); assert( pOp[-1].opcode==OP_CollSeq ); ctx.pColl = pOp[-1].p4.pColl; } Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -602,11 +602,11 @@ /* ** If the input FuncDef structure is ephemeral, then free it. If ** the FuncDef is not ephermal, then do nothing. */ static void freeEphemeralFunction(sqlite3 *db, FuncDef *pDef){ - if( ALWAYS(pDef) && (pDef->flags & SQLITE_FUNC_EPHEM)!=0 ){ + if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0 ){ sqlite3DbFree(db, pDef); } } static void vdbeFreeOpArray(sqlite3 *, Op *, int); Index: src/vdbemem.c ================================================================== --- src/vdbemem.c +++ src/vdbemem.c @@ -1302,10 +1302,12 @@ int iVal, /* Array element to populate */ int *pbOk /* OUT: True if value was extracted */ ){ int rc = SQLITE_OK; sqlite3_value *pVal = 0; + sqlite3 *db = pParse->db; + struct ValueNewStat4Ctx alloc; alloc.pParse = pParse; alloc.pIdx = pIdx; alloc.ppRec = ppRec; @@ -1313,11 +1315,11 @@ /* Skip over any TK_COLLATE nodes */ pExpr = sqlite3ExprSkipCollate(pExpr); if( !pExpr ){ - pVal = valueNew(pParse->db, &alloc); + pVal = valueNew(db, &alloc); if( pVal ){ sqlite3VdbeMemSetNull((Mem*)pVal); *pbOk = 1; } }else if( pExpr->op==TK_VARIABLE @@ -1325,30 +1327,29 @@ ){ Vdbe *v; int iBindVar = pExpr->iColumn; sqlite3VdbeSetVarmask(pParse->pVdbe, iBindVar); if( (v = pParse->pReprepare)!=0 ){ - pVal = valueNew(pParse->db, &alloc); + pVal = valueNew(db, &alloc); if( pVal ){ rc = sqlite3VdbeMemCopy((Mem*)pVal, &v->aVar[iBindVar-1]); if( rc==SQLITE_OK ){ - sqlite3ValueApplyAffinity(pVal, affinity, SQLITE_UTF8); + sqlite3ValueApplyAffinity(pVal, affinity, ENC(db)); } pVal->db = pParse->db; *pbOk = 1; sqlite3VdbeMemStoreType((Mem*)pVal); } }else{ *pbOk = 0; } }else{ - sqlite3 *db = pParse->db; rc = valueFromExpr(db, pExpr, ENC(db), affinity, &pVal, &alloc); *pbOk = (pVal!=0); } - assert( pVal==0 || pVal->db==pParse->db ); + assert( pVal==0 || pVal->db==db ); return rc; } /* ** Unless it is NULL, the argument must be an UnpackedRecord object returned Index: src/vtab.c ================================================================== --- src/vtab.c +++ src/vtab.c @@ -1011,11 +1011,11 @@ *pNew = *pDef; pNew->zName = (char *)&pNew[1]; memcpy(pNew->zName, pDef->zName, sqlite3Strlen30(pDef->zName)+1); pNew->xFunc = xFunc; pNew->pUserData = pArg; - pNew->flags |= SQLITE_FUNC_EPHEM; + pNew->funcFlags |= SQLITE_FUNC_EPHEM; return pNew; } /* ** Make sure virtual table pTab is contained in the pParse->apVirtualLock[] Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -2419,16 +2419,19 @@ UnpackedRecord *pRec, /* Vector of values to consider */ int roundUp, /* Round up if true. Round down if false */ tRowcnt *aStat /* OUT: stats written here */ ){ IndexSample *aSample = pIdx->aSample; - int iCol = pRec->nField-1; /* Index of required stats in anEq[] etc. */ + int iCol; /* Index of required stats in anEq[] etc. */ int iMin = 0; /* Smallest sample not yet tested */ int i = pIdx->nSample; /* Smallest sample larger than or equal to pRec */ int iTest; /* Next sample to test */ int res; /* Result of comparison operation */ + assert( pRec!=0 || pParse->db->mallocFailed ); + if( pRec==0 ) return; + iCol = pRec->nField - 1; assert( pIdx->nSample>0 ); assert( pRec->nField>0 && iColnSampleCol ); do{ iTest = (iMin+i)/2; res = sqlite3VdbeRecordCompare(aSample[iTest].n, aSample[iTest].p, pRec); @@ -2544,18 +2547,18 @@ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 Index *p = pBuilder->pNew->u.btree.pIndex; int nEq = pBuilder->pNew->u.btree.nEq; - if( nEq==pBuilder->nRecValid + if( p->nSample>0 + && nEq==pBuilder->nRecValid && nEqnSampleCol - && p->nSample && OptimizationEnabled(pParse->db, SQLITE_Stat3) ){ UnpackedRecord *pRec = pBuilder->pRec; tRowcnt a[2]; - u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity; + u8 aff; /* Variable iLower will be set to the estimate of the number of rows in ** the index that are less than the lower bound of the range query. The ** lower bound being the concatenation of $P and $L, where $P is the ** key-prefix formed by the nEq values matched against the nEq left-most @@ -2573,10 +2576,15 @@ ** of iUpper are requested of whereKeyStats() and the smaller used. */ tRowcnt iLower; tRowcnt iUpper; + if( nEq==p->nColumn ){ + aff = SQLITE_AFF_INTEGER; + }else{ + aff = p->pTable->aCol[p->aiColumn[nEq]].affinity; + } /* Determine iLower and iUpper using ($P) only. */ if( nEq==0 ){ iLower = 0; iUpper = p->aiRowEst[0]; }else{ @@ -4058,12 +4066,15 @@ /* ** Transfer content from the second pLoop into the first. */ static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){ - if( whereLoopResize(db, pTo, pFrom->nLTerm) ) return SQLITE_NOMEM; whereLoopClearUnion(db, pTo); + if( whereLoopResize(db, pTo, pFrom->nLTerm) ){ + memset(&pTo->u, 0, sizeof(pTo->u)); + return SQLITE_NOMEM; + } memcpy(pTo, pFrom, WHERE_LOOP_XFER_SZ); memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0])); if( pFrom->wsFlags & WHERE_VIRTUALTABLE ){ pFrom->u.vtab.needFree = 0; }else if( (pFrom->wsFlags & WHERE_AUTO_INDEX)!=0 ){ @@ -4173,15 +4184,15 @@ ){ /* This branch taken when p is equal or better than pTemplate in ** all of (1) dependencies (2) setup-cost, (3) run-cost, and ** (4) number of output rows. */ assert( p->rSetup==pTemplate->rSetup ); - if( p->nLTermnLTerm + if( p->prereq==pTemplate->prereq + && p->nLTermnLTerm && (p->wsFlags & WHERE_INDEXED)!=0 && (pTemplate->wsFlags & WHERE_INDEXED)!=0 && p->u.btree.pIndex==pTemplate->u.btree.pIndex - && p->prereq==pTemplate->prereq ){ /* Overwrite an existing WhereLoop with an similar one that uses ** more terms of the index */ pNext = p->pNextLoop; break; @@ -5902,11 +5913,11 @@ if( pWInfo->nLevel>=2 && pResultSet!=0 && OptimizationEnabled(db, SQLITE_OmitNoopJoin) ){ Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet); - if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy); + if( sWLB.pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, sWLB.pOrderBy); while( pWInfo->nLevel>=2 ){ WhereTerm *pTerm, *pEnd; pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop; if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break; if( (wctrlFlags & WHERE_WANT_DISTINCT)==0 Index: test/analyze9.test ================================================================== --- test/analyze9.test +++ test/analyze9.test @@ -242,11 +242,11 @@ lrange(ndlt, 0, 2), lrange(test_decode(sample), 0, 1) FROM sqlite_stat4 ORDER BY rowid DESC LIMIT 2; } { - {2 1 1 1} {295 296 296} {120 122 125} {201 4} + {2 1 1 1} {295 295 295} {120 121 124} {201 3} {5 3 1 1} {290 290 292} {119 119 121} {200 1} } do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120 do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119 @@ -554,8 +554,400 @@ } do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/} } } -finish_test +#------------------------------------------------------------------------- +# Check that affinities are taken into account when using stat4 data to +# estimate the number of rows scanned by a rowid constraint. +# +drop_all_tables +do_test 13.1 { + execsql { + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(a); + CREATE INDEX i2 ON t1(b, c); + } + for {set i 0} {$i<100} {incr i} { + if {$i %2} {set a abc} else {set a def} + execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) } + } + execsql ANALYZE +} {} +do_eqp_test 13.2.1 { + SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<20 +} {/SEARCH TABLE t1 USING INDEX i1/} +do_eqp_test 13.2.2 { + SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<20 +} {/SEARCH TABLE t1 USING INDEX i1/} +do_eqp_test 13.3.1 { + SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<20 +} {/SEARCH TABLE t1 USING INDEX i2/} +do_eqp_test 13.3.2 { + SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<20 +} {/SEARCH TABLE t1 USING INDEX i2/} + +#------------------------------------------------------------------------- +# Check also that affinities are taken into account when using stat4 data +# to estimate the number of rows scanned by any other constraint on a +# column other than the leftmost. +# +drop_all_tables +do_test 14.1 { + execsql { CREATE TABLE t1(a, b INTEGER, c) } + for {set i 0} {$i<100} {incr i} { + set c [expr $i % 3] + execsql { INSERT INTO t1 VALUES('ott', $i, $c) } + } + execsql { + CREATE INDEX i1 ON t1(a, b); + CREATE INDEX i2 ON t1(c); + ANALYZE; + } +} {} +do_eqp_test 13.2.1 { + SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1 +} {/SEARCH TABLE t1 USING INDEX i1/} +do_eqp_test 13.2.2 { + SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1 +} {/SEARCH TABLE t1 USING INDEX i1/} + +#------------------------------------------------------------------------- +# By default, 16 non-periodic samples are collected for the stat4 table. +# The following tests attempt to verify that the most common keys are +# being collected. +# +proc check_stat4 {tn} { + db eval ANALYZE + db eval {SELECT a, b, c, d FROM t1} { + incr k($a) + incr k([list $a $b]) + incr k([list $a $b $c]) + if { [info exists k([list $a $b $c $d])]==0 } { incr nRow } + incr k([list $a $b $c $d]) + } + + set L [list] + foreach key [array names k] { + lappend L [list $k($key) $key] + } + + set nSample $nRow + if {$nSample>16} {set nSample 16} + + set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0] + foreach key [array names k] { + if {$k($key)>$nThreshold} { + set expect($key) 1 + } + if {$k($key)==$nThreshold} { + set possible($key) 1 + } + } + + + set nPossible [expr $nSample - [llength [array names expect]]] + + #puts "EXPECT: [array names expect]" + #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]" + #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx='i1'}]" + + db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} { + set seen 0 + for {set i 0} {$i<4} {incr i} { + unset -nocomplain expect([lrange $s 0 $i]) + if {[info exists possible([lrange $s 0 $i])]} { + set seen 1 + unset -nocomplain possible([lrange $s 0 $i]) + } + } + if {$seen} {incr nPossible -1} + } + if {$nPossible<0} {set nPossible 0} + + set res [list [llength [array names expect]] $nPossible] + uplevel [list do_test $tn [list set {} $res] {0 0}] +} + +drop_all_tables +do_test 14.1.1 { + execsql { + CREATE TABLE t1(a,b,c,d); + CREATE INDEX i1 ON t1(a,b,c,d); + } + for {set i 0} {$i < 160} {incr i} { + execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } + if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } } + } +} {} +check_stat4 14.1.2 + +do_test 14.2.1 { + execsql { DELETE FROM t1 } + for {set i 0} {$i < 1600} {incr i} { + execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) } + } +} {} +check_stat4 14.2.2 + +do_test 14.3.1 { + for {set i 0} {$i < 10} {incr i} { + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } + } +} {} +check_stat4 14.3.2 + +do_test 14.4.1 { + execsql {DELETE FROM t1} + for {set i 1} {$i < 160} {incr i} { + set b [expr $i % 10] + if {$b==0 || $b==2} {set b 1} + execsql { INSERT INTO t1 VALUES($i/10,$b,$i,$i) } + } +} {} +check_stat4 14.4.2 +db func lrange lrange +db func lindex lindex +do_execsql_test 14.4.3 { + SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4 + WHERE lindex(s, 1)=='1' ORDER BY rowid +} { + {0 1} {1 1} {2 1} {3 1} + {4 1} {5 1} {6 1} {7 1} + {8 1} {9 1} {10 1} {11 1} + {12 1} {13 1} {14 1} {15 1} +} + +#------------------------------------------------------------------------- +# Test that nothing untoward happens if the stat4 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_stat4 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_stat4 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_stat4 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_stat4 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 stat4 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} { + if {$i<8} {set b 0} else { set b $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 AND b=10 AND c=10; +} {/USING INDEX i1/} +do_eqp_test 17.3 { + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; +} {/USING INDEX i1/} + +do_execsql_test 17.4 { + CREATE INDEX i2 ON t1(c); + ANALYZE main.i2; +} +do_eqp_test 17.5 { + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; +} {/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_stat4 } +} {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 $i $i $i" + do_execsql_test 20.3.$i { + SELECT count(*) FROM sqlite_stat4 + WHERE lrange(test_decode(sample), 0, 3)=$val + } {1} +} +finish_test ADDED test/analyzeB.test Index: test/analyzeB.test ================================================================== --- test/analyzeB.test +++ test/analyzeB.test @@ -0,0 +1,683 @@ +# 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 sqlite_stat3 +# functionality is working. The tests in this file are based on a subset +# of the sqlite_stat4 tests in analyze9.test. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix analyzeB + +ifcapable !stat3 { + finish_test + return +} + +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); +} {} + + +do_execsql_test 1.1 { + ANALYZE; +} {} + +do_execsql_test 1.2 { + SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3; +} { + t1 i1 1 0 0 '(0)' + t1 i1 1 1 1 '(1)' + t1 i1 1 2 2 '(2)' + t1 i1 1 3 3 '(3)' + t1 i1 1 4 4 '(4)' +} + +if {[permutation] != "utf16"} { + do_execsql_test 1.3 { + SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3; + } { + t1 i1 1 0 0 '(0)' + t1 i1 1 1 1 '(1)' + t1 i1 1 2 2 '(2)' + t1 i1 1 3 3 '(3)' + t1 i1 1 4 4 '(4)' + } +} + + +#------------------------------------------------------------------------- +# This is really just to test SQL user function "test_decode". +# +reset_db +do_execsql_test 2.1 { + CREATE TABLE t1(a, b, c); + INSERT INTO t1(a) VALUES('some text'); + INSERT INTO t1(a) VALUES(14); + INSERT INTO t1(a) VALUES(NULL); + INSERT INTO t1(a) VALUES(22.0); + INSERT INTO t1(a) VALUES(x'656667'); + CREATE INDEX i1 ON t1(a, b, c); + ANALYZE; + SELECT quote(sample) FROM sqlite_stat3; +} { + NULL 14 22.0 {'some text'} X'656667' +} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 3.1 { + CREATE TABLE t2(a, b); + CREATE INDEX i2 ON t2(a, b); + BEGIN; +} + +do_test 3.2 { + for {set i 0} {$i < 1000} {incr i} { + set a [expr $i / 10] + set b [expr int(rand() * 15.0)] + execsql { INSERT INTO t2 VALUES($a, $b) } + } + execsql COMMIT +} {} + +db func lindex lindex + +# Each value of "a" occurs exactly 10 times in the table. +# +do_execsql_test 3.3.1 { + SELECT count(*) FROM t2 GROUP BY a; +} [lrange [string repeat "10 " 100] 0 99] + +# The first element in the "nEq" list of all samples should therefore be 10. +# +do_execsql_test 3.3.2 { + ANALYZE; + SELECT nEq FROM sqlite_stat3; +} [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} + + +#------------------------------------------------------------------------- +# These tests verify that the sample selection for stat3 appears to be +# working as designed. +# + +reset_db +db func lindex lindex +db func lrange lrange + +do_execsql_test 4.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a, b, c); + CREATE INDEX i1 ON t1(c, b, a); +} + + +proc insert_filler_rows_n {iStart args} { + set A(-ncopy) 1 + set A(-nval) 1 + + foreach {k v} $args { + if {[info exists A($k)]==0} { error "no such option: $k" } + set A($k) $v + } + if {[llength $args] % 2} { + error "option requires an argument: [lindex $args end]" + } + + for {set i 0} {$i < $A(-nval)} {incr i} { + set iVal [expr $iStart+$i] + for {set j 0} {$j < $A(-ncopy)} {incr j} { + execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) } + } + } +} + +do_test 4.1 { + execsql { BEGIN } + insert_filler_rows_n 0 -ncopy 10 -nval 19 + insert_filler_rows_n 20 -ncopy 1 -nval 100 + + execsql { + INSERT INTO t1(c, b, a) VALUES(200, 1, 'a'); + INSERT INTO t1(c, b, a) VALUES(200, 1, 'b'); + INSERT INTO t1(c, b, a) VALUES(200, 1, 'c'); + + INSERT INTO t1(c, b, a) VALUES(200, 2, 'e'); + INSERT INTO t1(c, b, a) VALUES(200, 2, 'f'); + + INSERT INTO t1(c, b, a) VALUES(201, 3, 'g'); + INSERT INTO t1(c, b, a) VALUES(201, 4, 'h'); + + ANALYZE; + SELECT count(*) FROM sqlite_stat3; + SELECT count(*) FROM t1; + } +} {24 297} + +do_execsql_test 4.2 { + SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 ORDER BY rowid LIMIT 16; +} { + 10 0 0 0 + 10 10 1 1 + 10 20 2 2 + 10 30 3 3 + 10 40 4 4 + 10 50 5 5 + 10 60 6 6 + 10 70 7 7 + 10 80 8 8 + 10 90 9 9 + 10 100 10 10 + 10 110 11 11 + 10 120 12 12 + 10 130 13 13 + 10 140 14 14 + 10 150 15 15 +} + +do_execsql_test 4.3 { + SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 + ORDER BY rowid DESC LIMIT 2; +} { + 2 295 120 201 + 5 290 119 200 +} + +do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120 +do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119 + +reset_db +do_test 4.7 { + execsql { + BEGIN; + CREATE TABLE t1(o,t INTEGER PRIMARY KEY); + CREATE INDEX i1 ON t1(o); + } + for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} { + execsql { INSERT INTO t1 VALUES('x', $i) } + } + execsql { + COMMIT; + ANALYZE; + SELECT count(*) FROM sqlite_stat3; + } +} {1} +do_execsql_test 4.8 { + SELECT sample FROM sqlite_stat3; +} {x} + + +#------------------------------------------------------------------------- +# The following would cause a crash at one point. +# +reset_db +do_execsql_test 5.1 { + PRAGMA encoding = 'utf-16'; + CREATE TABLE t0(v); + ANALYZE; +} + +#------------------------------------------------------------------------- +# This was also crashing (corrupt sqlite_stat3 table). +# +reset_db +do_execsql_test 6.1 { + CREATE TABLE t1(a, b); + CREATE INDEX i1 ON t1(a); + CREATE INDEX i2 ON t1(b); + INSERT INTO t1 VALUES(1, 1); + INSERT INTO t1 VALUES(2, 2); + INSERT INTO t1 VALUES(3, 3); + INSERT INTO t1 VALUES(4, 4); + INSERT INTO t1 VALUES(5, 5); + ANALYZE; + PRAGMA writable_schema = 1; + CREATE TEMP TABLE x1 AS + SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat3 + ORDER BY (rowid%5), rowid; + DELETE FROM sqlite_stat3; + INSERT INTO sqlite_stat3 SELECT * FROM x1; + PRAGMA writable_schema = 0; + ANALYZE sqlite_master; +} +do_execsql_test 6.2 { + SELECT * FROM t1 WHERE a = 'abc'; +} + +#------------------------------------------------------------------------- +# The following tests experiment with adding corrupted records to the +# 'sample' column of the sqlite_stat3 table. +# +reset_db +sqlite3_db_config_lookaside db 0 0 0 + +do_execsql_test 7.1 { + CREATE TABLE t1(a, b); + CREATE INDEX i1 ON t1(a, b); + INSERT INTO t1 VALUES(1, 1); + INSERT INTO t1 VALUES(2, 2); + INSERT INTO t1 VALUES(3, 3); + INSERT INTO t1 VALUES(4, 4); + INSERT INTO t1 VALUES(5, 5); + ANALYZE; + UPDATE sqlite_stat3 SET sample = X'' WHERE rowid = 1; + ANALYZE sqlite_master; +} + +do_execsql_test 7.2 { + UPDATE sqlite_stat3 SET sample = X'FFFF'; + ANALYZE sqlite_master; + SELECT * FROM t1 WHERE a = 1; +} {1 1} + +do_execsql_test 7.3 { + ANALYZE; + UPDATE sqlite_stat3 SET neq = '0 0 0'; + ANALYZE sqlite_master; + SELECT * FROM t1 WHERE a = 1; +} {1 1} + +do_execsql_test 7.4 { + ANALYZE; + UPDATE sqlite_stat3 SET ndlt = '0 0 0'; + ANALYZE sqlite_master; + SELECT * FROM t1 WHERE a = 3; +} {3 3} + +do_execsql_test 7.5 { + ANALYZE; + UPDATE sqlite_stat3 SET nlt = '0 0 0'; + ANALYZE sqlite_master; + SELECT * FROM t1 WHERE a = 5; +} {5 5} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 8.1 { + CREATE TABLE t1(x TEXT); + CREATE INDEX i1 ON t1(x); + INSERT INTO t1 VALUES('1'); + INSERT INTO t1 VALUES('2'); + INSERT INTO t1 VALUES('3'); + INSERT INTO t1 VALUES('4'); + ANALYZE; +} +do_execsql_test 8.2 { + SELECT * FROM t1 WHERE x = 3; +} {3} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 9.1 { + CREATE TABLE t1(a, b, c, d, e); + CREATE INDEX i1 ON t1(a, b, c, d); + CREATE INDEX i2 ON t1(e); +} +do_test 9.2 { + execsql BEGIN; + for {set i 0} {$i < 100} {incr i} { + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" + } + for {set i 0} {$i < 20} {incr i} { + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)" + } + for {set i 102} {$i < 200} {incr i} { + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" + } + execsql COMMIT + execsql ANALYZE +} {} + +do_eqp_test 9.3.1 { + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5; +} {/t1 USING INDEX i1/} +do_eqp_test 9.3.2 { + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5; +} {/t1 USING INDEX i1/} + +set value_d [expr 101] +do_eqp_test 9.4.1 { + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 +} {/t1 USING INDEX i1/} +set value_d [expr 99] +do_eqp_test 9.4.2 { + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 +} {/t1 USING INDEX i1/} + +#------------------------------------------------------------------------- +# Check that the planner takes stat3 data into account when considering +# "IS NULL" and "IS NOT NULL" constraints. +# +do_execsql_test 10.1.1 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a, b); + CREATE INDEX t3a ON t3(a); + CREATE INDEX t3b ON t3(b); +} +do_test 10.1.2 { + for {set i 1} {$i < 100} {incr i} { + if {$i>90} { 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 + ADDED test/fkey7.test Index: test/fkey7.test ================================================================== --- test/fkey7.test +++ test/fkey7.test @@ -0,0 +1,54 @@ +# 2001 September 15 +# +# 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 implements regression tests for SQLite library. +# +# This file implements tests for foreign keys. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix fkey7 + +ifcapable {!foreignkey} { + finish_test + return +} + +do_execsql_test 1.1 { + PRAGMA foreign_keys = 1; + + CREATE TABLE s1(a PRIMARY KEY, b); + CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a)); + + CREATE TABLE c1(a, b REFERENCES par); + CREATE TABLE c2(a, b REFERENCES par); + CREATE TABLE c3(a, b REFERENCES par(c)); +} + +proc auth {op tbl args} { + if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 } + return "SQLITE_OK" +} +db auth auth +db cache size 0 +proc do_tblsread_test {tn sql tbllist} { + array unset ::tbls + uplevel [list execsql $sql] + uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist] +} + +do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1} +do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par} +do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par} +do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1} + + +finish_test Index: test/mallocA.test ================================================================== --- test/mallocA.test +++ test/mallocA.test @@ -94,10 +94,28 @@ } 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}] + } } # Ensure that no file descriptors were leaked. do_test malloc-99.X { catch {db close} Index: test/permutations.test ================================================================== --- test/permutations.test +++ test/permutations.test @@ -309,10 +309,18 @@ } -files { pager1.test pager2.test pagerfault.test pagerfault2.test walfault.test walbak.test journal2.test tkt-9d68c883.test } +test_suite "coverage-analyze" -description { + Coverage tests for file analyze.c. +} -files { + analyze3.test analyze4.test analyze5.test analyze6.test + analyze7.test analyze8.test analyze9.test analyzeA.test + analyze.test analyzeB.test mallocA.test +} + lappend ::testsuitelist xxx #------------------------------------------------------------------------- # Define the permutation test suites: # @@ -499,11 +507,11 @@ } -presql { pragma encoding = 'UTF-16' } -files { alter.test alter3.test analyze.test analyze3.test analyze4.test analyze5.test analyze6.test - analyze7.test analyze8.test analyze9.test analyzeA.test + analyze7.test analyze8.test analyze9.test analyzeA.test analyzeB.test auth.test bind.test blob.test capi2.test capi3.test collate1.test collate2.test collate3.test collate4.test collate5.test collate6.test conflict.test date.test delete.test expr.test fkey1.test func.test hook.test index.test insert2.test insert.test interrupt.test in.test intpkey.test ioerr.test join2.test join.test lastinsert.test Index: test/shell1.test ================================================================== --- test/shell1.test +++ test/shell1.test @@ -720,11 +720,15 @@ # Test the output of the ".dump" command # do_test shell1-4.1 { + db close + forcedelete test.db + sqlite3 db test.db db eval { + PRAGMA encoding=UTF16; CREATE TABLE t1(x); INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f'); } catchcmd test.db {.dump} } {0 {PRAGMA foreign_keys=OFF; @@ -750,10 +754,18 @@ INSERT INTO t1 VALUES(X'807f');}} # Test the output of ".mode tcl" # do_test shell1-4.3 { + db close + forcedelete test.db + sqlite3 db test.db + db eval { + PRAGMA encoding=UTF8; + CREATE TABLE t1(x); + INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f'); + } catchcmd test.db ".mode tcl\nselect * from t1;" } {0 {"" "" "1" "2.25" Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -1301,7 +1301,36 @@ CREATE TABLE tother(a, b); INSERT INTO tother VALUES(1, 3.7); SELECT id, a FROM tbooking, tother WHERE id>a; } } {42 1 43 1} + +# Ticket [be84e357c035d068135f20bcfe82761bbf95006b] 2013-09-03 +# Segfault during query involving LEFT JOIN column in the ORDER BY clause. +# +do_execsql_test where-18.1 { + CREATE TABLE t181(a); + CREATE TABLE t182(b,c); + INSERT INTO t181 VALUES(1); + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL; +} {1} +do_execsql_test where-18.2 { + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c; +} {1} +do_execsql_test where-18.3 { + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c; +} {1} +do_execsql_test where-18.4 { + INSERT INTO t181 VALUES(1),(1),(1),(1); + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c; +} {1} +do_execsql_test where-18.5 { + INSERT INTO t181 VALUES(2); + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a; +} {1 2} +do_execsql_test where-18.6 { + INSERT INTO t181 VALUES(2); + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL; +} {1 2} + finish_test