/ Check-in [82d3d1ae]
Login

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

Overview
Comment:Enhancements to the query planner to exploit transitive relationships in the WHERE clause, and other minor changes to bring the sessions branch into alignment with the trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 82d3d1ae824e1fbc7958657be79231590ec17ace
User & Date: drh 2013-01-25 02:10:06
Context
2013-02-13
13:42
Enhancements to the query planner to make use of indices for ORDER BY even when IN constraints are in the WHERE clause. Add extended error codes for all SQLITE_CONSTRAINT errors. check-in: 7e14dc73 user: drh tags: sessions
2013-01-25
02:10
Enhancements to the query planner to exploit transitive relationships in the WHERE clause, and other minor changes to bring the sessions branch into alignment with the trunk. check-in: 82d3d1ae user: drh tags: sessions
2013-01-23
18:53
Improved parsing of the arguments to the ".backup" command in the command-line shell. check-in: f1127e87 user: drh tags: trunk
2013-01-09
14:49
Merge latest trunk changes into the sessions branch, especially the ORDER BY bug fix of 3.7.15.2. check-in: 34af6fac user: drh tags: sessions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.msc.

   679    679     $(TOP)\src\test_autoext.c \
   680    680     $(TOP)\src\test_async.c \
   681    681     $(TOP)\src\test_backup.c \
   682    682     $(TOP)\src\test_btree.c \
   683    683     $(TOP)\src\test_config.c \
   684    684     $(TOP)\src\test_demovfs.c \
   685    685     $(TOP)\src\test_devsym.c \
          686  +  $(TOP)\src\test_fs.c \
   686    687     $(TOP)\src\test_func.c \
   687    688     $(TOP)\src\test_fuzzer.c \
   688    689     $(TOP)\src\test_hexio.c \
   689    690     $(TOP)\src\test_init.c \
   690    691     $(TOP)\src\test_intarray.c \
   691    692     $(TOP)\src\test_journal.c \
   692    693     $(TOP)\src\test_malloc.c \

