/ Check-in [3b7dbe9d]
Login

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

Overview
Comment:Add support for new operators in virtual tables: !=, IS, IS NOT, IS NULL, and NOTNULL.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3b7dbe9da90b7db336eed597a73a5364d8a691e57c1febd60908349f57a539ad
User & Date: drh 2017-09-11 20:54:54
Context
2017-09-11
23:46
New test case for ticket [b899b6042f97f52d]. check-in: 9d91ee5e user: drh tags: trunk
20:54
Add support for new operators in virtual tables: !=, IS, IS NOT, IS NULL, and NOTNULL. check-in: 3b7dbe9d user: drh tags: trunk
19:47
Refactor names of constants and functions associated with the auxiliary operators for xBestIndex. Closed-Leaf check-in: 0fb992af user: drh tags: vtab-extra-ops
2017-09-09
00:51
Fix harmless compiler warnings seen with MSVC. check-in: faa22e29 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

  6243   6243   ** CAPI3REF: Virtual Table Constraint Operator Codes
  6244   6244   **
  6245   6245   ** These macros defined the allowed values for the
  6246   6246   ** [sqlite3_index_info].aConstraint[].op field.  Each value represents
  6247   6247   ** an operator that is part of a constraint term in the wHERE clause of
  6248   6248   ** a query that uses a [virtual table].
  6249   6249   */
  6250         -#define SQLITE_INDEX_CONSTRAINT_EQ      2
  6251         -#define SQLITE_INDEX_CONSTRAINT_GT      4
  6252         -#define SQLITE_INDEX_CONSTRAINT_LE      8
  6253         -#define SQLITE_INDEX_CONSTRAINT_LT     16
  6254         -#define SQLITE_INDEX_CONSTRAINT_GE     32
  6255         -#define SQLITE_INDEX_CONSTRAINT_MATCH  64
  6256         -#define SQLITE_INDEX_CONSTRAINT_LIKE   65
  6257         -#define SQLITE_INDEX_CONSTRAINT_GLOB   66
  6258         -#define SQLITE_INDEX_CONSTRAINT_REGEXP 67
         6250  +#define SQLITE_INDEX_CONSTRAINT_EQ         2
         6251  +#define SQLITE_INDEX_CONSTRAINT_GT         4
         6252  +#define SQLITE_INDEX_CONSTRAINT_LE         8
         6253  +#define SQLITE_INDEX_CONSTRAINT_LT        16
         6254  +#define SQLITE_INDEX_CONSTRAINT_GE        32
         6255  +#define SQLITE_INDEX_CONSTRAINT_MATCH     64
         6256  +#define SQLITE_INDEX_CONSTRAINT_LIKE      65
         6257  +#define SQLITE_INDEX_CONSTRAINT_GLOB      66
         6258  +#define SQLITE_INDEX_CONSTRAINT_REGEXP    67
         6259  +#define SQLITE_INDEX_CONSTRAINT_NE        68
         6260  +#define SQLITE_INDEX_CONSTRAINT_ISNOT     69
         6261  +#define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70
         6262  +#define SQLITE_INDEX_CONSTRAINT_ISNULL    71
         6263  +#define SQLITE_INDEX_CONSTRAINT_IS        72
  6259   6264   
  6260   6265   /*
  6261   6266   ** CAPI3REF: Register A Virtual Table Implementation
  6262   6267   ** METHOD: sqlite3
  6263   6268   **
  6264   6269   ** ^These routines are used to register a new [virtual table module] name.
  6265   6270   ** ^Module names must be registered before

Changes to src/test8.c.

   893    893           case SQLITE_INDEX_CONSTRAINT_LIKE:
   894    894             zOp = "like"; break;
   895    895           case SQLITE_INDEX_CONSTRAINT_GLOB:
   896    896             zOp = "glob"; break;
   897    897           case SQLITE_INDEX_CONSTRAINT_REGEXP:
   898    898             zOp = "regexp"; break;
   899    899         }
   900         -      if( zOp[0]=='L' ){
   901         -        zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", 
   902         -                               zSep, zNewCol);
   903         -      } else {
   904         -        zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zNewCol, zOp);
          900  +      if( zOp ){
          901  +        if( zOp[0]=='L' ){
          902  +          zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", 
          903  +              zSep, zNewCol);
          904  +        } else {
          905  +          zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zNewCol, zOp);
          906  +        }
          907  +        string_concat(&zQuery, zNew, 1, &rc);
          908  +        zSep = "AND";
          909  +        pUsage->argvIndex = ++nArg;
          910  +        pUsage->omit = 1;
   905    911         }
   906         -      string_concat(&zQuery, zNew, 1, &rc);
   907         -
   908         -      zSep = "AND";
   909         -      pUsage->argvIndex = ++nArg;
   910         -      pUsage->omit = 1;
   911    912       }
   912    913     }
   913    914   
   914    915     /* If there is only one term in the ORDER BY clause, and it is
   915    916     ** on a column that this virtual table has an index for, then consume 
   916    917     ** the ORDER BY clause.
   917    918     */

