Index: src/analyze.c ================================================================== --- src/analyze.c +++ src/analyze.c @@ -1127,11 +1127,11 @@ ** regChng = N ** goto endDistinctTest */ sqlite3VdbeAddOp0(v, OP_Goto); addrNextRow = sqlite3VdbeCurrentAddr(v); - if( nColTest==1 && pIdx->nKeyCol==1 && pIdx->onError!=OE_None ){ + if( nColTest==1 && pIdx->nKeyCol==1 && IsUniqueIndex(pIdx) ){ /* For a single-column UNIQUE index, once we have found a non-NULL ** row, we know that all the rest will be distinct, so skip ** subsequent distinctness tests. */ sqlite3VdbeAddOp2(v, OP_NotNull, regPrev, endDistinctTest); VdbeCoverage(v); Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -160,11 +160,11 @@ /* If the client is reading or writing an index and the schema is ** not loaded, then it is too difficult to actually check to see if ** the correct locks are held. So do not bother - just return true. ** This case does not come up very often anyhow. */ - if( isIndex && (!pSchema || (pSchema->flags&DB_SchemaLoaded)==0) ){ + if( isIndex && (!pSchema || (pSchema->schemaFlags&DB_SchemaLoaded)==0) ){ return 1; } /* Figure out the root-page that the lock should be held on. For table ** b-trees, this is just the root page of the b-tree being read or Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -2128,11 +2128,11 @@ pTable->aCol = pSelTab->aCol; pSelTab->nCol = 0; pSelTab->aCol = 0; sqlite3DeleteTable(db, pSelTab); assert( sqlite3SchemaMutexHeld(db, 0, pTable->pSchema) ); - pTable->pSchema->flags |= DB_UnresetViews; + pTable->pSchema->schemaFlags |= DB_UnresetViews; }else{ pTable->nCol = 0; nErr++; } sqlite3SelectDelete(db, pSel); @@ -2705,11 +2705,11 @@ (char *)pKey, P4_KEYINFO); sqlite3VdbeChangeP5(v, OPFLAG_BULKCSR|((memRootPage>=0)?OPFLAG_P2ISREG:0)); addr1 = sqlite3VdbeAddOp2(v, OP_SorterSort, iSorter, 0); VdbeCoverage(v); assert( pKey!=0 || db->mallocFailed || pParse->nErr ); - if( pIndex->onError!=OE_None && pKey!=0 ){ + if( IsUniqueIndex(pIndex) && pKey!=0 ){ int j2 = sqlite3VdbeCurrentAddr(v) + 3; sqlite3VdbeAddOp2(v, OP_Goto, 0, j2); addr2 = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp4Int(v, OP_SorterCompare, iSorter, j2, regRecord, pIndex->nKeyCol); VdbeCoverage(v); @@ -3102,13 +3102,13 @@ ** considered distinct and both result in separate indices. */ Index *pIdx; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ int k; - assert( pIdx->onError!=OE_None ); + assert( IsUniqueIndex(pIdx) ); assert( pIdx->idxType!=SQLITE_IDXTYPE_APPDEF ); - assert( pIndex->onError!=OE_None ); + assert( IsUniqueIndex(pIndex) ); if( pIdx->nKeyCol!=pIndex->nKeyCol ) continue; for(k=0; knKeyCol; k++){ const char *z1; const char *z2; @@ -3295,11 +3295,11 @@ for(i=nCopy+1; i<=pIdx->nKeyCol; i++){ a[i] = 23; assert( 23==sqlite3LogEst(5) ); } assert( 0==sqlite3LogEst(1) ); - if( pIdx->onError!=OE_None ) a[pIdx->nKeyCol] = 0; + if( IsUniqueIndex(pIdx) ) a[pIdx->nKeyCol] = 0; } /* ** This routine will drop an existing named index. This routine ** implements the DROP INDEX statement. Index: src/callback.c ================================================================== --- src/callback.c +++ src/callback.c @@ -445,13 +445,13 @@ sqlite3DeleteTable(0, pTab); } sqlite3HashClear(&temp1); sqlite3HashClear(&pSchema->fkeyHash); pSchema->pSeqTab = 0; - if( pSchema->flags & DB_SchemaLoaded ){ + if( pSchema->schemaFlags & DB_SchemaLoaded ){ pSchema->iGeneration++; - pSchema->flags &= ~DB_SchemaLoaded; + pSchema->schemaFlags &= ~DB_SchemaLoaded; } } /* ** Find and return the schema associated with a BTree. Create Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -1366,10 +1366,13 @@ case TK_INTEGER: case TK_STRING: case TK_FLOAT: case TK_BLOB: return 0; + case TK_COLUMN: + assert( p->pTab!=0 ); + return p->iColumn>=0 && p->pTab->aCol[p->iColumn].notNull==0; default: return 1; } } @@ -1473,83 +1476,124 @@ int sqlite3CodeOnce(Parse *pParse){ Vdbe *v = sqlite3GetVdbe(pParse); /* Virtual machine being coded */ return sqlite3VdbeAddOp1(v, OP_Once, pParse->nOnce++); } +/* +** Generate code that checks the left-most column of index table iCur to see if +** it contains any NULL entries. Cause the register at regHasNull to be set +** to a non-NULL value if iCur contains no NULLs. Cause register regHasNull +** to be set to NULL if iCur contains one or more NULL values. +*/ +static void sqlite3SetHasNullFlag(Vdbe *v, int iCur, int regHasNull){ + int j1; + sqlite3VdbeAddOp2(v, OP_Integer, 0, regHasNull); + j1 = sqlite3VdbeAddOp1(v, OP_Rewind, iCur); VdbeCoverage(v); + sqlite3VdbeAddOp3(v, OP_Column, iCur, 0, regHasNull); + sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); + VdbeComment((v, "first_entry_in(%d)", iCur)); + sqlite3VdbeJumpHere(v, j1); +} + + +#ifndef SQLITE_OMIT_SUBQUERY +/* +** The argument is an IN operator with a list (not a subquery) on the +** right-hand side. Return TRUE if that list is constant. +*/ +static int sqlite3InRhsIsConstant(Expr *pIn){ + Expr *pLHS; + int res; + assert( !ExprHasProperty(pIn, EP_xIsSelect) ); + pLHS = pIn->pLeft; + pIn->pLeft = 0; + res = sqlite3ExprIsConstant(pIn); + pIn->pLeft = pLHS; + return res; +} +#endif + /* ** This function is used by the implementation of the IN (...) operator. ** The pX parameter is the expression on the RHS of the IN operator, which ** might be either a list of expressions or a subquery. ** ** The job of this routine is to find or create a b-tree object that can ** be used either to test for membership in the RHS set or to iterate through ** all members of the RHS set, skipping duplicates. ** -** A cursor is opened on the b-tree object that the RHS of the IN operator +** A cursor is opened on the b-tree object that is the RHS of the IN operator ** and pX->iTable is set to the index of that cursor. ** ** The returned value of this function indicates the b-tree type, as follows: ** ** IN_INDEX_ROWID - The cursor was opened on a database table. ** IN_INDEX_INDEX_ASC - The cursor was opened on an ascending index. ** IN_INDEX_INDEX_DESC - The cursor was opened on a descending index. ** IN_INDEX_EPH - The cursor was opened on a specially created and ** populated epheremal table. +** IN_INDEX_NOOP - No cursor was allocated. The IN operator must be +** implemented as a sequence of comparisons. ** ** An existing b-tree might be used if the RHS expression pX is a simple ** subquery such as: ** ** SELECT FROM ** ** If the RHS of the IN operator is a list or a more complex subquery, then ** an ephemeral table might need to be generated from the RHS and then ** pX->iTable made to point to the ephermeral table instead of an -** existing table. +** existing table. +** +** The inFlags parameter must contain exactly one of the bits +** IN_INDEX_MEMBERSHIP or IN_INDEX_LOOP. If inFlags contains +** IN_INDEX_MEMBERSHIP, then the generated table will be used for a +** fast membership test. When the IN_INDEX_LOOP bit is set, the +** IN index will be used to loop over all values of the RHS of the +** IN operator. ** -** If the prNotFound parameter is 0, then the b-tree will be used to iterate -** through the set members, skipping any duplicates. In this case an -** epheremal table must be used unless the selected is guaranteed +** When IN_INDEX_LOOP is used (and the b-tree will be used to iterate +** through the set members) then the b-tree must not contain duplicates. +** An epheremal table must be used unless the selected is guaranteed ** to be unique - either because it is an INTEGER PRIMARY KEY or it ** has a UNIQUE constraint or UNIQUE index. ** -** If the prNotFound parameter is not 0, then the b-tree will be used -** for fast set membership tests. In this case an epheremal table must +** When IN_INDEX_MEMBERSHIP is used (and the b-tree will be used +** for fast set membership tests) then an epheremal table must ** be used unless is an INTEGER PRIMARY KEY or an index can ** be found with as its left-most column. +** +** If the IN_INDEX_NOOP_OK and IN_INDEX_MEMBERSHIP are both set and +** if the RHS of the IN operator is a list (not a subquery) then this +** routine might decide that creating an ephemeral b-tree for membership +** testing is too expensive and return IN_INDEX_NOOP. In that case, the +** calling routine should implement the IN operator using a sequence +** of Eq or Ne comparison operations. ** ** When the b-tree is being used for membership tests, the calling function -** needs to know whether or not the structure contains an SQL NULL -** value in order to correctly evaluate expressions like "X IN (Y, Z)". -** If there is any chance that the (...) might contain a NULL value at +** might need to know whether or not the RHS side of the IN operator +** contains a NULL. If prRhsHasNull is not a NULL pointer and +** if there is any chance that the (...) might contain a NULL value at ** runtime, then a register is allocated and the register number written -** to *prNotFound. If there is no chance that the (...) contains a -** NULL value, then *prNotFound is left unchanged. -** -** If a register is allocated and its location stored in *prNotFound, then -** its initial value is NULL. If the (...) does not remain constant -** for the duration of the query (i.e. the SELECT within the (...) -** is a correlated subquery) then the value of the allocated register is -** reset to NULL each time the subquery is rerun. This allows the -** caller to use vdbe code equivalent to the following: -** -** if( register==NULL ){ -** has_null = -** register = 1 -** } -** -** in order to avoid running the -** test more often than is necessary. +** to *prRhsHasNull. If there is no chance that the (...) contains a +** NULL value, then *prRhsHasNull is left unchanged. +** +** If a register is allocated and its location stored in *prRhsHasNull, then +** the value in that register will be NULL if the b-tree contains one or more +** NULL values, and it will be some non-NULL value if the b-tree contains no +** NULL values. */ #ifndef SQLITE_OMIT_SUBQUERY -int sqlite3FindInIndex(Parse *pParse, Expr *pX, int *prNotFound){ +int sqlite3FindInIndex(Parse *pParse, Expr *pX, u32 inFlags, int *prRhsHasNull){ Select *p; /* SELECT to the right of IN operator */ int eType = 0; /* Type of RHS table. IN_INDEX_* */ int iTab = pParse->nTab++; /* Cursor of the RHS table */ - int mustBeUnique = (prNotFound==0); /* True if RHS must be unique */ + int mustBeUnique; /* True if RHS must be unique */ Vdbe *v = sqlite3GetVdbe(pParse); /* Virtual machine being coded */ assert( pX->op==TK_IN ); + mustBeUnique = (inFlags & IN_INDEX_LOOP)!=0; /* Check to see if an existing table or index can be used to ** satisfy the query. This is preferable to generating a new ** ephemeral table. */ @@ -1602,44 +1646,59 @@ int affinity_ok = sqlite3IndexAffinityOk(pX, pTab->aCol[iCol].affinity); for(pIdx=pTab->pIndex; pIdx && eType==0 && affinity_ok; pIdx=pIdx->pNext){ if( (pIdx->aiColumn[0]==iCol) && sqlite3FindCollSeq(db, ENC(db), pIdx->azColl[0], 0)==pReq - && (!mustBeUnique || (pIdx->nKeyCol==1 && pIdx->onError!=OE_None)) + && (!mustBeUnique || (pIdx->nKeyCol==1 && IsUniqueIndex(pIdx))) ){ int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIdx); VdbeComment((v, "%s", pIdx->zName)); assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 ); eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0]; - if( prNotFound && !pTab->aCol[iCol].notNull ){ - *prNotFound = ++pParse->nMem; - sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound); + if( prRhsHasNull && !pTab->aCol[iCol].notNull ){ + *prRhsHasNull = ++pParse->nMem; + sqlite3SetHasNullFlag(v, iTab, *prRhsHasNull); } sqlite3VdbeJumpHere(v, iAddr); } } } } + + /* If no preexisting index is available for the IN clause + ** and IN_INDEX_NOOP is an allowed reply + ** and the RHS of the IN operator is a list, not a subquery + ** and the RHS is not contant or has two or fewer terms, + ** then it is not worth creating an ephermeral table to evaluate + ** the IN operator so return IN_INDEX_NOOP. + */ + if( eType==0 + && (inFlags & IN_INDEX_NOOP_OK) + && !ExprHasProperty(pX, EP_xIsSelect) + && (!sqlite3InRhsIsConstant(pX) || pX->x.pList->nExpr<=2) + ){ + eType = IN_INDEX_NOOP; + } + if( eType==0 ){ - /* Could not found an existing table or index to use as the RHS b-tree. + /* Could not find an existing table or index to use as the RHS b-tree. ** We will have to generate an ephemeral table to do the job. */ u32 savedNQueryLoop = pParse->nQueryLoop; int rMayHaveNull = 0; eType = IN_INDEX_EPH; - if( prNotFound ){ - *prNotFound = rMayHaveNull = ++pParse->nMem; - sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound); - }else{ + if( inFlags & IN_INDEX_LOOP ){ pParse->nQueryLoop = 0; if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){ eType = IN_INDEX_ROWID; } + }else if( prRhsHasNull ){ + *prRhsHasNull = rMayHaveNull = ++pParse->nMem; } sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID); pParse->nQueryLoop = savedNQueryLoop; }else{ pX->iTable = iTab; @@ -1666,31 +1725,25 @@ ** intkey B-Tree to store the set of IN(...) values instead of the usual ** (slower) variable length keys B-Tree. ** ** If rMayHaveNull is non-zero, that means that the operation is an IN ** (not a SELECT or EXISTS) and that the RHS might contains NULLs. -** Furthermore, the IN is in a WHERE clause and that we really want -** to iterate over the RHS of the IN operator in order to quickly locate -** all corresponding LHS elements. All this routine does is initialize -** the register given by rMayHaveNull to NULL. Calling routines will take -** care of changing this register value to non-NULL if the RHS is NULL-free. -** -** If rMayHaveNull is zero, that means that the subquery is being used -** for membership testing only. There is no need to initialize any -** registers to indicate the presence or absence of NULLs on the RHS. +** All this routine does is initialize the register given by rMayHaveNull +** to NULL. Calling routines will take care of changing this register +** value to non-NULL if the RHS is NULL-free. ** ** For a SELECT or EXISTS operator, return the register that holds the ** result. For IN operators or if an error occurs, the return value is 0. */ #ifndef SQLITE_OMIT_SUBQUERY int sqlite3CodeSubselect( Parse *pParse, /* Parsing context */ Expr *pExpr, /* The IN, SELECT, or EXISTS operator */ - int rMayHaveNull, /* Register that records whether NULLs exist in RHS */ + int rHasNullFlag, /* Register that records whether NULLs exist in RHS */ int isRowid /* If true, LHS of IN operator is a rowid */ ){ - int testAddr = -1; /* One-time test address */ + int jmpIfDynamic = -1; /* One-time test address */ int rReg = 0; /* Register storing resulting */ Vdbe *v = sqlite3GetVdbe(pParse); if( NEVER(v==0) ) return 0; sqlite3ExprCachePush(pParse); @@ -1703,17 +1756,17 @@ ** ** If all of the above are false, then we can run this code just once ** save the results, and reuse the same result on subsequent invocations. */ if( !ExprHasProperty(pExpr, EP_VarSelect) ){ - testAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v); + jmpIfDynamic = sqlite3CodeOnce(pParse); VdbeCoverage(v); } #ifndef SQLITE_OMIT_EXPLAIN if( pParse->explain==2 ){ char *zMsg = sqlite3MPrintf( - pParse->db, "EXECUTE %s%s SUBQUERY %d", testAddr>=0?"":"CORRELATED ", + pParse->db, "EXECUTE %s%s SUBQUERY %d", jmpIfDynamic>=0?"":"CORRELATED ", pExpr->op==TK_IN?"LIST":"SCALAR", pParse->iNextSelectId ); sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC); } #endif @@ -1723,14 +1776,10 @@ char affinity; /* Affinity of the LHS of the IN */ int addr; /* Address of OP_OpenEphemeral instruction */ Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */ KeyInfo *pKeyInfo = 0; /* Key information */ - if( rMayHaveNull ){ - sqlite3VdbeAddOp2(v, OP_Null, 0, rMayHaveNull); - } - affinity = sqlite3ExprAffinity(pLeft); /* Whether this is an 'x IN(SELECT...)' or an 'x IN()' ** expression it is handled the same way. An ephemeral table is ** filled with single-field index keys representing the results @@ -1752,24 +1801,27 @@ /* Case 1: expr IN (SELECT ...) ** ** Generate code to write the results of the select into the temporary ** table allocated and opened above. */ + Select *pSelect = pExpr->x.pSelect; SelectDest dest; ExprList *pEList; assert( !isRowid ); sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable); dest.affSdst = (u8)affinity; assert( (pExpr->iTable&0x0000FFFF)==pExpr->iTable ); - pExpr->x.pSelect->iLimit = 0; + pSelect->iLimit = 0; + testcase( pSelect->selFlags & SF_Distinct ); + pSelect->selFlags &= ~SF_Distinct; testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */ - if( sqlite3Select(pParse, pExpr->x.pSelect, &dest) ){ + if( sqlite3Select(pParse, pSelect, &dest) ){ sqlite3KeyInfoUnref(pKeyInfo); return 0; } - pEList = pExpr->x.pSelect->pEList; + pEList = pSelect->pEList; assert( pKeyInfo!=0 ); /* OOM will cause exit after sqlite3Select() */ assert( pEList!=0 ); assert( pEList->nExpr>0 ); assert( sqlite3KeyInfoIsWriteable(pKeyInfo) ); pKeyInfo->aColl[0] = sqlite3BinaryCompareCollSeq(pParse, pExpr->pLeft, @@ -1796,23 +1848,23 @@ } /* Loop through each expression in . */ r1 = sqlite3GetTempReg(pParse); r2 = sqlite3GetTempReg(pParse); - sqlite3VdbeAddOp2(v, OP_Null, 0, r2); + if( isRowid ) sqlite3VdbeAddOp2(v, OP_Null, 0, r2); for(i=pList->nExpr, pItem=pList->a; i>0; i--, pItem++){ Expr *pE2 = pItem->pExpr; int iValToIns; /* If the expression is not constant then we will need to ** disable the test that was generated above that makes sure ** this code only executes once. Because for a non-constant ** expression we need to rerun this code each time. */ - if( testAddr>=0 && !sqlite3ExprIsConstant(pE2) ){ - sqlite3VdbeChangeToNoop(v, testAddr); - testAddr = -1; + if( jmpIfDynamic>=0 && !sqlite3ExprIsConstant(pE2) ){ + sqlite3VdbeChangeToNoop(v, jmpIfDynamic); + jmpIfDynamic = -1; } /* Evaluate the expression and insert it into the temp table */ if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){ sqlite3VdbeAddOp3(v, OP_InsertInt, pExpr->iTable, r2, iValToIns); @@ -1878,12 +1930,16 @@ ExprSetVVAProperty(pExpr, EP_NoReduce); break; } } - if( testAddr>=0 ){ - sqlite3VdbeJumpHere(v, testAddr); + if( rHasNullFlag ){ + sqlite3SetHasNullFlag(v, pExpr->iTable, rHasNullFlag); + } + + if( jmpIfDynamic>=0 ){ + sqlite3VdbeJumpHere(v, jmpIfDynamic); } sqlite3ExprCachePop(pParse); return rReg; } @@ -1900,11 +1956,11 @@ ** is an array of zero or more values. The expression is true if the LHS is ** contained within the RHS. The value of the expression is unknown (NULL) ** if the LHS is NULL or if the LHS is not contained within the RHS and the ** RHS contains one or more NULL values. ** -** This routine generates code will jump to destIfFalse if the LHS is not +** This routine generates code that jumps to destIfFalse if the LHS is not ** contained within the RHS. If due to NULLs we cannot determine if the LHS ** is contained in the RHS then jump to destIfNull. If the LHS is contained ** within the RHS then fall through. */ static void sqlite3ExprCodeIN( @@ -1923,11 +1979,13 @@ ** pExpr->iTable will contains the values that make up the RHS. */ v = pParse->pVdbe; assert( v!=0 ); /* OOM detected prior to this routine */ VdbeNoopComment((v, "begin IN expr")); - eType = sqlite3FindInIndex(pParse, pExpr, &rRhsHasNull); + eType = sqlite3FindInIndex(pParse, pExpr, + IN_INDEX_MEMBERSHIP | IN_INDEX_NOOP_OK, + destIfFalse==destIfNull ? 0 : &rRhsHasNull); /* Figure out the affinity to use to create a key from the results ** of the expression. affinityStr stores a static string suitable for ** P4 of OP_MakeRecord. */ @@ -1937,86 +1995,118 @@ */ sqlite3ExprCachePush(pParse); r1 = sqlite3GetTempReg(pParse); sqlite3ExprCode(pParse, pExpr->pLeft, r1); - /* If the LHS is NULL, then the result is either false or NULL depending - ** on whether the RHS is empty or not, respectively. - */ - if( destIfNull==destIfFalse ){ - /* Shortcut for the common case where the false and NULL outcomes are - ** the same. */ - sqlite3VdbeAddOp2(v, OP_IsNull, r1, destIfNull); VdbeCoverage(v); - }else{ - int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse); - VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfNull); - sqlite3VdbeJumpHere(v, addr1); - } - - if( eType==IN_INDEX_ROWID ){ - /* In this case, the RHS is the ROWID of table b-tree - */ - sqlite3VdbeAddOp2(v, OP_MustBeInt, r1, destIfFalse); VdbeCoverage(v); - sqlite3VdbeAddOp3(v, OP_NotExists, pExpr->iTable, destIfFalse, r1); - VdbeCoverage(v); - }else{ - /* In this case, the RHS is an index b-tree. - */ - sqlite3VdbeAddOp4(v, OP_Affinity, r1, 1, 0, &affinity, 1); - - /* If the set membership test fails, then the result of the - ** "x IN (...)" expression must be either 0 or NULL. If the set - ** contains no NULL values, then the result is 0. If the set - ** contains one or more NULL values, then the result of the - ** expression is also NULL. - */ - if( rRhsHasNull==0 || destIfFalse==destIfNull ){ - /* This branch runs if it is known at compile time that the RHS - ** cannot contain NULL values. This happens as the result - ** of a "NOT NULL" constraint in the database schema. - ** - ** Also run this branch if NULL is equivalent to FALSE - ** for this particular IN operator. - */ - sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse, r1, 1); - VdbeCoverage(v); - }else{ - /* In this branch, the RHS of the IN might contain a NULL and - ** the presence of a NULL on the RHS makes a difference in the - ** outcome. - */ - int j1, j2; - - /* First check to see if the LHS is contained in the RHS. If so, - ** then the presence of NULLs in the RHS does not matter, so jump - ** over all of the code that follows. - */ - j1 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, r1, 1); - VdbeCoverage(v); - - /* Here we begin generating code that runs if the LHS is not - ** contained within the RHS. Generate additional code that - ** tests the RHS for NULLs. If the RHS contains a NULL then - ** jump to destIfNull. If there are no NULLs in the RHS then - ** jump to destIfFalse. - */ - sqlite3VdbeAddOp2(v, OP_If, rRhsHasNull, destIfNull); VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_IfNot, rRhsHasNull, destIfFalse); VdbeCoverage(v); - j2 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, rRhsHasNull, 1); - VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_Integer, 0, rRhsHasNull); - sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse); - sqlite3VdbeJumpHere(v, j2); - sqlite3VdbeAddOp2(v, OP_Integer, 1, rRhsHasNull); - sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfNull); - - /* The OP_Found at the top of this branch jumps here when true, - ** causing the overall IN expression evaluation to fall through. - */ - sqlite3VdbeJumpHere(v, j1); + /* If sqlite3FindInIndex() did not find or create an index that is + ** suitable for evaluating the IN operator, then evaluate using a + ** sequence of comparisons. + */ + if( eType==IN_INDEX_NOOP ){ + ExprList *pList = pExpr->x.pList; + CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft); + int labelOk = sqlite3VdbeMakeLabel(v); + int r2, regToFree; + int regCkNull = 0; + int ii; + assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); + if( destIfNull!=destIfFalse ){ + regCkNull = sqlite3GetTempReg(pParse); + sqlite3VdbeAddOp3(v, OP_BitAnd, r1, r1, regCkNull); + } + for(ii=0; iinExpr; ii++){ + r2 = sqlite3ExprCodeTemp(pParse, pList->a[ii].pExpr, ®ToFree); + if( regCkNull && sqlite3ExprCanBeNull(pList->a[ii].pExpr) ){ + sqlite3VdbeAddOp3(v, OP_BitAnd, regCkNull, r2, regCkNull); + } + if( iinExpr-1 || destIfNull!=destIfFalse ){ + sqlite3VdbeAddOp4(v, OP_Eq, r1, labelOk, r2, + (void*)pColl, P4_COLLSEQ); + VdbeCoverageIf(v, iinExpr-1); + VdbeCoverageIf(v, ii==pList->nExpr-1); + sqlite3VdbeChangeP5(v, affinity); + }else{ + assert( destIfNull==destIfFalse ); + sqlite3VdbeAddOp4(v, OP_Ne, r1, destIfFalse, r2, + (void*)pColl, P4_COLLSEQ); VdbeCoverage(v); + sqlite3VdbeChangeP5(v, affinity | SQLITE_JUMPIFNULL); + } + sqlite3ReleaseTempReg(pParse, regToFree); + } + if( regCkNull ){ + sqlite3VdbeAddOp2(v, OP_IsNull, regCkNull, destIfNull); VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse); + } + sqlite3VdbeResolveLabel(v, labelOk); + sqlite3ReleaseTempReg(pParse, regCkNull); + }else{ + + /* If the LHS is NULL, then the result is either false or NULL depending + ** on whether the RHS is empty or not, respectively. + */ + if( sqlite3ExprCanBeNull(pExpr->pLeft) ){ + if( destIfNull==destIfFalse ){ + /* Shortcut for the common case where the false and NULL outcomes are + ** the same. */ + sqlite3VdbeAddOp2(v, OP_IsNull, r1, destIfNull); VdbeCoverage(v); + }else{ + int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse); + VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfNull); + sqlite3VdbeJumpHere(v, addr1); + } + } + + if( eType==IN_INDEX_ROWID ){ + /* In this case, the RHS is the ROWID of table b-tree + */ + sqlite3VdbeAddOp2(v, OP_MustBeInt, r1, destIfFalse); VdbeCoverage(v); + sqlite3VdbeAddOp3(v, OP_NotExists, pExpr->iTable, destIfFalse, r1); + VdbeCoverage(v); + }else{ + /* In this case, the RHS is an index b-tree. + */ + sqlite3VdbeAddOp4(v, OP_Affinity, r1, 1, 0, &affinity, 1); + + /* If the set membership test fails, then the result of the + ** "x IN (...)" expression must be either 0 or NULL. If the set + ** contains no NULL values, then the result is 0. If the set + ** contains one or more NULL values, then the result of the + ** expression is also NULL. + */ + assert( destIfFalse!=destIfNull || rRhsHasNull==0 ); + if( rRhsHasNull==0 ){ + /* This branch runs if it is known at compile time that the RHS + ** cannot contain NULL values. This happens as the result + ** of a "NOT NULL" constraint in the database schema. + ** + ** Also run this branch if NULL is equivalent to FALSE + ** for this particular IN operator. + */ + sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse, r1, 1); + VdbeCoverage(v); + }else{ + /* In this branch, the RHS of the IN might contain a NULL and + ** the presence of a NULL on the RHS makes a difference in the + ** outcome. + */ + int j1; + + /* First check to see if the LHS is contained in the RHS. If so, + ** then the answer is TRUE the presence of NULLs in the RHS does + ** not matter. If the LHS is not contained in the RHS, then the + ** answer is NULL if the RHS contains NULLs and the answer is + ** FALSE if the RHS is NULL-free. + */ + j1 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, r1, 1); + VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_IsNull, rRhsHasNull, destIfNull); + VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse); + sqlite3VdbeJumpHere(v, j1); + } } } sqlite3ReleaseTempReg(pParse, r1); sqlite3ExprCachePop(pParse); VdbeComment((v, "end IN expr")); @@ -2636,11 +2726,11 @@ r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, ®Free1); testcase( regFree1==0 ); addr = sqlite3VdbeAddOp1(v, op, r1); VdbeCoverageIf(v, op==TK_ISNULL); VdbeCoverageIf(v, op==TK_NOTNULL); - sqlite3VdbeAddOp2(v, OP_AddImm, target, -1); + sqlite3VdbeAddOp2(v, OP_Integer, 0, target); sqlite3VdbeJumpHere(v, addr); break; } case TK_AGG_FUNCTION: { AggInfo *pInfo = pExpr->pAggInfo; @@ -2672,11 +2762,11 @@ nFarg = pFarg ? pFarg->nExpr : 0; assert( !ExprHasProperty(pExpr, EP_IntValue) ); zId = pExpr->u.zToken; nId = sqlite3Strlen30(zId); pDef = sqlite3FindFunction(db, zId, nId, nFarg, enc, 0); - if( pDef==0 ){ + if( pDef==0 || pDef->xFunc==0 ){ sqlite3ErrorMsg(pParse, "unknown function: %.*s()", nId, zId); break; } /* Attempt a direct implementation of the built-in COALESCE() and Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -223,11 +223,11 @@ if( !aiCol ) return 1; *paiCol = aiCol; } for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){ - if( pIdx->nKeyCol==nCol && pIdx->onError!=OE_None ){ + if( pIdx->nKeyCol==nCol && IsUniqueIndex(pIdx) ){ /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number ** of columns. If each indexed column corresponds to a foreign key ** column of pFKey, then this index is a winner. */ if( zKey==0 ){ Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -1896,11 +1896,11 @@ ){ return 0; /* Default values must be the same for all columns */ } } for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){ - if( pDestIdx->onError!=OE_None ){ + if( IsUniqueIndex(pDestIdx) ){ destHasUniqueIdx = 1; } for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; } Index: src/mutex.c ================================================================== --- src/mutex.c +++ src/mutex.c @@ -79,11 +79,11 @@ /* ** Retrieve a pointer to a static mutex or allocate a new dynamic one. */ sqlite3_mutex *sqlite3_mutex_alloc(int id){ #ifndef SQLITE_OMIT_AUTOINIT - if( sqlite3_initialize() ) return 0; + if( id<=SQLITE_MUTEX_RECURSIVE && sqlite3_initialize() ) return 0; #endif return sqlite3GlobalConfig.mutex.xMutexAlloc(id); } sqlite3_mutex *sqlite3MutexAlloc(int id){ Index: src/mutex_noop.c ================================================================== --- src/mutex_noop.c +++ src/mutex_noop.c @@ -105,11 +105,11 @@ ** The sqlite3_mutex_alloc() routine allocates a new ** mutex and returns a pointer to it. If it returns NULL ** that means that a mutex could not be allocated. */ static sqlite3_mutex *debugMutexAlloc(int id){ - static sqlite3_debug_mutex aStatic[6]; + static sqlite3_debug_mutex aStatic[SQLITE_MUTEX_STATIC_APP3 - 1]; sqlite3_debug_mutex *pNew = 0; switch( id ){ case SQLITE_MUTEX_FAST: case SQLITE_MUTEX_RECURSIVE: { pNew = sqlite3Malloc(sizeof(*pNew)); Index: src/mutex_unix.c ================================================================== --- src/mutex_unix.c +++ src/mutex_unix.c @@ -94,14 +94,17 @@ **
    **
  • SQLITE_MUTEX_FAST **
  • SQLITE_MUTEX_RECURSIVE **
  • SQLITE_MUTEX_STATIC_MASTER **
  • SQLITE_MUTEX_STATIC_MEM -**
  • SQLITE_MUTEX_STATIC_MEM2 +**
  • SQLITE_MUTEX_STATIC_OPEN **
  • SQLITE_MUTEX_STATIC_PRNG **
  • SQLITE_MUTEX_STATIC_LRU **
  • SQLITE_MUTEX_STATIC_PMEM +**
  • SQLITE_MUTEX_STATIC_APP1 +**
  • SQLITE_MUTEX_STATIC_APP2 +**
  • SQLITE_MUTEX_STATIC_APP3 **
** ** The first two constants cause sqlite3_mutex_alloc() to create ** a new mutex. The new mutex is recursive when SQLITE_MUTEX_RECURSIVE ** is used but not necessarily so when SQLITE_MUTEX_FAST is used. @@ -126,10 +129,13 @@ ** mutex types, the same mutex is returned on every call that has ** the same type number. */ static sqlite3_mutex *pthreadMutexAlloc(int iType){ static sqlite3_mutex staticMutexes[] = { + SQLITE3_MUTEX_INITIALIZER, + SQLITE3_MUTEX_INITIALIZER, + SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, Index: src/mutex_w32.c ================================================================== --- src/mutex_w32.c +++ src/mutex_w32.c @@ -78,27 +78,30 @@ #endif /* ** Initialize and deinitialize the mutex subsystem. */ -static sqlite3_mutex winMutex_staticMutexes[6] = { +static sqlite3_mutex winMutex_staticMutexes[] = { + SQLITE3_MUTEX_INITIALIZER, + SQLITE3_MUTEX_INITIALIZER, + SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER, SQLITE3_MUTEX_INITIALIZER }; static int winMutex_isInit = 0; +static int winMutex_isNt = -1; /* <0 means "need to query" */ -/* As winMutexInit() and winMutexEnd() are called as part of the -** sqlite3_initialize() and sqlite3_shutdown() processing, the -** "interlocked" magic used in this section may not strictly -** necessary. +/* As the winMutexInit() and winMutexEnd() functions are called as part +** of the sqlite3_initialize() and sqlite3_shutdown() processing, the +** "interlocked" magic used here is probably not strictly necessary. */ -static LONG winMutex_lock = 0; +static LONG volatile winMutex_lock = 0; int sqlite3_win32_is_nt(void); /* os_win.c */ void sqlite3_win32_sleep(DWORD milliseconds); /* os_win.c */ static int winMutexInit(void){ @@ -148,14 +151,17 @@ **
    **
  • SQLITE_MUTEX_FAST **
  • SQLITE_MUTEX_RECURSIVE **
  • SQLITE_MUTEX_STATIC_MASTER **
  • SQLITE_MUTEX_STATIC_MEM -**
  • SQLITE_MUTEX_STATIC_MEM2 +**
  • SQLITE_MUTEX_STATIC_OPEN **
  • SQLITE_MUTEX_STATIC_PRNG **
  • SQLITE_MUTEX_STATIC_LRU **
  • SQLITE_MUTEX_STATIC_PMEM +**
  • SQLITE_MUTEX_STATIC_APP1 +**
  • SQLITE_MUTEX_STATIC_APP2 +**
  • SQLITE_MUTEX_STATIC_APP3 **
** ** The first two constants cause sqlite3_mutex_alloc() to create ** a new mutex. The new mutex is recursive when SQLITE_MUTEX_RECURSIVE ** is used but not necessarily so when SQLITE_MUTEX_FAST is used. @@ -229,10 +235,11 @@ assert( p ); #ifdef SQLITE_DEBUG assert( p->nRef==0 && p->owner==0 ); assert( p->id==SQLITE_MUTEX_FAST || p->id==SQLITE_MUTEX_RECURSIVE ); #endif + assert( winMutex_isInit==1 ); DeleteCriticalSection(&p->mutex); sqlite3_free(p); } /* @@ -254,10 +261,11 @@ assert( p ); assert( p->id==SQLITE_MUTEX_RECURSIVE || winMutexNotheld2(p, tid) ); #else assert( p ); #endif + assert( winMutex_isInit==1 ); EnterCriticalSection(&p->mutex); #ifdef SQLITE_DEBUG assert( p->nRef>0 || p->owner==0 ); p->owner = tid; p->nRef++; @@ -285,11 +293,17 @@ ** first doing some #defines that prevent SQLite from building on Win98. ** For that reason, we will omit this optimization for now. See ** ticket #2685. */ #if defined(_WIN32_WINNT) && _WIN32_WINNT >= 0x0400 - if( sqlite3_win32_is_nt() && TryEnterCriticalSection(&p->mutex) ){ + assert( winMutex_isInit==1 ); + assert( winMutex_isNt>=-1 && winMutex_isNt<=1 ); + if( winMutex_isNt<0 ){ + winMutex_isNt = sqlite3_win32_is_nt(); + } + assert( winMutex_isNt==0 || winMutex_isNt==1 ); + if( winMutex_isNt && TryEnterCriticalSection(&p->mutex) ){ #ifdef SQLITE_DEBUG p->owner = tid; p->nRef++; #endif rc = SQLITE_OK; @@ -322,10 +336,11 @@ assert( p->owner==tid ); p->nRef--; if( p->nRef==0 ) p->owner = 0; assert( p->nRef==0 || p->id==SQLITE_MUTEX_RECURSIVE ); #endif + assert( winMutex_isInit==1 ); LeaveCriticalSection(&p->mutex); #ifdef SQLITE_DEBUG if( p->trace ){ OSTRACE(("LEAVE-MUTEX tid=%lu, mutex=%p (%d), nRef=%d\n", tid, p, p->trace, p->nRef)); Index: src/os_win.c ================================================================== --- src/os_win.c +++ src/os_win.c @@ -412,14 +412,13 @@ ** ** In order to facilitate testing on a WinNT system, the test fixture ** can manually set this value to 1 to emulate Win98 behavior. */ #ifdef SQLITE_TEST -int sqlite3_os_type = 0; -#elif !SQLITE_OS_WINCE && !SQLITE_OS_WINRT && \ - defined(SQLITE_WIN32_HAS_ANSI) && defined(SQLITE_WIN32_HAS_WIDE) -static int sqlite3_os_type = 0; +LONG volatile sqlite3_os_type = 0; +#else +static LONG volatile sqlite3_os_type = 0; #endif #ifndef SYSCALL # define SYSCALL sqlite3_syscall_ptr #endif @@ -1045,10 +1044,15 @@ { "CreateFileMappingFromApp", (SYSCALL)0, 0 }, #endif #define osCreateFileMappingFromApp ((HANDLE(WINAPI*)(HANDLE, \ LPSECURITY_ATTRIBUTES,ULONG,ULONG64,LPCWSTR))aSyscall[75].pCurrent) + + { "InterlockedCompareExchange", (SYSCALL)InterlockedCompareExchange, 0 }, + +#define osInterlockedCompareExchange ((LONG(WINAPI*)(LONG volatile*, \ + LONG,LONG))aSyscall[76].pCurrent) }; /* End of the overrideable system calls */ /* ** This is the xSetSystemCall() method of sqlite3_vfs for all of the @@ -1296,31 +1300,32 @@ #elif SQLITE_OS_WINCE || SQLITE_OS_WINRT || !defined(SQLITE_WIN32_HAS_ANSI) # define osIsNT() (1) #elif !defined(SQLITE_WIN32_HAS_WIDE) # define osIsNT() (0) #else -# define osIsNT() (sqlite3_win32_is_nt()) +# define osIsNT() ((sqlite3_os_type==2) || sqlite3_win32_is_nt()) #endif /* ** This function determines if the machine is running a version of Windows ** based on the NT kernel. */ int sqlite3_win32_is_nt(void){ - if( sqlite3_os_type==0 ){ + if( osInterlockedCompareExchange(&sqlite3_os_type, 0, 0)==0 ){ #if defined(NTDDI_VERSION) && NTDDI_VERSION >= NTDDI_WIN8 OSVERSIONINFOW sInfo; sInfo.dwOSVersionInfoSize = sizeof(sInfo); osGetVersionExW(&sInfo); #else OSVERSIONINFOA sInfo; sInfo.dwOSVersionInfoSize = sizeof(sInfo); osGetVersionExA(&sInfo); #endif - sqlite3_os_type = (sInfo.dwPlatformId == VER_PLATFORM_WIN32_NT) ? 2 : 1; + osInterlockedCompareExchange(&sqlite3_os_type, + (sInfo.dwPlatformId == VER_PLATFORM_WIN32_NT) ? 2 : 1, 0); } - return (sqlite3_os_type == 2); + return osInterlockedCompareExchange(&sqlite3_os_type, 2, 2)==2; } #ifdef SQLITE_WIN32_MALLOC /* ** Allocate nBytes of memory. @@ -5473,11 +5478,11 @@ }; #endif /* Double-check that the aSyscall[] array has been constructed ** correctly. See ticket [bb3a86e890c8e96ab] */ - assert( ArraySize(aSyscall)==76 ); + assert( ArraySize(aSyscall)==77 ); /* get memory map allocation granularity */ memset(&winSysInfo, 0, sizeof(SYSTEM_INFO)); #if SQLITE_OS_WINRT osGetNativeSystemInfo(&winSysInfo); Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1542,11 +1542,11 @@ sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC); sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC); for(pIdx=pTab->pIndex, i=0; pIdx; pIdx=pIdx->pNext, i++){ sqlite3VdbeAddOp2(v, OP_Integer, i, 1); sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0); - sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3); + sqlite3VdbeAddOp2(v, OP_Integer, IsUniqueIndex(pIdx), 3); sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3); } } } break; @@ -1792,13 +1792,12 @@ ** messages have been generated, output OK. Otherwise output the ** error message */ static const int iLn = VDBE_OFFSET_LINENO(2); static const VdbeOpList endCode[] = { - { OP_AddImm, 1, 0, 0}, /* 0 */ - { OP_IfNeg, 1, 0, 0}, /* 1 */ - { OP_String8, 0, 3, 0}, /* 2 */ + { OP_IfNeg, 1, 0, 0}, /* 0 */ + { OP_String8, 0, 3, 0}, /* 1 */ { OP_ResultRow, 3, 1, 0}, }; int isQuick = (sqlite3Tolower(zLeft[0])=='q'); @@ -1906,32 +1905,80 @@ sqlite3VdbeAddOp2(v, OP_Integer, 0, 8+j); /* index entries counter */ } pParse->nMem = MAX(pParse->nMem, 8+j); sqlite3VdbeAddOp2(v, OP_Rewind, iDataCur, 0); VdbeCoverage(v); loopTop = sqlite3VdbeAddOp2(v, OP_AddImm, 7, 1); + /* Verify that all NOT NULL columns really are NOT NULL */ + for(j=0; jnCol; j++){ + char *zErr; + int jmp2, jmp3; + if( j==pTab->iPKey ) continue; + if( pTab->aCol[j].notNull==0 ) continue; + sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, j, 3); + sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG); + jmp2 = sqlite3VdbeAddOp1(v, OP_NotNull, 3); VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1); /* Decrement error limit */ + zErr = sqlite3MPrintf(db, "NULL value in %s.%s", pTab->zName, + pTab->aCol[j].zName); + sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, zErr, P4_DYNAMIC); + sqlite3VdbeAddOp2(v, OP_ResultRow, 3, 1); + jmp3 = sqlite3VdbeAddOp1(v, OP_IfPos, 1); VdbeCoverage(v); + sqlite3VdbeAddOp0(v, OP_Halt); + sqlite3VdbeJumpHere(v, jmp2); + sqlite3VdbeJumpHere(v, jmp3); + } + /* Validate index entries for the current row */ for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){ - int jmp2, jmp3, jmp4; + int jmp2, jmp3, jmp4, jmp5; + int ckUniq = sqlite3VdbeMakeLabel(v); if( pPk==pIdx ) continue; r1 = sqlite3GenerateIndexKey(pParse, pIdx, iDataCur, 0, 0, &jmp3, pPrior, r1); pPrior = pIdx; sqlite3VdbeAddOp2(v, OP_AddImm, 8+j, 1); /* increment entry count */ - jmp2 = sqlite3VdbeAddOp4Int(v, OP_Found, iIdxCur+j, 0, r1, + /* Verify that an index entry exists for the current table row */ + jmp2 = sqlite3VdbeAddOp4Int(v, OP_Found, iIdxCur+j, ckUniq, r1, pIdx->nColumn); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1); /* Decrement error limit */ sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, "row ", P4_STATIC); sqlite3VdbeAddOp3(v, OP_Concat, 7, 3, 3); - sqlite3VdbeAddOp4(v, OP_String8, 0, 4, 0, " missing from index ", - P4_STATIC); + sqlite3VdbeAddOp4(v, OP_String8, 0, 4, 0, + " missing from index ", P4_STATIC); sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 3); - sqlite3VdbeAddOp4(v, OP_String8, 0, 4, 0, pIdx->zName, P4_TRANSIENT); + jmp5 = sqlite3VdbeAddOp4(v, OP_String8, 0, 4, 0, + pIdx->zName, P4_TRANSIENT); sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 3); sqlite3VdbeAddOp2(v, OP_ResultRow, 3, 1); jmp4 = sqlite3VdbeAddOp1(v, OP_IfPos, 1); VdbeCoverage(v); sqlite3VdbeAddOp0(v, OP_Halt); - sqlite3VdbeJumpHere(v, jmp4); sqlite3VdbeJumpHere(v, jmp2); + /* For UNIQUE indexes, verify that only one entry exists with the + ** current key. The entry is unique if (1) any column is NULL + ** or (2) the next entry has a different key */ + if( IsUniqueIndex(pIdx) ){ + int uniqOk = sqlite3VdbeMakeLabel(v); + int jmp6; + int kk; + for(kk=0; kknKeyCol; kk++){ + int iCol = pIdx->aiColumn[kk]; + assert( iCol>=0 && iColnCol ); + if( pTab->aCol[iCol].notNull ) continue; + sqlite3VdbeAddOp2(v, OP_IsNull, r1+kk, uniqOk); + VdbeCoverage(v); + } + jmp6 = sqlite3VdbeAddOp1(v, OP_Next, iIdxCur+j); VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_Goto, 0, uniqOk); + sqlite3VdbeJumpHere(v, jmp6); + sqlite3VdbeAddOp4Int(v, OP_IdxGT, iIdxCur+j, uniqOk, r1, + pIdx->nKeyCol); VdbeCoverage(v); + sqlite3VdbeAddOp2(v, OP_AddImm, 1, -1); /* Decrement error limit */ + sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, + "non-unique entry in index ", P4_STATIC); + sqlite3VdbeAddOp2(v, OP_Goto, 0, jmp5); + sqlite3VdbeResolveLabel(v, uniqOk); + } + sqlite3VdbeJumpHere(v, jmp4); sqlite3ResolvePartIdxLabel(pParse, jmp3); } sqlite3VdbeAddOp2(v, OP_Next, iDataCur, loopTop); VdbeCoverage(v); sqlite3VdbeJumpHere(v, loopTop-1); #ifndef SQLITE_OMIT_BTREECOUNT @@ -1952,13 +1999,13 @@ } #endif /* SQLITE_OMIT_BTREECOUNT */ } } addr = sqlite3VdbeAddOpList(v, ArraySize(endCode), endCode, iLn); - sqlite3VdbeChangeP2(v, addr, -mxErr); - sqlite3VdbeJumpHere(v, addr+1); - sqlite3VdbeChangeP4(v, addr+2, "ok", P4_STATIC); + sqlite3VdbeChangeP3(v, addr, -mxErr); + sqlite3VdbeJumpHere(v, addr); + sqlite3VdbeChangeP4(v, addr+1, "ok", P4_STATIC); } break; #endif /* SQLITE_OMIT_INTEGRITY_CHECK */ #ifndef SQLITE_OMIT_UTF16 Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -539,12 +539,11 @@ int iOffset, /* Register holding the offset counter */ int iContinue /* Jump here to skip the current record */ ){ if( iOffset>0 ){ int addr; - sqlite3VdbeAddOp2(v, OP_AddImm, iOffset, -1); - addr = sqlite3VdbeAddOp1(v, OP_IfNeg, iOffset); VdbeCoverage(v); + addr = sqlite3VdbeAddOp3(v, OP_IfNeg, iOffset, 0, -1); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Goto, 0, iContinue); VdbeComment((v, "skip OFFSET records")); sqlite3VdbeJumpHere(v, addr); } } Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -5873,14 +5873,16 @@ **
    **
  • SQLITE_MUTEX_FAST **
  • SQLITE_MUTEX_RECURSIVE **
  • SQLITE_MUTEX_STATIC_MASTER **
  • SQLITE_MUTEX_STATIC_MEM -**
  • SQLITE_MUTEX_STATIC_MEM2 +**
  • SQLITE_MUTEX_STATIC_OPEN **
  • SQLITE_MUTEX_STATIC_PRNG **
  • SQLITE_MUTEX_STATIC_LRU -**
  • SQLITE_MUTEX_STATIC_LRU2 +**
  • SQLITE_MUTEX_STATIC_PMEM +**
  • SQLITE_MUTEX_STATIC_APP1 +**
  • SQLITE_MUTEX_STATIC_APP2 **
)^ ** ** ^The first two constants (SQLITE_MUTEX_FAST and SQLITE_MUTEX_RECURSIVE) ** cause sqlite3_mutex_alloc() to create ** a new mutex. ^The new mutex is recursive when SQLITE_MUTEX_RECURSIVE @@ -6080,10 +6082,13 @@ #define SQLITE_MUTEX_STATIC_OPEN 4 /* sqlite3BtreeOpen() */ #define SQLITE_MUTEX_STATIC_PRNG 5 /* sqlite3_random() */ #define SQLITE_MUTEX_STATIC_LRU 6 /* lru page list */ #define SQLITE_MUTEX_STATIC_LRU2 7 /* NOT USED */ #define SQLITE_MUTEX_STATIC_PMEM 7 /* sqlite3PageMalloc() */ +#define SQLITE_MUTEX_STATIC_APP1 8 /* For use by application */ +#define SQLITE_MUTEX_STATIC_APP2 9 /* For use by application */ +#define SQLITE_MUTEX_STATIC_APP3 10 /* For use by application */ /* ** CAPI3REF: Retrieve the mutex for a database connection ** ** ^This interface returns a pointer the [sqlite3_mutex] object that Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -875,22 +875,22 @@ Hash trigHash; /* All triggers indexed by name */ Hash fkeyHash; /* All foreign keys by referenced table name */ Table *pSeqTab; /* The sqlite_sequence table used by AUTOINCREMENT */ u8 file_format; /* Schema format version for this file */ u8 enc; /* Text encoding used by this database */ - u16 flags; /* Flags associated with this schema */ + u16 schemaFlags; /* Flags associated with this schema */ int cache_size; /* Number of pages to use in the cache */ }; /* ** These macros can be used to test, set, or clear bits in the ** Db.pSchema->flags field. */ -#define DbHasProperty(D,I,P) (((D)->aDb[I].pSchema->flags&(P))==(P)) -#define DbHasAnyProperty(D,I,P) (((D)->aDb[I].pSchema->flags&(P))!=0) -#define DbSetProperty(D,I,P) (D)->aDb[I].pSchema->flags|=(P) -#define DbClearProperty(D,I,P) (D)->aDb[I].pSchema->flags&=~(P) +#define DbHasProperty(D,I,P) (((D)->aDb[I].pSchema->schemaFlags&(P))==(P)) +#define DbHasAnyProperty(D,I,P) (((D)->aDb[I].pSchema->schemaFlags&(P))!=0) +#define DbSetProperty(D,I,P) (D)->aDb[I].pSchema->schemaFlags|=(P) +#define DbClearProperty(D,I,P) (D)->aDb[I].pSchema->schemaFlags&=~(P) /* ** Allowed values for the DB.pSchema->flags field. ** ** The DB_SchemaLoaded flag is set after the database schema has been @@ -1726,10 +1726,13 @@ #define SQLITE_IDXTYPE_PRIMARYKEY 2 /* Is the PRIMARY KEY for the table */ /* Return true if index X is a PRIMARY KEY index */ #define IsPrimaryKeyIndex(X) ((X)->idxType==SQLITE_IDXTYPE_PRIMARYKEY) +/* Return true if index X is a UNIQUE index */ +#define IsUniqueIndex(X) ((X)->onError!=OE_None) + /* ** Each sample stored in the sqlite_stat3 table is represented in memory ** using a structure of this type. See documentation at the top of the ** analyze.c source file for additional information. */ @@ -3598,15 +3601,25 @@ #else #define sqlite3BeginBenignMalloc() #define sqlite3EndBenignMalloc() #endif -#define IN_INDEX_ROWID 1 -#define IN_INDEX_EPH 2 -#define IN_INDEX_INDEX_ASC 3 -#define IN_INDEX_INDEX_DESC 4 -int sqlite3FindInIndex(Parse *, Expr *, int*); +/* +** Allowed return values from sqlite3FindInIndex() +*/ +#define IN_INDEX_ROWID 1 /* Search the rowid of the table */ +#define IN_INDEX_EPH 2 /* Search an ephemeral b-tree */ +#define IN_INDEX_INDEX_ASC 3 /* Existing index ASCENDING */ +#define IN_INDEX_INDEX_DESC 4 /* Existing index DESCENDING */ +#define IN_INDEX_NOOP 5 /* No table available. Use comparisons */ +/* +** Allowed flags for the 3rd parameter to sqlite3FindInIndex(). +*/ +#define IN_INDEX_NOOP_OK 0x0001 /* OK to return IN_INDEX_NOOP */ +#define IN_INDEX_MEMBERSHIP 0x0002 /* IN operator used for membership test */ +#define IN_INDEX_LOOP 0x0004 /* IN operator used as a loop */ +int sqlite3FindInIndex(Parse *, Expr *, u32, int*); #ifdef SQLITE_ENABLE_ATOMIC_WRITE int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int); int sqlite3JournalSize(sqlite3_vfs *); int sqlite3JournalCreate(sqlite3_file *); Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -6579,11 +6579,11 @@ extern int sqlite3_xferopt_count; extern int sqlite3_pager_readdb_count; extern int sqlite3_pager_writedb_count; extern int sqlite3_pager_writej_count; #if SQLITE_OS_WIN - extern int sqlite3_os_type; + extern LONG volatile sqlite3_os_type; #endif #ifdef SQLITE_DEBUG extern int sqlite3WhereTrace; extern int sqlite3OSTrace; extern int sqlite3WalTrace; @@ -6637,11 +6637,11 @@ Tcl_LinkVar(interp, "sqlite_last_needed_collation", (char*)&pzNeededCollation, TCL_LINK_STRING|TCL_LINK_READ_ONLY); #endif #if SQLITE_OS_WIN Tcl_LinkVar(interp, "sqlite_os_type", - (char*)&sqlite3_os_type, TCL_LINK_INT); + (char*)&sqlite3_os_type, TCL_LINK_LONG); #endif #ifdef SQLITE_TEST { static const char *query_plan = "*** OBSOLETE VARIABLE ***"; Tcl_LinkVar(interp, "sqlite_query_plan", Index: src/test_multiplex.c ================================================================== --- src/test_multiplex.c +++ src/test_multiplex.c @@ -1174,18 +1174,24 @@ ** routine. ** ** THIS ROUTINE IS NOT THREADSAFE. Call this routine exactly once while ** shutting down in order to free all remaining multiplex groups. */ -int sqlite3_multiplex_shutdown(void){ +int sqlite3_multiplex_shutdown(int eForce){ + int rc = SQLITE_OK; if( gMultiplex.isInitialized==0 ) return SQLITE_MISUSE; - if( gMultiplex.pGroups ) return SQLITE_MISUSE; + if( gMultiplex.pGroups ){ + sqlite3_log(SQLITE_MISUSE, "sqlite3_multiplex_shutdown() called " + "while database connections are still open"); + if( !eForce ) return SQLITE_MISUSE; + rc = SQLITE_MISUSE; + } gMultiplex.isInitialized = 0; sqlite3_mutex_free(gMultiplex.pMutex); sqlite3_vfs_unregister(&gMultiplex.sThisVfs); memset(&gMultiplex, 0, sizeof(gMultiplex)); - return SQLITE_OK; + return rc; } /***************************** Test Code ***********************************/ #ifdef SQLITE_TEST #include @@ -1234,17 +1240,20 @@ ){ int rc; /* Value returned by multiplex_shutdown() */ UNUSED_PARAMETER(clientData); - if( objc!=1 ){ - Tcl_WrongNumArgs(interp, 1, objv, ""); + if( objc==2 && strcmp(Tcl_GetString(objv[1]),"-force")!=0 ){ + objc = 3; + } + if( (objc!=1 && objc!=2) ){ + Tcl_WrongNumArgs(interp, 1, objv, "?-force?"); return TCL_ERROR; } /* Call sqlite3_multiplex_shutdown() */ - rc = sqlite3_multiplex_shutdown(); + rc = sqlite3_multiplex_shutdown(objc==2); Tcl_SetResult(interp, (char *)sqlite3ErrName(rc), TCL_STATIC); return TCL_OK; } Index: src/test_multiplex.h ================================================================== --- src/test_multiplex.h +++ src/test_multiplex.h @@ -88,11 +88,11 @@ ** routine. ** ** THIS ROUTINE IS NOT THREADSAFE. Call this routine exactly once while ** shutting down in order to free all remaining multiplex groups. */ -extern int sqlite3_multiplex_shutdown(void); +extern int sqlite3_multiplex_shutdown(int eForce); #ifdef __cplusplus } /* End of the 'extern "C"' block */ #endif Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -436,11 +436,12 @@ sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelBreak, regKey, nKey); VdbeCoverageNeverTaken(v); } labelContinue = labelBreak; sqlite3VdbeAddOp2(v, OP_IsNull, pPk ? regKey : regOldRowid, labelBreak); - VdbeCoverage(v); + VdbeCoverageIf(v, pPk==0); + VdbeCoverageIf(v, pPk!=0); }else if( pPk ){ labelContinue = sqlite3VdbeMakeLabel(v); sqlite3VdbeAddOp2(v, OP_Rewind, iEph, labelBreak); VdbeCoverage(v); addrTop = sqlite3VdbeAddOp2(v, OP_RowKey, iEph, regKey); sqlite3VdbeAddOp4Int(v, OP_NotFound, iDataCur, labelContinue, regKey, 0); Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -124,10 +124,16 @@ ** ** M is an integer, 2 or 3, that indices how many different ways the ** branch can go. It is usually 2. "I" is the direction the branch ** goes. 0 means falls through. 1 means branch is taken. 2 means the ** second alternative branch is taken. +** +** iSrcLine is the source code line (from the __LINE__ macro) that +** generated the VDBE instruction. This instrumentation assumes that all +** source code is in a single file (the amalgamation). Special values 1 +** and 2 for the iSrcLine parameter mean that this particular branch is +** always taken or never taken, respectively. */ #if !defined(SQLITE_VDBE_COVERAGE) # define VdbeBranchTaken(I,M) #else # define VdbeBranchTaken(I,M) vdbeTakeBranch(pOp->iSrcLine,I,M) @@ -798,11 +804,11 @@ break; } /* Opcode: EndCoroutine P1 * * * * ** -** The instruction at the address in register P1 is an Yield. +** The instruction at the address in register P1 is a Yield. ** Jump to the P2 parameter of that Yield. ** After the jump, register P1 becomes undefined. ** ** See also: InitCoroutine */ @@ -991,11 +997,11 @@ /* Opcode: String8 * P2 * P4 * ** Synopsis: r[P2]='P4' ** ** P4 points to a nul terminated UTF-8 string. This opcode is transformed -** into an OP_String before it is executed for the first time. During +** into a String before it is executed for the first time. During ** this transformation, the length of string P4 is computed and stored ** as the P1 parameter. */ case OP_String8: { /* same as TK_STRING, out2-prerelease */ assert( pOp->p4.z!=0 ); @@ -2237,17 +2243,17 @@ /* Opcode: If P1 P2 P3 * * ** ** Jump to P2 if the value in register P1 is true. The value ** is considered true if it is numeric and non-zero. If the value -** in P1 is NULL then take the jump if P3 is non-zero. +** in P1 is NULL then take the jump if and only if P3 is non-zero. */ /* Opcode: IfNot P1 P2 P3 * * ** ** Jump to P2 if the value in register P1 is False. The value ** is considered false if it has a numeric value of zero. If the value -** in P1 is NULL then take the jump if P3 is zero. +** in P1 is NULL then take the jump if and only if P3 is non-zero. */ case OP_If: /* jump, in1 */ case OP_IfNot: { /* jump, in1 */ int c; pIn1 = &aMem[pOp->p1]; @@ -3515,11 +3521,11 @@ ** Reposition cursor P1 so that it points to the smallest entry that ** is greater than or equal to the key value. If there are no records ** greater than or equal to the key and P2 is not zero, then jump to P2. ** ** This opcode leaves the cursor configured to move in forward order, -** from the begining toward the end. In other words, the cursor is +** from the beginning toward the end. In other words, the cursor is ** configured to use Next, not Prev. ** ** See also: Found, NotFound, SeekLt, SeekGt, SeekLe */ /* Opcode: SeekGT P1 P2 P3 P4 * @@ -3755,13 +3761,13 @@ ** ** Cursor P1 is on an index btree. If the record identified by P3 and P4 ** is a prefix of any entry in P1 then a jump is made to P2 and ** P1 is left pointing at the matching entry. ** -** This operation leaves the cursor in a state where it cannot be -** advanced in either direction. In other words, the Next and Prev -** opcodes do not work after this operation. +** This operation leaves the cursor in a state where it can be +** advanced in the forward direction. The Next instruction will work, +** but not the Prev instruction. ** ** See also: NotFound, NoConflict, NotExists. SeekGe */ /* Opcode: NotFound P1 P2 P3 P4 * ** Synopsis: key=r[P3@P4] @@ -3824,11 +3830,11 @@ assert( pOp->p1>=0 && pOp->p1nCursor ); assert( pOp->p4type==P4_INT32 ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); #ifdef SQLITE_DEBUG - pC->seekOp = 0; + pC->seekOp = pOp->opcode; #endif pIn3 = &aMem[pOp->p3]; assert( pC->pCursor!=0 ); assert( pC->isTable==0 ); pFree = 0; /* Not needed. Only used to suppress a compiler warning. */ @@ -4234,11 +4240,11 @@ ** Delete the record at which the P1 cursor is currently pointing. ** ** The cursor will be left pointing at either the next or the previous ** record in the table. If it is left pointing at the next record, then ** the next Next instruction will be a no-op. Hence it is OK to delete -** a record from within an Next loop. +** a record from within a Next loop. ** ** If the OPFLAG_NCHANGE flag of P2 is set, then the row change count is ** incremented (otherwise not). ** ** P1 must not be pseudo-table. It has to be a real table with @@ -4333,11 +4339,11 @@ /* Opcode: SorterCompare P1 P2 P3 P4 ** Synopsis: if key(P1)!=trim(r[P3],P4) goto P2 ** ** P1 is a sorter cursor. This instruction compares a prefix of the -** the record blob in register P3 against a prefix of the entry that +** record blob in register P3 against a prefix of the entry that ** the sorter cursor currently points to. Only the first P4 fields ** of r[P3] and the sorter record are compared. ** ** If either P3 or the sorter contains a NULL in one of their significant ** fields (not counting the P4 fields at the end which are ignored) then @@ -4732,11 +4738,11 @@ /* The Next opcode is only used after SeekGT, SeekGE, and Rewind. ** The Prev opcode is only used after SeekLT, SeekLE, and Last. */ assert( pOp->opcode!=OP_Next || pOp->opcode!=OP_NextIfOpen || pC->seekOp==OP_SeekGT || pC->seekOp==OP_SeekGE - || pC->seekOp==OP_Rewind ); + || pC->seekOp==OP_Rewind || pC->seekOp==OP_Found); assert( pOp->opcode!=OP_Prev || pOp->opcode!=OP_PrevIfOpen || pC->seekOp==OP_SeekLT || pC->seekOp==OP_SeekLE || pC->seekOp==OP_Last ); rc = pOp->p4.xAdvance(pC->pCursor, &res); @@ -5659,21 +5665,20 @@ pc = pOp->p2 - 1; } break; } -/* Opcode: IfNeg P1 P2 * * * -** Synopsis: if r[P1]<0 goto P2 +/* Opcode: IfNeg P1 P2 P3 * * +** Synopsis: r[P1]+=P3, if r[P1]<0 goto P2 ** -** If the value of register P1 is less than zero, jump to P2. -** -** It is illegal to use this instruction on a register that does -** not contain an integer. An assertion fault will result if you try. +** Register P1 must contain an integer. Add literal P3 to the value in +** register P1 then if the value of register P1 is less than zero, jump to P2. */ case OP_IfNeg: { /* jump, in1 */ pIn1 = &aMem[pOp->p1]; assert( pIn1->flags&MEM_Int ); + pIn1->u.i += pOp->p3; VdbeBranchTaken(pIn1->u.i<0, 2); if( pIn1->u.i<0 ){ pc = pOp->p2 - 1; } break; @@ -5682,13 +5687,10 @@ /* Opcode: IfZero P1 P2 P3 * * ** Synopsis: r[P1]+=P3, if r[P1]==0 goto P2 ** ** The register P1 must contain an integer. Add literal P3 to the ** value in register P1. If the result is exactly 0, jump to P2. -** -** It is illegal to use this instruction on a register that does -** not contain an integer. An assertion fault will result if you try. */ case OP_IfZero: { /* jump, in1 */ pIn1 = &aMem[pOp->p1]; assert( pIn1->flags&MEM_Int ); pIn1->u.i += pOp->p3; Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -1468,11 +1468,11 @@ ** ** 3. All of those index columns for which the WHERE clause does not ** contain a "col=X" term are subject to a NOT NULL constraint. */ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ - if( pIdx->onError==OE_None ) continue; + if( !IsUniqueIndex(pIdx) ) continue; for(i=0; inKeyCol; i++){ i16 iCol = pIdx->aiColumn[i]; if( 0==findTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) ){ int iIdxCol = findIndexCol(pParse, pDistinct, iBase, pIdx, i); if( iIdxCol<0 || pTab->aCol[iCol].notNull==0 ){ @@ -2520,11 +2520,11 @@ testcase( bRev ); bRev = !bRev; } assert( pX->op==TK_IN ); iReg = iTarget; - eType = sqlite3FindInIndex(pParse, pX, 0); + eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0); if( eType==IN_INDEX_INDEX_DESC ){ testcase( bRev ); bRev = !bRev; } iTab = pX->iTable; @@ -4374,11 +4374,11 @@ ** changes "x IN (?)" into "x=?". */ }else if( eOp & (WO_EQ) ){ pNew->wsFlags |= WHERE_COLUMN_EQ; if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){ - if( iCol>=0 && pProbe->onError==OE_None ){ + if( iCol>=0 && !IsUniqueIndex(pProbe) ){ pNew->wsFlags |= WHERE_UNQ_WANTED; }else{ pNew->wsFlags |= WHERE_ONEROW; } } @@ -5229,11 +5229,11 @@ }else{ nKeyCol = pIndex->nKeyCol; nColumn = pIndex->nColumn; assert( nColumn==nKeyCol+1 || !HasRowid(pIndex->pTable) ); assert( pIndex->aiColumn[nColumn-1]==(-1) || !HasRowid(pIndex->pTable)); - isOrderDistinct = pIndex->onError!=OE_None; + isOrderDistinct = IsUniqueIndex(pIndex); } /* Loop through all columns of the index and deal with the ones ** that are not constrained by == or IN. */ @@ -5744,11 +5744,11 @@ pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */ }else{ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ assert( pLoop->aLTermSpace==pLoop->aLTerm ); assert( ArraySize(pLoop->aLTermSpace)==4 ); - if( pIdx->onError==OE_None + if( !IsUniqueIndex(pIdx) || pIdx->pPartIdxWhere!=0 || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) ) continue; for(j=0; jnKeyCol; j++){ pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx); Index: test/in4.test ================================================================== --- test/in4.test +++ test/in4.test @@ -229,15 +229,15 @@ do_execsql_test in4-3.44 { EXPLAIN SELECT * FROM t3 WHERE x IN (10); } {~/OpenEphemeral/} do_execsql_test in4-3.45 { - SELECT * FROM t3 WHERE x NOT IN (10,11); + SELECT * FROM t3 WHERE x NOT IN (10,11,99999); } {1 1 1} do_execsql_test in4-3.46 { EXPLAIN - SELECT * FROM t3 WHERE x NOT IN (10,11); + SELECT * FROM t3 WHERE x NOT IN (10,11,99999); } {/OpenEphemeral/} do_execsql_test in4-3.47 { SELECT * FROM t3 WHERE x NOT IN (10); } {1 1 1} do_execsql_test in4-3.48 { Index: test/multiplex.test ================================================================== --- test/multiplex.test +++ test/multiplex.test @@ -66,10 +66,16 @@ forcedelete [multiplex_name $name-wal $i] } } db close +sqlite3_shutdown +test_sqlite3_log xLog +proc xLog {error_code msg} { + lappend ::log $error_code $msg +} +unset -nocomplain log multiplex_delete test.db multiplex_delete test2.db #------------------------------------------------------------------------- @@ -186,16 +192,20 @@ sqlite3 db2 test2.x db2 close } {} +unset -nocomplain ::log do_test multiplex-2.4.1 { sqlite3_multiplex_shutdown } {SQLITE_MISUSE} do_test multiplex-2.4.2 { execsql { INSERT INTO t1 VALUES(3, randomblob(1100)) } } {} +do_test multiplex-2.4.3 { + set ::log +} {SQLITE_MISUSE {sqlite3_multiplex_shutdown() called while database connections are still open}} do_test multiplex-2.4.4 { file size [multiplex_name test.x 0] } {7168} do_test multiplex-2.4.5 { db close sqlite3 db test.x db eval vacuum @@ -581,7 +591,11 @@ } {SQLITE_OK} } +catch { db close } catch { sqlite3_multiplex_shutdown } +sqlite3_shutdown +test_sqlite3_log +sqlite3_initialize finish_test Index: test/pragma.test ================================================================== --- test/pragma.test +++ test/pragma.test @@ -429,11 +429,36 @@ forcedelete test.db test.db-journal sqlite3 db test.db db eval {PRAGMA integrity_check} } {ok} } -#exit + +# Verify that PRAGMA integrity_check catches UNIQUE and NOT NULL +# constraint violations. +# +do_execsql_test pragma-3.20 { + CREATE TABLE t1(a,b); + CREATE INDEX t1a ON t1(a); + INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(2,4),(NULL,5),(NULL,6); + PRAGMA writable_schema=ON; + UPDATE sqlite_master SET sql='CREATE UNIQUE INDEX t1a ON t1(a)' + WHERE name='t1a'; + UPDATE sqlite_master SET sql='CREATE TABLE t1(a NOT NULL,b)' + WHERE name='t1'; + PRAGMA writable_schema=OFF; + ALTER TABLE t1 RENAME TO t1x; + PRAGMA integrity_check; +} {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a} {NULL value in t1x.a}} +do_execsql_test pragma-3.21 { + PRAGMA integrity_check(3); +} {{non-unique entry in index t1a} {NULL value in t1x.a} {non-unique entry in index t1a}} +do_execsql_test pragma-3.22 { + PRAGMA integrity_check(2); +} {{non-unique entry in index t1a} {NULL value in t1x.a}} +do_execsql_test pragma-3.21 { + PRAGMA integrity_check(1); +} {{non-unique entry in index t1a}} # Test modifying the cache_size of an attached database. ifcapable pager_pragmas&&attach { do_test pragma-4.1 { execsql { Index: test/table.test ================================================================== --- test/table.test +++ test/table.test @@ -723,7 +723,54 @@ for {set i 0} {$i<2000} {incr i} { execsql "DROP TABLE tbl$i" } execsql {COMMIT} } {} + +# Ticket 3a88d85f36704eebe134f7f48aebf00cd6438c1a (2014-08-05) +# The following SQL script segfaults while running the INSERT statement: +# +# CREATE TABLE t1(x DEFAULT(max(1))); +# INSERT INTO t1(rowid) VALUES(1); +# +# The problem appears to be the use of an aggregate function as part of +# the default value for a column. This problem has been in the code since +# at least 2006-01-01 and probably before that. This problem was detected +# and reported on the sqlite-users@sqlite.org mailing list by Zsbán Ambrus. +# +do_execsql_test table-16.1 { + CREATE TABLE t16(x DEFAULT(max(1))); + INSERT INTO t16(x) VALUES(123); + SELECT rowid, x FROM t16; +} {1 123} +do_catchsql_test table-16.2 { + INSERT INTO t16(rowid) VALUES(4); +} {1 {unknown function: max()}} +do_execsql_test table-16.3 { + DROP TABLE t16; + CREATE TABLE t16(x DEFAULT(abs(1))); + INSERT INTO t16(rowid) VALUES(4); + SELECT rowid, x FROM t16; +} {4 1} +do_catchsql_test table-16.4 { + DROP TABLE t16; + CREATE TABLE t16(x DEFAULT(avg(1))); + INSERT INTO t16(rowid) VALUES(123); + SELECT rowid, x FROM t16; +} {1 {unknown function: avg()}} +do_catchsql_test table-16.5 { + DROP TABLE t16; + CREATE TABLE t16(x DEFAULT(count())); + INSERT INTO t16(rowid) VALUES(123); + SELECT rowid, x FROM t16; +} {1 {unknown function: count()}} +do_catchsql_test table-16.6 { + DROP TABLE t16; + CREATE TABLE t16(x DEFAULT(group_concat('x',','))); + INSERT INTO t16(rowid) VALUES(123); + SELECT rowid, x FROM t16; +} {1 {unknown function: group_concat()}} +do_catchsql_test table-16.7 { + INSERT INTO t16 DEFAULT VALUES; +} {1 {unknown function: group_concat()}} finish_test Index: test/tester.tcl ================================================================== --- test/tester.tcl +++ test/tester.tcl @@ -867,10 +867,11 @@ # Run this routine last # proc finish_test {} { catch {db close} + catch {db1 close} catch {db2 close} catch {db3 close} if {0==[info exists ::SLAVE]} { finalize_testing } } proc finalize_testing {} { Index: test/tkt-80e031a00f.test ================================================================== --- test/tkt-80e031a00f.test +++ test/tkt-80e031a00f.test @@ -158,10 +158,14 @@ do_execsql_test tkt-80e031a00f.321 {SELECT 'd' NOT IN t7n} 0 do_execsql_test tkt-80e031a00f.322 {SELECT 'b' IN t8} 1 do_execsql_test tkt-80e031a00f.323 {SELECT 'c' NOT IN t8} 0 do_execsql_test tkt-80e031a00f.324 {SELECT 'c' IN t8n} 1 do_execsql_test tkt-80e031a00f.325 {SELECT 'd' NOT IN t8n} 0 +do_execsql_test tkt-80e031a00f.326 {SELECT 'a' IN (NULL,'a')} 1 +do_execsql_test tkt-80e031a00f.327 {SELECT 'a' IN (NULL,'b')} {{}} +do_execsql_test tkt-80e031a00f.328 {SELECT 'a' NOT IN (NULL,'a')} 0 +do_execsql_test tkt-80e031a00f.329 {SELECT 'a' NOT IN (NULL,'b')} {{}} # # Row 4: do_execsql_test tkt-80e031a00f.400 {SELECT 1 IN (2,3,4,null)} {{}} do_execsql_test tkt-80e031a00f.401 {SELECT 1 NOT IN (2,3,4,null)} {{}} do_execsql_test tkt-80e031a00f.402 {SELECT 'a' IN ('b','c',null,'d')} {{}}