Changes to ext/rtree/rtree.c.

  3045   3045   ** This ensures that each node is stored on a single database page. If the 
  3046   3046   ** database page-size is so large that more than RTREE_MAXCELLS entries 
  3047   3047   ** would fit in a single node, use a smaller node-size.
  3048   3048   */
  3049   3049   static int getNodeSize(
  3050   3050     sqlite3 *db,                    /* Database handle */
  3051   3051     Rtree *pRtree,                  /* Rtree handle */
  3052         -  int isCreate                    /* True for xCreate, false for xConnect */
         3052  +  int isCreate,                   /* True for xCreate, false for xConnect */
         3053  +  char **pzErr                    /* OUT: Error message, if any */
  3053   3054   ){
  3054   3055     int rc;
  3055   3056     char *zSql;
  3056   3057     if( isCreate ){
  3057   3058       int iPageSize = 0;
  3058   3059       zSql = sqlite3_mprintf("PRAGMA %Q.page_size", pRtree->zDb);
  3059   3060       rc = getIntFromStmt(db, zSql, &iPageSize);
  3060   3061       if( rc==SQLITE_OK ){
  3061   3062         pRtree->iNodeSize = iPageSize-64;
  3062   3063         if( (4+pRtree->nBytesPerCell*RTREE_MAXCELLS)<pRtree->iNodeSize ){
  3063   3064           pRtree->iNodeSize = 4+pRtree->nBytesPerCell*RTREE_MAXCELLS;
  3064   3065         }
         3066  +    }else{
         3067  +      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
  3065   3068       }
  3066   3069     }else{
  3067   3070       zSql = sqlite3_mprintf(
  3068   3071           "SELECT length(data) FROM '%q'.'%q_node' WHERE nodeno = 1",
  3069   3072           pRtree->zDb, pRtree->zName
  3070   3073       );
  3071   3074       rc = getIntFromStmt(db, zSql, &pRtree->iNodeSize);
         3075  +    if( rc!=SQLITE_OK ){
         3076  +      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
         3077  +    }
  3072   3078     }
  3073   3079   
  3074   3080     sqlite3_free(zSql);
  3075   3081     return rc;
  3076   3082   }
  3077   3083   
  3078   3084   /* 
................................................................................
  3128   3134     pRtree->nDim = (argc-4)/2;
  3129   3135     pRtree->nBytesPerCell = 8 + pRtree->nDim*4*2;
  3130   3136     pRtree->eCoordType = eCoordType;
  3131   3137     memcpy(pRtree->zDb, argv[1], nDb);
  3132   3138     memcpy(pRtree->zName, argv[2], nName);
  3133   3139   
  3134   3140     /* Figure out the node size to use. */
  3135         -  rc = getNodeSize(db, pRtree, isCreate);
         3141  +  rc = getNodeSize(db, pRtree, isCreate, pzErr);
  3136   3142   
  3137   3143     /* Create/Connect to the underlying relational database schema. If
  3138   3144     ** that is successful, call sqlite3_declare_vtab() to configure
  3139   3145     ** the r-tree table schema.
  3140   3146     */
  3141   3147     if( rc==SQLITE_OK ){
  3142   3148       if( (rc = rtreeSqlInit(pRtree, db, argv[1], argv[2], isCreate)) ){

Changes to main.mk.

   237    237     $(TOP)/src/test_autoext.c \
   238    238     $(TOP)/src/test_async.c \
   239    239     $(TOP)/src/test_backup.c \
   240    240     $(TOP)/src/test_btree.c \
   241    241     $(TOP)/src/test_config.c \
   242    242     $(TOP)/src/test_demovfs.c \
   243    243     $(TOP)/src/test_devsym.c \
          244  +  $(TOP)/src/test_fs.c \
   244    245     $(TOP)/src/test_func.c \
   245    246     $(TOP)/src/test_fuzzer.c \
   246    247     $(TOP)/src/test_hexio.c \
   247    248     $(TOP)/src/test_init.c \
   248    249     $(TOP)/src/test_intarray.c \
   249    250     $(TOP)/src/test_journal.c \
   250    251     $(TOP)/src/test_malloc.c \

Changes to src/pragma.c.

  1729   1729         sqlite3_key(db, zKey, i/2);
  1730   1730       }else{
  1731   1731         sqlite3_rekey(db, zKey, i/2);
  1732   1732       }
  1733   1733     }else
  1734   1734   #endif
  1735   1735   #if defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD)
  1736         -  if( sqlite3StrICmp(zLeft, "activate_extensions")==0 ){
         1736  +  if( sqlite3StrICmp(zLeft, "activate_extensions")==0 && zRight ){
  1737   1737   #ifdef SQLITE_HAS_CODEC
  1738   1738       if( sqlite3StrNICmp(zRight, "see-", 4)==0 ){
  1739   1739         sqlite3_activate_see(&zRight[4]);
  1740   1740       }
  1741   1741   #endif
  1742   1742   #ifdef SQLITE_ENABLE_CEROD
  1743   1743       if( sqlite3StrNICmp(zRight, "cerod-", 6)==0 ){

Changes to src/shell.c.

  1625   1625     }
  1626   1626   
  1627   1627     /* Process the input line.
  1628   1628     */
  1629   1629     if( nArg==0 ) return 0; /* no tokens, no error */
  1630   1630     n = strlen30(azArg[0]);
  1631   1631     c = azArg[0][0];
  1632         -  if( c=='b' && n>=3 && strncmp(azArg[0], "backup", n)==0 && nArg>1 && nArg<4){
  1633         -    const char *zDestFile;
  1634         -    const char *zDb;
         1632  +  if( c=='b' && n>=3 && strncmp(azArg[0], "backup", n)==0 ){
         1633  +    const char *zDestFile = 0;
         1634  +    const char *zDb = 0;
         1635  +    const char *zKey = 0;
  1635   1636       sqlite3 *pDest;
  1636   1637       sqlite3_backup *pBackup;
  1637         -    if( nArg==2 ){
  1638         -      zDestFile = azArg[1];
  1639         -      zDb = "main";
  1640         -    }else{
  1641         -      zDestFile = azArg[2];
  1642         -      zDb = azArg[1];
         1638  +    int j;
         1639  +    for(j=1; j<nArg; j++){
         1640  +      const char *z = azArg[j];
         1641  +      if( z[0]=='-' ){
         1642  +        while( z[0]=='-' ) z++;
         1643  +        if( strcmp(z,"key")==0 && j<nArg-1 ){
         1644  +          zKey = azArg[++j];
         1645  +        }else
         1646  +        {
         1647  +          fprintf(stderr, "unknown option: %s\n", azArg[j]);
         1648  +          return 1;
         1649  +        }
         1650  +      }else if( zDestFile==0 ){
         1651  +        zDestFile = azArg[j];
         1652  +      }else if( zDb==0 ){
         1653  +        zDb = zDestFile;
         1654  +        zDestFile = azArg[j];
         1655  +      }else{
         1656  +        fprintf(stderr, "too many arguments to .backup\n");
         1657  +        return 1;
         1658  +      }
  1643   1659       }
         1660  +    if( zDestFile==0 ){
         1661  +      fprintf(stderr, "missing FILENAME argument on .backup\n");
         1662  +      return 1;
         1663  +    }
         1664  +    if( zDb==0 ) zDb = "main";
  1644   1665       rc = sqlite3_open(zDestFile, &pDest);
  1645   1666       if( rc!=SQLITE_OK ){
  1646   1667         fprintf(stderr, "Error: cannot open \"%s\"\n", zDestFile);
  1647   1668         sqlite3_close(pDest);
  1648   1669         return 1;
  1649   1670       }
         1671  +#ifdef SQLITE_HAS_CODEC
         1672  +    sqlite3_key(pDest, zKey, (int)strlen(zKey));
         1673  +#else
         1674  +    (void)zKey;
         1675  +#endif
  1650   1676       open_db(p);
  1651   1677       pBackup = sqlite3_backup_init(pDest, "main", p->db, zDb);
  1652   1678       if( pBackup==0 ){
  1653   1679         fprintf(stderr, "Error: %s\n", sqlite3_errmsg(pDest));
  1654   1680         sqlite3_close(pDest);
  1655   1681         return 1;
  1656   1682       }

Changes to src/sqliteInt.h.

   571    571   
   572    572   /*
   573    573   ** A convenience macro that returns the number of elements in
   574    574   ** an array.
   575    575   */
   576    576   #define ArraySize(X)    ((int)(sizeof(X)/sizeof(X[0])))
   577    577   
          578  +/*
          579  +** Determine if the argument is a power of two
          580  +*/
          581  +#define IsPowerOfTwo(X) (((X)&((X)-1))==0)
          582  +
   578    583   /*
   579    584   ** The following value as a destructor means to use sqlite3DbFree().
   580    585   ** The sqlite3DbFree() routine requires two parameters instead of the 
   581    586   ** one parameter that destructors normally want.  So we have to introduce 
   582    587   ** this magic value that the code knows to handle differently.  Any 
   583    588   ** pointer will work here as long as it is distinct from SQLITE_STATIC
   584    589   ** and SQLITE_TRANSIENT.
................................................................................
   977    982   #define SQLITE_GroupByOrder   0x0004   /* GROUPBY cover of ORDERBY */
   978    983   #define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
   979    984   #define SQLITE_IdxRealAsInt   0x0010   /* Store REAL as INT in indices */
   980    985   #define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
   981    986   #define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
   982    987   #define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
   983    988   #define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
          989  +#define SQLITE_Transitive     0x0200   /* Transitive constraints */
   984    990   #define SQLITE_AllOpts        0xffff   /* All optimizations */
   985    991   
   986    992   /*
   987    993   ** Macros for testing whether or not optimizations are enabled or disabled.
   988    994   */
   989    995   #ifndef SQLITE_OMIT_BUILTIN_TEST
   990    996   #define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)

Changes to src/tclsqlite.c.

  3813   3813       extern int Sqlitetest_hexio_Init(Tcl_Interp*);
  3814   3814       extern int Sqlitetest_init_Init(Tcl_Interp*);
  3815   3815       extern int Sqlitetest_malloc_Init(Tcl_Interp*);
  3816   3816       extern int Sqlitetest_mutex_Init(Tcl_Interp*);
  3817   3817       extern int Sqlitetestschema_Init(Tcl_Interp*);
  3818   3818       extern int Sqlitetestsse_Init(Tcl_Interp*);
  3819   3819       extern int Sqlitetesttclvar_Init(Tcl_Interp*);
         3820  +    extern int Sqlitetestfs_Init(Tcl_Interp*);
  3820   3821       extern int SqlitetestThread_Init(Tcl_Interp*);
  3821   3822       extern int SqlitetestOnefile_Init();
  3822   3823       extern int SqlitetestOsinst_Init(Tcl_Interp*);
  3823   3824       extern int Sqlitetestbackup_Init(Tcl_Interp*);
  3824   3825       extern int Sqlitetestintarray_Init(Tcl_Interp*);
  3825   3826       extern int Sqlitetestvfs_Init(Tcl_Interp *);
  3826   3827       extern int Sqlitetestrtree_Init(Tcl_Interp*);
................................................................................
  3860   3861       Sqlitetest_func_Init(interp);
  3861   3862       Sqlitetest_hexio_Init(interp);
  3862   3863       Sqlitetest_init_Init(interp);
  3863   3864       Sqlitetest_malloc_Init(interp);
  3864   3865       Sqlitetest_mutex_Init(interp);
  3865   3866       Sqlitetestschema_Init(interp);
  3866   3867       Sqlitetesttclvar_Init(interp);
         3868  +    Sqlitetestfs_Init(interp);
  3867   3869       SqlitetestThread_Init(interp);
  3868   3870       SqlitetestOnefile_Init(interp);
  3869   3871       SqlitetestOsinst_Init(interp);
  3870   3872       Sqlitetestbackup_Init(interp);
  3871   3873       Sqlitetestintarray_Init(interp);
  3872   3874       Sqlitetestvfs_Init(interp);
  3873   3875       Sqlitetestrtree_Init(interp);

Added src/test_fs.c.

            1  +/*
            2  +** 2013 Jan 11
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +** Code for testing the virtual table interfaces.  This code
           13  +** is not included in the SQLite library.  It is used for automated
           14  +** testing of the SQLite library.
           15  +**
           16  +** The FS virtual table is created as follows:
           17  +**
           18  +**   CREATE VIRTUAL TABLE tbl USING fs(idx);
           19  +**
           20  +** where idx is the name of a table in the db with 2 columns.  The virtual
           21  +** table also has two columns - file path and file contents.
           22  +**
           23  +** The first column of table idx must be an IPK, and the second contains file
           24  +** paths. For example:
           25  +**
           26  +**   CREATE TABLE idx(id INTEGER PRIMARY KEY, path TEXT);
           27  +**   INSERT INTO idx VALUES(4, '/etc/passwd');
           28  +**
           29  +** Adding the row to the idx table automatically creates a row in the 
           30  +** virtual table with rowid=4, path=/etc/passwd and a text field that 
           31  +** contains data read from file /etc/passwd on disk.
           32  +*/
           33  +#include "sqliteInt.h"
           34  +#include "tcl.h"
           35  +
           36  +#include <stdlib.h>
           37  +#include <string.h>
           38  +#include <sys/types.h>
           39  +#include <sys/stat.h>
           40  +#include <fcntl.h>
           41  +
           42  +#if SQLITE_OS_UNIX
           43  +# include <unistd.h>
           44  +#endif
           45  +#if SQLITE_OS_WIN
           46  +# include <io.h>
           47  +#endif
           48  +
           49  +#ifndef SQLITE_OMIT_VIRTUALTABLE
           50  +
           51  +typedef struct fs_vtab fs_vtab;
           52  +typedef struct fs_cursor fs_cursor;
           53  +
           54  +/* 
           55  +** A fs virtual-table object 
           56  +*/
           57  +struct fs_vtab {
           58  +  sqlite3_vtab base;
           59  +  sqlite3 *db;
           60  +  char *zDb;                      /* Name of db containing zTbl */
           61  +  char *zTbl;                     /* Name of docid->file map table */
           62  +};
           63  +
           64  +/* A fs cursor object */
           65  +struct fs_cursor {
           66  +  sqlite3_vtab_cursor base;
           67  +  sqlite3_stmt *pStmt;
           68  +  char *zBuf;
           69  +  int nBuf;
           70  +  int nAlloc;
           71  +};
           72  +
           73  +/*
           74  +** This function is the implementation of both the xConnect and xCreate
           75  +** methods of the fs virtual table.
           76  +**
           77  +** The argv[] array contains the following:
           78  +**
           79  +**   argv[0]   -> module name  ("fs")
           80  +**   argv[1]   -> database name
           81  +**   argv[2]   -> table name
           82  +**   argv[...] -> other module argument fields.
           83  +*/
           84  +static int fsConnect(
           85  +  sqlite3 *db,
           86  +  void *pAux,
           87  +  int argc, const char *const*argv,
           88  +  sqlite3_vtab **ppVtab,
           89  +  char **pzErr
           90  +){
           91  +  fs_vtab *pVtab;
           92  +  int nByte;
           93  +  const char *zTbl;
           94  +  const char *zDb = argv[1];
           95  +
           96  +  if( argc!=4 ){
           97  +    *pzErr = sqlite3_mprintf("wrong number of arguments");
           98  +    return SQLITE_ERROR;
           99  +  }
          100  +  zTbl = argv[3];
          101  +
          102  +  nByte = sizeof(fs_vtab) + strlen(zTbl) + 1 + strlen(zDb) + 1;
          103  +  pVtab = (fs_vtab *)sqlite3MallocZero( nByte );
          104  +  if( !pVtab ) return SQLITE_NOMEM;
          105  +
          106  +  pVtab->zTbl = (char *)&pVtab[1];
          107  +  pVtab->zDb = &pVtab->zTbl[strlen(zTbl)+1];
          108  +  pVtab->db = db;
          109  +  memcpy(pVtab->zTbl, zTbl, strlen(zTbl));
          110  +  memcpy(pVtab->zDb, zDb, strlen(zDb));
          111  +  *ppVtab = &pVtab->base;
          112  +  sqlite3_declare_vtab(db, "CREATE TABLE xyz(path TEXT, data TEXT)");
          113  +
          114  +  return SQLITE_OK;
          115  +}
          116  +/* Note that for this virtual table, the xCreate and xConnect
          117  +** methods are identical. */
          118  +
          119  +static int fsDisconnect(sqlite3_vtab *pVtab){
          120  +  sqlite3_free(pVtab);
          121  +  return SQLITE_OK;
          122  +}
          123  +/* The xDisconnect and xDestroy methods are also the same */
          124  +
          125  +/*
          126  +** Open a new fs cursor.
          127  +*/
          128  +static int fsOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          129  +  fs_cursor *pCur;
          130  +  pCur = sqlite3MallocZero(sizeof(fs_cursor));
          131  +  *ppCursor = &pCur->base;
          132  +  return SQLITE_OK;
          133  +}
          134  +
          135  +/*
          136  +** Close a fs cursor.
          137  +*/
          138  +static int fsClose(sqlite3_vtab_cursor *cur){
          139  +  fs_cursor *pCur = (fs_cursor *)cur;
          140  +  sqlite3_finalize(pCur->pStmt);
          141  +  sqlite3_free(pCur->zBuf);
          142  +  sqlite3_free(pCur);
          143  +  return SQLITE_OK;
          144  +}
          145  +
          146  +static int fsNext(sqlite3_vtab_cursor *cur){
          147  +  fs_cursor *pCur = (fs_cursor *)cur;
          148  +  int rc;
          149  +
          150  +  rc = sqlite3_step(pCur->pStmt);
          151  +  if( rc==SQLITE_ROW || rc==SQLITE_DONE ) rc = SQLITE_OK;
          152  +
          153  +  return rc;
          154  +}
          155  +
          156  +static int fsFilter(
          157  +  sqlite3_vtab_cursor *pVtabCursor, 
          158  +  int idxNum, const char *idxStr,
          159  +  int argc, sqlite3_value **argv
          160  +){
          161  +  int rc;
          162  +  fs_cursor *pCur = (fs_cursor *)pVtabCursor;
          163  +  fs_vtab *p = (fs_vtab *)(pVtabCursor->pVtab);
          164  +
          165  +  assert( (idxNum==0 && argc==0) || (idxNum==1 && argc==1) );
          166  +  if( idxNum==1 ){
          167  +    char *zStmt = sqlite3_mprintf(
          168  +        "SELECT * FROM %Q.%Q WHERE rowid=?", p->zDb, p->zTbl);
          169  +    if( !zStmt ) return SQLITE_NOMEM;
          170  +    rc = sqlite3_prepare_v2(p->db, zStmt, -1, &pCur->pStmt, 0);
          171  +    sqlite3_free(zStmt);
          172  +    if( rc==SQLITE_OK ){
          173  +      sqlite3_bind_value(pCur->pStmt, 1, argv[0]);
          174  +    }
          175  +  }else{
          176  +    char *zStmt = sqlite3_mprintf("SELECT * FROM %Q.%Q", p->zDb, p->zTbl);
          177  +    if( !zStmt ) return SQLITE_NOMEM;
          178  +    rc = sqlite3_prepare_v2(p->db, zStmt, -1, &pCur->pStmt, 0);
          179  +    sqlite3_free(zStmt);
          180  +  }
          181  +
          182  +  if( rc==SQLITE_OK ){
          183  +    rc = fsNext(pVtabCursor); 
          184  +  }
          185  +  return rc;
          186  +}
          187  +
          188  +static int fsColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
          189  +  fs_cursor *pCur = (fs_cursor*)cur;
          190  +
          191  +  assert( i==0 || i==1 );
          192  +  if( i==0 ){
          193  +    sqlite3_result_value(ctx, sqlite3_column_value(pCur->pStmt, 0));
          194  +  }else{
          195  +    const char *zFile = (const char *)sqlite3_column_text(pCur->pStmt, 1);
          196  +    struct stat sbuf;
          197  +    int fd;
          198  +
          199  +    fd = open(zFile, O_RDONLY);
          200  +    if( fd<0 ) return SQLITE_IOERR;
          201  +    fstat(fd, &sbuf);
          202  +
          203  +    if( sbuf.st_size>=pCur->nAlloc ){
          204  +      int nNew = sbuf.st_size*2;
          205  +      char *zNew;
          206  +      if( nNew<1024 ) nNew = 1024;
          207  +
          208  +      zNew = sqlite3Realloc(pCur->zBuf, nNew);
          209  +      if( zNew==0 ){
          210  +        close(fd);
          211  +        return SQLITE_NOMEM;
          212  +      }
          213  +      pCur->zBuf = zNew;
          214  +      pCur->nAlloc = nNew;
          215  +    }
          216  +
          217  +    read(fd, pCur->zBuf, sbuf.st_size);
          218  +    close(fd);
          219  +    pCur->nBuf = sbuf.st_size;
          220  +    pCur->zBuf[pCur->nBuf] = '\0';
          221  +
          222  +    sqlite3_result_text(ctx, pCur->zBuf, -1, SQLITE_TRANSIENT);
          223  +  }
          224  +  return SQLITE_OK;
          225  +}
          226  +
          227  +static int fsRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          228  +  fs_cursor *pCur = (fs_cursor*)cur;
          229  +  *pRowid = sqlite3_column_int64(pCur->pStmt, 0);
          230  +  return SQLITE_OK;
          231  +}
          232  +
          233  +static int fsEof(sqlite3_vtab_cursor *cur){
          234  +  fs_cursor *pCur = (fs_cursor*)cur;
          235  +  return (sqlite3_data_count(pCur->pStmt)==0);
          236  +}
          237  +
          238  +static int fsBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
          239  +  int ii;
          240  +
          241  +  for(ii=0; ii<pIdxInfo->nConstraint; ii++){
          242  +    struct sqlite3_index_constraint const *pCons = &pIdxInfo->aConstraint[ii];
          243  +    if( pCons->iColumn<0 && pCons->usable
          244  +           && pCons->op==SQLITE_INDEX_CONSTRAINT_EQ ){
          245  +      struct sqlite3_index_constraint_usage *pUsage;
          246  +      pUsage = &pIdxInfo->aConstraintUsage[ii];
          247  +      pUsage->omit = 0;
          248  +      pUsage->argvIndex = 1;
          249  +      pIdxInfo->idxNum = 1;
          250  +      pIdxInfo->estimatedCost = 1.0;
          251  +      break;
          252  +    }
          253  +  }
          254  +
          255  +  return SQLITE_OK;
          256  +}
          257  +
          258  +/*
          259  +** A virtual table module that provides read-only access to a
          260  +** Tcl global variable namespace.
          261  +*/
          262  +static sqlite3_module fsModule = {
          263  +  0,                         /* iVersion */
          264  +  fsConnect,
          265  +  fsConnect,
          266  +  fsBestIndex,
          267  +  fsDisconnect, 
          268  +  fsDisconnect,
          269  +  fsOpen,                      /* xOpen - open a cursor */
          270  +  fsClose,                     /* xClose - close a cursor */
          271  +  fsFilter,                    /* xFilter - configure scan constraints */
          272  +  fsNext,                      /* xNext - advance a cursor */
          273  +  fsEof,                       /* xEof - check for end of scan */
          274  +  fsColumn,                    /* xColumn - read data */
          275  +  fsRowid,                     /* xRowid - read data */
          276  +  0,                           /* xUpdate */
          277  +  0,                           /* xBegin */
          278  +  0,                           /* xSync */
          279  +  0,                           /* xCommit */
          280  +  0,                           /* xRollback */
          281  +  0,                           /* xFindMethod */
          282  +  0,                           /* xRename */
          283  +};
          284  +
          285  +/*
          286  +** Decode a pointer to an sqlite3 object.
          287  +*/
          288  +extern int getDbPointer(Tcl_Interp *interp, const char *zA, sqlite3 **ppDb);
          289  +
          290  +/*
          291  +** Register the echo virtual table module.
          292  +*/
          293  +static int register_fs_module(
          294  +  ClientData clientData, /* Pointer to sqlite3_enable_XXX function */
          295  +  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
          296  +  int objc,              /* Number of arguments */
          297  +  Tcl_Obj *CONST objv[]  /* Command arguments */
          298  +){
          299  +  sqlite3 *db;
          300  +  if( objc!=2 ){
          301  +    Tcl_WrongNumArgs(interp, 1, objv, "DB");
          302  +    return TCL_ERROR;
          303  +  }
          304  +  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
          305  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          306  +  sqlite3_create_module(db, "fs", &fsModule, (void *)interp);
          307  +#endif
          308  +  return TCL_OK;
          309  +}
          310  +
          311  +#endif
          312  +
          313  +
          314  +/*
          315  +** Register commands with the TCL interpreter.
          316  +*/
          317  +int Sqlitetestfs_Init(Tcl_Interp *interp){
          318  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          319  +  static struct {
          320  +     char *zName;
          321  +     Tcl_ObjCmdProc *xProc;
          322  +     void *clientData;
          323  +  } aObjCmd[] = {
          324  +     { "register_fs_module",   register_fs_module, 0 },
          325  +  };
          326  +  int i;
          327  +  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
          328  +    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 
          329  +        aObjCmd[i].xProc, aObjCmd[i].clientData, 0);
          330  +  }
          331  +#endif
          332  +  return TCL_OK;
          333  +}

