/ Check-in [277a5b40]
Login

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

Overview
Comment:Add experimental support for LIKE, GLOB and REGEXP to the virtual table interface.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vtab-like-operator
Files: files | file ages | folders
SHA1: 277a5b4027d4c2caba8143228a4f7d6df899dbb4
User & Date: dan 2015-11-23 21:09:54
Context
2015-11-24
17:39
Add further tests and related fixes for GLOB/REGEXP/LIKE support in virtual tables. check-in: c5e9fd0d user: dan tags: vtab-like-operator
2015-11-23
21:09
Add experimental support for LIKE, GLOB and REGEXP to the virtual table interface. check-in: 277a5b40 user: dan tags: vtab-like-operator
2015-11-21
19:43
Fix an obscure memory leak found by libfuzzer that may occur under some circumstances if expanding a "*" expression causes a SELECT to return more than 32767 columns. check-in: 60de5f23 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

  5705   5705   ** CAPI3REF: Virtual Table Constraint Operator Codes
  5706   5706   **
  5707   5707   ** These macros defined the allowed values for the
  5708   5708   ** [sqlite3_index_info].aConstraint[].op field.  Each value represents
  5709   5709   ** an operator that is part of a constraint term in the wHERE clause of
  5710   5710   ** a query that uses a [virtual table].
  5711   5711   */
  5712         -#define SQLITE_INDEX_CONSTRAINT_EQ    2
  5713         -#define SQLITE_INDEX_CONSTRAINT_GT    4
  5714         -#define SQLITE_INDEX_CONSTRAINT_LE    8
  5715         -#define SQLITE_INDEX_CONSTRAINT_LT    16
  5716         -#define SQLITE_INDEX_CONSTRAINT_GE    32
  5717         -#define SQLITE_INDEX_CONSTRAINT_MATCH 64
         5712  +#define SQLITE_INDEX_CONSTRAINT_EQ      2
         5713  +#define SQLITE_INDEX_CONSTRAINT_GT      4
         5714  +#define SQLITE_INDEX_CONSTRAINT_LE      8
         5715  +#define SQLITE_INDEX_CONSTRAINT_LT     16
         5716  +#define SQLITE_INDEX_CONSTRAINT_GE     32
         5717  +#define SQLITE_INDEX_CONSTRAINT_MATCH  64
         5718  +#define SQLITE_INDEX_CONSTRAINT_LIKE   65
         5719  +#define SQLITE_INDEX_CONSTRAINT_GLOB   66
         5720  +#define SQLITE_INDEX_CONSTRAINT_REGEXP 67
  5718   5721   
  5719   5722   /*
  5720   5723   ** CAPI3REF: Register A Virtual Table Implementation
  5721   5724   ** METHOD: sqlite3
  5722   5725   **
  5723   5726   ** ^These routines are used to register a new [virtual table module] name.
  5724   5727   ** ^Module names must be registered before

Changes to src/test8.c.

   845    845             zOp = ">"; break;
   846    846           case SQLITE_INDEX_CONSTRAINT_LE:
   847    847             zOp = "<="; break;
   848    848           case SQLITE_INDEX_CONSTRAINT_GE:
   849    849             zOp = ">="; break;
   850    850           case SQLITE_INDEX_CONSTRAINT_MATCH:
   851    851             zOp = "LIKE"; break;
          852  +        case SQLITE_INDEX_CONSTRAINT_LIKE:
          853  +          zOp = "like"; break;
          854  +        case SQLITE_INDEX_CONSTRAINT_GLOB:
          855  +          zOp = "glob"; break;
          856  +        case SQLITE_INDEX_CONSTRAINT_REGEXP:
          857  +          zOp = "regexp"; break;
   852    858         }
   853    859         if( zOp[0]=='L' ){
   854    860           zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", 
   855    861                                  zSep, zCol);
   856    862         } else {
   857    863           zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zCol, zOp);
   858    864         }

Changes to src/where.c.

   889    889       if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
   890    890       if( pTerm->wtFlags & TERM_VNULL ) continue;
   891    891       assert( pTerm->u.leftColumn>=(-1) );
   892    892       pIdxCons[j].iColumn = pTerm->u.leftColumn;
   893    893       pIdxCons[j].iTermOffset = i;
   894    894       op = (u8)pTerm->eOperator & WO_ALL;
   895    895       if( op==WO_IN ) op = WO_EQ;
          896  +    if( op==WO_MATCH ){
          897  +      op = pTerm->eMatchOp;
          898  +    }
   896    899       pIdxCons[j].op = op;
   897    900       /* The direct assignment in the previous line is possible only because
   898    901       ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
   899    902       ** following asserts verify this fact. */
   900    903       assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
   901    904       assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
   902    905       assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );

