/ Check-in [3428043c]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:The IS operator can now use indexes the same as the == operator.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3428043cd00294457548bb07ada2ad526b6532d6
User & Date: drh 2015-05-14 14:32:11
References
2017-07-18
16:57 New ticket [ce68383b] Incorrect LEFT JOIN result with IS operator in WHERE clause and auto-index. artifact: 4776331e user: drh
Context
2015-05-14
15:39
Increase the version number to 3.8.11. Upgrade autoconf from 2.62 to 2.69. check-in: be438d04 user: drh tags: trunk
15:17
Merge in the IS-operator enhancements and other recent changes from trunk. check-in: 4f922944 user: drh tags: sessions
14:32
The IS operator can now use indexes the same as the == operator. check-in: 3428043c user: drh tags: trunk
14:03
A few more test cases for the IS operator. Closed-Leaf check-in: f397c862 user: drh tags: index-is-operator
2015-05-13
04:50
Enhancements to the MSVC makefile. check-in: 59e3e9e7 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   359    359   ** "=", "<", ">", "<=", ">=", "IN", and "IS NULL"
   360    360   */
   361    361   static int allowedOp(int op){
   362    362     assert( TK_GT>TK_EQ && TK_GT<TK_GE );
   363    363     assert( TK_LT>TK_EQ && TK_LT<TK_GE );
   364    364     assert( TK_LE>TK_EQ && TK_LE<TK_GE );
   365    365     assert( TK_GE==TK_EQ+4 );
   366         -  return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL;
          366  +  return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL || op==TK_IS;
   367    367   }
   368    368   
   369    369   /*
   370    370   ** Commute a comparison operator.  Expressions of the form "X op Y"
   371    371   ** are converted into "Y op X".
   372    372   **
   373    373   ** If left/right precedence rules come into play when determining the
................................................................................
   412    412   static u16 operatorMask(int op){
   413    413     u16 c;
   414    414     assert( allowedOp(op) );
   415    415     if( op==TK_IN ){
   416    416       c = WO_IN;
   417    417     }else if( op==TK_ISNULL ){
   418    418       c = WO_ISNULL;
          419  +  }else if( op==TK_IS ){
          420  +    c = WO_IS;
   419    421     }else{
   420    422       assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff );
   421    423       c = (u16)(WO_EQ<<(op-TK_EQ));
   422    424     }
   423    425     assert( op!=TK_ISNULL || c==WO_ISNULL );
   424    426     assert( op!=TK_IN || c==WO_IN );
   425    427     assert( op!=TK_EQ || c==WO_EQ );
   426    428     assert( op!=TK_LT || c==WO_LT );
   427    429     assert( op!=TK_LE || c==WO_LE );
   428    430     assert( op!=TK_GT || c==WO_GT );
   429    431     assert( op!=TK_GE || c==WO_GE );
          432  +  assert( op!=TK_IS || c==WO_IS );
   430    433     return c;
   431    434   }
   432    435   
   433    436   /*
   434    437   ** Advance to the next WhereTerm that matches according to the criteria
   435    438   ** established when the pScan object was initialized by whereScanInit().
   436    439   ** Return NULL if there are no more matching WhereTerms.
................................................................................
   483    486                 pColl = sqlite3BinaryCompareCollSeq(pParse,
   484    487                                                     pX->pLeft, pX->pRight);
   485    488                 if( pColl==0 ) pColl = pParse->db->pDfltColl;
   486    489                 if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){
   487    490                   continue;
   488    491                 }
   489    492               }
   490         -            if( (pTerm->eOperator & WO_EQ)!=0
          493  +            if( (pTerm->eOperator & (WO_EQ|WO_IS))!=0
   491    494                && (pX = pTerm->pExpr->pRight)->op==TK_COLUMN
   492    495                && pX->iTable==pScan->aEquiv[0]
   493    496                && pX->iColumn==pScan->aEquiv[1]
   494    497               ){
          498  +              testcase( pTerm->eOperator & WO_IS );
   495    499                 continue;
   496    500               }
   497    501               pScan->k = k+1;
   498    502               return pTerm;
   499    503             }
   500    504           }
   501    505         }
................................................................................
   589    593     Index *pIdx           /* Must be compatible with this index, if not NULL */
   590    594   ){
   591    595     WhereTerm *pResult = 0;
   592    596     WhereTerm *p;
   593    597     WhereScan scan;
   594    598   
   595    599     p = whereScanInit(&scan, pWC, iCur, iColumn, op, pIdx);
          600  +  op &= WO_EQ|WO_IS;
   596    601     while( p ){
   597    602       if( (p->prereqRight & notReady)==0 ){
   598         -      if( p->prereqRight==0 && (p->eOperator&WO_EQ)!=0 ){
          603  +      if( p->prereqRight==0 && (p->eOperator&op)!=0 ){
          604  +        testcase( p->eOperator & WO_IS );
   599    605           return p;
   600    606         }
   601    607         if( pResult==0 ) pResult = p;
   602    608       }
   603    609       p = whereScanNext(&scan);
   604    610     }
   605    611     return pResult;
................................................................................
  1250   1256       Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight);
  1251   1257       u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV;
  1252   1258       if( pLeft->op==TK_COLUMN ){
  1253   1259         pTerm->leftCursor = pLeft->iTable;
  1254   1260         pTerm->u.leftColumn = pLeft->iColumn;
  1255   1261         pTerm->eOperator = operatorMask(op) & opMask;
  1256   1262       }
         1263  +    if( op==TK_IS ) pTerm->wtFlags |= TERM_IS;
  1257   1264       if( pRight && pRight->op==TK_COLUMN ){
  1258   1265         WhereTerm *pNew;
  1259   1266         Expr *pDup;
  1260   1267         u16 eExtraOp = 0;        /* Extra bits for pNew->eOperator */
  1261   1268         if( pTerm->leftCursor>=0 ){
  1262   1269           int idxNew;
  1263   1270           pDup = sqlite3ExprDup(db, pExpr, 0);
................................................................................
  1267   1274           }
  1268   1275           idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
  1269   1276           if( idxNew==0 ) return;
  1270   1277           pNew = &pWC->a[idxNew];
  1271   1278           markTermAsChild(pWC, idxNew, idxTerm);
  1272   1279           pTerm = &pWC->a[idxTerm];
  1273   1280           pTerm->wtFlags |= TERM_COPIED;
  1274         -        if( pExpr->op==TK_EQ
         1281  +        if( (op==TK_EQ || op==TK_IS)
  1275   1282            && !ExprHasProperty(pExpr, EP_FromJoin)
  1276   1283            && OptimizationEnabled(db, SQLITE_Transitive)
  1277   1284           ){
  1278   1285             pTerm->eOperator |= WO_EQUIV;
  1279   1286             eExtraOp = WO_EQUIV;
  1280   1287           }
         1288  +        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
  1281   1289         }else{
  1282   1290           pDup = pExpr;
  1283   1291           pNew = pTerm;
  1284   1292         }
  1285   1293         exprCommute(pParse, pDup);
  1286   1294         pLeft = sqlite3ExprSkipCollate(pDup->pLeft);
  1287   1295         pNew->leftCursor = pLeft->iTable;
................................................................................
  1464   1472   
  1465   1473   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  1466   1474     /* When sqlite_stat3 histogram data is available an operator of the
  1467   1475     ** form "x IS NOT NULL" can sometimes be evaluated more efficiently
  1468   1476     ** as "x>NULL" if x is not an INTEGER PRIMARY KEY.  So construct a
  1469   1477     ** virtual term of that form.
  1470   1478     **
  1471         -  ** Note that the virtual term must be tagged with TERM_VNULL.  This
  1472         -  ** TERM_VNULL tag will suppress the not-null check at the beginning
  1473         -  ** of the loop.  Without the TERM_VNULL flag, the not-null check at
  1474         -  ** the start of the loop will prevent any results from being returned.
         1479  +  ** Note that the virtual term must be tagged with TERM_VNULL.
  1475   1480     */
  1476   1481     if( pExpr->op==TK_NOTNULL
  1477   1482      && pExpr->pLeft->op==TK_COLUMN
  1478   1483      && pExpr->pLeft->iColumn>=0
  1479   1484      && OptimizationEnabled(db, SQLITE_Stat34)
  1480   1485     ){
  1481   1486       Expr *pNewExpr;
................................................................................
  1671   1676   static int termCanDriveIndex(
  1672   1677     WhereTerm *pTerm,              /* WHERE clause term to check */
  1673   1678     struct SrcList_item *pSrc,     /* Table we are trying to access */
  1674   1679     Bitmask notReady               /* Tables in outer loops of the join */
  1675   1680   ){
  1676   1681     char aff;
  1677   1682     if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
  1678         -  if( (pTerm->eOperator & WO_EQ)==0 ) return 0;
         1683  +  if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
  1679   1684     if( (pTerm->prereqRight & notReady)!=0 ) return 0;
  1680   1685     if( pTerm->u.leftColumn<0 ) return 0;
  1681   1686     aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
  1682   1687     if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
         1688  +  testcase( pTerm->pExpr->op==TK_IS );
  1683   1689     return 1;
  1684   1690   }
  1685   1691   #endif
  1686   1692   
  1687   1693   
  1688   1694   #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
  1689   1695   /*
................................................................................
  1892   1898     /* Count the number of possible WHERE clause constraints referring
  1893   1899     ** to this virtual table */
  1894   1900     for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
  1895   1901       if( pTerm->leftCursor != pSrc->iCursor ) continue;
  1896   1902       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
  1897   1903       testcase( pTerm->eOperator & WO_IN );
  1898   1904       testcase( pTerm->eOperator & WO_ISNULL );
         1905  +    testcase( pTerm->eOperator & WO_IS );
  1899   1906       testcase( pTerm->eOperator & WO_ALL );
  1900         -    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV))==0 ) continue;
         1907  +    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
  1901   1908       if( pTerm->wtFlags & TERM_VNULL ) continue;
  1902   1909       nTerm++;
  1903   1910     }
  1904   1911   
  1905   1912     /* If the ORDER BY clause contains only columns in the current 
  1906   1913     ** virtual table then allocate space for the aOrderBy part of
  1907   1914     ** the sqlite3_index_info structure.
................................................................................
  1944   1951                                                                      pUsage;
  1945   1952   
  1946   1953     for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
  1947   1954       u8 op;
  1948   1955       if( pTerm->leftCursor != pSrc->iCursor ) continue;
  1949   1956       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
  1950   1957       testcase( pTerm->eOperator & WO_IN );
         1958  +    testcase( pTerm->eOperator & WO_IS );
  1951   1959       testcase( pTerm->eOperator & WO_ISNULL );
  1952   1960       testcase( pTerm->eOperator & WO_ALL );
  1953         -    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV))==0 ) continue;
         1961  +    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
  1954   1962       if( pTerm->wtFlags & TERM_VNULL ) continue;
  1955   1963       pIdxCons[j].iColumn = pTerm->u.leftColumn;
  1956   1964       pIdxCons[j].iTermOffset = i;
  1957   1965       op = (u8)pTerm->eOperator & WO_ALL;
  1958   1966       if( op==WO_IN ) op = WO_EQ;
  1959   1967       pIdxCons[j].op = op;
  1960   1968       /* The direct assignment in the previous line is possible only because
................................................................................
  2788   2796     int iTarget         /* Attempt to leave results in this register */
  2789   2797   ){
  2790   2798     Expr *pX = pTerm->pExpr;
  2791   2799     Vdbe *v = pParse->pVdbe;
  2792   2800     int iReg;                  /* Register holding results */
  2793   2801   
  2794   2802     assert( iTarget>0 );
  2795         -  if( pX->op==TK_EQ ){
         2803  +  if( pX->op==TK_EQ || pX->op==TK_IS ){
  2796   2804       iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);
  2797   2805     }else if( pX->op==TK_ISNULL ){
  2798   2806       iReg = iTarget;
  2799   2807       sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
  2800   2808   #ifndef SQLITE_OMIT_SUBQUERY
  2801   2809     }else{
  2802   2810       int eType;
................................................................................
  2973   2981           sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
  2974   2982         }
  2975   2983       }
  2976   2984       testcase( pTerm->eOperator & WO_ISNULL );
  2977   2985       testcase( pTerm->eOperator & WO_IN );
  2978   2986       if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
  2979   2987         Expr *pRight = pTerm->pExpr->pRight;
  2980         -      if( sqlite3ExprCanBeNull(pRight) ){
         2988  +      if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){
  2981   2989           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
  2982   2990           VdbeCoverage(v);
  2983   2991         }
  2984   2992         if( zAff ){
  2985   2993           if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_NONE ){
  2986   2994             zAff[j] = SQLITE_AFF_NONE;
  2987   2995           }
