/ Check-in [34c8e952]
Login

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

Overview
Comment:Enhance the vtab interface to handle IS, !=, IS NOT, IS NULL and IS NOT NULL constraints.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vtab-extra-ops
Files: files | file ages | folders
SHA3-256:34c8e952616013deb6fffec701ac5989afac9bef1bf92458a2e4ba92c7ee924f
User & Date: dan 2017-09-09 19:41:12
Context
2017-09-11
08:53
Add tests to check that affinities work with != operators on virtual table column values. No changes to code. check-in: 8d24e080 user: dan tags: vtab-extra-ops
2017-09-09
19:41
Enhance the vtab interface to handle IS, !=, IS NOT, IS NULL and IS NOT NULL constraints. check-in: 34c8e952 user: dan tags: vtab-extra-ops
00:51
Fix harmless compiler warnings seen with MSVC. check-in: faa22e29 user: mistachkin tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

6252
6253
6254
6255
6256
6257
6258





6259
6260
6261
6262
6263
6264
6265
#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







>
>
>
>
>







6252
6253
6254
6255
6256
6257
6258
6259
6260
6261
6262
6263
6264
6265
6266
6267
6268
6269
6270
#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
#define SQLITE_INDEX_CONSTRAINT_NE        68
#define SQLITE_INDEX_CONSTRAINT_ISNOT     69
#define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70
#define SQLITE_INDEX_CONSTRAINT_ISNULL    71
#define SQLITE_INDEX_CONSTRAINT_IS        72

/*
** 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.

893
894
895
896
897
898
899

900
901
902
903
904
905
906
907
908
909
910

911
912
913
914
915
916
917
        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, zNewCol);
      } else {
        zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zNewCol, zOp);
      }
      string_concat(&zQuery, zNew, 1, &rc);

      zSep = "AND";
      pUsage->argvIndex = ++nArg;
      pUsage->omit = 1;

    }
  }

  /* If there is only one term in the ORDER BY clause, and it is
  ** on a column that this virtual table has an index for, then consume 
  ** the ORDER BY clause.
  */







>
|
|
|
|
|
|
|
<
|
|
|
>







893
894
895
896
897
898
899
900
901
902
903
904
905
906
907

908
909
910
911
912
913
914
915
916
917
918
        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 ){
        if( zOp[0]=='L' ){
          zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", 
              zSep, zNewCol);
        } else {
          zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zNewCol, zOp);
        }
        string_concat(&zQuery, zNew, 1, &rc);

        zSep = "AND";
        pUsage->argvIndex = ++nArg;
        pUsage->omit = 1;
      }
    }
  }

  /* If there is only one term in the ORDER BY clause, and it is
  ** on a column that this virtual table has an index for, then consume 
  ** the ORDER BY clause.
  */

Changes to src/test_bestindex.c.

410
411
412
413
414
415
416










417
418
419
420
421
422
423
        zOp = "match"; 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;










    }

    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("op", -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj(zOp, -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("column", -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pCons->iColumn));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("usable", -1));







>
>
>
>
>
>
>
>
>
>







410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
        zOp = "match"; 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;
      case SQLITE_INDEX_CONSTRAINT_NE:
        zOp = "ne"; break;
      case SQLITE_INDEX_CONSTRAINT_ISNOT:
        zOp = "isnot"; break;
      case SQLITE_INDEX_CONSTRAINT_ISNOTNULL:
        zOp = "isnotnull"; break;
      case SQLITE_INDEX_CONSTRAINT_ISNULL:
        zOp = "isnull"; break;
      case SQLITE_INDEX_CONSTRAINT_IS:
        zOp = "is"; break;
    }

    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("op", -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj(zOp, -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("column", -1));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewIntObj(pCons->iColumn));
    Tcl_ListObjAppendElement(0, pElem, Tcl_NewStringObj("usable", -1));

Changes to src/where.c.

864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
...
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935





936

937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954

