/ Check-in [116b2064]
Login

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

Overview
Comment:Add the "colUsed" field to the sqlite3_index_info structure passed to virtual table xBestIndex methods. To indicate the subset of the virtual table columns that may be required by the current scan.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vtab-colused
Files: files | file ages | folders
SHA1:116b206494eb8ba963c7c5acfbf9e7b6db11c79c
User & Date: dan 2015-11-26 19:33:41
Context
2015-11-30
12:01
Add the "colUsed" field to the sqlite3_index_info structure passed to virtual table xBestIndex methods. To indicate the subset of the virtual table columns that may be required by the current scan. check-in: 47f10b7e user: dan tags: trunk
2015-11-26
19:33
Add the "colUsed" field to the sqlite3_index_info structure passed to virtual table xBestIndex methods. To indicate the subset of the virtual table columns that may be required by the current scan. Closed-Leaf check-in: 116b2064 user: dan tags: vtab-colused
15:51
Fix a problem with the userauth extension and no-authentication databases. Run the tests for this extension as part of the Debug-One module in releasetest.tcl. check-in: 8b156219 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

5610
5611
5612
5613
5614
5615
5616











5617
5618
5619
5620
5621
5622
5623
....
5690
5691
5692
5693
5694
5695
5696


5697
5698
5699
5700
5701
5702
5703
** and makes other simplifications to the WHERE clause in an attempt to
** get as many WHERE clause terms into the form shown above as possible.
** ^The aConstraint[] array only reports WHERE clause terms that are
** relevant to the particular virtual table being queried.
**
** ^Information about the ORDER BY clause is stored in aOrderBy[].
** ^Each term of aOrderBy records a column of the ORDER BY clause.











**
** The [xBestIndex] method must fill aConstraintUsage[] with information
** about what parameters to pass to xFilter.  ^If argvIndex>0 then
** the right-hand side of the corresponding aConstraint[] is evaluated
** and becomes the argvIndex-th entry in argv.  ^(If aConstraintUsage[].omit
** is true, then the constraint is assumed to be fully handled by the
** virtual table and is not checked again by SQLite.)^
................................................................................
  int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
  int orderByConsumed;       /* True if output is already ordered */
  double estimatedCost;           /* Estimated cost of using this index */
  /* Fields below are only available in SQLite 3.8.2 and later */
  sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
  /* Fields below are only available in SQLite 3.9.0 and later */
  int idxFlags;              /* Mask of SQLITE_INDEX_SCAN_* flags */


};

/*
** CAPI3REF: Virtual Table Scan Flags
*/
#define SQLITE_INDEX_SCAN_UNIQUE      1     /* Scan visits at most 1 row */








>
>
>
>
>
>
>
>
>
>
>







 







>
>







5610
5611
5612
5613
5614
5615
5616
5617
5618
5619
5620
5621
5622
5623
5624
5625
5626
5627
5628
5629
5630
5631
5632
5633
5634
....
5701
5702
5703
5704
5705
5706
5707
5708
5709
5710
5711
5712
5713
5714
5715
5716
** and makes other simplifications to the WHERE clause in an attempt to
** get as many WHERE clause terms into the form shown above as possible.
** ^The aConstraint[] array only reports WHERE clause terms that are
** relevant to the particular virtual table being queried.
**
** ^Information about the ORDER BY clause is stored in aOrderBy[].
** ^Each term of aOrderBy records a column of the ORDER BY clause.
**
** The colUsed field indicates which columns of the virtual table may be
** required by the current scan. Virtual table columns are numbered from
** zero in the order in which they appear within the CREATE TABLE statement
** passed to sqlite3_declare_vtab(). For the first 63 columns (columns 0-62),
** the corresponding bit is set within the colUsed mask if the column may be
** required by SQLite. If the table has at least 64 columns and any column
** to the right of the first 63 is required, then bit 63 of colUsed is also
** set. In other words, column iCol may be required if the expression
** (colUsed & ((sqlite3_uint64)1 << (iCol>=63 ? 63 : iCol))) evaluates to 
** non-zero.
**
** The [xBestIndex] method must fill aConstraintUsage[] with information
** about what parameters to pass to xFilter.  ^If argvIndex>0 then
** the right-hand side of the corresponding aConstraint[] is evaluated
** and becomes the argvIndex-th entry in argv.  ^(If aConstraintUsage[].omit
** is true, then the constraint is assumed to be fully handled by the
** virtual table and is not checked again by SQLite.)^
................................................................................
  int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
  int orderByConsumed;       /* True if output is already ordered */
  double estimatedCost;           /* Estimated cost of using this index */
  /* Fields below are only available in SQLite 3.8.2 and later */
  sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
  /* Fields below are only available in SQLite 3.9.0 and later */
  int idxFlags;              /* Mask of SQLITE_INDEX_SCAN_* flags */
  /* Fields below are only available in SQLite 3.10.0 and later */
  sqlite3_uint64 colUsed;    /* Input: Mask of columns used by statement */
};