Changes to src/whereInt.h.

   249    249       WhereOrInfo *pOrInfo;   /* Extra information if (eOperator & WO_OR)!=0 */
   250    250       WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */
   251    251     } u;
   252    252     LogEst truthProb;       /* Probability of truth for this expression */
   253    253     u16 eOperator;          /* A WO_xx value describing <op> */
   254    254     u16 wtFlags;            /* TERM_xxx bit flags.  See below */
   255    255     u8 nChild;              /* Number of children that must disable us */
          256  +  u8 eMatchOp;            /* Op for vtab MATCH/LIKE/GLOB/REGEXP terms */
   256    257     WhereClause *pWC;       /* The clause this term is part of */
   257    258     Bitmask prereqRight;    /* Bitmask of tables used by pExpr->pRight */
   258    259     Bitmask prereqAll;      /* Bitmask of tables referenced by pExpr */
   259    260   };
   260    261   
   261    262   /*
   262    263   ** Allowed values of WhereTerm.wtFlags

Changes to src/whereexpr.c.

   278    278   ** Check to see if the given expression is of the form
   279    279   **
   280    280   **         column MATCH expr
   281    281   **
   282    282   ** If it is then return TRUE.  If not, return FALSE.
   283    283   */
   284    284   static int isMatchOfColumn(
   285         -  Expr *pExpr      /* Test this expression */
          285  +  Expr *pExpr,                    /* Test this expression */
          286  +  unsigned char *peOp2            /* OUT: 0 for MATCH, or else an op2 value */
   286    287   ){
          288  +  struct Op2 {
          289  +    const char *zOp;
          290  +    unsigned char eOp2;
          291  +  } aOp[] = {
          292  +    { "match", SQLITE_INDEX_CONSTRAINT_MATCH },
          293  +    { "glob",  SQLITE_INDEX_CONSTRAINT_GLOB },
          294  +    { "like",  SQLITE_INDEX_CONSTRAINT_LIKE },
          295  +    { "regex", SQLITE_INDEX_CONSTRAINT_REGEXP }
          296  +  };
   287    297     ExprList *pList;
          298  +  int i;
   288    299   
   289    300     if( pExpr->op!=TK_FUNCTION ){
   290    301       return 0;
   291    302     }
   292         -  if( sqlite3StrICmp(pExpr->u.zToken,"match")!=0 ){
   293         -    return 0;
   294         -  }
   295    303     pList = pExpr->x.pList;
   296    304     if( pList->nExpr!=2 ){
   297    305       return 0;
   298    306     }
   299    307     if( pList->a[1].pExpr->op != TK_COLUMN ){
   300    308       return 0;
   301    309     }
   302         -  return 1;
          310  +  for(i=0; i<ArraySize(aOp); i++){
          311  +    if( sqlite3StrICmp(pExpr->u.zToken, aOp[i].zOp)==0 ){
          312  +      *peOp2 = aOp[i].eOp2;
          313  +      return 1;
          314  +    }
          315  +  }
          316  +  return 0;
   303    317   }
   304    318   #endif /* SQLITE_OMIT_VIRTUALTABLE */
   305    319   
   306    320   /*
   307    321   ** If the pBase expression originated in the ON or USING clause of
   308    322   ** a join, then transfer the appropriate markings over to derived.
   309    323   */
................................................................................
   872    886     Bitmask extraRight = 0;          /* Extra dependencies on LEFT JOIN */
   873    887     Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
   874    888     int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
   875    889     int noCase = 0;                  /* uppercase equivalent to lowercase */
   876    890     int op;                          /* Top-level operator.  pExpr->op */
   877    891     Parse *pParse = pWInfo->pParse;  /* Parsing context */
   878    892     sqlite3 *db = pParse->db;        /* Database connection */
          893  +  unsigned char eOp2;              /* op2 value for LIKE/REGEXP/GLOB */
   879    894   
   880    895     if( db->mallocFailed ){
   881    896       return;
   882    897     }
   883    898     pTerm = &pWC->a[idxTerm];
   884    899     pMaskSet = &pWInfo->sMaskSet;
   885    900     pExpr = pTerm->pExpr;