................................................................................
  4095   4103     ** then we cannot use the "t1.a=t2.b" constraint, but we can code
  4096   4104     ** the implied "t1.a=123" constraint.
  4097   4105     */
  4098   4106     for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
  4099   4107       Expr *pE, *pEAlt;
  4100   4108       WhereTerm *pAlt;
  4101   4109       if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  4102         -    if( pTerm->eOperator!=(WO_EQUIV|WO_EQ) ) continue;
         4110  +    if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) continue;
         4111  +    if( (pTerm->eOperator & WO_EQUIV)==0 ) continue;
  4103   4112       if( pTerm->leftCursor!=iCur ) continue;
  4104   4113       if( pLevel->iLeftJoin ) continue;
  4105   4114       pE = pTerm->pExpr;
  4106   4115       assert( !ExprHasProperty(pE, EP_FromJoin) );
  4107   4116       assert( (pTerm->prereqRight & pLevel->notReady)!=0 );
  4108         -    pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady, WO_EQ|WO_IN, 0);
         4117  +    pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady,
         4118  +                    WO_EQ|WO_IN|WO_IS, 0);
  4109   4119       if( pAlt==0 ) continue;
  4110   4120       if( pAlt->wtFlags & (TERM_CODED) ) continue;
  4111   4121       testcase( pAlt->eOperator & WO_EQ );
         4122  +    testcase( pAlt->eOperator & WO_IS );
  4112   4123       testcase( pAlt->eOperator & WO_IN );
  4113   4124       VdbeModuleComment((v, "begin transitive constraint"));
  4114   4125       pEAlt = sqlite3StackAllocRaw(db, sizeof(*pEAlt));
  4115   4126       if( pEAlt ){
  4116   4127         *pEAlt = *pAlt->pExpr;
  4117   4128         pEAlt->pLeft = pE->pLeft;
  4118   4129         sqlite3ExprIfFalse(pParse, pEAlt, addrCont, SQLITE_JUMPIFNULL);
................................................................................
  4154   4165       sqlite3DebugPrintf("TERM-%-3d NULL\n", iTerm);
  4155   4166     }else{
  4156   4167       char zType[4];
  4157   4168       memcpy(zType, "...", 4);
  4158   4169       if( pTerm->wtFlags & TERM_VIRTUAL ) zType[0] = 'V';
  4159   4170       if( pTerm->eOperator & WO_EQUIV  ) zType[1] = 'E';
  4160   4171       if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) zType[2] = 'L';
  4161         -    sqlite3DebugPrintf("TERM-%-3d %p %s cursor=%-3d prob=%-3d op=0x%03x\n",
  4162         -                       iTerm, pTerm, zType, pTerm->leftCursor, pTerm->truthProb,
  4163         -                       pTerm->eOperator);
         4172  +    sqlite3DebugPrintf(
         4173  +       "TERM-%-3d %p %s cursor=%-3d prob=%-3d op=0x%03x wtFlags=0x%04x\n",
         4174  +       iTerm, pTerm, zType, pTerm->leftCursor, pTerm->truthProb,
         4175  +       pTerm->eOperator, pTerm->wtFlags);
  4164   4176       sqlite3TreeViewExpr(0, pTerm->pExpr, 0);
  4165   4177     }
  4166   4178   }
  4167   4179   #endif
  4168   4180   
  4169   4181   #ifdef WHERETRACE_ENABLED
  4170   4182   /*
................................................................................
  4646   4658           /* If a truth probability is specified using the likelihood() hints,
  4647   4659           ** then use the probability provided by the application. */
  4648   4660           pLoop->nOut += pTerm->truthProb;
  4649   4661         }else{
  4650   4662           /* In the absence of explicit truth probabilities, use heuristics to
  4651   4663           ** guess a reasonable truth probability. */
  4652   4664           pLoop->nOut--;
  4653         -        if( pTerm->eOperator&WO_EQ ){
         4665  +        if( pTerm->eOperator&(WO_EQ|WO_IS) ){
  4654   4666             Expr *pRight = pTerm->pExpr->pRight;
         4667  +          testcase( pTerm->pExpr->op==TK_IS );
  4655   4668             if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){
  4656   4669               k = 10;
  4657   4670             }else{
  4658   4671               k = 20;
  4659   4672             }
  4660   4673             if( iReduce<k ) iReduce = k;
  4661   4674           }
................................................................................
  4715   4728     pNew = pBuilder->pNew;
  4716   4729     if( db->mallocFailed ) return SQLITE_NOMEM;
  4717   4730   
  4718   4731     assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
  4719   4732     assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
  4720   4733     if( pNew->wsFlags & WHERE_BTM_LIMIT ){
  4721   4734       opMask = WO_LT|WO_LE;
  4722         -  }else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
         4735  +  }else if( /*pProbe->tnum<=0 ||*/ (pSrc->jointype & JT_LEFT)!=0 ){
  4723   4736       opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
  4724   4737     }else{
  4725         -    opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE;
         4738  +    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
  4726   4739     }
  4727   4740     if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
  4728   4741   
  4729   4742     assert( pNew->u.btree.nEq<pProbe->nColumn );
  4730   4743     iCol = pProbe->aiColumn[pNew->u.btree.nEq];
  4731   4744   
  4732   4745     pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
