/ Check-in [4541688b3f]
Login

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

Overview
Comment:A new implementation of indexing with the IS operator that works correctly when the IS operator is in the WHERE clause and the operands are from opposite sides of a LEFT JOIN.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | index-is-operator
Files: files | file ages | folders
SHA1:4541688b3f56f5cd3d5b299594b58c577ad633bb
User & Date: drh 2015-05-14 01:05:25
Context
2015-05-14
13:18
Add testcase() macros. Get transitive WHERE clause constraints on IS operators working again. check-in: d195d4a65d user: drh tags: index-is-operator
09:53
Merge changes from the index-is-operator branch into this one. Drop the partial support added for IS on this branch by [52e73eec]. check-in: 16ab9cafd0 user: dan tags: ota-update
01:05
A new implementation of indexing with the IS operator that works correctly when the IS operator is in the WHERE clause and the operands are from opposite sides of a LEFT JOIN. check-in: 4541688b3f user: drh tags: index-is-operator
2015-05-13
19:33
Simplified implementation of indexing with the IS operator. check-in: 95b1f9bf14 user: drh tags: index-is-operator
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   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    419     }else if( op==TK_IS ){
   420         -    c = WO_EQ;  /* IS works like ==, just without the IsNull tests */
          420  +    c = WO_IS;
   421    421     }else{
   422    422       assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff );
   423    423       c = (u16)(WO_EQ<<(op-TK_EQ));
   424    424     }
   425    425     assert( op!=TK_ISNULL || c==WO_ISNULL );
   426    426     assert( op!=TK_IN || c==WO_IN );
   427    427     assert( op!=TK_EQ || c==WO_EQ );
   428    428     assert( op!=TK_LT || c==WO_LT );
   429    429     assert( op!=TK_LE || c==WO_LE );
   430    430     assert( op!=TK_GT || c==WO_GT );
   431    431     assert( op!=TK_GE || c==WO_GE );
   432         -  assert( op!=TK_IS || c==WO_EQ );
          432  +  assert( op!=TK_IS || c==WO_IS );
   433    433     return c;
   434    434   }
   435    435   
   436    436   /*
   437    437   ** Advance to the next WhereTerm that matches according to the criteria
   438    438   ** established when the pScan object was initialized by whereScanInit().
   439    439   ** Return NULL if there are no more matching WhereTerms.
................................................................................
   486    486                 pColl = sqlite3BinaryCompareCollSeq(pParse,
   487    487                                                     pX->pLeft, pX->pRight);
   488    488                 if( pColl==0 ) pColl = pParse->db->pDfltColl;
   489    489                 if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){
   490    490                   continue;
   491    491                 }
   492    492               }
   493         -            if( (pTerm->eOperator & WO_EQ)!=0
          493  +            if( (pTerm->eOperator & (WO_EQ|WO_IS))!=0
   494    494                && (pX = pTerm->pExpr->pRight)->op==TK_COLUMN
   495    495                && pX->iTable==pScan->aEquiv[0]
   496    496                && pX->iColumn==pScan->aEquiv[1]
   497    497               ){
          498  +              testcase( pTerm->eOperator & WO_IS );
   498    499                 continue;
   499    500               }
   500    501               pScan->k = k+1;
   501    502               return pTerm;
   502    503             }
   503    504           }
   504    505         }
................................................................................
   592    593     Index *pIdx           /* Must be compatible with this index, if not NULL */
   593    594   ){
   594    595     WhereTerm *pResult = 0;
   595    596     WhereTerm *p;
   596    597     WhereScan scan;
   597    598   
   598    599     p = whereScanInit(&scan, pWC, iCur, iColumn, op, pIdx);
          600  +  op &= WO_EQ|WO_IS;
   599    601     while( p ){
   600    602       if( (p->prereqRight & notReady)==0 ){
   601         -      if( p->prereqRight==0 && (p->eOperator&WO_EQ)!=0 ){
          603  +      if( p->prereqRight==0 && (p->eOperator&op)!=0 ){
          604  +        testcase( p->eOperator & WO_IS );
   602    605           return p;
   603    606         }
   604    607         if( pResult==0 ) pResult = p;
   605    608       }
   606    609       p = whereScanNext(&scan);
   607    610     }
   608    611     return pResult;
................................................................................
  1675   1678   static int termCanDriveIndex(
  1676   1679     WhereTerm *pTerm,              /* WHERE clause term to check */
  1677   1680     struct SrcList_item *pSrc,     /* Table we are trying to access */
  1678   1681     Bitmask notReady               /* Tables in outer loops of the join */
  1679   1682   ){
  1680   1683     char aff;
  1681   1684     if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
  1682         -  if( (pTerm->eOperator & WO_EQ)==0 ) return 0;
         1685  +  if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
  1683   1686     if( (pTerm->prereqRight & notReady)!=0 ) return 0;
  1684   1687     if( pTerm->u.leftColumn<0 ) return 0;
  1685   1688     aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
  1686   1689     if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
  1687   1690     testcase( pTerm->pExpr->op==TK_IS );
  1688   1691     return 1;
  1689   1692   }
................................................................................
  1951   1954     for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
  1952   1955       u8 op;
  1953   1956       if( pTerm->leftCursor != pSrc->iCursor ) continue;
  1954   1957       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
  1955   1958       testcase( pTerm->eOperator & WO_IN );
  1956   1959       testcase( pTerm->eOperator & WO_ISNULL );
  1957   1960       testcase( pTerm->eOperator & WO_ALL );
  1958         -    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV))==0 ) continue;
         1961  +    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
  1959   1962       if( pTerm->wtFlags & TERM_VNULL ) continue;
  1960   1963       pIdxCons[j].iColumn = pTerm->u.leftColumn;
  1961   1964       pIdxCons[j].iTermOffset = i;
  1962   1965       op = (u8)pTerm->eOperator & WO_ALL;
  1963   1966       if( op==WO_IN ) op = WO_EQ;
  1964   1967       pIdxCons[j].op = op;
  1965   1968       /* The direct assignment in the previous line is possible only because
................................................................................
  4100   4103     ** then we cannot use the "t1.a=t2.b" constraint, but we can code
  4101   4104     ** the implied "t1.a=123" constraint.
  4102   4105     */
  4103   4106     for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
  4104   4107       Expr *pE, *pEAlt;
  4105   4108       WhereTerm *pAlt;
  4106   4109       if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  4107         -    if( pTerm->eOperator!=(WO_EQUIV|WO_EQ) ) continue;
         4110  +    if( (pTerm->eOperator&(WO_EQUIV|WO_EQ|WO_IS))<=WO_EQUIV ) continue;
  4108   4111       if( pTerm->leftCursor!=iCur ) continue;
  4109   4112       if( pLevel->iLeftJoin ) continue;
  4110   4113       pE = pTerm->pExpr;
  4111   4114       assert( !ExprHasProperty(pE, EP_FromJoin) );
  4112   4115       assert( (pTerm->prereqRight & pLevel->notReady)!=0 );
  4113         -    pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady, WO_EQ|WO_IN, 0);
         4116  +    pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady,
         4117  +                    WO_EQ|WO_IN|WO_IS, 0);
  4114   4118       if( pAlt==0 ) continue;
  4115   4119       if( pAlt->wtFlags & (TERM_CODED) ) continue;
  4116         -    testcase( (pAlt->eOperator & WO_EQ)!=0 && pAlt->pExpr->op==TK_IS );
         4120  +    testcase( pAlt->eOperator & WO_EQ );
         4121  +    testcase( pAlt->eOperator & WO_IS );
  4117   4122       testcase( pAlt->eOperator & WO_IN );
  4118   4123       VdbeModuleComment((v, "begin transitive constraint"));
  4119   4124       pEAlt = sqlite3StackAllocRaw(db, sizeof(*pEAlt));
  4120   4125       if( pEAlt ){
  4121   4126         *pEAlt = *pAlt->pExpr;
  4122   4127         pEAlt->pLeft = pE->pLeft;
  4123   4128         sqlite3ExprIfFalse(pParse, pEAlt, addrCont, SQLITE_JUMPIFNULL);
................................................................................
  4652   4657           /* If a truth probability is specified using the likelihood() hints,
  4653   4658           ** then use the probability provided by the application. */
  4654   4659           pLoop->nOut += pTerm->truthProb;
  4655   4660         }else{
  4656   4661           /* In the absence of explicit truth probabilities, use heuristics to
  4657   4662           ** guess a reasonable truth probability. */
  4658   4663           pLoop->nOut--;
  4659         -        if( pTerm->eOperator&WO_EQ ){
         4664  +        if( pTerm->eOperator&(WO_EQ|WO_IS) ){
  4660   4665             Expr *pRight = pTerm->pExpr->pRight;
  4661   4666             testcase( pTerm->pExpr->op==TK_IS );
  4662   4667             if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){
  4663   4668               k = 10;
  4664   4669             }else{
  4665   4670               k = 20;
  4666   4671             }
................................................................................
  4725   4730     assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
  4726   4731     assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
  4727   4732     if( pNew->wsFlags & WHERE_BTM_LIMIT ){
  4728   4733       opMask = WO_LT|WO_LE;
  4729   4734     }else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
  4730   4735       opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
  4731   4736     }else{
  4732         -    opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE;
         4737  +    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
  4733   4738     }
  4734   4739     if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
  4735   4740   
  4736   4741     assert( pNew->u.btree.nEq<pProbe->nColumn );
  4737   4742     iCol = pProbe->aiColumn[pNew->u.btree.nEq];
  4738   4743   
  4739   4744     pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
................................................................................
  4788   4793         }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  4789   4794           /* "x IN (value, value, ...)" */
  4790   4795           nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
  4791   4796         }
  4792   4797         assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
  4793   4798                           ** changes "x IN (?)" into "x=?". */
  4794   4799   
  4795         -    }else if( eOp & (WO_EQ) ){
         4800  +    }else if( eOp & (WO_EQ|WO_IS) ){
  4796   4801         pNew->wsFlags |= WHERE_COLUMN_EQ;
  4797   4802         if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
  4798   4803           if( iCol>=0 && pProbe->uniqNotNull==0 ){
  4799   4804             pNew->wsFlags |= WHERE_UNQ_WANTED;
  4800   4805           }else{
  4801   4806             pNew->wsFlags |= WHERE_ONEROW;
  4802   4807           }
................................................................................
  4838   4843       assert( pNew->nOut==saved_nOut );
  4839   4844       if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
  4840   4845         /* Adjust nOut using stat3/stat4 data. Or, if there is no stat3/stat4
  4841   4846         ** data, using some other estimate.  */
  4842   4847         whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew);
  4843   4848       }else{
  4844   4849         int nEq = ++pNew->u.btree.nEq;
  4845         -      assert( eOp & (WO_ISNULL|WO_EQ|WO_IN) );
         4850  +      assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) );
  4846   4851   
  4847   4852         assert( pNew->nOut==saved_nOut );
  4848   4853         if( pTerm->truthProb<=0 && iCol>=0 ){
  4849   4854           assert( (eOp & WO_IN) || nIn==0 );
  4850   4855           testcase( eOp & WO_IN );
  4851   4856           pNew->nOut += pTerm->truthProb;
  4852   4857           pNew->nOut -= nIn;
................................................................................
  4855   4860           tRowcnt nOut = 0;
  4856   4861           if( nInMul==0 
  4857   4862            && pProbe->nSample 
  4858   4863            && pNew->u.btree.nEq<=pProbe->nSampleCol
  4859   4864            && ((eOp & WO_IN)==0 || !ExprHasProperty(pTerm->pExpr, EP_xIsSelect))
  4860   4865           ){
  4861   4866             Expr *pExpr = pTerm->pExpr;
  4862         -          if( (eOp & (WO_EQ|WO_ISNULL))!=0 ){
  4863         -            testcase( (eOp & WO_EQ)!=0 && pExpr->op==TK_IS );
         4867  +          if( (eOp & (WO_EQ|WO_ISNULL|WO_IS))!=0 ){
         4868  +            testcase( eOp & WO_EQ );
         4869  +            testcase( eOp & WO_IS );
  4864   4870               testcase( eOp & WO_ISNULL );
  4865   4871               rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut);
  4866   4872             }else{
  4867   4873               rc = whereInScanEst(pParse, pBuilder, pExpr->x.pList, &nOut);
  4868   4874             }
  4869   4875             if( rc==SQLITE_NOTFOUND ) rc = SQLITE_OK;
  4870   4876             if( rc!=SQLITE_OK ) break;          /* Jump out of the pTerm loop */
................................................................................
  5693   5699       */
  5694   5700       for(i=0; i<nOrderBy; i++){
  5695   5701         if( MASKBIT(i) & obSat ) continue;
  5696   5702         pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
  5697   5703         if( pOBExpr->op!=TK_COLUMN ) continue;
  5698   5704         if( pOBExpr->iTable!=iCur ) continue;
  5699   5705         pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
  5700         -                       ~ready, WO_EQ|WO_ISNULL, 0);
         5706  +                       ~ready, WO_EQ|WO_ISNULL|WO_IS, 0);
  5701   5707         if( pTerm==0 ) continue;
  5702         -      if( (pTerm->eOperator&WO_EQ)!=0 && pOBExpr->iColumn>=0 ){
         5708  +      if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
  5703   5709           const char *z1, *z2;
  5704   5710           pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  5705   5711           if( !pColl ) pColl = db->pDfltColl;
  5706   5712           z1 = pColl->zName;
  5707   5713           pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
  5708   5714           if( !pColl ) pColl = db->pDfltColl;
  5709   5715           z2 = pColl->zName;
................................................................................
  5735   5741         distinctColumns = 0;
  5736   5742         for(j=0; j<nColumn; j++){
  5737   5743           u8 bOnce;   /* True to run the ORDER BY search loop */
  5738   5744   
  5739   5745           /* Skip over == and IS NULL terms */
  5740   5746           if( j<pLoop->u.btree.nEq
  5741   5747            && pLoop->nSkip==0
  5742         -         && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0
         5748  +         && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL|WO_IS))!=0
  5743   5749           ){
  5744   5750             if( i & WO_ISNULL ){
  5745   5751               testcase( isOrderDistinct );
  5746   5752               isOrderDistinct = 0;
  5747   5753             }
  5748   5754             continue;  
  5749   5755           }
................................................................................
  6308   6314     if( IsVirtual(pTab) ) return 0;
  6309   6315     if( pItem->zIndex ) return 0;
  6310   6316     iCur = pItem->iCursor;
  6311   6317     pWC = &pWInfo->sWC;
  6312   6318     pLoop = pBuilder->pNew;
  6313   6319     pLoop->wsFlags = 0;
  6314   6320     pLoop->nSkip = 0;
  6315         -  pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ, 0);
         6321  +  pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IS, 0);
  6316   6322     if( pTerm ){
         6323  +    testcase( pTerm->eOperator & WO_IS );
  6317   6324       pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
  6318   6325       pLoop->aLTerm[0] = pTerm;
  6319   6326       pLoop->nLTerm = 1;
  6320   6327       pLoop->u.btree.nEq = 1;
  6321   6328       /* TUNING: Cost of a rowid lookup is 10 */
  6322   6329       pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  6323   6330     }else{
................................................................................
  6324   6331       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  6325   6332         assert( pLoop->aLTermSpace==pLoop->aLTerm );
  6326   6333         if( !IsUniqueIndex(pIdx)
  6327   6334          || pIdx->pPartIdxWhere!=0 
  6328   6335          || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
  6329   6336         ) continue;
  6330   6337         for(j=0; j<pIdx->nKeyCol; j++){
  6331         -        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
         6338  +        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ|WO_IS, pIdx);
  6332   6339           if( pTerm==0 ) break;
         6340  +         testcase( pTerm->eOperator & WO_IS );
  6333   6341           pLoop->aLTerm[j] = pTerm;
  6334         -        testcase( pTerm->pExpr->op==TK_IS );
  6335   6342         }
  6336   6343         if( j!=pIdx->nKeyCol ) continue;
  6337   6344         pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
  6338   6345         if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
  6339   6346           pLoop->wsFlags |= WHERE_IDX_ONLY;
  6340   6347         }
  6341   6348         pLoop->nLTerm = j;

