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_IS; }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_IS ); return c; } /* ** Advance to the next WhereTerm that matches according to the criteria @@ -485,15 +488,16 @@ if( pColl==0 ) pColl = pParse->db->pDfltColl; if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){ continue; } } - if( (pTerm->eOperator & WO_EQ)!=0 + if( (pTerm->eOperator & (WO_EQ|WO_IS))!=0 && (pX = pTerm->pExpr->pRight)->op==TK_COLUMN && pX->iTable==pScan->aEquiv[0] && pX->iColumn==pScan->aEquiv[1] ){ + testcase( pTerm->eOperator & WO_IS ); continue; } pScan->k = k+1; return pTerm; } @@ -591,13 +595,15 @@ WhereTerm *pResult = 0; WhereTerm *p; WhereScan scan; p = whereScanInit(&scan, pWC, iCur, iColumn, op, pIdx); + op &= WO_EQ|WO_IS; while( p ){ if( (p->prereqRight & notReady)==0 ){ - if( p->prereqRight==0 && (p->eOperator&WO_EQ)!=0 ){ + if( p->prereqRight==0 && (p->eOperator&op)!=0 ){ + testcase( p->eOperator & WO_IS ); return p; } if( pResult==0 ) pResult = p; } p = whereScanNext(&scan); @@ -1252,10 +1258,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_IS; if( pRight && pRight->op==TK_COLUMN ){ WhereTerm *pNew; Expr *pDup; u16 eExtraOp = 0; /* Extra bits for pNew->eOperator */ if( pTerm->leftCursor>=0 ){ @@ -1269,17 +1276,18 @@ if( idxNew==0 ) return; pNew = &pWC->a[idxNew]; markTermAsChild(pWC, idxNew, idxTerm); pTerm = &pWC->a[idxTerm]; pTerm->wtFlags |= TERM_COPIED; - if( pExpr->op==TK_EQ + if( (op==TK_EQ || op==TK_IS) && !ExprHasProperty(pExpr, EP_FromJoin) && OptimizationEnabled(db, SQLITE_Transitive) ){ pTerm->eOperator |= WO_EQUIV; eExtraOp = WO_EQUIV; } + if( op==TK_IS ) pNew->wtFlags |= TERM_IS; }else{ pDup = pExpr; pNew = pTerm; } exprCommute(pParse, pDup); @@ -1466,14 +1474,11 @@ /* 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 TERM_VNULL. */ if( pExpr->op==TK_NOTNULL && pExpr->pLeft->op==TK_COLUMN && pExpr->pLeft->iColumn>=0 && OptimizationEnabled(db, SQLITE_Stat34) @@ -1673,15 +1678,16 @@ struct SrcList_item *pSrc, /* Table we are trying to access */ Bitmask notReady /* Tables in outer loops of the join */ ){ char aff; if( pTerm->leftCursor!=pSrc->iCursor ) return 0; - if( (pTerm->eOperator & WO_EQ)==0 ) return 0; + if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0; if( (pTerm->prereqRight & notReady)!=0 ) return 0; if( pTerm->u.leftColumn<0 ) return 0; aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity; if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0; + testcase( pTerm->pExpr->op==TK_IS ); return 1; } #endif @@ -1894,12 +1900,13 @@ for(i=nTerm=0, pTerm=pWC->a; inTerm; i++, pTerm++){ if( pTerm->leftCursor != pSrc->iCursor ) continue; assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); testcase( pTerm->eOperator & WO_ISNULL ); + testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ALL ); - if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV))==0 ) continue; + if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; nTerm++; } /* If the ORDER BY clause contains only columns in the current @@ -1946,13 +1953,14 @@ for(i=j=0, pTerm=pWC->a; inTerm; i++, pTerm++){ u8 op; if( pTerm->leftCursor != pSrc->iCursor ) continue; assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); + testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_ALL ); - if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV))==0 ) continue; + if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; op = (u8)pTerm->eOperator & WO_ALL; if( op==WO_IN ) op = WO_EQ; @@ -2790,11 +2798,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 +2983,11 @@ } 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_IS)==0 && sqlite3ExprCanBeNull(pRight) ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk); VdbeCoverage(v); } if( zAff ){ if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_NONE ){ @@ -4097,20 +4105,23 @@ */ for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){ Expr *pE, *pEAlt; WhereTerm *pAlt; if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; - if( pTerm->eOperator!=(WO_EQUIV|WO_EQ) ) continue; + if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) continue; + if( (pTerm->eOperator & WO_EQUIV)==0 ) continue; if( pTerm->leftCursor!=iCur ) continue; if( pLevel->iLeftJoin ) continue; pE = pTerm->pExpr; assert( !ExprHasProperty(pE, EP_FromJoin) ); assert( (pTerm->prereqRight & pLevel->notReady)!=0 ); - pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady, WO_EQ|WO_IN, 0); + pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady, + WO_EQ|WO_IN|WO_IS, 0); if( pAlt==0 ) continue; if( pAlt->wtFlags & (TERM_CODED) ) continue; testcase( pAlt->eOperator & WO_EQ ); + testcase( pAlt->eOperator & WO_IS ); testcase( pAlt->eOperator & WO_IN ); VdbeModuleComment((v, "begin transitive constraint")); pEAlt = sqlite3StackAllocRaw(db, sizeof(*pEAlt)); if( pEAlt ){ *pEAlt = *pAlt->pExpr; @@ -4156,13 +4167,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 @@ -4648,12 +4660,13 @@ pLoop->nOut += pTerm->truthProb; }else{ /* In the absence of explicit truth probabilities, use heuristics to ** guess a reasonable truth probability. */ pLoop->nOut--; - if( pTerm->eOperator&WO_EQ ){ + if( pTerm->eOperator&(WO_EQ|WO_IS) ){ Expr *pRight = pTerm->pExpr->pRight; + testcase( pTerm->pExpr->op==TK_IS ); if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){ k = 10; }else{ k = 20; } @@ -4717,14 +4730,14 @@ assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 ); assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 ); if( pNew->wsFlags & WHERE_BTM_LIMIT ){ opMask = WO_LT|WO_LE; - }else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){ + }else if( /*pProbe->tnum<=0 ||*/ (pSrc->jointype & JT_LEFT)!=0 ){ opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE; }else{ - opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE; + opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS; } if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); assert( pNew->u.btree.nEqnColumn ); iCol = pProbe->aiColumn[pNew->u.btree.nEq]; @@ -4783,11 +4796,11 @@ nIn = sqlite3LogEst(pExpr->x.pList->nExpr); } assert( nIn>0 ); /* RHS always has 2 or more terms... The parser ** changes "x IN (?)" into "x=?". */ - }else if( eOp & (WO_EQ) ){ + }else if( eOp & (WO_EQ|WO_IS) ){ pNew->wsFlags |= WHERE_COLUMN_EQ; if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){ if( iCol>=0 && pProbe->uniqNotNull==0 ){ pNew->wsFlags |= WHERE_UNQ_WANTED; }else{ @@ -4833,11 +4846,11 @@ /* Adjust nOut using stat3/stat4 data. Or, if there is no stat3/stat4 ** data, using some other estimate. */ whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew); }else{ int nEq = ++pNew->u.btree.nEq; - assert( eOp & (WO_ISNULL|WO_EQ|WO_IN) ); + assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) ); assert( pNew->nOut==saved_nOut ); if( pTerm->truthProb<=0 && iCol>=0 ){ assert( (eOp & WO_IN) || nIn==0 ); testcase( eOp & WO_IN ); @@ -4850,12 +4863,13 @@ && pProbe->nSample && pNew->u.btree.nEq<=pProbe->nSampleCol && ((eOp & WO_IN)==0 || !ExprHasProperty(pTerm->pExpr, EP_xIsSelect)) ){ Expr *pExpr = pTerm->pExpr; - if( (eOp & (WO_EQ|WO_ISNULL))!=0 ){ + if( (eOp & (WO_EQ|WO_ISNULL|WO_IS))!=0 ){ testcase( eOp & WO_EQ ); + testcase( eOp & WO_IS ); testcase( eOp & WO_ISNULL ); rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut); }else{ rc = whereInScanEst(pParse, pBuilder, pExpr->x.pList, &nOut); } @@ -5688,21 +5702,22 @@ if( MASKBIT(i) & obSat ) continue; pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr); if( pOBExpr->op!=TK_COLUMN ) continue; if( pOBExpr->iTable!=iCur ) continue; pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn, - ~ready, WO_EQ|WO_ISNULL, 0); + ~ready, WO_EQ|WO_ISNULL|WO_IS, 0); if( pTerm==0 ) continue; - if( (pTerm->eOperator&WO_EQ)!=0 && pOBExpr->iColumn>=0 ){ + if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){ const char *z1, *z2; pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr); if( !pColl ) pColl = db->pDfltColl; z1 = pColl->zName; pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr); if( !pColl ) pColl = db->pDfltColl; z2 = pColl->zName; if( sqlite3StrICmp(z1, z2)!=0 ) continue; + testcase( pTerm->pExpr->op==TK_IS ); } obSat |= MASKBIT(i); } if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){ @@ -5729,11 +5744,11 @@ u8 bOnce; /* True to run the ORDER BY search loop */ /* Skip over == and IS NULL terms */ if( ju.btree.nEq && pLoop->nSkip==0 - && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0 + && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL|WO_IS))!=0 ){ if( i & WO_ISNULL ){ testcase( isOrderDistinct ); isOrderDistinct = 0; } @@ -6302,12 +6317,13 @@ iCur = pItem->iCursor; pWC = &pWInfo->sWC; pLoop = pBuilder->pNew; pLoop->wsFlags = 0; pLoop->nSkip = 0; - pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ, 0); + pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IS, 0); if( pTerm ){ + testcase( pTerm->eOperator & WO_IS ); pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW; pLoop->aLTerm[0] = pTerm; pLoop->nLTerm = 1; pLoop->u.btree.nEq = 1; /* TUNING: Cost of a rowid lookup is 10 */ @@ -6318,12 +6334,13 @@ 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); + pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ|WO_IS, pIdx); if( pTerm==0 ) break; + testcase( pTerm->eOperator & WO_IS ); pLoop->aLTerm[j] = pTerm; } if( j!=pIdx->nKeyCol ) continue; pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED; if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){ 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_IS 0x800 /* Term.pExpr is an IS operator */ /* ** 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. */ @@ -426,25 +427,26 @@ ** Bitmasks for the operators on WhereTerm objects. These are all ** operators that are of interest to the query planner. An ** OR-ed combination of these values can be used when searching for ** particular WhereTerms within a WhereClause. */ -#define WO_IN 0x001 -#define WO_EQ 0x002 +#define WO_IN 0x0001 +#define WO_EQ 0x0002 #define WO_LT (WO_EQ<<(TK_LT-TK_EQ)) #define WO_LE (WO_EQ<<(TK_LE-TK_EQ)) #define WO_GT (WO_EQ<<(TK_GT-TK_EQ)) #define WO_GE (WO_EQ<<(TK_GE-TK_EQ)) -#define WO_MATCH 0x040 -#define WO_ISNULL 0x080 -#define WO_OR 0x100 /* Two or more OR-connected terms */ -#define WO_AND 0x200 /* Two or more AND-connected terms */ -#define WO_EQUIV 0x400 /* Of the form A==B, both columns */ -#define WO_NOOP 0x800 /* This term does not restrict search space */ - -#define WO_ALL 0xfff /* Mask of all possible WO_* values */ -#define WO_SINGLE 0x0ff /* Mask of all non-compound WO_* values */ +#define WO_MATCH 0x0040 +#define WO_IS 0x0080 +#define WO_ISNULL 0x0100 +#define WO_OR 0x0200 /* Two or more OR-connected terms */ +#define WO_AND 0x0400 /* Two or more AND-connected terms */ +#define WO_EQUIV 0x0800 /* Of the form A==B, both columns */ +#define WO_NOOP 0x1000 /* This term does not restrict search space */ + +#define WO_ALL 0x1fff /* Mask of all possible WO_* values */ +#define WO_SINGLE 0x01ff /* Mask of all non-compound WO_* values */ /* ** These are definitions of bits in the WhereLoop.wsFlags field. ** The particular combination of bits in each WhereLoop help to ** determine the algorithm that WhereLoop represents. Index: test/transitive1.test ================================================================== --- test/transitive1.test +++ test/transitive1.test @@ -63,10 +63,16 @@ do_execsql_test transitive1-301 { SELECT * FROM t301 CROSS JOIN t302 WHERE w=y AND y IS NOT NULL ORDER BY w; +} {1 2 1 3 3 4 3 6 5 6 5 7} +do_execsql_test transitive1-302 { + SELECT * + FROM t301 CROSS JOIN t302 + WHERE w IS y AND y IS NOT NULL + ORDER BY w; } {1 2 1 3 3 4 3 6 5 6 5 7} do_execsql_test transitive1-310 { SELECT * FROM t301 CROSS JOIN t302 ON w=y WHERE y>1 @@ -101,11 +107,11 @@ WHERE y BETWEEN 1 AND 4 ORDER BY w DESC; } {3 4 3 6 1 2 1 3} # Ticket [c620261b5b5dc] circa 2013-10-28. -# Make sureconstraints are not used with LEFT JOINs. +# Make sure constraints are not used with LEFT JOINs. # # The next case is from the ticket report. It outputs no rows in 3.8.1 # prior to the bug-fix. # do_execsql_test transitive1-400 { @@ -114,10 +120,20 @@ CREATE TABLE t403(c INTEGER PRIMARY KEY); INSERT INTO t401 VALUES(1); INSERT INTO t403 VALUES(1); SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c=a; } {1-row} +do_execsql_test transitive1-401 { + SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a; +} {1-row} +do_execsql_test transitive1-402 { + SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a; +} {1-row} +do_execsql_test transitive1-403 { + SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a; +} {1-row} + # The following is a script distilled from the XBMC project where the # bug was originally encountered. The correct answer is a single row # of output. Before the bug was fixed, zero rows were generated. # Index: test/vtab1.test ================================================================== --- test/vtab1.test +++ test/vtab1.test @@ -1083,14 +1083,25 @@ execsql { INSERT INTO c VALUES(15, NULL, 16); SELECT * FROM echo_c WHERE b IS NULL } } {15 {} 16} -do_test vtab1.13-3 { +do_test vtab1.13-4 { + unset -nocomplain null + execsql { + SELECT * FROM echo_c WHERE b IS $null + } +} {15 {} 16} +do_test vtab1.13-5 { execsql { SELECT * FROM echo_c WHERE b IS NULL AND a = 15; } +} {15 {} 16} +do_test vtab1.13-6 { + execsql { + SELECT * FROM echo_c WHERE NULL IS b AND a IS 15; + } } {15 {} 16} do_test vtab1-14.001 { execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (1,2,3)} Index: test/vtab2.test ================================================================== --- test/vtab2.test +++ test/vtab2.test @@ -102,10 +102,18 @@ SELECT *, b.rowid FROM schema a LEFT JOIN schema b ON a.dflt_value=b.dflt_value WHERE a.rowid=1 } } {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}} +do_test vtab2-3.3 { + execsql { + SELECT *, b.rowid + FROM schema a LEFT JOIN schema b ON a.dflt_value IS b.dflt_value + AND a.dflt_value IS NOT NULL + WHERE a.rowid=1 + } +} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}} do_test vtab2-4.1 { execsql { BEGIN TRANSACTION; CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); @@ -151,6 +159,5 @@ } finish_test - Index: test/vtab6.test ================================================================== --- test/vtab6.test +++ test/vtab6.test @@ -230,10 +230,15 @@ } {1 {RIGHT and FULL OUTER JOINs are not currently supported}} do_test vtab6-2.4 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d } +} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} +do_test vtab6-2.4.1 { + execsql { + SELECT * FROM t1 LEFT JOIN t2 ON t1.a IS t2.d + } } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} do_test vtab6-2.5 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 } Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -63,13 +63,19 @@ # small we can be assured that indices are being used properly. # do_test where-1.1.1 { count {SELECT x, y, w FROM t1 WHERE w=10} } {3 121 10 3} +do_test where-1.1.1b { + count {SELECT x, y, w FROM t1 WHERE w IS 10} +} {3 121 10 3} do_eqp_test where-1.1.2 { SELECT x, y, w FROM t1 WHERE w=10 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} +do_eqp_test where-1.1.2b { + SELECT x, y, w FROM t1 WHERE w IS 10 +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.1.3 { db status step } {0} do_test where-1.1.4 { db eval {SELECT x, y, w FROM t1 WHERE +w=10} @@ -99,16 +105,25 @@ count {SELECT x, y, w AS abc FROM t1 WHERE 11=w} } {3 144 11 3} do_test where-1.3.2 { count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc} } {3 144 11 3} +do_test where-1.3.3 { + count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc} +} {3 144 11 3} do_test where-1.4.1 { count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2} } {11 3 144 3} +do_test where-1.4.1b { + count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2} +} {11 3 144 3} do_eqp_test where-1.4.2 { SELECT w, x, y FROM t1 WHERE 11=w AND x>2 } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} +do_eqp_test where-1.4.2b { + SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2 +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} do_test where-1.4.3 { count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2} } {11 3 144 3} do_eqp_test where-1.4.4 { SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2 @@ -141,23 +156,32 @@ count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} } {3 121 3} do_test where-1.11 { count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10} } {3 100 3} +do_test where-1.11b { + count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10} +} {3 100 3} # New for SQLite version 2.1: Verify that that inequality constraints # are used correctly. # do_test where-1.12 { count {SELECT w FROM t1 WHERE x=3 AND y<100} } {8 3} +do_test where-1.12b { + count {SELECT w FROM t1 WHERE x IS 3 AND y<100} +} {8 3} do_test where-1.13 { count {SELECT w FROM t1 WHERE x=3 AND 100>y} } {8 3} do_test where-1.14 { count {SELECT w FROM t1 WHERE 3=x AND y<100} } {8 3} +do_test where-1.14b { + count {SELECT w FROM t1 WHERE 3 IS x AND y<100} +} {8 3} do_test where-1.15 { count {SELECT w FROM t1 WHERE 3=x AND 100>y} } {8 3} do_test where-1.16 { count {SELECT w FROM t1 WHERE x=3 AND y<=100} @@ -166,10 +190,13 @@ count {SELECT w FROM t1 WHERE x=3 AND 100>=y} } {8 9 5} do_test where-1.18 { count {SELECT w FROM t1 WHERE x=3 AND y>225} } {15 3} +do_test where-1.18b { + count {SELECT w FROM t1 WHERE x IS 3 AND y>225} +} {15 3} do_test where-1.19 { count {SELECT w FROM t1 WHERE x=3 AND 225=225} @@ -178,10 +205,13 @@ count {SELECT w FROM t1 WHERE x=3 AND 225<=y} } {14 15 5} do_test where-1.22 { count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196} } {11 12 5} +do_test where-1.22b { + count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196} +} {11 12 5} do_test where-1.23 { count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196} } {10 11 12 13 9} do_test where-1.24 { count {SELECT w FROM t1 WHERE x=3 AND 121y} Index: test/where4.test ================================================================== --- test/where4.test +++ test/where4.test @@ -55,10 +55,14 @@ # small we can be assured that indices are being used properly. # do_test where4-1.1 { count {SELECT rowid FROM t1 WHERE w IS NULL} } {7 2} +do_test where4-1.1b { + unset -nocomplain null + count {SELECT rowid FROM t1 WHERE w IS $null} +} {7 2} do_test where4-1.2 { count {SELECT rowid FROM t1 WHERE +w IS NULL} } {7 6} do_test where4-1.3 { count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL} @@ -140,10 +144,21 @@ } {2 2 {} 3 {} {}} do_test where4-3.2 { execsql { SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL; } +} {2 2 {} 3 {} {}} +do_test where4-3.3 { + execsql { + SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE NULL is y; + } +} {2 2 {} 3 {} {}} +do_test where4-3.4 { + unset -nocomplain null + execsql { + SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS $null; + } } {2 2 {} 3 {} {}} # Ticket #2189. Probably the same bug as #2177. # do_test where4-4.1 { Index: test/whereC.test ================================================================== --- test/whereC.test +++ test/whereC.test @@ -57,10 +57,11 @@ 10 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 11" {10 11} 11 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10" {} 12 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i=NULL" {} 14 "SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5" {3 4} + 15 "SELECT i FROM t1 WHERE rowid IS '12'" {12} } { do_execsql_test 1.$tn.1 $sql $res do_execsql_test 1.$tn.2 "$sql ORDER BY i ASC" [lsort -integer -inc $res] do_execsql_test 1.$tn.3 "$sql ORDER BY i DESC" [lsort -integer -dec $res] }