................................................................................
  4781   4794         }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  4782   4795           /* "x IN (value, value, ...)" */
  4783   4796           nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
  4784   4797         }
  4785   4798         assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
  4786   4799                           ** changes "x IN (?)" into "x=?". */
  4787   4800   
  4788         -    }else if( eOp & (WO_EQ) ){
         4801  +    }else if( eOp & (WO_EQ|WO_IS) ){
  4789   4802         pNew->wsFlags |= WHERE_COLUMN_EQ;
  4790   4803         if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
  4791   4804           if( iCol>=0 && pProbe->uniqNotNull==0 ){
  4792   4805             pNew->wsFlags |= WHERE_UNQ_WANTED;
  4793   4806           }else{
  4794   4807             pNew->wsFlags |= WHERE_ONEROW;
  4795   4808           }
................................................................................
  4831   4844       assert( pNew->nOut==saved_nOut );
  4832   4845       if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
  4833   4846         /* Adjust nOut using stat3/stat4 data. Or, if there is no stat3/stat4
  4834   4847         ** data, using some other estimate.  */
  4835   4848         whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew);
  4836   4849       }else{
  4837   4850         int nEq = ++pNew->u.btree.nEq;
  4838         -      assert( eOp & (WO_ISNULL|WO_EQ|WO_IN) );
         4851  +      assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) );
  4839   4852   
  4840   4853         assert( pNew->nOut==saved_nOut );
  4841   4854         if( pTerm->truthProb<=0 && iCol>=0 ){
  4842   4855           assert( (eOp & WO_IN) || nIn==0 );
  4843   4856           testcase( eOp & WO_IN );
  4844   4857           pNew->nOut += pTerm->truthProb;
  4845   4858           pNew->nOut -= nIn;
................................................................................
  4848   4861           tRowcnt nOut = 0;
  4849   4862           if( nInMul==0 
  4850   4863            && pProbe->nSample 
  4851   4864            && pNew->u.btree.nEq<=pProbe->nSampleCol
  4852   4865            && ((eOp & WO_IN)==0 || !ExprHasProperty(pTerm->pExpr, EP_xIsSelect))
  4853   4866           ){
  4854   4867             Expr *pExpr = pTerm->pExpr;
  4855         -          if( (eOp & (WO_EQ|WO_ISNULL))!=0 ){
         4868  +          if( (eOp & (WO_EQ|WO_ISNULL|WO_IS))!=0 ){
  4856   4869               testcase( eOp & WO_EQ );
         4870  +            testcase( eOp & WO_IS );
  4857   4871               testcase( eOp & WO_ISNULL );
  4858   4872               rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut);
  4859   4873             }else{
  4860   4874               rc = whereInScanEst(pParse, pBuilder, pExpr->x.pList, &nOut);
  4861   4875             }
  4862   4876             if( rc==SQLITE_NOTFOUND ) rc = SQLITE_OK;
  4863   4877             if( rc!=SQLITE_OK ) break;          /* Jump out of the pTerm loop */
................................................................................
  5686   5700       */
  5687   5701       for(i=0; i<nOrderBy; i++){
  5688   5702         if( MASKBIT(i) & obSat ) continue;
  5689   5703         pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
  5690   5704         if( pOBExpr->op!=TK_COLUMN ) continue;
  5691   5705         if( pOBExpr->iTable!=iCur ) continue;
  5692   5706         pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
  5693         -                       ~ready, WO_EQ|WO_ISNULL, 0);
         5707  +                       ~ready, WO_EQ|WO_ISNULL|WO_IS, 0);
  5694   5708         if( pTerm==0 ) continue;
  5695         -      if( (pTerm->eOperator&WO_EQ)!=0 && pOBExpr->iColumn>=0 ){
         5709  +      if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
  5696   5710           const char *z1, *z2;
  5697   5711           pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  5698   5712           if( !pColl ) pColl = db->pDfltColl;
  5699   5713           z1 = pColl->zName;
  5700   5714           pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
  5701   5715           if( !pColl ) pColl = db->pDfltColl;
  5702   5716           z2 = pColl->zName;
  5703   5717           if( sqlite3StrICmp(z1, z2)!=0 ) continue;
         5718  +        testcase( pTerm->pExpr->op==TK_IS );
  5704   5719         }
  5705   5720         obSat |= MASKBIT(i);
  5706   5721       }
  5707   5722   
  5708   5723       if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
  5709   5724         if( pLoop->wsFlags & WHERE_IPK ){
  5710   5725           pIndex = 0;
................................................................................
  5727   5742         distinctColumns = 0;
  5728   5743         for(j=0; j<nColumn; j++){
  5729   5744           u8 bOnce;   /* True to run the ORDER BY search loop */
  5730   5745   
  5731   5746           /* Skip over == and IS NULL terms */
  5732   5747           if( j<pLoop->u.btree.nEq
  5733   5748            && pLoop->nSkip==0
  5734         -         && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0
         5749  +         && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL|WO_IS))!=0
  5735   5750           ){
  5736   5751             if( i & WO_ISNULL ){
  5737   5752               testcase( isOrderDistinct );
  5738   5753               isOrderDistinct = 0;
  5739   5754             }
  5740   5755             continue;  
  5741   5756           }
