/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

5705
5706
5707
5708
5709
5710
5711
5712
5713
5714
5715
5716
5717



5718
5719
5720
5721
5722
5723
5724
** CAPI3REF: Virtual Table Constraint Operator Codes
**
** These macros defined the allowed values for the
** [sqlite3_index_info].aConstraint[].op field.  Each value represents
** an operator that is part of a constraint term in the wHERE clause of
** a query that uses a [virtual table].
*/
#define SQLITE_INDEX_CONSTRAINT_EQ    2
#define SQLITE_INDEX_CONSTRAINT_GT    4
#define SQLITE_INDEX_CONSTRAINT_LE    8
#define SQLITE_INDEX_CONSTRAINT_LT    16
#define SQLITE_INDEX_CONSTRAINT_GE    32
#define SQLITE_INDEX_CONSTRAINT_MATCH 64




/*
** CAPI3REF: Register A Virtual Table Implementation
** METHOD: sqlite3
**
** ^These routines are used to register a new [virtual table module] name.
** ^Module names must be registered before







|
|
|
|
|
|
>
>
>







5705
5706
5707
5708
5709
5710
5711
5712
5713
5714
5715
5716
5717
5718
5719
5720
5721
5722
5723
5724
5725
5726
5727
** CAPI3REF: Virtual Table Constraint Operator Codes
**
** These macros defined the allowed values for the
** [sqlite3_index_info].aConstraint[].op field.  Each value represents
** an operator that is part of a constraint term in the wHERE clause of
** a query that uses a [virtual table].
*/
#define SQLITE_INDEX_CONSTRAINT_EQ      2
#define SQLITE_INDEX_CONSTRAINT_GT      4
#define SQLITE_INDEX_CONSTRAINT_LE      8
#define SQLITE_INDEX_CONSTRAINT_LT     16
#define SQLITE_INDEX_CONSTRAINT_GE     32
#define SQLITE_INDEX_CONSTRAINT_MATCH  64
#define SQLITE_INDEX_CONSTRAINT_LIKE   65
#define SQLITE_INDEX_CONSTRAINT_GLOB   66
#define SQLITE_INDEX_CONSTRAINT_REGEXP 67

/*
** CAPI3REF: Register A Virtual Table Implementation
** METHOD: sqlite3
**
** ^These routines are used to register a new [virtual table module] name.
** ^Module names must be registered before

Changes to src/test8.c.

845
846
847
848
849
850
851






852
853
854
855
856
857
858
          zOp = ">"; break;
        case SQLITE_INDEX_CONSTRAINT_LE:
          zOp = "<="; break;
        case SQLITE_INDEX_CONSTRAINT_GE:
          zOp = ">="; break;
        case SQLITE_INDEX_CONSTRAINT_MATCH:
          zOp = "LIKE"; break;






      }
      if( zOp[0]=='L' ){
        zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", 
                               zSep, zCol);
      } else {
        zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zCol, zOp);
      }







>
>
>
>
>
>







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

Changes to src/where.c.

889
890
891
892
893
894
895



896
897
898
899
900
901
902
    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    assert( pTerm->u.leftColumn>=(-1) );
    pIdxCons[j].iColumn = pTerm->u.leftColumn;
    pIdxCons[j].iTermOffset = i;
    op = (u8)pTerm->eOperator & WO_ALL;
    if( op==WO_IN ) op = WO_EQ;



    pIdxCons[j].op = op;
    /* The direct assignment in the previous line is possible only because
    ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
    ** following asserts verify this fact. */
    assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
    assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
    assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );







>
>
>







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

Changes to src/whereInt.h.

249
250
251
252
253
254
255

256
257
258
259
260
261
262
    WhereOrInfo *pOrInfo;   /* Extra information if (eOperator & WO_OR)!=0 */
    WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */
  } u;
  LogEst truthProb;       /* Probability of truth for this expression */
  u16 eOperator;          /* A WO_xx value describing <op> */
  u16 wtFlags;            /* TERM_xxx bit flags.  See below */
  u8 nChild;              /* Number of children that must disable us */

  WhereClause *pWC;       /* The clause this term is part of */
  Bitmask prereqRight;    /* Bitmask of tables used by pExpr->pRight */
  Bitmask prereqAll;      /* Bitmask of tables referenced by pExpr */
};