Changes to src/whereInt.h.

   425    425   
   426    426   /*
   427    427   ** Bitmasks for the operators on WhereTerm objects.  These are all
   428    428   ** operators that are of interest to the query planner.  An
   429    429   ** OR-ed combination of these values can be used when searching for
   430    430   ** particular WhereTerms within a WhereClause.
   431    431   */
   432         -#define WO_IN     0x001
   433         -#define WO_EQ     0x002                      /* Used for both == and IS */
          432  +#define WO_IN     0x0001
          433  +#define WO_EQ     0x0002
   434    434   #define WO_LT     (WO_EQ<<(TK_LT-TK_EQ))
   435    435   #define WO_LE     (WO_EQ<<(TK_LE-TK_EQ))
   436    436   #define WO_GT     (WO_EQ<<(TK_GT-TK_EQ))
   437    437   #define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
   438         -#define WO_MATCH  0x040
   439         -#define WO_ISNULL 0x080
   440         -#define WO_OR     0x100       /* Two or more OR-connected terms */
   441         -#define WO_AND    0x200       /* Two or more AND-connected terms */
   442         -#define WO_EQUIV  0x400       /* Of the form A==B, both columns */
   443         -#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 */
   444    445   
   445         -#define WO_ALL    0xfff       /* Mask of all possible WO_* values */
   446         -#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 */
   447    448   
   448    449   /*
   449    450   ** These are definitions of bits in the WhereLoop.wsFlags field.
   450    451   ** The particular combination of bits in each WhereLoop help to
   451    452   ** determine the algorithm that WhereLoop represents.
   452    453   */
   453    454   #define WHERE_COLUMN_EQ    0x00000001  /* x=EXPR */

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);