Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -361,11 +361,11 @@ static int allowedOp(int op){ assert( TK_GT>TK_EQ && TK_GTTK_EQ && TK_LTTK_EQ && TK_LE=TK_EQ && op<=TK_GE) || op==TK_ISNULL; + return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL || op==TK_IS; } /* ** Commute a comparison operator. Expressions of the form "X op Y" ** are converted into "Y op X". @@ -414,10 +414,12 @@ assert( allowedOp(op) ); if( op==TK_IN ){ c = WO_IN; }else if( op==TK_ISNULL ){ c = WO_ISNULL; + }else if( op==TK_IS ){ + c = WO_EQ; }else{ assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff ); c = (u16)(WO_EQ<<(op-TK_EQ)); } assert( op!=TK_ISNULL || c==WO_ISNULL ); @@ -425,10 +427,11 @@ assert( op!=TK_EQ || c==WO_EQ ); assert( op!=TK_LT || c==WO_LT ); assert( op!=TK_LE || c==WO_LE ); assert( op!=TK_GT || c==WO_GT ); assert( op!=TK_GE || c==WO_GE ); + assert( op!=TK_IS || c==WO_EQ ); return c; } /* ** Advance to the next WhereTerm that matches according to the criteria @@ -1252,10 +1255,11 @@ if( pLeft->op==TK_COLUMN ){ pTerm->leftCursor = pLeft->iTable; pTerm->u.leftColumn = pLeft->iColumn; pTerm->eOperator = operatorMask(op) & opMask; } + if( op==TK_IS ) pTerm->wtFlags |= TERM_NULLOK; if( pRight && pRight->op==TK_COLUMN ){ WhereTerm *pNew; Expr *pDup; u16 eExtraOp = 0; /* Extra bits for pNew->eOperator */ if( pTerm->leftCursor>=0 ){ @@ -1276,10 +1280,11 @@ && OptimizationEnabled(db, SQLITE_Transitive) ){ pTerm->eOperator |= WO_EQUIV; eExtraOp = WO_EQUIV; } + if( op==TK_IS ) pNew->wtFlags |= TERM_NULLOK; }else{ pDup = pExpr; pNew = pTerm; } exprCommute(pParse, pDup); @@ -1466,14 +1471,12 @@ /* When sqlite_stat3 histogram data is available an operator of the ** form "x IS NOT NULL" can sometimes be evaluated more efficiently ** as "x>NULL" if x is not an INTEGER PRIMARY KEY. So construct a ** virtual term of that form. ** - ** Note that the virtual term must be tagged with TERM_VNULL. This - ** TERM_VNULL tag will suppress the not-null check at the beginning - ** of the loop. Without the TERM_VNULL flag, the not-null check at - ** the start of the loop will prevent any results from being returned. + ** Note that the virtual term must be tagged with both TERM_VNULL + ** and TERM_NULLOK. */ if( pExpr->op==TK_NOTNULL && pExpr->pLeft->op==TK_COLUMN && pExpr->pLeft->iColumn>=0 && OptimizationEnabled(db, SQLITE_Stat34) @@ -1486,11 +1489,11 @@ pNewExpr = sqlite3PExpr(pParse, TK_GT, sqlite3ExprDup(db, pLeft, 0), sqlite3PExpr(pParse, TK_NULL, 0, 0, 0), 0); idxNew = whereClauseInsert(pWC, pNewExpr, - TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL); + TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL|TERM_NULLOK); if( idxNew ){ pNewTerm = &pWC->a[idxNew]; pNewTerm->prereqRight = 0; pNewTerm->leftCursor = pLeft->iTable; pNewTerm->u.leftColumn = pLeft->iColumn; @@ -2790,11 +2793,11 @@ Expr *pX = pTerm->pExpr; Vdbe *v = pParse->pVdbe; int iReg; /* Register holding results */ assert( iTarget>0 ); - if( pX->op==TK_EQ ){ + if( pX->op==TK_EQ || pX->op==TK_IS ){ iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget); }else if( pX->op==TK_ISNULL ){ iReg = iTarget; sqlite3VdbeAddOp2(v, OP_Null, 0, iReg); #ifndef SQLITE_OMIT_SUBQUERY @@ -2975,11 +2978,13 @@ } testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_IN ); if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){ Expr *pRight = pTerm->pExpr->pRight; - if( sqlite3ExprCanBeNull(pRight) ){ + if( (pTerm->wtFlags & TERM_NULLOK)==0 + && sqlite3ExprCanBeNull(pRight) + ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk); VdbeCoverage(v); } if( zAff ){ if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_NONE ){ @@ -3624,11 +3629,11 @@ nConstraint = nEq; if( pRangeStart ){ Expr *pRight = pRangeStart->pExpr->pRight; sqlite3ExprCode(pParse, pRight, regBase+nEq); whereLikeOptimizationStringFixup(v, pLevel, pRangeStart); - if( (pRangeStart->wtFlags & TERM_VNULL)==0 + if( (pRangeStart->wtFlags & TERM_NULLOK)==0 && sqlite3ExprCanBeNull(pRight) ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt); VdbeCoverage(v); } @@ -3670,11 +3675,11 @@ if( pRangeEnd ){ Expr *pRight = pRangeEnd->pExpr->pRight; sqlite3ExprCacheRemove(pParse, regBase+nEq, 1); sqlite3ExprCode(pParse, pRight, regBase+nEq); whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd); - if( (pRangeEnd->wtFlags & TERM_VNULL)==0 + if( (pRangeEnd->wtFlags & TERM_NULLOK)==0 && sqlite3ExprCanBeNull(pRight) ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt); VdbeCoverage(v); } @@ -4156,13 +4161,14 @@ char zType[4]; memcpy(zType, "...", 4); if( pTerm->wtFlags & TERM_VIRTUAL ) zType[0] = 'V'; if( pTerm->eOperator & WO_EQUIV ) zType[1] = 'E'; if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) zType[2] = 'L'; - sqlite3DebugPrintf("TERM-%-3d %p %s cursor=%-3d prob=%-3d op=0x%03x\n", - iTerm, pTerm, zType, pTerm->leftCursor, pTerm->truthProb, - pTerm->eOperator); + sqlite3DebugPrintf( + "TERM-%-3d %p %s cursor=%-3d prob=%-3d op=0x%03x wtFlags=0x%04x\n", + iTerm, pTerm, zType, pTerm->leftCursor, pTerm->truthProb, + pTerm->eOperator, pTerm->wtFlags); sqlite3TreeViewExpr(0, pTerm->pExpr, 0); } } #endif Index: src/whereInt.h ================================================================== --- src/whereInt.h +++ src/whereInt.h @@ -278,10 +278,11 @@ # define TERM_VNULL 0x00 /* Disabled if not using stat3 */ #endif #define TERM_LIKEOPT 0x100 /* Virtual terms from the LIKE optimization */ #define TERM_LIKECOND 0x200 /* Conditionally this LIKE operator term */ #define TERM_LIKE 0x400 /* The original LIKE operator */ +#define TERM_NULLOK 0x800 /* Comparison operators against NULL work */ /* ** An instance of the WhereScan object is used as an iterator for locating ** terms in the WHERE clause that are useful to the query planner. */