955
956
957
958
959
960
961
    if( pTerm->leftCursor != pSrc->iCursor ) continue;
    if( pTerm->prereqRight & mUnusable ) continue;
    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
    testcase( pTerm->eOperator & WO_IN );
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_IS );
    testcase( pTerm->eOperator & WO_ALL );
    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    assert( pTerm->u.leftColumn>=(-1) );
    nTerm++;
  }

  /* If the ORDER BY clause contains only columns in the current 
  ** virtual table then allocate space for the aOrderBy part of
................................................................................
  *(int*)&pIdxInfo->nOrderBy = nOrderBy;
  *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons;
  *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
  *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
                                                                   pUsage;

  for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    u8 op;
    if( pTerm->leftCursor != pSrc->iCursor ) continue;
    if( pTerm->prereqRight & mUnusable ) continue;
    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
    testcase( pTerm->eOperator & WO_IN );
    testcase( pTerm->eOperator & WO_IS );
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_ALL );
    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 );
    assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
    assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
    assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH );
    assert( pTerm->eOperator & (WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) );

    if( op & (WO_LT|WO_LE|WO_GT|WO_GE)
     && sqlite3ExprIsVector(pTerm->pExpr->pRight) 
    ){
      if( i<16 ) mNoOmit |= (1 << i);
      if( op==WO_LT ) pIdxCons[j].op = WO_LE;
      if( op==WO_GT ) pIdxCons[j].op = WO_GE;

    }

    j++;
  }
  for(i=0; i<nOrderBy; i++){
    Expr *pExpr = pOrderBy->a[i].pExpr;
    pIdxOrderBy[i].iColumn = pExpr->iColumn;







|







 







|







|




|


|
>
>
>
>
>
|
>
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
>







864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
...
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
    if( pTerm->leftCursor != pSrc->iCursor ) continue;
    if( pTerm->prereqRight & mUnusable ) continue;
    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
    testcase( pTerm->eOperator & WO_IN );
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_IS );
    testcase( pTerm->eOperator & WO_ALL );
    if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    assert( pTerm->u.leftColumn>=(-1) );
    nTerm++;
  }

  /* If the ORDER BY clause contains only columns in the current 
  ** virtual table then allocate space for the aOrderBy part of
................................................................................
  *(int*)&pIdxInfo->nOrderBy = nOrderBy;
  *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons;
  *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
  *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
                                                                   pUsage;

  for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    u16 op;
    if( pTerm->leftCursor != pSrc->iCursor ) continue;
    if( pTerm->prereqRight & mUnusable ) continue;
    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
    testcase( pTerm->eOperator & WO_IN );
    testcase( pTerm->eOperator & WO_IS );
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_ALL );
    if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    assert( pTerm->u.leftColumn>=(-1) );
    pIdxCons[j].iColumn = pTerm->u.leftColumn;
    pIdxCons[j].iTermOffset = i;
    op = pTerm->eOperator & WO_ALL;
    if( op==WO_IN ) op = WO_EQ;
    if( op==WO_MATCH ){
      pIdxCons[j].op = pTerm->eMatchOp;
    }else if( op & (WO_ISNULL|WO_IS) ){
      if( op==WO_ISNULL ){
        pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL;
      }else{
        pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS;
      }
    }else{
      pIdxCons[j].op = (u8)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 );
      assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
      assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
      assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH );
      assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) );

      if( op & (WO_LT|WO_LE|WO_GT|WO_GE)
          && sqlite3ExprIsVector(pTerm->pExpr->pRight) 
        ){
        if( i<16 ) mNoOmit |= (1 << i);
        if( op==WO_LT ) pIdxCons[j].op = WO_LE;
        if( op==WO_GT ) pIdxCons[j].op = WO_GE;
      }
    }

    j++;
  }
  for(i=0; i<nOrderBy; i++){
    Expr *pExpr = pOrderBy->a[i].pExpr;
    pIdxOrderBy[i].iColumn = pExpr->iColumn;

Changes to src/wherecode.c.

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







|







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

Changes to src/whereexpr.c.

313
314
315
316
317
318
319











320
321
322

323
324
325
326


327

328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354


355
356


















357
358
359
360
361
362
363
....
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197




1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223


1224
1225
1226
1227
1228
1229
1230
#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Check to see if the given expression is of the form
**
**         column OP expr
**
** where OP is one of MATCH, GLOB, LIKE or REGEXP and "column" is a 











** column of a virtual table.
**
** 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 */


){

  static const struct Op2 {
    const char *zOp;
    unsigned char eOp2;
  } aOp[] = {
    { "match",  SQLITE_INDEX_CONSTRAINT_MATCH },
    { "glob",   SQLITE_INDEX_CONSTRAINT_GLOB },
    { "like",   SQLITE_INDEX_CONSTRAINT_LIKE },
    { "regexp", SQLITE_INDEX_CONSTRAINT_REGEXP }
  };
  ExprList *pList;
  Expr *pCol;                     /* Column reference */
  int i;

  if( pExpr->op!=TK_FUNCTION ){
    return 0;
  }
  pList = pExpr->x.pList;
  if( pList==0 || pList->nExpr!=2 ){
    return 0;
  }
  pCol = pList->a[1].pExpr;
  if( pCol->op!=TK_COLUMN || !IsVirtual(pCol->pTab) ){
    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
................................................................................
#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( pWC->op==TK_AND && 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;
    prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
    prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
    if( (prereqExpr & prereqColumn)==0 ){
      Expr *pNewExpr;
      pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
                              0, sqlite3ExprDup(db, pRight, 0));
      if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
        ExprSetProperty(pNewExpr, EP_FromJoin);
      }
      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 */

  /* If there is a vector == or IS term - e.g. "(a, b) == (?, ?)" - create
  ** new terms for each component comparison - "a = ?" and "b = ?".  The
  ** new terms completely replace the original vector comparison, which is







>
>
>
>
>
>
>
>
>
>
>
|

<
>



|
>
>

>
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
<

>
>
>
>
|
|

<
<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>







313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332

333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
....
1222
1223
1224
1225
1226
1227
1228
1229

1230
1231
1232
1233
1234
1235
1236
1237


1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Check to see if the given expression is of the form
**
**         column OP expr
**
** where OP is one of MATCH, GLOB, LIKE or REGEXP and "column" is a 
** column of a virtual table. If so, set *ppLeft to point to the
** expression for "column", *ppRight to "expr" and return 1.
**
** Also check if the expression is one of:
**
**         column != expr
**         column IS NOT expr
**         column IS NOT NULL
**
** where "column" is a column of a virtual table. If so, set *ppLeft
** to point to "column", *ppRight to "expr" and return 1. Or, if "expr"
** is also a column of a virtual table, return 2.
**

** If the expression matches none of the patterns above, return 0.
*/
static int isMatchOfColumn(
  Expr *pExpr,                    /* Test this expression */
  unsigned char *peOp2,           /* OUT: 0 for MATCH, or else an op2 value */
  Expr **ppLeft,                  /* Column expression to left of MATCH/op2 */
  Expr **ppRight                  /* Expression to left of MATCH/op2 */
){
  if( pExpr->op==TK_FUNCTION ){
    static const struct Op2 {
      const char *zOp;
      unsigned char eOp2;
    } aOp[] = {
      { "match",  SQLITE_INDEX_CONSTRAINT_MATCH },
      { "glob",   SQLITE_INDEX_CONSTRAINT_GLOB },
      { "like",   SQLITE_INDEX_CONSTRAINT_LIKE },
      { "regexp", SQLITE_INDEX_CONSTRAINT_REGEXP }
    };
    ExprList *pList;
    Expr *pCol;                     /* Column reference */
    int i;

    if( pExpr->op!=TK_FUNCTION ){
      return 0;
    }
    pList = pExpr->x.pList;
    if( pList==0 || pList->nExpr!=2 ){
      return 0;
    }
    pCol = pList->a[1].pExpr;
    if( pCol->op!=TK_COLUMN || !IsVirtual(pCol->pTab) ){
      return 0;
    }
    for(i=0; i<ArraySize(aOp); i++){
      if( sqlite3StrICmp(pExpr->u.zToken, aOp[i].zOp)==0 ){
        *peOp2 = aOp[i].eOp2;
        *ppRight = pList->a[0].pExpr;
        *ppLeft = pCol;
        return 1;
      }
    }
  }else if( pExpr->op==TK_NE || pExpr->op==TK_ISNOT || pExpr->op==TK_NOTNULL ){
    int res = 0;
    Expr *pLeft = pExpr->pLeft;
    Expr *pRight = pExpr->pRight;
    if( pLeft->op==TK_COLUMN && IsVirtual(pLeft->pTab) ){
      res++;
    }
    if( pRight && pRight->op==TK_COLUMN && IsVirtual(pRight->pTab) ){
      res++;
      SWAP(Expr*, pLeft, pRight);
    }
    *ppLeft = pLeft;
    *ppRight = pRight;
    if( pExpr->op==TK_NE ) *peOp2 = SQLITE_INDEX_CONSTRAINT_NE;
    if( pExpr->op==TK_ISNOT ) *peOp2 = SQLITE_INDEX_CONSTRAINT_ISNOT;
    if( pExpr->op==TK_NOTNULL ) *peOp2 = SQLITE_INDEX_CONSTRAINT_ISNOTNULL;
    return res;
  }
  return 0;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*
** If the pBase expression originated in the ON or USING clause of
................................................................................
#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( pWC->op==TK_AND ){

    Expr *pRight, *pLeft;
    int i;
    int res = isMatchOfColumn(pExpr, &eOp2, &pLeft, &pRight);
    for(i=0; i<res; i++){
      int idxNew;
      WhereTerm *pNewTerm;
      Bitmask prereqColumn, prereqExpr;



      prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight);
      prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft);
      if( (prereqExpr & prereqColumn)==0 ){
        Expr *pNewExpr;
        pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 
            0, sqlite3ExprDup(db, pRight, 0));
        if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){
          ExprSetProperty(pNewExpr, EP_FromJoin);
        }
        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;
      }
      SWAP(Expr*, pLeft, pRight);
    }
  }