Changes to src/test_regexp.c.

    22     22   **     X|Y     X or Y
    23     23   **     ^X      X occurring at the beginning of the string
    24     24   **     X$      X occurring at the end of the string
    25     25   **     .       Match any single character
    26     26   **     \c      Character c where c is one of \{}()[]|*+?.
    27     27   **     \c      C-language escapes for c in afnrtv.  ex: \t or \n
    28     28   **     \uXXXX  Where XXXX is exactly 4 hex digits, unicode value XXXX
    29         -**     \xXXX   Where XXX is any number of hex digits, unicode value XXX
           29  +**     \xXX    Where XX is exactly 2 hex digits, unicode value XX
    30     30   **     [abc]   Any single character from the set abc
    31     31   **     [^abc]  Any single character not in the set abc
    32     32   **     [a-z]   Any single character in the range a-z
    33     33   **     [^a-z]  Any single character not in the range a-z
    34     34   **     \b      Word boundary
    35     35   **     \w      Word character.  [A-Za-z0-9_]
    36     36   **     \W      Non-word character
................................................................................
   374    374       return 0;
   375    375     }
   376    376     *pV = (*pV)*16 + (c & 0xff);
   377    377     return 1;
   378    378   }
   379    379   
   380    380   /* A backslash character has been seen, read the next character and
   381         -** return its intepretation.
          381  +** return its interpretation.
   382    382   */
   383    383   static unsigned re_esc_char(ReCompiled *p){
   384    384     static const char zEsc[] = "afnrtv\\()*.+?[$^{|}]";
   385    385     static const char zTrans[] = "\a\f\n\r\t\v";
   386    386     int i, v = 0;
   387    387     char c;
   388    388     if( p->sIn.i>=p->sIn.mx ) return 0;
   389    389     c = p->sIn.z[p->sIn.i];
   390         -  if( c=='u' && p->sIn.i+5<p->sIn.mx ){
          390  +  if( c=='u' && p->sIn.i+4<p->sIn.mx ){
   391    391       const unsigned char *zIn = p->sIn.z + p->sIn.i;
   392         -    v = 0;
   393    392       if( re_hex(zIn[1],&v)
   394    393        && re_hex(zIn[2],&v)
   395    394        && re_hex(zIn[3],&v)
   396    395        && re_hex(zIn[4],&v)
   397    396       ){
   398    397         p->sIn.i += 5;
   399    398         return v;
   400    399       }
   401    400     }
   402         -  if( c=='x' ){
   403         -    v = 0;
   404         -    for(i=1; p->sIn.i<p->sIn.mx && re_hex(p->sIn.z[p->sIn.i+i], &v); i++){}
   405         -    if( i>1 ){
   406         -      p->sIn.i += i;
          401  +  if( c=='x' && p->sIn.i+2<p->sIn.mx ){
          402  +    const unsigned char *zIn = p->sIn.z + p->sIn.i;
          403  +    if( re_hex(zIn[1],&v)
          404  +     && re_hex(zIn[2],&v)
          405  +    ){
          406  +      p->sIn.i += 3;
   407    407         return v;
   408    408       }
   409    409     }
   410    410     for(i=0; zEsc[i] && zEsc[i]!=c; i++){}
   411    411     if( zEsc[i] ){
   412    412       if( i<6 ) c = zTrans[i];
   413    413       p->sIn.i++;

Changes to src/vdbeaux.c.

  2475   2475     for(i=p->nzVar-1; i>=0; i--) sqlite3DbFree(db, p->azVar[i]);
  2476   2476     vdbeFreeOpArray(db, p->aOp, p->nOp);
  2477   2477     sqlite3DbFree(db, p->aLabel);
  2478   2478     sqlite3DbFree(db, p->aColName);
  2479   2479     sqlite3DbFree(db, p->zSql);
  2480   2480     sqlite3DbFree(db, p->pFree);
  2481   2481   #if defined(SQLITE_ENABLE_TREE_EXPLAIN)
  2482         -  sqlite3_free(p->zExplain);
         2482  +  sqlite3DbFree(db, p->zExplain);
  2483   2483     sqlite3DbFree(db, p->pExplain);
  2484   2484   #endif
  2485   2485   }
  2486   2486   
  2487   2487   /*
  2488   2488   ** Delete an entire VDBE.
  2489   2489   */

Changes to src/where.c.

    94     94   typedef struct WhereTerm WhereTerm;
    95     95   struct WhereTerm {
    96     96     Expr *pExpr;            /* Pointer to the subexpression that is this term */
    97     97     int iParent;            /* Disable pWC->a[iParent] when this term disabled */
    98     98     int leftCursor;         /* Cursor number of X in "X <op> <expr>" */
    99     99     union {
   100    100       int leftColumn;         /* Column number of X in "X <op> <expr>" */
   101         -    WhereOrInfo *pOrInfo;   /* Extra information if eOperator==WO_OR */
   102         -    WhereAndInfo *pAndInfo; /* Extra information if eOperator==WO_AND */
          101  +    WhereOrInfo *pOrInfo;   /* Extra information if (eOperator & WO_OR)!=0 */
          102  +    WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */
   103    103     } u;
   104    104     u16 eOperator;          /* A WO_xx value describing <op> */
   105    105     u8 wtFlags;             /* TERM_xxx bit flags.  See below */
   106    106     u8 nChild;              /* Number of children that must disable us */
   107    107     WhereClause *pWC;       /* The clause this term is part of */
   108    108     Bitmask prereqRight;    /* Bitmask of tables used by pExpr->pRight */
   109    109     Bitmask prereqAll;      /* Bitmask of tables referenced by pExpr */
................................................................................
   223    223   #define WO_LE     (WO_EQ<<(TK_LE-TK_EQ))
   224    224   #define WO_GT     (WO_EQ<<(TK_GT-TK_EQ))
   225    225   #define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
   226    226   #define WO_MATCH  0x040
   227    227   #define WO_ISNULL 0x080
   228    228   #define WO_OR     0x100       /* Two or more OR-connected terms */
   229    229   #define WO_AND    0x200       /* Two or more AND-connected terms */
          230  +#define WO_EQUIV  0x400       /* Of the form A==B, both columns */
   230    231   #define WO_NOOP   0x800       /* This term does not restrict search space */
   231    232   
   232    233   #define WO_ALL    0xfff       /* Mask of all possible WO_* values */
   233    234   #define WO_SINGLE 0x0ff       /* Mask of all non-compound WO_* values */
   234    235   
   235    236   /*
   236    237   ** Value for wsFlags returned by bestIndex() and stored in
................................................................................
   625    626   }
   626    627   
   627    628   /*
   628    629   ** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
   629    630   ** where X is a reference to the iColumn of table iCur and <op> is one of
   630    631   ** the WO_xx operator codes specified by the op parameter.
   631    632   ** Return a pointer to the term.  Return 0 if not found.
          633  +**
          634  +** The term returned might by Y=<expr> if there is another constraint in
          635  +** the WHERE clause that specifies that X=Y.  Any such constraints will be
          636  +** identified by the WO_EQUIV bit in the pTerm->eOperator field.  The
          637  +** aEquiv[] array holds X and all its equivalents, with each SQL variable
          638  +** taking up two slots in aEquiv[].  The first slot is for the cursor number
          639  +** and the second is for the column number.  There are 22 slots in aEquiv[]
          640  +** so that means we can look for X plus up to 10 other equivalent values.
          641  +** Hence a search for X will return <expr> if X=A1 and A1=A2 and A2=A3
          642  +** and ... and A9=A10 and A10=<expr>.
          643  +**
          644  +** If there are multiple terms in the WHERE clause of the form "X <op> <expr>"
          645  +** then try for the one with no dependencies on <expr> - in other words where
          646  +** <expr> is a constant expression of some kind.  Only return entries of
          647  +** the form "X <op> Y" where Y is a column in another table if no terms of
          648  +** the form "X <op> <const-expr>" exist.  Other than this priority, if there
          649  +** are two or more terms that match, then the choice of which term to return
          650  +** is arbitrary.
   632    651   */
   633    652   static WhereTerm *findTerm(
   634    653     WhereClause *pWC,     /* The WHERE clause to be searched */
   635    654     int iCur,             /* Cursor number of LHS */
   636    655     int iColumn,          /* Column number of LHS */
   637    656     Bitmask notReady,     /* RHS must not overlap with this mask */
   638    657     u32 op,               /* Mask of WO_xx values describing operator */
   639    658     Index *pIdx           /* Must be compatible with this index, if not NULL */
   640    659   ){
   641         -  WhereTerm *pTerm;
   642         -  int k;
          660  +  WhereTerm *pTerm;            /* Term being examined as possible result */
          661  +  WhereTerm *pResult = 0;      /* The answer to return */
          662  +  WhereClause *pWCOrig = pWC;  /* Original pWC value */
          663  +  int j, k;                    /* Loop counters */
          664  +  Expr *pX;                /* Pointer to an expression */
          665  +  Parse *pParse;           /* Parsing context */
          666  +  int iOrigCol = iColumn;  /* Original value of iColumn */
          667  +  int nEquiv = 2;          /* Number of entires in aEquiv[] */
          668  +  int iEquiv = 2;          /* Number of entries of aEquiv[] processed so far */
          669  +  int aEquiv[22];          /* iCur,iColumn and up to 10 other equivalents */
          670  +
   643    671     assert( iCur>=0 );
   644         -  op &= WO_ALL;
   645         -  for(; pWC; pWC=pWC->pOuter){
   646         -    for(pTerm=pWC->a, k=pWC->nTerm; k; k--, pTerm++){
   647         -      if( pTerm->leftCursor==iCur
   648         -         && (pTerm->prereqRight & notReady)==0
   649         -         && pTerm->u.leftColumn==iColumn
   650         -         && (pTerm->eOperator & op)!=0
   651         -      ){
   652         -        if( iColumn>=0 && pIdx && pTerm->eOperator!=WO_ISNULL ){
   653         -          Expr *pX = pTerm->pExpr;
   654         -          CollSeq *pColl;
   655         -          char idxaff;
   656         -          int j;
   657         -          Parse *pParse = pWC->pParse;
   658         -  
   659         -          idxaff = pIdx->pTable->aCol[iColumn].affinity;
   660         -          if( !sqlite3IndexAffinityOk(pX, idxaff) ) continue;
   661         -  
   662         -          /* Figure out the collation sequence required from an index for
   663         -          ** it to be useful for optimising expression pX. Store this
   664         -          ** value in variable pColl.
   665         -          */
   666         -          assert(pX->pLeft);
   667         -          pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
   668         -          if( pColl==0 ) pColl = pParse->db->pDfltColl;
   669         -  
   670         -          for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
   671         -            if( NEVER(j>=pIdx->nColumn) ) return 0;
          672  +  aEquiv[0] = iCur;
          673  +  aEquiv[1] = iColumn;
          674  +  for(;;){
          675  +    for(pWC=pWCOrig; pWC; pWC=pWC->pOuter){
          676  +      for(pTerm=pWC->a, k=pWC->nTerm; k; k--, pTerm++){
          677  +        if( pTerm->leftCursor==iCur
          678  +          && pTerm->u.leftColumn==iColumn
          679  +        ){
          680  +          if( (pTerm->prereqRight & notReady)==0
          681  +           && (pTerm->eOperator & op & WO_ALL)!=0
          682  +          ){
          683  +            if( iOrigCol>=0 && pIdx && (pTerm->eOperator & WO_ISNULL)==0 ){
          684  +              CollSeq *pColl;
          685  +              char idxaff;
          686  +      
          687  +              pX = pTerm->pExpr;
          688  +              pParse = pWC->pParse;
          689  +              idxaff = pIdx->pTable->aCol[iOrigCol].affinity;
          690  +              if( !sqlite3IndexAffinityOk(pX, idxaff) ){
          691  +                continue;
          692  +              }
          693  +      
          694  +              /* Figure out the collation sequence required from an index for
          695  +              ** it to be useful for optimising expression pX. Store this
          696  +              ** value in variable pColl.
          697  +              */
          698  +              assert(pX->pLeft);
          699  +              pColl = sqlite3BinaryCompareCollSeq(pParse,pX->pLeft,pX->pRight);
          700  +              if( pColl==0 ) pColl = pParse->db->pDfltColl;
          701  +      
          702  +              for(j=0; pIdx->aiColumn[j]!=iOrigCol; j++){
          703  +                if( NEVER(j>=pIdx->nColumn) ) return 0;
          704  +              }
          705  +              if( sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ){
          706  +                continue;
          707  +              }
          708  +            }
          709  +            pResult = pTerm;
          710  +            if( pTerm->prereqRight==0 ) goto findTerm_success;
          711  +          }
          712  +          if( (pTerm->eOperator & WO_EQUIV)!=0
          713  +           && nEquiv<ArraySize(aEquiv)
          714  +          ){
          715  +            pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight);
          716  +            assert( pX->op==TK_COLUMN );
          717  +            for(j=0; j<nEquiv; j+=2){
          718  +              if( aEquiv[j]==pX->iTable && aEquiv[j+1]==pX->iColumn ) break;
          719  +            }
          720  +            if( j==nEquiv ){
          721  +              aEquiv[j] = pX->iTable;
          722  +              aEquiv[j+1] = pX->iColumn;
          723  +              nEquiv += 2;
          724  +            }
   672    725             }
   673         -          if( sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ) continue;
   674    726           }
   675         -        return pTerm;
   676    727         }
   677    728       }
          729  +    if( iEquiv>=nEquiv ) break;
          730  +    iCur = aEquiv[iEquiv++];
          731  +    iColumn = aEquiv[iEquiv++];
   678    732     }
   679         -  return 0;
          733  +findTerm_success:
          734  +  return pResult;
   680    735   }
   681    736   
   682    737   /* Forward reference */
   683    738   static void exprAnalyze(SrcList*, WhereClause*, int);
   684    739   
   685    740   /*
   686    741   ** Call exprAnalyze on all terms in a WHERE clause.  
................................................................................
   950   1005     ** Compute the set of tables that might satisfy cases 1 or 2.
   951   1006     */
   952   1007     indexable = ~(Bitmask)0;
   953   1008     chngToIN = ~(pWC->vmask);
   954   1009     for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0 && indexable; i--, pOrTerm++){
   955   1010       if( (pOrTerm->eOperator & WO_SINGLE)==0 ){
   956   1011         WhereAndInfo *pAndInfo;
   957         -      assert( pOrTerm->eOperator==0 );
   958   1012         assert( (pOrTerm->wtFlags & (TERM_ANDINFO|TERM_ORINFO))==0 );
   959   1013         chngToIN = 0;
   960   1014         pAndInfo = sqlite3DbMallocRaw(db, sizeof(*pAndInfo));
   961   1015         if( pAndInfo ){
   962   1016           WhereClause *pAndWC;
   963   1017           WhereTerm *pAndTerm;
   964   1018           int j;
................................................................................
   989   1043         Bitmask b;
   990   1044         b = getMask(pMaskSet, pOrTerm->leftCursor);
   991   1045         if( pOrTerm->wtFlags & TERM_VIRTUAL ){
   992   1046           WhereTerm *pOther = &pOrWc->a[pOrTerm->iParent];
   993   1047           b |= getMask(pMaskSet, pOther->leftCursor);
   994   1048         }
   995   1049         indexable &= b;
   996         -      if( pOrTerm->eOperator!=WO_EQ ){
         1050  +      if( (pOrTerm->eOperator & WO_EQ)==0 ){
   997   1051           chngToIN = 0;
   998   1052         }else{
   999   1053           chngToIN &= b;
  1000   1054         }
  1001   1055       }
  1002   1056     }
  1003   1057   
................................................................................
  1040   1094       ** will be recorded in iCursor and iColumn.  There might not be any
  1041   1095       ** such table and column.  Set okToChngToIN if an appropriate table
  1042   1096       ** and column is found but leave okToChngToIN false if not found.
  1043   1097       */
  1044   1098       for(j=0; j<2 && !okToChngToIN; j++){
  1045   1099         pOrTerm = pOrWc->a;
  1046   1100         for(i=pOrWc->nTerm-1; i>=0; i--, pOrTerm++){
  1047         -        assert( pOrTerm->eOperator==WO_EQ );
         1101  +        assert( pOrTerm->eOperator & WO_EQ );
  1048   1102           pOrTerm->wtFlags &= ~TERM_OR_OK;
  1049   1103           if( pOrTerm->leftCursor==iCursor ){
  1050   1104             /* This is the 2-bit case and we are on the second iteration and
  1051   1105             ** current term is from the first iteration.  So skip this term. */
  1052   1106             assert( j==1 );
  1053   1107             continue;
  1054   1108           }
................................................................................
  1066   1120           iCursor = pOrTerm->leftCursor;
  1067   1121           break;
  1068   1122         }
  1069   1123         if( i<0 ){
  1070   1124           /* No candidate table+column was found.  This can only occur
  1071   1125           ** on the second iteration */
  1072   1126           assert( j==1 );
  1073         -        assert( (chngToIN&(chngToIN-1))==0 );
         1127  +        assert( IsPowerOfTwo(chngToIN) );
  1074   1128           assert( chngToIN==getMask(pMaskSet, iCursor) );
  1075   1129           break;
  1076   1130         }
  1077   1131         testcase( j==1 );
  1078   1132   
  1079   1133         /* We have found a candidate table and column.  Check to see if that
  1080   1134         ** table and column is common to every term in the OR clause */
  1081   1135         okToChngToIN = 1;
  1082   1136         for(; i>=0 && okToChngToIN; i--, pOrTerm++){
  1083         -        assert( pOrTerm->eOperator==WO_EQ );
         1137  +        assert( pOrTerm->eOperator & WO_EQ );
  1084   1138           if( pOrTerm->leftCursor!=iCursor ){
  1085   1139             pOrTerm->wtFlags &= ~TERM_OR_OK;
  1086   1140           }else if( pOrTerm->u.leftColumn!=iColumn ){
  1087   1141             okToChngToIN = 0;
  1088   1142           }else{
  1089   1143             int affLeft, affRight;
  1090   1144             /* If the right-hand side is also a column, then the affinities
................................................................................
  1112   1166         Expr *pDup;            /* A transient duplicate expression */
  1113   1167         ExprList *pList = 0;   /* The RHS of the IN operator */
  1114   1168         Expr *pLeft = 0;       /* The LHS of the IN operator */
  1115   1169         Expr *pNew;            /* The complete IN operator */
  1116   1170   
  1117   1171         for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0; i--, pOrTerm++){
  1118   1172           if( (pOrTerm->wtFlags & TERM_OR_OK)==0 ) continue;
  1119         -        assert( pOrTerm->eOperator==WO_EQ );
         1173  +        assert( pOrTerm->eOperator & WO_EQ );
  1120   1174           assert( pOrTerm->leftCursor==iCursor );
  1121   1175           assert( pOrTerm->u.leftColumn==iColumn );
  1122   1176           pDup = sqlite3ExprDup(db, pOrTerm->pExpr->pRight, 0);
  1123   1177           pList = sqlite3ExprListAppend(pWC->pParse, pList, pDup);
  1124   1178           pLeft = pOrTerm->pExpr->pLeft;
  1125   1179         }
  1126   1180         assert( pLeft!=0 );
................................................................................
  1141   1195           sqlite3ExprListDelete(db, pList);
  1142   1196         }
  1143   1197         pTerm->eOperator = WO_NOOP;  /* case 1 trumps case 2 */
  1144   1198       }
  1145   1199     }
  1146   1200   }
  1147   1201   #endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */
  1148         -
  1149   1202   
  1150   1203   /*
  1151   1204   ** The input to this routine is an WhereTerm structure with only the
  1152   1205   ** "pExpr" field filled in.  The job of this routine is to analyze the
  1153   1206   ** subexpression and populate all the other fields of the WhereTerm
  1154   1207   ** structure.
  1155   1208   **
................................................................................
  1211   1264       extraRight = x-1;  /* ON clause terms may not be used with an index
  1212   1265                          ** on left table of a LEFT JOIN.  Ticket #3015 */
  1213   1266     }
  1214   1267     pTerm->prereqAll = prereqAll;
  1215   1268     pTerm->leftCursor = -1;
  1216   1269     pTerm->iParent = -1;
  1217   1270     pTerm->eOperator = 0;
  1218         -  if( allowedOp(op) && (pTerm->prereqRight & prereqLeft)==0 ){
         1271  +  if( allowedOp(op) ){
  1219   1272       Expr *pLeft = sqlite3ExprSkipCollate(pExpr->pLeft);
  1220   1273       Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight);
         1274  +    u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV;
  1221   1275       if( pLeft->op==TK_COLUMN ){
  1222   1276         pTerm->leftCursor = pLeft->iTable;
  1223   1277         pTerm->u.leftColumn = pLeft->iColumn;
  1224         -      pTerm->eOperator = operatorMask(op);
         1278  +      pTerm->eOperator = operatorMask(op) & opMask;
  1225   1279       }
  1226   1280       if( pRight && pRight->op==TK_COLUMN ){
  1227   1281         WhereTerm *pNew;
  1228   1282         Expr *pDup;
         1283  +      u16 eExtraOp = 0;        /* Extra bits for pNew->eOperator */
  1229   1284         if( pTerm->leftCursor>=0 ){
  1230   1285           int idxNew;
  1231   1286           pDup = sqlite3ExprDup(db, pExpr, 0);
  1232   1287           if( db->mallocFailed ){
  1233   1288             sqlite3ExprDelete(db, pDup);
  1234   1289             return;
  1235   1290           }
................................................................................
  1236   1291           idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
  1237   1292           if( idxNew==0 ) return;
  1238   1293           pNew = &pWC->a[idxNew];
  1239   1294           pNew->iParent = idxTerm;
  1240   1295           pTerm = &pWC->a[idxTerm];
  1241   1296           pTerm->nChild = 1;
  1242   1297           pTerm->wtFlags |= TERM_COPIED;
         1298  +        if( pExpr->op==TK_EQ
         1299  +         && !ExprHasProperty(pExpr, EP_FromJoin)
         1300  +         && OptimizationEnabled(db, SQLITE_Transitive)
         1301  +        ){
         1302  +          pTerm->eOperator |= WO_EQUIV;
         1303  +          eExtraOp = WO_EQUIV;
         1304  +        }
  1243   1305         }else{
  1244   1306           pDup = pExpr;
  1245   1307           pNew = pTerm;
  1246   1308         }
  1247   1309         exprCommute(pParse, pDup);
  1248   1310         pLeft = sqlite3ExprSkipCollate(pDup->pLeft);
  1249   1311         pNew->leftCursor = pLeft->iTable;
  1250   1312         pNew->u.leftColumn = pLeft->iColumn;
  1251   1313         testcase( (prereqLeft | extraRight) != prereqLeft );
  1252   1314         pNew->prereqRight = prereqLeft | extraRight;
  1253   1315         pNew->prereqAll = prereqAll;
  1254         -      pNew->eOperator = operatorMask(pDup->op);
         1316  +      pNew->eOperator = (operatorMask(pDup->op) + eExtraOp) & opMask;
  1255   1317       }
  1256   1318     }
  1257   1319   
  1258   1320   #ifndef SQLITE_OMIT_BETWEEN_OPTIMIZATION
  1259   1321     /* If a term is the BETWEEN operator, create two new virtual terms
  1260   1322     ** that define the range that the BETWEEN implements.  For example:
  1261   1323     **
................................................................................
  1706   1768     }
  1707   1769     if( pWC->wctrlFlags & WHERE_AND_ONLY ){
  1708   1770       return;
  1709   1771     }
  1710   1772   
  1711   1773     /* Search the WHERE clause terms for a usable WO_OR term. */
  1712   1774     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1713         -    if( pTerm->eOperator==WO_OR 
         1775  +    if( (pTerm->eOperator & WO_OR)!=0
  1714   1776        && ((pTerm->prereqAll & ~maskSrc) & p->notReady)==0
  1715   1777        && (pTerm->u.pOrInfo->indexable & maskSrc)!=0 
  1716   1778       ){
  1717   1779         WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc;
  1718   1780         WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm];
  1719   1781         WhereTerm *pOrTerm;
  1720   1782         int flags = WHERE_MULTI_OR;
................................................................................
  1727   1789         sBOI.pOrderBy = 0;
  1728   1790         sBOI.pDistinct = 0;
  1729   1791         sBOI.ppIdxInfo = 0;
  1730   1792         for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){
  1731   1793           WHERETRACE(("... Multi-index OR testing for term %d of %d....\n", 
  1732   1794             (pOrTerm - pOrWC->a), (pTerm - pWC->a)
  1733   1795           ));
  1734         -        if( pOrTerm->eOperator==WO_AND ){
         1796  +        if( (pOrTerm->eOperator& WO_AND)!=0 ){
  1735   1797             sBOI.pWC = &pOrTerm->u.pAndInfo->wc;
  1736   1798             bestIndex(&sBOI);
  1737   1799           }else if( pOrTerm->leftCursor==iCur ){
  1738   1800             WhereClause tempWC;
  1739   1801             tempWC.pParse = pWC->pParse;
  1740   1802             tempWC.pMaskSet = pWC->pMaskSet;
  1741   1803             tempWC.pOuter = pWC;
................................................................................
  1788   1850   static int termCanDriveIndex(
  1789   1851     WhereTerm *pTerm,              /* WHERE clause term to check */
  1790   1852     struct SrcList_item *pSrc,     /* Table we are trying to access */
  1791   1853     Bitmask notReady               /* Tables in outer loops of the join */
  1792   1854   ){
  1793   1855     char aff;
  1794   1856     if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
  1795         -  if( pTerm->eOperator!=WO_EQ ) return 0;
         1857  +  if( (pTerm->eOperator & WO_EQ)==0 ) return 0;
  1796   1858     if( (pTerm->prereqRight & notReady)!=0 ) return 0;
  1797   1859     aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
  1798   1860     if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
  1799   1861     return 1;
  1800   1862   }
  1801   1863   #endif
  1802   1864   
................................................................................
  2050   2112   
  2051   2113     WHERETRACE(("Recomputing index info for %s...\n", pSrc->pTab->zName));
  2052   2114   
  2053   2115     /* Count the number of possible WHERE clause constraints referring
  2054   2116     ** to this virtual table */
  2055   2117     for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
  2056   2118       if( pTerm->leftCursor != pSrc->iCursor ) continue;
  2057         -    assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 );
  2058         -    testcase( pTerm->eOperator==WO_IN );
  2059         -    testcase( pTerm->eOperator==WO_ISNULL );
         2119  +    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
         2120  +    testcase( pTerm->eOperator & WO_IN );
         2121  +    testcase( pTerm->eOperator & WO_ISNULL );
  2060   2122       if( pTerm->eOperator & (WO_ISNULL) ) continue;
  2061   2123       if( pTerm->wtFlags & TERM_VNULL ) continue;
  2062   2124       nTerm++;
  2063   2125     }
  2064   2126   
  2065   2127     /* If the ORDER BY clause contains only columns in the current 
  2066   2128     ** virtual table then allocate space for the aOrderBy part of
................................................................................
  2103   2165     *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
  2104   2166     *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
  2105   2167                                                                      pUsage;
  2106   2168   
  2107   2169     for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
  2108   2170       u8 op;
  2109   2171       if( pTerm->leftCursor != pSrc->iCursor ) continue;
  2110         -    assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 );
  2111         -    testcase( pTerm->eOperator==WO_IN );
  2112         -    testcase( pTerm->eOperator==WO_ISNULL );
         2172  +    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
         2173  +    testcase( pTerm->eOperator & WO_IN );
         2174  +    testcase( pTerm->eOperator & WO_ISNULL );
  2113   2175       if( pTerm->eOperator & (WO_ISNULL) ) continue;
  2114   2176       if( pTerm->wtFlags & TERM_VNULL ) continue;
  2115   2177       pIdxCons[j].iColumn = pTerm->u.leftColumn;
  2116   2178       pIdxCons[j].iTermOffset = i;
  2117         -    op = (u8)pTerm->eOperator;
         2179  +    op = (u8)pTerm->eOperator & WO_ALL;
  2118   2180       if( op==WO_IN ) op = WO_EQ;
  2119   2181       pIdxCons[j].op = op;
  2120   2182       /* The direct assignment in the previous line is possible only because
  2121   2183       ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
  2122   2184       ** following asserts verify this fact. */
  2123   2185       assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
  2124   2186       assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
................................................................................
  2280   2342       */
  2281   2343       pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  2282   2344       pUsage = pIdxInfo->aConstraintUsage;
  2283   2345       for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
  2284   2346         j = pIdxCons->iTermOffset;
  2285   2347         pTerm = &pWC->a[j];
  2286   2348         if( (pTerm->prereqRight&p->notReady)==0
  2287         -       && (bAllowIN || pTerm->eOperator!=WO_IN)
         2349  +       && (bAllowIN || (pTerm->eOperator & WO_IN)==0)
  2288   2350         ){
  2289   2351           pIdxCons->usable = 1;
  2290   2352         }else{
  2291   2353           pIdxCons->usable = 0;
  2292   2354         }
  2293   2355       }
  2294   2356       memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint);
................................................................................
  2312   2374     
  2313   2375       pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  2314   2376       for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
  2315   2377         if( pUsage[i].argvIndex>0 ){
  2316   2378           j = pIdxCons->iTermOffset;
  2317   2379           pTerm = &pWC->a[j];
  2318   2380           p->cost.used |= pTerm->prereqRight;
  2319         -        if( pTerm->eOperator==WO_IN && pUsage[i].omit==0 ){
         2381  +        if( (pTerm->eOperator & WO_IN)!=0 && pUsage[i].omit==0 ){
  2320   2382             /* Do not attempt to use an IN constraint if the virtual table
  2321   2383             ** says that the equivalent EQ constraint cannot be safely omitted.
  2322   2384             ** If we do attempt to use such a constraint, some rows might be
  2323   2385             ** repeated in the output. */
  2324   2386             break;
  2325   2387           }
  2326   2388         }
................................................................................
  2618   2680       tRowcnt iUpper = p->aiRowEst[0];
  2619   2681       tRowcnt a[2];
  2620   2682       u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;
  2621   2683   
  2622   2684       if( pLower ){
  2623   2685         Expr *pExpr = pLower->pExpr->pRight;
  2624   2686         rc = valueFromExpr(pParse, pExpr, aff, &pRangeVal);
  2625         -      assert( pLower->eOperator==WO_GT || pLower->eOperator==WO_GE );
         2687  +      assert( (pLower->eOperator & (WO_GT|WO_GE))!=0 );
  2626   2688         if( rc==SQLITE_OK
  2627   2689          && whereKeyStats(pParse, p, pRangeVal, 0, a)==SQLITE_OK
  2628   2690         ){
  2629   2691           iLower = a[0];
  2630         -        if( pLower->eOperator==WO_GT ) iLower += a[1];
         2692  +        if( (pLower->eOperator & WO_GT)!=0 ) iLower += a[1];
  2631   2693         }
  2632   2694         sqlite3ValueFree(pRangeVal);
  2633   2695       }
  2634   2696       if( rc==SQLITE_OK && pUpper ){
  2635   2697         Expr *pExpr = pUpper->pExpr->pRight;
  2636   2698         rc = valueFromExpr(pParse, pExpr, aff, &pRangeVal);
  2637         -      assert( pUpper->eOperator==WO_LT || pUpper->eOperator==WO_LE );
         2699  +      assert( (pUpper->eOperator & (WO_LT|WO_LE))!=0 );
  2638   2700         if( rc==SQLITE_OK
  2639   2701          && whereKeyStats(pParse, p, pRangeVal, 1, a)==SQLITE_OK
  2640   2702         ){
  2641   2703           iUpper = a[0];
  2642         -        if( pUpper->eOperator==WO_LE ) iUpper += a[1];
         2704  +        if( (pUpper->eOperator & WO_LE)!=0 ) iUpper += a[1];
  2643   2705         }
  2644   2706         sqlite3ValueFree(pRangeVal);
  2645   2707       }
  2646   2708       if( rc==SQLITE_OK ){
  2647   2709         if( iUpper<=iLower ){
  2648   2710           *pRangeDiv = (double)p->aiRowEst[0];
  2649   2711         }else{
................................................................................
  2943   3005   
  2944   3006       /* If X is the column in the index and ORDER BY clause, check to see
  2945   3007       ** if there are any X= or X IS NULL constraints in the WHERE clause. */
  2946   3008       pConstraint = findTerm(p->pWC, base, iColumn, p->notReady,
  2947   3009                              WO_EQ|WO_ISNULL|WO_IN, pIdx);
  2948   3010       if( pConstraint==0 ){
  2949   3011         isEq = 0;
  2950         -    }else if( pConstraint->eOperator==WO_IN ){
         3012  +    }else if( (pConstraint->eOperator & WO_IN)!=0 ){
  2951   3013         /* Constraints of the form: "X IN ..." cannot be used with an ORDER BY
  2952   3014         ** because we do not know in what order the values on the RHS of the IN
  2953   3015         ** operator will occur. */
  2954   3016         break;
  2955         -    }else if( pConstraint->eOperator==WO_ISNULL ){
         3017  +    }else if( (pConstraint->eOperator & WO_ISNULL)!=0 ){
  2956   3018         uniqueNotNull = 0;
  2957   3019         isEq = 1;  /* "X IS NULL" means X has only a single value */
  2958   3020       }else if( pConstraint->prereqRight==0 ){
  2959   3021         isEq = 1;  /* Constraint "X=constant" means X has only a single value */
  2960   3022       }else{
  2961   3023         Expr *pRight = pConstraint->pExpr->pRight;
  2962   3024         if( pRight->op==TK_COLUMN ){
................................................................................
  3361   3423       ** to get a better estimate on the number of rows based on
  3362   3424       ** VALUE and how common that value is according to the histogram.
  3363   3425       */
  3364   3426       if( pc.plan.nRow>(double)1 && pc.plan.nEq==1
  3365   3427        && pFirstTerm!=0 && aiRowEst[1]>1 ){
  3366   3428         assert( (pFirstTerm->eOperator & (WO_EQ|WO_ISNULL|WO_IN))!=0 );
  3367   3429         if( pFirstTerm->eOperator & (WO_EQ|WO_ISNULL) ){
  3368         -        testcase( pFirstTerm->eOperator==WO_EQ );
  3369         -        testcase( pFirstTerm->eOperator==WO_ISNULL );
         3430  +        testcase( pFirstTerm->eOperator & WO_EQ );
         3431  +        testcase( pFirstTerm->eOperator & WO_EQUIV );
         3432  +        testcase( pFirstTerm->eOperator & WO_ISNULL );
  3370   3433           whereEqualScanEst(pParse, pProbe, pFirstTerm->pExpr->pRight,
  3371   3434                             &pc.plan.nRow);
  3372   3435         }else if( bInEst==0 ){
  3373         -        assert( pFirstTerm->eOperator==WO_IN );
         3436  +        assert( pFirstTerm->eOperator & WO_IN );
  3374   3437           whereInScanEst(pParse, pProbe, pFirstTerm->pExpr->x.pList,
  3375   3438                          &pc.plan.nRow);
  3376   3439         }
  3377   3440       }
  3378   3441   #endif /* SQLITE_ENABLE_STAT3 */
  3379   3442   
  3380   3443       /* Adjust the number of output rows and downward to reflect rows
................................................................................
  3513   3576               ** set size by a factor of 3.  Indexed range constraints reduce
  3514   3577               ** the search space by a larger factor: 4.  We make indexed range
  3515   3578               ** more selective intentionally because of the subjective 
  3516   3579               ** observation that indexed range constraints really are more
  3517   3580               ** selective in practice, on average. */
  3518   3581               pc.plan.nRow /= 3;
  3519   3582             }
  3520         -        }else if( pTerm->eOperator!=WO_NOOP ){
         3583  +        }else if( (pTerm->eOperator & WO_NOOP)==0 ){
  3521   3584             /* Any other expression lowers the output row count by half */
  3522   3585             pc.plan.nRow /= 2;
  3523   3586           }
  3524   3587         }
  3525   3588         if( pc.plan.nRow<2 ) pc.plan.nRow = 2;
  3526   3589       }
  3527   3590   
................................................................................
  3565   3628     assert( p->pOrderBy || (p->cost.plan.wsFlags&WHERE_ORDERED)==0 );
  3566   3629     assert( p->cost.plan.u.pIdx==0 || (p->cost.plan.wsFlags&WHERE_ROWID_EQ)==0 );
  3567   3630     assert( pSrc->pIndex==0 
  3568   3631          || p->cost.plan.u.pIdx==0 
  3569   3632          || p->cost.plan.u.pIdx==pSrc->pIndex 
  3570   3633     );
  3571   3634   
  3572         -  WHERETRACE(("   best index is: %s\n",
  3573         -         p->cost.plan.u.pIdx ? p->cost.plan.u.pIdx->zName : "ipk"));
         3635  +  WHERETRACE(("   best index is %s cost=%.1f\n",
         3636  +         p->cost.plan.u.pIdx ? p->cost.plan.u.pIdx->zName : "ipk",
         3637  +         p->cost.rCost));
  3574   3638     
  3575   3639     bestOrClauseIndex(p);
  3576   3640     bestAutomaticIndex(p);
  3577   3641     p->cost.plan.wsFlags |= eqTermMask;
  3578   3642   }
  3579   3643   
  3580   3644   /*
................................................................................
  4148   4212       **          we reference multiple rows using a "rowid IN (...)"
  4149   4213       **          construct.
  4150   4214       */
  4151   4215       iReleaseReg = sqlite3GetTempReg(pParse);
  4152   4216       pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0);
  4153   4217       assert( pTerm!=0 );
  4154   4218       assert( pTerm->pExpr!=0 );
  4155         -    assert( pTerm->leftCursor==iCur );
  4156   4219       assert( omitTable==0 );
  4157   4220       testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  4158   4221       iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, iReleaseReg);
  4159   4222       addrNxt = pLevel->addrNxt;
  4160   4223       sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt);
  4161   4224       sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg);
  4162   4225       sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
................................................................................
  4539   4602       int iRetInit;                             /* Address of regReturn init */
  4540   4603       int untestedTerms = 0;             /* Some terms not completely tested */
  4541   4604       int ii;                            /* Loop counter */
  4542   4605       Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
  4543   4606      
  4544   4607       pTerm = pLevel->plan.u.pTerm;
  4545   4608       assert( pTerm!=0 );
  4546         -    assert( pTerm->eOperator==WO_OR );
         4609  +    assert( pTerm->eOperator & WO_OR );
  4547   4610       assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
  4548   4611       pOrWc = &pTerm->u.pOrInfo->wc;
  4549   4612       pLevel->op = OP_Return;
  4550   4613       pLevel->p1 = regReturn;
  4551   4614   
  4552   4615       /* Set up a new SrcList in pOrTab containing the table being scanned
  4553   4616       ** by this loop in the a[0] slot and all notReady tables in a[1..] slots.
................................................................................
  4612   4675         if( pAndExpr ){
  4613   4676           pAndExpr = sqlite3PExpr(pParse, TK_AND, 0, pAndExpr, 0);
  4614   4677         }
  4615   4678       }
  4616   4679   
  4617   4680       for(ii=0; ii<pOrWc->nTerm; ii++){
  4618   4681         WhereTerm *pOrTerm = &pOrWc->a[ii];
  4619         -      if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){
         4682  +      if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
  4620   4683           WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
  4621   4684           Expr *pOrExpr = pOrTerm->pExpr;
  4622   4685           if( pAndExpr ){
  4623   4686             pAndExpr->pLeft = pOrExpr;
  4624   4687             pOrExpr = pAndExpr;
  4625   4688           }
  4626   4689           /* Loop through table entries that match term pOrTerm. */
................................................................................
  5067   5130     for(sWBI.i=iFrom=0, pLevel=pWInfo->a; sWBI.i<nTabList; sWBI.i++, pLevel++){
  5068   5131       WhereCost bestPlan;         /* Most efficient plan seen so far */
  5069   5132       Index *pIdx;                /* Index for FROM table at pTabItem */
  5070   5133       int j;                      /* For looping over FROM tables */
  5071   5134       int bestJ = -1;             /* The value of j */
  5072   5135       Bitmask m;                  /* Bitmask value for j or bestJ */
  5073   5136       int isOptimal;              /* Iterator for optimal/non-optimal search */
         5137  +    int ckOptimal;              /* Do the optimal scan check */
  5074   5138       int nUnconstrained;         /* Number tables without INDEXED BY */
  5075   5139       Bitmask notIndexed;         /* Mask of tables that cannot use an index */
  5076   5140   
  5077   5141       memset(&bestPlan, 0, sizeof(bestPlan));
  5078   5142       bestPlan.rCost = SQLITE_BIG_DBL;
  5079   5143       WHERETRACE(("*** Begin search for loop %d ***\n", sWBI.i));
  5080   5144   
................................................................................
  5101   5165       ** that do not use indices.  But this nRow reduction only happens if the
  5102   5166       ** table really is the innermost join.  
  5103   5167       **
  5104   5168       ** The second loop iteration is only performed if no optimal scan
  5105   5169       ** strategies were found by the first iteration. This second iteration
  5106   5170       ** is used to search for the lowest cost scan overall.
  5107   5171       **
  5108         -    ** Previous versions of SQLite performed only the second iteration -
  5109         -    ** the next outermost loop was always that with the lowest overall
  5110         -    ** cost. However, this meant that SQLite could select the wrong plan
  5111         -    ** for scripts such as the following:
         5172  +    ** Without the optimal scan step (the first iteration) a suboptimal
         5173  +    ** plan might be chosen for queries like this:
  5112   5174       **   
  5113   5175       **   CREATE TABLE t1(a, b); 
  5114   5176       **   CREATE TABLE t2(c, d);
  5115   5177       **   SELECT * FROM t2, t1 WHERE t2.rowid = t1.a;
  5116   5178       **
  5117   5179       ** The best strategy is to iterate through table t1 first. However it
  5118   5180       ** is not possible to determine this with a simple greedy algorithm.
................................................................................
  5119   5181       ** Since the cost of a linear scan through table t2 is the same 
  5120   5182       ** as the cost of a linear scan through table t1, a simple greedy 
  5121   5183       ** algorithm may choose to use t2 for the outer loop, which is a much
  5122   5184       ** costlier approach.
  5123   5185       */
  5124   5186       nUnconstrained = 0;
  5125   5187       notIndexed = 0;
  5126         -    for(isOptimal=(iFrom<nTabList-1); isOptimal>=0 && bestJ<0; isOptimal--){
         5188  +
         5189  +    /* The optimal scan check only occurs if there are two or more tables
         5190  +    ** available to be reordered */
         5191  +    if( iFrom==nTabList-1 ){
         5192  +      ckOptimal = 0;  /* Common case of just one table in the FROM clause */
         5193  +    }else{
         5194  +      ckOptimal = -1;
  5127   5195         for(j=iFrom, sWBI.pSrc=&pTabList->a[j]; j<nTabList; j++, sWBI.pSrc++){
  5128         -        int doNotReorder;    /* True if this table should not be reordered */
  5129         -  
  5130         -        doNotReorder =  (sWBI.pSrc->jointype & (JT_LEFT|JT_CROSS))!=0;
  5131         -        if( j!=iFrom && doNotReorder ) break;
  5132   5196           m = getMask(pMaskSet, sWBI.pSrc->iCursor);
  5133   5197           if( (m & sWBI.notValid)==0 ){
  5134   5198             if( j==iFrom ) iFrom++;
  5135   5199             continue;
         5200  +        }
         5201  +        if( j>iFrom && (sWBI.pSrc->jointype & (JT_LEFT|JT_CROSS))!=0 ) break;
         5202  +        if( ++ckOptimal ) break;
         5203  +        if( (sWBI.pSrc->jointype & JT_LEFT)!=0 ) break;
         5204  +      }
         5205  +    }
         5206  +    assert( ckOptimal==0 || ckOptimal==1 );
         5207  +
         5208  +    for(isOptimal=ckOptimal; isOptimal>=0 && bestJ<0; isOptimal--){
         5209  +      for(j=iFrom, sWBI.pSrc=&pTabList->a[j]; j<nTabList; j++, sWBI.pSrc++){
         5210  +        if( j>iFrom && (sWBI.pSrc->jointype & (JT_LEFT|JT_CROSS))!=0 ){
         5211  +          /* This break and one like it in the ckOptimal computation loop
         5212  +          ** above prevent table reordering across LEFT and CROSS JOINs.
         5213  +          ** The LEFT JOIN case is necessary for correctness.  The prohibition
         5214  +          ** against reordering across a CROSS JOIN is an SQLite feature that
         5215  +          ** allows the developer to control table reordering */
         5216  +          break;
         5217  +        }
         5218  +        m = getMask(pMaskSet, sWBI.pSrc->iCursor);
         5219  +        if( (m & sWBI.notValid)==0 ){
         5220  +          assert( j>iFrom );
         5221  +          continue;
  5136   5222           }
  5137   5223           sWBI.notReady = (isOptimal ? m : sWBI.notValid);
  5138   5224           if( sWBI.pSrc->pIndex==0 ) nUnconstrained++;
  5139   5225     
  5140   5226           WHERETRACE(("   === trying table %d (%s) with isOptimal=%d ===\n",
  5141   5227                       j, sWBI.pSrc->pTab->zName, isOptimal));
  5142   5228           assert( sWBI.pSrc->pTab );
................................................................................
  5158   5244                     || sWBI.cost.plan.u.pIdx==sWBI.pSrc->pIndex );
  5159   5245   
  5160   5246           if( isOptimal && (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ){
  5161   5247             notIndexed |= m;
  5162   5248           }
  5163   5249           if( isOptimal ){
  5164   5250             pWInfo->a[j].rOptCost = sWBI.cost.rCost;
  5165         -        }else if( iFrom<nTabList-1 ){
  5166         -          /* If two or more tables have nearly the same outer loop cost,
         5251  +        }else if( ckOptimal ){
         5252  +          /* If two or more tables have nearly the same outer loop cost, but
  5167   5253             ** very different inner loop (optimal) cost, we want to choose
  5168   5254             ** for the outer loop that table which benefits the least from
  5169   5255             ** being in the inner loop.  The following code scales the 
  5170   5256             ** outer loop cost estimate to accomplish that. */
  5171   5257             WHERETRACE(("   scaling cost from %.1f to %.1f\n",
  5172   5258                         sWBI.cost.rCost,
  5173   5259                         sWBI.cost.rCost/pWInfo->a[j].rOptCost));
................................................................................
  5204   5290                         "       cost=%.1f, nRow=%.1f, nOBSat=%d, wsFlags=%08x\n",
  5205   5291                         j, sWBI.pSrc->pTab->zName,
  5206   5292                         sWBI.cost.rCost, sWBI.cost.plan.nRow,
  5207   5293                         sWBI.cost.plan.nOBSat, sWBI.cost.plan.wsFlags));
  5208   5294             bestPlan = sWBI.cost;
  5209   5295             bestJ = j;
  5210   5296           }
  5211         -        if( doNotReorder ) break;
         5297  +
         5298  +        /* In a join like "w JOIN x LEFT JOIN y JOIN z"  make sure that
         5299  +        ** table y (and not table z) is always the next inner loop inside
         5300  +        ** of table x. */
         5301  +        if( (sWBI.pSrc->jointype & JT_LEFT)!=0 ) break;
  5212   5302         }
  5213   5303       }
  5214   5304       assert( bestJ>=0 );
  5215   5305       assert( sWBI.notValid & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
         5306  +    assert( bestJ==iFrom || (pTabList->a[iFrom].jointype & JT_LEFT)==0 );
         5307  +    testcase( bestJ>iFrom && (pTabList->a[iFrom].jointype & JT_CROSS)!=0 );
         5308  +    testcase( bestJ>iFrom && bestJ<nTabList-1
         5309  +                          && (pTabList->a[bestJ+1].jointype & JT_LEFT)!=0 );
  5216   5310       WHERETRACE(("*** Optimizer selects table %d (%s) for loop %d with:\n"
  5217   5311                   "    cost=%.1f, nRow=%.1f, nOBSat=%d, wsFlags=0x%08x\n",
  5218   5312                   bestJ, pTabList->a[bestJ].pTab->zName,
  5219   5313                   pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow,
  5220   5314                   bestPlan.plan.nOBSat, bestPlan.plan.wsFlags));
  5221   5315       if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){
  5222   5316         assert( pWInfo->eDistinct==0 );

Changes to test/autoindex1.test.

   253    253     CREATE TABLE t5(a, b, c);
   254    254     EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
   255    255   } {
   256    256     0 0 0 {SCAN TABLE t5 (~100000 rows)} 
   257    257     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   258    258   }
   259    259   
          260  +# The following checks a performance issue reported on the sqlite-dev
          261  +# mailing list on 2013-01-10
          262  +#
          263  +do_execsql_test autoindex1-800 {
          264  +  CREATE TABLE accounts(
          265  +    _id INTEGER PRIMARY KEY AUTOINCREMENT,
          266  +    account_name TEXT,
          267  +    account_type TEXT,
          268  +    data_set TEXT
          269  +  );
          270  +  CREATE TABLE data(
          271  +    _id INTEGER PRIMARY KEY AUTOINCREMENT,
          272  +    package_id INTEGER REFERENCES package(_id),
          273  +    mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,
          274  +    raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,
          275  +    is_read_only INTEGER NOT NULL DEFAULT 0,
          276  +    is_primary INTEGER NOT NULL DEFAULT 0,
          277  +    is_super_primary INTEGER NOT NULL DEFAULT 0,
          278  +    data_version INTEGER NOT NULL DEFAULT 0,
          279  +    data1 TEXT,
          280  +    data2 TEXT,
          281  +    data3 TEXT,
          282  +    data4 TEXT,
          283  +    data5 TEXT,
          284  +    data6 TEXT,
          285  +    data7 TEXT,
          286  +    data8 TEXT,
          287  +    data9 TEXT,
          288  +    data10 TEXT,
          289  +    data11 TEXT,
          290  +    data12 TEXT,
          291  +    data13 TEXT,
          292  +    data14 TEXT,
          293  +    data15 TEXT,
          294  +    data_sync1 TEXT,
          295  +    data_sync2 TEXT,
          296  +    data_sync3 TEXT,
          297  +    data_sync4 TEXT 
          298  +  );
          299  +  CREATE TABLE mimetypes(
          300  +    _id INTEGER PRIMARY KEY AUTOINCREMENT,
          301  +    mimetype TEXT NOT NULL
          302  +  );
          303  +  CREATE TABLE raw_contacts(
          304  +    _id INTEGER PRIMARY KEY AUTOINCREMENT,
          305  +    account_id INTEGER REFERENCES accounts(_id),
          306  +    sourceid TEXT,
          307  +    raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0,
          308  +    version INTEGER NOT NULL DEFAULT 1,
          309  +    dirty INTEGER NOT NULL DEFAULT 0,
          310  +    deleted INTEGER NOT NULL DEFAULT 0,
          311  +    contact_id INTEGER REFERENCES contacts(_id),
          312  +    aggregation_mode INTEGER NOT NULL DEFAULT 0,
          313  +    aggregation_needed INTEGER NOT NULL DEFAULT 1,
          314  +    custom_ringtone TEXT,
          315  +    send_to_voicemail INTEGER NOT NULL DEFAULT 0,
          316  +    times_contacted INTEGER NOT NULL DEFAULT 0,
          317  +    last_time_contacted INTEGER,
          318  +    starred INTEGER NOT NULL DEFAULT 0,
          319  +    display_name TEXT,
          320  +    display_name_alt TEXT,
          321  +    display_name_source INTEGER NOT NULL DEFAULT 0,
          322  +    phonetic_name TEXT,
          323  +    phonetic_name_style TEXT,
          324  +    sort_key TEXT,
          325  +    sort_key_alt TEXT,
          326  +    name_verified INTEGER NOT NULL DEFAULT 0,
          327  +    sync1 TEXT,
          328  +    sync2 TEXT,
          329  +    sync3 TEXT,
          330  +    sync4 TEXT,
          331  +    sync_uid TEXT,
          332  +    sync_version INTEGER NOT NULL DEFAULT 1,
          333  +    has_calendar_event INTEGER NOT NULL DEFAULT 0,
          334  +    modified_time INTEGER,
          335  +    is_restricted INTEGER DEFAULT 0,
          336  +    yp_source TEXT,
          337  +    method_selected INTEGER DEFAULT 0,
          338  +    custom_vibration_type INTEGER DEFAULT 0,
          339  +    custom_ringtone_path TEXT,
          340  +    message_notification TEXT,
          341  +    message_notification_path TEXT
          342  +  );
          343  +  CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);
          344  +  CREATE INDEX data_raw_contact_id ON data (raw_contact_id);
          345  +  CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
          346  +  CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key);
          347  +  CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt);
          348  +  CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id);
          349  +  CREATE INDEX raw_contacts_source_id_account_id_index
          350  +      ON raw_contacts (sourceid, account_id);
          351  +  ANALYZE sqlite_master;
          352  +  INSERT INTO sqlite_stat1
          353  +     VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4');
          354  +  INSERT INTO sqlite_stat1
          355  +     VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
          356  +  INSERT INTO sqlite_stat1
          357  +     VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
          358  +            '1600 1600 1600');
          359  +  INSERT INTO sqlite_stat1
          360  +     VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');
          361  +  INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');
          362  +  INSERT INTO sqlite_stat1
          363  +     VALUES('data','data_mimetype_data1_index','9819 2455 3');
          364  +  INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');
          365  +  INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1');
          366  +  DROP TABLE IF EXISTS sqlite_stat3;
          367  +  ANALYZE sqlite_master;
          368  +  
          369  +  EXPLAIN QUERY PLAN
          370  +  SELECT * FROM 
          371  +        data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 
          372  +             JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 
          373  +             JOIN accounts ON (raw_contacts.account_id=accounts._id)
          374  +   WHERE mimetype_id=10 AND data14 IS NOT NULL;
          375  +} {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/}
          376  +do_execsql_test autoindex1-801 {
          377  +  EXPLAIN QUERY PLAN
          378  +  SELECT * FROM 
          379  +        data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 
          380  +             JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 
          381  +             JOIN accounts ON (raw_contacts.account_id=accounts._id)
          382  +   WHERE mimetypes._id=10 AND data14 IS NOT NULL;
          383  +} {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/}
   260    384   
   261    385   finish_test

