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: |
82d3d1ae824e1fbc7958657be7923159 |
| 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
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