/*
** Allowed values of WhereTerm.wtFlags







>







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

/*
** Allowed values of WhereTerm.wtFlags

Changes to src/whereexpr.c.

278
279
280
281
282
283
284
285

286









287

288
289
290
291
292
293
294
295
296
297
298
299
300
301



302



303
304
305
306
307
308
309
...
872
873
874
875
876
877
878

879
880
881
882
883
884
885
....
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
....
1116
1117
1118
1119
1120
1121
1122

1123
1124
1125
1126
1127
1128
1129
** Check to see if the given expression is of the form
**
**         column MATCH expr
**
** If it is then return TRUE.  If not, return FALSE.
*/
static int isMatchOfColumn(
  Expr *pExpr      /* Test this expression */

){









  ExprList *pList;


  if( pExpr->op!=TK_FUNCTION ){
    return 0;
  }
  if( sqlite3StrICmp(pExpr->u.zToken,"match")!=0 ){
    return 0;
  }
  pList = pExpr->x.pList;
  if( pList->nExpr!=2 ){
    return 0;
  }
  if( pList->a[1].pExpr->op != TK_COLUMN ){
    return 0;
  }



  return 1;



}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*
** If the pBase expression originated in the ON or USING clause of
** a join, then transfer the appropriate markings over to derived.
*/
................................................................................
  Bitmask extraRight = 0;          /* Extra dependencies on LEFT JOIN */
  Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
  int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
  int noCase = 0;                  /* uppercase equivalent to lowercase */
  int op;                          /* Top-level operator.  pExpr->op */
  Parse *pParse = pWInfo->pParse;  /* Parsing context */
  sqlite3 *db = pParse->db;        /* Database connection */


  if( db->mallocFailed ){
    return;
  }
  pTerm = &pWC->a[idxTerm];
  pMaskSet = &pWInfo->sMaskSet;
  pExpr = pTerm->pExpr;
................................................................................
#ifndef SQLITE_OMIT_VIRTUALTABLE
  /* Add a WO_MATCH auxiliary term to the constraint set if the
  ** current expression is of the form:  column MATCH expr.
  ** This information is used by the xBestIndex methods of
  ** virtual tables.  The native query optimizer does not attempt
  ** to do anything with MATCH functions.
  */
  if( isMatchOfColumn(pExpr) ){
    int idxNew;
    Expr *pRight, *pLeft;
    WhereTerm *pNewTerm;
    Bitmask prereqColumn, prereqExpr;

    pRight = pExpr->x.pList->a[0].pExpr;
    pLeft = pExpr->x.pList->a[1].pExpr;
................................................................................
      idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
      testcase( idxNew==0 );
      pNewTerm = &pWC->a[idxNew];
      pNewTerm->prereqRight = prereqExpr;
      pNewTerm->leftCursor = pLeft->iTable;
      pNewTerm->u.leftColumn = pLeft->iColumn;
      pNewTerm->eOperator = WO_MATCH;

      markTermAsChild(pWC, idxNew, idxTerm);
      pTerm = &pWC->a[idxTerm];
      pTerm->wtFlags |= TERM_COPIED;
      pNewTerm->prereqAll = pTerm->prereqAll;
    }
  }
#endif /* SQLITE_OMIT_VIRTUALTABLE */







|
>

>
>
>
>
>
>
>
>
>

>




<
<
<







>
>
>
|
>
>
>







 







>







 







|







 







>







278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302