Changes to src/test_bestindex.c.

   410    410           zOp = "match"; break;
   411    411         case SQLITE_INDEX_CONSTRAINT_LIKE:
   412    412           zOp = "like"; break;
   413    413         case SQLITE_INDEX_CONSTRAINT_GLOB:
   414    414           zOp = "glob"; break;
   415    415         case SQLITE_INDEX_CONSTRAINT_REGEXP:
   416    416           zOp = "regexp"; break;
          417  +      case SQLITE_INDEX_CONSTRAINT_NE:
          418  +        zOp = "ne"; break;
          419  +      case SQLITE_INDEX_CONSTRAINT_ISNOT:
          420  +        zOp = "isnot"; break;
          421  +      case SQLITE_INDEX_CONSTRAINT_ISNOTNULL:
          422  +        zOp = "isnotnull"; break;
          423  +      case SQLITE_INDEX_CONSTRAINT_ISNULL:
          424  +        zOp = "isnull"; break;
          425  +      case SQLITE_INDEX_CONSTRAINT_IS:
          426  +        zOp = "is"; break;
   417    427       }
   418    428   
   419    429       Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("op", -1));
   420    430       Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj(zOp, -1));
   421    431       Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("column", -1));
   422    432       Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pCons->iColumn));
   423    433       Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("usable", -1));

