/ Check-in [47f10b7e]
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 | trunk
Files: files | file ages | folders
SHA1: 47f10b7e5d8c1d965df891990090623444933cc9
User & Date: dan 2015-11-30 12:01:37
Context
2015-11-30
19:15
Fix the threadtest3 test program so that it works with VFSes that omit the xCurrentTime() method and supply only xCurrentTimeInt64(). check-in: 3b155855 user: drh tags: trunk
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
00:05
Simplifications to the locking logic in the unix-dotfile VFS. check-in: 58381352 user: drh 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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqlite.h.in.

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

Changes to src/test8.c.

   740    740       }
   741    741     }
   742    742     *pzStr = zIn;
   743    743     if( doFree ){
   744    744       sqlite3_free(zAppend);
   745    745     }
   746    746   }
          747  +
          748  +/*
          749  +** This function returns a pointer to an sqlite3_malloc()ed buffer 
          750  +** containing the select-list (the thing between keywords SELECT and FROM)
          751  +** to query the underlying real table with for the scan described by
          752  +** argument pIdxInfo.
          753  +**
          754  +** If the current SQLite version is earlier than 3.10.0, this is just "*"
          755  +** (select all columns). Or, for version 3.10.0 and greater, the list of
          756  +** columns identified by the pIdxInfo->colUsed mask.
          757  +*/
          758  +static char *echoSelectList(echo_vtab *pTab, sqlite3_index_info *pIdxInfo){
          759  +  char *zRet = 0;
          760  +  if( sqlite3_libversion_number()<3010000 ){
          761  +    zRet = sqlite3_mprintf(", *");
          762  +  }else{
          763  +    int i;
          764  +    for(i=0; i<pTab->nCol; i++){
          765  +      if( pIdxInfo->colUsed & ((sqlite3_uint64)1 << (i>=63 ? 63 : i)) ){
          766  +        zRet = sqlite3_mprintf("%z, %s", zRet, pTab->aCol[i]);
          767  +      }else{
          768  +        zRet = sqlite3_mprintf("%z, NULL", zRet);
          769  +      }
          770  +      if( !zRet ) break;
          771  +    }
          772  +  }
          773  +  return zRet;
          774  +}
   747    775   
   748    776   /*
   749    777   ** The echo module implements the subset of query constraints and sort
   750    778   ** orders that may take advantage of SQLite indices on the underlying
   751    779   ** real table. For example, if the real table is declared as:
   752    780   **
   753    781   **     CREATE TABLE real(a, b, c);
................................................................................
   766    794   **
   767    795   ** where the <where-clause> and <order-by-clause> are determined
   768    796   ** by the contents of the structure pointed to by the pIdxInfo argument.
   769    797   */
   770    798   static int echoBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
   771    799     int ii;
   772    800     char *zQuery = 0;
          801  +  char *zCol = 0;
   773    802     char *zNew;
   774    803     int nArg = 0;
   775    804     const char *zSep = "WHERE";
   776    805     echo_vtab *pVtab = (echo_vtab *)tab;
   777    806     sqlite3_stmt *pStmt = 0;
   778    807     Tcl_Interp *interp = pVtab->interp;
   779    808   