303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
...
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
....
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
....
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
** Check to see if the given expression is of the form
**
**         column MATCH expr
**
** If it is then return TRUE.  If not, return FALSE.
*/
static int isMatchOfColumn(
  Expr *pExpr,                    /* Test this expression */
  unsigned char *peOp2            /* OUT: 0 for MATCH, or else an op2 value */
){
  struct Op2 {
    const char *zOp;
    unsigned char eOp2;
  } aOp[] = {
    { "match", SQLITE_INDEX_CONSTRAINT_MATCH },
    { "glob",  SQLITE_INDEX_CONSTRAINT_GLOB },
    { "like",  SQLITE_INDEX_CONSTRAINT_LIKE },
    { "regex", SQLITE_INDEX_CONSTRAINT_REGEXP }
  };
  ExprList *pList;
  int i;

  if( pExpr->op!=TK_FUNCTION ){
    return 0;
  }



  pList = pExpr->x.pList;
  if( pList->nExpr!=2 ){
    return 0;
  }
  if( pList->a[1].pExpr->op != TK_COLUMN ){
    return 0;
  }
  for(i=0; i<ArraySize(aOp); i++){
    if( sqlite3StrICmp(pExpr->u.zToken, aOp[i].zOp)==0 ){
      *peOp2 = aOp[i].eOp2;
      return 1;
    }
  }
  return 0;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*
** If the pBase expression originated in the ON or USING clause of
** a join, then transfer the appropriate markings over to derived.
*/
................................................................................
  Bitmask extraRight = 0;          /* Extra dependencies on LEFT JOIN */
  Expr *pStr1 = 0;                 /* RHS of LIKE/GLOB operator */
  int isComplete = 0;              /* RHS of LIKE/GLOB ends with wildcard */
  int noCase = 0;                  /* uppercase equivalent to lowercase */
  int op;                          /* Top-level operator.  pExpr->op */
  Parse *pParse = pWInfo->pParse;  /* Parsing context */
  sqlite3 *db = pParse->db;        /* Database connection */
  unsigned char eOp2;              /* op2 value for LIKE/REGEXP/GLOB */

  if( db->mallocFailed ){
    return;
  }
  pTerm = &pWC->a[idxTerm];
  pMaskSet = &pWInfo->sMaskSet;
  pExpr = pTerm->pExpr;
................................................................................
#ifndef SQLITE_OMIT_VIRTUALTABLE
  /* Add a WO_MATCH auxiliary term to the constraint set if the
  ** current expression is of the form:  column MATCH expr.
  ** This information is used by the xBestIndex methods of
  ** virtual tables.  The native query optimizer does not attempt
  ** to do anything with MATCH functions.
  */
  if( isMatchOfColumn(pExpr, &eOp2) ){
    int idxNew;
    Expr *pRight, *pLeft;
    WhereTerm *pNewTerm;
    Bitmask prereqColumn, prereqExpr;

    pRight = pExpr->x.pList->a[0].pExpr;
    pLeft = pExpr->x.pList->a[1].pExpr;
................................................................................
      idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
      testcase( idxNew==0 );
      pNewTerm = &pWC->a[idxNew];
      pNewTerm->prereqRight = prereqExpr;
      pNewTerm->leftCursor = pLeft->iTable;
      pNewTerm->u.leftColumn = pLeft->iColumn;
      pNewTerm->eOperator = WO_MATCH;
      pNewTerm->eMatchOp = eOp2;
      markTermAsChild(pWC, idxNew, idxTerm);
      pTerm = &pWC->a[idxTerm];
      pTerm->wtFlags |= TERM_COPIED;
      pNewTerm->prereqAll = pTerm->prereqAll;
    }
  }
#endif /* SQLITE_OMIT_VIRTUALTABLE */

Changes to test/vtab1.test.

1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
  1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James}

  1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K}

  1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6'}}

  1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6'}}
} {
  set echo_module {}
  do_execsql_test 18.$tn.1 $sql $res
  do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
}

do_execsql_test 18.2.0 {  PRAGMA case_sensitive_like = ON }
foreach {tn sql res filter} {
  2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6'}}

  2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
    {xFilter {SELECT rowid, * FROM 't6'}}
} {
  set echo_module {}
  do_execsql_test 18.$tn.1 $sql $res
  do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
}
do_execsql_test 18.2.x {  PRAGMA case_sensitive_like = OFF }








|


|









|


|







1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
  1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James}

  1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K}

  1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%}

  1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%}
} {
  set echo_module {}
  do_execsql_test 18.$tn.1 $sql $res
  do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
}

do_execsql_test 18.2.0 {  PRAGMA case_sensitive_like = ON }
foreach {tn sql res filter} {
  2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%}

  2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%}
} {
  set echo_module {}
  do_execsql_test 18.$tn.1 $sql $res
  do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
}
do_execsql_test 18.2.x {  PRAGMA case_sensitive_like = OFF }

Added test/vtabH.test.



































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# 2015 Nov 24
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix vtabH

ifcapable !vtab {
  finish_test
  return
}

register_echo_module db

do_execsql_test 1.0 {
  CREATE TABLE t6(a, b TEXT);
  CREATE INDEX i6 ON t6(b, a);
  CREATE VIRTUAL TABLE e6 USING echo(t6);
}

foreach {tn sql expect} {
  1 "SELECT * FROM e6 WHERE b LIKE 'abc'" {
    xBestIndex {SELECT rowid, * FROM 't6' WHERE b like ?} 
    xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} abc
  }

  2 "SELECT * FROM e6 WHERE b GLOB 'abc'" {
    xBestIndex {SELECT rowid, * FROM 't6' WHERE b glob ?} 
    xFilter {SELECT rowid, * FROM 't6' WHERE b glob ?} abc
  }
} {
  do_test 1.$tn {
    set echo_module {}
    execsql $sql
    set ::echo_module
  } [list {*}$expect]
}

finish_test