/*
** CAPI3REF: Virtual Table Scan Flags
*/
#define SQLITE_INDEX_SCAN_UNIQUE      1     /* Scan visits at most 1 row */

Changes to src/test8.c.

740
741
742
743
744
745
746




























747
748
749
750
751
752
753
...
766
767
768
769
770
771
772

773
774
775
776
777
778
779
...
813
814
815
816
817
818
819


820
821
822
823
824
825
826
827
828
829
830
    }
  }
  *pzStr = zIn;
  if( doFree ){
    sqlite3_free(zAppend);
  }
}





























/*
** The echo module implements the subset of query constraints and sort
** orders that may take advantage of SQLite indices on the underlying
** real table. For example, if the real table is declared as:
**
**     CREATE TABLE real(a, b, c);
................................................................................
**
** where the <where-clause> and <order-by-clause> are determined
** by the contents of the structure pointed to by the pIdxInfo argument.
*/
static int echoBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  int ii;
  char *zQuery = 0;

  char *zNew;
  int nArg = 0;
  const char *zSep = "WHERE";
  echo_vtab *pVtab = (echo_vtab *)tab;
  sqlite3_stmt *pStmt = 0;
  Tcl_Interp *interp = pVtab->interp;

................................................................................
    nRow = sqlite3_column_int(pStmt, 0);
    rc = sqlite3_finalize(pStmt);
    if( rc!=SQLITE_OK ){
      return rc;
    }
  }



  zQuery = sqlite3_mprintf("SELECT rowid, * FROM %Q", pVtab->zTableName);
  if( !zQuery ){
    return SQLITE_NOMEM;
  }
  for(ii=0; ii<pIdxInfo->nConstraint; ii++){
    const struct sqlite3_index_constraint *pConstraint;
    struct sqlite3_index_constraint_usage *pUsage;
    int iCol;

    pConstraint = &pIdxInfo->aConstraint[ii];
    pUsage = &pIdxInfo->aConstraintUsage[ii];







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







 







>
>
|
<
|
|







740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
...
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
...
842
843
844
845
846
847
848
849
850
851

852
853
854
855
856
857
858
859
860
    }
  }
  *pzStr = zIn;
  if( doFree ){
    sqlite3_free(zAppend);
  }
}

/*
** This function returns a pointer to an sqlite3_malloc()ed buffer 
** containing the select-list (the thing between keywords SELECT and FROM)
** to query the underlying real table with for the scan described by
** argument pIdxInfo.
**
** If the current SQLite version is earlier than 3.10.0, this is just "*"
** (select all columns). Or, for version 3.10.0 and greater, the list of
** columns identified by the pIdxInfo->colUsed mask.
*/
static char *echoSelectList(echo_vtab *pTab, sqlite3_index_info *pIdxInfo){
  char *zRet = 0;
  if( sqlite3_libversion_number()<3010000 ){
    zRet = sqlite3_mprintf(", *");
  }else{
    int i;
    for(i=0; i<pTab->nCol; i++){
      if( pIdxInfo->colUsed & ((sqlite3_uint64)1 << (i>=63 ? 63 : i)) ){
        zRet = sqlite3_mprintf("%z, %s", zRet, pTab->aCol[i]);
      }else{
        zRet = sqlite3_mprintf("%z, NULL", zRet);
      }
      if( !zRet ) break;
    }
  }
  return zRet;
}

/*
** The echo module implements the subset of query constraints and sort
** orders that may take advantage of SQLite indices on the underlying
** real table. For example, if the real table is declared as:
**
**     CREATE TABLE real(a, b, c);
................................................................................
**
** where the <where-clause> and <order-by-clause> are determined
** by the contents of the structure pointed to by the pIdxInfo argument.
*/
static int echoBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  int ii;
  char *zQuery = 0;
  char *zCol = 0;
  char *zNew;
  int nArg = 0;
  const char *zSep = "WHERE";
  echo_vtab *pVtab = (echo_vtab *)tab;
  sqlite3_stmt *pStmt = 0;
  Tcl_Interp *interp = pVtab->interp;