................................................................................
   813    842       nRow = sqlite3_column_int(pStmt, 0);
   814    843       rc = sqlite3_finalize(pStmt);
   815    844       if( rc!=SQLITE_OK ){
   816    845         return rc;
   817    846       }
   818    847     }
   819    848   
   820         -  zQuery = sqlite3_mprintf("SELECT rowid, * FROM %Q", pVtab->zTableName);
   821         -  if( !zQuery ){
   822         -    return SQLITE_NOMEM;
   823         -  }
          849  +  zCol = echoSelectList(pVtab, pIdxInfo);
          850  +  if( !zCol ) return SQLITE_NOMEM;
          851  +  zQuery = sqlite3_mprintf("SELECT rowid%z FROM %Q", zCol, pVtab->zTableName);
          852  +  if( !zQuery ) return SQLITE_NOMEM;
          853  +
   824    854     for(ii=0; ii<pIdxInfo->nConstraint; ii++){
   825    855       const struct sqlite3_index_constraint *pConstraint;
   826    856       struct sqlite3_index_constraint_usage *pUsage;
   827    857       int iCol;
   828    858   
   829    859       pConstraint = &pIdxInfo->aConstraint[ii];
   830    860       pUsage = &pIdxInfo->aConstraintUsage[ii];

Changes to src/update.c.

   259    259   #endif
   260    260     }
   261    261     assert( (chngRowid & chngPk)==0 );
   262    262     assert( chngRowid==0 || chngRowid==1 );
   263    263     assert( chngPk==0 || chngPk==1 );
   264    264     chngKey = chngRowid + chngPk;
   265    265   
   266         -  /* The SET expressions are not actually used inside the WHERE loop.
   267         -  ** So reset the colUsed mask
          266  +  /* The SET expressions are not actually used inside the WHERE loop.  
          267  +  ** So reset the colUsed mask. Unless this is a virtual table. In that
          268  +  ** case, set all bits of the colUsed mask (to ensure that the virtual
          269  +  ** table implementation makes all columns available).
   268    270     */
   269         -  pTabList->a[0].colUsed = 0;
          271  +  pTabList->a[0].colUsed = IsVirtual(pTab) ? (Bitmask)-1 : 0;
   270    272   
   271    273     hasFK = sqlite3FkRequired(pParse, pTab, aXRef, chngKey);
   272    274   
   273    275     /* There is one entry in the aRegIdx[] array for each index on the table
   274    276     ** being updated.  Fill in aRegIdx[] with a register number that will hold
   275    277     ** the key for accessing each index.
   276    278     **

Changes to src/where.c.

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

Changes to test/tkt3871.test.

    28     28     execsql { SELECT * FROM e WHERE a = 1 OR a = 2 }
    29     29   } {1 1 2 4}
    30     30   do_test tkt3871-1.3 {
    31     31     set echo_module ""
    32     32     execsql { SELECT * FROM e WHERE a = 1 OR a = 2 }
    33     33     set echo_module
    34     34   } [list \
    35         -  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 1 \
    36         -  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 2 \
           35  +  xFilter {SELECT rowid, a, b FROM 't1' WHERE a = ?} 1 \
           36  +  xFilter {SELECT rowid, a, b FROM 't1' WHERE a = ?} 2 \
    37     37   ]
    38     38   
    39     39   do_test tkt3871-1.4 {
    40     40     execsql { SELECT * FROM e WHERE a = 1 OR a = 2 OR b = 9 }
    41     41   } {1 1 2 4 3 9}
    42     42   do_test tkt3871-1.5 {
    43     43     set echo_module ""
    44     44     execsql { SELECT * FROM e WHERE a = 1 OR a = 2 OR b = 9 }
    45     45     set echo_module
    46     46   } [list \
    47         -  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 1 \
    48         -  xFilter {SELECT rowid, * FROM 't1' WHERE a = ?} 2 \
    49         -  xFilter {SELECT rowid, * FROM 't1' WHERE b = ?} 9
           47  +  xFilter {SELECT rowid, a, b FROM 't1' WHERE a = ?} 1 \
           48  +  xFilter {SELECT rowid, a, b FROM 't1' WHERE a = ?} 2 \
           49  +  xFilter {SELECT rowid, a, b FROM 't1' WHERE b = ?} 9
    50     50   ]
    51     51   
    52     52   
    53     53   finish_test

Changes to test/vtab1.test.

   389    389     }
   390    390   } {1 2 3 4 5 6}
   391    391   do_test vtab1-3.7 {
   392    392     execsql {
   393    393       SELECT rowid, * FROM t1;
   394    394     }
   395    395   } {1 1 2 3 2 4 5 6}
   396         -do_test vtab1-3.8 {
          396  +do_test vtab1-3.8.1 {
   397    397     execsql {
   398    398       SELECT a AS d, b AS e, c AS f FROM t1;
   399    399     }
   400    400   } {1 2 3 4 5 6}
   401    401   
   402    402   # Execute some SELECT statements with WHERE clauses on the t1 table.
   403    403   # Then check the echo_module variable (written to by the module methods
   404    404   # in test8.c) to make sure the xBestIndex() and xFilter() methods were
   405    405   # called correctly.
   406    406   #
   407         -do_test vtab1-3.8 {
          407  +do_test vtab1-3.8.2 {
   408    408     set echo_module ""
   409    409     execsql {
   410    410       SELECT * FROM t1;
   411    411     }
   412    412     set echo_module
   413         -} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
   414         -        xFilter    {SELECT rowid, * FROM 'treal'} ]
          413  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
          414  +        xFilter    {SELECT rowid, a, b, c FROM 'treal'} ]
   415    415   do_test vtab1-3.9 {
   416    416     set echo_module ""
   417    417     execsql {
   418    418       SELECT * FROM t1 WHERE b = 5;
   419    419     }
   420    420   } {4 5 6}
   421    421   do_test vtab1-3.10 {
   422    422     set echo_module
   423         -} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ?}   \
   424         -        xFilter    {SELECT rowid, * FROM 'treal' WHERE b = ?} 5 ]
          423  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b = ?}   \
          424  +        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b = ?} 5 ]
   425    425   do_test vtab1-3.10 {
   426    426     set echo_module ""
   427    427     execsql {
   428    428       SELECT * FROM t1 WHERE b >= 5 AND b <= 10;
   429    429     }
   430    430   } {4 5 6}
   431    431   do_test vtab1-3.11 {
   432    432     set echo_module
   433         -} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
   434         -        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 5 10 ]
          433  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
          434  +        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
          435  +        5 10 ]
   435    436   do_test vtab1-3.12 {
   436    437     set echo_module ""
   437    438     execsql {
   438    439       SELECT * FROM t1 WHERE b BETWEEN 2 AND 10;
   439    440     }
   440    441   } {1 2 3 4 5 6}
   441    442   do_test vtab1-3.13 {
   442    443     set echo_module
   443         -} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
   444         -        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ]
          444  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
          445  +        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
          446  +        2 10 ]
   445    447   
   446    448   # Add a function for the MATCH operator. Everything always matches!
   447    449   #proc test_match {lhs rhs} {
   448    450   #  lappend ::echo_module MATCH $lhs $rhs
   449    451   #  return 1
   450    452   #}
   451    453   #db function match test_match
................................................................................
   455    457     set echo_module ""
   456    458     catchsql {
   457    459       SELECT * FROM t1 WHERE a MATCH 'string';
   458    460     }
   459    461   } {1 {unable to use function MATCH in the requested context}}
   460    462   do_test vtab1-3.13 {
   461    463     set echo_module
   462         -} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
   463         -        xFilter    {SELECT rowid, * FROM 'treal'}]
          464  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
          465  +        xFilter    {SELECT rowid, a, b, c FROM 'treal'}]
   464    466   ifcapable subquery {
   465    467   # The echo module uses a subquery internally to implement the MATCH operator.
   466    468   do_test vtab1-3.14 {
   467    469     set echo_module ""
   468    470     execsql {
   469    471       SELECT * FROM t1 WHERE b MATCH 'string';
   470    472     }
   471    473   } {}
   472    474   do_test vtab1-3.15 {
   473    475     set echo_module
   474    476   } [list xBestIndex \
   475         -        {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')}  \
          477  +        {SELECT rowid, a, b, c FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
   476    478           xFilter \
   477         -        {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
          479  +        {SELECT rowid, a, b, c FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
   478    480           string ]
   479    481   }; #ifcapable subquery
   480    482   
   481    483   #----------------------------------------------------------------------
   482    484   # Test case vtab1-3 test table scans and the echo module's 
   483    485   # xBestIndex/xFilter handling of ORDER BY clauses.
   484    486   
................................................................................
   501    503     set echo_module ""
   502    504     cksort {
   503    505       SELECT b FROM t1 ORDER BY b;
   504    506     }
   505    507   } {2 5 nosort}
   506    508   do_test vtab1-4.2 {
   507    509     set echo_module
   508         -} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \
   509         -        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ]
          510  +} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b ASC} \
          511  +        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b ASC} ]
   510    512   do_test vtab1-4.3 {
   511    513     set echo_module ""
   512    514     cksort {
   513    515       SELECT b FROM t1 ORDER BY b DESC;
   514    516     }
   515    517   } {5 2 nosort}
   516    518   do_test vtab1-4.4 {
   517    519     set echo_module
   518         -} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \
   519         -        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ]
          520  +} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b DESC} \
          521  +        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b DESC} ]
   520    522   do_test vtab1-4.3 {
   521    523     set echo_module ""
   522    524     cksort {
   523    525       SELECT b FROM t1 ORDER BY b||'';
   524    526     }
   525    527   } {2 5 sort}
   526    528   do_test vtab1-4.4 {
   527    529     set echo_module
   528         -} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
   529         -        xFilter    {SELECT rowid, * FROM 'treal'} ]
          530  +} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal'} \
          531  +        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal'} ]
   530    532   
   531    533   execsql {
   532    534     DROP TABLE t1;
   533    535     DROP TABLE treal;
   534    536   }
   535    537   
   536    538   #----------------------------------------------------------------------
................................................................................
   571    573     1 red green 2 hearts diamonds  \
   572    574     2 blue black 1 spades clubs    \
   573    575     2 blue black 2 hearts diamonds \
   574    576   ]
   575    577   do_test vtab1-5-3 {
   576    578     filter $echo_module
   577    579   } [list \
   578         -  xFilter {SELECT rowid, * FROM 't1'} \
   579         -  xFilter {SELECT rowid, * FROM 't2'} \
   580         -  xFilter {SELECT rowid, * FROM 't2'} \
          580  +  xFilter {SELECT rowid, a, b, c FROM 't1'} \
          581  +  xFilter {SELECT rowid, d, e, f FROM 't2'} \
          582  +  xFilter {SELECT rowid, d, e, f FROM 't2'} \
   581    583   ]
   582    584   do_test vtab1-5-4 {
   583    585     set echo_module ""
   584    586     execsql {
   585    587       SELECT * FROM et1, et2 WHERE et2.d = 2;
   586    588     }
   587    589   } [list \
   588    590     1 red green 2 hearts diamonds  \
   589    591     2 blue black 2 hearts diamonds \
   590    592   ]
   591    593   do_test vtab1-5-5 {
   592    594     filter $echo_module
   593    595   } [list \
   594         -  xFilter {SELECT rowid, * FROM 't1'} \
   595         -  xFilter {SELECT rowid, * FROM 't2'} \
   596         -  xFilter {SELECT rowid, * FROM 't2'} \
          596  +  xFilter {SELECT rowid, a, b, c FROM 't1'} \
          597  +  xFilter {SELECT rowid, d, e, f FROM 't2'} \
          598  +  xFilter {SELECT rowid, d, e, f FROM 't2'} \
   597    599   ]
   598    600   do_test vtab1-5-6 {
   599    601     execsql {
   600    602       CREATE INDEX i1 ON t2(d);
   601    603     }
   602    604   
   603    605     db close
................................................................................
   611    613   } [list \
   612    614     1 red green 2 hearts diamonds  \
   613    615     2 blue black 2 hearts diamonds \
   614    616   ]
   615    617   do_test vtab1-5-7 {
   616    618     filter $::echo_module
   617    619   } [list \
   618         -  xFilter {SELECT rowid, * FROM 't1'}             \
   619         -  xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
   620         -  xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
          620  +  xFilter {SELECT rowid, a, b, c FROM 't1'}             \
          621  +  xFilter {SELECT rowid, d, e, f FROM 't2' WHERE d = ?} \
          622  +  xFilter {SELECT rowid, d, e, f FROM 't2' WHERE d = ?} \
   621    623   ]
   622    624   
   623    625   execsql {
   624    626     DROP TABLE t1;
   625    627     DROP TABLE t2;
   626    628     DROP TABLE et1;
   627    629     DROP TABLE et2;
................................................................................
   963    965   do_test vtab1.10-5 {
   964    966     set echo_module ""
   965    967     execsql {
   966    968       SELECT * FROM e WHERE rowid||'' MATCH 'pattern';
   967    969     }
   968    970     set echo_module
   969    971   } [list \
   970         -  xBestIndex {SELECT rowid, * FROM 'r'} \
   971         -  xFilter {SELECT rowid, * FROM 'r'}    \
          972  +  xBestIndex {SELECT rowid, a, b, c FROM 'r'} \
          973  +  xFilter {SELECT rowid, a, b, c FROM 'r'}    \
   972    974   ]
   973    975   proc match_func {args} {return ""}
   974    976   do_test vtab1.10-6 {
   975    977     set echo_module ""
   976    978     db function match match_func
   977    979     execsql {
   978    980       SELECT * FROM e WHERE match('pattern', rowid, 'pattern2');
   979    981     }
   980    982     set echo_module
   981    983   } [list \
   982         -  xBestIndex {SELECT rowid, * FROM 'r'} \
   983         -  xFilter {SELECT rowid, * FROM 'r'}    \
          984  +  xBestIndex {SELECT rowid, a, b, c FROM 'r'} \
          985  +  xFilter {SELECT rowid, a, b, c FROM 'r'}    \
   984    986   ]
   985    987   
   986    988   
   987    989   # Testing the xFindFunction interface
   988    990   #
   989    991   catch {rename ::echo_glob_overload {}}
   990    992   do_test vtab1.11-1 {
................................................................................
  1149   1151   #  set echo_module
  1150   1152   #} {/.*xBestIndex {SELECT rowid, . FROM 'c' WHERE rowid = .} xFilter {SELECT rowid, . FROM 'c'} 1/}
  1151   1153   
  1152   1154   do_test vtab1-14.2 {
  1153   1155     set echo_module ""
  1154   1156     execsql { SELECT * FROM echo_c WHERE rowid = 1 }
  1155   1157     set echo_module
  1156         -} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE rowid = ?} xFilter {SELECT rowid, * FROM 'c' WHERE rowid = ?} 1]
         1158  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'c' WHERE rowid = ?} \
         1159  +        xFilter {SELECT rowid, a, b, c FROM 'c' WHERE rowid = ?} 1]
  1157   1160   
  1158   1161   do_test vtab1-14.3 {
  1159   1162     set echo_module ""
  1160   1163     execsql { SELECT * FROM echo_c WHERE a = 1 }
  1161   1164     set echo_module
  1162         -} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1]
         1165  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'c' WHERE a = ?} \
         1166  +        xFilter {SELECT rowid, a, b, c FROM 'c' WHERE a = ?} 1]
  1163   1167   
  1164   1168   #do_test vtab1-14.4 {
  1165   1169   #  set echo_module ""
  1166   1170   #  execsql { SELECT * FROM echo_c WHERE a IN (1, 2) }
  1167   1171   #  set echo_module
  1168   1172   #} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE a = .} xFilter {SELECT rowid, . FROM 'c' WHERE a = .} 1/}
  1169   1173   
................................................................................
  1296   1300     INSERT INTO t6 VALUES(5, 'Phillip');
  1297   1301     INSERT INTO t6 VALUES(6, 'Bartholomew');
  1298   1302     CREATE VIRTUAL TABLE e6 USING echo(t6);
  1299   1303   }
  1300   1304   
  1301   1305   foreach {tn sql res filter} {
  1302   1306     1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5}
  1303         -    {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James}
         1307  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b > ?} James}
  1304   1308   
  1305   1309     1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4}
  1306         -    {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K}
         1310  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b >= ? AND b < ?} J K}
  1307   1311   
  1308   1312     1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
  1309         -    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%}
         1313  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} J%}
  1310   1314   
  1311   1315     1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
  1312         -    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%}
         1316  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} j%}
  1313   1317   } {
  1314   1318     set echo_module {}
  1315   1319     do_execsql_test 18.$tn.1 $sql $res
  1316   1320     do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
  1317   1321   }
  1318   1322   
  1319   1323   do_execsql_test 18.2.0 {  PRAGMA case_sensitive_like = ON }
  1320   1324   foreach {tn sql res filter} {
  1321   1325     2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
  1322         -    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%}
         1326  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} J%}
  1323   1327   
  1324   1328     2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
  1325         -    {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%}
         1329  +    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} j%}
  1326   1330   } {
  1327   1331     set echo_module {}
  1328   1332     do_execsql_test 18.$tn.1 $sql $res
  1329   1333     do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
  1330   1334   }
  1331   1335   do_execsql_test 18.2.x {  PRAGMA case_sensitive_like = OFF }
  1332   1336   

Changes to test/vtab4.test.

    53     53   } {xBegin echo(treal) xSync echo(treal) xCommit echo(treal)}
    54     54   do_test vtab4-1.3 {
    55     55     set echo_module [list]
    56     56     execsql {
    57     57       UPDATE techo SET a = 2;
    58     58     }
    59     59     set echo_module
    60         -} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
           60  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
    61     61           xBegin     echo(treal)                    \
    62         -        xFilter    {SELECT rowid, * FROM 'treal'} \
           62  +        xFilter    {SELECT rowid, a, b, c FROM 'treal'} \
    63     63           xSync      echo(treal)                    \
    64     64           xCommit    echo(treal)                    \
    65     65   ]
    66     66   do_test vtab4-1.4 {
    67     67     set echo_module [list]
    68     68     execsql {
    69     69       DELETE FROM techo;
    70     70     }
    71     71     set echo_module
    72         -} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
           72  +} [list xBestIndex {SELECT rowid, NULL, NULL, NULL FROM 'treal'} \
    73     73           xBegin     echo(treal)                    \
    74         -        xFilter    {SELECT rowid, * FROM 'treal'} \
           74  +        xFilter    {SELECT rowid, NULL, NULL, NULL FROM 'treal'} \
    75     75           xSync      echo(treal)                    \
    76     76           xCommit    echo(treal)                    \
    77     77   ]
    78     78   
    79     79   # Ensure xBegin is not called more than once in a single transaction.
    80     80   #
    81     81   do_test vtab4-2.1 {
................................................................................
   101    101     execsql {
   102    102       BEGIN;
   103    103       INSERT INTO secho SELECT * FROM techo;
   104    104       DELETE FROM techo;
   105    105       COMMIT;
   106    106     }
   107    107     set echo_module
   108         -} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
          108  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
   109    109           xBegin     echo(sreal)                    \
   110         -        xFilter    {SELECT rowid, * FROM 'treal'} \
   111         -        xBestIndex {SELECT rowid, * FROM 'treal'} \
          110  +        xFilter    {SELECT rowid, a, b, c FROM 'treal'} \
          111  +        xBestIndex {SELECT rowid, NULL, NULL, NULL FROM 'treal'} \
   112    112           xBegin     echo(treal)                    \
   113         -        xFilter    {SELECT rowid, * FROM 'treal'} \
          113  +        xFilter    {SELECT rowid, NULL, NULL, NULL FROM 'treal'} \
   114    114           xSync   echo(sreal)                       \
   115    115           xSync   echo(treal)                       \
   116    116           xCommit echo(sreal)                       \
   117    117           xCommit echo(treal)                       \
   118    118   ]
   119    119   do_test vtab4-2.3 {
   120    120     execsql {
................................................................................
   133    133     execsql {
   134    134       BEGIN;
   135    135       INSERT INTO techo SELECT * FROM secho;
   136    136       DELETE FROM secho;
   137    137       ROLLBACK;
   138    138     }
   139    139     set echo_module
   140         -} [list xBestIndex {SELECT rowid, * FROM 'sreal'} \
          140  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'sreal'} \
   141    141           xBegin     echo(treal)                    \
   142         -        xFilter    {SELECT rowid, * FROM 'sreal'} \
   143         -        xBestIndex {SELECT rowid, * FROM 'sreal'} \
          142  +        xFilter    {SELECT rowid, a, b, c FROM 'sreal'} \
          143  +        xBestIndex {SELECT rowid, NULL, NULL, NULL FROM 'sreal'} \
   144    144           xBegin     echo(sreal)                    \
   145         -        xFilter    {SELECT rowid, * FROM 'sreal'} \
          145  +        xFilter    {SELECT rowid, NULL, NULL, NULL FROM 'sreal'} \
   146    146           xRollback  echo(treal)                    \
   147    147           xRollback  echo(sreal)                    \
   148    148   ]
   149    149   do_test vtab4-2.6 {
   150    150     execsql {
   151    151       SELECT * FROM secho;
   152    152     }
................................................................................
   174    174     catchsql {
   175    175       BEGIN;
   176    176       INSERT INTO techo SELECT * FROM secho;
   177    177       DELETE FROM secho;
   178    178       COMMIT;
   179    179     }
   180    180     set echo_module
   181         -} [list xBestIndex {SELECT rowid, * FROM 'sreal'} \
          181  +} [list xBestIndex {SELECT rowid, a, b, c FROM 'sreal'} \
   182    182           xBegin     echo(treal)                    \
   183         -        xFilter    {SELECT rowid, * FROM 'sreal'} \
   184         -        xBestIndex {SELECT rowid, * FROM 'sreal'} \
          183  +        xFilter    {SELECT rowid, a, b, c FROM 'sreal'} \
          184  +        xBestIndex {SELECT rowid, NULL, NULL, NULL FROM 'sreal'} \
   185    185           xBegin     echo(sreal)                    \
   186         -        xFilter    {SELECT rowid, * FROM 'sreal'} \
          186  +        xFilter    {SELECT rowid, NULL, NULL, NULL FROM 'sreal'} \
   187    187           xSync      echo(treal)                    \
   188    188           xSync      echo(sreal)                    \
   189    189           xRollback  echo(treal)                    \
   190    190           xRollback  echo(sreal)                    \
   191    191   ]
   192    192   
   193    193   finish_test

Changes to test/vtabH.test.

    28     28     CREATE TABLE t6(a, b TEXT);
    29     29     CREATE INDEX i6 ON t6(b, a);
    30     30     CREATE VIRTUAL TABLE e6 USING echo(t6);
    31     31   }
    32     32   
    33     33   foreach {tn sql expect} {
    34     34     1 "SELECT * FROM e6 WHERE b LIKE 'abc'" {
    35         -    xBestIndex {SELECT rowid, * FROM 't6' WHERE b like ?} 
    36         -    xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} abc
           35  +    xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b like ?} 
           36  +    xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} abc
    37     37     }
    38     38   
    39     39     2 "SELECT * FROM e6 WHERE b GLOB 'abc'" {
    40         -    xBestIndex {SELECT rowid, * FROM 't6' WHERE b glob ?} 
    41         -    xFilter {SELECT rowid, * FROM 't6' WHERE b glob ?} abc
           40  +    xBestIndex {SELECT rowid, a, b FROM 't6' WHERE b glob ?} 
           41  +    xFilter {SELECT rowid, a, b FROM 't6' WHERE b glob ?} abc
    42     42     }
    43     43   } {
    44     44     do_test 1.$tn {
    45     45       set echo_module {}
    46     46       execsql $sql
    47     47       set ::echo_module
    48     48     } [list {*}$expect]

Added test/vtabI.test.

            1  +# 2015 Nov 26
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library. Specifically,
           12  +# it tests the sqlite3_index_info.colUsed variable is set correctly.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix vtabI
           18  +
           19  +ifcapable !vtab {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +register_echo_module db
           25  +
           26  +do_execsql_test 1.0 {
           27  +  CREATE TABLE t1(a, b, c, d, e);
           28  +  CREATE VIRTUAL TABLE e1 USING echo(t1);
           29  +}
           30  +
           31  +foreach {tn query filter} {
           32  +  1 {SELECT * FROM e1} 
           33  +    {SELECT rowid, a, b, c, d, e FROM 't1'}
           34  +
           35  +  2 {SELECT a, b FROM e1} 
           36  +    {SELECT rowid, a, b, NULL, NULL, NULL FROM 't1'}
           37  +
           38  +  3 {SELECT count(*) FROM e1 GROUP BY b} 
           39  +    {SELECT rowid, NULL, b, NULL, NULL, NULL FROM 't1'}
           40  +
           41  +  4 {SELECT count(*) FROM e1 GROUP BY b HAVING a=?} 
           42  +    {SELECT rowid, a, b, NULL, NULL, NULL FROM 't1'}
           43  +
           44  +  5 {SELECT a FROM e1 WHERE c=?}
           45  +    {SELECT rowid, a, NULL, c, NULL, NULL FROM 't1'}
           46  +
           47  +  6 {SELECT a FROM e1 ORDER BY e}
           48  +    {SELECT rowid, a, NULL, NULL, NULL, e FROM 't1'}
           49  +
           50  +  7 {SELECT a FROM e1 ORDER BY e, d}
           51  +    {SELECT rowid, a, NULL, NULL, d, e FROM 't1'}
           52  +} {
           53  +  do_test 1.$tn {
           54  +    set ::echo_module [list]
           55  +    execsql $query
           56  +    set idx [lsearch -exact $::echo_module xFilter]
           57  +    lindex $::echo_module [expr $idx+1]
           58  +  } $filter
           59  +}
           60  +
           61  +#-------------------------------------------------------------------------
           62  +# Tests with a table with more than 64 columns.
           63  +#
           64  +proc all_col_list {} {
           65  +  set L [list]
           66  +  for {set i 1} {$i <= 100} {incr i} { lappend L "c$i" }
           67  +  set L
           68  +}
           69  +
           70  +proc part_col_list {cols} {
           71  +  set L [list]
           72  +  for {set i 1} {$i <= 100} {incr i} { 
           73  +    set c "c$i"
           74  +    if {[lsearch $cols $c]>=0} {
           75  +      lappend L "c$i" 
           76  +    } else {
           77  +      lappend L NULL
           78  +    }
           79  +  }
           80  +  set L
           81  +}
           82  +proc CL {args} {
           83  +  join [part_col_list $args] ", "
           84  +}
           85  +proc CLT {args} {
           86  +  set cols $args
           87  +  for {set i 64} {$i <= 100} {incr i} {
           88  +    lappend cols "c$i"
           89  +  }
           90  +  join [part_col_list $cols] ", "
           91  +}
           92  +
           93  +do_test 2.0 {
           94  +  execsql "CREATE TABLE t2([join [all_col_list] ,])"
           95  +  execsql "CREATE VIRTUAL TABLE e2 USING echo(t2)"
           96  +} {}
           97  +
           98  +foreach {tn query filter} {
           99  +  1 {SELECT c1, c10, c20 FROM e2} 
          100  +    {SELECT rowid, [CL c1 c10 c20] FROM 't2'}
          101  +
          102  +  2 {SELECT c40, c50, c60 FROM e2} 
          103  +    {SELECT rowid, [CL c40 c50 c60] FROM 't2'}
          104  +
          105  +  3 {SELECT c7, c80, c90 FROM e2} 
          106  +    {SELECT rowid, [CLT c7] FROM 't2'}
          107  +
          108  +  4 {SELECT c64 FROM e2} 
          109  +    {SELECT rowid, [CLT c64] FROM 't2'}
          110  +
          111  +  5 {SELECT c63 FROM e2} 
          112  +    {SELECT rowid, [CL c63] FROM 't2'}
          113  +
          114  +  6 {SELECT c22 FROM e2 ORDER BY c50, c70} 
          115  +    {SELECT rowid, [CLT c22 c50] FROM 't2'}
          116  +
          117  +} {
          118  +  do_test 2.$tn {
          119  +    set ::echo_module [list]
          120  +    execsql $query
          121  +    set idx [lsearch -exact $::echo_module xFilter]
          122  +    lindex $::echo_module [expr $idx+1]
          123  +  } [subst $filter]
          124  +}
          125  +
          126  +finish_test