................................................................................
  6300   6315     if( IsVirtual(pTab) ) return 0;
  6301   6316     if( pItem->zIndex ) return 0;
  6302   6317     iCur = pItem->iCursor;
  6303   6318     pWC = &pWInfo->sWC;
  6304   6319     pLoop = pBuilder->pNew;
  6305   6320     pLoop->wsFlags = 0;
  6306   6321     pLoop->nSkip = 0;
  6307         -  pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ, 0);
         6322  +  pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IS, 0);
  6308   6323     if( pTerm ){
         6324  +    testcase( pTerm->eOperator & WO_IS );
  6309   6325       pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
  6310   6326       pLoop->aLTerm[0] = pTerm;
  6311   6327       pLoop->nLTerm = 1;
  6312   6328       pLoop->u.btree.nEq = 1;
  6313   6329       /* TUNING: Cost of a rowid lookup is 10 */
  6314   6330       pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  6315   6331     }else{
................................................................................
  6316   6332       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  6317   6333         assert( pLoop->aLTermSpace==pLoop->aLTerm );
  6318   6334         if( !IsUniqueIndex(pIdx)
  6319   6335          || pIdx->pPartIdxWhere!=0 
  6320   6336          || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
  6321   6337         ) continue;
  6322   6338         for(j=0; j<pIdx->nKeyCol; j++){
  6323         -        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
         6339  +        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ|WO_IS, pIdx);
  6324   6340           if( pTerm==0 ) break;
         6341  +         testcase( pTerm->eOperator & WO_IS );
  6325   6342           pLoop->aLTerm[j] = pTerm;
  6326   6343         }
  6327   6344         if( j!=pIdx->nKeyCol ) continue;
  6328   6345         pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
  6329   6346         if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
  6330   6347           pLoop->wsFlags |= WHERE_IDX_ONLY;
  6331   6348         }