................................................................................
    nRow = sqlite3_column_int(pStmt, 0);
    rc = sqlite3_finalize(pStmt);
    if( rc!=SQLITE_OK ){
      return rc;
    }
  }

  zCol = echoSelectList(pVtab, pIdxInfo);
  if( !zCol ) return SQLITE_NOMEM;
  zQuery = sqlite3_mprintf("SELECT rowid%z FROM %Q", zCol, pVtab->zTableName);

  if( !zQuery ) return SQLITE_NOMEM;

  for(ii=0; ii<pIdxInfo->nConstraint; ii++){
    const struct sqlite3_index_constraint *pConstraint;
    struct sqlite3_index_constraint_usage *pUsage;
    int iCol;

    pConstraint = &pIdxInfo->aConstraint[ii];
    pUsage = &pIdxInfo->aConstraintUsage[ii];

Changes to src/update.c.

259
260
261
262
263
264
265
266
267


268
269
270
271
272
273
274
275
276
#endif
  }
  assert( (chngRowid & chngPk)==0 );
  assert( chngRowid==0 || chngRowid==1 );
  assert( chngPk==0 || chngPk==1 );
  chngKey = chngRowid + chngPk;

  /* The SET expressions are not actually used inside the WHERE loop.
  ** So reset the colUsed mask


  */
  pTabList->a[0].colUsed = 0;

  hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey);

  /* There is one entry in the aRegIdx[] array for each index on the table
  ** being updated.  Fill in aRegIdx[] with a register number that will hold
  ** the key for accessing each index.
  **







|
|
>
>

|







259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
#endif
  }
  assert( (chngRowid & chngPk)==0 );
  assert( chngRowid==0 || chngRowid==1 );
  assert( chngPk==0 || chngPk==1 );
  chngKey = chngRowid + chngPk;

  /* The SET expressions are not actually used inside the WHERE loop.  
  ** So reset the colUsed mask. Unless this is a virtual table. In that
  ** case, set all bits of the colUsed mask (to ensure that the virtual
  ** table implementation makes all columns available).
  */
  pTabList->a[0].colUsed = IsVirtual(pTab) ? (Bitmask)-1 : 0;

  hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey);

  /* There is one entry in the aRegIdx[] array for each index on the table
  ** being updated.  Fill in aRegIdx[] with a register number that will hold
  ** the key for accessing each index.
  **

Changes to src/where.c.

2860
2861
2862
2863
2864
2865
2866

2867
2868
2869
2870
2871
2872
2873
    pIdxInfo->idxStr = 0;
    pIdxInfo->idxNum = 0;
    pIdxInfo->needToFreeIdxStr = 0;
    pIdxInfo->orderByConsumed = 0;
    pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
    pIdxInfo->estimatedRows = 25;
    pIdxInfo->idxFlags = 0;

    rc = vtabBestIndex(pParse, pTab, pIdxInfo);
    if( rc ) goto whereLoopAddVtab_exit;
    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
    pNew->prereq = mExtra;
    mxTerm = -1;
    assert( pNew->nLSlot>=nConstraint );
    for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;







>







2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
    pIdxInfo->idxStr = 0;
    pIdxInfo->idxNum = 0;
    pIdxInfo->needToFreeIdxStr = 0;
    pIdxInfo->orderByConsumed = 0;
    pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
    pIdxInfo->estimatedRows = 25;
    pIdxInfo->idxFlags = 0;
    pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed;
    rc = vtabBestIndex(pParse, pTab, pIdxInfo);
    if( rc ) goto whereLoopAddVtab_exit;
    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
    pNew->prereq = mExtra;
    mxTerm = -1;
    assert( pNew->nLSlot>=nConstraint );
    for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;

Changes to test/tkt3871.test.

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
  execsql { SELECT * FROM e WHERE a = 1 OR a = 2 }
} {1 1 2 4}
do_test tkt3871-1.3 {
  set echo_module ""
  execsql { SELECT * FROM e WHERE a = 1 OR a = 2 }
  set echo_module
} [list \
  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 1 \
  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 2 \
]

do_test tkt3871-1.4 {
  execsql { SELECT * FROM e WHERE a = 1 OR a = 2 OR b = 9 }
} {1 1 2 4 3 9}
do_test tkt3871-1.5 {
  set echo_module ""
  execsql { SELECT * FROM e WHERE a = 1 OR a = 2 OR b = 9 }
  set echo_module
} [list \
  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 1 \
  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 2 \
  xFilter {SELECT rowid, * FROM 't1' WHERE b = ?} 9
]


finish_test







|
|










|
|
|




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
  execsql { SELECT * FROM e WHERE a = 1 OR a = 2 }
} {1 1 2 4}
do_test tkt3871-1.3 {
  set echo_module ""
  execsql { SELECT * FROM e WHERE a = 1 OR a = 2 }
  set echo_module
} [list \
  xFilter {SELECT rowid, a, b FROM 't1' WHERE a = ?} 1 \
  xFilter {SELECT rowid, a, b FROM 't1' WHERE a = ?} 2 \
]

do_test tkt3871-1.4 {
  execsql { SELECT * FROM e WHERE a = 1 OR a = 2 OR b = 9 }
} {1 1 2 4 3 9}
do_test tkt3871-1.5 {
  set echo_module ""
  execsql { SELECT * FROM e WHERE a = 1 OR a = 2 OR b = 9 }
  set echo_module
} [list \
  xFilter {SELECT rowid, a, b FROM 't1' WHERE a = ?} 1 \
  xFilter {SELECT rowid, a, b FROM 't1' WHERE a = ?} 2 \
  xFilter {SELECT rowid, a, b FROM 't1' WHERE b = ?} 9
]


finish_test

Changes to test/vtab1.test.

389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434

435
436
437
438
439
440
441
442
443
444

445
446
447
448
449
450
451
...
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
...
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
...
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
...
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
...
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
....
1149
1150
1151
1152
1153
1154
1155
1156

1157
1158
1159
1160
1161
1162

1163
1164
1165
1166
1167
1168
1169
....
1296
1297
1298
1299
1300
1301
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 2 3 4 5 6}
do_test vtab1-3.7 {
  execsql {
    SELECT rowid, * FROM t1;
  }
} {1 1 2 3 2 4 5 6}
do_test vtab1-3.8 {
  execsql {
    SELECT a AS d, b AS e, c AS f FROM t1;
  }
} {1 2 3 4 5 6}

# Execute some SELECT statements with WHERE clauses on the t1 table.
# Then check the echo_module variable (written to by the module methods
# in test8.c) to make sure the xBestIndex() and xFilter() methods were
# called correctly.
#
do_test vtab1-3.8 {
  set echo_module ""
  execsql {
    SELECT * FROM t1;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} ]
do_test vtab1-3.9 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b = 5;
  }
} {4 5 6}
do_test vtab1-3.10 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ?}   \
        xFilter    {SELECT rowid, * FROM 'treal' WHERE b = ?} 5 ]
do_test vtab1-3.10 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b >= 5 AND b <= 10;
  }
} {4 5 6}
do_test vtab1-3.11 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 5 10 ]

do_test vtab1-3.12 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b BETWEEN 2 AND 10;
  }
} {1 2 3 4 5 6}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ]


# Add a function for the MATCH operator. Everything always matches!
#proc test_match {lhs rhs} {
#  lappend ::echo_module MATCH $lhs $rhs
#  return 1
#}
#db function match test_match
................................................................................
  set echo_module ""
  catchsql {
    SELECT * FROM t1 WHERE a MATCH 'string';
  }
} {1 {unable to use function MATCH in the requested context}}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'}]
ifcapable subquery {
# The echo module uses a subquery internally to implement the MATCH operator.
do_test vtab1-3.14 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b MATCH 'string';
  }
} {}
do_test vtab1-3.15 {
  set echo_module
} [list xBestIndex \
        {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')}  \
        xFilter \
        {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
        string ]
}; #ifcapable subquery

#----------------------------------------------------------------------
# Test case vtab1-3 test table scans and the echo module's 
# xBestIndex/xFilter handling of ORDER BY clauses.

................................................................................
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b;
  }
} {2 5 nosort}
do_test vtab1-4.2 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \
        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ]
do_test vtab1-4.3 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b DESC;
  }
} {5 2 nosort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \
        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ]
do_test vtab1-4.3 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b||'';
  }
} {2 5 sort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} ]

execsql {
  DROP TABLE t1;
  DROP TABLE treal;
}

#----------------------------------------------------------------------
................................................................................
  1 red green 2 hearts diamonds  \
  2 blue black 1 spades clubs    \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-3 {
  filter $echo_module
} [list \
  xFilter {SELECT rowid, * FROM 't1'} \
  xFilter {SELECT rowid, * FROM 't2'} \
  xFilter {SELECT rowid, * FROM 't2'} \
]
do_test vtab1-5-4 {
  set echo_module ""
  execsql {
    SELECT * FROM et1, et2 WHERE et2.d = 2;
  }
} [list \
  1 red green 2 hearts diamonds  \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-5 {
  filter $echo_module
} [list \
  xFilter {SELECT rowid, * FROM 't1'} \
  xFilter {SELECT rowid, * FROM 't2'} \
  xFilter {SELECT rowid, * FROM 't2'} \
]
do_test vtab1-5-6 {
  execsql {
    CREATE INDEX i1 ON t2(d);
  }

  db close
................................................................................
} [list \
  1 red green 2 hearts diamonds  \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-7 {
  filter $::echo_module
} [list \
  xFilter {SELECT rowid, * FROM 't1'}             \
  xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
  xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
]

execsql {
  DROP TABLE t1;
  DROP TABLE t2;
  DROP TABLE et1;
  DROP TABLE et2;
................................................................................
do_test vtab1.10-5 {
  set echo_module ""
  execsql {
    SELECT * FROM e WHERE rowid||'' MATCH 'pattern';
  }
  set echo_module
} [list \
  xBestIndex {SELECT rowid, * FROM 'r'} \
  xFilter {SELECT rowid, * FROM 'r'}    \
]
proc match_func {args} {return ""}
do_test vtab1.10-6 {
  set echo_module ""
  db function match match_func
  execsql {
    SELECT * FROM e WHERE match('pattern', rowid, 'pattern2');
  }
  set echo_module
} [list \
  xBestIndex {SELECT rowid, * FROM 'r'} \
  xFilter {SELECT rowid, * FROM 'r'}    \
]


# Testing the xFindFunction interface
#
catch {rename ::echo_glob_overload {}}
do_test vtab1.11-1 {
................................................................................
#  set echo_module
#} {/.*xBestIndex {SELECT rowid, . FROM 'c' WHERE rowid = .} xFilter {SELECT rowid, . FROM 'c'} 1/}

do_test vtab1-14.2 {
  set echo_module ""
  execsql { SELECT * FROM echo_c WHERE rowid = 1 }
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE rowid = ?} xFilter {SELECT rowid, * FROM 'c' WHERE rowid = ?} 1]


do_test vtab1-14.3 {
  set echo_module ""
  execsql { SELECT * FROM echo_c WHERE a = 1 }
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1]


#do_test vtab1-14.4 {
#  set echo_module ""
#  execsql { SELECT * FROM echo_c WHERE a IN (1, 2) }
#  set echo_module
#} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE a = .} xFilter {SELECT rowid, . FROM 'c' WHERE a = .} 1/}

................................................................................
  INSERT INTO t6 VALUES(5, 'Phillip');
  INSERT INTO t6 VALUES(6, 'Bartholomew');
  CREATE VIRTUAL TABLE e6 USING echo(t6);
}

foreach {tn sql res filter} {
  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 }








|










|





|
|








|
|








|
|
>








|
|
>







 







|
|











|

|







 







|
|








|
|








|
|







 







|
|
|













|
|
|







 







|
|
|







 







|
|










|
|







 







|
>





|
>







 







|


|


|


|









|


|







389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
...
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
...
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
...
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
...
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
...
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
....
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
....
1300
1301
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
1333
1334
1335
1336
  }
} {1 2 3 4 5 6}
do_test vtab1-3.7 {
  execsql {
    SELECT rowid, * FROM t1;
  }
} {1 1 2 3 2 4 5 6}
do_test vtab1-3.8.1 {
  execsql {
    SELECT a AS d, b AS e, c AS f FROM t1;
  }
} {1 2 3 4 5 6}

# Execute some SELECT statements with WHERE clauses on the t1 table.
# Then check the echo_module variable (written to by the module methods
# in test8.c) to make sure the xBestIndex() and xFilter() methods were
# called correctly.
#
do_test vtab1-3.8.2 {
  set echo_module ""
  execsql {
    SELECT * FROM t1;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
        xFilter    {SELECT rowid, a, b, c FROM 'treal'} ]
do_test vtab1-3.9 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b = 5;
  }
} {4 5 6}
do_test vtab1-3.10 {
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b = ?}   \
        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b = ?} 5 ]
do_test vtab1-3.10 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b >= 5 AND b <= 10;
  }
} {4 5 6}
do_test vtab1-3.11 {
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
        5 10 ]
do_test vtab1-3.12 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b BETWEEN 2 AND 10;
  }
} {1 2 3 4 5 6}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
        2 10 ]

# Add a function for the MATCH operator. Everything always matches!
#proc test_match {lhs rhs} {
#  lappend ::echo_module MATCH $lhs $rhs
#  return 1
#}
#db function match test_match
................................................................................
  set echo_module ""
  catchsql {
    SELECT * FROM t1 WHERE a MATCH 'string';
  }
} {1 {unable to use function MATCH in the requested context}}
do_test vtab1-3.13 {
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
        xFilter    {SELECT rowid, a, b, c FROM 'treal'}]
ifcapable subquery {
# The echo module uses a subquery internally to implement the MATCH operator.
do_test vtab1-3.14 {
  set echo_module ""
  execsql {
    SELECT * FROM t1 WHERE b MATCH 'string';
  }
} {}
do_test vtab1-3.15 {
  set echo_module
} [list xBestIndex \
        {SELECT rowid, a, b, c FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
        xFilter \
        {SELECT rowid, a, b, c FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
        string ]
}; #ifcapable subquery

#----------------------------------------------------------------------
# Test case vtab1-3 test table scans and the echo module's 
# xBestIndex/xFilter handling of ORDER BY clauses.

................................................................................
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b;
  }
} {2 5 nosort}
do_test vtab1-4.2 {
  set echo_module
} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b ASC} \
        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b ASC} ]
do_test vtab1-4.3 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b DESC;
  }
} {5 2 nosort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b DESC} \
        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b DESC} ]
do_test vtab1-4.3 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b||'';
  }
} {2 5 sort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal'} \
        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal'} ]

execsql {
  DROP TABLE t1;
  DROP TABLE treal;
}

#----------------------------------------------------------------------
................................................................................
  1 red green 2 hearts diamonds  \
  2 blue black 1 spades clubs    \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-3 {
  filter $echo_module
} [list \
  xFilter {SELECT rowid, a, b, c FROM 't1'} \
  xFilter {SELECT rowid, d, e, f FROM 't2'} \
  xFilter {SELECT rowid, d, e, f FROM 't2'} \
]
do_test vtab1-5-4 {
  set echo_module ""
  execsql {
    SELECT * FROM et1, et2 WHERE et2.d = 2;
  }
} [list \
  1 red green 2 hearts diamonds  \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-5 {
  filter $echo_module
} [list \
  xFilter {SELECT rowid, a, b, c FROM 't1'} \
  xFilter {SELECT rowid, d, e, f FROM 't2'} \
  xFilter {SELECT rowid, d, e, f FROM 't2'} \
]
do_test vtab1-5-6 {
  execsql {
    CREATE INDEX i1 ON t2(d);
  }

  db close
................................................................................
} [list \
  1 red green 2 hearts diamonds  \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-7 {
  filter $::echo_module
} [list \
  xFilter {SELECT rowid, a, b, c FROM 't1'}             \
  xFilter {SELECT rowid, d, e, f FROM 't2' WHERE d = ?} \
  xFilter {SELECT rowid, d, e, f FROM 't2' WHERE d = ?} \
]

execsql {
  DROP TABLE t1;
  DROP TABLE t2;
  DROP TABLE et1;
  DROP TABLE et2;
................................................................................
do_test vtab1.10-5 {
  set echo_module ""
  execsql {
    SELECT * FROM e WHERE rowid||'' MATCH 'pattern';
  }
  set echo_module
} [list \
  xBestIndex {SELECT rowid, a, b, c FROM 'r'} \
  xFilter {SELECT rowid, a, b, c FROM 'r'}    \
]
proc match_func {args} {return ""}
do_test vtab1.10-6 {
  set echo_module ""
  db function match match_func
  execsql {
    SELECT * FROM e WHERE match('pattern', rowid, 'pattern2');
  }
  set echo_module
} [list \
  xBestIndex {SELECT rowid, a, b, c FROM 'r'} \
  xFilter {SELECT rowid, a, b, c FROM 'r'}    \
]


# Testing the xFindFunction interface
#
catch {rename ::echo_glob_overload {}}
do_test vtab1.11-1 {
................................................................................
#  set echo_module
#} {/.*xBestIndex {SELECT rowid, . FROM 'c' WHERE rowid = .} xFilter {SELECT rowid, . FROM 'c'} 1/}

do_test vtab1-14.2 {
  set echo_module ""
  execsql { SELECT * FROM echo_c WHERE rowid = 1 }
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'c' WHERE rowid = ?} \
        xFilter {SELECT rowid, a, b, c FROM 'c' WHERE rowid = ?} 1]

do_test vtab1-14.3 {
  set echo_module ""
  execsql { SELECT * FROM echo_c WHERE a = 1 }
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'c' WHERE a = ?} \
        xFilter {SELECT rowid, a, b, c FROM 'c' WHERE a = ?} 1]

#do_test vtab1-14.4 {
#  set echo_module ""
#  execsql { SELECT * FROM echo_c WHERE a IN (1, 2) }
#  set echo_module
#} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE a = .} xFilter {SELECT rowid, . FROM 'c' WHERE a = .} 1/}

................................................................................
  INSERT INTO t6 VALUES(5, 'Phillip');
  INSERT INTO t6 VALUES(6, 'Bartholomew');
  CREATE VIRTUAL TABLE e6 USING echo(t6);
}

foreach {tn sql res filter} {
  1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5}
    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b > ?} James}

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

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

  1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
    {xFilter {SELECT rowid, a, b 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, a, b FROM 't6' WHERE b like ?} J%}

  2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
    {xFilter {SELECT rowid, a, b 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 }

Changes to test/vtab4.test.

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
...
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
...
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
} {xBegin echo(treal) xSync echo(treal) xCommit echo(treal)}
do_test vtab4-1.3 {
  set echo_module [list]
  execsql {
    UPDATE techo SET a = 2;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, * FROM 'treal'} \
        xSync      echo(treal)                    \
        xCommit    echo(treal)                    \
]
do_test vtab4-1.4 {
  set echo_module [list]
  execsql {
    DELETE FROM techo;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, * FROM 'treal'} \
        xSync      echo(treal)                    \
        xCommit    echo(treal)                    \
]

# Ensure xBegin is not called more than once in a single transaction.
#
do_test vtab4-2.1 {
................................................................................
  execsql {
    BEGIN;
    INSERT INTO secho SELECT * FROM techo;
    DELETE FROM techo;
    COMMIT;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xBegin     echo(sreal)                    \
        xFilter    {SELECT rowid, * FROM 'treal'} \
        xBestIndex {SELECT rowid, * FROM 'treal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, * FROM 'treal'} \
        xSync   echo(sreal)                       \
        xSync   echo(treal)                       \
        xCommit echo(sreal)                       \
        xCommit echo(treal)                       \
]
do_test vtab4-2.3 {
  execsql {
................................................................................
  execsql {
    BEGIN;
    INSERT INTO techo SELECT * FROM secho;
    DELETE FROM secho;
    ROLLBACK;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'sreal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, * FROM 'sreal'} \
        xBestIndex {SELECT rowid, * FROM 'sreal'} \
        xBegin     echo(sreal)                    \
        xFilter    {SELECT rowid, * FROM 'sreal'} \
        xRollback  echo(treal)                    \
        xRollback  echo(sreal)                    \
]
do_test vtab4-2.6 {
  execsql {
    SELECT * FROM secho;
  }
................................................................................
  catchsql {
    BEGIN;
    INSERT INTO techo SELECT * FROM secho;
    DELETE FROM secho;
    COMMIT;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'sreal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, * FROM 'sreal'} \
        xBestIndex {SELECT rowid, * FROM 'sreal'} \
        xBegin     echo(sreal)                    \
        xFilter    {SELECT rowid, * FROM 'sreal'} \
        xSync      echo(treal)                    \
        xSync      echo(sreal)                    \
        xRollback  echo(treal)                    \
        xRollback  echo(sreal)                    \
]

finish_test







|

|









|

|







 







|

|
|

|







 







|

|
|

|







 







|

|
|

|







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
...
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
...
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
} {xBegin echo(treal) xSync echo(treal) xCommit echo(treal)}
do_test vtab4-1.3 {
  set echo_module [list]
  execsql {
    UPDATE techo SET a = 2;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, a, b, c FROM 'treal'} \
        xSync      echo(treal)                    \
        xCommit    echo(treal)                    \
]
do_test vtab4-1.4 {
  set echo_module [list]
  execsql {
    DELETE FROM techo;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, NULL, NULL, NULL FROM 'treal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, NULL, NULL, NULL FROM 'treal'} \
        xSync      echo(treal)                    \
        xCommit    echo(treal)                    \
]

# Ensure xBegin is not called more than once in a single transaction.
#
do_test vtab4-2.1 {
................................................................................
  execsql {
    BEGIN;
    INSERT INTO secho SELECT * FROM techo;
    DELETE FROM techo;
    COMMIT;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
        xBegin     echo(sreal)                    \
        xFilter    {SELECT rowid, a, b, c FROM 'treal'} \
        xBestIndex {SELECT rowid, NULL, NULL, NULL FROM 'treal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, NULL, NULL, NULL FROM 'treal'} \
        xSync   echo(sreal)                       \
        xSync   echo(treal)                       \
        xCommit echo(sreal)                       \
        xCommit echo(treal)                       \
]
do_test vtab4-2.3 {
  execsql {
................................................................................
  execsql {
    BEGIN;
    INSERT INTO techo SELECT * FROM secho;
    DELETE FROM secho;
    ROLLBACK;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'sreal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, a, b, c FROM 'sreal'} \
        xBestIndex {SELECT rowid, NULL, NULL, NULL FROM 'sreal'} \
        xBegin     echo(sreal)                    \
        xFilter    {SELECT rowid, NULL, NULL, NULL FROM 'sreal'} \
        xRollback  echo(treal)                    \
        xRollback  echo(sreal)                    \
]
do_test vtab4-2.6 {
  execsql {
    SELECT * FROM secho;
  }
................................................................................
  catchsql {
    BEGIN;
    INSERT INTO techo SELECT * FROM secho;
    DELETE FROM secho;
    COMMIT;
  }
  set echo_module
} [list xBestIndex {SELECT rowid, a, b, c FROM 'sreal'} \
        xBegin     echo(treal)                    \
        xFilter    {SELECT rowid, a, b, c FROM 'sreal'} \
        xBestIndex {SELECT rowid, NULL, NULL, NULL FROM 'sreal'} \
        xBegin     echo(sreal)                    \
        xFilter    {SELECT rowid, NULL, NULL, NULL FROM 'sreal'} \
        xSync      echo(treal)                    \
        xSync      echo(sreal)                    \
        xRollback  echo(treal)                    \
        xRollback  echo(sreal)                    \
]

finish_test

Changes to test/vtabH.test.

28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
  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]







|
|



|
|







28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
  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, a, b FROM 't6' WHERE b like ?} 
    xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} abc
  }

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

Added test/vtabI.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
# 2015 Nov 26
#
# 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. Specifically,
# it tests the sqlite3_index_info.colUsed variable is set correctly.
#

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

ifcapable !vtab {
  finish_test
  return
}

register_echo_module db

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c, d, e);
  CREATE VIRTUAL TABLE e1 USING echo(t1);
}

foreach {tn query filter} {
  1 {SELECT * FROM e1} 
    {SELECT rowid, a, b, c, d, e FROM 't1'}

  2 {SELECT a, b FROM e1} 
    {SELECT rowid, a, b, NULL, NULL, NULL FROM 't1'}

  3 {SELECT count(*) FROM e1 GROUP BY b} 
    {SELECT rowid, NULL, b, NULL, NULL, NULL FROM 't1'}

  4 {SELECT count(*) FROM e1 GROUP BY b HAVING a=?} 
    {SELECT rowid, a, b, NULL, NULL, NULL FROM 't1'}

  5 {SELECT a FROM e1 WHERE c=?}
    {SELECT rowid, a, NULL, c, NULL, NULL FROM 't1'}

  6 {SELECT a FROM e1 ORDER BY e}
    {SELECT rowid, a, NULL, NULL, NULL, e FROM 't1'}

  7 {SELECT a FROM e1 ORDER BY e, d}
    {SELECT rowid, a, NULL, NULL, d, e FROM 't1'}
} {
  do_test 1.$tn {
    set ::echo_module [list]
    execsql $query
    set idx [lsearch -exact $::echo_module xFilter]
    lindex $::echo_module [expr $idx+1]
  } $filter
}

#-------------------------------------------------------------------------
# Tests with a table with more than 64 columns.
#
proc all_col_list {} {
  set L [list]
  for {set i 1} {$i <= 100} {incr i} { lappend L "c$i" }
  set L
}

proc part_col_list {cols} {
  set L [list]
  for {set i 1} {$i <= 100} {incr i} { 
    set c "c$i"
    if {[lsearch $cols $c]>=0} {
      lappend L "c$i" 
    } else {
      lappend L NULL
    }
  }
  set L
}
proc CL {args} {
  join [part_col_list $args] ", "
}
proc CLT {args} {
  set cols $args
  for {set i 64} {$i <= 100} {incr i} {
    lappend cols "c$i"
  }
  join [part_col_list $cols] ", "
}

do_test 2.0 {
  execsql "CREATE TABLE t2([join [all_col_list] ,])"
  execsql "CREATE VIRTUAL TABLE e2 USING echo(t2)"
} {}

foreach {tn query filter} {
  1 {SELECT c1, c10, c20 FROM e2} 
    {SELECT rowid, [CL c1 c10 c20] FROM 't2'}

  2 {SELECT c40, c50, c60 FROM e2} 
    {SELECT rowid, [CL c40 c50 c60] FROM 't2'}

  3 {SELECT c7, c80, c90 FROM e2} 
    {SELECT rowid, [CLT c7] FROM 't2'}

  4 {SELECT c64 FROM e2} 
    {SELECT rowid, [CLT c64] FROM 't2'}

  5 {SELECT c63 FROM e2} 
    {SELECT rowid, [CL c63] FROM 't2'}

  6 {SELECT c22 FROM e2 ORDER BY c50, c70} 
    {SELECT rowid, [CLT c22 c50] FROM 't2'}

} {
  do_test 2.$tn {
    set ::echo_module [list]
    execsql $query
    set idx [lsearch -exact $::echo_module xFilter]
    lindex $::echo_module [expr $idx+1]
  } [subst $filter]
}

finish_test