................................................................................
  1095   1110   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1096   1111     /* Add a WO_MATCH auxiliary term to the constraint set if the
  1097   1112     ** current expression is of the form:  column MATCH expr.
  1098   1113     ** This information is used by the xBestIndex methods of
  1099   1114     ** virtual tables.  The native query optimizer does not attempt
  1100   1115     ** to do anything with MATCH functions.
  1101   1116     */
  1102         -  if( isMatchOfColumn(pExpr) ){
         1117  +  if( isMatchOfColumn(pExpr, &eOp2) ){
  1103   1118       int idxNew;
  1104   1119       Expr *pRight, *pLeft;
  1105   1120       WhereTerm *pNewTerm;
  1106   1121       Bitmask prereqColumn, prereqExpr;
  1107   1122   
  1108   1123       pRight = pExpr->x.pList->a[0].pExpr;
  1109   1124       pLeft = pExpr->x.pList->a[1].pExpr;
................................................................................
  1116   1131         idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
  1117   1132         testcase( idxNew==0 );
  1118   1133         pNewTerm = &pWC->a[idxNew];
  1119   1134         pNewTerm->prereqRight = prereqExpr;
  1120   1135         pNewTerm->leftCursor = pLeft->iTable;
  1121   1136         pNewTerm->u.leftColumn = pLeft->iColumn;
  1122   1137         pNewTerm->eOperator = WO_MATCH;
         1138  +      pNewTerm->eMatchOp = eOp2;
  1123   1139         markTermAsChild(pWC, idxNew, idxTerm);
  1124   1140         pTerm = &pWC->a[idxTerm];
  1125   1141         pTerm->wtFlags |= TERM_COPIED;
  1126   1142         pNewTerm->prereqAll = pTerm->prereqAll;
  1127   1143       }
  1128   1144     }
  1129   1145   #endif /* SQLITE_OMIT_VIRTUALTABLE */

Changes to test/vtab1.test.

  1302   1302     1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5}
  1303   1303       {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James}
  1304   1304   
  1305   1305     1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4}
  1306   1306       {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K}
  1307   1307   
  1308   1308     1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
  1309         -    {xFilter {SELECT rowid, * FROM 't6'}}
         1309  +    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%}
  1310   1310   
  1311   1311     1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
  1312         -    {xFilter {SELECT rowid, * FROM 't6'}}
         1312  +    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%}
  1313   1313   } {
  1314   1314     set echo_module {}
  1315   1315     do_execsql_test 18.$tn.1 $sql $res
  1316   1316     do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
  1317   1317   }
  1318   1318   
  1319   1319   do_execsql_test 18.2.0 {  PRAGMA case_sensitive_like = ON }
  1320   1320   foreach {tn sql res filter} {
  1321   1321     2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
  1322         -    {xFilter {SELECT rowid, * FROM 't6'}}
         1322  +    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%}
  1323   1323   
  1324   1324     2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
  1325         -    {xFilter {SELECT rowid, * FROM 't6'}}
         1325  +    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%}
  1326   1326   } {
  1327   1327     set echo_module {}
  1328   1328     do_execsql_test 18.$tn.1 $sql $res
  1329   1329     do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
  1330   1330   }
  1331   1331   do_execsql_test 18.2.x {  PRAGMA case_sensitive_like = OFF }
  1332   1332   

Added test/vtabH.test.

            1  +# 2015 Nov 24
            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  +# This file implements regression tests for SQLite library.
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix vtabH
           17  +
           18  +ifcapable !vtab {
           19  +  finish_test
           20  +  return
           21  +}
           22  +
           23  +register_echo_module db
           24  +
           25  +do_execsql_test 1.0 {
           26  +  CREATE TABLE t6(a, b TEXT);
           27  +  CREATE INDEX i6 ON t6(b, a);
           28  +  CREATE VIRTUAL TABLE e6 USING echo(t6);
           29  +}
           30  +
           31  +foreach {tn sql expect} {
           32  +  1 "SELECT * FROM e6 WHERE b LIKE 'abc'" {
           33  +    xBestIndex {SELECT rowid, * FROM 't6' WHERE b like ?} 
           34  +    xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} abc
           35  +  }
           36  +
           37  +  2 "SELECT * FROM e6 WHERE b GLOB 'abc'" {
           38  +    xBestIndex {SELECT rowid, * FROM 't6' WHERE b glob ?} 
           39  +    xFilter {SELECT rowid, * FROM 't6' WHERE b glob ?} abc
           40  +  }
           41  +} {
           42  +  do_test 1.$tn {
           43  +    set echo_module {}
           44  +    execsql $sql
           45  +    set ::echo_module
           46  +  } [list {*}$expect]
           47  +}
           48  +
           49  +finish_test