Changes to src/where.c.

   864    864       if( pTerm->leftCursor != pSrc->iCursor ) continue;
   865    865       if( pTerm->prereqRight & mUnusable ) continue;
   866    866       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
   867    867       testcase( pTerm->eOperator & WO_IN );
   868    868       testcase( pTerm->eOperator & WO_ISNULL );
   869    869       testcase( pTerm->eOperator & WO_IS );
   870    870       testcase( pTerm->eOperator & WO_ALL );
   871         -    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
          871  +    if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue;
   872    872       if( pTerm->wtFlags & TERM_VNULL ) continue;
   873    873       assert( pTerm->u.leftColumn>=(-1) );
   874    874       nTerm++;
   875    875     }
   876    876   
   877    877     /* If the ORDER BY clause contains only columns in the current 
   878    878     ** virtual table then allocate space for the aOrderBy part of
................................................................................
   912    912     *(int*)&pIdxInfo->nOrderBy = nOrderBy;
   913    913     *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons;
   914    914     *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
   915    915     *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
   916    916                                                                      pUsage;
   917    917   
   918    918     for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
   919         -    u8 op;
          919  +    u16 op;
   920    920       if( pTerm->leftCursor != pSrc->iCursor ) continue;
   921    921       if( pTerm->prereqRight & mUnusable ) continue;
   922    922       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
   923    923       testcase( pTerm->eOperator & WO_IN );
   924    924       testcase( pTerm->eOperator & WO_IS );
   925    925       testcase( pTerm->eOperator & WO_ISNULL );
   926    926       testcase( pTerm->eOperator & WO_ALL );
   927         -    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
          927  +    if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue;
   928    928       if( pTerm->wtFlags & TERM_VNULL ) continue;
   929    929       assert( pTerm->u.leftColumn>=(-1) );
   930    930       pIdxCons[j].iColumn = pTerm->u.leftColumn;
   931    931       pIdxCons[j].iTermOffset = i;
   932         -    op = (u8)pTerm->eOperator & WO_ALL;
          932  +    op = pTerm->eOperator & WO_ALL;
   933    933       if( op==WO_IN ) op = WO_EQ;
   934         -    if( op==WO_MATCH ){
   935         -      op = pTerm->eMatchOp;
   936         -    }
   937         -    pIdxCons[j].op = op;
   938         -    /* The direct assignment in the previous line is possible only because
   939         -    ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
   940         -    ** following asserts verify this fact. */
   941         -    assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
   942         -    assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
   943         -    assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );
   944         -    assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
   945         -    assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
   946         -    assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH );
   947         -    assert( pTerm->eOperator & (WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) );
   948         -
   949         -    if( op & (WO_LT|WO_LE|WO_GT|WO_GE)
   950         -     && sqlite3ExprIsVector(pTerm->pExpr->pRight) 
   951         -    ){
   952         -      if( i<16 ) mNoOmit |= (1 << i);
   953         -      if( op==WO_LT ) pIdxCons[j].op = WO_LE;
   954         -      if( op==WO_GT ) pIdxCons[j].op = WO_GE;
          934  +    if( op==WO_AUX ){
          935  +      pIdxCons[j].op = pTerm->eMatchOp;
          936  +    }else if( op & (WO_ISNULL|WO_IS) ){
          937  +      if( op==WO_ISNULL ){
          938  +        pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL;
          939  +      }else{
          940  +        pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS;
          941  +      }
          942  +    }else{
          943  +      pIdxCons[j].op = (u8)op;
          944  +      /* The direct assignment in the previous line is possible only because
          945  +      ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
          946  +      ** following asserts verify this fact. */
          947  +      assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
          948  +      assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
          949  +      assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );
          950  +      assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
          951  +      assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
          952  +      assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_AUX) );
          953  +
          954  +      if( op & (WO_LT|WO_LE|WO_GT|WO_GE)
          955  +       && sqlite3ExprIsVector(pTerm->pExpr->pRight) 
          956  +      ){
          957  +        if( i<16 ) mNoOmit |= (1 << i);
          958  +        if( op==WO_LT ) pIdxCons[j].op = WO_LE;
          959  +        if( op==WO_GT ) pIdxCons[j].op = WO_GE;
          960  +      }
   955    961       }
   956    962   
   957    963       j++;
   958    964     }
   959    965     for(i=0; i<nOrderBy; i++){
   960    966       Expr *pExpr = pOrderBy->a[i].pExpr;
   961    967       pIdxOrderBy[i].iColumn = pExpr->iColumn;

Changes to src/whereInt.h.

   511    511   **
   512    512   ** Value constraints:
   513    513   **     WO_EQ    == SQLITE_INDEX_CONSTRAINT_EQ
   514    514   **     WO_LT    == SQLITE_INDEX_CONSTRAINT_LT
   515    515   **     WO_LE    == SQLITE_INDEX_CONSTRAINT_LE
   516    516   **     WO_GT    == SQLITE_INDEX_CONSTRAINT_GT
   517    517   **     WO_GE    == SQLITE_INDEX_CONSTRAINT_GE
   518         -**     WO_MATCH == SQLITE_INDEX_CONSTRAINT_MATCH
   519    518   */
   520    519   #define WO_IN     0x0001
   521    520   #define WO_EQ     0x0002
   522    521   #define WO_LT     (WO_EQ<<(TK_LT-TK_EQ))
   523    522   #define WO_LE     (WO_EQ<<(TK_LE-TK_EQ))
   524    523   #define WO_GT     (WO_EQ<<(TK_GT-TK_EQ))
   525    524   #define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
   526         -#define WO_MATCH  0x0040
          525  +#define WO_AUX    0x0040       /* Op useful to virtual tables only */
   527    526   #define WO_IS     0x0080
   528    527   #define WO_ISNULL 0x0100
   529    528   #define WO_OR     0x0200       /* Two or more OR-connected terms */
   530    529   #define WO_AND    0x0400       /* Two or more AND-connected terms */
   531    530   #define WO_EQUIV  0x0800       /* Of the form A==B, both columns */
   532    531   #define WO_NOOP   0x1000       /* This term does not restrict search space */
   533    532   

Changes to src/wherecode.c.

  1013   1013   **
  1014   1014   ** If the expression is not a vector, then nReg must be passed 1. In
  1015   1015   ** this case, generate code to evaluate the expression and leave the
  1016   1016   ** result in register iReg.
  1017   1017   */
  1018   1018   static void codeExprOrVector(Parse *pParse, Expr *p, int iReg, int nReg){
  1019   1019     assert( nReg>0 );
  1020         -  if( sqlite3ExprIsVector(p) ){
         1020  +  if( p && sqlite3ExprIsVector(p) ){
  1021   1021   #ifndef SQLITE_OMIT_SUBQUERY
  1022   1022       if( (p->flags & EP_xIsSelect) ){
  1023   1023         Vdbe *v = pParse->pVdbe;
  1024   1024         int iSelect = sqlite3CodeSubselect(pParse, p, 0, 0);
  1025   1025         sqlite3VdbeAddOp3(v, OP_Copy, iSelect, iReg, nReg-1);
  1026   1026       }else
  1027   1027   #endif

Changes to src/whereexpr.c.

   308    308     return rc;
   309    309   }
   310    310   #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
   311    311   
   312    312   
   313    313   #ifndef SQLITE_OMIT_VIRTUALTABLE
   314    314   /*
   315         -** Check to see if the given expression is of the form
          315  +** Check to see if the pExpr expression is a form that needs to be passed
          316  +** to the xBestIndex method of virtual tables.  Forms of interest include:
   316    317   **
   317         -**         column OP expr
          318  +**          Expression                   Virtual Table Operator
          319  +**          -----------------------      ---------------------------------
          320  +**      1.  column MATCH expr            SQLITE_INDEX_CONSTRAINT_MATCH
          321  +**      2.  column GLOB expr             SQLITE_INDEX_CONSTRAINT_GLOB
          322  +**      3.  column LIKE expr             SQLITE_INDEX_CONSTRAINT_LIKE
          323  +**      4.  column REGEXP expr           SQLITE_INDEX_CONSTRAINT_REGEXP
          324  +**      5.  column != expr               SQLITE_INDEX_CONSTRAINT_NE
          325  +**      6.  expr != column               SQLITE_INDEX_CONSTRAINT_NE
          326  +**      7.  column IS NOT expr           SQLITE_INDEX_CONSTRAINT_ISNOT
          327  +**      8.  expr IS NOT column           SQLITE_INDEX_CONSTRAINT_ISNOT
          328  +**      9.  column IS NOT NULL           SQLITE_INDEX_CONSTRAINT_ISNOTNULL
   318    329   **
   319         -** where OP is one of MATCH, GLOB, LIKE or REGEXP and "column" is a 
   320         -** column of a virtual table.
          330  +** In every case, "column" must be a column of a virtual table.  If there
          331  +** is a match, set *ppLeft to the "column" expression, set *ppRight to the 
          332  +** "expr" expression (even though in forms (6) and (8) the column is on the
          333  +** right and the expression is on the left).  Also set *peOp2 to the
          334  +** appropriate virtual table operator.  The return value is 1 or 2 if there
          335  +** is a match.  The usual return is 1, but if the RHS is also a column
          336  +** of virtual table in forms (5) or (7) then return 2.
   321    337   **
   322         -** If it is then return TRUE.  If not, return FALSE.
          338  +** If the expression matches none of the patterns above, return 0.
   323    339   */
   324         -static int isMatchOfColumn(
          340  +static int isAuxiliaryVtabOperator(
   325    341     Expr *pExpr,                    /* Test this expression */
   326         -  unsigned char *peOp2            /* OUT: 0 for MATCH, or else an op2 value */
          342  +  unsigned char *peOp2,           /* OUT: 0 for MATCH, or else an op2 value */
          343  +  Expr **ppLeft,                  /* Column expression to left of MATCH/op2 */
          344  +  Expr **ppRight                  /* Expression to left of MATCH/op2 */
   327    345   ){
   328         -  static const struct Op2 {
   329         -    const char *zOp;
   330         -    unsigned char eOp2;
   331         -  } aOp[] = {
   332         -    { "match",  SQLITE_INDEX_CONSTRAINT_MATCH },
   333         -    { "glob",   SQLITE_INDEX_CONSTRAINT_GLOB },
   334         -    { "like",   SQLITE_INDEX_CONSTRAINT_LIKE },
   335         -    { "regexp", SQLITE_INDEX_CONSTRAINT_REGEXP }
   336         -  };
   337         -  ExprList *pList;
   338         -  Expr *pCol;                     /* Column reference */
   339         -  int i;
   340         -
   341         -  if( pExpr->op!=TK_FUNCTION ){
   342         -    return 0;
   343         -  }
   344         -  pList = pExpr->x.pList;
   345         -  if( pList==0 || pList->nExpr!=2 ){
   346         -    return 0;
   347         -  }
   348         -  pCol = pList->a[1].pExpr;
   349         -  if( pCol->op!=TK_COLUMN || !IsVirtual(pCol->pTab) ){
   350         -    return 0;
   351         -  }
   352         -  for(i=0; i<ArraySize(aOp); i++){
   353         -    if( sqlite3StrICmp(pExpr->u.zToken, aOp[i].zOp)==0 ){
   354         -      *peOp2 = aOp[i].eOp2;
   355         -      return 1;
   356         -    }
          346  +  if( pExpr->op==TK_FUNCTION ){
          347  +    static const struct Op2 {
          348  +      const char *zOp;
          349  +      unsigned char eOp2;
          350  +    } aOp[] = {
          351  +      { "match",  SQLITE_INDEX_CONSTRAINT_MATCH },
          352  +      { "glob",   SQLITE_INDEX_CONSTRAINT_GLOB },
          353  +      { "like",   SQLITE_INDEX_CONSTRAINT_LIKE },
          354  +      { "regexp", SQLITE_INDEX_CONSTRAINT_REGEXP }
          355  +    };
          356  +    ExprList *pList;
          357  +    Expr *pCol;                     /* Column reference */
          358  +    int i;
          359  +
          360  +    pList = pExpr->x.pList;
          361  +    if( pList==0 || pList->nExpr!=2 ){
          362  +      return 0;
          363  +    }
          364  +    pCol = pList->a[1].pExpr;
          365  +    if( pCol->op!=TK_COLUMN || !IsVirtual(pCol->pTab) ){
          366  +      return 0;
          367  +    }
          368  +    for(i=0; i<ArraySize(aOp); i++){
          369  +      if( sqlite3StrICmp(pExpr->u.zToken, aOp[i].zOp)==0 ){
          370  +        *peOp2 = aOp[i].eOp2;
          371  +        *ppRight = pList->a[0].pExpr;
          372  +        *ppLeft = pCol;
          373  +        return 1;
          374  +      }
          375  +    }
          376  +  }else if( pExpr->op==TK_NE || pExpr->op==TK_ISNOT || pExpr->op==TK_NOTNULL ){
          377  +    int res = 0;
          378  +    Expr *pLeft = pExpr->pLeft;
          379  +    Expr *pRight = pExpr->pRight;
          380  +    if( pLeft->op==TK_COLUMN && IsVirtual(pLeft->pTab) ){
          381  +      res++;
          382  +    }
          383  +    if( pRight && pRight->op==TK_COLUMN && IsVirtual(pRight->pTab) ){
          384  +      res++;
          385  +      SWAP(Expr*, pLeft, pRight);
          386  +    }
          387  +    *ppLeft = pLeft;
          388  +    *ppRight = pRight;
          389  +    if( pExpr->op==TK_NE ) *peOp2 = SQLITE_INDEX_CONSTRAINT_NE;
          390  +    if( pExpr->op==TK_ISNOT ) *peOp2 = SQLITE_INDEX_CONSTRAINT_ISNOT;
          391  +    if( pExpr->op==TK_NOTNULL ) *peOp2 = SQLITE_INDEX_CONSTRAINT_ISNOTNULL;
          392  +    return res;
   357    393     }
   358    394     return 0;
   359    395   }
   360    396   #endif /* SQLITE_OMIT_VIRTUALTABLE */
   361    397   
   362    398   /*
   363    399   ** If the pBase expression originated in the ON or USING clause of
................................................................................
   600    636           sqlite3WhereSplit(pAndWC, pOrTerm->pExpr, TK_AND);
   601    637           sqlite3WhereExprAnalyze(pSrc, pAndWC);
   602    638           pAndWC->pOuter = pWC;
   603    639           if( !db->mallocFailed ){
   604    640             for(j=0, pAndTerm=pAndWC->a; j<pAndWC->nTerm; j++, pAndTerm++){
   605    641               assert( pAndTerm->pExpr );
   606    642               if( allowedOp(pAndTerm->pExpr->op) 
   607         -             || pAndTerm->eOperator==WO_MATCH 
          643  +             || pAndTerm->eOperator==WO_AUX
   608    644               ){
   609    645                 b |= sqlite3WhereGetMask(&pWInfo->sMaskSet, pAndTerm->leftCursor);
   610    646               }
   611    647             }
   612    648           }
   613    649           indexable &= b;
   614    650         }
................................................................................
  1182   1218         markTermAsChild(pWC, idxNew1, idxTerm);
  1183   1219         markTermAsChild(pWC, idxNew2, idxTerm);
  1184   1220       }
  1185   1221     }
  1186   1222   #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
  1187   1223   
  1188   1224   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1189         -  /* Add a WO_MATCH auxiliary term to the constraint set if the
  1190         -  ** current expression is of the form:  column MATCH expr.
         1225  +  /* Add a WO_AUX auxiliary term to the constraint set if the
         1226  +  ** current expression is of the form "column OP expr" where OP
         1227  +  ** is an operator that gets passed into virtual tables but which is
         1228  +  ** not normally optimized for ordinary tables.  In other words, OP
         1229  +  ** is one of MATCH, LIKE, GLOB, REGEXP, !=, IS, IS NOT, or NOT NULL.
  1191   1230     ** This information is used by the xBestIndex methods of
  1192   1231     ** virtual tables.  The native query optimizer does not attempt
  1193   1232     ** to do anything with MATCH functions.
  1194   1233     */
  1195         -  if( pWC->op==TK_AND && isMatchOfColumn(pExpr, &eOp2) ){
  1196         -    int idxNew;
         1234  +  if( pWC->op==TK_AND ){
  1197   1235       Expr *pRight, *pLeft;
  1198         -    WhereTerm *pNewTerm;
  1199         -    Bitmask prereqColumn, prereqExpr;
         1236  +    int res = isAuxiliaryVtabOperator(pExpr, &eOp2, &pLeft, &pRight);
         1237  +    while( res-- > 0 ){
         1238  +      int idxNew;
         1239  +      WhereTerm *pNewTerm;
         1240  +      Bitmask prereqColumn, prereqExpr;
  1200   1241   
  1201         -    pRight = pExpr->x.pList->a[0].pExpr;
  1202         -    pLeft = pExpr->x.pList->a[1].pExpr;
  1203         -    prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
  1204         -    prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
  1205         -    if( (prereqExpr & prereqColumn)==0 ){
  1206         -      Expr *pNewExpr;
  1207         -      pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
  1208         -                              0, sqlite3ExprDup(db, pRight, 0));
  1209         -      if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
  1210         -        ExprSetProperty(pNewExpr, EP_FromJoin);
         1242  +      prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
         1243  +      prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
         1244  +      if( (prereqExpr & prereqColumn)==0 ){
         1245  +        Expr *pNewExpr;
         1246  +        pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
         1247  +            0, sqlite3ExprDup(db, pRight, 0));
         1248  +        if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
         1249  +          ExprSetProperty(pNewExpr, EP_FromJoin);
         1250  +        }
         1251  +        idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
         1252  +        testcase( idxNew==0 );
         1253  +        pNewTerm = &pWC->a[idxNew];
         1254  +        pNewTerm->prereqRight = prereqExpr;
         1255  +        pNewTerm->leftCursor = pLeft->iTable;
         1256  +        pNewTerm->u.leftColumn = pLeft->iColumn;
         1257  +        pNewTerm->eOperator = WO_AUX;
         1258  +        pNewTerm->eMatchOp = eOp2;
         1259  +        markTermAsChild(pWC, idxNew, idxTerm);
         1260  +        pTerm = &pWC->a[idxTerm];
         1261  +        pTerm->wtFlags |= TERM_COPIED;
         1262  +        pNewTerm->prereqAll = pTerm->prereqAll;
  1211   1263         }
  1212         -      idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
  1213         -      testcase( idxNew==0 );
  1214         -      pNewTerm = &pWC->a[idxNew];
  1215         -      pNewTerm->prereqRight = prereqExpr;
  1216         -      pNewTerm->leftCursor = pLeft->iTable;
  1217         -      pNewTerm->u.leftColumn = pLeft->iColumn;
  1218         -      pNewTerm->eOperator = WO_MATCH;
  1219         -      pNewTerm->eMatchOp = eOp2;
  1220         -      markTermAsChild(pWC, idxNew, idxTerm);
  1221         -      pTerm = &pWC->a[idxTerm];
  1222         -      pTerm->wtFlags |= TERM_COPIED;
  1223         -      pNewTerm->prereqAll = pTerm->prereqAll;
         1264  +      SWAP(Expr*, pLeft, pRight);
  1224   1265       }
  1225   1266     }
  1226   1267   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  1227   1268   
  1228   1269     /* If there is a vector == or IS term - e.g. "(a, b) == (?, ?)" - create
  1229   1270     ** new terms for each component comparison - "a = ?" and "b = ?".  The
  1230   1271     ** new terms completely replace the original vector comparison, which is

Added test/bestindex5.test.

            1  +# 2017 September 10
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# Test the virtual table interface. In particular the xBestIndex
           12  +# method.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix bestindex4
           18  +
           19  +ifcapable !vtab {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +#-------------------------------------------------------------------------
           25  +# Virtual table callback for a virtual table named $tbl.
           26  +#  
           27  +proc vtab_cmd {method args} {
           28  +
           29  +  set binops(ne)    !=
           30  +  set binops(eq)    =
           31  +  set binops(isnot) "IS NOT"
           32  +  set binops(is)    "IS"
           33  +
           34  +  set unops(isnotnull) "IS NOT NULL"
           35  +  set unops(isnull)    "IS NULL"
           36  +
           37  +  set cols(0) a
           38  +  set cols(1) b
           39  +  set cols(2) c
           40  +
           41  +  switch -- $method {
           42  +    xConnect {
           43  +      return "CREATE TABLE t1(a, b, c)"
           44  +    }
           45  +
           46  +    xBestIndex {
           47  +      foreach {clist orderby mask} $args {}
           48  +
           49  +      set cost 1000000.0
           50  +      set ret [list]
           51  +      set str [list]
           52  +
           53  +      set v 0
           54  +      for {set i 0} {$i < [llength $clist]} {incr i} {
           55  +        array unset C
           56  +        array set C [lindex $clist $i]
           57  +        if {$C(usable)} {
           58  +          if {[info exists binops($C(op))]} {
           59  +            lappend ret omit $i
           60  +            lappend str "$cols($C(column)) $binops($C(op)) %$v%"
           61  +            incr v
           62  +            set cost [expr $cost / 2]
           63  +          }
           64  +          if {[info exists unops($C(op))]} {
           65  +            lappend ret omit $i
           66  +            lappend str "$cols($C(column)) $unops($C(op))"
           67  +            incr v
           68  +            set cost [expr $cost / 2]
           69  +          }
           70  +        }
           71  +      }
           72  +
           73  +      lappend ret idxstr [join $str " AND "]
           74  +      lappend ret cost $cost
           75  +      return $ret
           76  +    }
           77  +
           78  +    xFilter {
           79  +      set q [lindex $args 1]
           80  +      set a [lindex $args 2]
           81  +      for {set v 0} {$v < [llength $a]} {incr v} {
           82  +        set val [lindex $a $v]
           83  +        set q [string map [list %$v% '$val'] $q]
           84  +      }
           85  +      if {$q==""} { set q 1 }
           86  +      lappend ::xFilterQueries "WHERE $q"
           87  +      return [list sql "SELECT rowid, * FROM t1x WHERE $q"]
           88  +    }
           89  +  }
           90  +  return ""
           91  +}
           92  +
           93  +proc vtab_simple {method args} {
           94  +  switch -- $method {
           95  +    xConnect {
           96  +      return "CREATE TABLE t2(x)"
           97  +    }
           98  +    xBestIndex {
           99  +      return [list cost 999999.0]
          100  +    }
          101  +    xFilter {
          102  +      return [list sql "SELECT rowid, * FROM t2x"]
          103  +    }
          104  +  }
          105  +  return ""
          106  +}
          107  +
          108  +register_tcl_module db
          109  +
          110  +proc do_vtab_query_test {tn query result} {
          111  +  set ::xFilterQueries [list]
          112  +  uplevel [list
          113  +    do_test $tn [string map [list %QUERY% $query] {
          114  +      set r [execsql {%QUERY%}]
          115  +      set r [concat $::xFilterQueries $r]
          116  +      set r
          117  +    }] [list {*}$result]
          118  +  ]
          119  +}
          120  +
          121  +do_execsql_test 1.0 {
          122  +  CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd');
          123  +  CREATE TABLE t1x(a INTEGER, b TEXT, c REAL);
          124  +  INSERT INTO t1x VALUES(1, 2, 3);
          125  +  INSERT INTO t1x VALUES(4, 5, 6);
          126  +  INSERT INTO t1x VALUES(7, 8, 9);
          127  +
          128  +  CREATE VIRTUAL TABLE t2 USING tcl('vtab_simple');
          129  +  CREATE TABLE t2x(x INTEGER);
          130  +  INSERT INTO t2x VALUES(1);
          131  +}
          132  +
          133  +do_vtab_query_test 1.1 { SELECT * FROM t1 WHERE a!='hello'; } {
          134  +  "WHERE a != 'hello'"
          135  +  1 2 3.0 4 5 6.0 7 8 9.0
          136  +}
          137  +
          138  +do_vtab_query_test 1.2.1 { SELECT * FROM t1 WHERE b!=8 } {
          139  +  "WHERE b != '8'"
          140  +  1 2 3.0 4 5 6.0
          141  +}
          142  +do_vtab_query_test 1.2.2 { SELECT * FROM t1 WHERE 8!=b } {
          143  +  "WHERE b != '8'"
          144  +  1 2 3.0 4 5 6.0
          145  +}
          146  +
          147  +do_vtab_query_test 1.3 { SELECT * FROM t1 WHERE c IS NOT 3 } {
          148  +  "WHERE c IS NOT '3'"
          149  +  4 5 6.0 7 8 9.0
          150  +}
          151  +do_vtab_query_test 1.3.2 { SELECT * FROM t1 WHERE 3 IS NOT c } {
          152  +  "WHERE c IS NOT '3'"
          153  +  4 5 6.0 7 8 9.0
          154  +}
          155  +
          156  +do_vtab_query_test 1.4.1 { SELECT * FROM t1, t2 WHERE x != a } {
          157  +  "WHERE a != '1'"
          158  +  4 5 6.0 1   7 8 9.0 1
          159  +}
          160  +do_vtab_query_test 1.4.2 { SELECT * FROM t1, t2 WHERE a != x } {
          161  +  "WHERE a != '1'"
          162  +  4 5 6.0 1   7 8 9.0 1
          163  +}
          164  +
          165  +do_vtab_query_test 1.5.1 { SELECT * FROM t1 WHERE a IS NOT NULL } {
          166  +  "WHERE a IS NOT NULL"
          167  +  1 2 3.0 4 5 6.0 7 8 9.0
          168  +}
          169  +do_vtab_query_test 1.5.2 { SELECT * FROM t1 WHERE NULL IS NOT a } {
          170  +  "WHERE a IS NOT ''"
          171  +  1 2 3.0 4 5 6.0 7 8 9.0
          172  +}
          173  +
          174  +do_vtab_query_test 1.6.1 { SELECT * FROM t1 WHERE a IS NULL } {
          175  +  "WHERE a IS NULL"
          176  +}
          177  +
          178  +do_vtab_query_test 1.6.2 { SELECT * FROM t1 WHERE NULL IS a } {
          179  +  "WHERE a IS ''"
          180  +}
          181  +
          182  +do_vtab_query_test 1.7.1 { SELECT * FROM t1 WHERE (a, b) IS (1, 2) } {
          183  +  "WHERE a IS '1' AND b IS '2'"
          184  +  1 2 3.0
          185  +}
          186  +do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } {
          187  +  {WHERE b IS '5' AND a IS '4'} 
          188  +  4 5 6.0
          189  +}
          190  +
          191  +#---------------------------------------------------------------------
          192  +do_execsql_test 2.0.0 {
          193  +  DELETE FROM t1x;
          194  +  INSERT INTO t1x VALUES('a', 'b', 'c');
          195  +}
          196  +do_execsql_test 2.0.1 { SELECT * FROM t1 } {a b c}
          197  +do_execsql_test 2.0.2 { SELECT * FROM t1 WHERE (a, b) != ('a', 'b'); } {}
          198  +
          199  +do_execsql_test 2.1.0 {
          200  +  DELETE FROM t1x;
          201  +  INSERT INTO t1x VALUES(7, 8, 9);
          202  +}
          203  +do_execsql_test 2.1.1 { SELECT * FROM t1 } {7 8 9.0}
          204  +do_execsql_test 2.1.2 { SELECT * FROM t1 WHERE (a, b) != (7, '8') } {}
          205  +do_execsql_test 2.1.3 { SELECT * FROM t1 WHERE a!=7 OR b!='8' }
          206  +do_execsql_test 2.1.4 { SELECT * FROM t1 WHERE a!=7 OR b!='8' }
          207  +
          208  +
          209  +do_execsql_test 2.2.1 {
          210  +  CREATE TABLE t3(a INTEGER, b TEXT);
          211  +  INSERT INTO t3 VALUES(45, 46);
          212  +}
          213  +do_execsql_test 2.2.2 { SELECT * FROM t3 WHERE (a, b) != (45, 46); }
          214  +do_execsql_test 2.2.3 { SELECT * FROM t3 WHERE (a, b) != ('45', '46'); }
          215  +do_execsql_test 2.2.4 { SELECT * FROM t3 WHERE (a, b) == (45, 46); } {45 46}
          216  +do_execsql_test 2.2.5 { SELECT * FROM t3 WHERE (a, b) == ('45', '46'); } {45 46}
          217  +
          218  +#---------------------------------------------------------------------
          219  +# Test the != operator on a virtual table with column affinities.
          220  +#
          221  +proc vtab_simple_integer {method args} {
          222  +  switch -- $method {
          223  +    xConnect {
          224  +      return "CREATE TABLE t4(x INTEGER)"
          225  +    }
          226  +    xBestIndex {
          227  +      return [list cost 999999.0]
          228  +    }
          229  +    xFilter {
          230  +      return [list sql "SELECT rowid, * FROM t4x"]
          231  +    }
          232  +  }
          233  +  return ""
          234  +}
          235  +
          236  +do_execsql_test 3.0 {
          237  +  CREATE TABLE t4x(a INTEGER);
          238  +  INSERT INTO t4x VALUES(245);
          239  +  CREATE VIRTUAL TABLE t4 USING tcl('vtab_simple_integer');
          240  +}
          241  +do_execsql_test 3.1 { SELECT rowid, * FROM t4 WHERE x=245; } {1 245}
          242  +do_execsql_test 3.2 { SELECT rowid, * FROM t4 WHERE x='245'; } {1 245}
          243  +do_execsql_test 3.3 { SELECT rowid, * FROM t4 WHERE x!=245; } {}
          244  +do_execsql_test 3.4 { SELECT rowid, * FROM t4 WHERE x!='245'; } {}
          245  +
          246  +do_execsql_test 3.5 { SELECT rowid, * FROM t4 WHERE rowid!=1 OR x!='245'; } {}
          247  +
          248  +
          249  +finish_test
          250  +