Changes to test/fts4content.test.

    42     42   #   7.* - Test that if content=xxx is specified and table xxx does not
    43     43   #         exist, the FTS table can still be used for INSERT and some
    44     44   #         SELECT statements.
    45     45   #
    46     46   #   8.* - Test that if the content=xxx and prefix options are used together,
    47     47   #         the 'rebuild' command still works.
    48     48   #
           49  +#   9.* - Test using content=xxx where xxx is a virtual table.
           50  +#
    49     51   
    50     52   do_execsql_test 1.1.1 {
    51     53     CREATE TABLE t1(a, b, c);
    52     54     INSERT INTO t1 VALUES('w x', 'x y', 'y z');
    53     55     CREATE VIRTUAL TABLE ft1 USING fts4(content=t1);
    54     56   }
    55     57   
................................................................................
   517    519   }
   518    520   
   519    521   do_execsql_test 8.2 { SELECT * FROM ft10 WHERE a MATCH 'ab*';          }
   520    522   do_execsql_test 8.3 { INSERT INTO ft10(ft10) VALUES('rebuild');        }
   521    523   do_execsql_test 8.4 { SELECT rowid FROM ft10 WHERE a MATCH 'ab*';      } {1 2 3}
   522    524   do_execsql_test 8.5 { SELECT rowid FROM ft10 WHERE b MATCH 'abav*';    } {3}
   523    525   do_execsql_test 8.6 { SELECT rowid FROM ft10 WHERE ft10 MATCH 'abas*'; } {1}
          526  +
          527  +#-------------------------------------------------------------------------
          528  +# Test cases 9.*
          529  +# 
          530  +reset_db
          531  +register_echo_module [sqlite3_connection_pointer db]
          532  +
          533  +do_execsql_test 9.1 {
          534  +  CREATE TABLE tbl1(a, b);
          535  +  INSERT INTO tbl1 VALUES('a b', 'c d');
          536  +  INSERT INTO tbl1 VALUES('e f', 'a b');
          537  +  CREATE VIRTUAL TABLE e1 USING echo(tbl1);
          538  +  CREATE VIRTUAL TABLE ft1 USING fts4(content=e1);
          539  +  INSERT INTO ft1(ft1) VALUES('rebuild');
          540  +}
          541  +
          542  +do_execsql_test 9.2 {
          543  +  SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'e'
          544  +} {2 {e f} {a b}}
          545  +
          546  +do_execsql_test 9.3 {
          547  +  SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
          548  +} {1 {a b} {c d} 2 {e f} {a b}}
          549  +
          550  +do_execsql_test 9.4 { 
          551  +  DELETE FROM ft1 WHERE docid=1;
          552  +}
          553  +
          554  +do_execsql_test 9.5 {
          555  +  SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
          556  +} {2 {e f} {a b}}
          557  +
          558  +do_execsql_test 9.6 {
          559  +  INSERT INTO ft1(ft1) VALUES('rebuild');
          560  +  SELECT rowid, * FROM ft1 WHERE ft1 MATCH 'a'
          561  +} {1 {a b} {c d} 2 {e f} {a b}}
          562  +
          563  +
          564  +#-------------------------------------------------------------------------
          565  +# Test cases 10.*
          566  +# 
          567  +reset_db
          568  +register_fs_module [sqlite3_connection_pointer db]
          569  +
          570  +proc write_file {path text} {
          571  +  set fd [open $path w]
          572  +  puts -nonewline $fd $text
          573  +  close $fd
          574  +}
          575  +
          576  +write_file t1.txt {a b c d e f g h i j k l m n o p q r s t u v w x y z}
          577  +write_file t2.txt {a b c d e f g h i j k l m a b c d e f g h i j k l m}
          578  +write_file t3.txt {n o p q r s t u v w x y z n o p q r s t u v w x y z}
          579  +
          580  +do_execsql_test 10.1 {
          581  +  CREATE TABLE idx(id INTEGER PRIMARY KEY, path TEXT);
          582  +  INSERT INTO idx VALUES (1, 't1.txt');
          583  +  INSERT INTO idx VALUES (2, 't2.txt');
          584  +  INSERT INTO idx VALUES (3, 't3.txt');
          585  +
          586  +  CREATE VIRTUAL TABLE vt USING fs(idx);
          587  +  SELECT * FROM vt;
          588  +} {
          589  +  1 {a b c d e f g h i j k l m n o p q r s t u v w x y z} 
          590  +  2 {a b c d e f g h i j k l m a b c d e f g h i j k l m}
          591  +  3 {n o p q r s t u v w x y z n o p q r s t u v w x y z}
          592  +}
          593  +
          594  +do_execsql_test 10.2 {
          595  +  SELECT * FROM vt WHERE rowid = 2;
          596  +} {
          597  +  2 {a b c d e f g h i j k l m a b c d e f g h i j k l m}
          598  +}
          599  +
          600  +do_execsql_test 10.3 {
          601  +  CREATE VIRTUAL TABLE ft USING fts4(content=vt);
          602  +  INSERT INTO ft(ft) VALUES('rebuild');
          603  +}
          604  +
          605  +do_execsql_test 10.4 {
          606  +  SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'
          607  +} {
          608  +  {...c d [e] f g...} {...c d [e] f g...}
          609  +}
          610  +
          611  +do_execsql_test 10.5 {
          612  +  SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 't'
          613  +} {
          614  +  {...r s [t] u v...} {...r s [t] u v...}
          615  +}
          616  +
          617  +do_execsql_test 10.6 { DELETE FROM ft WHERE docid=2 }
          618  +
          619  +do_execsql_test 10.7 {
          620  +  SELECT snippet(ft, '[', ']', '...', -1, 5) FROM ft WHERE ft MATCH 'e'
          621  +} {
          622  +  {...c d [e] f g...}
          623  +}
   524    624   
   525    625   finish_test
          626  +