#endif /* SQLITE_OMIT_VIRTUALTABLE */

  /* If there is a vector == or IS term - e.g. "(a, b) == (?, ?)" - create
  ** new terms for each component comparison - "a = ?" and "b = ?".  The
  ** new terms completely replace the original vector comparison, which is

Added test/bestindex5.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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
# 2017 September 10
#
# 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.
#
#***********************************************************************
# Test the virtual table interface. In particular the xBestIndex
# method.
#

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

ifcapable !vtab {
  finish_test
  return
}

#-------------------------------------------------------------------------
# Virtual table callback for a virtual table named $tbl.
#  
proc vtab_cmd {method args} {

  set binops(ne)    !=
  set binops(eq)    =
  set binops(isnot) "IS NOT"
  set binops(is)    "IS"

  set unops(isnotnull) "IS NOT NULL"
  set unops(isnull)    "IS NULL"

  set cols(0) a
  set cols(1) b
  set cols(2) c

  switch -- $method {
    xConnect {
      return "CREATE TABLE t1(a, b, c)"
    }

    xBestIndex {
      foreach {clist orderby mask} $args {}

      set cost 1000000.0
      set ret [list]
      set str [list]

      set v 0
      for {set i 0} {$i < [llength $clist]} {incr i} {
        array unset C
        array set C [lindex $clist $i]
        if {$C(usable)} {
          if {[info exists binops($C(op))]} {
            lappend ret omit $i
            lappend str "$cols($C(column)) $binops($C(op)) %$v%"
            incr v
            set cost [expr $cost / 2]
          }
          if {[info exists unops($C(op))]} {
            lappend ret omit $i
            lappend str "$cols($C(column)) $unops($C(op))"
            incr v
            set cost [expr $cost / 2]
          }
        }
      }

      lappend ret idxstr [join $str " AND "]
      lappend ret cost $cost
      return $ret
    }

    xFilter {
      set q [lindex $args 1]
      set a [lindex $args 2]
      for {set v 0} {$v < [llength $a]} {incr v} {
        set val [lindex $a $v]
        set q [string map [list %$v% '$val'] $q]
      }
      if {$q==""} { set q 1 }
      lappend ::xFilterQueries "WHERE $q"
      return [list sql "SELECT rowid, * FROM t1x WHERE $q"]
    }
  }
  return ""
}

proc vtab_simple {method args} {
  switch -- $method {
    xConnect {
      return "CREATE TABLE t2(x)"
    }
    xBestIndex {
      return [list cost 999999.0]
    }
    xFilter {
      return [list sql "SELECT rowid, * FROM t2x"]
    }
  }
  return ""
}

register_tcl_module db

proc do_vtab_query_test {tn query result} {
  set ::xFilterQueries [list]
  uplevel [list
    do_test $tn [string map [list %QUERY% $query] {
      set r [execsql {%QUERY%}]
      set r [concat $::xFilterQueries $r]
      set r
    }] [list {*}$result]
  ]
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd');
  CREATE TABLE t1x(a INTEGER, b TEXT, c REAL);
  INSERT INTO t1x VALUES(1, 2, 3);
  INSERT INTO t1x VALUES(4, 5, 6);
  INSERT INTO t1x VALUES(7, 8, 9);

  CREATE VIRTUAL TABLE t2 USING tcl('vtab_simple');
  CREATE TABLE t2x(x INTEGER);
  INSERT INTO t2x VALUES(1);
}

do_vtab_query_test 1.1 { SELECT * FROM t1 WHERE a!='hello'; } {
  "WHERE a != 'hello'"
  1 2 3.0 4 5 6.0 7 8 9.0
}

do_vtab_query_test 1.2.1 { SELECT * FROM t1 WHERE b!=8 } {
  "WHERE b != '8'"
  1 2 3.0 4 5 6.0
}
do_vtab_query_test 1.2.2 { SELECT * FROM t1 WHERE 8!=b } {
  "WHERE b != '8'"
  1 2 3.0 4 5 6.0
}

do_vtab_query_test 1.3 { SELECT * FROM t1 WHERE c IS NOT 3 } {
  "WHERE c IS NOT '3'"
  4 5 6.0 7 8 9.0
}
do_vtab_query_test 1.3.2 { SELECT * FROM t1 WHERE 3 IS NOT c } {
  "WHERE c IS NOT '3'"
  4 5 6.0 7 8 9.0
}

do_vtab_query_test 1.4.1 { SELECT * FROM t1, t2 WHERE x != a } {
  "WHERE a != '1'"
  4 5 6.0 1   7 8 9.0 1
}
do_vtab_query_test 1.4.2 { SELECT * FROM t1, t2 WHERE a != x } {
  "WHERE a != '1'"
  4 5 6.0 1   7 8 9.0 1
}

do_vtab_query_test 1.5.1 { SELECT * FROM t1 WHERE a IS NOT NULL } {
  "WHERE a IS NOT NULL"
  1 2 3.0 4 5 6.0 7 8 9.0
}
do_vtab_query_test 1.5.2 { SELECT * FROM t1 WHERE NULL IS NOT a } {
  "WHERE a IS NOT ''"
  1 2 3.0 4 5 6.0 7 8 9.0
}

do_vtab_query_test 1.6.1 { SELECT * FROM t1 WHERE a IS NULL } {
  "WHERE a IS NULL"
}

do_vtab_query_test 1.6.2 { SELECT * FROM t1 WHERE NULL IS a } {
  "WHERE a IS ''"
}

do_vtab_query_test 1.7.1 { SELECT * FROM t1 WHERE (a, b) IS (1, 2) } {
  "WHERE a IS '1' AND b IS '2'"
  1 2 3.0
}
do_vtab_query_test 1.7.2 { SELECT * FROM t1 WHERE (5, 4) IS (b, a) } {
  {WHERE b IS '5' AND a IS '4'} 
  4 5 6.0
}

finish_test