Changes to src/whereInt.h.

   276    276   #  define TERM_VNULL    0x80   /* Manufactured x>NULL or x<=NULL term */
   277    277   #else
   278    278   #  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
   279    279   #endif
   280    280   #define TERM_LIKEOPT    0x100  /* Virtual terms from the LIKE optimization */
   281    281   #define TERM_LIKECOND   0x200  /* Conditionally this LIKE operator term */
   282    282   #define TERM_LIKE       0x400  /* The original LIKE operator */
          283  +#define TERM_IS         0x800  /* Term.pExpr is an IS operator */
   283    284   
   284    285   /*
   285    286   ** An instance of the WhereScan object is used as an iterator for locating
   286    287   ** terms in the WHERE clause that are useful to the query planner.
   287    288   */
   288    289   struct WhereScan {
   289    290     WhereClause *pOrigWC;      /* Original, innermost WhereClause */
................................................................................
   424    425   
   425    426   /*
   426    427   ** Bitmasks for the operators on WhereTerm objects.  These are all
   427    428   ** operators that are of interest to the query planner.  An
   428    429   ** OR-ed combination of these values can be used when searching for
   429    430   ** particular WhereTerms within a WhereClause.
   430    431   */
   431         -#define WO_IN     0x001
   432         -#define WO_EQ     0x002
          432  +#define WO_IN     0x0001
          433  +#define WO_EQ     0x0002
   433    434   #define WO_LT     (WO_EQ<<(TK_LT-TK_EQ))
   434    435   #define WO_LE     (WO_EQ<<(TK_LE-TK_EQ))
   435    436   #define WO_GT     (WO_EQ<<(TK_GT-TK_EQ))
   436    437   #define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
   437         -#define WO_MATCH  0x040
   438         -#define WO_ISNULL 0x080
   439         -#define WO_OR     0x100       /* Two or more OR-connected terms */
   440         -#define WO_AND    0x200       /* Two or more AND-connected terms */
   441         -#define WO_EQUIV  0x400       /* Of the form A==B, both columns */
   442         -#define WO_NOOP   0x800       /* This term does not restrict search space */
          438  +#define WO_MATCH  0x0040
          439  +#define WO_IS     0x0080
          440  +#define WO_ISNULL 0x0100
          441  +#define WO_OR     0x0200       /* Two or more OR-connected terms */
          442  +#define WO_AND    0x0400       /* Two or more AND-connected terms */
          443  +#define WO_EQUIV  0x0800       /* Of the form A==B, both columns */
          444  +#define WO_NOOP   0x1000       /* This term does not restrict search space */
   443    445   
   444         -#define WO_ALL    0xfff       /* Mask of all possible WO_* values */
   445         -#define WO_SINGLE 0x0ff       /* Mask of all non-compound WO_* values */
          446  +#define WO_ALL    0x1fff       /* Mask of all possible WO_* values */
          447  +#define WO_SINGLE 0x01ff       /* Mask of all non-compound WO_* values */
   446    448   
   447    449   /*
   448    450   ** These are definitions of bits in the WhereLoop.wsFlags field.
   449    451   ** The particular combination of bits in each WhereLoop help to
   450    452   ** determine the algorithm that WhereLoop represents.
   451    453   */
   452    454   #define WHERE_COLUMN_EQ    0x00000001  /* x=EXPR */

Changes to test/transitive1.test.

    61     61      ORDER BY +w;
    62     62   } {1 2 1 3 3 4 3 6 5 6 5 7}
    63     63   do_execsql_test transitive1-301 {
    64     64     SELECT *
    65     65       FROM t301 CROSS JOIN t302
    66     66      WHERE w=y AND y IS NOT NULL
    67     67      ORDER BY w;
           68  +} {1 2 1 3 3 4 3 6 5 6 5 7}
           69  +do_execsql_test transitive1-302 {
           70  +  SELECT *
           71  +    FROM t301 CROSS JOIN t302
           72  +   WHERE w IS y AND y IS NOT NULL
           73  +   ORDER BY w;
    68     74   } {1 2 1 3 3 4 3 6 5 6 5 7}
    69     75   do_execsql_test transitive1-310 {
    70     76     SELECT *
    71     77       FROM t301 CROSS JOIN t302 ON w=y
    72     78      WHERE y>1
    73     79      ORDER BY +w
    74     80   } {3 4 3 6 5 6 5 7}