Changes to test/regexp1.test.

   193    193            'abc{4}x' REGEXP '^abc\{4\}x$',
   194    194            'abc|def' REGEXP '^abc\|def$'
   195    195   } {1 1 1 1 1 1 1 1 1 1 1 1}
   196    196   
   197    197   do_execsql_test regexp1-2.20 {
   198    198     SELECT 'abc$¢€xyz' REGEXP '^abc\u0024\u00a2\u20acxyz$',
   199    199            'abc$¢€xyz' REGEXP '^abc\u0024\u00A2\u20ACxyz$',
   200         -         'abc$¢€xyz' REGEXP '^abc\x24\xa2\x20acxyz$'
          200  +         'abc$¢€xyz' REGEXP '^abc\x24\xa2\u20acxyz$'
   201    201   } {1 1 1}
   202    202   do_execsql_test regexp1-2.21 {
   203    203     SELECT 'abc$¢€xyz' REGEXP '^abc[\u0024][\u00a2][\u20ac]xyz$',
   204    204            'abc$¢€xyz' REGEXP '^abc[\u0024\u00A2\u20AC]{3}xyz$',
   205         -         'abc$¢€xyz' REGEXP '^abc[\x24][\xa2\x20ac]+xyz$'
          205  +         'abc$¢€xyz' REGEXP '^abc[\x24][\xa2\u20ac]+xyz$'
   206    206   } {1 1 1}
   207    207   do_execsql_test regexp1-2.22 {
   208    208     SELECT 'abc$¢€xyz' REGEXP '^abc[^\u0025-X][^ -\u007f][^\u20ab]xyz$'
   209    209   } {1}
   210    210   
   211    211   finish_test

