/ Check-in [6f7f1673]
Login

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

Overview
Comment:An early attempt to get indexes to work with the IS operator. This code passes tests, but much more testing is needed to verify that it works on all corner cases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | index-is-operator
Files: files | file ages | folders
SHA1: 6f7f1673d00d216a5aa456acb44793a14f3b3d91
User & Date: drh 2015-05-13 15:24:07
Context
2015-05-13
17:54
Add testcase() macros and comments and a few test-cases. check-in: 24263d08 user: drh tags: index-is-operator
15:24
An early attempt to get indexes to work with the IS operator. This code passes tests, but much more testing is needed to verify that it works on all corner cases. check-in: 6f7f1673 user: drh tags: index-is-operator
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_EQ;
   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_EQ );
   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.
................................................................................
  1250   1253       Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight);
  1251   1254       u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV;
  1252   1255       if( pLeft->op==TK_COLUMN ){
  1253   1256         pTerm->leftCursor = pLeft->iTable;
  1254   1257         pTerm->u.leftColumn = pLeft->iColumn;
  1255   1258         pTerm->eOperator = operatorMask(op) & opMask;
  1256   1259       }
         1260  +    if( op==TK_IS ) pTerm->wtFlags |= TERM_NULLOK;
  1257   1261       if( pRight && pRight->op==TK_COLUMN ){
  1258   1262         WhereTerm *pNew;
  1259   1263         Expr *pDup;
  1260   1264         u16 eExtraOp = 0;        /* Extra bits for pNew->eOperator */
  1261   1265         if( pTerm->leftCursor>=0 ){
  1262   1266           int idxNew;
  1263   1267           pDup = sqlite3ExprDup(db, pExpr, 0);
................................................................................
  1274   1278           if( pExpr->op==TK_EQ
  1275   1279            && !ExprHasProperty(pExpr, EP_FromJoin)
  1276   1280            && OptimizationEnabled(db, SQLITE_Transitive)
  1277   1281           ){
  1278   1282             pTerm->eOperator |= WO_EQUIV;
  1279   1283             eExtraOp = WO_EQUIV;
  1280   1284           }
         1285  +        if( op==TK_IS ) pNew->wtFlags |= TERM_NULLOK;
  1281   1286         }else{
  1282   1287           pDup = pExpr;
  1283   1288           pNew = pTerm;
  1284   1289         }
  1285   1290         exprCommute(pParse, pDup);
  1286   1291         pLeft = sqlite3ExprSkipCollate(pDup->pLeft);
  1287   1292         pNew->leftCursor = pLeft->iTable;
................................................................................
  1464   1469   
  1465   1470   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  1466   1471     /* When sqlite_stat3 histogram data is available an operator of the
  1467   1472     ** form "x IS NOT NULL" can sometimes be evaluated more efficiently
  1468   1473     ** as "x>NULL" if x is not an INTEGER PRIMARY KEY.  So construct a
  1469   1474     ** virtual term of that form.
  1470   1475     **
  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.
         1476  +  ** Note that the virtual term must be tagged with both TERM_VNULL
         1477  +  ** and TERM_NULLOK.
  1475   1478     */
  1476   1479     if( pExpr->op==TK_NOTNULL
  1477   1480      && pExpr->pLeft->op==TK_COLUMN
  1478   1481      && pExpr->pLeft->iColumn>=0
  1479   1482      && OptimizationEnabled(db, SQLITE_Stat34)
  1480   1483     ){
  1481   1484       Expr *pNewExpr;
................................................................................
  1484   1487       WhereTerm *pNewTerm;
  1485   1488   
  1486   1489       pNewExpr = sqlite3PExpr(pParse, TK_GT,
  1487   1490                               sqlite3ExprDup(db, pLeft, 0),
  1488   1491                               sqlite3PExpr(pParse, TK_NULL, 0, 0, 0), 0);
  1489   1492   
  1490   1493       idxNew = whereClauseInsert(pWC, pNewExpr,
  1491         -                              TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL);
         1494  +                              TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL|TERM_NULLOK);
  1492   1495       if( idxNew ){
  1493   1496         pNewTerm = &pWC->a[idxNew];
  1494   1497         pNewTerm->prereqRight = 0;
  1495   1498         pNewTerm->leftCursor = pLeft->iTable;
  1496   1499         pNewTerm->u.leftColumn = pLeft->iColumn;
  1497   1500         pNewTerm->eOperator = WO_GT;
  1498   1501         markTermAsChild(pWC, idxNew, idxTerm);
................................................................................
  2788   2791     int iTarget         /* Attempt to leave results in this register */
  2789   2792   ){
  2790   2793     Expr *pX = pTerm->pExpr;
  2791   2794     Vdbe *v = pParse->pVdbe;
  2792   2795     int iReg;                  /* Register holding results */
  2793   2796   
  2794   2797     assert( iTarget>0 );
  2795         -  if( pX->op==TK_EQ ){
         2798  +  if( pX->op==TK_EQ || pX->op==TK_IS ){
  2796   2799       iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);
  2797   2800     }else if( pX->op==TK_ISNULL ){
  2798   2801       iReg = iTarget;
  2799   2802       sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
  2800   2803   #ifndef SQLITE_OMIT_SUBQUERY
  2801   2804     }else{
  2802   2805       int eType;
................................................................................
  2973   2976           sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
  2974   2977         }
  2975   2978       }
  2976   2979       testcase( pTerm->eOperator & WO_ISNULL );
  2977   2980       testcase( pTerm->eOperator & WO_IN );
  2978   2981       if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
  2979   2982         Expr *pRight = pTerm->pExpr->pRight;
  2980         -      if( sqlite3ExprCanBeNull(pRight) ){
         2983  +      if( (pTerm->wtFlags & TERM_NULLOK)==0
         2984  +       && sqlite3ExprCanBeNull(pRight)
         2985  +      ){
  2981   2986           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
  2982   2987           VdbeCoverage(v);
  2983   2988         }
  2984   2989         if( zAff ){
  2985   2990           if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_NONE ){
  2986   2991             zAff[j] = SQLITE_AFF_NONE;
  2987   2992           }
................................................................................
  3622   3627   
  3623   3628       /* Seek the index cursor to the start of the range. */
  3624   3629       nConstraint = nEq;
  3625   3630       if( pRangeStart ){
  3626   3631         Expr *pRight = pRangeStart->pExpr->pRight;
  3627   3632         sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3628   3633         whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
  3629         -      if( (pRangeStart->wtFlags & TERM_VNULL)==0
         3634  +      if( (pRangeStart->wtFlags & TERM_NULLOK)==0
  3630   3635          && sqlite3ExprCanBeNull(pRight)
  3631   3636         ){
  3632   3637           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3633   3638           VdbeCoverage(v);
  3634   3639         }
  3635   3640         if( zStartAff ){
  3636   3641           if( sqlite3CompareAffinity(pRight, zStartAff[nEq])==SQLITE_AFF_NONE){
................................................................................
  3668   3673       */
  3669   3674       nConstraint = nEq;
  3670   3675       if( pRangeEnd ){
  3671   3676         Expr *pRight = pRangeEnd->pExpr->pRight;
  3672   3677         sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
  3673   3678         sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3674   3679         whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
  3675         -      if( (pRangeEnd->wtFlags & TERM_VNULL)==0
         3680  +      if( (pRangeEnd->wtFlags & TERM_NULLOK)==0
  3676   3681          && sqlite3ExprCanBeNull(pRight)
  3677   3682         ){
  3678   3683           sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3679   3684           VdbeCoverage(v);
  3680   3685         }
  3681   3686         if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE
  3682   3687          && !sqlite3ExprNeedsNoAffinityChange(pRight, cEndAff)
................................................................................
  4154   4159       sqlite3DebugPrintf("TERM-%-3d NULL\n", iTerm);
  4155   4160     }else{
  4156   4161       char zType[4];
  4157   4162       memcpy(zType, "...", 4);
  4158   4163       if( pTerm->wtFlags & TERM_VIRTUAL ) zType[0] = 'V';
  4159   4164       if( pTerm->eOperator & WO_EQUIV  ) zType[1] = 'E';
  4160   4165       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);
         4166  +    sqlite3DebugPrintf(
         4167  +       "TERM-%-3d %p %s cursor=%-3d prob=%-3d op=0x%03x wtFlags=0x%04x\n",
         4168  +       iTerm, pTerm, zType, pTerm->leftCursor, pTerm->truthProb,
         4169  +       pTerm->eOperator, pTerm->wtFlags);
  4164   4170       sqlite3TreeViewExpr(0, pTerm->pExpr, 0);
  4165   4171     }
  4166   4172   }
  4167   4173   #endif
  4168   4174   
  4169   4175   #ifdef WHERETRACE_ENABLED
  4170   4176   /*

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_NULLOK     0x800  /* Comparison operators against NULL work */
   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 */