................................................................................
    99    105     SELECT *
   100    106       FROM t301 CROSS JOIN t302 ON w=y
   101    107      WHERE y BETWEEN 1 AND 4
   102    108      ORDER BY w DESC;
   103    109   } {3 4 3 6 1 2 1 3}
   104    110   
   105    111   # Ticket [c620261b5b5dc] circa 2013-10-28.
   106         -# Make sureconstraints are not used with LEFT JOINs.
          112  +# Make sure constraints are not used with LEFT JOINs.
   107    113   #
   108    114   # The next case is from the ticket report.  It outputs no rows in 3.8.1
   109    115   # prior to the bug-fix.
   110    116   #
   111    117   do_execsql_test transitive1-400 {
   112    118     CREATE TABLE t401(a);
   113    119     CREATE TABLE t402(b);
   114    120     CREATE TABLE t403(c INTEGER PRIMARY KEY);
   115    121     INSERT INTO t401 VALUES(1);
   116    122     INSERT INTO t403 VALUES(1);
   117    123     SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c=a;
   118    124   } {1-row}
          125  +do_execsql_test transitive1-401 {
          126  +  SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a;
          127  +} {1-row}
          128  +do_execsql_test transitive1-402 {
          129  +  SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a;
          130  +} {1-row}
          131  +do_execsql_test transitive1-403 {
          132  +  SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a;
          133  +} {1-row}
          134  +
   119    135   
   120    136   # The following is a script distilled from the XBMC project where the
   121    137   # bug was originally encountered.  The correct answer is a single row
   122    138   # of output.  Before the bug was fixed, zero rows were generated.
   123    139   #
   124    140   do_execsql_test transitive1-410 {
   125    141     CREATE TABLE bookmark ( idBookmark integer primary key, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);

Changes to test/vtab1.test.

  1081   1081   } {{} 15 16}
  1082   1082   do_test vtab1.13-3 {
  1083   1083     execsql { 
  1084   1084       INSERT INTO c VALUES(15, NULL, 16);
  1085   1085       SELECT * FROM echo_c WHERE b IS NULL 
  1086   1086     }
  1087   1087   } {15 {} 16}
  1088         -do_test vtab1.13-3 {
         1088  +do_test vtab1.13-4 {
         1089  +  unset -nocomplain null
         1090  +  execsql { 
         1091  +    SELECT * FROM echo_c WHERE b IS $null
         1092  +  }
         1093  +} {15 {} 16}
         1094  +do_test vtab1.13-5 {
  1089   1095     execsql { 
  1090   1096       SELECT * FROM echo_c WHERE b IS NULL AND a = 15;
  1091   1097     }
         1098  +} {15 {} 16}
         1099  +do_test vtab1.13-6 {
         1100  +  execsql { 
         1101  +    SELECT * FROM echo_c WHERE NULL IS b AND a IS 15;
         1102  +  }
  1092   1103   } {15 {} 16}
  1093   1104   
  1094   1105   
  1095   1106   do_test vtab1-14.001 {
  1096   1107     execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (1,2,3)}
  1097   1108   } {1 3 G H 2 {} 15 16 3 15 {} 16}
  1098   1109   do_test vtab1-14.002 {

Changes to test/vtab2.test.

   100    100   do_test vtab2-3.2 {
   101    101     execsql {
   102    102       SELECT *, b.rowid
   103    103         FROM schema a LEFT JOIN schema b ON a.dflt_value=b.dflt_value
   104    104        WHERE a.rowid=1
   105    105     }
   106    106   } {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}
          107  +do_test vtab2-3.3 {
          108  +  execsql {
          109  +    SELECT *, b.rowid
          110  +      FROM schema a LEFT JOIN schema b ON a.dflt_value IS b.dflt_value
          111  +                                      AND a.dflt_value IS NOT NULL
          112  +     WHERE a.rowid=1
          113  +  }
          114  +} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}
   107    115   
   108    116   do_test vtab2-4.1 {
   109    117     execsql {
   110    118       BEGIN TRANSACTION;
   111    119       CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
   112    120       CREATE TABLE fkey(
   113    121         to_tbl,
................................................................................
   149    157       sqlite3_exec_hex db { CREATE VIRTUAL TABLE %C9 USING s }
   150    158     } {/1 {malformed database schema.* already exists}/}
   151    159   }
   152    160   
   153    161   
   154    162   
   155    163   finish_test
   156         -

Changes to test/vtab6.test.

   228    228       SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
   229    229     }
   230    230   } {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
   231    231   do_test vtab6-2.4 {
   232    232     execsql {
   233    233       SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
   234    234     }
          235  +} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
          236  +do_test vtab6-2.4.1 {
          237  +  execsql {
          238  +    SELECT * FROM t1 LEFT JOIN t2 ON t1.a IS t2.d
          239  +  }
   235    240   } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
   236    241   do_test vtab6-2.5 {
   237    242     execsql {
   238    243       SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
   239    244     }
   240    245   } {2 3 4 {} {} {} 3 4 5 1 2 3}
   241    246   do_test vtab6-2.6 {

Changes to test/where.test.

    61     61   # "sqlite_search_count" which tallys the number of executions of MoveTo
    62     62   # and Next operators in the VDBE.  By verifing that the search count is
    63     63   # small we can be assured that indices are being used properly.
    64     64   #
    65     65   do_test where-1.1.1 {
    66     66     count {SELECT x, y, w FROM t1 WHERE w=10}
    67     67   } {3 121 10 3}
           68  +do_test where-1.1.1b {
           69  +  count {SELECT x, y, w FROM t1 WHERE w IS 10}
           70  +} {3 121 10 3}
    68     71   do_eqp_test where-1.1.2 {
    69     72     SELECT x, y, w FROM t1 WHERE w=10
    70     73   } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
           74  +do_eqp_test where-1.1.2b {
           75  +  SELECT x, y, w FROM t1 WHERE w IS 10
           76  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
    71     77   do_test where-1.1.3 {
    72     78     db status step
    73     79   } {0}
    74     80   do_test where-1.1.4 {
    75     81     db eval {SELECT x, y, w FROM t1 WHERE +w=10}
    76     82   } {3 121 10}
    77     83   do_test where-1.1.5 {
................................................................................
    97    103   } {3 144 11 3}
    98    104   do_test where-1.3.1 {
    99    105     count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
   100    106   } {3 144 11 3}
   101    107   do_test where-1.3.2 {
   102    108     count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
   103    109   } {3 144 11 3}
          110  +do_test where-1.3.3 {
          111  +  count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc}
          112  +} {3 144 11 3}
   104    113   do_test where-1.4.1 {
   105    114     count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
   106    115   } {11 3 144 3}
          116  +do_test where-1.4.1b {
          117  +  count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2}
          118  +} {11 3 144 3}
   107    119   do_eqp_test where-1.4.2 {
   108    120     SELECT w, x, y FROM t1 WHERE 11=w AND x>2
   109    121   } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
          122  +do_eqp_test where-1.4.2b {
          123  +  SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2
          124  +} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
   110    125   do_test where-1.4.3 {
   111    126     count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
   112    127   } {11 3 144 3}
   113    128   do_eqp_test where-1.4.4 {
   114    129     SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
   115    130   } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
   116    131   do_test where-1.5 {
................................................................................
   139    154   } {3 144 3}
   140    155   do_test where-1.10 {
   141    156     count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
   142    157   } {3 121 3}
   143    158   do_test where-1.11 {
   144    159     count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
   145    160   } {3 100 3}
          161  +do_test where-1.11b {
          162  +  count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10}
          163  +} {3 100 3}
   146    164   
   147    165   # New for SQLite version 2.1: Verify that that inequality constraints
   148    166   # are used correctly.
   149    167   #
   150    168   do_test where-1.12 {
   151    169     count {SELECT w FROM t1 WHERE x=3 AND y<100}
   152    170   } {8 3}
          171  +do_test where-1.12b {
          172  +  count {SELECT w FROM t1 WHERE x IS 3 AND y<100}
          173  +} {8 3}
   153    174   do_test where-1.13 {
   154    175     count {SELECT w FROM t1 WHERE x=3 AND 100>y}
   155    176   } {8 3}
   156    177   do_test where-1.14 {
   157    178     count {SELECT w FROM t1 WHERE 3=x AND y<100}
   158    179   } {8 3}
          180  +do_test where-1.14b {
          181  +  count {SELECT w FROM t1 WHERE 3 IS x AND y<100}
          182  +} {8 3}
   159    183   do_test where-1.15 {
   160    184     count {SELECT w FROM t1 WHERE 3=x AND 100>y}
   161    185   } {8 3}
   162    186   do_test where-1.16 {
   163    187     count {SELECT w FROM t1 WHERE x=3 AND y<=100}
   164    188   } {8 9 5}
   165    189   do_test where-1.17 {
   166    190     count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
   167    191   } {8 9 5}
   168    192   do_test where-1.18 {
   169    193     count {SELECT w FROM t1 WHERE x=3 AND y>225}
   170    194   } {15 3}
          195  +do_test where-1.18b {
          196  +  count {SELECT w FROM t1 WHERE x IS 3 AND y>225}
          197  +} {15 3}
   171    198   do_test where-1.19 {
   172    199     count {SELECT w FROM t1 WHERE x=3 AND 225<y}
   173    200   } {15 3}
   174    201   do_test where-1.20 {
   175    202     count {SELECT w FROM t1 WHERE x=3 AND y>=225}
   176    203   } {14 15 5}
   177    204   do_test where-1.21 {
   178    205     count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
   179    206   } {14 15 5}
   180    207   do_test where-1.22 {
   181    208     count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
   182    209   } {11 12 5}
          210  +do_test where-1.22b {
          211  +  count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196}
          212  +} {11 12 5}
   183    213   do_test where-1.23 {
   184    214     count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
   185    215   } {10 11 12 13 9}
   186    216   do_test where-1.24 {
   187    217     count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
   188    218   } {11 12 5}
   189    219   do_test where-1.25 {

Changes to test/where4.test.

    53     53   # "sqlite_search_count" which tallys the number of executions of MoveTo
    54     54   # and Next operators in the VDBE.  By verifing that the search count is
    55     55   # small we can be assured that indices are being used properly.
    56     56   #
    57     57   do_test where4-1.1 {
    58     58     count {SELECT rowid FROM t1 WHERE w IS NULL}
    59     59   } {7 2}
           60  +do_test where4-1.1b {
           61  +  unset -nocomplain null
           62  +  count {SELECT rowid FROM t1 WHERE w IS $null}
           63  +} {7 2}
    60     64   do_test where4-1.2 {
    61     65     count {SELECT rowid FROM t1 WHERE +w IS NULL}
    62     66   } {7 6}
    63     67   do_test where4-1.3 {
    64     68     count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL}
    65     69   } {2 2}
    66     70   do_test where4-1.4 {
................................................................................
   138    142       SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
   139    143     }
   140    144   } {2 2 {} 3 {} {}}
   141    145   do_test where4-3.2 {
   142    146     execsql {
   143    147       SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
   144    148     }
          149  +} {2 2 {} 3 {} {}}
          150  +do_test where4-3.3 {
          151  +  execsql {
          152  +    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE NULL is y;
          153  +  }
          154  +} {2 2 {} 3 {} {}}
          155  +do_test where4-3.4 {
          156  +  unset -nocomplain null
          157  +  execsql {
          158  +    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS $null;
          159  +  }
   145    160   } {2 2 {} 3 {} {}}
   146    161   
   147    162   # Ticket #2189.  Probably the same bug as #2177.
   148    163   #
   149    164   do_test where4-4.1 {
   150    165     execsql {
   151    166       CREATE TABLE test(col1 TEXT PRIMARY KEY);

Changes to test/whereC.test.

    55     55     8   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12" {10 11 12}
    56     56     9   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 11 AND 12" {11 12}
    57     57    10   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 11" {10 11}
    58     58    11   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10" {}
    59     59    12   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i<NULL"      {}
    60     60    13   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL"     {}
    61     61    14   "SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5"     {3 4}
           62  + 15   "SELECT i FROM t1 WHERE rowid IS '12'"               {12}
    62     63   } {
    63     64     do_execsql_test 1.$tn.1 $sql $res
    64     65     do_execsql_test 1.$tn.2 "$sql ORDER BY i ASC"  [lsort -integer -inc  $res]
    65     66     do_execsql_test 1.$tn.3 "$sql ORDER BY i DESC" [lsort -integer -dec  $res]
    66     67   }
    67     68   
    68     69   
    69     70   finish_test