Changes to test/shell1.test.

   249    249   #----------------------------------------------------------------------------
   250    250   # Test cases shell1-3.*: Basic test that "dot" command can be called.
   251    251   #
   252    252   
   253    253   # .backup ?DB? FILE      Backup DB (default "main") to FILE
   254    254   do_test shell1-3.1.1 {
   255    255     catchcmd "test.db" ".backup"
   256         -} {1 {Error: unknown command or invalid arguments:  "backup". Enter ".help" for help}}
          256  +} {1 {missing FILENAME argument on .backup}}
   257    257   do_test shell1-3.1.2 {
   258    258     catchcmd "test.db" ".backup FOO"
   259    259   } {0 {}}
   260    260   do_test shell1-3.1.3 {
   261    261     catchcmd "test.db" ".backup FOO BAR"
   262    262   } {1 {Error: unknown database FOO}}
   263    263   do_test shell1-3.1.4 {
   264    264     # too many arguments
   265    265     catchcmd "test.db" ".backup FOO BAR BAD"
   266         -} {1 {Error: unknown command or invalid arguments:  "backup". Enter ".help" for help}}
          266  +} {1 {too many arguments to .backup}}
   267    267   
   268    268   # .bail ON|OFF           Stop after hitting an error.  Default OFF
   269    269   do_test shell1-3.2.1 {
   270    270     catchcmd "test.db" ".bail"
   271    271   } {1 {Error: unknown command or invalid arguments:  "bail". Enter ".help" for help}}
   272    272   do_test shell1-3.2.2 {
   273    273     catchcmd "test.db" ".bail ON"

Added test/transitive1.test.

            1  +# 2013 April 17
            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.  The
           12  +# focus of this script is testing of transitive WHERE clause constraints
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +do_execsql_test transitive1-100 {
           19  +  CREATE TABLE t1(a TEXT, b TEXT, c TEXT COLLATE NOCASE);
           20  +  INSERT INTO t1 VALUES('abc','abc','Abc');
           21  +  INSERT INTO t1 VALUES('def','def','def');
           22  +  INSERT INTO t1 VALUES('ghi','ghi','GHI');
           23  +  CREATE INDEX t1a1 ON t1(a);
           24  +  CREATE INDEX t1a2 ON t1(a COLLATE nocase);
           25  +
           26  +  SELECT * FROM t1 WHERE a=b AND c=b AND c='DEF';
           27  +} {def def def}
           28  +do_execsql_test transitive1-110 {
           29  +  SELECT * FROM t1 WHERE a=b AND c=b AND c>='DEF' ORDER BY +a;
           30  +} {def def def ghi ghi GHI}
           31  +do_execsql_test transitive1-120 {
           32  +  SELECT * FROM t1 WHERE a=b AND c=b AND c<='DEF' ORDER BY +a;
           33  +} {abc abc Abc def def def}
           34  +
           35  +do_execsql_test transitive1-200 {
           36  +  CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT);
           37  +  INSERT INTO t2 VALUES(100,100,100);
           38  +  INSERT INTO t2 VALUES(20,20,20);
           39  +  INSERT INTO t2 VALUES(3,3,3);
           40  +
           41  +  SELECT * FROM t2 WHERE a=b AND c=b AND c=20;
           42  +} {20 20 20}
           43  +do_execsql_test transitive1-210 {
           44  +  SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
           45  +} {3 3 3 20 20 20}
           46  +do_execsql_test transitive1-220 {
           47  +  SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
           48  +} {20 20 20 100 100 100}
           49  +
           50  +finish_test