Overview
| SHA1 Hash: | 1138815c625a1a1721e0efdad6a5abca1e3c7299 |
|---|---|
| Date: | 2012-10-03 20:25:58 |
| User: | mistachkin |
| Comment: | Merge updates from trunk. |
Tags And Properties
- branch=configReadOnly inherited from [7c3401657a]
- sym-configReadOnly inherited from [7c3401657a]
Changes
Changes to Makefile.msc
153 NLTLIBPATHS = "/LIBPATH:$(NCRTLIBPATH)" "/LIBPATH:$(NSDKLIBPATH)" 153 NLTLIBPATHS = "/LIBPATH:$(NCRTLIBPATH)" "/LIBPATH:$(NSDKLIBPATH)" 154 !ENDIF 154 !ENDIF 155 155 156 # C compiler and options for use in building executables that 156 # C compiler and options for use in building executables that 157 # will run on the target platform. (BCC and TCC are usually the 157 # will run on the target platform. (BCC and TCC are usually the 158 # same unless your are cross-compiling.) 158 # same unless your are cross-compiling.) 159 # 159 # 160 TCC = $(CC) -W3 -DSQLITE_OS_WIN=1 -I. -I$(TOP)\src -fp:precise | 160 TCC = $(CC) -W3 -DSQLITE_OS_WIN=1 -I$(TOP) -I$(TOP)\src -fp:precise 161 RCC = $(RC) -DSQLITE_OS_WIN=1 -I. -I$(TOP)\src | 161 RCC = $(RC) -DSQLITE_OS_WIN=1 -I$(TOP) -I$(TOP)\src 162 162 163 # When compiling the library for use in the WinRT environment, 163 # When compiling the library for use in the WinRT environment, 164 # the following compile-time options must be used as well to 164 # the following compile-time options must be used as well to 165 # disable use of Win32 APIs that are not available and to enable 165 # disable use of Win32 APIs that are not available and to enable 166 # use of Win32 APIs that are specific to Windows 8 and/or WinRT. 166 # use of Win32 APIs that are specific to Windows 8 and/or WinRT. 167 # 167 # 168 !IF $(FOR_WINRT)!=0 168 !IF $(FOR_WINRT)!=0 ................................................................................................................................................................................ 817 opcodes.lo: opcodes.c 817 opcodes.lo: opcodes.c 818 $(LTCOMPILE) -c opcodes.c 818 $(LTCOMPILE) -c opcodes.c 819 819 820 # Rule to build the Win32 resources object file. 820 # Rule to build the Win32 resources object file. 821 # 821 # 822 sqlite3res.lo: $(TOP)\src\sqlite3.rc $(HDR) 822 sqlite3res.lo: $(TOP)\src\sqlite3.rc $(HDR) 823 echo #ifndef SQLITE_RESOURCE_VERSION > sqlite3rc.h 823 echo #ifndef SQLITE_RESOURCE_VERSION > sqlite3rc.h 824 for /F %%V in ('type VERSION') do ( \ | 824 for /F %%V in ('type "$(TOP)\VERSION"') do ( \ 825 echo #define SQLITE_RESOURCE_VERSION %%V \ 825 echo #define SQLITE_RESOURCE_VERSION %%V \ 826 | $(NAWK) "/.*/ { gsub(/[.]/,\",\");print }" >> sqlite3r 826 | $(NAWK) "/.*/ { gsub(/[.]/,\",\");print }" >> sqlite3r 827 ) 827 ) 828 echo #endif >> sqlite3rc.h 828 echo #endif >> sqlite3rc.h 829 $(LTRCOMPILE) -fo sqlite3res.lo $(TOP)\src\sqlite3.rc 829 $(LTRCOMPILE) -fo sqlite3res.lo $(TOP)\src\sqlite3.rc 830 830 831 # Rules to build individual *.lo files from files in the src directory. 831 # Rules to build individual *.lo files from files in the src directory.
Changes to ext/rtree/rtree.c
2656 } 2656 } 2657 2657 2658 /* 2658 /* 2659 ** Remove the entry with rowid=iDelete from the r-tree structure. 2659 ** Remove the entry with rowid=iDelete from the r-tree structure. 2660 */ 2660 */ 2661 static int rtreeDeleteRowid(Rtree *pRtree, sqlite3_int64 iDelete){ 2661 static int rtreeDeleteRowid(Rtree *pRtree, sqlite3_int64 iDelete){ 2662 int rc; /* Return code */ 2662 int rc; /* Return code */ 2663 RtreeNode *pLeaf; /* Leaf node containing record iDelete */ | 2663 RtreeNode *pLeaf = 0; /* Leaf node containing record iDelete */ 2664 int iCell; /* Index of iDelete cell in pLeaf */ 2664 int iCell; /* Index of iDelete cell in pLeaf */ 2665 RtreeNode *pRoot; /* Root node of rtree structure */ 2665 RtreeNode *pRoot; /* Root node of rtree structure */ 2666 2666 2667 2667 2668 /* Obtain a reference to the root node to initialise Rtree.iDepth */ 2668 /* Obtain a reference to the root node to initialise Rtree.iDepth */ 2669 rc = nodeAcquire(pRtree, 1, 0, &pRoot); 2669 rc = nodeAcquire(pRtree, 1, 0, &pRoot); 2670 2670 ................................................................................................................................................................................ 2859 2859 2860 /* If the azData[] array contains more than one element, elements 2860 /* If the azData[] array contains more than one element, elements 2861 ** (azData[2]..azData[argc-1]) contain a new record to insert into 2861 ** (azData[2]..azData[argc-1]) contain a new record to insert into 2862 ** the r-tree structure. 2862 ** the r-tree structure. 2863 */ 2863 */ 2864 if( rc==SQLITE_OK && nData>1 ){ 2864 if( rc==SQLITE_OK && nData>1 ){ 2865 /* Insert the new record into the r-tree */ 2865 /* Insert the new record into the r-tree */ 2866 RtreeNode *pLeaf; | 2866 RtreeNode *pLeaf = 0; 2867 2867 2868 /* Figure out the rowid of the new row. */ 2868 /* Figure out the rowid of the new row. */ 2869 if( bHaveRowid==0 ){ 2869 if( bHaveRowid==0 ){ 2870 rc = newRowid(pRtree, &cell.iRowid); 2870 rc = newRowid(pRtree, &cell.iRowid); 2871 } 2871 } 2872 *pRowid = cell.iRowid; 2872 *pRowid = cell.iRowid; 2873 2873
Changes to src/backup.c
215 static int backupOnePage(sqlite3_backup *p, Pgno iSrcPg, const u8 *zSrcData){ 215 static int backupOnePage(sqlite3_backup *p, Pgno iSrcPg, const u8 *zSrcData){ 216 Pager * const pDestPager = sqlite3BtreePager(p->pDest); 216 Pager * const pDestPager = sqlite3BtreePager(p->pDest); 217 const int nSrcPgsz = sqlite3BtreeGetPageSize(p->pSrc); 217 const int nSrcPgsz = sqlite3BtreeGetPageSize(p->pSrc); 218 int nDestPgsz = sqlite3BtreeGetPageSize(p->pDest); 218 int nDestPgsz = sqlite3BtreeGetPageSize(p->pDest); 219 const int nCopy = MIN(nSrcPgsz, nDestPgsz); 219 const int nCopy = MIN(nSrcPgsz, nDestPgsz); 220 const i64 iEnd = (i64)iSrcPg*(i64)nSrcPgsz; 220 const i64 iEnd = (i64)iSrcPg*(i64)nSrcPgsz; 221 #ifdef SQLITE_HAS_CODEC 221 #ifdef SQLITE_HAS_CODEC > 222 /* Use BtreeGetReserveNoMutex() for the source b-tree, as although it is > 223 ** guaranteed that the shared-mutex is held by this thread, handle > 224 ** p->pSrc may not actually be the owner. */ 222 int nSrcReserve = sqlite3BtreeGetReserve(p->pSrc); | 225 int nSrcReserve = sqlite3BtreeGetReserveNoMutex(p->pSrc); 223 int nDestReserve = sqlite3BtreeGetReserve(p->pDest); 226 int nDestReserve = sqlite3BtreeGetReserve(p->pDest); 224 #endif 227 #endif 225 < 226 int rc = SQLITE_OK; 228 int rc = SQLITE_OK; 227 i64 iOff; 229 i64 iOff; 228 230 > 231 assert( sqlite3BtreeGetReserveNoMutex(p->pSrc)>=0 ); 229 assert( p->bDestLocked ); 232 assert( p->bDestLocked ); 230 assert( !isFatalError(p->rc) ); 233 assert( !isFatalError(p->rc) ); 231 assert( iSrcPg!=PENDING_BYTE_PAGE(p->pSrc->pBt) ); 234 assert( iSrcPg!=PENDING_BYTE_PAGE(p->pSrc->pBt) ); 232 assert( zSrcData ); 235 assert( zSrcData ); 233 236 234 /* Catch the case where the destination is an in-memory database and the 237 /* Catch the case where the destination is an in-memory database and the 235 ** page sizes of the source and destination differ. 238 ** page sizes of the source and destination differ.
Changes to src/btree.c
2195 2195 2196 /* 2196 /* 2197 ** Return the currently defined page size 2197 ** Return the currently defined page size 2198 */ 2198 */ 2199 int sqlite3BtreeGetPageSize(Btree *p){ 2199 int sqlite3BtreeGetPageSize(Btree *p){ 2200 return p->pBt->pageSize; 2200 return p->pBt->pageSize; 2201 } 2201 } > 2202 > 2203 #if defined(SQLITE_HAS_CODEC) || defined(SQLITE_DEBUG) > 2204 /* > 2205 ** This function is similar to sqlite3BtreeGetReserve(), except that it > 2206 ** may only be called if it is guaranteed that the b-tree mutex is already > 2207 ** held. > 2208 ** > 2209 ** This is useful in one special case in the backup API code where it is > 2210 ** known that the shared b-tree mutex is held, but the mutex on the > 2211 ** database handle that owns *p is not. In this case if sqlite3BtreeEnter() > 2212 ** were to be called, it might collide with some other operation on the > 2213 ** database handle that owns *p, causing undefined behaviour. > 2214 */ > 2215 int sqlite3BtreeGetReserveNoMutex(Btree *p){ > 2216 assert( sqlite3_mutex_held(p->pBt->mutex) ); > 2217 return p->pBt->pageSize - p->pBt->usableSize; > 2218 } > 2219 #endif /* SQLITE_HAS_CODEC || SQLITE_DEBUG */ 2202 2220 2203 #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) || !defined(SQLITE_OMIT_VACUUM) 2221 #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) || !defined(SQLITE_OMIT_VACUUM) 2204 /* 2222 /* 2205 ** Return the number of bytes of space at the end of every page that 2223 ** Return the number of bytes of space at the end of every page that 2206 ** are intentually left unused. This is the "reserved" space that is 2224 ** are intentually left unused. This is the "reserved" space that is 2207 ** sometimes used by extensions. 2225 ** sometimes used by extensions. 2208 */ 2226 */ ................................................................................................................................................................................ 5252 5270 5253 assert( sqlite3_mutex_held(pPage->pBt->mutex) ); 5271 assert( sqlite3_mutex_held(pPage->pBt->mutex) ); 5254 btreeParseCellPtr(pPage, pCell, &info); 5272 btreeParseCellPtr(pPage, pCell, &info); 5255 if( info.iOverflow==0 ){ 5273 if( info.iOverflow==0 ){ 5256 return SQLITE_OK; /* No overflow pages. Return without doing anything */ 5274 return SQLITE_OK; /* No overflow pages. Return without doing anything */ 5257 } 5275 } 5258 if( pCell+info.iOverflow+3 > pPage->aData+pPage->maskPage ){ 5276 if( pCell+info.iOverflow+3 > pPage->aData+pPage->maskPage ){ 5259 return SQLITE_CORRUPT; /* Cell extends past end of page */ | 5277 return SQLITE_CORRUPT_BKPT; /* Cell extends past end of page */ 5260 } 5278 } 5261 ovflPgno = get4byte(&pCell[info.iOverflow]); 5279 ovflPgno = get4byte(&pCell[info.iOverflow]); 5262 assert( pBt->usableSize > 4 ); 5280 assert( pBt->usableSize > 4 ); 5263 ovflPageSize = pBt->usableSize - 4; 5281 ovflPageSize = pBt->usableSize - 4; 5264 nOvfl = (info.nPayload - info.nLocal + ovflPageSize - 1)/ovflPageSize; 5282 nOvfl = (info.nPayload - info.nLocal + ovflPageSize - 1)/ovflPageSize; 5265 assert( ovflPgno==0 || nOvfl>0 ); 5283 assert( ovflPgno==0 || nOvfl>0 ); 5266 while( nOvfl-- ){ 5284 while( nOvfl-- ){ ................................................................................................................................................................................ 5918 ** size of a cell stored within an internal node is always less than 1/4 5936 ** size of a cell stored within an internal node is always less than 1/4 5919 ** of the page-size, the aOvflSpace[] buffer is guaranteed to be large 5937 ** of the page-size, the aOvflSpace[] buffer is guaranteed to be large 5920 ** enough for all overflow cells. 5938 ** enough for all overflow cells. 5921 ** 5939 ** 5922 ** If aOvflSpace is set to a null pointer, this function returns 5940 ** If aOvflSpace is set to a null pointer, this function returns 5923 ** SQLITE_NOMEM. 5941 ** SQLITE_NOMEM. 5924 */ 5942 */ > 5943 #if defined(_MSC_VER) && _MSC_VER >= 1700 && defined(_M_ARM) > 5944 #pragma optimize("", off) > 5945 #endif 5925 static int balance_nonroot( 5946 static int balance_nonroot( 5926 MemPage *pParent, /* Parent page of siblings being balanced */ 5947 MemPage *pParent, /* Parent page of siblings being balanced */ 5927 int iParentIdx, /* Index of "the page" in pParent */ 5948 int iParentIdx, /* Index of "the page" in pParent */ 5928 u8 *aOvflSpace, /* page-size bytes of space for parent ovfl */ 5949 u8 *aOvflSpace, /* page-size bytes of space for parent ovfl */ 5929 int isRoot, /* True if pParent is a root-page */ 5950 int isRoot, /* True if pParent is a root-page */ 5930 int bBulk /* True if this call is part of a bulk load */ 5951 int bBulk /* True if this call is part of a bulk load */ 5931 ){ 5952 ){ ................................................................................................................................................................................ 6548 } 6569 } 6549 for(i=0; i<nNew; i++){ 6570 for(i=0; i<nNew; i++){ 6550 releasePage(apNew[i]); 6571 releasePage(apNew[i]); 6551 } 6572 } 6552 6573 6553 return rc; 6574 return rc; 6554 } 6575 } > 6576 #if defined(_MSC_VER) && _MSC_VER >= 1700 && defined(_M_ARM) > 6577 #pragma optimize("", on) > 6578 #endif 6555 6579 6556 6580 6557 /* 6581 /* 6558 ** This function is called when the root page of a b-tree structure is 6582 ** This function is called when the root page of a b-tree structure is 6559 ** overfull (has one or more overflow pages). 6583 ** overfull (has one or more overflow pages). 6560 ** 6584 ** 6561 ** A new child page is allocated and the contents of the current root 6585 ** A new child page is allocated and the contents of the current root
Changes to src/btree.h
67 int sqlite3BtreeSyncDisabled(Btree*); 67 int sqlite3BtreeSyncDisabled(Btree*); 68 int sqlite3BtreeSetPageSize(Btree *p, int nPagesize, int nReserve, int eFix); 68 int sqlite3BtreeSetPageSize(Btree *p, int nPagesize, int nReserve, int eFix); 69 int sqlite3BtreeGetPageSize(Btree*); 69 int sqlite3BtreeGetPageSize(Btree*); 70 int sqlite3BtreeMaxPageCount(Btree*,int); 70 int sqlite3BtreeMaxPageCount(Btree*,int); 71 u32 sqlite3BtreeLastPage(Btree*); 71 u32 sqlite3BtreeLastPage(Btree*); 72 int sqlite3BtreeSecureDelete(Btree*,int); 72 int sqlite3BtreeSecureDelete(Btree*,int); 73 int sqlite3BtreeGetReserve(Btree*); 73 int sqlite3BtreeGetReserve(Btree*); > 74 #if defined(SQLITE_HAS_CODEC) || defined(SQLITE_DEBUG) > 75 int sqlite3BtreeGetReserveNoMutex(Btree *p); > 76 #endif 74 int sqlite3BtreeSetAutoVacuum(Btree *, int); 77 int sqlite3BtreeSetAutoVacuum(Btree *, int); 75 int sqlite3BtreeGetAutoVacuum(Btree *); 78 int sqlite3BtreeGetAutoVacuum(Btree *); 76 int sqlite3BtreeBeginTrans(Btree*,int); 79 int sqlite3BtreeBeginTrans(Btree*,int); 77 int sqlite3BtreeCommitPhaseOne(Btree*, const char *zMaster); 80 int sqlite3BtreeCommitPhaseOne(Btree*, const char *zMaster); 78 int sqlite3BtreeCommitPhaseTwo(Btree*, int); 81 int sqlite3BtreeCommitPhaseTwo(Btree*, int); 79 int sqlite3BtreeCommit(Btree*); 82 int sqlite3BtreeCommit(Btree*); 80 int sqlite3BtreeRollback(Btree*,int); 83 int sqlite3BtreeRollback(Btree*,int);
Changes to src/delete.c
634 }else{ 634 }else{ 635 sqlite3VdbeAddOp3(v, OP_Column, iCur, idx, regBase+j); 635 sqlite3VdbeAddOp3(v, OP_Column, iCur, idx, regBase+j); 636 sqlite3ColumnDefault(v, pTab, idx, -1); 636 sqlite3ColumnDefault(v, pTab, idx, -1); 637 } 637 } 638 } 638 } 639 if( doMakeRec ){ 639 if( doMakeRec ){ 640 const char *zAff; 640 const char *zAff; 641 if( pTab->pSelect || (pParse->db->flags & SQLITE_IdxRealAsInt)!=0 ){ | 641 if( pTab->pSelect > 642 || OptimizationDisabled(pParse->db, SQLITE_IdxRealAsInt) > 643 ){ 642 zAff = 0; 644 zAff = 0; 643 }else{ 645 }else{ 644 zAff = sqlite3IndexAffinityStr(v, pIdx); 646 zAff = sqlite3IndexAffinityStr(v, pIdx); 645 } 647 } 646 sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol+1, regOut); 648 sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nCol+1, regOut); 647 sqlite3VdbeChangeP4(v, -1, zAff, P4_TRANSIENT); 649 sqlite3VdbeChangeP4(v, -1, zAff, P4_TRANSIENT); 648 } 650 } 649 sqlite3ReleaseTempRange(pParse, regBase, nCol+1); 651 sqlite3ReleaseTempRange(pParse, regBase, nCol+1); 650 return regBase; 652 return regBase; 651 } 653 }
Changes to src/expr.c
2062 assert( iReg>0 ); /* Register numbers are always positive */ 2062 assert( iReg>0 ); /* Register numbers are always positive */ 2063 assert( iCol>=-1 && iCol<32768 ); /* Finite column numbers */ 2063 assert( iCol>=-1 && iCol<32768 ); /* Finite column numbers */ 2064 2064 2065 /* The SQLITE_ColumnCache flag disables the column cache. This is used 2065 /* The SQLITE_ColumnCache flag disables the column cache. This is used 2066 ** for testing only - to verify that SQLite always gets the same answer 2066 ** for testing only - to verify that SQLite always gets the same answer 2067 ** with and without the column cache. 2067 ** with and without the column cache. 2068 */ 2068 */ 2069 if( pParse->db->flags & SQLITE_ColumnCache ) return; | 2069 if( OptimizationDisabled(pParse->db, SQLITE_ColumnCache) ) return; 2070 2070 2071 /* First replace any existing entry. 2071 /* First replace any existing entry. 2072 ** 2072 ** 2073 ** Actually, the way the column cache is currently used, we are guaranteed 2073 ** Actually, the way the column cache is currently used, we are guaranteed 2074 ** that the object will never already be in cache. Verify this guarantee. 2074 ** that the object will never already be in cache. Verify this guarantee. 2075 */ 2075 */ 2076 #ifndef NDEBUG 2076 #ifndef NDEBUG ................................................................................................................................................................................ 3378 ** interface. This allows test logic to verify that the same answer is 3378 ** interface. This allows test logic to verify that the same answer is 3379 ** obtained for queries regardless of whether or not constants are 3379 ** obtained for queries regardless of whether or not constants are 3380 ** precomputed into registers or if they are inserted in-line. 3380 ** precomputed into registers or if they are inserted in-line. 3381 */ 3381 */ 3382 void sqlite3ExprCodeConstants(Parse *pParse, Expr *pExpr){ 3382 void sqlite3ExprCodeConstants(Parse *pParse, Expr *pExpr){ 3383 Walker w; 3383 Walker w; 3384 if( pParse->cookieGoto ) return; 3384 if( pParse->cookieGoto ) return; 3385 if( (pParse->db->flags & SQLITE_FactorOutConst)!=0 ) return; | 3385 if( OptimizationDisabled(pParse->db, SQLITE_FactorOutConst) ) return; 3386 w.xExprCallback = evalConstExpr; 3386 w.xExprCallback = evalConstExpr; 3387 w.xSelectCallback = 0; 3387 w.xSelectCallback = 0; 3388 w.pParse = pParse; 3388 w.pParse = pParse; 3389 sqlite3WalkExpr(&w, pExpr); 3389 sqlite3WalkExpr(&w, pExpr); 3390 } 3390 } 3391 3391 3392 3392
Changes to src/main.c
1156 int (*xBusy)(void*,int), 1156 int (*xBusy)(void*,int), 1157 void *pArg 1157 void *pArg 1158 ){ 1158 ){ 1159 sqlite3_mutex_enter(db->mutex); 1159 sqlite3_mutex_enter(db->mutex); 1160 db->busyHandler.xFunc = xBusy; 1160 db->busyHandler.xFunc = xBusy; 1161 db->busyHandler.pArg = pArg; 1161 db->busyHandler.pArg = pArg; 1162 db->busyHandler.nBusy = 0; 1162 db->busyHandler.nBusy = 0; > 1163 db->busyTimeout = 0; 1163 sqlite3_mutex_leave(db->mutex); 1164 sqlite3_mutex_leave(db->mutex); 1164 return SQLITE_OK; 1165 return SQLITE_OK; 1165 } 1166 } 1166 1167 1167 #ifndef SQLITE_OMIT_PROGRESS_CALLBACK 1168 #ifndef SQLITE_OMIT_PROGRESS_CALLBACK 1168 /* 1169 /* 1169 ** This routine sets the progress callback for an Sqlite database to the 1170 ** This routine sets the progress callback for an Sqlite database to the ................................................................................................................................................................................ 1193 1194 1194 /* 1195 /* 1195 ** This routine installs a default busy handler that waits for the 1196 ** This routine installs a default busy handler that waits for the 1196 ** specified number of milliseconds before returning 0. 1197 ** specified number of milliseconds before returning 0. 1197 */ 1198 */ 1198 int sqlite3_busy_timeout(sqlite3 *db, int ms){ 1199 int sqlite3_busy_timeout(sqlite3 *db, int ms){ 1199 if( ms>0 ){ 1200 if( ms>0 ){ 1200 db->busyTimeout = ms; < 1201 sqlite3_busy_handler(db, sqliteDefaultBusyCallback, (void*)db); 1201 sqlite3_busy_handler(db, sqliteDefaultBusyCallback, (void*)db); > 1202 db->busyTimeout = ms; 1202 }else{ 1203 }else{ 1203 sqlite3_busy_handler(db, 0, 0); 1204 sqlite3_busy_handler(db, 0, 0); 1204 } 1205 } 1205 return SQLITE_OK; 1206 return SQLITE_OK; 1206 } 1207 } 1207 1208 1208 /* 1209 /* ................................................................................................................................................................................ 3064 ** operation N should be 0. The idea is that a test program (like the 3065 ** operation N should be 0. The idea is that a test program (like the 3065 ** SQL Logic Test or SLT test module) can run the same SQL multiple times 3066 ** SQL Logic Test or SLT test module) can run the same SQL multiple times 3066 ** with various optimizations disabled to verify that the same answer 3067 ** with various optimizations disabled to verify that the same answer 3067 ** is obtained in every case. 3068 ** is obtained in every case. 3068 */ 3069 */ 3069 case SQLITE_TESTCTRL_OPTIMIZATIONS: { 3070 case SQLITE_TESTCTRL_OPTIMIZATIONS: { 3070 sqlite3 *db = va_arg(ap, sqlite3*); 3071 sqlite3 *db = va_arg(ap, sqlite3*); 3071 int x = va_arg(ap,int); | 3072 db->dbOptFlags = (u8)(va_arg(ap, int) & 0xff); 3072 db->flags = (x & SQLITE_OptMask) | (db->flags & ~SQLITE_OptMask); < 3073 break; 3073 break; 3074 } 3074 } 3075 3075 3076 #ifdef SQLITE_N_KEYWORD 3076 #ifdef SQLITE_N_KEYWORD 3077 /* sqlite3_test_control(SQLITE_TESTCTRL_ISKEYWORD, const char *zWord) 3077 /* sqlite3_test_control(SQLITE_TESTCTRL_ISKEYWORD, const char *zWord) 3078 ** 3078 ** 3079 ** If zWord is a keyword recognized by the parser, then return the 3079 ** If zWord is a keyword recognized by the parser, then return the
Changes to src/os_unix.c
3006 static int seekAndRead(unixFile *id, sqlite3_int64 offset, void *pBuf, int cnt){ 3006 static int seekAndRead(unixFile *id, sqlite3_int64 offset, void *pBuf, int cnt){ 3007 int got; 3007 int got; 3008 int prior = 0; 3008 int prior = 0; 3009 #if (!defined(USE_PREAD) && !defined(USE_PREAD64)) 3009 #if (!defined(USE_PREAD) && !defined(USE_PREAD64)) 3010 i64 newOffset; 3010 i64 newOffset; 3011 #endif 3011 #endif 3012 TIMER_START; 3012 TIMER_START; > 3013 assert( cnt==(cnt&0x1ffff) ); > 3014 cnt &= 0x1ffff; 3013 do{ 3015 do{ 3014 #if defined(USE_PREAD) 3016 #if defined(USE_PREAD) 3015 got = osPread(id->h, pBuf, cnt, offset); 3017 got = osPread(id->h, pBuf, cnt, offset); 3016 SimulateIOError( got = -1 ); 3018 SimulateIOError( got = -1 ); 3017 #elif defined(USE_PREAD64) 3019 #elif defined(USE_PREAD64) 3018 got = osPread64(id->h, pBuf, cnt, offset); 3020 got = osPread64(id->h, pBuf, cnt, offset); 3019 SimulateIOError( got = -1 ); 3021 SimulateIOError( got = -1 ); ................................................................................................................................................................................ 3095 ** is set before returning. 3097 ** is set before returning. 3096 */ 3098 */ 3097 static int seekAndWrite(unixFile *id, i64 offset, const void *pBuf, int cnt){ 3099 static int seekAndWrite(unixFile *id, i64 offset, const void *pBuf, int cnt){ 3098 int got; 3100 int got; 3099 #if (!defined(USE_PREAD) && !defined(USE_PREAD64)) 3101 #if (!defined(USE_PREAD) && !defined(USE_PREAD64)) 3100 i64 newOffset; 3102 i64 newOffset; 3101 #endif 3103 #endif > 3104 assert( cnt==(cnt&0x1ffff) ); > 3105 cnt &= 0x1ffff; 3102 TIMER_START; 3106 TIMER_START; 3103 #if defined(USE_PREAD) 3107 #if defined(USE_PREAD) 3104 do{ got = osPwrite(id->h, pBuf, cnt, offset); }while( got<0 && errno==EINTR ); 3108 do{ got = osPwrite(id->h, pBuf, cnt, offset); }while( got<0 && errno==EINTR ); 3105 #elif defined(USE_PREAD64) 3109 #elif defined(USE_PREAD64) 3106 do{ got = osPwrite64(id->h, pBuf, cnt, offset);}while( got<0 && errno==EINTR); 3110 do{ got = osPwrite64(id->h, pBuf, cnt, offset);}while( got<0 && errno==EINTR); 3107 #else 3111 #else 3108 do{ 3112 do{
Changes to src/pager.c
2504 if( rc==SQLITE_OK ){ 2504 if( rc==SQLITE_OK ){ 2505 pPager->dbFileSize = nPage; 2505 pPager->dbFileSize = nPage; 2506 } 2506 } 2507 } 2507 } 2508 } 2508 } 2509 return rc; 2509 return rc; 2510 } 2510 } > 2511 > 2512 /* > 2513 ** Return a sanitized version of the sector-size of OS file pFile. The > 2514 ** return value is guaranteed to lie between 32 and MAX_SECTOR_SIZE. > 2515 */ > 2516 int sqlite3SectorSize(sqlite3_file *pFile){ > 2517 int iRet = sqlite3OsSectorSize(pFile); > 2518 if( iRet<32 ){ > 2519 iRet = 512; > 2520 }else if( iRet>MAX_SECTOR_SIZE ){ > 2521 assert( MAX_SECTOR_SIZE>=512 ); > 2522 iRet = MAX_SECTOR_SIZE; > 2523 } > 2524 return iRet; > 2525 } 2511 2526 2512 /* 2527 /* 2513 ** Set the value of the Pager.sectorSize variable for the given 2528 ** Set the value of the Pager.sectorSize variable for the given 2514 ** pager based on the value returned by the xSectorSize method 2529 ** pager based on the value returned by the xSectorSize method 2515 ** of the open database file. The sector size will be used used 2530 ** of the open database file. The sector size will be used used 2516 ** to determine the size and alignment of journal header and 2531 ** to determine the size and alignment of journal header and 2517 ** master journal pointers within created journal files. 2532 ** master journal pointers within created journal files. ................................................................................................................................................................................ 2540 SQLITE_IOCAP_POWERSAFE_OVERWRITE)!=0 2555 SQLITE_IOCAP_POWERSAFE_OVERWRITE)!=0 2541 ){ 2556 ){ 2542 /* Sector size doesn't matter for temporary files. Also, the file 2557 /* Sector size doesn't matter for temporary files. Also, the file 2543 ** may not have been opened yet, in which case the OsSectorSize() 2558 ** may not have been opened yet, in which case the OsSectorSize() 2544 ** call will segfault. */ 2559 ** call will segfault. */ 2545 pPager->sectorSize = 512; 2560 pPager->sectorSize = 512; 2546 }else{ 2561 }else{ 2547 pPager->sectorSize = sqlite3OsSectorSize(pPager->fd); < 2548 if( pPager->sectorSize<32 ){ < 2549 pPager->sectorSize = 512; | 2562 pPager->sectorSize = sqlite3SectorSize(pPager->fd); 2550 } < 2551 if( pPager->sectorSize>MAX_SECTOR_SIZE ){ < 2552 assert( MAX_SECTOR_SIZE>=512 ); < 2553 pPager->sectorSize = MAX_SECTOR_SIZE; < 2554 } < 2555 } 2563 } 2556 } 2564 } 2557 2565 2558 /* 2566 /* 2559 ** Playback the journal and thus restore the database file to 2567 ** Playback the journal and thus restore the database file to 2560 ** the state it was in before we started making changes. 2568 ** the state it was in before we started making changes. 2561 ** 2569 ** ................................................................................................................................................................................ 3464 ** retried. If it returns zero, then the SQLITE_BUSY error is 3472 ** retried. If it returns zero, then the SQLITE_BUSY error is 3465 ** returned to the caller of the pager API function. 3473 ** returned to the caller of the pager API function. 3466 */ 3474 */ 3467 void sqlite3PagerSetBusyhandler( 3475 void sqlite3PagerSetBusyhandler( 3468 Pager *pPager, /* Pager object */ 3476 Pager *pPager, /* Pager object */ 3469 int (*xBusyHandler)(void *), /* Pointer to busy-handler function */ 3477 int (*xBusyHandler)(void *), /* Pointer to busy-handler function */ 3470 void *pBusyHandlerArg /* Argument to pass to xBusyHandler */ 3478 void *pBusyHandlerArg /* Argument to pass to xBusyHandler */ 3471 ){ | 3479 ){ 3472 pPager->xBusyHandler = xBusyHandler; 3480 pPager->xBusyHandler = xBusyHandler; 3473 pPager->pBusyHandlerArg = pBusyHandlerArg; 3481 pPager->pBusyHandlerArg = pBusyHandlerArg; > 3482 > 3483 if( isOpen(pPager->fd) ){ > 3484 void **ap = (void **)&pPager->xBusyHandler; > 3485 assert( ((int(*)(void *))(ap[0]))==xBusyHandler ); > 3486 assert( ap[1]==pBusyHandlerArg ); > 3487 sqlite3OsFileControl(pPager->fd, SQLITE_FCNTL_BUSYHANDLER, (void *)ap); > 3488 } 3474 } 3489 } 3475 3490 3476 /* 3491 /* 3477 ** Change the page size used by the Pager object. The new page size 3492 ** Change the page size used by the Pager object. The new page size 3478 ** is passed in *pPageSize. 3493 ** is passed in *pPageSize. 3479 ** 3494 ** 3480 ** If the pager is in the error state when this function is called, it 3495 ** If the pager is in the error state when this function is called, it
Changes to src/pager.h
156 sqlite3_file *sqlite3PagerFile(Pager*); 156 sqlite3_file *sqlite3PagerFile(Pager*); 157 const char *sqlite3PagerJournalname(Pager*); 157 const char *sqlite3PagerJournalname(Pager*); 158 int sqlite3PagerNosync(Pager*); 158 int sqlite3PagerNosync(Pager*); 159 void *sqlite3PagerTempSpace(Pager*); 159 void *sqlite3PagerTempSpace(Pager*); 160 int sqlite3PagerIsMemdb(Pager*); 160 int sqlite3PagerIsMemdb(Pager*); 161 void sqlite3PagerCacheStat(Pager *, int, int, int *); 161 void sqlite3PagerCacheStat(Pager *, int, int, int *); 162 void sqlite3PagerClearCache(Pager *); 162 void sqlite3PagerClearCache(Pager *); > 163 int sqlite3SectorSize(sqlite3_file *); 163 164 164 /* Functions used to truncate the database file. */ 165 /* Functions used to truncate the database file. */ 165 void sqlite3PagerTruncateImage(Pager*,Pgno); 166 void sqlite3PagerTruncateImage(Pager*,Pgno); 166 167 167 #if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_WAL) 168 #if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_WAL) 168 void *sqlite3PagerCodec(DbPage *); 169 void *sqlite3PagerCodec(DbPage *); 169 #endif 170 #endif
Changes to src/pragma.c
353 ** connection. If it returns SQLITE_OK, then assume that the VFS 353 ** connection. If it returns SQLITE_OK, then assume that the VFS 354 ** handled the pragma and generate a no-op prepared statement. 354 ** handled the pragma and generate a no-op prepared statement. 355 */ 355 */ 356 aFcntl[0] = 0; 356 aFcntl[0] = 0; 357 aFcntl[1] = zLeft; 357 aFcntl[1] = zLeft; 358 aFcntl[2] = zRight; 358 aFcntl[2] = zRight; 359 aFcntl[3] = 0; 359 aFcntl[3] = 0; > 360 db->busyHandler.nBusy = 0; 360 rc = sqlite3_file_control(db, zDb, SQLITE_FCNTL_PRAGMA, (void*)aFcntl); 361 rc = sqlite3_file_control(db, zDb, SQLITE_FCNTL_PRAGMA, (void*)aFcntl); 361 if( rc==SQLITE_OK ){ 362 if( rc==SQLITE_OK ){ 362 if( aFcntl[0] ){ 363 if( aFcntl[0] ){ 363 int mem = ++pParse->nMem; 364 int mem = ++pParse->nMem; 364 sqlite3VdbeAddOp4(v, OP_String8, 0, mem, 0, aFcntl[0], 0); 365 sqlite3VdbeAddOp4(v, OP_String8, 0, mem, 0, aFcntl[0], 0); 365 sqlite3VdbeSetNumCols(v, 1); 366 sqlite3VdbeSetNumCols(v, 1); 366 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "result", SQLITE_STATIC); 367 sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "result", SQLITE_STATIC); ................................................................................................................................................................................ 1533 ** This pragma attempts to free as much memory as possible from the 1534 ** This pragma attempts to free as much memory as possible from the 1534 ** current database connection. 1535 ** current database connection. 1535 */ 1536 */ 1536 if( sqlite3StrICmp(zLeft, "shrink_memory")==0 ){ 1537 if( sqlite3StrICmp(zLeft, "shrink_memory")==0 ){ 1537 sqlite3_db_release_memory(db); 1538 sqlite3_db_release_memory(db); 1538 }else 1539 }else 1539 1540 > 1541 /* > 1542 ** PRAGMA busy_timeout > 1543 ** PRAGMA busy_timeout = N > 1544 ** > 1545 ** Call sqlite3_busy_timeout(db, N). Return the current timeout value > 1546 ** if one is set. If no busy handler or a different busy handler is set > 1547 ** then 0 is returned. Setting the busy_timeout to 0 or negative > 1548 ** disables the timeout. > 1549 */ > 1550 if( sqlite3StrICmp(zLeft, "busy_timeout")==0 ){ > 1551 if( zRight ){ > 1552 sqlite3_busy_timeout(db, sqlite3Atoi(zRight)); > 1553 } > 1554 returnSingleInt(pParse, "timeout", db->busyTimeout); > 1555 }else > 1556 1540 #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) 1557 #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) 1541 /* 1558 /* 1542 ** Report the current state of file logs for all databases 1559 ** Report the current state of file logs for all databases 1543 */ 1560 */ 1544 if( sqlite3StrICmp(zLeft, "lock_status")==0 ){ 1561 if( sqlite3StrICmp(zLeft, "lock_status")==0 ){ 1545 static const char *const azLockName[] = { 1562 static const char *const azLockName[] = { 1546 "unlocked", "shared", "reserved", "pending", "exclusive" 1563 "unlocked", "shared", "reserved", "pending", "exclusive"
Changes to src/select.c
2805 struct SrcList_item *pSubitem; /* The subquery */ 2805 struct SrcList_item *pSubitem; /* The subquery */ 2806 sqlite3 *db = pParse->db; 2806 sqlite3 *db = pParse->db; 2807 2807 2808 /* Check to see if flattening is permitted. Return 0 if not. 2808 /* Check to see if flattening is permitted. Return 0 if not. 2809 */ 2809 */ 2810 assert( p!=0 ); 2810 assert( p!=0 ); 2811 assert( p->pPrior==0 ); /* Unable to flatten compound queries */ 2811 assert( p->pPrior==0 ); /* Unable to flatten compound queries */ 2812 if( db->flags & SQLITE_QueryFlattener ) return 0; | 2812 if( OptimizationDisabled(db, SQLITE_QueryFlattener) ) return 0; 2813 pSrc = p->pSrc; 2813 pSrc = p->pSrc; 2814 assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); 2814 assert( pSrc && iFrom>=0 && iFrom<pSrc->nSrc ); 2815 pSubitem = &pSrc->a[iFrom]; 2815 pSubitem = &pSrc->a[iFrom]; 2816 iParent = pSubitem->iCursor; 2816 iParent = pSubitem->iCursor; 2817 pSub = pSubitem->pSelect; 2817 pSub = pSubitem->pSelect; 2818 assert( pSub!=0 ); 2818 assert( pSub!=0 ); 2819 if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */ 2819 if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */ ................................................................................................................................................................................ 4008 ** identical, then disable the ORDER BY clause since the GROUP BY 4008 ** identical, then disable the ORDER BY clause since the GROUP BY 4009 ** will cause elements to come out in the correct order. This is 4009 ** will cause elements to come out in the correct order. This is 4010 ** an optimization - the correct answer should result regardless. 4010 ** an optimization - the correct answer should result regardless. 4011 ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER 4011 ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER 4012 ** to disable this optimization for testing purposes. 4012 ** to disable this optimization for testing purposes. 4013 */ 4013 */ 4014 if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy)==0 4014 if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy)==0 4015 && (db->flags & SQLITE_GroupByOrder)==0 ){ | 4015 && OptimizationEnabled(db, SQLITE_GroupByOrder) ){ 4016 pOrderBy = 0; 4016 pOrderBy = 0; 4017 } 4017 } 4018 4018 4019 /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 4019 /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 4020 ** if the select-list is the same as the ORDER BY list, then this query 4020 ** if the select-list is the same as the ORDER BY list, then this query 4021 ** can be rewritten as a GROUP BY. In other words, this: 4021 ** can be rewritten as a GROUP BY. In other words, this: 4022 ** 4022 ** ................................................................................................................................................................................ 4502 resetAccumulator(pParse, &sAggInfo); 4502 resetAccumulator(pParse, &sAggInfo); 4503 pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax,0,flag,0); 4503 pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax,0,flag,0); 4504 if( pWInfo==0 ){ 4504 if( pWInfo==0 ){ 4505 sqlite3ExprListDelete(db, pDel); 4505 sqlite3ExprListDelete(db, pDel); 4506 goto select_end; 4506 goto select_end; 4507 } 4507 } 4508 updateAccumulator(pParse, &sAggInfo); 4508 updateAccumulator(pParse, &sAggInfo); > 4509 assert( pMinMax==0 || pMinMax->nExpr==1 ); 4509 if( pWInfo->nOBSat>0 ){ 4510 if( pWInfo->nOBSat>0 ){ 4510 sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak); 4511 sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak); 4511 VdbeComment((v, "%s() by index", 4512 VdbeComment((v, "%s() by index", 4512 (flag==WHERE_ORDERBY_MIN?"min":"max"))); 4513 (flag==WHERE_ORDERBY_MIN?"min":"max"))); 4513 } 4514 } 4514 sqlite3WhereEnd(pWInfo); 4515 sqlite3WhereEnd(pWInfo); 4515 finalizeAggFunctions(pParse, &sAggInfo); 4516 finalizeAggFunctions(pParse, &sAggInfo);
Changes to src/sqlite.h.in
848 ** prepared statement. ^If the [SQLITE_FCNTL_PRAGMA] file control returns 848 ** prepared statement. ^If the [SQLITE_FCNTL_PRAGMA] file control returns 849 ** any result code other than [SQLITE_OK] or [SQLITE_NOTFOUND], that means 849 ** any result code other than [SQLITE_OK] or [SQLITE_NOTFOUND], that means 850 ** that the VFS encountered an error while handling the [PRAGMA] and the 850 ** that the VFS encountered an error while handling the [PRAGMA] and the 851 ** compilation of the PRAGMA fails with an error. ^The [SQLITE_FCNTL_PRAGMA] 851 ** compilation of the PRAGMA fails with an error. ^The [SQLITE_FCNTL_PRAGMA] 852 ** file control occurs at the beginning of pragma statement analysis and so 852 ** file control occurs at the beginning of pragma statement analysis and so 853 ** it is able to override built-in [PRAGMA] statements. 853 ** it is able to override built-in [PRAGMA] statements. 854 ** </ul> 854 ** </ul> > 855 ** > 856 ** <li>[[SQLITE_FCNTL_BUSYHANDLER]] > 857 ** ^This file-control may be invoked by SQLite on the database file handle > 858 ** shortly after it is opened in order to provide a custom VFS with access > 859 ** to the connections busy-handler callback. The argument is of type (void **) > 860 ** - an array of two (void *) values. The first (void *) actually points > 861 ** to a function of type (int (*)(void *)). In order to invoke the connections > 862 ** busy-handler, this function should be invoked with the second (void *) in > 863 ** the array as the only argument. If it returns non-zero, then the operation > 864 ** should be retried. If it returns zero, the custom VFS should abandon the > 865 ** current operation. 855 */ 866 */ 856 #define SQLITE_FCNTL_LOCKSTATE 1 867 #define SQLITE_FCNTL_LOCKSTATE 1 857 #define SQLITE_GET_LOCKPROXYFILE 2 868 #define SQLITE_GET_LOCKPROXYFILE 2 858 #define SQLITE_SET_LOCKPROXYFILE 3 869 #define SQLITE_SET_LOCKPROXYFILE 3 859 #define SQLITE_LAST_ERRNO 4 870 #define SQLITE_LAST_ERRNO 4 860 #define SQLITE_FCNTL_SIZE_HINT 5 871 #define SQLITE_FCNTL_SIZE_HINT 5 861 #define SQLITE_FCNTL_CHUNK_SIZE 6 872 #define SQLITE_FCNTL_CHUNK_SIZE 6 ................................................................................................................................................................................ 863 #define SQLITE_FCNTL_SYNC_OMITTED 8 874 #define SQLITE_FCNTL_SYNC_OMITTED 8 864 #define SQLITE_FCNTL_WIN32_AV_RETRY 9 875 #define SQLITE_FCNTL_WIN32_AV_RETRY 9 865 #define SQLITE_FCNTL_PERSIST_WAL 10 876 #define SQLITE_FCNTL_PERSIST_WAL 10 866 #define SQLITE_FCNTL_OVERWRITE 11 877 #define SQLITE_FCNTL_OVERWRITE 11 867 #define SQLITE_FCNTL_VFSNAME 12 878 #define SQLITE_FCNTL_VFSNAME 12 868 #define SQLITE_FCNTL_POWERSAFE_OVERWRITE 13 879 #define SQLITE_FCNTL_POWERSAFE_OVERWRITE 13 869 #define SQLITE_FCNTL_PRAGMA 14 880 #define SQLITE_FCNTL_PRAGMA 14 > 881 #define SQLITE_FCNTL_BUSYHANDLER 15 870 882 871 /* 883 /* 872 ** CAPI3REF: Mutex Handle 884 ** CAPI3REF: Mutex Handle 873 ** 885 ** 874 ** The mutex module within SQLite defines [sqlite3_mutex] to be an 886 ** The mutex module within SQLite defines [sqlite3_mutex] to be an 875 ** abstract type for a mutex object. The SQLite core never looks 887 ** abstract type for a mutex object. The SQLite core never looks 876 ** at the internal representation of an [sqlite3_mutex]. It only 888 ** at the internal representation of an [sqlite3_mutex]. It only
Changes to src/sqliteInt.h
823 Db *aDb; /* All backends */ 823 Db *aDb; /* All backends */ 824 int nDb; /* Number of backends currently in use */ 824 int nDb; /* Number of backends currently in use */ 825 int flags; /* Miscellaneous flags. See below */ 825 int flags; /* Miscellaneous flags. See below */ 826 i64 lastRowid; /* ROWID of most recent insert (see above) */ 826 i64 lastRowid; /* ROWID of most recent insert (see above) */ 827 unsigned int openFlags; /* Flags passed to sqlite3_vfs.xOpen() */ 827 unsigned int openFlags; /* Flags passed to sqlite3_vfs.xOpen() */ 828 int errCode; /* Most recent error code (SQLITE_*) */ 828 int errCode; /* Most recent error code (SQLITE_*) */ 829 int errMask; /* & result codes with this before returning */ 829 int errMask; /* & result codes with this before returning */ > 830 u8 dbOptFlags; /* Flags to enable/disable optimizations */ 830 u8 autoCommit; /* The auto-commit flag. */ 831 u8 autoCommit; /* The auto-commit flag. */ 831 u8 temp_store; /* 1: file 2: memory 0: default */ 832 u8 temp_store; /* 1: file 2: memory 0: default */ 832 u8 mallocFailed; /* True if we have seen a malloc failure */ 833 u8 mallocFailed; /* True if we have seen a malloc failure */ 833 u8 dfltLockMode; /* Default locking-mode for attached dbs */ 834 u8 dfltLockMode; /* Default locking-mode for attached dbs */ 834 signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ 835 signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ 835 u8 suppressErr; /* Do not issue error messages if true */ 836 u8 suppressErr; /* Do not issue error messages if true */ 836 u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ 837 u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ ................................................................................................................................................................................ 927 ** A macro to discover the encoding of a database. 928 ** A macro to discover the encoding of a database. 928 */ 929 */ 929 #define ENC(db) ((db)->aDb[0].pSchema->enc) 930 #define ENC(db) ((db)->aDb[0].pSchema->enc) 930 931 931 /* 932 /* 932 ** Possible values for the sqlite3.flags. 933 ** Possible values for the sqlite3.flags. 933 */ 934 */ 934 #define SQLITE_VdbeTrace 0x00000100 /* True to trace VDBE execution */ | 935 #define SQLITE_VdbeTrace 0x00000001 /* True to trace VDBE execution */ 935 #define SQLITE_InternChanges 0x00000200 /* Uncommitted Hash table changes */ | 936 #define SQLITE_InternChanges 0x00000002 /* Uncommitted Hash table changes */ 936 #define SQLITE_FullColNames 0x00000400 /* Show full column names on SELECT */ | 937 #define SQLITE_FullColNames 0x00000004 /* Show full column names on SELECT */ 937 #define SQLITE_ShortColNames 0x00000800 /* Show short columns names */ | 938 #define SQLITE_ShortColNames 0x00000008 /* Show short columns names */ 938 #define SQLITE_CountRows 0x00001000 /* Count rows changed by INSERT, */ | 939 #define SQLITE_CountRows 0x00000010 /* Count rows changed by INSERT, */ 939 /* DELETE, or UPDATE and return */ 940 /* DELETE, or UPDATE and return */ 940 /* the count using a callback. */ 941 /* the count using a callback. */ 941 #define SQLITE_NullCallback 0x00002000 /* Invoke the callback once if the */ | 942 #define SQLITE_NullCallback 0x00000020 /* Invoke the callback once if the */ 942 /* result set is empty */ 943 /* result set is empty */ 943 #define SQLITE_SqlTrace 0x00004000 /* Debug print SQL as it executes */ | 944 #define SQLITE_SqlTrace 0x00000040 /* Debug print SQL as it executes */ 944 #define SQLITE_VdbeListing 0x00008000 /* Debug listings of VDBE programs */ | 945 #define SQLITE_VdbeListing 0x00000080 /* Debug listings of VDBE programs */ 945 #define SQLITE_WriteSchema 0x00010000 /* OK to update SQLITE_MASTER */ | 946 #define SQLITE_WriteSchema 0x00000100 /* OK to update SQLITE_MASTER */ 946 /* 0x00020000 Unused */ | 947 /* 0x00000200 Unused */ 947 #define SQLITE_IgnoreChecks 0x00040000 /* Do not enforce check constraints */ | 948 #define SQLITE_IgnoreChecks 0x00000400 /* Do not enforce check constraints */ 948 #define SQLITE_ReadUncommitted 0x0080000 /* For shared-cache mode */ | 949 #define SQLITE_ReadUncommitted 0x0000800 /* For shared-cache mode */ 949 #define SQLITE_LegacyFileFmt 0x00100000 /* Create new databases in format 1 */ | 950 #define SQLITE_LegacyFileFmt 0x00001000 /* Create new databases in format 1 */ 950 #define SQLITE_FullFSync 0x00200000 /* Use full fsync on the backend */ | 951 #define SQLITE_FullFSync 0x00002000 /* Use full fsync on the backend */ 951 #define SQLITE_CkptFullFSync 0x00400000 /* Use full fsync for checkpoint */ | 952 #define SQLITE_CkptFullFSync 0x00004000 /* Use full fsync for checkpoint */ 952 #define SQLITE_RecoveryMode 0x00800000 /* Ignore schema errors */ | 953 #define SQLITE_RecoveryMode 0x00008000 /* Ignore schema errors */ 953 #define SQLITE_ReverseOrder 0x01000000 /* Reverse unordered SELECTs */ | 954 #define SQLITE_ReverseOrder 0x00010000 /* Reverse unordered SELECTs */ 954 #define SQLITE_RecTriggers 0x02000000 /* Enable recursive triggers */ | 955 #define SQLITE_RecTriggers 0x00020000 /* Enable recursive triggers */ 955 #define SQLITE_ForeignKeys 0x04000000 /* Enforce foreign key constraints */ | 956 #define SQLITE_ForeignKeys 0x00040000 /* Enforce foreign key constraints */ 956 #define SQLITE_AutoIndex 0x08000000 /* Enable automatic indexes */ | 957 #define SQLITE_AutoIndex 0x00080000 /* Enable automatic indexes */ 957 #define SQLITE_PreferBuiltin 0x10000000 /* Preference to built-in funcs */ | 958 #define SQLITE_PreferBuiltin 0x00100000 /* Preference to built-in funcs */ 958 #define SQLITE_LoadExtension 0x20000000 /* Enable load_extension */ | 959 #define SQLITE_LoadExtension 0x00200000 /* Enable load_extension */ 959 #define SQLITE_EnableTrigger 0x40000000 /* True to enable triggers */ | 960 #define SQLITE_EnableTrigger 0x00400000 /* True to enable triggers */ 960 961 961 /* 962 /* 962 ** Bits of the sqlite3.flags field that are used by the | 963 ** Bits of the sqlite3.dbOptFlags field that are used by the 963 ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface. | 964 ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to 964 ** These must be the low-order bits of the flags field. | 965 ** selectively disable various optimizations. 965 */ 966 */ 966 #define SQLITE_QueryFlattener 0x01 /* Disable query flattening */ | 967 #define SQLITE_QueryFlattener 0x0001 /* Query flattening */ 967 #define SQLITE_ColumnCache 0x02 /* Disable the column cache */ | 968 #define SQLITE_ColumnCache 0x0002 /* Column cache */ 968 #define SQLITE_GroupByOrder 0x04 /* Disable GROUPBY cover of ORDERBY */ | 969 #define SQLITE_GroupByOrder 0x0004 /* GROUPBY cover of ORDERBY */ 969 #define SQLITE_FactorOutConst 0x08 /* Disable factoring out constants */ | 970 #define SQLITE_FactorOutConst 0x0008 /* Constant factoring */ 970 #define SQLITE_IdxRealAsInt 0x10 /* Store REAL as INT in indices */ | 971 #define SQLITE_IdxRealAsInt 0x0010 /* Store REAL as INT in indices */ 971 #define SQLITE_DistinctOpt 0x20 /* DISTINCT using indexes */ | 972 #define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ 972 #define SQLITE_CoverIdxScan 0x40 /* Disable covering index scans */ | 973 #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ 973 #define SQLITE_OptMask 0xff /* Mask of all disablable opts */ | 974 #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ > 975 #define SQLITE_AllOpts 0x00ff /* All optimizations */ > 976 > 977 /* > 978 ** Macros for testing whether or not optimizations are enabled or disabled. > 979 */ > 980 #ifndef SQLITE_OMIT_BUILTIN_TEST > 981 #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) > 982 #define OptimizationEnabled(db, mask) (((db)->dbOptFlags&(mask))==0) > 983 #else > 984 #define OptimizationDisabled(db, mask) 0 > 985 #define OptimizationEnabled(db, mask) 1 > 986 #endif 974 987 975 /* 988 /* 976 ** Possible values for the sqlite.magic field. 989 ** Possible values for the sqlite.magic field. 977 ** The numbers are obtained at random and have no special meaning, other 990 ** The numbers are obtained at random and have no special meaning, other 978 ** than being distinct from one another. 991 ** than being distinct from one another. 979 */ 992 */ 980 #define SQLITE_MAGIC_OPEN 0xa029a697 /* Database is open */ 993 #define SQLITE_MAGIC_OPEN 0xa029a697 /* Database is open */ ................................................................................................................................................................................ 1902 ** Within the union, pIdx is only used when wsFlags&WHERE_INDEXED is true. 1915 ** Within the union, pIdx is only used when wsFlags&WHERE_INDEXED is true. 1903 ** pTerm is only used when wsFlags&WHERE_MULTI_OR is true. And pVtabIdx 1916 ** pTerm is only used when wsFlags&WHERE_MULTI_OR is true. And pVtabIdx 1904 ** is only used when wsFlags&WHERE_VIRTUALTABLE is true. It is never the 1917 ** is only used when wsFlags&WHERE_VIRTUALTABLE is true. It is never the 1905 ** case that more than one of these conditions is true. 1918 ** case that more than one of these conditions is true. 1906 */ 1919 */ 1907 struct WherePlan { 1920 struct WherePlan { 1908 u32 wsFlags; /* WHERE_* flags that describe the strategy */ 1921 u32 wsFlags; /* WHERE_* flags that describe the strategy */ 1909 u32 nEq; /* Number of == constraints */ | 1922 u16 nEq; /* Number of == constraints */ > 1923 u16 nOBSat; /* Number of ORDER BY terms satisfied */ 1910 double nRow; /* Estimated number of rows (for EQP) */ 1924 double nRow; /* Estimated number of rows (for EQP) */ 1911 union { 1925 union { 1912 Index *pIdx; /* Index when WHERE_INDEXED is true */ 1926 Index *pIdx; /* Index when WHERE_INDEXED is true */ 1913 struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ 1927 struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ 1914 sqlite3_index_info *pVtabIdx; /* Virtual table index to use */ 1928 sqlite3_index_info *pVtabIdx; /* Virtual table index to use */ 1915 } u; 1929 } u; 1916 }; 1930 };
Changes to src/tclsqlite.c
49 #undef TCL_STORAGE_CLASS 49 #undef TCL_STORAGE_CLASS 50 #define TCL_STORAGE_CLASS DLLEXPORT 50 #define TCL_STORAGE_CLASS DLLEXPORT 51 #endif /* BUILD_sqlite */ 51 #endif /* BUILD_sqlite */ 52 52 53 #define NUM_PREPARED_STMTS 10 53 #define NUM_PREPARED_STMTS 10 54 #define MAX_PREPARED_STMTS 100 54 #define MAX_PREPARED_STMTS 100 55 55 56 /* | 56 /* Forward declaration */ 57 ** If TCL uses UTF-8 and SQLite is configured to use iso8859, then we | 57 typedef struct SqliteDb SqliteDb; 58 ** have to do a translation when going between the two. Set the < 59 ** UTF_TRANSLATION_NEEDED macro to indicate that we need to do < 60 ** this translation. < 61 */ < 62 #if defined(TCL_UTF_MAX) && !defined(SQLITE_UTF8) < 63 # define UTF_TRANSLATION_NEEDED 1 < 64 #endif < 65 58 66 /* 59 /* 67 ** New SQL functions can be created as TCL scripts. Each such function 60 ** New SQL functions can be created as TCL scripts. Each such function 68 ** is described by an instance of the following structure. 61 ** is described by an instance of the following structure. 69 */ 62 */ 70 typedef struct SqlFunc SqlFunc; 63 typedef struct SqlFunc SqlFunc; 71 struct SqlFunc { 64 struct SqlFunc { 72 Tcl_Interp *interp; /* The TCL interpret to execute the function */ 65 Tcl_Interp *interp; /* The TCL interpret to execute the function */ 73 Tcl_Obj *pScript; /* The Tcl_Obj representation of the script */ 66 Tcl_Obj *pScript; /* The Tcl_Obj representation of the script */ > 67 SqliteDb *pDb; /* Database connection that owns this function */ 74 int useEvalObjv; /* True if it is safe to use Tcl_EvalObjv */ 68 int useEvalObjv; /* True if it is safe to use Tcl_EvalObjv */ 75 char *zName; /* Name of this function */ 69 char *zName; /* Name of this function */ 76 SqlFunc *pNext; /* Next function on the list of them all */ 70 SqlFunc *pNext; /* Next function on the list of them all */ 77 }; 71 }; 78 72 79 /* 73 /* 80 ** New collation sequences function can be created as TCL scripts. Each such 74 ** New collation sequences function can be created as TCL scripts. Each such ................................................................................................................................................................................ 109 ** that has been opened by the SQLite TCL interface. 103 ** that has been opened by the SQLite TCL interface. 110 ** 104 ** 111 ** If this module is built with SQLITE_TEST defined (to create the SQLite 105 ** If this module is built with SQLITE_TEST defined (to create the SQLite 112 ** testfixture executable), then it may be configured to use either 106 ** testfixture executable), then it may be configured to use either 113 ** sqlite3_prepare_v2() or sqlite3_prepare() to prepare SQL statements. 107 ** sqlite3_prepare_v2() or sqlite3_prepare() to prepare SQL statements. 114 ** If SqliteDb.bLegacyPrepare is true, sqlite3_prepare() is used. 108 ** If SqliteDb.bLegacyPrepare is true, sqlite3_prepare() is used. 115 */ 109 */ 116 typedef struct SqliteDb SqliteDb; < 117 struct SqliteDb { 110 struct SqliteDb { 118 sqlite3 *db; /* The "real" database structure. MUST BE FIRST */ 111 sqlite3 *db; /* The "real" database structure. MUST BE FIRST */ 119 Tcl_Interp *interp; /* The interpreter used for this database */ 112 Tcl_Interp *interp; /* The interpreter used for this database */ 120 char *zBusy; /* The busy callback routine */ 113 char *zBusy; /* The busy callback routine */ 121 char *zCommit; /* The commit hook callback routine */ 114 char *zCommit; /* The commit hook callback routine */ 122 char *zTrace; /* The trace callback routine */ 115 char *zTrace; /* The trace callback routine */ 123 char *zProfile; /* The profile callback routine */ 116 char *zProfile; /* The profile callback routine */ ................................................................................................................................................................................ 427 for(p=pDb->pFunc; p; p=p->pNext){ 420 for(p=pDb->pFunc; p; p=p->pNext){ 428 if( strcmp(p->zName, pNew->zName)==0 ){ 421 if( strcmp(p->zName, pNew->zName)==0 ){ 429 Tcl_Free((char*)pNew); 422 Tcl_Free((char*)pNew); 430 return p; 423 return p; 431 } 424 } 432 } 425 } 433 pNew->interp = pDb->interp; 426 pNew->interp = pDb->interp; > 427 pNew->pDb = pDb; 434 pNew->pScript = 0; 428 pNew->pScript = 0; 435 pNew->pNext = pDb->pFunc; 429 pNew->pNext = pDb->pFunc; 436 pDb->pFunc = pNew; 430 pDb->pFunc = pNew; 437 return pNew; 431 return pNew; 438 } 432 } 439 433 440 /* 434 /* ................................................................................................................................................................................ 474 SqliteDb *pDb = (SqliteDb*)db; 468 SqliteDb *pDb = (SqliteDb*)db; 475 flushStmtCache(pDb); 469 flushStmtCache(pDb); 476 closeIncrblobChannels(pDb); 470 closeIncrblobChannels(pDb); 477 sqlite3_close(pDb->db); 471 sqlite3_close(pDb->db); 478 while( pDb->pFunc ){ 472 while( pDb->pFunc ){ 479 SqlFunc *pFunc = pDb->pFunc; 473 SqlFunc *pFunc = pDb->pFunc; 480 pDb->pFunc = pFunc->pNext; 474 pDb->pFunc = pFunc->pNext; > 475 assert( pFunc->pDb==pDb ); 481 Tcl_DecrRefCount(pFunc->pScript); 476 Tcl_DecrRefCount(pFunc->pScript); 482 Tcl_Free((char*)pFunc); 477 Tcl_Free((char*)pFunc); 483 } 478 } 484 while( pDb->pCollate ){ 479 while( pDb->pCollate ){ 485 SqlCollate *pCollate = pDb->pCollate; 480 SqlCollate *pCollate = pDb->pCollate; 486 pDb->pCollate = pCollate->pNext; 481 pDb->pCollate = pCollate->pNext; 487 Tcl_Free((char*)pCollate); 482 Tcl_Free((char*)pCollate); ................................................................................................................................................................................ 790 } 785 } 791 case SQLITE_FLOAT: { 786 case SQLITE_FLOAT: { 792 double r = sqlite3_value_double(pIn); 787 double r = sqlite3_value_double(pIn); 793 pVal = Tcl_NewDoubleObj(r); 788 pVal = Tcl_NewDoubleObj(r); 794 break; 789 break; 795 } 790 } 796 case SQLITE_NULL: { 791 case SQLITE_NULL: { 797 pVal = Tcl_NewStringObj("", 0); | 792 pVal = Tcl_NewStringObj(p->pDb->zNull, -1); 798 break; 793 break; 799 } 794 } 800 default: { 795 default: { 801 int bytes = sqlite3_value_bytes(pIn); 796 int bytes = sqlite3_value_bytes(pIn); 802 pVal = Tcl_NewStringObj((char *)sqlite3_value_text(pIn), bytes); 797 pVal = Tcl_NewStringObj((char *)sqlite3_value_text(pIn), bytes); 803 break; 798 break; 804 } 799 } ................................................................................................................................................................................ 929 }else{ 924 }else{ 930 rc = 999; 925 rc = 999; 931 } 926 } 932 return rc; 927 return rc; 933 } 928 } 934 #endif /* SQLITE_OMIT_AUTHORIZATION */ 929 #endif /* SQLITE_OMIT_AUTHORIZATION */ 935 930 936 /* < 937 ** zText is a pointer to text obtained via an sqlite3_result_text() < 938 ** or similar interface. This routine returns a Tcl string object, < 939 ** reference count set to 0, containing the text. If a translation < 940 ** between iso8859 and UTF-8 is required, it is preformed. < 941 */ < 942 static Tcl_Obj *dbTextToObj(char const *zText){ < 943 Tcl_Obj *pVal; < 944 #ifdef UTF_TRANSLATION_NEEDED < 945 Tcl_DString dCol; < 946 Tcl_DStringInit(&dCol); < 947 Tcl_ExternalToUtfDString(NULL, zText, -1, &dCol); < 948 pVal = Tcl_NewStringObj(Tcl_DStringValue(&dCol), -1); < 949 Tcl_DStringFree(&dCol); < 950 #else < 951 pVal = Tcl_NewStringObj(zText, -1); < 952 #endif < 953 return pVal; < 954 } < 955 < 956 /* 931 /* 957 ** This routine reads a line of text from FILE in, stores 932 ** This routine reads a line of text from FILE in, stores 958 ** the text in memory obtained from malloc() and returns a pointer 933 ** the text in memory obtained from malloc() and returns a pointer 959 ** to the text. NULL is returned at end of file, or if malloc() 934 ** to the text. NULL is returned at end of file, or if malloc() 960 ** fails. 935 ** fails. 961 ** 936 ** 962 ** The interface is like "readline" but no command-line editing 937 ** The interface is like "readline" but no command-line editing ................................................................................................................................................................................ 1136 1111 1137 /* If no prepared statement was found. Compile the SQL text. Also allocate 1112 /* If no prepared statement was found. Compile the SQL text. Also allocate 1138 ** a new SqlPreparedStmt structure. */ 1113 ** a new SqlPreparedStmt structure. */ 1139 if( pPreStmt==0 ){ 1114 if( pPreStmt==0 ){ 1140 int nByte; 1115 int nByte; 1141 1116 1142 if( SQLITE_OK!=dbPrepare(pDb, zSql, &pStmt, pzOut) ){ 1117 if( SQLITE_OK!=dbPrepare(pDb, zSql, &pStmt, pzOut) ){ 1143 Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db))); | 1118 Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3_errmsg(pDb->db), -1)); 1144 return TCL_ERROR; 1119 return TCL_ERROR; 1145 } 1120 } 1146 if( pStmt==0 ){ 1121 if( pStmt==0 ){ 1147 if( SQLITE_OK!=sqlite3_errcode(pDb->db) ){ 1122 if( SQLITE_OK!=sqlite3_errcode(pDb->db) ){ 1148 /* A compile-time error in the statement. */ 1123 /* A compile-time error in the statement. */ 1149 Tcl_SetObjResult(interp, dbTextToObj(sqlite3_errmsg(pDb->db))); | 1124 Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3_errmsg(pDb->db), -1)); 1150 return TCL_ERROR; 1125 return TCL_ERROR; 1151 }else{ 1126 }else{ 1152 /* The statement was a no-op. Continue to the next statement 1127 /* The statement was a no-op. Continue to the next statement 1153 ** in the SQL string. 1128 ** in the SQL string. 1154 */ 1129 */ 1155 return TCL_OK; 1130 return TCL_OK; 1156 } 1131 } ................................................................................................................................................................................ 1361 int nCol; /* Number of columns returned by pStmt */ 1336 int nCol; /* Number of columns returned by pStmt */ 1362 Tcl_Obj **apColName = 0; /* Array of column names */ 1337 Tcl_Obj **apColName = 0; /* Array of column names */ 1363 1338 1364 p->nCol = nCol = sqlite3_column_count(pStmt); 1339 p->nCol = nCol = sqlite3_column_count(pStmt); 1365 if( nCol>0 && (papColName || p->pArray) ){ 1340 if( nCol>0 && (papColName || p->pArray) ){ 1366 apColName = (Tcl_Obj**)Tcl_Alloc( sizeof(Tcl_Obj*)*nCol ); 1341 apColName = (Tcl_Obj**)Tcl_Alloc( sizeof(Tcl_Obj*)*nCol ); 1367 for(i=0; i<nCol; i++){ 1342 for(i=0; i<nCol; i++){ 1368 apColName[i] = dbTextToObj(sqlite3_column_name(pStmt,i)); | 1343 apColName[i] = Tcl_NewStringObj(sqlite3_column_name(pStmt,i), -1); 1369 Tcl_IncrRefCount(apColName[i]); 1344 Tcl_IncrRefCount(apColName[i]); 1370 } 1345 } 1371 p->apColName = apColName; 1346 p->apColName = apColName; 1372 } 1347 } 1373 1348 1374 /* If results are being stored in an array variable, then create 1349 /* If results are being stored in an array variable, then create 1375 ** the array(*) entry for that array 1350 ** the array(*) entry for that array ................................................................................................................................................................................ 1448 ** interface, retry prepare()/step() on the same SQL statement. 1423 ** interface, retry prepare()/step() on the same SQL statement. 1449 ** This only happens once. If there is a second SQLITE_SCHEMA 1424 ** This only happens once. If there is a second SQLITE_SCHEMA 1450 ** error, the error will be returned to the caller. */ 1425 ** error, the error will be returned to the caller. */ 1451 p->zSql = zPrevSql; 1426 p->zSql = zPrevSql; 1452 continue; 1427 continue; 1453 } 1428 } 1454 #endif 1429 #endif 1455 Tcl_SetObjResult(pDb->interp, dbTextToObj(sqlite3_errmsg(pDb->db))); | 1430 Tcl_SetObjResult(pDb->interp, > 1431 Tcl_NewStringObj(sqlite3_errmsg(pDb->db), -1)); 1456 return TCL_ERROR; 1432 return TCL_ERROR; 1457 }else{ 1433 }else{ 1458 dbReleaseStmt(pDb, pPreStmt, 0); 1434 dbReleaseStmt(pDb, pPreStmt, 0); 1459 } 1435 } 1460 } 1436 } 1461 } 1437 } 1462 1438 ................................................................................................................................................................................ 1505 return Tcl_NewWideIntObj(v); 1481 return Tcl_NewWideIntObj(v); 1506 } 1482 } 1507 } 1483 } 1508 case SQLITE_FLOAT: { 1484 case SQLITE_FLOAT: { 1509 return Tcl_NewDoubleObj(sqlite3_column_double(pStmt, iCol)); 1485 return Tcl_NewDoubleObj(sqlite3_column_double(pStmt, iCol)); 1510 } 1486 } 1511 case SQLITE_NULL: { 1487 case SQLITE_NULL: { 1512 return dbTextToObj(p->pDb->zNull); | 1488 return Tcl_NewStringObj(p->pDb->zNull, -1); 1513 } 1489 } 1514 } 1490 } 1515 1491 1516 return dbTextToObj((char *)sqlite3_column_text(pStmt, iCol)); | 1492 return Tcl_NewStringObj((char*)sqlite3_column_text(pStmt, iCol), -1); 1517 } 1493 } 1518 1494 1519 /* 1495 /* 1520 ** If using Tcl version 8.6 or greater, use the NR functions to avoid 1496 ** If using Tcl version 8.6 or greater, use the NR functions to avoid 1521 ** recursive evalution of scripts by the [db eval] and [db trans] 1497 ** recursive evalution of scripts by the [db eval] and [db trans] 1522 ** commands. Even if the headers used while compiling the extension 1498 ** commands. Even if the headers used while compiling the extension 1523 ** are 8.6 or newer, the code still tests the Tcl version at runtime. 1499 ** are 8.6 or newer, the code still tests the Tcl version at runtime. ................................................................................................................................................................................ 2363 Tcl_AppendResult(interp, "incrblob not available in this build", 0); 2339 Tcl_AppendResult(interp, "incrblob not available in this build", 0); 2364 return TCL_ERROR; 2340 return TCL_ERROR; 2365 #else 2341 #else 2366 int isReadonly = 0; 2342 int isReadonly = 0; 2367 const char *zDb = "main"; 2343 const char *zDb = "main"; 2368 const char *zTable; 2344 const char *zTable; 2369 const char *zColumn; 2345 const char *zColumn; 2370 sqlite_int64 iRow; | 2346 Tcl_WideInt iRow; 2371 2347 2372 /* Check for the -readonly option */ 2348 /* Check for the -readonly option */ 2373 if( objc>3 && strcmp(Tcl_GetString(objv[2]), "-readonly")==0 ){ 2349 if( objc>3 && strcmp(Tcl_GetString(objv[2]), "-readonly")==0 ){ 2374 isReadonly = 1; 2350 isReadonly = 1; 2375 } 2351 } 2376 2352 2377 if( objc!=(5+isReadonly) && objc!=(6+isReadonly) ){ 2353 if( objc!=(5+isReadonly) && objc!=(6+isReadonly) ){ ................................................................................................................................................................................ 2429 pDb->zNull = Tcl_Alloc( len + 1 ); 2405 pDb->zNull = Tcl_Alloc( len + 1 ); 2430 memcpy(pDb->zNull, zNull, len); 2406 memcpy(pDb->zNull, zNull, len); 2431 pDb->zNull[len] = '\0'; 2407 pDb->zNull[len] = '\0'; 2432 }else{ 2408 }else{ 2433 pDb->zNull = 0; 2409 pDb->zNull = 0; 2434 } 2410 } 2435 } 2411 } 2436 Tcl_SetObjResult(interp, dbTextToObj(pDb->zNull)); | 2412 Tcl_SetObjResult(interp, Tcl_NewStringObj(pDb->zNull, -1)); 2437 break; 2413 break; 2438 } 2414 } 2439 2415 2440 /* 2416 /* 2441 ** $db last_insert_rowid 2417 ** $db last_insert_rowid 2442 ** 2418 ** 2443 ** Return an integer which is the ROWID for the most recent insert. 2419 ** Return an integer which is the ROWID for the most recent insert.
Changes to src/test1.c
3063 void * clientData, 3063 void * clientData, 3064 Tcl_Interp *interp, 3064 Tcl_Interp *interp, 3065 int objc, 3065 int objc, 3066 Tcl_Obj *CONST objv[] 3066 Tcl_Obj *CONST objv[] 3067 ){ 3067 ){ 3068 sqlite3_stmt *pStmt; 3068 sqlite3_stmt *pStmt; 3069 int idx; 3069 int idx; 3070 i64 value; | 3070 Tcl_WideInt value; 3071 int rc; 3071 int rc; 3072 3072 3073 if( objc!=4 ){ 3073 if( objc!=4 ){ 3074 Tcl_AppendResult(interp, "wrong # args: should be \"", 3074 Tcl_AppendResult(interp, "wrong # args: should be \"", 3075 Tcl_GetStringFromObj(objv[0], 0), " STMT N VALUE", 0); 3075 Tcl_GetStringFromObj(objv[0], 0), " STMT N VALUE", 0); 3076 return TCL_ERROR; 3076 return TCL_ERROR; 3077 } 3077 } ................................................................................................................................................................................ 4699 static int test_soft_heap_limit( 4699 static int test_soft_heap_limit( 4700 void * clientData, 4700 void * clientData, 4701 Tcl_Interp *interp, 4701 Tcl_Interp *interp, 4702 int objc, 4702 int objc, 4703 Tcl_Obj *CONST objv[] 4703 Tcl_Obj *CONST objv[] 4704 ){ 4704 ){ 4705 sqlite3_int64 amt; 4705 sqlite3_int64 amt; 4706 sqlite3_int64 N = -1; | 4706 Tcl_WideInt N = -1; 4707 if( objc!=1 && objc!=2 ){ 4707 if( objc!=1 && objc!=2 ){ 4708 Tcl_WrongNumArgs(interp, 1, objv, "?N?"); 4708 Tcl_WrongNumArgs(interp, 1, objv, "?N?"); 4709 return TCL_ERROR; 4709 return TCL_ERROR; 4710 } 4710 } 4711 if( objc==2 ){ 4711 if( objc==2 ){ 4712 if( Tcl_GetWideIntFromObj(interp, objv[1], &N) ) return TCL_ERROR; 4712 if( Tcl_GetWideIntFromObj(interp, objv[1], &N) ) return TCL_ERROR; 4713 } 4713 } ................................................................................................................................................................................ 5092 */ 5092 */ 5093 static int file_control_sizehint_test( 5093 static int file_control_sizehint_test( 5094 ClientData clientData, /* Pointer to sqlite3_enable_XXX function */ 5094 ClientData clientData, /* Pointer to sqlite3_enable_XXX function */ 5095 Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ 5095 Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ 5096 int objc, /* Number of arguments */ 5096 int objc, /* Number of arguments */ 5097 Tcl_Obj *CONST objv[] /* Command arguments */ 5097 Tcl_Obj *CONST objv[] /* Command arguments */ 5098 ){ 5098 ){ 5099 sqlite3_int64 nSize; /* Hinted size */ | 5099 Tcl_WideInt nSize; /* Hinted size */ 5100 char *zDb; /* Db name ("main", "temp" etc.) */ 5100 char *zDb; /* Db name ("main", "temp" etc.) */ 5101 sqlite3 *db; /* Database handle */ 5101 sqlite3 *db; /* Database handle */ 5102 int rc; /* file_control() return code */ 5102 int rc; /* file_control() return code */ 5103 5103 5104 if( objc!=4 ){ 5104 if( objc!=4 ){ 5105 Tcl_WrongNumArgs(interp, 1, objv, "DB DBNAME SIZE"); 5105 Tcl_WrongNumArgs(interp, 1, objv, "DB DBNAME SIZE"); 5106 return TCL_ERROR; 5106 return TCL_ERROR; ................................................................................................................................................................................ 5929 const char *zOpt; 5929 const char *zOpt; 5930 int onoff; 5930 int onoff; 5931 int mask = 0; 5931 int mask = 0; 5932 static const struct { 5932 static const struct { 5933 const char *zOptName; 5933 const char *zOptName; 5934 int mask; 5934 int mask; 5935 } aOpt[] = { 5935 } aOpt[] = { 5936 { "all", SQLITE_OptMask }, | 5936 { "all", SQLITE_AllOpts }, 5937 { "query-flattener", SQLITE_QueryFlattener }, 5937 { "query-flattener", SQLITE_QueryFlattener }, 5938 { "column-cache", SQLITE_ColumnCache }, 5938 { "column-cache", SQLITE_ColumnCache }, 5939 { "groupby-order", SQLITE_GroupByOrder }, 5939 { "groupby-order", SQLITE_GroupByOrder }, 5940 { "factor-constants", SQLITE_FactorOutConst }, 5940 { "factor-constants", SQLITE_FactorOutConst }, 5941 { "real-as-int", SQLITE_IdxRealAsInt }, 5941 { "real-as-int", SQLITE_IdxRealAsInt }, 5942 { "distinct-opt", SQLITE_DistinctOpt }, 5942 { "distinct-opt", SQLITE_DistinctOpt }, 5943 { "cover-idx-scan", SQLITE_CoverIdxScan }, 5943 { "cover-idx-scan", SQLITE_CoverIdxScan }, > 5944 { "order-by-idx-join",SQLITE_OrderByIdxJoin }, 5944 }; 5945 }; 5945 5946 5946 if( objc!=4 ){ 5947 if( objc!=4 ){ 5947 Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN"); 5948 Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN"); 5948 return TCL_ERROR; 5949 return TCL_ERROR; 5949 } 5950 } 5950 if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; 5951 if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
Changes to src/test_intarray.c
342 #ifndef SQLITE_OMIT_VIRTUALTABLE 342 #ifndef SQLITE_OMIT_VIRTUALTABLE 343 a = sqlite3_malloc( sizeof(a[0])*n ); 343 a = sqlite3_malloc( sizeof(a[0])*n ); 344 if( a==0 ){ 344 if( a==0 ){ 345 Tcl_AppendResult(interp, "SQLITE_NOMEM", (char*)0); 345 Tcl_AppendResult(interp, "SQLITE_NOMEM", (char*)0); 346 return TCL_ERROR; 346 return TCL_ERROR; 347 } 347 } 348 for(i=0; i<n; i++){ 348 for(i=0; i<n; i++){ 349 a[i] = 0; | 349 Tcl_WideInt x = 0; 350 Tcl_GetWideIntFromObj(0, objv[i+2], &a[i]); | 350 Tcl_GetWideIntFromObj(0, objv[i+2], &x); > 351 a[i] = x; 351 } 352 } 352 rc = sqlite3_intarray_bind(pArray, n, a, sqlite3_free); 353 rc = sqlite3_intarray_bind(pArray, n, a, sqlite3_free); 353 if( rc!=SQLITE_OK ){ 354 if( rc!=SQLITE_OK ){ 354 Tcl_AppendResult(interp, sqlite3TestErrorName(rc), (char*)0); 355 Tcl_AppendResult(interp, sqlite3TestErrorName(rc), (char*)0); 355 return TCL_ERROR; 356 return TCL_ERROR; 356 } 357 } 357 #endif 358 #endif
Changes to src/test_quota.c
1069 pFile = 0; 1069 pFile = 0; 1070 } 1070 } 1071 rc = fwrite(pBuf, size, nmemb, p->f); 1071 rc = fwrite(pBuf, size, nmemb, p->f); 1072 1072 1073 /* If the write was incomplete, adjust the file size and group size 1073 /* If the write was incomplete, adjust the file size and group size 1074 ** downward */ 1074 ** downward */ 1075 if( rc<nmemb && pFile ){ 1075 if( rc<nmemb && pFile ){ 1076 size_t nWritten = rc>=0 ? rc : 0; | 1076 size_t nWritten = rc; 1077 sqlite3_int64 iNewEnd = iOfst + size*nWritten; 1077 sqlite3_int64 iNewEnd = iOfst + size*nWritten; 1078 if( iNewEnd<iEnd ) iNewEnd = iEnd; 1078 if( iNewEnd<iEnd ) iNewEnd = iEnd; 1079 quotaEnter(); 1079 quotaEnter(); 1080 pFile->pGroup->iSize += iNewEnd - pFile->iSize; 1080 pFile->pGroup->iSize += iNewEnd - pFile->iSize; 1081 pFile->iSize = iNewEnd; 1081 pFile->iSize = iNewEnd; 1082 quotaLeave(); 1082 quotaLeave(); 1083 } 1083 } ................................................................................................................................................................................ 1350 Tcl_IncrRefCount(pEval); 1350 Tcl_IncrRefCount(pEval); 1351 Tcl_ListObjAppendElement(0, pEval, Tcl_NewStringObj(zFilename, -1)); 1351 Tcl_ListObjAppendElement(0, pEval, Tcl_NewStringObj(zFilename, -1)); 1352 Tcl_ListObjAppendElement(0, pEval, pVarname); 1352 Tcl_ListObjAppendElement(0, pEval, pVarname); 1353 Tcl_ListObjAppendElement(0, pEval, Tcl_NewWideIntObj(iSize)); 1353 Tcl_ListObjAppendElement(0, pEval, Tcl_NewWideIntObj(iSize)); 1354 rc = Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL); 1354 rc = Tcl_EvalObjEx(p->interp, pEval, TCL_EVAL_GLOBAL); 1355 1355 1356 if( rc==TCL_OK ){ 1356 if( rc==TCL_OK ){ > 1357 Tcl_WideInt x; 1357 Tcl_Obj *pLimit = Tcl_ObjGetVar2(p->interp, pVarname, 0, 0); 1358 Tcl_Obj *pLimit = Tcl_ObjGetVar2(p->interp, pVarname, 0, 0); 1358 rc = Tcl_GetWideIntFromObj(p->interp, pLimit, piLimit); | 1359 rc = Tcl_GetWideIntFromObj(p->interp, pLimit, &x); > 1360 *piLimit = x; 1359 Tcl_UnsetVar(p->interp, Tcl_GetString(pVarname), 0); 1361 Tcl_UnsetVar(p->interp, Tcl_GetString(pVarname), 0); 1360 } 1362 } 1361 1363 1362 Tcl_DecrRefCount(pEval); 1364 Tcl_DecrRefCount(pEval); 1363 Tcl_DecrRefCount(pVarname); 1365 Tcl_DecrRefCount(pVarname); 1364 if( rc!=TCL_OK ) Tcl_BackgroundError(p->interp); 1366 if( rc!=TCL_OK ) Tcl_BackgroundError(p->interp); 1365 } 1367 } ................................................................................................................................................................................ 1433 static int test_quota_set( 1435 static int test_quota_set( 1434 void * clientData, 1436 void * clientData, 1435 Tcl_Interp *interp, 1437 Tcl_Interp *interp, 1436 int objc, 1438 int objc, 1437 Tcl_Obj *CONST objv[] 1439 Tcl_Obj *CONST objv[] 1438 ){ 1440 ){ 1439 const char *zPattern; /* File pattern to configure */ 1441 const char *zPattern; /* File pattern to configure */ 1440 sqlite3_int64 iLimit; /* Initial quota in bytes */ | 1442 Tcl_WideInt iLimit; /* Initial quota in bytes */ 1441 Tcl_Obj *pScript; /* Tcl script to invoke to increase quota */ 1443 Tcl_Obj *pScript; /* Tcl script to invoke to increase quota */ 1442 int rc; /* Value returned by quota_set() */ 1444 int rc; /* Value returned by quota_set() */ 1443 TclQuotaCallback *p; /* Callback object */ 1445 TclQuotaCallback *p; /* Callback object */ 1444 int nScript; /* Length of callback script */ 1446 int nScript; /* Length of callback script */ 1445 void (*xDestroy)(void*); /* Optional destructor for pArg */ 1447 void (*xDestroy)(void*); /* Optional destructor for pArg */ 1446 void (*xCallback)(const char *, sqlite3_int64 *, sqlite3_int64, void *); 1448 void (*xCallback)(const char *, sqlite3_int64 *, sqlite3_int64, void *); 1447 1449 ................................................................................................................................................................................ 1609 if( Tcl_GetIntFromObj(interp, objv[3], &nElem) ) return TCL_ERROR; 1611 if( Tcl_GetIntFromObj(interp, objv[3], &nElem) ) return TCL_ERROR; 1610 zBuf = (char*)sqlite3_malloc( sz*nElem + 1 ); 1612 zBuf = (char*)sqlite3_malloc( sz*nElem + 1 ); 1611 if( zBuf==0 ){ 1613 if( zBuf==0 ){ 1612 Tcl_SetResult(interp, "out of memory", TCL_STATIC); 1614 Tcl_SetResult(interp, "out of memory", TCL_STATIC); 1613 return TCL_ERROR; 1615 return TCL_ERROR; 1614 } 1616 } 1615 got = sqlite3_quota_fread(zBuf, sz, nElem, p); 1617 got = sqlite3_quota_fread(zBuf, sz, nElem, p); 1616 if( got<0 ) got = 0; < 1617 zBuf[got*sz] = 0; 1618 zBuf[got*sz] = 0; 1618 Tcl_SetResult(interp, zBuf, TCL_VOLATILE); 1619 Tcl_SetResult(interp, zBuf, TCL_VOLATILE); 1619 sqlite3_free(zBuf); 1620 sqlite3_free(zBuf); 1620 return TCL_OK; 1621 return TCL_OK; 1621 } 1622 } 1622 1623 1623 /* 1624 /*
Changes to src/wal.c
2824 ** final frame is repeated (with its commit mark) until the next sector 2824 ** final frame is repeated (with its commit mark) until the next sector 2825 ** boundary is crossed. Only the part of the WAL prior to the last 2825 ** boundary is crossed. Only the part of the WAL prior to the last 2826 ** sector boundary is synced; the part of the last frame that extends 2826 ** sector boundary is synced; the part of the last frame that extends 2827 ** past the sector boundary is written after the sync. 2827 ** past the sector boundary is written after the sync. 2828 */ 2828 */ 2829 if( isCommit && (sync_flags & WAL_SYNC_TRANSACTIONS)!=0 ){ 2829 if( isCommit && (sync_flags & WAL_SYNC_TRANSACTIONS)!=0 ){ 2830 if( pWal->padToSectorBoundary ){ 2830 if( pWal->padToSectorBoundary ){ 2831 int sectorSize = sqlite3OsSectorSize(pWal->pWalFd); | 2831 int sectorSize = sqlite3SectorSize(pWal->pWalFd); 2832 w.iSyncPoint = ((iOffset+sectorSize-1)/sectorSize)*sectorSize; 2832 w.iSyncPoint = ((iOffset+sectorSize-1)/sectorSize)*sectorSize; 2833 while( iOffset<w.iSyncPoint ){ 2833 while( iOffset<w.iSyncPoint ){ 2834 rc = walWriteOneFrame(&w, pLast, nTruncate, iOffset); 2834 rc = walWriteOneFrame(&w, pLast, nTruncate, iOffset); 2835 if( rc ) return rc; 2835 if( rc ) return rc; 2836 iOffset += szFrame; 2836 iOffset += szFrame; 2837 nExtra++; 2837 nExtra++; 2838 } 2838 }
Changes to src/where.c
253 #define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ 253 #define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ 254 #define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ 254 #define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ 255 #define WHERE_NOT_FULLSCAN 0x100f3000 /* Does not do a full table scan */ 255 #define WHERE_NOT_FULLSCAN 0x100f3000 /* Does not do a full table scan */ 256 #define WHERE_IN_ABLE 0x000f1000 /* Able to support an IN operator */ 256 #define WHERE_IN_ABLE 0x000f1000 /* Able to support an IN operator */ 257 #define WHERE_TOP_LIMIT 0x00100000 /* x<EXPR or x<=EXPR constraint */ 257 #define WHERE_TOP_LIMIT 0x00100000 /* x<EXPR or x<=EXPR constraint */ 258 #define WHERE_BTM_LIMIT 0x00200000 /* x>EXPR or x>=EXPR constraint */ 258 #define WHERE_BTM_LIMIT 0x00200000 /* x>EXPR or x>=EXPR constraint */ 259 #define WHERE_BOTH_LIMIT 0x00300000 /* Both x>EXPR and x<EXPR */ 259 #define WHERE_BOTH_LIMIT 0x00300000 /* Both x>EXPR and x<EXPR */ 260 #define WHERE_IDX_ONLY 0x00800000 /* Use index only - omit table */ | 260 #define WHERE_IDX_ONLY 0x00400000 /* Use index only - omit table */ 261 #define WHERE_ORDERBY 0x01000000 /* Output will appear in correct order */ | 261 #define WHERE_ORDERED 0x00800000 /* Output will appear in correct order */ 262 #define WHERE_REVERSE 0x02000000 /* Scan in reverse order */ | 262 #define WHERE_REVERSE 0x01000000 /* Scan in reverse order */ 263 #define WHERE_UNIQUE 0x04000000 /* Selects no more than one row */ | 263 #define WHERE_UNIQUE 0x02000000 /* Selects no more than one row */ > 264 #define WHERE_ALL_UNIQUE 0x04000000 /* This and all prior have one row */ 264 #define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */ 265 #define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */ 265 #define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */ 266 #define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */ 266 #define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */ 267 #define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */ 267 #define WHERE_DISTINCT 0x40000000 /* Correct order for DISTINCT */ 268 #define WHERE_DISTINCT 0x40000000 /* Correct order for DISTINCT */ 268 #define WHERE_COVER_SCAN 0x80000000 /* Full scan of a covering index */ 269 #define WHERE_COVER_SCAN 0x80000000 /* Full scan of a covering index */ > 270 > 271 /* > 272 ** This module contains many separate subroutines that work together to > 273 ** find the best indices to use for accessing a particular table in a query. > 274 ** An instance of the following structure holds context information about the > 275 ** index search so that it can be more easily passed between the various > 276 ** routines. > 277 */ > 278 typedef struct WhereBestIdx WhereBestIdx; > 279 struct WhereBestIdx { > 280 Parse *pParse; /* Parser context */ > 281 WhereClause *pWC; /* The WHERE clause */ > 282 struct SrcList_item *pSrc; /* The FROM clause term to search */ > 283 Bitmask notReady; /* Mask of cursors not available */ > 284 Bitmask notValid; /* Cursors not available for any purpose */ > 285 ExprList *pOrderBy; /* The ORDER BY clause */ > 286 ExprList *pDistinct; /* The select-list if query is DISTINCT */ > 287 sqlite3_index_info **ppIdxInfo; /* Index information passed to xBestIndex */ > 288 int i, n; /* Which loop is being coded; # of loops */ > 289 WhereLevel *aLevel; /* Info about outer loops */ > 290 WhereCost cost; /* Lowest cost query plan */ > 291 }; > 292 > 293 /* > 294 ** Return TRUE if the probe cost is less than the baseline cost > 295 */ > 296 static int compareCost(const WhereCost *pProbe, const WhereCost *pBaseline){ > 297 if( pProbe->rCost<pBaseline->rCost ) return 1; > 298 if( pProbe->rCost>pBaseline->rCost ) return 0; > 299 if( pProbe->plan.nOBSat>pBaseline->plan.nOBSat ) return 1; > 300 if( pProbe->plan.nRow<pBaseline->plan.nRow ) return 1; > 301 return 0; > 302 } 269 303 270 /* 304 /* 271 ** Initialize a preallocated WhereClause structure. 305 ** Initialize a preallocated WhereClause structure. 272 */ 306 */ 273 static void whereClauseInit( 307 static void whereClauseInit( 274 WhereClause *pWC, /* The WhereClause to be initialized */ 308 WhereClause *pWC, /* The WhereClause to be initialized */ 275 Parse *pParse, /* The parsing context */ 309 Parse *pParse, /* The parsing context */ ................................................................................................................................................................................ 1405 /* Prevent ON clause terms of a LEFT JOIN from being used to drive 1439 /* Prevent ON clause terms of a LEFT JOIN from being used to drive 1406 ** an index for tables to the left of the join. 1440 ** an index for tables to the left of the join. 1407 */ 1441 */ 1408 pTerm->prereqRight |= extraRight; 1442 pTerm->prereqRight |= extraRight; 1409 } 1443 } 1410 1444 1411 /* 1445 /* 1412 ** Return TRUE if any of the expressions in pList->a[iFirst...] contain | 1446 ** Return TRUE if the given index is UNIQUE and all columns past the 1413 ** a reference to any table other than the iBase table. | 1447 ** first nSkip columns are NOT NULL. 1414 */ 1448 */ 1415 static int referencesOtherTables( | 1449 static int indexIsUniqueNotNull(Index *pIdx, int nSkip){ 1416 ExprList *pList, /* Search expressions in ths list */ | 1450 Table *pTab = pIdx->pTable; 1417 WhereMaskSet *pMaskSet, /* Mapping from tables to bitmaps */ | 1451 int i; 1418 int iFirst, /* Be searching with the iFirst-th expression */ | 1452 if( pIdx->onError==OE_None ) return 0; 1419 int iBase /* Ignore references to this table */ | 1453 for(i=nSkip; i<pIdx->nColumn; i++){ 1420 ){ < > 1454 int j = pIdx->aiColumn[i]; 1421 Bitmask allowed = ~getMask(pMaskSet, iBase); | 1455 assert( j>=0 && j<pTab->nCol ); 1422 while( iFirst<pList->nExpr ){ | 1456 if( pTab->aCol[j].notNull==0 ) return 0; 1423 if( (exprTableUsage(pMaskSet, pList->a[iFirst++].pExpr)&allowed)!=0 ){ < 1424 return 1; < 1425 } | 1457 } 1426 } < 1427 return 0; | 1458 return 1; 1428 } 1459 } 1429 1460 1430 /* 1461 /* 1431 ** This function searches the expression list passed as the second argument 1462 ** This function searches the expression list passed as the second argument 1432 ** for an expression of type TK_COLUMN that refers to the same column and 1463 ** for an expression of type TK_COLUMN that refers to the same column and 1433 ** uses the same collation sequence as the iCol'th column of index pIdx. 1464 ** uses the same collation sequence as the iCol'th column of index pIdx. 1434 ** Argument iBase is the cursor number used for the table that pIdx refers 1465 ** Argument iBase is the cursor number used for the table that pIdx refers ................................................................................................................................................................................ 1482 int base, /* Cursor number for the table pIdx is on */ 1513 int base, /* Cursor number for the table pIdx is on */ 1483 ExprList *pDistinct, /* The DISTINCT expressions */ 1514 ExprList *pDistinct, /* The DISTINCT expressions */ 1484 int nEqCol /* Number of index columns with == */ 1515 int nEqCol /* Number of index columns with == */ 1485 ){ 1516 ){ 1486 Bitmask mask = 0; /* Mask of unaccounted for pDistinct exprs */ 1517 Bitmask mask = 0; /* Mask of unaccounted for pDistinct exprs */ 1487 int i; /* Iterator variable */ 1518 int i; /* Iterator variable */ 1488 1519 > 1520 assert( pDistinct!=0 ); 1489 if( pIdx->zName==0 || pDistinct==0 || pDistinct->nExpr>=BMS ) return 0; | 1521 if( pIdx->zName==0 || pDistinct->nExpr>=BMS ) return 0; 1490 testcase( pDistinct->nExpr==BMS-1 ); 1522 testcase( pDistinct->nExpr==BMS-1 ); 1491 1523 1492 /* Loop through all the expressions in the distinct list. If any of them 1524 /* Loop through all the expressions in the distinct list. If any of them 1493 ** are not simple column references, return early. Otherwise, test if the 1525 ** are not simple column references, return early. Otherwise, test if the 1494 ** WHERE clause contains a "col=X" clause. If it does, the expression 1526 ** WHERE clause contains a "col=X" clause. If it does, the expression 1495 ** can be ignored. If it does not, and the column does not belong to the 1527 ** can be ignored. If it does not, and the column does not belong to the 1496 ** same table as index pIdx, return early. Finally, if there is no 1528 ** same table as index pIdx, return early. Finally, if there is no ................................................................................................................................................................................ 1584 return 1; 1616 return 1; 1585 } 1617 } 1586 } 1618 } 1587 1619 1588 return 0; 1620 return 0; 1589 } 1621 } 1590 1622 1591 /* < 1592 ** This routine decides if pIdx can be used to satisfy the ORDER BY < 1593 ** clause. If it can, it returns 1. If pIdx cannot satisfy the < 1594 ** ORDER BY clause, this routine returns 0. < 1595 ** < 1596 ** pOrderBy is an ORDER BY clause from a SELECT statement. pTab is the < 1597 ** left-most table in the FROM clause of that same SELECT statement and < 1598 ** the table has a cursor number of "base". pIdx is an index on pTab. < 1599 ** < 1600 ** nEqCol is the number of columns of pIdx that are used as equality < 1601 ** constraints. Any of these columns may be missing from the ORDER BY < 1602 ** clause and the match can still be a success. < 1603 ** < 1604 ** All terms of the ORDER BY that match against the index must be either < 1605 ** ASC or DESC. (Terms of the ORDER BY clause past the end of a UNIQUE < 1606 ** index do not need to satisfy this constraint.) The *pbRev value is < 1607 ** set to 1 if the ORDER BY clause is all DESC and it is set to 0 if < 1608 ** the ORDER BY clause is all ASC. < 1609 */ < 1610 static int isSortingIndex( < 1611 Parse *pParse, /* Parsing context */ < 1612 WhereMaskSet *pMaskSet, /* Mapping from table cursor numbers to bitmaps */ < 1613 Index *pIdx, /* The index we are testing */ < 1614 int base, /* Cursor number for the table to be sorted */ < 1615 ExprList *pOrderBy, /* The ORDER BY clause */ < 1616 int nEqCol, /* Number of index columns with == constraints */ < 1617 int wsFlags, /* Index usages flags */ < 1618 int *pbRev /* Set to 1 if ORDER BY is DESC */ < 1619 ){ < 1620 int i, j; /* Loop counters */ < 1621 int sortOrder = 0; /* XOR of index and ORDER BY sort direction */ < 1622 int nTerm; /* Number of ORDER BY terms */ < 1623 struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ < 1624 sqlite3 *db = pParse->db; < 1625 < 1626 if( !pOrderBy ) return 0; < 1627 if( wsFlags & WHERE_COLUMN_IN ) return 0; < 1628 if( pIdx->bUnordered ) return 0; < 1629 < 1630 nTerm = pOrderBy->nExpr; < 1631 assert( nTerm>0 ); < 1632 < 1633 /* Argument pIdx must either point to a 'real' named index structure, < 1634 ** or an index structure allocated on the stack by bestBtreeIndex() to < 1635 ** represent the rowid index that is part of every table. */ < 1636 assert( pIdx->zName || (pIdx->nColumn==1 && pIdx->aiColumn[0]==-1) ); < 1637 < 1638 /* Match terms of the ORDER BY clause against columns of < 1639 ** the index. < 1640 ** < 1641 ** Note that indices have pIdx->nColumn regular columns plus < 1642 ** one additional column containing the rowid. The rowid column < 1643 ** of the index is also allowed to match against the ORDER BY < 1644 ** clause. < 1645 */ < 1646 for(i=j=0, pTerm=pOrderBy->a; j<nTerm && i<=pIdx->nColumn; i++){ < 1647 Expr *pExpr; /* The expression of the ORDER BY pTerm */ < 1648 CollSeq *pColl; /* The collating sequence of pExpr */ < 1649 int termSortOrder; /* Sort order for this term */ < 1650 int iColumn; /* The i-th column of the index. -1 for rowid */ < 1651 int iSortOrder; /* 1 for DESC, 0 for ASC on the i-th index term */ < 1652 const char *zColl; /* Name of the collating sequence for i-th index term */ < 1653 < 1654 pExpr = pTerm->pExpr; < 1655 if( pExpr->op!=TK_COLUMN || pExpr->iTable!=base ){ < 1656 /* Can not use an index sort on anything that is not a column in the < 1657 ** left-most table of the FROM clause */ < 1658 break; < 1659 } < 1660 pColl = sqlite3ExprCollSeq(pParse, pExpr); < 1661 if( !pColl ){ < 1662 pColl = db->pDfltColl; < 1663 } < 1664 if( pIdx->zName && i<pIdx->nColumn ){ < 1665 iColumn = pIdx->aiColumn[i]; < 1666 if( iColumn==pIdx->pTable->iPKey ){ < 1667 iColumn = -1; < 1668 } < 1669 iSortOrder = pIdx->aSortOrder[i]; < 1670 zColl = pIdx->azColl[i]; < 1671 }else{ < 1672 iColumn = -1; < 1673 iSortOrder = 0; < 1674 zColl = pColl->zName; < 1675 } < 1676 if( pExpr->iColumn!=iColumn || sqlite3StrICmp(pColl->zName, zColl) ){ < 1677 /* Term j of the ORDER BY clause does not match column i of the index */ < 1678 if( i<nEqCol ){ < 1679 /* If an index column that is constrained by == fails to match an < 1680 ** ORDER BY term, that is OK. Just ignore that column of the index < 1681 */ < 1682 continue; < 1683 }else if( i==pIdx->nColumn ){ < 1684 /* Index column i is the rowid. All other terms match. */ < 1685 break; < 1686 }else{ < 1687 /* If an index column fails to match and is not constrained by == < 1688 ** then the index cannot satisfy the ORDER BY constraint. < 1689 */ < 1690 return 0; < 1691 } < 1692 } < 1693 assert( pIdx->aSortOrder!=0 || iColumn==-1 ); < 1694 assert( pTerm->sortOrder==0 || pTerm->sortOrder==1 ); < 1695 assert( iSortOrder==0 || iSortOrder==1 ); < 1696 termSortOrder = iSortOrder ^ pTerm->sortOrder; < 1697 if( i>nEqCol ){ < 1698 if( termSortOrder!=sortOrder ){ < 1699 /* Indices can only be used if all ORDER BY terms past the < 1700 ** equality constraints are all either DESC or ASC. */ < 1701 return 0; < 1702 } < 1703 }else{ < 1704 sortOrder = termSortOrder; < 1705 } < 1706 j++; < 1707 pTerm++; < 1708 if( iColumn<0 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){ < 1709 /* If the indexed column is the primary key and everything matches < 1710 ** so far and none of the ORDER BY terms to the right reference other < 1711 ** tables in the join, then we are assured that the index can be used < 1712 ** to sort because the primary key is unique and so none of the other < 1713 ** columns will make any difference < 1714 */ < 1715 j = nTerm; < 1716 } < 1717 } < 1718 < 1719 *pbRev = sortOrder!=0; < 1720 if( j>=nTerm ){ < 1721 /* All terms of the ORDER BY clause are covered by this index so < 1722 ** this index can be used for sorting. */ < 1723 return 1; < 1724 } < 1725 if( pIdx->onError!=OE_None && i==pIdx->nColumn < 1726 && (wsFlags & WHERE_COLUMN_NULL)==0 < 1727 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) < 1728 ){ < 1729 Column *aCol = pIdx->pTable->aCol; < 1730 < 1731 /* All terms of this index match some prefix of the ORDER BY clause, < 1732 ** the index is UNIQUE, and no terms on the tail of the ORDER BY < 1733 ** refer to other tables in a join. So, assuming that the index entries < 1734 ** visited contain no NULL values, then this index delivers rows in < 1735 ** the required order. < 1736 ** < 1737 ** It is not possible for any of the first nEqCol index fields to be < 1738 ** NULL (since the corresponding "=" operator in the WHERE clause would < 1739 ** not be true). So if all remaining index columns have NOT NULL < 1740 ** constaints attached to them, we can be confident that the visited < 1741 ** index entries are free of NULLs. */ < 1742 for(i=nEqCol; i<pIdx->nColumn; i++){ < 1743 if( aCol[pIdx->aiColumn[i]].notNull==0 ) break; < 1744 } < 1745 return (i==pIdx->nColumn); < 1746 } < 1747 return 0; < 1748 } < 1749 < 1750 /* 1623 /* 1751 ** Prepare a crude estimate of the logarithm of the input value. 1624 ** Prepare a crude estimate of the logarithm of the input value. 1752 ** The results need not be exact. This is only used for estimating 1625 ** The results need not be exact. This is only used for estimating 1753 ** the total cost of performing operations with O(logN) or O(NlogN) 1626 ** the total cost of performing operations with O(logN) or O(NlogN) 1754 ** complexity. Because N is just a guess, it is no great tragedy if 1627 ** complexity. Because N is just a guess, it is no great tragedy if 1755 ** logN is a little off. 1628 ** logN is a little off. 1756 */ 1629 */ ................................................................................................................................................................................ 1807 #define TRACE_IDX_INPUTS(A) 1680 #define TRACE_IDX_INPUTS(A) 1808 #define TRACE_IDX_OUTPUTS(A) 1681 #define TRACE_IDX_OUTPUTS(A) 1809 #endif 1682 #endif 1810 1683 1811 /* 1684 /* 1812 ** Required because bestIndex() is called by bestOrClauseIndex() 1685 ** Required because bestIndex() is called by bestOrClauseIndex() 1813 */ 1686 */ 1814 static void bestIndex( | 1687 static void bestIndex(WhereBestIdx*); 1815 Parse*, WhereClause*, struct SrcList_item*, < 1816 Bitmask, Bitmask, WhereCost*); < 1817 1688 1818 /* 1689 /* 1819 ** This routine attempts to find an scanning strategy that can be used 1690 ** This routine attempts to find an scanning strategy that can be used 1820 ** to optimize an 'OR' expression that is part of a WHERE clause. 1691 ** to optimize an 'OR' expression that is part of a WHERE clause. 1821 ** 1692 ** 1822 ** The table associated with FROM clause term pSrc may be either a 1693 ** The table associated with FROM clause term pSrc may be either a 1823 ** regular B-Tree table or a virtual table. 1694 ** regular B-Tree table or a virtual table. 1824 */ 1695 */ 1825 static void bestOrClauseIndex( | 1696 static void bestOrClauseIndex(WhereBestIdx *p){ 1826 Parse *pParse, /* The parsing context */ | 1697 #ifndef SQLITE_OMIT_OR_OPTIMIZATION 1827 WhereClause *pWC, /* The WHERE clause */ | 1698 WhereClause *pWC = p->pWC; /* The WHERE clause */ 1828 struct SrcList_item *pSrc, /* The FROM clause term to search */ | 1699 struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */ 1829 Bitmask notReady, /* Mask of cursors not available for indexing */ < 1830 Bitmask notValid, /* Cursors not available for any purpose */ < 1831 ExprList *pOrderBy, /* The ORDER BY clause */ < 1832 WhereCost *pCost /* Lowest cost query plan */ < 1833 ){ < 1834 #ifndef SQLITE_OMIT_OR_OPTIMIZATION < 1835 const int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */ | 1700 const int iCur = pSrc->iCursor; /* The cursor of the table */ 1836 const Bitmask maskSrc = getMask(pWC->pMaskSet, iCur); /* Bitmask for pSrc */ 1701 const Bitmask maskSrc = getMask(pWC->pMaskSet, iCur); /* Bitmask for pSrc */ 1837 WhereTerm * const pWCEnd = &pWC->a[pWC->nTerm]; /* End of pWC->a[] */ 1702 WhereTerm * const pWCEnd = &pWC->a[pWC->nTerm]; /* End of pWC->a[] */ 1838 WhereTerm *pTerm; /* A single term of the WHERE clause */ | 1703 WhereTerm *pTerm; /* A single term of the WHERE clause */ 1839 1704 1840 /* The OR-clause optimization is disallowed if the INDEXED BY or 1705 /* The OR-clause optimization is disallowed if the INDEXED BY or 1841 ** NOT INDEXED clauses are used or if the WHERE_AND_ONLY bit is set. */ 1706 ** NOT INDEXED clauses are used or if the WHERE_AND_ONLY bit is set. */ 1842 if( pSrc->notIndexed || pSrc->pIndex!=0 ){ 1707 if( pSrc->notIndexed || pSrc->pIndex!=0 ){ 1843 return; 1708 return; 1844 } 1709 } 1845 if( pWC->wctrlFlags & WHERE_AND_ONLY ){ 1710 if( pWC->wctrlFlags & WHERE_AND_ONLY ){ 1846 return; 1711 return; 1847 } 1712 } 1848 1713 1849 /* Search the WHERE clause terms for a usable WO_OR term. */ 1714 /* Search the WHERE clause terms for a usable WO_OR term. */ 1850 for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ 1715 for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ 1851 if( pTerm->eOperator==WO_OR 1716 if( pTerm->eOperator==WO_OR 1852 && ((pTerm->prereqAll & ~maskSrc) & notReady)==0 | 1717 && ((pTerm->prereqAll & ~maskSrc) & p->notReady)==0 1853 && (pTerm->u.pOrInfo->indexable & maskSrc)!=0 1718 && (pTerm->u.pOrInfo->indexable & maskSrc)!=0 1854 ){ 1719 ){ 1855 WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc; 1720 WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc; 1856 WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm]; 1721 WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm]; 1857 WhereTerm *pOrTerm; 1722 WhereTerm *pOrTerm; 1858 int flags = WHERE_MULTI_OR; 1723 int flags = WHERE_MULTI_OR; 1859 double rTotal = 0; 1724 double rTotal = 0; 1860 double nRow = 0; 1725 double nRow = 0; 1861 Bitmask used = 0; 1726 Bitmask used = 0; > 1727 WhereBestIdx sBOI; 1862 1728 > 1729 sBOI = *p; > 1730 sBOI.pOrderBy = 0; > 1731 sBOI.pDistinct = 0; > 1732 sBOI.ppIdxInfo = 0; 1863 for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){ 1733 for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){ 1864 WhereCost sTermCost; < 1865 WHERETRACE(("... Multi-index OR testing for term %d of %d....\n", 1734 WHERETRACE(("... Multi-index OR testing for term %d of %d....\n", 1866 (pOrTerm - pOrWC->a), (pTerm - pWC->a) 1735 (pOrTerm - pOrWC->a), (pTerm - pWC->a) 1867 )); 1736 )); 1868 if( pOrTerm->eOperator==WO_AND ){ 1737 if( pOrTerm->eOperator==WO_AND ){ 1869 WhereClause *pAndWC = &pOrTerm->u.pAndInfo->wc; | 1738 sBOI.pWC = &pOrTerm->u.pAndInfo->wc; 1870 bestIndex(pParse, pAndWC, pSrc, notReady, notValid, &sTermCost); | 1739 bestIndex(&sBOI); 1871 }else if( pOrTerm->leftCursor==iCur ){ 1740 }else if( pOrTerm->leftCursor==iCur ){ 1872 WhereClause tempWC; 1741 WhereClause tempWC; 1873 tempWC.pParse = pWC->pParse; 1742 tempWC.pParse = pWC->pParse; 1874 tempWC.pMaskSet = pWC->pMaskSet; 1743 tempWC.pMaskSet = pWC->pMaskSet; 1875 tempWC.pOuter = pWC; 1744 tempWC.pOuter = pWC; 1876 tempWC.op = TK_AND; 1745 tempWC.op = TK_AND; 1877 tempWC.a = pOrTerm; 1746 tempWC.a = pOrTerm; 1878 tempWC.wctrlFlags = 0; 1747 tempWC.wctrlFlags = 0; 1879 tempWC.nTerm = 1; 1748 tempWC.nTerm = 1; 1880 bestIndex(pParse, &tempWC, pSrc, notReady, notValid, &sTermCost); | 1749 sBOI.pWC = &tempWC; > 1750 bestIndex(&sBOI); 1881 }else{ 1751 }else{ 1882 continue; 1752 continue; 1883 } 1753 } 1884 rTotal += sTermCost.rCost; | 1754 rTotal += sBOI.cost.rCost; 1885 nRow += sTermCost.plan.nRow; | 1755 nRow += sBOI.cost.plan.nRow; 1886 used |= sTermCost.used; | 1756 used |= sBOI.cost.used; 1887 if( rTotal>=pCost->rCost ) break; | 1757 if( rTotal>=p->cost.rCost ) break; 1888 } 1758 } 1889 1759 1890 /* If there is an ORDER BY clause, increase the scan cost to account 1760 /* If there is an ORDER BY clause, increase the scan cost to account 1891 ** for the cost of the sort. */ 1761 ** for the cost of the sort. */ 1892 if( pOrderBy!=0 ){ | 1762 if( p->pOrderBy!=0 ){ 1893 WHERETRACE(("... sorting increases OR cost %.9g to %.9g\n", 1763 WHERETRACE(("... sorting increases OR cost %.9g to %.9g\n", 1894 rTotal, rTotal+nRow*estLog(nRow))); 1764 rTotal, rTotal+nRow*estLog(nRow))); 1895 rTotal += nRow*estLog(nRow); 1765 rTotal += nRow*estLog(nRow); 1896 } 1766 } 1897 1767 1898 /* If the cost of scanning using this OR term for optimization is 1768 /* If the cost of scanning using this OR term for optimization is 1899 ** less than the current cost stored in pCost, replace the contents 1769 ** less than the current cost stored in pCost, replace the contents 1900 ** of pCost. */ 1770 ** of pCost. */ 1901 WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow)); 1771 WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow)); 1902 if( rTotal<pCost->rCost ){ | 1772 if( rTotal<p->cost.rCost ){ 1903 pCost->rCost = rTotal; | 1773 p->cost.rCost = rTotal; 1904 pCost->used = used; | 1774 p->cost.used = used; 1905 pCost->plan.nRow = nRow; | 1775 p->cost.plan.nRow = nRow; > 1776 p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0; 1906 pCost->plan.wsFlags = flags; | 1777 p->cost.plan.wsFlags = flags; 1907 pCost->plan.u.pTerm = pTerm; | 1778 p->cost.plan.u.pTerm = pTerm; 1908 } 1779 } 1909 } 1780 } 1910 } 1781 } 1911 #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ 1782 #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ 1912 } 1783 } 1913 1784 1914 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX 1785 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX ................................................................................................................................................................................ 1937 ** If the query plan for pSrc specified in pCost is a full table scan 1808 ** If the query plan for pSrc specified in pCost is a full table scan 1938 ** and indexing is allows (if there is no NOT INDEXED clause) and it 1809 ** and indexing is allows (if there is no NOT INDEXED clause) and it 1939 ** possible to construct a transient index that would perform better 1810 ** possible to construct a transient index that would perform better 1940 ** than a full table scan even when the cost of constructing the index 1811 ** than a full table scan even when the cost of constructing the index 1941 ** is taken into account, then alter the query plan to use the 1812 ** is taken into account, then alter the query plan to use the 1942 ** transient index. 1813 ** transient index. 1943 */ 1814 */ 1944 static void bestAutomaticIndex( | 1815 static void bestAutomaticIndex(WhereBestIdx *p){ 1945 Parse *pParse, /* The parsing context */ | 1816 Parse *pParse = p->pParse; /* The parsing context */ 1946 WhereClause *pWC, /* The WHERE clause */ | 1817 WhereClause *pWC = p->pWC; /* The WHERE clause */ 1947 struct SrcList_item *pSrc, /* The FROM clause term to search */ | 1818 struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */ 1948 Bitmask notReady, /* Mask of cursors that are not available */ < 1949 WhereCost *pCost /* Lowest cost query plan */ < 1950 ){ < 1951 double nTableRow; /* Rows in the input table */ | 1819 double nTableRow; /* Rows in the input table */ 1952 double logN; /* log(nTableRow) */ | 1820 double logN; /* log(nTableRow) */ 1953 double costTempIdx; /* per-query cost of the transient index */ 1821 double costTempIdx; /* per-query cost of the transient index */ 1954 WhereTerm *pTerm; /* A single term of the WHERE clause */ 1822 WhereTerm *pTerm; /* A single term of the WHERE clause */ 1955 WhereTerm *pWCEnd; /* End of pWC->a[] */ 1823 WhereTerm *pWCEnd; /* End of pWC->a[] */ 1956 Table *pTable; /* Table tht might be indexed */ 1824 Table *pTable; /* Table tht might be indexed */ 1957 1825 1958 if( pParse->nQueryLoop<=(double)1 ){ 1826 if( pParse->nQueryLoop<=(double)1 ){ 1959 /* There is no point in building an automatic index for a single scan */ 1827 /* There is no point in building an automatic index for a single scan */ 1960 return; 1828 return; 1961 } 1829 } 1962 if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){ 1830 if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){ 1963 /* Automatic indices are disabled at run-time */ 1831 /* Automatic indices are disabled at run-time */ 1964 return; 1832 return; 1965 } 1833 } 1966 if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){ | 1834 if( (p->cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){ 1967 /* We already have some kind of index in use for this query. */ 1835 /* We already have some kind of index in use for this query. */ 1968 return; 1836 return; 1969 } 1837 } 1970 if( pSrc->notIndexed ){ 1838 if( pSrc->notIndexed ){ 1971 /* The NOT INDEXED clause appears in the SQL. */ 1839 /* The NOT INDEXED clause appears in the SQL. */ 1972 return; 1840 return; 1973 } 1841 } ................................................................................................................................................................................ 1977 } 1845 } 1978 1846 1979 assert( pParse->nQueryLoop >= (double)1 ); 1847 assert( pParse->nQueryLoop >= (double)1 ); 1980 pTable = pSrc->pTab; 1848 pTable = pSrc->pTab; 1981 nTableRow = pTable->nRowEst; 1849 nTableRow = pTable->nRowEst; 1982 logN = estLog(nTableRow); 1850 logN = estLog(nTableRow); 1983 costTempIdx = 2*logN*(nTableRow/pParse->nQueryLoop + 1); 1851 costTempIdx = 2*logN*(nTableRow/pParse->nQueryLoop + 1); 1984 if( costTempIdx>=pCost->rCost ){ | 1852 if( costTempIdx>=p->cost.rCost ){ 1985 /* The cost of creating the transient table would be greater than 1853 /* The cost of creating the transient table would be greater than 1986 ** doing the full table scan */ 1854 ** doing the full table scan */ 1987 return; 1855 return; 1988 } 1856 } 1989 1857 1990 /* Search for any equality comparison term */ 1858 /* Search for any equality comparison term */ 1991 pWCEnd = &pWC->a[pWC->nTerm]; 1859 pWCEnd = &pWC->a[pWC->nTerm]; 1992 for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ 1860 for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ 1993 if( termCanDriveIndex(pTerm, pSrc, notReady) ){ | 1861 if( termCanDriveIndex(pTerm, pSrc, p->notReady) ){ 1994 WHERETRACE(("auto-index reduces cost from %.1f to %.1f\n", 1862 WHERETRACE(("auto-index reduces cost from %.1f to %.1f\n", 1995 pCost->rCost, costTempIdx)); | 1863 p->cost.rCost, costTempIdx)); 1996 pCost->rCost = costTempIdx; | 1864 p->cost.rCost = costTempIdx; 1997 pCost->plan.nRow = logN + 1; | 1865 p->cost.plan.nRow = logN + 1; 1998 pCost->plan.wsFlags = WHERE_TEMP_INDEX; | 1866 p->cost.plan.wsFlags = WHERE_TEMP_INDEX; 1999 pCost->used = pTerm->prereqRight; | 1867 p->cost.used = pTerm->prereqRight; 2000 break; 1868 break; 2001 } 1869 } 2002 } 1870 } 2003 } 1871 } 2004 #else 1872 #else 2005 # define bestAutomaticIndex(A,B,C,D,E) /* no-op */ | 1873 # define bestAutomaticIndex(A) /* no-op */ 2006 #endif /* SQLITE_OMIT_AUTOMATIC_INDEX */ 1874 #endif /* SQLITE_OMIT_AUTOMATIC_INDEX */ 2007 1875 2008 1876 2009 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX 1877 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX 2010 /* 1878 /* 2011 ** Generate code to construct the Index object for an automatic index 1879 ** Generate code to construct the Index object for an automatic index 2012 ** and to set up the WhereLevel object pLevel so that the code generator 1880 ** and to set up the WhereLevel object pLevel so that the code generator ................................................................................................................................................................................ 2159 2027 2160 #ifndef SQLITE_OMIT_VIRTUALTABLE 2028 #ifndef SQLITE_OMIT_VIRTUALTABLE 2161 /* 2029 /* 2162 ** Allocate and populate an sqlite3_index_info structure. It is the 2030 ** Allocate and populate an sqlite3_index_info structure. It is the 2163 ** responsibility of the caller to eventually release the structure 2031 ** responsibility of the caller to eventually release the structure 2164 ** by passing the pointer returned by this function to sqlite3_free(). 2032 ** by passing the pointer returned by this function to sqlite3_free(). 2165 */ 2033 */ 2166 static sqlite3_index_info *allocateIndexInfo( | 2034 static sqlite3_index_info *allocateIndexInfo(WhereBestIdx *p){ 2167 Parse *pParse, | 2035 Parse *pParse = p->pParse; 2168 WhereClause *pWC, | 2036 WhereClause *pWC = p->pWC; 2169 struct SrcList_item *pSrc, | 2037 struct SrcList_item *pSrc = p->pSrc; 2170 ExprList *pOrderBy | 2038 ExprList *pOrderBy = p->pOrderBy; 2171 ){ < 2172 int i, j; 2039 int i, j; 2173 int nTerm; 2040 int nTerm; 2174 struct sqlite3_index_constraint *pIdxCons; 2041 struct sqlite3_index_constraint *pIdxCons; 2175 struct sqlite3_index_orderby *pIdxOrderBy; 2042 struct sqlite3_index_orderby *pIdxOrderBy; 2176 struct sqlite3_index_constraint_usage *pUsage; 2043 struct sqlite3_index_constraint_usage *pUsage; 2177 WhereTerm *pTerm; 2044 WhereTerm *pTerm; 2178 int nOrderBy; 2045 int nOrderBy; ................................................................................................................................................................................ 2194 2061 2195 /* If the ORDER BY clause contains only columns in the current 2062 /* If the ORDER BY clause contains only columns in the current 2196 ** virtual table then allocate space for the aOrderBy part of 2063 ** virtual table then allocate space for the aOrderBy part of 2197 ** the sqlite3_index_info structure. 2064 ** the sqlite3_index_info structure. 2198 */ 2065 */ 2199 nOrderBy = 0; 2066 nOrderBy = 0; 2200 if( pOrderBy ){ 2067 if( pOrderBy ){ 2201 for(i=0; i<pOrderBy->nExpr; i++){ | 2068 int n = pOrderBy->nExpr; > 2069 for(i=0; i<n; i++){ 2202 Expr *pExpr = pOrderBy->a[i].pExpr; 2070 Expr *pExpr = pOrderBy->a[i].pExpr; 2203 if( pExpr->op!=TK_COLUMN || pExpr->iTable!=pSrc->iCursor ) break; 2071 if( pExpr->op!=TK_COLUMN || pExpr->iTable!=pSrc->iCursor ) break; 2204 } 2072 } 2205 if( i==pOrderBy->nExpr ){ | 2073 if( i==n){ 2206 nOrderBy = pOrderBy->nExpr; | 2074 nOrderBy = n; 2207 } 2075 } 2208 } 2076 } 2209 2077 2210 /* Allocate the sqlite3_index_info structure 2078 /* Allocate the sqlite3_index_info structure 2211 */ 2079 */ 2212 pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo) 2080 pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo) 2213 + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm 2081 + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm ................................................................................................................................................................................ 2323 ** same virtual table. The sqlite3_index_info structure is created 2191 ** same virtual table. The sqlite3_index_info structure is created 2324 ** and initialized on the first invocation and reused on all subsequent 2192 ** and initialized on the first invocation and reused on all subsequent 2325 ** invocations. The sqlite3_index_info structure is also used when 2193 ** invocations. The sqlite3_index_info structure is also used when 2326 ** code is generated to access the virtual table. The whereInfoDelete() 2194 ** code is generated to access the virtual table. The whereInfoDelete() 2327 ** routine takes care of freeing the sqlite3_index_info structure after 2195 ** routine takes care of freeing the sqlite3_index_info structure after 2328 ** everybody has finished with it. 2196 ** everybody has finished with it. 2329 */ 2197 */ 2330 static void bestVirtualIndex( | 2198 static void bestVirtualIndex(WhereBestIdx *p){ 2331 Parse *pParse, /* The parsing context */ | 2199 Parse *pParse = p->pParse; /* The parsing context */ 2332 WhereClause *pWC, /* The WHERE clause */ | 2200 WhereClause *pWC = p->pWC; /* The WHERE clause */ 2333 struct SrcList_item *pSrc, /* The FROM clause term to search */ | 2201 struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */ 2334 Bitmask notReady, /* Mask of cursors not available for index */ < 2335 Bitmask notValid, /* Cursors not valid for any purpose */ < 2336 ExprList *pOrderBy, /* The order by clause */ < 2337 WhereCost *pCost, /* Lowest cost query plan */ < 2338 sqlite3_index_info **ppIdxInfo /* Index information passed to xBestIndex */ < 2339 ){ < 2340 Table *pTab = pSrc->pTab; 2202 Table *pTab = pSrc->pTab; 2341 sqlite3_index_info *pIdxInfo; 2203 sqlite3_index_info *pIdxInfo; 2342 struct sqlite3_index_constraint *pIdxCons; 2204 struct sqlite3_index_constraint *pIdxCons; 2343 struct sqlite3_index_constraint_usage *pUsage; 2205 struct sqlite3_index_constraint_usage *pUsage; 2344 WhereTerm *pTerm; 2206 WhereTerm *pTerm; 2345 int i, j; 2207 int i, j; 2346 int nOrderBy; 2208 int nOrderBy; 2347 double rCost; 2209 double rCost; 2348 2210 2349 /* Make sure wsFlags is initialized to some sane value. Otherwise, if the 2211 /* Make sure wsFlags is initialized to some sane value. Otherwise, if the 2350 ** malloc in allocateIndexInfo() fails and this function returns leaving 2212 ** malloc in allocateIndexInfo() fails and this function returns leaving 2351 ** wsFlags in an uninitialized state, the caller may behave unpredictably. 2213 ** wsFlags in an uninitialized state, the caller may behave unpredictably. 2352 */ 2214 */ 2353 memset(pCost, 0, sizeof(*pCost)); | 2215 memset(&p->cost, 0, sizeof(p->cost)); 2354 pCost->plan.wsFlags = WHERE_VIRTUALTABLE; | 2216 p->cost.plan.wsFlags = WHERE_VIRTUALTABLE; 2355 2217 2356 /* If the sqlite3_index_info structure has not been previously 2218 /* If the sqlite3_index_info structure has not been previously 2357 ** allocated and initialized, then allocate and initialize it now. 2219 ** allocated and initialized, then allocate and initialize it now. 2358 */ 2220 */ 2359 pIdxInfo = *ppIdxInfo; | 2221 pIdxInfo = *p->ppIdxInfo; 2360 if( pIdxInfo==0 ){ 2222 if( pIdxInfo==0 ){ 2361 *ppIdxInfo = pIdxInfo = allocateIndexInfo(pParse, pWC, pSrc, pOrderBy); | 2223 *p->ppIdxInfo = pIdxInfo = allocateIndexInfo(p); 2362 } 2224 } 2363 if( pIdxInfo==0 ){ 2225 if( pIdxInfo==0 ){ 2364 return; 2226 return; 2365 } 2227 } 2366 2228 2367 /* At this point, the sqlite3_index_info structure that pIdxInfo points 2229 /* At this point, the sqlite3_index_info structure that pIdxInfo points 2368 ** to will have been initialized, either during the current invocation or 2230 ** to will have been initialized, either during the current invocation or ................................................................................................................................................................................ 2399 ** each time. 2261 ** each time. 2400 */ 2262 */ 2401 pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; 2263 pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; 2402 pUsage = pIdxInfo->aConstraintUsage; 2264 pUsage = pIdxInfo->aConstraintUsage; 2403 for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){ 2265 for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){ 2404 j = pIdxCons->iTermOffset; 2266 j = pIdxCons->iTermOffset; 2405 pTerm = &pWC->a[j]; 2267 pTerm = &pWC->a[j]; 2406 pIdxCons->usable = (pTerm->prereqRight¬Ready) ? 0 : 1; | 2268 pIdxCons->usable = (pTerm->prereqRight&p->notReady) ? 0 : 1; 2407 } 2269 } 2408 memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint); 2270 memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint); 2409 if( pIdxInfo->needToFreeIdxStr ){ 2271 if( pIdxInfo->needToFreeIdxStr ){ 2410 sqlite3_free(pIdxInfo->idxStr); 2272 sqlite3_free(pIdxInfo->idxStr); 2411 } 2273 } 2412 pIdxInfo->idxStr = 0; 2274 pIdxInfo->idxStr = 0; 2413 pIdxInfo->idxNum = 0; 2275 pIdxInfo->idxNum = 0; 2414 pIdxInfo->needToFreeIdxStr = 0; 2276 pIdxInfo->needToFreeIdxStr = 0; 2415 pIdxInfo->orderByConsumed = 0; 2277 pIdxInfo->orderByConsumed = 0; 2416 /* ((double)2) In case of SQLITE_OMIT_FLOATING_POINT... */ 2278 /* ((double)2) In case of SQLITE_OMIT_FLOATING_POINT... */ 2417 pIdxInfo->estimatedCost = SQLITE_BIG_DBL / ((double)2); 2279 pIdxInfo->estimatedCost = SQLITE_BIG_DBL / ((double)2); 2418 nOrderBy = pIdxInfo->nOrderBy; 2280 nOrderBy = pIdxInfo->nOrderBy; 2419 if( !pOrderBy ){ | 2281 if( !p->pOrderBy ){ 2420 pIdxInfo->nOrderBy = 0; 2282 pIdxInfo->nOrderBy = 0; 2421 } 2283 } 2422 2284 2423 if( vtabBestIndex(pParse, pTab, pIdxInfo) ){ 2285 if( vtabBestIndex(pParse, pTab, pIdxInfo) ){ 2424 return; 2286 return; 2425 } 2287 } 2426 2288 2427 pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; 2289 pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; 2428 for(i=0; i<pIdxInfo->nConstraint; i++){ 2290 for(i=0; i<pIdxInfo->nConstraint; i++){ 2429 if( pUsage[i].argvIndex>0 ){ 2291 if( pUsage[i].argvIndex>0 ){ 2430 pCost->used |= pWC->a[pIdxCons[i].iTermOffset].prereqRight; | 2292 p->cost.used |= pWC->a[pIdxCons[i].iTermOffset].prereqRight; 2431 } 2293 } 2432 } 2294 } 2433 2295 2434 /* If there is an ORDER BY clause, and the selected virtual table index 2296 /* If there is an ORDER BY clause, and the selected virtual table index 2435 ** does not satisfy it, increase the cost of the scan accordingly. This 2297 ** does not satisfy it, increase the cost of the scan accordingly. This 2436 ** matches the processing for non-virtual tables in bestBtreeIndex(). 2298 ** matches the processing for non-virtual tables in bestBtreeIndex(). 2437 */ 2299 */ 2438 rCost = pIdxInfo->estimatedCost; 2300 rCost = pIdxInfo->estimatedCost; 2439 if( pOrderBy && pIdxInfo->orderByConsumed==0 ){ | 2301 if( p->pOrderBy && pIdxInfo->orderByConsumed==0 ){ 2440 rCost += estLog(rCost)*rCost; 2302 rCost += estLog(rCost)*rCost; 2441 } 2303 } 2442 2304 2443 /* The cost is not allowed to be larger than SQLITE_BIG_DBL (the 2305 /* The cost is not allowed to be larger than SQLITE_BIG_DBL (the 2444 ** inital value of lowestCost in this loop. If it is, then the 2306 ** inital value of lowestCost in this loop. If it is, then the 2445 ** (cost<lowestCost) test below will never be true. 2307 ** (cost<lowestCost) test below will never be true. 2446 ** 2308 ** 2447 ** Use "(double)2" instead of "2.0" in case OMIT_FLOATING_POINT 2309 ** Use "(double)2" instead of "2.0" in case OMIT_FLOATING_POINT 2448 ** is defined. 2310 ** is defined. 2449 */ 2311 */ 2450 if( (SQLITE_BIG_DBL/((double)2))<rCost ){ 2312 if( (SQLITE_BIG_DBL/((double)2))<rCost ){ 2451 pCost->rCost = (SQLITE_BIG_DBL/((double)2)); | 2313 p->cost.rCost = (SQLITE_BIG_DBL/((double)2)); 2452 }else{ 2314 }else{ 2453 pCost->rCost = rCost; | 2315 p->cost.rCost = rCost; 2454 } 2316 } 2455 pCost->plan.u.pVtabIdx = pIdxInfo; | 2317 p->cost.plan.u.pVtabIdx = pIdxInfo; 2456 if( pIdxInfo->orderByConsumed ){ 2318 if( pIdxInfo->orderByConsumed ){ 2457 pCost->plan.wsFlags |= WHERE_ORDERBY; | 2319 p->cost.plan.wsFlags |= WHERE_ORDERED; > 2320 p->cost.plan.nOBSat = nOrderBy; > 2321 }else{ > 2322 p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0; 2458 } 2323 } 2459 pCost->plan.nEq = 0; | 2324 p->cost.plan.nEq = 0; 2460 pIdxInfo->nOrderBy = nOrderBy; 2325 pIdxInfo->nOrderBy = nOrderBy; 2461 2326 2462 /* Try to find a more efficient access pattern by using multiple indexes 2327 /* Try to find a more efficient access pattern by using multiple indexes 2463 ** to optimize an OR expression within the WHERE clause. 2328 ** to optimize an OR expression within the WHERE clause. 2464 */ 2329 */ 2465 bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost); | 2330 bestOrClauseIndex(p); 2466 } 2331 } 2467 #endif /* SQLITE_OMIT_VIRTUALTABLE */ 2332 #endif /* SQLITE_OMIT_VIRTUALTABLE */ 2468 2333 2469 #ifdef SQLITE_ENABLE_STAT3 2334 #ifdef SQLITE_ENABLE_STAT3 2470 /* 2335 /* 2471 ** Estimate the location of a particular key among all keys in an 2336 ** Estimate the location of a particular key among all keys in an 2472 ** index. Store the results in aStat as follows: 2337 ** index. Store the results in aStat as follows: ................................................................................................................................................................................ 2857 *pnRow = nRowEst; 2722 *pnRow = nRowEst; 2858 WHERETRACE(("IN row estimate: est=%g\n", nRowEst)); 2723 WHERETRACE(("IN row estimate: est=%g\n", nRowEst)); 2859 } 2724 } 2860 return rc; 2725 return rc; 2861 } 2726 } 2862 #endif /* defined(SQLITE_ENABLE_STAT3) */ 2727 #endif /* defined(SQLITE_ENABLE_STAT3) */ 2863 2728 > 2729 /* > 2730 ** Check to see if column iCol of the table with cursor iTab will appear > 2731 ** in sorted order according to the current query plan. Return true if > 2732 ** it will and false if not. > 2733 ** > 2734 ** If *pbRev is initially 2 (meaning "unknown") then set *pbRev to the > 2735 ** sort order of iTab.iCol. If *pbRev is 0 or 1 but does not match > 2736 ** the sort order of iTab.iCol, then consider the column to be unordered. > 2737 */ > 2738 static int isOrderedColumn(WhereBestIdx *p, int iTab, int iCol, int *pbRev){ > 2739 int i, j; > 2740 WhereLevel *pLevel = &p->aLevel[p->i-1]; > 2741 Index *pIdx; > 2742 u8 sortOrder; > 2743 for(i=p->i-1; i>=0; i--, pLevel--){ > 2744 if( pLevel->iTabCur!=iTab ) continue; > 2745 if( (pLevel->plan.wsFlags & WHERE_ALL_UNIQUE)!=0 ){ > 2746 return 1; 2864 | 2747 } > 2748 if( (pLevel->plan.wsFlags & WHERE_ORDERED)==0 ){ > 2749 return 0; > 2750 } > 2751 if( (pIdx = pLevel->plan.u.pIdx)!=0 ){ > 2752 if( iCol<0 ){ > 2753 sortOrder = 0; > 2754 testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 ); > 2755 }else{ > 2756 int n = pIdx->nColumn; > 2757 for(j=0; j<n; j++){ > 2758 if( iCol==pIdx->aiColumn[j] ) break; > 2759 } > 2760 if( j>=n ) return 0; > 2761 sortOrder = pIdx->aSortOrder[j]; > 2762 testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 ); > 2763 } > 2764 }else{ > 2765 if( iCol!=(-1) ) return 0; > 2766 sortOrder = 0; > 2767 testcase( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 ); > 2768 } > 2769 if( (pLevel->plan.wsFlags & WHERE_REVERSE)!=0 ){ > 2770 assert( sortOrder==0 || sortOrder==1 ); > 2771 testcase( sortOrder==1 ); > 2772 sortOrder = 1 - sortOrder; > 2773 } > 2774 if( *pbRev==2 ){ > 2775 *pbRev = sortOrder; > 2776 return 1; > 2777 } > 2778 return (*pbRev==sortOrder); > 2779 } > 2780 return 0; > 2781 } > 2782 2865 /* 2783 /* > 2784 ** pTerm is an == constraint. Check to see if the other side of > 2785 ** the == is a constant or a value that is guaranteed to be ordered > 2786 ** by outer loops. Return 1 if pTerm is ordered, and 0 if not. > 2787 */ > 2788 static int isOrderedTerm(WhereBestIdx *p, WhereTerm *pTerm, int *pbRev){ > 2789 Expr *pExpr = pTerm->pExpr; > 2790 assert( pExpr->op==TK_EQ ); > 2791 assert( pExpr->pLeft!=0 && pExpr->pLeft->op==TK_COLUMN ); > 2792 assert( pExpr->pRight!=0 ); > 2793 if( pTerm->prereqRight==0 ){ > 2794 return 1; /* RHS of the == is a constant */ > 2795 } > 2796 if( pExpr->pRight->op==TK_COLUMN > 2797 && isOrderedColumn(p, pExpr->pRight->iTable, pExpr->pRight->iColumn, pbRev) > 2798 ){ > 2799 return 1; > 2800 } > 2801 > 2802 /* If we cannot prove that the constraint is ordered, assume it is not */ > 2803 return 0; > 2804 } > 2805 > 2806 /* > 2807 ** This routine decides if pIdx can be used to satisfy the ORDER BY > 2808 ** clause, either in whole or in part. The return value is the > 2809 ** cumulative number of terms in the ORDER BY clause that are satisfied > 2810 ** by the index pIdx and other indices in outer loops. > 2811 ** > 2812 ** The table being queried has a cursor number of "base". pIdx is the > 2813 ** index that is postulated for use to access the table. > 2814 ** > 2815 ** nEqCol is the number of columns of pIdx that are used as equality > 2816 ** constraints and where the other side of the == is an ordered column > 2817 ** or constant. An "order column" in the previous sentence means a column > 2818 ** in table from an outer loop whose values will always appear in the > 2819 ** correct order due to othre index, or because the outer loop generates > 2820 ** a unique result. Any of the first nEqCol columns of pIdx may be missing > 2821 ** from the ORDER BY clause and the match can still be a success. > 2822 ** > 2823 ** The *pbRev value is set to 0 order 1 depending on whether or not > 2824 ** pIdx should be run in the forward order or in reverse order. > 2825 */ > 2826 static int isSortingIndex( > 2827 WhereBestIdx *p, /* Best index search context */ > 2828 Index *pIdx, /* The index we are testing */ > 2829 int base, /* Cursor number for the table to be sorted */ > 2830 int nEqCol, /* Number of index columns with ordered == constraints */ > 2831 int wsFlags, /* Index usages flags */ > 2832 int bOuterRev, /* True if outer loops scan in reverse order */ > 2833 int *pbRev /* Set to 1 for reverse-order scan of pIdx */ > 2834 ){ > 2835 int i; /* Number of pIdx terms used */ > 2836 int j; /* Number of ORDER BY terms satisfied */ > 2837 int sortOrder = 0; /* XOR of index and ORDER BY sort direction */ > 2838 int nTerm; /* Number of ORDER BY terms */ > 2839 struct ExprList_item *pTerm; /* A term of the ORDER BY clause */ > 2840 ExprList *pOrderBy; /* The ORDER BY clause */ > 2841 Parse *pParse = p->pParse; /* Parser context */ > 2842 sqlite3 *db = pParse->db; /* Database connection */ > 2843 int nPriorSat; /* ORDER BY terms satisfied by outer loops */ > 2844 int seenRowid = 0; /* True if an ORDER BY rowid term is seen */ > 2845 int nEqOneRow; /* Idx columns that ref unique values */ > 2846 > 2847 if( p->i==0 ){ > 2848 nPriorSat = 0; > 2849 }else{ > 2850 nPriorSat = p->aLevel[p->i-1].plan.nOBSat; > 2851 if( OptimizationDisabled(db, SQLITE_OrderByIdxJoin) ) return nPriorSat; > 2852 } > 2853 if( nEqCol==0 ){ > 2854 if( p->i && (p->aLevel[p->i-1].plan.wsFlags & WHERE_ORDERED)==0 ){ > 2855 return nPriorSat; > 2856 } > 2857 nEqOneRow = 0; > 2858 }else if( p->i==0 || (p->aLevel[p->i-1].plan.wsFlags & WHERE_ALL_UNIQUE)!=0 ){ > 2859 nEqOneRow = nEqCol; > 2860 }else{ > 2861 sortOrder = bOuterRev; > 2862 nEqOneRow = -1; > 2863 } > 2864 pOrderBy = p->pOrderBy; > 2865 assert( pOrderBy!=0 ); > 2866 if( wsFlags & WHERE_COLUMN_IN ) return nPriorSat; > 2867 if( pIdx->bUnordered ) return nPriorSat; > 2868 nTerm = pOrderBy->nExpr; > 2869 assert( nTerm>0 ); > 2870 > 2871 /* Argument pIdx must either point to a 'real' named index structure, > 2872 ** or an index structure allocated on the stack by bestBtreeIndex() to > 2873 ** represent the rowid index that is part of every table. */ > 2874 assert( pIdx->zName || (pIdx->nColumn==1 && pIdx->aiColumn[0]==-1) ); > 2875 > 2876 /* Match terms of the ORDER BY clause against columns of > 2877 ** the index. > 2878 ** > 2879 ** Note that indices have pIdx->nColumn regular columns plus > 2880 ** one additional column containing the rowid. The rowid column > 2881 ** of the index is also allowed to match against the ORDER BY > 2882 ** clause. > 2883 */ > 2884 for(i=0,j=nPriorSat,pTerm=&pOrderBy->a[j]; j<nTerm; i++){ > 2885 Expr *pExpr; /* The expression of the ORDER BY pTerm */ > 2886 CollSeq *pColl; /* The collating sequence of pExpr */ > 2887 int termSortOrder; /* Sort order for this term */ > 2888 int iColumn; /* The i-th column of the index. -1 for rowid */ > 2889 int iSortOrder; /* 1 for DESC, 0 for ASC on the i-th index term */ > 2890 const char *zColl; /* Name of the collating sequence for i-th index term */ > 2891 > 2892 assert( i<=pIdx->nColumn ); > 2893 pExpr = pTerm->pExpr; > 2894 if( pExpr->op!=TK_COLUMN || pExpr->iTable!=base ){ > 2895 /* Can not use an index sort on anything that is not a column in the > 2896 ** left-most table of the FROM clause */ > 2897 break; > 2898 } > 2899 pColl = sqlite3ExprCollSeq(pParse, pExpr); > 2900 if( !pColl ){ > 2901 pColl = db->pDfltColl; > 2902 } > 2903 if( pIdx->zName && i<pIdx->nColumn ){ > 2904 iColumn = pIdx->aiColumn[i]; > 2905 if( iColumn==pIdx->pTable->iPKey ){ > 2906 iColumn = -1; > 2907 } > 2908 iSortOrder = pIdx->aSortOrder[i]; > 2909 zColl = pIdx->azColl[i]; > 2910 }else{ > 2911 iColumn = -1; > 2912 iSortOrder = 0; > 2913 zColl = pColl->zName; > 2914 } > 2915 if( pExpr->iColumn!=iColumn || sqlite3StrICmp(pColl->zName, zColl) ){ > 2916 /* Term j of the ORDER BY clause does not match column i of the index */ > 2917 if( i<nEqCol ){ > 2918 /* If an index column that is constrained by == fails to match an > 2919 ** ORDER BY term, that is OK. Just ignore that column of the index > 2920 */ > 2921 continue; > 2922 }else if( i==pIdx->nColumn ){ > 2923 /* Index column i is the rowid. All other terms match. */ > 2924 break; > 2925 }else{ > 2926 /* If an index column fails to match and is not constrained by == > 2927 ** then the index cannot satisfy the ORDER BY constraint. > 2928 */ > 2929 return nPriorSat; > 2930 } > 2931 } > 2932 assert( pIdx->aSortOrder!=0 || iColumn==-1 ); > 2933 assert( pTerm->sortOrder==0 || pTerm->sortOrder==1 ); > 2934 assert( iSortOrder==0 || iSortOrder==1 ); > 2935 termSortOrder = iSortOrder ^ pTerm->sortOrder; > 2936 if( i>nEqOneRow ){ > 2937 if( termSortOrder!=sortOrder ){ > 2938 /* Indices can only be used if all ORDER BY terms past the > 2939 ** equality constraints have the correct DESC or ASC. */ > 2940 break; > 2941 } > 2942 }else{ > 2943 sortOrder = termSortOrder; > 2944 } > 2945 j++; > 2946 pTerm++; > 2947 if( iColumn<0 ){ > 2948 seenRowid = 1; > 2949 break; > 2950 } > 2951 } > 2952 *pbRev = sortOrder; > 2953 > 2954 /* If there was an "ORDER BY rowid" term that matched, or it is only > 2955 ** possible for a single row from this table to match, then skip over > 2956 ** any additional ORDER BY terms dealing with this table. > 2957 */ > 2958 if( seenRowid || > 2959 ( (wsFlags & WHERE_COLUMN_NULL)==0 > 2960 && i>=pIdx->nColumn > 2961 && indexIsUniqueNotNull(pIdx, nEqCol) > 2962 ) > 2963 ){ > 2964 /* Advance j over additional ORDER BY terms associated with base */ > 2965 WhereMaskSet *pMS = p->pWC->pMaskSet; > 2966 Bitmask m = ~getMask(pMS, base); > 2967 while( j<nTerm && (exprTableUsage(pMS, pOrderBy->a[j].pExpr)&m)==0 ){ > 2968 j++; > 2969 } > 2970 } > 2971 return j; > 2972 } > 2973 > 2974 /* 2866 ** Find the best query plan for accessing a particular table. Write the 2975 ** Find the best query plan for accessing a particular table. Write the 2867 ** best query plan and its cost into the WhereCost object supplied as the | 2976 ** best query plan and its cost into the p->cost. 2868 ** last parameter. < 2869 ** 2977 ** 2870 ** The lowest cost plan wins. The cost is an estimate of the amount of 2978 ** The lowest cost plan wins. The cost is an estimate of the amount of 2871 ** CPU and disk I/O needed to process the requested result. 2979 ** CPU and disk I/O needed to process the requested result. 2872 ** Factors that influence cost include: 2980 ** Factors that influence cost include: 2873 ** 2981 ** 2874 ** * The estimated number of rows that will be retrieved. (The 2982 ** * The estimated number of rows that will be retrieved. (The 2875 ** fewer the better.) 2983 ** fewer the better.) ................................................................................................................................................................................ 2886 ** then the cost is calculated in the usual way. 2994 ** then the cost is calculated in the usual way. 2887 ** 2995 ** 2888 ** If a NOT INDEXED clause (pSrc->notIndexed!=0) was attached to the table 2996 ** If a NOT INDEXED clause (pSrc->notIndexed!=0) was attached to the table 2889 ** in the SELECT statement, then no indexes are considered. However, the 2997 ** in the SELECT statement, then no indexes are considered. However, the 2890 ** selected plan may still take advantage of the built-in rowid primary key 2998 ** selected plan may still take advantage of the built-in rowid primary key 2891 ** index. 2999 ** index. 2892 */ 3000 */ 2893 static void bestBtreeIndex( | 3001 static void bestBtreeIndex(WhereBestIdx *p){ 2894 Parse *pParse, /* The parsing context */ | 3002 Parse *pParse = p->pParse; /* The parsing context */ 2895 WhereClause *pWC, /* The WHERE clause */ | 3003 WhereClause *pWC = p->pWC; /* The WHERE clause */ 2896 struct SrcList_item *pSrc, /* The FROM clause term to search */ | 3004 struct SrcList_item *pSrc = p->pSrc; /* The FROM clause term to search */ 2897 Bitmask notReady, /* Mask of cursors not available for indexing */ < 2898 Bitmask notValid, /* Cursors not available for any purpose */ < 2899 ExprList *pOrderBy, /* The ORDER BY clause */ < 2900 ExprList *pDistinct, /* The select-list if query is DISTINCT */ < 2901 WhereCost *pCost /* Lowest cost query plan */ < 2902 ){ < 2903 int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */ 3005 int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */ 2904 Index *pProbe; /* An index we are evaluating */ 3006 Index *pProbe; /* An index we are evaluating */ 2905 Index *pIdx; /* Copy of pProbe, or zero for IPK index */ 3007 Index *pIdx; /* Copy of pProbe, or zero for IPK index */ 2906 int eqTermMask; /* Current mask of valid equality operators */ 3008 int eqTermMask; /* Current mask of valid equality operators */ 2907 int idxEqTermMask; /* Index mask of valid equality operators */ 3009 int idxEqTermMask; /* Index mask of valid equality operators */ 2908 Index sPk; /* A fake index object for the primary key */ 3010 Index sPk; /* A fake index object for the primary key */ 2909 tRowcnt aiRowEstPk[2]; /* The aiRowEst[] value for the sPk index */ 3011 tRowcnt aiRowEstPk[2]; /* The aiRowEst[] value for the sPk index */ 2910 int aiColumnPk = -1; /* The aColumn[] value for the sPk index */ 3012 int aiColumnPk = -1; /* The aColumn[] value for the sPk index */ 2911 int wsFlagMask; /* Allowed flags in pCost->plan.wsFlag */ | 3013 int wsFlagMask; /* Allowed flags in p->cost.plan.wsFlag */ 2912 3014 2913 /* Initialize the cost to a worst-case value */ 3015 /* Initialize the cost to a worst-case value */ 2914 memset(pCost, 0, sizeof(*pCost)); | 3016 memset(&p->cost, 0, sizeof(p->cost)); 2915 pCost->rCost = SQLITE_BIG_DBL; | 3017 p->cost.rCost = SQLITE_BIG_DBL; 2916 3018 2917 /* If the pSrc table is the right table of a LEFT JOIN then we may not 3019 /* If the pSrc table is the right table of a LEFT JOIN then we may not 2918 ** use an index to satisfy IS NULL constraints on that table. This is 3020 ** use an index to satisfy IS NULL constraints on that table. This is 2919 ** because columns might end up being NULL if the table does not match - 3021 ** because columns might end up being NULL if the table does not match - 2920 ** a circumstance which the index cannot help us discover. Ticket #2177. 3022 ** a circumstance which the index cannot help us discover. Ticket #2177. 2921 */ 3023 */ 2922 if( pSrc->jointype & JT_LEFT ){ 3024 if( pSrc->jointype & JT_LEFT ){ ................................................................................................................................................................................ 2958 pIdx = 0; 3060 pIdx = 0; 2959 } 3061 } 2960 3062 2961 /* Loop over all indices looking for the best one to use 3063 /* Loop over all indices looking for the best one to use 2962 */ 3064 */ 2963 for(; pProbe; pIdx=pProbe=pProbe->pNext){ 3065 for(; pProbe; pIdx=pProbe=pProbe->pNext){ 2964 const tRowcnt * const aiRowEst = pProbe->aiRowEst; 3066 const tRowcnt * const aiRowEst = pProbe->aiRowEst; 2965 double cost; /* Cost of using pProbe */ | 3067 WhereCost pc; /* Cost of using pProbe */ 2966 double nRow; /* Estimated number of rows in result set */ < 2967 double log10N = (double)1; /* base-10 logarithm of nRow (inexact) */ 3068 double log10N = (double)1; /* base-10 logarithm of nRow (inexact) */ 2968 int rev; /* True to scan in reverse order */ < 2969 int wsFlags = 0; < 2970 Bitmask used = 0; < > 3069 int bRev = 2; /* 0=forward scan. 1=reverse. 2=undecided */ 2971 3070 2972 /* The following variables are populated based on the properties of 3071 /* The following variables are populated based on the properties of 2973 ** index being evaluated. They are then used to determine the expected 3072 ** index being evaluated. They are then used to determine the expected 2974 ** cost and number of rows returned. 3073 ** cost and number of rows returned. 2975 ** 3074 ** 2976 ** nEq: | 3075 ** pc.plan.nEq: 2977 ** Number of equality terms that can be implemented using the index. 3076 ** Number of equality terms that can be implemented using the index. 2978 ** In other words, the number of initial fields in the index that 3077 ** In other words, the number of initial fields in the index that 2979 ** are used in == or IN or NOT NULL constraints of the WHERE clause. 3078 ** are used in == or IN or NOT NULL constraints of the WHERE clause. 2980 ** 3079 ** 2981 ** nInMul: 3080 ** nInMul: 2982 ** The "in-multiplier". This is an estimate of how many seek operations 3081 ** The "in-multiplier". This is an estimate of how many seek operations 2983 ** SQLite must perform on the index in question. For example, if the 3082 ** SQLite must perform on the index in question. For example, if the ................................................................................................................................................................................ 2994 ** 3093 ** 2995 ** nInMul is set to 1. 3094 ** nInMul is set to 1. 2996 ** 3095 ** 2997 ** If there exists a WHERE term of the form "x IN (SELECT ...)", then 3096 ** If there exists a WHERE term of the form "x IN (SELECT ...)", then 2998 ** the sub-select is assumed to return 25 rows for the purposes of 3097 ** the sub-select is assumed to return 25 rows for the purposes of 2999 ** determining nInMul. 3098 ** determining nInMul. 3000 ** 3099 ** > 3100 ** nOrdered: > 3101 ** The number of equality terms that are constrainted by outer loop > 3102 ** variables that are well-ordered. > 3103 ** 3001 ** bInEst: 3104 ** bInEst: 3002 ** Set to true if there was at least one "x IN (SELECT ...)" term used 3105 ** Set to true if there was at least one "x IN (SELECT ...)" term used 3003 ** in determining the value of nInMul. Note that the RHS of the 3106 ** in determining the value of nInMul. Note that the RHS of the 3004 ** IN operator must be a SELECT, not a value list, for this variable 3107 ** IN operator must be a SELECT, not a value list, for this variable 3005 ** to be true. 3108 ** to be true. 3006 ** 3109 ** 3007 ** rangeDiv: 3110 ** rangeDiv: ................................................................................................................................................................................ 3012 ** space to 1/16th of its original size (rangeDiv==16). 3115 ** space to 1/16th of its original size (rangeDiv==16). 3013 ** 3116 ** 3014 ** bSort: 3117 ** bSort: 3015 ** Boolean. True if there is an ORDER BY clause that will require an 3118 ** Boolean. True if there is an ORDER BY clause that will require an 3016 ** external sort (i.e. scanning the index being evaluated will not 3119 ** external sort (i.e. scanning the index being evaluated will not 3017 ** correctly order records). 3120 ** correctly order records). 3018 ** 3121 ** > 3122 ** bDist: > 3123 ** Boolean. True if there is a DISTINCT clause that will require an > 3124 ** external btree. > 3125 ** 3019 ** bLookup: 3126 ** bLookup: 3020 ** Boolean. True if a table lookup is required for each index entry 3127 ** Boolean. True if a table lookup is required for each index entry 3021 ** visited. In other words, true if this is not a covering index. 3128 ** visited. In other words, true if this is not a covering index. 3022 ** This is always false for the rowid primary key index of a table. 3129 ** This is always false for the rowid primary key index of a table. 3023 ** For other indexes, it is true unless all the columns of the table 3130 ** For other indexes, it is true unless all the columns of the table 3024 ** used by the SELECT statement are present in the index (such an 3131 ** used by the SELECT statement are present in the index (such an 3025 ** index is sometimes described as a covering index). 3132 ** index is sometimes described as a covering index). ................................................................................................................................................................................ 3027 ** two queries requires table b-tree lookups in order to find the value 3134 ** two queries requires table b-tree lookups in order to find the value 3028 ** of column c, but the first does not because columns a and b are 3135 ** of column c, but the first does not because columns a and b are 3029 ** both available in the index. 3136 ** both available in the index. 3030 ** 3137 ** 3031 ** SELECT a, b FROM tbl WHERE a = 1; 3138 ** SELECT a, b FROM tbl WHERE a = 1; 3032 ** SELECT a, b, c FROM tbl WHERE a = 1; 3139 ** SELECT a, b, c FROM tbl WHERE a = 1; 3033 */ 3140 */ 3034 int nEq; /* Number of == or IN terms matching index */ | 3141 int nOrdered; /* Number of ordered terms matching index */ 3035 int bInEst = 0; /* True if "x IN (SELECT...)" seen */ 3142 int bInEst = 0; /* True if "x IN (SELECT...)" seen */ 3036 int nInMul = 1; /* Number of distinct equalities to lookup */ 3143 int nInMul = 1; /* Number of distinct equalities to lookup */ 3037 double rangeDiv = (double)1; /* Estimated reduction in search space */ 3144 double rangeDiv = (double)1; /* Estimated reduction in search space */ 3038 int nBound = 0; /* Number of range constraints seen */ 3145 int nBound = 0; /* Number of range constraints seen */ 3039 int bSort = !!pOrderBy; /* True if external sort required */ | 3146 int bSort; /* True if external sort required */ 3040 int bDist = !!pDistinct; /* True if index cannot help with DISTINCT */ | 3147 int bDist; /* True if index cannot help with DISTINCT */ 3041 int bLookup = 0; /* True if not a covering index */ 3148 int bLookup = 0; /* True if not a covering index */ > 3149 int nPriorSat; /* ORDER BY terms satisfied by outer loops */ > 3150 int nOrderBy; /* Number of ORDER BY terms */ 3042 WhereTerm *pTerm; /* A single term of the WHERE clause */ 3151 WhereTerm *pTerm; /* A single term of the WHERE clause */ 3043 #ifdef SQLITE_ENABLE_STAT3 3152 #ifdef SQLITE_ENABLE_STAT3 3044 WhereTerm *pFirstTerm = 0; /* First term matching the index */ 3153 WhereTerm *pFirstTerm = 0; /* First term matching the index */ 3045 #endif 3154 #endif 3046 3155 > 3156 memset(&pc, 0, sizeof(pc)); > 3157 nOrderBy = p->pOrderBy ? p->pOrderBy->nExpr : 0; > 3158 if( p->i ){ > 3159 nPriorSat = pc.plan.nOBSat = p->aLevel[p->i-1].plan.nOBSat; > 3160 bSort = nPriorSat<nOrderBy; > 3161 bDist = 0; > 3162 }else{ > 3163 nPriorSat = pc.plan.nOBSat = 0; > 3164 bSort = nOrderBy>0; > 3165 bDist = p->pDistinct!=0; > 3166 } > 3167 3047 /* Determine the values of nEq and nInMul */ | 3168 /* Determine the values of pc.plan.nEq and nInMul */ 3048 for(nEq=0; nEq<pProbe->nColumn; nEq++){ | 3169 for(pc.plan.nEq=nOrdered=0; pc.plan.nEq<pProbe->nColumn; pc.plan.nEq++){ 3049 int j = pProbe->aiColumn[nEq]; | 3170 int j = pProbe->aiColumn[pc.plan.nEq]; 3050 pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx); | 3171 pTerm = findTerm(pWC, iCur, j, p->notReady, eqTermMask, pIdx); 3051 if( pTerm==0 ) break; 3172 if( pTerm==0 ) break; 3052 wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ); | 3173 pc.plan.wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ); 3053 testcase( pTerm->pWC!=pWC ); 3174 testcase( pTerm->pWC!=pWC ); 3054 if( pTerm->eOperator & WO_IN ){ 3175 if( pTerm->eOperator & WO_IN ){ 3055 Expr *pExpr = pTerm->pExpr; 3176 Expr *pExpr = pTerm->pExpr; 3056 wsFlags |= WHERE_COLUMN_IN; | 3177 pc.plan.wsFlags |= WHERE_COLUMN_IN; 3057 if( ExprHasProperty(pExpr, EP_xIsSelect) ){ 3178 if( ExprHasProperty(pExpr, EP_xIsSelect) ){ 3058 /* "x IN (SELECT ...)": Assume the SELECT returns 25 rows */ 3179 /* "x IN (SELECT ...)": Assume the SELECT returns 25 rows */ 3059 nInMul *= 25; 3180 nInMul *= 25; 3060 bInEst = 1; 3181 bInEst = 1; 3061 }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ 3182 }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){ 3062 /* "x IN (value, value, ...)" */ 3183 /* "x IN (value, value, ...)" */ 3063 nInMul *= pExpr->x.pList->nExpr; 3184 nInMul *= pExpr->x.pList->nExpr; 3064 } 3185 } 3065 }else if( pTerm->eOperator & WO_ISNULL ){ 3186 }else if( pTerm->eOperator & WO_ISNULL ){ 3066 wsFlags |= WHERE_COLUMN_NULL; | 3187 pc.plan.wsFlags |= WHERE_COLUMN_NULL; > 3188 if( pc.plan.nEq==nOrdered ) nOrdered++; > 3189 }else if( bSort && pc.plan.nEq==nOrdered > 3190 && isOrderedTerm(p,pTerm,&bRev) ){ > 3191 nOrdered++; 3067 } 3192 } 3068 #ifdef SQLITE_ENABLE_STAT3 3193 #ifdef SQLITE_ENABLE_STAT3 3069 if( nEq==0 && pProbe->aSample ) pFirstTerm = pTerm; | 3194 if( pc.plan.nEq==0 && pProbe->aSample ) pFirstTerm = pTerm; 3070 #endif 3195 #endif 3071 used |= pTerm->prereqRight; | 3196 pc.used |= pTerm->prereqRight; 3072 } 3197 } 3073 3198 3074 /* If the index being considered is UNIQUE, and there is an equality 3199 /* If the index being considered is UNIQUE, and there is an equality 3075 ** constraint for all columns in the index, then this search will find 3200 ** constraint for all columns in the index, then this search will find 3076 ** at most a single row. In this case set the WHERE_UNIQUE flag to 3201 ** at most a single row. In this case set the WHERE_UNIQUE flag to 3077 ** indicate this to the caller. 3202 ** indicate this to the caller. 3078 ** 3203 ** 3079 ** Otherwise, if the search may find more than one row, test to see if 3204 ** Otherwise, if the search may find more than one row, test to see if 3080 ** there is a range constraint on indexed column (nEq+1) that can be | 3205 ** there is a range constraint on indexed column (pc.plan.nEq+1) that can be 3081 ** optimized using the index. 3206 ** optimized using the index. 3082 */ 3207 */ 3083 if( nEq==pProbe->nColumn && pProbe->onError!=OE_None ){ | 3208 if( pc.plan.nEq==pProbe->nColumn && pProbe->onError!=OE_None ){ 3084 testcase( wsFlags & WHERE_COLUMN_IN ); | 3209 testcase( pc.plan.wsFlags & WHERE_COLUMN_IN ); 3085 testcase( wsFlags & WHERE_COLUMN_NULL ); | 3210 testcase( pc.plan.wsFlags & WHERE_COLUMN_NULL ); 3086 if( (wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0 ){ | 3211 if( (pc.plan.wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0 ){ 3087 wsFlags |= WHERE_UNIQUE; | 3212 pc.plan.wsFlags |= WHERE_UNIQUE; > 3213 if( p->i==0 || (p->aLevel[p->i-1].plan.wsFlags & WHERE_ALL_UNIQUE)!=0 ){ > 3214 pc.plan.wsFlags |= WHERE_ALL_UNIQUE; 3088 } | 3215 } > 3216 } 3089 }else if( pProbe->bUnordered==0 ){ 3217 }else if( pProbe->bUnordered==0 ){ > 3218 int j; 3090 int j = (nEq==pProbe->nColumn ? -1 : pProbe->aiColumn[nEq]); | 3219 j = (pc.plan.nEq==pProbe->nColumn ? -1 : pProbe->aiColumn[pc.plan.nEq]); 3091 if( findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pIdx) ){ | 3220 if( findTerm(pWC, iCur, j, p->notReady, WO_LT|WO_LE|WO_GT|WO_GE, pIdx) ){ > 3221 WhereTerm *pTop, *pBtm; 3092 WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE, pIdx); | 3222 pTop = findTerm(pWC, iCur, j, p->notReady, WO_LT|WO_LE, pIdx); 3093 WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT|WO_GE, pIdx); | 3223 pBtm = findTerm(pWC, iCur, j, p->notReady, WO_GT|WO_GE, pIdx); 3094 whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &rangeDiv); | 3224 whereRangeScanEst(pParse, pProbe, pc.plan.nEq, pBtm, pTop, &rangeDiv); 3095 if( pTop ){ 3225 if( pTop ){ 3096 nBound = 1; 3226 nBound = 1; 3097 wsFlags |= WHERE_TOP_LIMIT; | 3227 pc.plan.wsFlags |= WHERE_TOP_LIMIT; 3098 used |= pTop->prereqRight; | 3228 pc.used |= pTop->prereqRight; 3099 testcase( pTop->pWC!=pWC ); 3229 testcase( pTop->pWC!=pWC ); 3100 } 3230 } 3101 if( pBtm ){ 3231 if( pBtm ){ 3102 nBound++; 3232 nBound++; 3103 wsFlags |= WHERE_BTM_LIMIT; | 3233 pc.plan.wsFlags |= WHERE_BTM_LIMIT; 3104 used |= pBtm->prereqRight; | 3234 pc.used |= pBtm->prereqRight; 3105 testcase( pBtm->pWC!=pWC ); 3235 testcase( pBtm->pWC!=pWC ); 3106 } 3236 } 3107 wsFlags |= (WHERE_COLUMN_RANGE|WHERE_ROWID_RANGE); | 3237 pc.plan.wsFlags |= (WHERE_COLUMN_RANGE|WHERE_ROWID_RANGE); 3108 } 3238 } 3109 } 3239 } 3110 3240 3111 /* If there is an ORDER BY clause and the index being considered will 3241 /* If there is an ORDER BY clause and the index being considered will 3112 ** naturally scan rows in the required order, set the appropriate flags 3242 ** naturally scan rows in the required order, set the appropriate flags 3113 ** in wsFlags. Otherwise, if there is an ORDER BY clause but the index | 3243 ** in pc.plan.wsFlags. Otherwise, if there is an ORDER BY clause but 3114 ** will scan rows in a different order, set the bSort variable. */ | 3244 ** the index will scan rows in a different order, set the bSort > 3245 ** variable. */ > 3246 assert( bRev>=0 && bRev<=2 ); > 3247 if( bSort ){ > 3248 testcase( bRev==0 ); > 3249 testcase( bRev==1 ); > 3250 testcase( bRev==2 ); 3115 if( isSortingIndex( | 3251 pc.plan.nOBSat = isSortingIndex(p, pProbe, iCur, nOrdered, 3116 pParse, pWC->pMaskSet, pProbe, iCur, pOrderBy, nEq, wsFlags, &rev) < 3117 ){ < > 3252 pc.plan.wsFlags, bRev&1, &bRev); > 3253 if( nPriorSat<pc.plan.nOBSat || (pc.plan.wsFlags & WHERE_UNIQUE)!=0 ){ > 3254 pc.plan.wsFlags |= WHERE_ORDERED; > 3255 } > 3256 if( nOrderBy==pc.plan.nOBSat ){ 3118 bSort = 0; | 3257 bSort = 0; 3119 wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_ORDERBY; | 3258 pc.plan.wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE; 3120 wsFlags |= (rev ? WHERE_REVERSE : 0); < 3121 } | 3259 } > 3260 if( bRev & 1 ) pc.plan.wsFlags |= WHERE_REVERSE; 3122 | 3261 } > 3262 3123 /* If there is a DISTINCT qualifier and this index will scan rows in 3263 /* If there is a DISTINCT qualifier and this index will scan rows in 3124 ** order of the DISTINCT expressions, clear bDist and set the appropriate 3264 ** order of the DISTINCT expressions, clear bDist and set the appropriate 3125 ** flags in wsFlags. */ | 3265 ** flags in pc.plan.wsFlags. */ > 3266 if( bDist 3126 if( isDistinctIndex(pParse, pWC, pProbe, iCur, pDistinct, nEq) | 3267 && isDistinctIndex(pParse, pWC, pProbe, iCur, p->pDistinct, pc.plan.nEq) 3127 && (wsFlags & WHERE_COLUMN_IN)==0 | 3268 && (pc.plan.wsFlags & WHERE_COLUMN_IN)==0 3128 ){ 3269 ){ 3129 bDist = 0; 3270 bDist = 0; 3130 wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_DISTINCT; | 3271 pc.plan.wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_DISTINCT; 3131 } 3272 } 3132 3273 3133 /* If currently calculating the cost of using an index (not the IPK 3274 /* If currently calculating the cost of using an index (not the IPK 3134 ** index), determine if all required column data may be obtained without 3275 ** index), determine if all required column data may be obtained without 3135 ** using the main table (i.e. if the index is a covering 3276 ** using the main table (i.e. if the index is a covering 3136 ** index for this query). If it is, set the WHERE_IDX_ONLY flag in 3277 ** index for this query). If it is, set the WHERE_IDX_ONLY flag in 3137 ** wsFlags. Otherwise, set the bLookup variable to true. */ | 3278 ** pc.plan.wsFlags. Otherwise, set the bLookup variable to true. */ 3138 if( pIdx ){ 3279 if( pIdx ){ 3139 Bitmask m = pSrc->colUsed; 3280 Bitmask m = pSrc->colUsed; 3140 int j; 3281 int j; 3141 for(j=0; j<pIdx->nColumn; j++){ 3282 for(j=0; j<pIdx->nColumn; j++){ 3142 int x = pIdx->aiColumn[j]; 3283 int x = pIdx->aiColumn[j]; 3143 if( x<BMS-1 ){ 3284 if( x<BMS-1 ){ 3144 m &= ~(((Bitmask)1)<<x); 3285 m &= ~(((Bitmask)1)<<x); 3145 } 3286 } 3146 } 3287 } 3147 if( m==0 ){ 3288 if( m==0 ){ 3148 wsFlags |= WHERE_IDX_ONLY; | 3289 pc.plan.wsFlags |= WHERE_IDX_ONLY; 3149 }else{ 3290 }else{ 3150 bLookup = 1; 3291 bLookup = 1; 3151 } 3292 } 3152 } 3293 } 3153 3294 3154 /* 3295 /* 3155 ** Estimate the number of rows of output. For an "x IN (SELECT...)" 3296 ** Estimate the number of rows of output. For an "x IN (SELECT...)" 3156 ** constraint, do not let the estimate exceed half the rows in the table. 3297 ** constraint, do not let the estimate exceed half the rows in the table. 3157 */ 3298 */ 3158 nRow = (double)(aiRowEst[nEq] * nInMul); | 3299 pc.plan.nRow = (double)(aiRowEst[pc.plan.nEq] * nInMul); 3159 if( bInEst && nRow*2>aiRowEst[0] ){ | 3300 if( bInEst && pc.plan.nRow*2>aiRowEst[0] ){ 3160 nRow = aiRowEst[0]/2; | 3301 pc.plan.nRow = aiRowEst[0]/2; 3161 nInMul = (int)(nRow / aiRowEst[nEq]); | 3302 nInMul = (int)(pc.plan.nRow / aiRowEst[pc.plan.nEq]); 3162 } 3303 } 3163 3304 3164 #ifdef SQLITE_ENABLE_STAT3 3305 #ifdef SQLITE_ENABLE_STAT3 3165 /* If the constraint is of the form x=VALUE or x IN (E1,E2,...) 3306 /* If the constraint is of the form x=VALUE or x IN (E1,E2,...) 3166 ** and we do not think that values of x are unique and if histogram 3307 ** and we do not think that values of x are unique and if histogram 3167 ** data is available for column x, then it might be possible 3308 ** data is available for column x, then it might be possible 3168 ** to get a better estimate on the number of rows based on 3309 ** to get a better estimate on the number of rows based on 3169 ** VALUE and how common that value is according to the histogram. 3310 ** VALUE and how common that value is according to the histogram. 3170 */ 3311 */ > 3312 if( pc.plan.nRow>(double)1 && pc.plan.nEq==1 3171 if( nRow>(double)1 && nEq==1 && pFirstTerm!=0 && aiRowEst[1]>1 ){ | 3313 && pFirstTerm!=0 && aiRowEst[1]>1 ){ 3172 assert( (pFirstTerm->eOperator & (WO_EQ|WO_ISNULL|WO_IN))!=0 ); 3314 assert( (pFirstTerm->eOperator & (WO_EQ|WO_ISNULL|WO_IN))!=0 ); 3173 if( pFirstTerm->eOperator & (WO_EQ|WO_ISNULL) ){ 3315 if( pFirstTerm->eOperator & (WO_EQ|WO_ISNULL) ){ 3174 testcase( pFirstTerm->eOperator==WO_EQ ); 3316 testcase( pFirstTerm->eOperator==WO_EQ ); 3175 testcase( pFirstTerm->eOperator==WO_ISNULL ); 3317 testcase( pFirstTerm->eOperator==WO_ISNULL ); 3176 whereEqualScanEst(pParse, pProbe, pFirstTerm->pExpr->pRight, &nRow); | 3318 whereEqualScanEst(pParse, pProbe, pFirstTerm->pExpr->pRight, > 3319 &pc.plan.nRow); 3177 }else if( bInEst==0 ){ 3320 }else if( bInEst==0 ){ 3178 assert( pFirstTerm->eOperator==WO_IN ); 3321 assert( pFirstTerm->eOperator==WO_IN ); 3179 whereInScanEst(pParse, pProbe, pFirstTerm->pExpr->x.pList, &nRow); | 3322 whereInScanEst(pParse, pProbe, pFirstTerm->pExpr->x.pList, > 3323 &pc.plan.nRow); 3180 } 3324 } 3181 } 3325 } 3182 #endif /* SQLITE_ENABLE_STAT3 */ 3326 #endif /* SQLITE_ENABLE_STAT3 */ 3183 3327 3184 /* Adjust the number of output rows and downward to reflect rows 3328 /* Adjust the number of output rows and downward to reflect rows 3185 ** that are excluded by range constraints. 3329 ** that are excluded by range constraints. 3186 */ 3330 */ 3187 nRow = nRow/rangeDiv; | 3331 pc.plan.nRow = pc.plan.nRow/rangeDiv; 3188 if( nRow<1 ) nRow = 1; | 3332 if( pc.plan.nRow<1 ) pc.plan.nRow = 1; 3189 3333 3190 /* Experiments run on real SQLite databases show that the time needed 3334 /* Experiments run on real SQLite databases show that the time needed 3191 ** to do a binary search to locate a row in a table or index is roughly 3335 ** to do a binary search to locate a row in a table or index is roughly 3192 ** log10(N) times the time to move from one row to the next row within 3336 ** log10(N) times the time to move from one row to the next row within 3193 ** a table or index. The actual times can vary, with the size of 3337 ** a table or index. The actual times can vary, with the size of 3194 ** records being an important factor. Both moves and searches are 3338 ** records being an important factor. Both moves and searches are 3195 ** slower with larger records, presumably because fewer records fit 3339 ** slower with larger records, presumably because fewer records fit ................................................................................................................................................................................ 3196 ** on one page and hence more pages have to be fetched. 3340 ** on one page and hence more pages have to be fetched. 3197 ** 3341 ** 3198 ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do 3342 ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do 3199 ** not give us data on the relative sizes of table and index records. 3343 ** not give us data on the relative sizes of table and index records. 3200 ** So this computation assumes table records are about twice as big 3344 ** So this computation assumes table records are about twice as big 3201 ** as index records 3345 ** as index records 3202 */ 3346 */ 3203 if( wsFlags==WHERE_IDX_ONLY | 3347 if( (pc.plan.wsFlags&~(WHERE_REVERSE|WHERE_ORDERED))==WHERE_IDX_ONLY 3204 && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 3348 && (pWC->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 3205 && sqlite3GlobalConfig.bUseCis 3349 && sqlite3GlobalConfig.bUseCis 3206 #ifndef SQLITE_OMIT_BUILTIN_TEST < 3207 && (pParse->db->flags & SQLITE_CoverIdxScan)==0 | 3350 && OptimizationEnabled(pParse->db, SQLITE_CoverIdxScan) 3208 #endif < 3209 ){ 3351 ){ 3210 /* This index is not useful for indexing, but it is a covering index. 3352 /* This index is not useful for indexing, but it is a covering index. 3211 ** A full-scan of the index might be a little faster than a full-scan 3353 ** A full-scan of the index might be a little faster than a full-scan 3212 ** of the table, so give this case a cost slightly less than a table 3354 ** of the table, so give this case a cost slightly less than a table 3213 ** scan. */ 3355 ** scan. */ 3214 cost = aiRowEst[0]*3 + pProbe->nColumn; | 3356 pc.rCost = aiRowEst[0]*3 + pProbe->nColumn; 3215 wsFlags |= WHERE_COVER_SCAN|WHERE_COLUMN_RANGE; | 3357 pc.plan.wsFlags |= WHERE_COVER_SCAN|WHERE_COLUMN_RANGE; 3216 }else if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ){ | 3358 }else if( (pc.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ){ 3217 /* The cost of a full table scan is a number of move operations equal 3359 /* The cost of a full table scan is a number of move operations equal 3218 ** to the number of rows in the table. 3360 ** to the number of rows in the table. 3219 ** 3361 ** 3220 ** We add an additional 4x penalty to full table scans. This causes 3362 ** We add an additional 4x penalty to full table scans. This causes 3221 ** the cost function to err on the side of choosing an index over 3363 ** the cost function to err on the side of choosing an index over 3222 ** choosing a full scan. This 4x full-scan penalty is an arguable 3364 ** choosing a full scan. This 4x full-scan penalty is an arguable 3223 ** decision and one which we expect to revisit in the future. But 3365 ** decision and one which we expect to revisit in the future. But 3224 ** it seems to be working well enough at the moment. 3366 ** it seems to be working well enough at the moment. 3225 */ 3367 */ 3226 cost = aiRowEst[0]*4; | 3368 pc.rCost = aiRowEst[0]*4; 3227 wsFlags &= ~WHERE_IDX_ONLY; | 3369 pc.plan.wsFlags &= ~WHERE_IDX_ONLY; > 3370 if( pIdx ) pc.plan.wsFlags &= ~WHERE_ORDERED; 3228 }else{ 3371 }else{ 3229 log10N = estLog(aiRowEst[0]); 3372 log10N = estLog(aiRowEst[0]); 3230 cost = nRow; < > 3373 pc.rCost = pc.plan.nRow; 3231 if( pIdx ){ 3374 if( pIdx ){ 3232 if( bLookup ){ 3375 if( bLookup ){ 3233 /* For an index lookup followed by a table lookup: 3376 /* For an index lookup followed by a table lookup: 3234 ** nInMul index searches to find the start of each index range 3377 ** nInMul index searches to find the start of each index range 3235 ** + nRow steps through the index 3378 ** + nRow steps through the index 3236 ** + nRow table searches to lookup the table entry using the rowid 3379 ** + nRow table searches to lookup the table entry using the rowid 3237 */ 3380 */ 3238 cost += (nInMul + nRow)*log10N; | 3381 pc.rCost += (nInMul + pc.plan.nRow)*log10N; 3239 }else{ 3382 }else{ 3240 /* For a covering index: 3383 /* For a covering index: 3241 ** nInMul index searches to find the initial entry 3384 ** nInMul index searches to find the initial entry 3242 ** + nRow steps through the index 3385 ** + nRow steps through the index 3243 */ 3386 */ 3244 cost += nInMul*log10N; | 3387 pc.rCost += nInMul*log10N; 3245 } 3388 } 3246 }else{ 3389 }else{ 3247 /* For a rowid primary key lookup: 3390 /* For a rowid primary key lookup: 3248 ** nInMult table searches to find the initial entry for each range 3391 ** nInMult table searches to find the initial entry for each range 3249 ** + nRow steps through the table 3392 ** + nRow steps through the table 3250 */ 3393 */ 3251 cost += nInMul*log10N; | 3394 pc.rCost += nInMul*log10N; 3252 } 3395 } 3253 } 3396 } 3254 3397 3255 /* Add in the estimated cost of sorting the result. Actual experimental 3398 /* Add in the estimated cost of sorting the result. Actual experimental 3256 ** measurements of sorting performance in SQLite show that sorting time 3399 ** measurements of sorting performance in SQLite show that sorting time 3257 ** adds C*N*log10(N) to the cost, where N is the number of rows to be 3400 ** adds C*N*log10(N) to the cost, where N is the number of rows to be 3258 ** sorted and C is a factor between 1.95 and 4.3. We will split the 3401 ** sorted and C is a factor between 1.95 and 4.3. We will split the 3259 ** difference and select C of 3.0. 3402 ** difference and select C of 3.0. 3260 */ 3403 */ 3261 if( bSort ){ 3404 if( bSort ){ 3262 cost += nRow*estLog(nRow)*3; | 3405 double m = estLog(pc.plan.nRow*(nOrderBy - pc.plan.nOBSat)/nOrderBy); > 3406 m *= (double)(pc.plan.nOBSat ? 2 : 3); > 3407 pc.rCost += pc.plan.nRow*m; 3263 } 3408 } 3264 if( bDist ){ 3409 if( bDist ){ 3265 cost += nRow*estLog(nRow)*3; < > 3410 pc.rCost += pc.plan.nRow*estLog(pc.plan.nRow)*3; 3266 } 3411 } 3267 3412 3268 /**** Cost of using this index has now been computed ****/ 3413 /**** Cost of using this index has now been computed ****/ 3269 3414 3270 /* If there are additional constraints on this table that cannot 3415 /* If there are additional constraints on this table that cannot 3271 ** be used with the current index, but which might lower the number 3416 ** be used with the current index, but which might lower the number 3272 ** of output rows, adjust the nRow value accordingly. This only 3417 ** of output rows, adjust the nRow value accordingly. This only ................................................................................................................................................................................ 3279 ** mask will only have one bit set - the bit for the current table. 3424 ** mask will only have one bit set - the bit for the current table. 3280 ** The notValid mask, on the other hand, always has all bits set for 3425 ** The notValid mask, on the other hand, always has all bits set for 3281 ** tables that are not in outer loops. If notReady is used here instead 3426 ** tables that are not in outer loops. If notReady is used here instead 3282 ** of notValid, then a optimal index that depends on inner joins loops 3427 ** of notValid, then a optimal index that depends on inner joins loops 3283 ** might be selected even when there exists an optimal index that has 3428 ** might be selected even when there exists an optimal index that has 3284 ** no such dependency. 3429 ** no such dependency. 3285 */ 3430 */ 3286 if( nRow>2 && cost<=pCost->rCost ){ | 3431 if( pc.plan.nRow>2 && pc.rCost<=p->cost.rCost ){ 3287 int k; /* Loop counter */ 3432 int k; /* Loop counter */ 3288 int nSkipEq = nEq; /* Number of == constraints to skip */ | 3433 int nSkipEq = pc.plan.nEq; /* Number of == constraints to skip */ 3289 int nSkipRange = nBound; /* Number of < constraints to skip */ 3434 int nSkipRange = nBound; /* Number of < constraints to skip */ 3290 Bitmask thisTab; /* Bitmap for pSrc */ 3435 Bitmask thisTab; /* Bitmap for pSrc */ 3291 3436 3292 thisTab = getMask(pWC->pMaskSet, iCur); 3437 thisTab = getMask(pWC->pMaskSet, iCur); 3293 for(pTerm=pWC->a, k=pWC->nTerm; nRow>2 && k; k--, pTerm++){ | 3438 for(pTerm=pWC->a, k=pWC->nTerm; pc.plan.nRow>2 && k; k--, pTerm++){ 3294 if( pTerm->wtFlags & TERM_VIRTUAL ) continue; 3439 if( pTerm->wtFlags & TERM_VIRTUAL ) continue; 3295 if( (pTerm->prereqAll & notValid)!=thisTab ) continue; | 3440 if( (pTerm->prereqAll & p->notValid)!=thisTab ) continue; 3296 if( pTerm->eOperator & (WO_EQ|WO_IN|WO_ISNULL) ){ 3441 if( pTerm->eOperator & (WO_EQ|WO_IN|WO_ISNULL) ){ 3297 if( nSkipEq ){ 3442 if( nSkipEq ){ 3298 /* Ignore the first nEq equality matches since the index | 3443 /* Ignore the first pc.plan.nEq equality matches since the index 3299 ** has already accounted for these */ 3444 ** has already accounted for these */ 3300 nSkipEq--; 3445 nSkipEq--; 3301 }else{ 3446 }else{ 3302 /* Assume each additional equality match reduces the result 3447 /* Assume each additional equality match reduces the result 3303 ** set size by a factor of 10 */ 3448 ** set size by a factor of 10 */ 3304 nRow /= 10; | 3449 pc.plan.nRow /= 10; 3305 } 3450 } 3306 }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GT|WO_GE) ){ 3451 }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GT|WO_GE) ){ 3307 if( nSkipRange ){ 3452 if( nSkipRange ){ 3308 /* Ignore the first nSkipRange range constraints since the index 3453 /* Ignore the first nSkipRange range constraints since the index 3309 ** has already accounted for these */ 3454 ** has already accounted for these */ 3310 nSkipRange--; 3455 nSkipRange--; 3311 }else{ 3456 }else{ 3312 /* Assume each additional range constraint reduces the result 3457 /* Assume each additional range constraint reduces the result 3313 ** set size by a factor of 3. Indexed range constraints reduce 3458 ** set size by a factor of 3. Indexed range constraints reduce 3314 ** the search space by a larger factor: 4. We make indexed range 3459 ** the search space by a larger factor: 4. We make indexed range 3315 ** more selective intentionally because of the subjective 3460 ** more selective intentionally because of the subjective 3316 ** observation that indexed range constraints really are more 3461 ** observation that indexed range constraints really are more 3317 ** selective in practice, on average. */ 3462 ** selective in practice, on average. */ 3318 nRow /= 3; | 3463 pc.plan.nRow /= 3; 3319 } 3464 } 3320 }else if( pTerm->eOperator!=WO_NOOP ){ 3465 }else if( pTerm->eOperator!=WO_NOOP ){ 3321 /* Any other expression lowers the output row count by half */ 3466 /* Any other expression lowers the output row count by half */ 3322 nRow /= 2; | 3467 pc.plan.nRow /= 2; 3323 } 3468 } 3324 } 3469 } 3325 if( nRow<2 ) nRow = 2; | 3470 if( pc.plan.nRow<2 ) pc.plan.nRow = 2; 3326 } 3471 } 3327 3472 3328 3473 3329 WHERETRACE(( 3474 WHERETRACE(( > 3475 "%s(%s):\n" 3330 "%s(%s): nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%x\n" | 3476 " nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%08x\n" 3331 " notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f used=0x%llx\n", | 3477 " notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f\n" > 3478 " used=0x%llx nOrdered=%d nOBSat=%d\n", 3332 pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), 3479 pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), 3333 nEq, nInMul, (int)rangeDiv, bSort, bLookup, wsFlags, | 3480 pc.plan.nEq, nInMul, (int)rangeDiv, bSort, bLookup, pc.plan.wsFlags, 3334 notReady, log10N, nRow, cost, used | 3481 p->notReady, log10N, pc.plan.nRow, pc.rCost, pc.used, nOrdered, > 3482 pc.plan.nOBSat 3335 )); 3483 )); 3336 3484 3337 /* If this index is the best we have seen so far, then record this 3485 /* If this index is the best we have seen so far, then record this 3338 ** index and its cost in the pCost structure. | 3486 ** index and its cost in the p->cost structure. 3339 */ 3487 */ 3340 if( (!pIdx || wsFlags) | 3488 if( (!pIdx || pc.plan.wsFlags) && compareCost(&pc, &p->cost) ){ 3341 && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->plan.nRow)) < 3342 ){ < 3343 pCost->rCost = cost; | 3489 p->cost = pc; 3344 pCost->used = used; < 3345 pCost->plan.nRow = nRow; < 3346 pCost->plan.wsFlags = (wsFlags&wsFlagMask); < 3347 pCost->plan.nEq = nEq; < > 3490 p->cost.plan.wsFlags &= wsFlagMask; 3348 pCost->plan.u.pIdx = pIdx; | 3491 p->cost.plan.u.pIdx = pIdx; 3349 } 3492 } 3350 3493 3351 /* If there was an INDEXED BY clause, then only that one index is 3494 /* If there was an INDEXED BY clause, then only that one index is 3352 ** considered. */ 3495 ** considered. */ 3353 if( pSrc->pIndex ) break; 3496 if( pSrc->pIndex ) break; 3354 3497 3355 /* Reset masks for the next index in the loop */ 3498 /* Reset masks for the next index in the loop */ ................................................................................................................................................................................ 3358 } 3501 } 3359 3502 3360 /* If there is no ORDER BY clause and the SQLITE_ReverseOrder flag 3503 /* If there is no ORDER BY clause and the SQLITE_ReverseOrder flag 3361 ** is set, then reverse the order that the index will be scanned 3504 ** is set, then reverse the order that the index will be scanned 3362 ** in. This is used for application testing, to help find cases 3505 ** in. This is used for application testing, to help find cases 3363 ** where application behaviour depends on the (undefined) order that 3506 ** where application behaviour depends on the (undefined) order that 3364 ** SQLite outputs rows in in the absence of an ORDER BY clause. */ 3507 ** SQLite outputs rows in in the absence of an ORDER BY clause. */ 3365 if( !pOrderBy && pParse->db->flags & SQLITE_ReverseOrder ){ | 3508 if( !p->pOrderBy && pParse->db->flags & SQLITE_ReverseOrder ){ 3366 pCost->plan.wsFlags |= WHERE_REVERSE; | 3509 p->cost.plan.wsFlags |= WHERE_REVERSE; 3367 } 3510 } 3368 3511 3369 assert( pOrderBy || (pCost->plan.wsFlags&WHERE_ORDERBY)==0 ); | 3512 assert( p->pOrderBy || (p->cost.plan.wsFlags&WHERE_ORDERED)==0 ); 3370 assert( pCost->plan.u.pIdx==0 || (pCost->plan.wsFlags&WHERE_ROWID_EQ)==0 ); | 3513 assert( p->cost.plan.u.pIdx==0 || (p->cost.plan.wsFlags&WHERE_ROWID_EQ)==0 ); 3371 assert( pSrc->pIndex==0 3514 assert( pSrc->pIndex==0 3372 || pCost->plan.u.pIdx==0 | 3515 || p->cost.plan.u.pIdx==0 3373 || pCost->plan.u.pIdx==pSrc->pIndex | 3516 || p->cost.plan.u.pIdx==pSrc->pIndex 3374 ); 3517 ); 3375 3518 3376 WHERETRACE(("best index is: %s\n", | 3519 WHERETRACE(("best index is: %s\n", 3377 ((pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ? "none" : < 3378 pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk") | 3520 p->cost.plan.u.pIdx ? p->cost.plan.u.pIdx->zName : "ipk")); 3379 )); < 3380 3521 3381 bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost); | 3522 bestOrClauseIndex(p); 3382 bestAutomaticIndex(pParse, pWC, pSrc, notReady, pCost); | 3523 bestAutomaticIndex(p); 3383 pCost->plan.wsFlags |= eqTermMask; | 3524 p->cost.plan.wsFlags |= eqTermMask; 3384 } 3525 } 3385 3526 3386 /* 3527 /* 3387 ** Find the query plan for accessing table pSrc->pTab. Write the 3528 ** Find the query plan for accessing table pSrc->pTab. Write the 3388 ** best query plan and its cost into the WhereCost object supplied 3529 ** best query plan and its cost into the WhereCost object supplied 3389 ** as the last parameter. This function may calculate the cost of 3530 ** as the last parameter. This function may calculate the cost of 3390 ** both real and virtual table scans. 3531 ** both real and virtual table scans. ................................................................................................................................................................................ 3391 ** 3532 ** 3392 ** This function does not take ORDER BY or DISTINCT into account. Nor 3533 ** This function does not take ORDER BY or DISTINCT into account. Nor 3393 ** does it remember the virtual table query plan. All it does is compute 3534 ** does it remember the virtual table query plan. All it does is compute 3394 ** the cost while determining if an OR optimization is applicable. The 3535 ** the cost while determining if an OR optimization is applicable. The 3395 ** details will be reconsidered later if the optimization is found to be 3536 ** details will be reconsidered later if the optimization is found to be 3396 ** applicable. 3537 ** applicable. 3397 */ 3538 */ 3398 static void bestIndex( | 3539 static void bestIndex(WhereBestIdx *p){ 3399 Parse *pParse, /* The parsing context */ < 3400 WhereClause *pWC, /* The WHERE clause */ < 3401 struct SrcList_item *pSrc, /* The FROM clause term to search */ < 3402 Bitmask notReady, /* Mask of cursors not available for indexing */ < 3403 Bitmask notValid, /* Cursors not available for any purpose */ < 3404 WhereCost *pCost /* Lowest cost query plan */ < 3405 ){ < 3406 #ifndef SQLITE_OMIT_VIRTUALTABLE 3540 #ifndef SQLITE_OMIT_VIRTUALTABLE 3407 if( IsVirtual(pSrc->pTab) ){ | 3541 if( IsVirtual(p->pSrc->pTab) ){ 3408 sqlite3_index_info *p = 0; | 3542 sqlite3_index_info *pIdxInfo = 0; > 3543 p->ppIdxInfo = &pIdxInfo; 3409 bestVirtualIndex(pParse, pWC, pSrc, notReady, notValid, 0, pCost, &p); | 3544 bestVirtualIndex(p); 3410 if( p->needToFreeIdxStr ){ | 3545 if( pIdxInfo->needToFreeIdxStr ){ 3411 sqlite3_free(p->idxStr); | 3546 sqlite3_free(pIdxInfo->idxStr); 3412 } 3547 } 3413 sqlite3DbFree(pParse->db, p); | 3548 sqlite3DbFree(p->pParse->db, pIdxInfo); 3414 }else 3549 }else 3415 #endif 3550 #endif 3416 { 3551 { 3417 bestBtreeIndex(pParse, pWC, pSrc, notReady, notValid, 0, 0, pCost); | 3552 bestBtreeIndex(p); 3418 } 3553 } 3419 } 3554 } 3420 3555 3421 /* 3556 /* 3422 ** Disable a term in the WHERE clause. Except, do not disable the term 3557 ** Disable a term in the WHERE clause. Except, do not disable the term 3423 ** if it controls a LEFT OUTER JOIN and it did not originate in the ON 3558 ** if it controls a LEFT OUTER JOIN and it did not originate in the ON 3424 ** or USING clause of that join. 3559 ** or USING clause of that join. ................................................................................................................................................................................ 4106 ** query, then the caller will only allow the loop to run for 4241 ** query, then the caller will only allow the loop to run for 4107 ** a single iteration. This means that the first row returned 4242 ** a single iteration. This means that the first row returned 4108 ** should not have a NULL value stored in 'x'. If column 'x' is 4243 ** should not have a NULL value stored in 'x'. If column 'x' is 4109 ** the first one after the nEq equality constraints in the index, 4244 ** the first one after the nEq equality constraints in the index, 4110 ** this requires some special handling. 4245 ** this requires some special handling. 4111 */ 4246 */ 4112 if( (wctrlFlags&WHERE_ORDERBY_MIN)!=0 4247 if( (wctrlFlags&WHERE_ORDERBY_MIN)!=0 4113 && (pLevel->plan.wsFlags&WHERE_ORDERBY) | 4248 && (pLevel->plan.wsFlags&WHERE_ORDERED) 4114 && (pIdx->nColumn>nEq) 4249 && (pIdx->nColumn>nEq) 4115 ){ 4250 ){ 4116 /* assert( pOrderBy->nExpr==1 ); */ 4251 /* assert( pOrderBy->nExpr==1 ); */ 4117 /* assert( pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq] ); */ 4252 /* assert( pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq] ); */ 4118 isMinQuery = 1; 4253 isMinQuery = 1; 4119 nExtraReg = 1; 4254 nExtraReg = 1; 4120 } 4255 } ................................................................................................................................................................................ 4690 SrcList *pTabList, /* A list of all tables to be scanned */ 4825 SrcList *pTabList, /* A list of all tables to be scanned */ 4691 Expr *pWhere, /* The WHERE clause */ 4826 Expr *pWhere, /* The WHERE clause */ 4692 ExprList *pOrderBy, /* An ORDER BY clause, or NULL */ 4827 ExprList *pOrderBy, /* An ORDER BY clause, or NULL */ 4693 ExprList *pDistinct, /* The select-list for DISTINCT queries - or NULL */ 4828 ExprList *pDistinct, /* The select-list for DISTINCT queries - or NULL */ 4694 u16 wctrlFlags, /* One of the WHERE_* flags defined in sqliteInt.h */ 4829 u16 wctrlFlags, /* One of the WHERE_* flags defined in sqliteInt.h */ 4695 int iIdxCur /* If WHERE_ONETABLE_ONLY is set, index cursor number */ 4830 int iIdxCur /* If WHERE_ONETABLE_ONLY is set, index cursor number */ 4696 ){ 4831 ){ 4697 int i; /* Loop counter */ < 4698 int nByteWInfo; /* Num. bytes allocated for WhereInfo struct */ 4832 int nByteWInfo; /* Num. bytes allocated for WhereInfo struct */ 4699 int nTabList; /* Number of elements in pTabList */ 4833 int nTabList; /* Number of elements in pTabList */ 4700 WhereInfo *pWInfo; /* Will become the return value of this function */ 4834 WhereInfo *pWInfo; /* Will become the return value of this function */ 4701 Vdbe *v = pParse->pVdbe; /* The virtual database engine */ 4835 Vdbe *v = pParse->pVdbe; /* The virtual database engine */ 4702 Bitmask notReady; /* Cursors that are not yet positioned */ 4836 Bitmask notReady; /* Cursors that are not yet positioned */ > 4837 WhereBestIdx sWBI; /* Best index search context */ 4703 WhereMaskSet *pMaskSet; /* The expression mask set */ 4838 WhereMaskSet *pMaskSet; /* The expression mask set */ 4704 WhereClause *pWC; /* Decomposition of the WHERE clause */ < 4705 struct SrcList_item *pTabItem; /* A single entry from pTabList */ < 4706 WhereLevel *pLevel; /* A single level in pWInfo->a[] */ | 4839 WhereLevel *pLevel; /* A single level in pWInfo->a[] */ 4707 int iFrom; /* First unused FROM clause element */ | 4840 int iFrom; /* First unused FROM clause element */ 4708 int andFlags; /* AND-ed combination of all pWC->a[].wtFlags */ 4841 int andFlags; /* AND-ed combination of all pWC->a[].wtFlags */ > 4842 int ii; /* Loop counter */ 4709 sqlite3 *db; /* Database connection */ 4843 sqlite3 *db; /* Database connection */ 4710 4844 > 4845 > 4846 /* Variable initialization */ > 4847 memset(&sWBI, 0, sizeof(sWBI)); > 4848 sWBI.pParse = pParse; > 4849 4711 /* The number of tables in the FROM clause is limited by the number of 4850 /* The number of tables in the FROM clause is limited by the number of 4712 ** bits in a Bitmask 4851 ** bits in a Bitmask 4713 */ 4852 */ 4714 testcase( pTabList->nSrc==BMS ); 4853 testcase( pTabList->nSrc==BMS ); 4715 if( pTabList->nSrc>BMS ){ 4854 if( pTabList->nSrc>BMS ){ 4716 sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS); 4855 sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS); 4717 return 0; 4856 return 0; ................................................................................................................................................................................ 4743 pWInfo = 0; 4882 pWInfo = 0; 4744 goto whereBeginError; 4883 goto whereBeginError; 4745 } 4884 } 4746 pWInfo->nLevel = nTabList; 4885 pWInfo->nLevel = nTabList; 4747 pWInfo->pParse = pParse; 4886 pWInfo->pParse = pParse; 4748 pWInfo->pTabList = pTabList; 4887 pWInfo->pTabList = pTabList; 4749 pWInfo->iBreak = sqlite3VdbeMakeLabel(v); 4888 pWInfo->iBreak = sqlite3VdbeMakeLabel(v); 4750 pWInfo->pWC = pWC = (WhereClause *)&((u8 *)pWInfo)[nByteWInfo]; | 4889 pWInfo->pWC = sWBI.pWC = (WhereClause *)&((u8 *)pWInfo)[nByteWInfo]; 4751 pWInfo->wctrlFlags = wctrlFlags; 4890 pWInfo->wctrlFlags = wctrlFlags; 4752 pWInfo->savedNQueryLoop = pParse->nQueryLoop; 4891 pWInfo->savedNQueryLoop = pParse->nQueryLoop; 4753 pMaskSet = (WhereMaskSet*)&pWC[1]; | 4892 pMaskSet = (WhereMaskSet*)&sWBI.pWC[1]; > 4893 sWBI.aLevel = pWInfo->a; 4754 4894 4755 /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via 4895 /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via 4756 ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */ 4896 ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */ 4757 if( db->flags & SQLITE_DistinctOpt ) pDistinct = 0; | 4897 if( OptimizationDisabled(db, SQLITE_DistinctOpt) ) pDistinct = 0; 4758 4898 4759 /* Split the WHERE clause into separate subexpressions where each 4899 /* Split the WHERE clause into separate subexpressions where each 4760 ** subexpression is separated by an AND operator. 4900 ** subexpression is separated by an AND operator. 4761 */ 4901 */ 4762 initMaskSet(pMaskSet); 4902 initMaskSet(pMaskSet); 4763 whereClauseInit(pWC, pParse, pMaskSet, wctrlFlags); | 4903 whereClauseInit(sWBI.pWC, pParse, pMaskSet, wctrlFlags); 4764 sqlite3ExprCodeConstants(pParse, pWhere); 4904 sqlite3ExprCodeConstants(pParse, pWhere); 4765 whereSplit(pWC, pWhere, TK_AND); /* IMP: R-15842-53296 */ | 4905 whereSplit(sWBI.pWC, pWhere, TK_AND); /* IMP: R-15842-53296 */ 4766 4906 4767 /* Special case: a WHERE clause that is constant. Evaluate the 4907 /* Special case: a WHERE clause that is constant. Evaluate the 4768 ** expression and either jump over all of the code or fall thru. 4908 ** expression and either jump over all of the code or fall thru. 4769 */ 4909 */ 4770 if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){ 4910 if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){ 4771 sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL); 4911 sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL); 4772 pWhere = 0; 4912 pWhere = 0; ................................................................................................................................................................................ 4789 ** with virtual tables. 4929 ** with virtual tables. 4790 ** 4930 ** 4791 ** Note that bitmasks are created for all pTabList->nSrc tables in 4931 ** Note that bitmasks are created for all pTabList->nSrc tables in 4792 ** pTabList, not just the first nTabList tables. nTabList is normally 4932 ** pTabList, not just the first nTabList tables. nTabList is normally 4793 ** equal to pTabList->nSrc but might be shortened to 1 if the 4933 ** equal to pTabList->nSrc but might be shortened to 1 if the 4794 ** WHERE_ONETABLE_ONLY flag is set. 4934 ** WHERE_ONETABLE_ONLY flag is set. 4795 */ 4935 */ 4796 assert( pWC->vmask==0 && pMaskSet->n==0 ); | 4936 assert( sWBI.pWC->vmask==0 && pMaskSet->n==0 ); 4797 for(i=0; i<pTabList->nSrc; i++){ | 4937 for(ii=0; ii<pTabList->nSrc; ii++){ 4798 createMask(pMaskSet, pTabList->a[i].iCursor); | 4938 createMask(pMaskSet, pTabList->a[ii].iCursor); 4799 #ifndef SQLITE_OMIT_VIRTUALTABLE 4939 #ifndef SQLITE_OMIT_VIRTUALTABLE 4800 if( ALWAYS(pTabList->a[i].pTab) && IsVirtual(pTabList->a[i].pTab) ){ | 4940 if( ALWAYS(pTabList->a[ii].pTab) && IsVirtual(pTabList->a[ii].pTab) ){ 4801 pWC->vmask |= ((Bitmask)1 << i); | 4941 sWBI.pWC->vmask |= ((Bitmask)1 << ii); 4802 } 4942 } 4803 #endif 4943 #endif 4804 } 4944 } 4805 #ifndef NDEBUG 4945 #ifndef NDEBUG 4806 { 4946 { 4807 Bitmask toTheLeft = 0; 4947 Bitmask toTheLeft = 0; 4808 for(i=0; i<pTabList->nSrc; i++){ | 4948 for(ii=0; ii<pTabList->nSrc; ii++){ 4809 Bitmask m = getMask(pMaskSet, pTabList->a[i].iCursor); | 4949 Bitmask m = getMask(pMaskSet, pTabList->a[ii].iCursor); 4810 assert( (m-1)==toTheLeft ); 4950 assert( (m-1)==toTheLeft ); 4811 toTheLeft |= m; 4951 toTheLeft |= m; 4812 } 4952 } 4813 } 4953 } 4814 #endif 4954 #endif 4815 4955 4816 /* Analyze all of the subexpressions. Note that exprAnalyze() might 4956 /* Analyze all of the subexpressions. Note that exprAnalyze() might 4817 ** add new virtual terms onto the end of the WHERE clause. We do not 4957 ** add new virtual terms onto the end of the WHERE clause. We do not 4818 ** want to analyze these virtual terms, so start analyzing at the end 4958 ** want to analyze these virtual terms, so start analyzing at the end 4819 ** and work forward so that the added virtual terms are never processed. 4959 ** and work forward so that the added virtual terms are never processed. 4820 */ 4960 */ 4821 exprAnalyzeAll(pTabList, pWC); | 4961 exprAnalyzeAll(pTabList, sWBI.pWC); 4822 if( db->mallocFailed ){ 4962 if( db->mallocFailed ){ 4823 goto whereBeginError; 4963 goto whereBeginError; 4824 } 4964 } 4825 4965 4826 /* Check if the DISTINCT qualifier, if there is one, is redundant. 4966 /* Check if the DISTINCT qualifier, if there is one, is redundant. 4827 ** If it is, then set pDistinct to NULL and WhereInfo.eDistinct to 4967 ** If it is, then set pDistinct to NULL and WhereInfo.eDistinct to 4828 ** WHERE_DISTINCT_UNIQUE to tell the caller to ignore the DISTINCT. 4968 ** WHERE_DISTINCT_UNIQUE to tell the caller to ignore the DISTINCT. 4829 */ 4969 */ 4830 if( pDistinct && isDistinctRedundant(pParse, pTabList, pWC, pDistinct) ){ | 4970 if( pDistinct && isDistinctRedundant(pParse, pTabList, sWBI.pWC, pDistinct) ){ 4831 pDistinct = 0; 4971 pDistinct = 0; 4832 pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; 4972 pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; 4833 } 4973 } 4834 4974 4835 /* Chose the best index to use for each table in the FROM clause. 4975 /* Chose the best index to use for each table in the FROM clause. 4836 ** 4976 ** 4837 ** This loop fills in the following fields: 4977 ** This loop fills in the following fields: ................................................................................................................................................................................ 4843 ** pWInfo->a[].iTabCur The VDBE cursor for the database table 4983 ** pWInfo->a[].iTabCur The VDBE cursor for the database table 4844 ** pWInfo->a[].iIdxCur The VDBE cursor for the index 4984 ** pWInfo->a[].iIdxCur The VDBE cursor for the index 4845 ** pWInfo->a[].pTerm When wsFlags==WO_OR, the OR-clause term 4985 ** pWInfo->a[].pTerm When wsFlags==WO_OR, the OR-clause term 4846 ** 4986 ** 4847 ** This loop also figures out the nesting order of tables in the FROM 4987 ** This loop also figures out the nesting order of tables in the FROM 4848 ** clause. 4988 ** clause. 4849 */ 4989 */ 4850 notReady = ~(Bitmask)0; | 4990 sWBI.notValid = ~(Bitmask)0; > 4991 sWBI.pOrderBy = pOrderBy; > 4992 sWBI.n = nTabList; > 4993 sWBI.pDistinct = pDistinct; 4851 andFlags = ~0; 4994 andFlags = ~0; 4852 WHERETRACE(("*** Optimizer Start ***\n")); 4995 WHERETRACE(("*** Optimizer Start ***\n")); 4853 for(i=iFrom=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){ | 4996 for(sWBI.i=iFrom=0, pLevel=pWInfo->a; sWBI.i<nTabList; sWBI.i++, pLevel++){ 4854 WhereCost bestPlan; /* Most efficient plan seen so far */ 4997 WhereCost bestPlan; /* Most efficient plan seen so far */ 4855 Index *pIdx; /* Index for FROM table at pTabItem */ 4998 Index *pIdx; /* Index for FROM table at pTabItem */ 4856 int j; /* For looping over FROM tables */ 4999 int j; /* For looping over FROM tables */ 4857 int bestJ = -1; /* The value of j */ 5000 int bestJ = -1; /* The value of j */ 4858 Bitmask m; /* Bitmask value for j or bestJ */ 5001 Bitmask m; /* Bitmask value for j or bestJ */ 4859 int isOptimal; /* Iterator for optimal/non-optimal search */ 5002 int isOptimal; /* Iterator for optimal/non-optimal search */ 4860 int nUnconstrained; /* Number tables without INDEXED BY */ 5003 int nUnconstrained; /* Number tables without INDEXED BY */ 4861 Bitmask notIndexed; /* Mask of tables that cannot use an index */ 5004 Bitmask notIndexed; /* Mask of tables that cannot use an index */ 4862 5005 4863 memset(&bestPlan, 0, sizeof(bestPlan)); 5006 memset(&bestPlan, 0, sizeof(bestPlan)); 4864 bestPlan.rCost = SQLITE_BIG_DBL; 5007 bestPlan.rCost = SQLITE_BIG_DBL; 4865 WHERETRACE(("*** Begin search for loop %d ***\n", i)); | 5008 WHERETRACE(("*** Begin search for loop %d ***\n", sWBI.i)); 4866 5009 4867 /* Loop through the remaining entries in the FROM clause to find the 5010 /* Loop through the remaining entries in the FROM clause to find the 4868 ** next nested loop. The loop tests all FROM clause entries 5011 ** next nested loop. The loop tests all FROM clause entries 4869 ** either once or twice. 5012 ** either once or twice. 4870 ** 5013 ** 4871 ** The first test is always performed if there are two or more entries 5014 ** The first test is always performed if there are two or more entries 4872 ** remaining and never performed if there is only one FROM clause entry 5015 ** remaining and never performed if there is only one FROM clause entry ................................................................................................................................................................................ 4874 ** this context an optimal scan is one that uses the same strategy 5017 ** this context an optimal scan is one that uses the same strategy 4875 ** for the given FROM clause entry as would be selected if the entry 5018 ** for the given FROM clause entry as would be selected if the entry 4876 ** were used as the innermost nested loop. In other words, a table 5019 ** were used as the innermost nested loop. In other words, a table 4877 ** is chosen such that the cost of running that table cannot be reduced 5020 ** is chosen such that the cost of running that table cannot be reduced 4878 ** by waiting for other tables to run first. This "optimal" test works 5021 ** by waiting for other tables to run first. This "optimal" test works 4879 ** by first assuming that the FROM clause is on the inner loop and finding 5022 ** by first assuming that the FROM clause is on the inner loop and finding 4880 ** its query plan, then checking to see if that query plan uses any 5023 ** its query plan, then checking to see if that query plan uses any 4881 ** other FROM clause terms that are notReady. If no notReady terms are | 5024 ** other FROM clause terms that are sWBI.notValid. If no notValid terms 4882 ** used then the "optimal" query plan works. | 5025 ** are used then the "optimal" query plan works. 4883 ** 5026 ** 4884 ** Note that the WhereCost.nRow parameter for an optimal scan might 5027 ** Note that the WhereCost.nRow parameter for an optimal scan might 4885 ** not be as small as it would be if the table really were the innermost 5028 ** not be as small as it would be if the table really were the innermost 4886 ** join. The nRow value can be reduced by WHERE clause constraints 5029 ** join. The nRow value can be reduced by WHERE clause constraints 4887 ** that do not use indices. But this nRow reduction only happens if the 5030 ** that do not use indices. But this nRow reduction only happens if the 4888 ** table really is the innermost join. 5031 ** table really is the innermost join. 4889 ** 5032 ** ................................................................................................................................................................................ 4906 ** as the cost of a linear scan through table t1, a simple greedy 5049 ** as the cost of a linear scan through table t1, a simple greedy 4907 ** algorithm may choose to use t2 for the outer loop, which is a much 5050 ** algorithm may choose to use t2 for the outer loop, which is a much 4908 ** costlier approach. 5051 ** costlier approach. 4909 */ 5052 */ 4910 nUnconstrained = 0; 5053 nUnconstrained = 0; 4911 notIndexed = 0; 5054 notIndexed = 0; 4912 for(isOptimal=(iFrom<nTabList-1); isOptimal>=0 && bestJ<0; isOptimal--){ 5055 for(isOptimal=(iFrom<nTabList-1); isOptimal>=0 && bestJ<0; isOptimal--){ 4913 Bitmask mask; /* Mask of tables not yet ready */ < 4914 for(j=iFrom, pTabItem=&pTabList->a[j]; j<nTabList; j++, pTabItem++){ | 5056 for(j=iFrom, sWBI.pSrc=&pTabList->a[j]; j<nTabList; j++, sWBI.pSrc++){ 4915 int doNotReorder; /* True if this table should not be reordered */ 5057 int doNotReorder; /* True if this table should not be reordered */ 4916 WhereCost sCost; /* Cost information from best[Virtual]Index() */ < 4917 ExprList *pOB; /* ORDER BY clause for index to optimize */ < 4918 ExprList *pDist; /* DISTINCT clause for index to optimize */ < 4919 5058 4920 doNotReorder = (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0; | 5059 doNotReorder = (sWBI.pSrc->jointype & (JT_LEFT|JT_CROSS))!=0; 4921 if( j!=iFrom && doNotReorder ) break; 5060 if( j!=iFrom && doNotReorder ) break; 4922 m = getMask(pMaskSet, pTabItem->iCursor); | 5061 m = getMask(pMaskSet, sWBI.pSrc->iCursor); 4923 if( (m & notReady)==0 ){ < > 5062 if( (m & sWBI.notValid)==0 ){ 4924 if( j==iFrom ) iFrom++; 5063 if( j==iFrom ) iFrom++; 4925 continue; 5064 continue; 4926 } 5065 } 4927 mask = (isOptimal ? m : notReady); | 5066 sWBI.notReady = (isOptimal ? m : sWBI.notValid); 4928 pOB = (i==0) ? pOrderBy : 0; < 4929 pDist = (i==0 ? pDistinct : 0); < 4930 if( pTabItem->pIndex==0 ) nUnconstrained++; | 5067 if( sWBI.pSrc->pIndex==0 ) nUnconstrained++; 4931 5068 4932 WHERETRACE(("=== trying table %d with isOptimal=%d ===\n", | 5069 WHERETRACE(("=== trying table %d (%s) with isOptimal=%d ===\n", 4933 j, isOptimal)); | 5070 j, sWBI.pSrc->pTab->zName, isOptimal)); 4934 assert( pTabItem->pTab ); < > 5071 assert( sWBI.pSrc->pTab ); 4935 #ifndef SQLITE_OMIT_VIRTUALTABLE 5072 #ifndef SQLITE_OMIT_VIRTUALTABLE 4936 if( IsVirtual(pTabItem->pTab) ){ | 5073 if( IsVirtual(sWBI.pSrc->pTab) ){ 4937 sqlite3_index_info **pp = &pWInfo->a[j].pIdxInfo; | 5074 sWBI.ppIdxInfo = &pWInfo->a[j].pIdxInfo; 4938 bestVirtualIndex(pParse, pWC, pTabItem, mask, notReady, pOB, | 5075 bestVirtualIndex(&sWBI); 4939 &sCost, pp); < 4940 }else 5076 }else 4941 #endif 5077 #endif 4942 { 5078 { 4943 bestBtreeIndex(pParse, pWC, pTabItem, mask, notReady, pOB, | 5079 bestBtreeIndex(&sWBI); 4944 pDist, &sCost); < 4945 } 5080 } 4946 assert( isOptimal || (sCost.used¬Ready)==0 ); | 5081 assert( isOptimal || (sWBI.cost.used&sWBI.notValid)==0 ); 4947 5082 4948 /* If an INDEXED BY clause is present, then the plan must use that 5083 /* If an INDEXED BY clause is present, then the plan must use that 4949 ** index if it uses any index at all */ 5084 ** index if it uses any index at all */ 4950 assert( pTabItem->pIndex==0 | 5085 assert( sWBI.pSrc->pIndex==0 4951 || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 | 5086 || (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 4952 || sCost.plan.u.pIdx==pTabItem->pIndex ); | 5087 || sWBI.cost.plan.u.pIdx==sWBI.pSrc->pIndex ); 4953 5088 4954 if( isOptimal && (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ){ | 5089 if( isOptimal && (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 ){ 4955 notIndexed |= m; 5090 notIndexed |= m; 4956 } 5091 } 4957 5092 4958 /* Conditions under which this table becomes the best so far: 5093 /* Conditions under which this table becomes the best so far: 4959 ** 5094 ** 4960 ** (1) The table must not depend on other tables that have not 5095 ** (1) The table must not depend on other tables that have not 4961 ** yet run. | 5096 ** yet run. (In other words, it must not depend on tables > 5097 ** in inner loops.) 4962 ** 5098 ** 4963 ** (2) A full-table-scan plan cannot supercede indexed plan unless 5099 ** (2) A full-table-scan plan cannot supercede indexed plan unless 4964 ** the full-table-scan is an "optimal" plan as defined above. 5100 ** the full-table-scan is an "optimal" plan as defined above. 4965 ** 5101 ** 4966 ** (3) All tables have an INDEXED BY clause or this table lacks an 5102 ** (3) All tables have an INDEXED BY clause or this table lacks an 4967 ** INDEXED BY clause or this table uses the specific 5103 ** INDEXED BY clause or this table uses the specific 4968 ** index specified by its INDEXED BY clause. This rule ensures 5104 ** index specified by its INDEXED BY clause. This rule ensures 4969 ** that a best-so-far is always selected even if an impossible 5105 ** that a best-so-far is always selected even if an impossible 4970 ** combination of INDEXED BY clauses are given. The error 5106 ** combination of INDEXED BY clauses are given. The error 4971 ** will be detected and relayed back to the application later. 5107 ** will be detected and relayed back to the application later. 4972 ** The NEVER() comes about because rule (2) above prevents 5108 ** The NEVER() comes about because rule (2) above prevents 4973 ** An indexable full-table-scan from reaching rule (3). 5109 ** An indexable full-table-scan from reaching rule (3). 4974 ** 5110 ** 4975 ** (4) The plan cost must be lower than prior plans or else the | 5111 ** (4) The plan cost must be lower than prior plans, where "cost" 4976 ** cost must be the same and the number of rows must be lower. | 5112 ** is defined by the compareCost() function above. 4977 */ 5113 */ 4978 if( (sCost.used¬Ready)==0 /* (1) */ | 5114 if( (sWBI.cost.used&sWBI.notValid)==0 /* (1) */ 4979 && (bestJ<0 || (notIndexed&m)!=0 /* (2) */ | 5115 && (bestJ<0 || (notIndexed&m)!=0 /* (2) */ 4980 || (bestPlan.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 5116 || (bestPlan.plan.wsFlags & WHERE_NOT_FULLSCAN)==0 4981 || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0) | 5117 || (sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0) 4982 && (nUnconstrained==0 || pTabItem->pIndex==0 /* (3) */ | 5118 && (nUnconstrained==0 || sWBI.pSrc->pIndex==0 /* (3) */ 4983 || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) | 5119 || NEVER((sWBI.cost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) 4984 && (bestJ<0 || sCost.rCost<bestPlan.rCost /* (4) */ | 5120 && (bestJ<0 || compareCost(&sWBI.cost, &bestPlan)) /* (4) */ 4985 || (sCost.rCost<=bestPlan.rCost < 4986 && sCost.plan.nRow<bestPlan.plan.nRow)) < 4987 ){ 5121 ){ 4988 WHERETRACE(("=== table %d is best so far" | 5122 WHERETRACE(("=== table %d (%s) is best so far\n" 4989 " with cost=%g and nRow=%g\n", < > 5123 " cost=%.1f, nRow=%.1f, nOBSat=%d, wsFlags=%08x\n", > 5124 j, sWBI.pSrc->pTab->zName, 4990 j, sCost.rCost, sCost.plan.nRow)); | 5125 sWBI.cost.rCost, sWBI.cost.plan.nRow, > 5126 sWBI.cost.plan.nOBSat, sWBI.cost.plan.wsFlags)); 4991 bestPlan = sCost; | 5127 bestPlan = sWBI.cost; 4992 bestJ = j; 5128 bestJ = j; 4993 } 5129 } 4994 if( doNotReorder ) break; 5130 if( doNotReorder ) break; 4995 } 5131 } 4996 } 5132 } 4997 assert( bestJ>=0 ); 5133 assert( bestJ>=0 ); 4998 assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); | 5134 assert( sWBI.notValid & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); 4999 WHERETRACE(("*** Optimizer selects table %d for loop %d" | 5135 WHERETRACE(("*** Optimizer selects table %d (%s) for loop %d with:\n" 5000 " with cost=%g and nRow=%g\n", | 5136 " cost=%.1f, nRow=%.1f, nOBSat=%d, wsFlags=0x%08x\n", > 5137 bestJ, pTabList->a[bestJ].pTab->zName, 5001 bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow)); | 5138 pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow, 5002 if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ | 5139 bestPlan.plan.nOBSat, bestPlan.plan.wsFlags)); 5003 pWInfo->nOBSat = pOrderBy->nExpr; < 5004 } < 5005 if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){ 5140 if( (bestPlan.plan.wsFlags & WHERE_DISTINCT)!=0 ){ 5006 assert( pWInfo->eDistinct==0 ); 5141 assert( pWInfo->eDistinct==0 ); 5007 pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; 5142 pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; 5008 } 5143 } 5009 andFlags &= bestPlan.plan.wsFlags; 5144 andFlags &= bestPlan.plan.wsFlags; 5010 pLevel->plan = bestPlan.plan; 5145 pLevel->plan = bestPlan.plan; > 5146 pLevel->iTabCur = pTabList->a[bestJ].iCursor; 5011 testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); 5147 testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); 5012 testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); 5148 testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); 5013 if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){ 5149 if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){ 5014 if( (wctrlFlags & WHERE_ONETABLE_ONLY) 5150 if( (wctrlFlags & WHERE_ONETABLE_ONLY) 5015 && (bestPlan.plan.wsFlags & WHERE_TEMP_INDEX)==0 5151 && (bestPlan.plan.wsFlags & WHERE_TEMP_INDEX)==0 5016 ){ 5152 ){ 5017 pLevel->iIdxCur = iIdxCur; 5153 pLevel->iIdxCur = iIdxCur; 5018 }else{ 5154 }else{ 5019 pLevel->iIdxCur = pParse->nTab++; 5155 pLevel->iIdxCur = pParse->nTab++; 5020 } 5156 } 5021 }else{ 5157 }else{ 5022 pLevel->iIdxCur = -1; 5158 pLevel->iIdxCur = -1; 5023 } 5159 } 5024 notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor); | 5160 sWBI.notValid &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor); 5025 pLevel->iFrom = (u8)bestJ; 5161 pLevel->iFrom = (u8)bestJ; 5026 if( bestPlan.plan.nRow>=(double)1 ){ 5162 if( bestPlan.plan.nRow>=(double)1 ){ 5027 pParse->nQueryLoop *= bestPlan.plan.nRow; 5163 pParse->nQueryLoop *= bestPlan.plan.nRow; 5028 } 5164 } 5029 5165 5030 /* Check that if the table scanned by this loop iteration had an 5166 /* Check that if the table scanned by this loop iteration had an 5031 ** INDEXED BY clause attached to it, that the named index is being 5167 ** INDEXED BY clause attached to it, that the named index is being ................................................................................................................................................................................ 5045 } 5181 } 5046 } 5182 } 5047 } 5183 } 5048 WHERETRACE(("*** Optimizer Finished ***\n")); 5184 WHERETRACE(("*** Optimizer Finished ***\n")); 5049 if( pParse->nErr || db->mallocFailed ){ 5185 if( pParse->nErr || db->mallocFailed ){ 5050 goto whereBeginError; 5186 goto whereBeginError; 5051 } 5187 } > 5188 if( nTabList ){ > 5189 pLevel--; > 5190 pWInfo->nOBSat = pLevel->plan.nOBSat; > 5191 }else{ > 5192 pWInfo->nOBSat = 0; > 5193 } 5052 5194 5053 /* If the total query only selects a single row, then the ORDER BY 5195 /* If the total query only selects a single row, then the ORDER BY 5054 ** clause is irrelevant. 5196 ** clause is irrelevant. 5055 */ 5197 */ 5056 if( (andFlags & WHERE_UNIQUE)!=0 && pOrderBy ){ 5198 if( (andFlags & WHERE_UNIQUE)!=0 && pOrderBy ){ > 5199 assert( nTabList==0 || (pLevel->plan.wsFlags & WHERE_ALL_UNIQUE)!=0 ); 5057 pWInfo->nOBSat = pOrderBy->nExpr; 5200 pWInfo->nOBSat = pOrderBy->nExpr; 5058 } 5201 } 5059 5202 5060 /* If the caller is an UPDATE or DELETE statement that is requesting 5203 /* If the caller is an UPDATE or DELETE statement that is requesting 5061 ** to use a one-pass algorithm, determine if this is appropriate. 5204 ** to use a one-pass algorithm, determine if this is appropriate. 5062 ** The one-pass algorithm only works if the WHERE clause constraints 5205 ** The one-pass algorithm only works if the WHERE clause constraints 5063 ** the statement to update a single row. 5206 ** the statement to update a single row. ................................................................................................................................................................................ 5070 5213 5071 /* Open all tables in the pTabList and any indices selected for 5214 /* Open all tables in the pTabList and any indices selected for 5072 ** searching those tables. 5215 ** searching those tables. 5073 */ 5216 */ 5074 sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ 5217 sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ 5075 notReady = ~(Bitmask)0; 5218 notReady = ~(Bitmask)0; 5076 pWInfo->nRowOut = (double)1; 5219 pWInfo->nRowOut = (double)1; 5077 for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){ | 5220 for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){ 5078 Table *pTab; /* Table to open */ 5221 Table *pTab; /* Table to open */ 5079 int iDb; /* Index of database containing table/index */ 5222 int iDb; /* Index of database containing table/index */ > 5223 struct SrcList_item *pTabItem; 5080 5224 5081 pTabItem = &pTabList->a[pLevel->iFrom]; 5225 pTabItem = &pTabList->a[pLevel->iFrom]; 5082 pTab = pTabItem->pTab; 5226 pTab = pTabItem->pTab; 5083 pLevel->iTabCur = pTabItem->iCursor; < 5084 pWInfo->nRowOut *= pLevel->plan.nRow; 5227 pWInfo->nRowOut *= pLevel->plan.nRow; 5085 iDb = sqlite3SchemaToIndex(db, pTab->pSchema); 5228 iDb = sqlite3SchemaToIndex(db, pTab->pSchema); 5086 if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){ 5229 if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){ 5087 /* Do nothing */ 5230 /* Do nothing */ 5088 }else 5231 }else 5089 #ifndef SQLITE_OMIT_VIRTUALTABLE 5232 #ifndef SQLITE_OMIT_VIRTUALTABLE 5090 if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ 5233 if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ ................................................................................................................................................................................ 5108 assert( n<=pTab->nCol ); 5251 assert( n<=pTab->nCol ); 5109 } 5252 } 5110 }else{ 5253 }else{ 5111 sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); 5254 sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); 5112 } 5255 } 5113 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX 5256 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX 5114 if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){ 5257 if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){ 5115 constructAutomaticIndex(pParse, pWC, pTabItem, notReady, pLevel); | 5258 constructAutomaticIndex(pParse, sWBI.pWC, pTabItem, notReady, pLevel); 5116 }else 5259 }else 5117 #endif 5260 #endif 5118 if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){ 5261 if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){ 5119 Index *pIx = pLevel->plan.u.pIdx; 5262 Index *pIx = pLevel->plan.u.pIdx; 5120 KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx); 5263 KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx); 5121 int iIndexCur = pLevel->iIdxCur; 5264 int iIndexCur = pLevel->iIdxCur; 5122 assert( pIx->pSchema==pTab->pSchema ); 5265 assert( pIx->pSchema==pTab->pSchema ); 5123 assert( iIndexCur>=0 ); 5266 assert( iIndexCur>=0 ); 5124 sqlite3VdbeAddOp4(v, OP_OpenRead, iIndexCur, pIx->tnum, iDb, 5267 sqlite3VdbeAddOp4(v, OP_OpenRead, iIndexCur, pIx->tnum, iDb, 5125 (char*)pKey, P4_KEYINFO_HANDOFF); 5268 (char*)pKey, P4_KEYINFO_HANDOFF); 5126 VdbeComment((v, "%s", pIx->zName)); 5269 VdbeComment((v, "%s", pIx->zName)); 5127 } 5270 } 5128 sqlite3CodeVerifySchema(pParse, iDb); 5271 sqlite3CodeVerifySchema(pParse, iDb); 5129 notReady &= ~getMask(pWC->pMaskSet, pTabItem->iCursor); | 5272 notReady &= ~getMask(sWBI.pWC->pMaskSet, pTabItem->iCursor); 5130 } 5273 } 5131 pWInfo->iTop = sqlite3VdbeCurrentAddr(v); 5274 pWInfo->iTop = sqlite3VdbeCurrentAddr(v); 5132 if( db->mallocFailed ) goto whereBeginError; 5275 if( db->mallocFailed ) goto whereBeginError; 5133 5276 5134 /* Generate the code to do the search. Each iteration of the for 5277 /* Generate the code to do the search. Each iteration of the for 5135 ** loop below generates code for a single nested loop of the VM 5278 ** loop below generates code for a single nested loop of the VM 5136 ** program. 5279 ** program. 5137 */ 5280 */ 5138 notReady = ~(Bitmask)0; 5281 notReady = ~(Bitmask)0; 5139 for(i=0; i<nTabList; i++){ | 5282 for(ii=0; ii<nTabList; ii++){ 5140 pLevel = &pWInfo->a[i]; | 5283 pLevel = &pWInfo->a[ii]; 5141 explainOneScan(pParse, pTabList, pLevel, i, pLevel->iFrom, wctrlFlags); | 5284 explainOneScan(pParse, pTabList, pLevel, ii, pLevel->iFrom, wctrlFlags); 5142 notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady); | 5285 notReady = codeOneLoopStart(pWInfo, ii, wctrlFlags, notReady); 5143 pWInfo->iContinue = pLevel->addrCont; 5286 pWInfo->iContinue = pLevel->addrCont; 5144 } 5287 } 5145 5288 5146 #ifdef SQLITE_TEST /* For testing and debugging use only */ 5289 #ifdef SQLITE_TEST /* For testing and debugging use only */ 5147 /* Record in the query plan information about the current table 5290 /* Record in the query plan information about the current table 5148 ** and the index used to access it (if any). If the table itself 5291 ** and the index used to access it (if any). If the table itself 5149 ** is not used, its name is just '{}'. If no index is used 5292 ** is not used, its name is just '{}'. If no index is used 5150 ** the index is listed as "{}". If the primary key is used the 5293 ** the index is listed as "{}". If the primary key is used the 5151 ** index name is '*'. 5294 ** index name is '*'. 5152 */ 5295 */ 5153 for(i=0; i<nTabList; i++){ | 5296 for(ii=0; ii<nTabList; ii++){ 5154 char *z; 5297 char *z; 5155 int n; 5298 int n; 5156 int w; 5299 int w; > 5300 struct SrcList_item *pTabItem; > 5301 5157 pLevel = &pWInfo->a[i]; | 5302 pLevel = &pWInfo->a[ii]; 5158 w = pLevel->plan.wsFlags; 5303 w = pLevel->plan.wsFlags; 5159 pTabItem = &pTabList->a[pLevel->iFrom]; 5304 pTabItem = &pTabList->a[pLevel->iFrom]; 5160 z = pTabItem->zAlias; 5305 z = pTabItem->zAlias; 5161 if( z==0 ) z = pTabItem->pTab->zName; 5306 if( z==0 ) z = pTabItem->pTab->zName; 5162 n = sqlite3Strlen30(z); 5307 n = sqlite3Strlen30(z); 5163 if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){ 5308 if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){ 5164 if( (w & WHERE_IDX_ONLY)!=0 && (w & WHERE_COVER_SCAN)==0 ){ 5309 if( (w & WHERE_IDX_ONLY)!=0 && (w & WHERE_COVER_SCAN)==0 ){
Changes to test/bigfile.test
12 # focus of this script testing the ability of SQLite to handle database 12 # focus of this script testing the ability of SQLite to handle database 13 # files larger than 4GB. 13 # files larger than 4GB. 14 # 14 # 15 # $Id: bigfile.test,v 1.12 2009/03/05 04:27:08 shane Exp $ 15 # $Id: bigfile.test,v 1.12 2009/03/05 04:27:08 shane Exp $ 16 # 16 # 17 17 18 if {[file exists skip-big-file]} return 18 if {[file exists skip-big-file]} return > 19 if {$tcl_platform(os)=="Darwin"} return 19 20 20 set testdir [file dirname $argv0] 21 set testdir [file dirname $argv0] 21 source $testdir/tester.tcl 22 source $testdir/tester.tcl 22 23 23 # Do not use a codec for this file, as the database is manipulated using 24 # Do not use a codec for this file, as the database is manipulated using 24 # external methods (the [fake_big_file] and [hexio_write] commands). 25 # external methods (the [fake_big_file] and [hexio_write] commands). 25 # 26 #
Changes to test/bigfile2.test
10 #*********************************************************************** 10 #*********************************************************************** 11 # This file implements regression tests for SQLite library. The 11 # This file implements regression tests for SQLite library. The 12 # focus of this script testing the ability of SQLite to handle database 12 # focus of this script testing the ability of SQLite to handle database 13 # files larger than 4GB. 13 # files larger than 4GB. 14 # 14 # 15 15 16 if {[file exists skip-big-file]} return 16 if {[file exists skip-big-file]} return > 17 if {$tcl_platform(os)=="Darwin"} return 17 18 18 set testdir [file dirname $argv0] 19 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 source $testdir/tester.tcl 20 set testprefix bigfile2 21 set testprefix bigfile2 21 22 22 # Create a small database. 23 # Create a small database. 23 # 24 #
Changes to test/collate5.test
217 # These tests - collate5-3.* - focus on compound SELECT queries that 217 # These tests - collate5-3.* - focus on compound SELECT queries that 218 # feature ORDER BY clauses. 218 # feature ORDER BY clauses. 219 # 219 # 220 do_test collate5-3.0 { 220 do_test collate5-3.0 { 221 execsql { 221 execsql { 222 SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1; 222 SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1; 223 } 223 } 224 } {a A a A b B b B n N} | 224 } {/[aA] [aA] [aA] [aA] [bB] [bB] [bB] [bB] [nN] [nN]/} 225 do_test collate5-3.1 { 225 do_test collate5-3.1 { 226 execsql { 226 execsql { 227 SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1; 227 SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1; 228 } 228 } 229 } {A A B B N a a b b n} 229 } {A A B B N a a b b n} 230 do_test collate5-3.2 { 230 do_test collate5-3.2 { 231 execsql { 231 execsql { ................................................................................................................................................................................ 278 SELECT a, count(*) FROM collate5t1 GROUP BY a; 278 SELECT a, count(*) FROM collate5t1 GROUP BY a; 279 }] 279 }] 280 } {a 2 b 2} 280 } {a 2 b 2} 281 do_test collate5-4.2 { 281 do_test collate5-4.2 { 282 execsql { 282 execsql { 283 SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b; 283 SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b; 284 } 284 } 285 } {A 1.0 2 b 2 1 B 3 1} | 285 } {/[aA] 1(.0)? 2 [bB] 2 1 [bB] 3 1/} 286 do_test collate5-4.3 { 286 do_test collate5-4.3 { 287 execsql { 287 execsql { 288 DROP TABLE collate5t1; 288 DROP TABLE collate5t1; 289 } 289 } 290 } {} 290 } {} 291 291 292 finish_test 292 finish_test
Changes to test/e_select.test
1019 # These tests also show that the following is not untrue: 1019 # These tests also show that the following is not untrue: 1020 # 1020 # 1021 # EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do 1021 # EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do 1022 # not have to be expressions that appear in the result. 1022 # not have to be expressions that appear in the result. 1023 # 1023 # 1024 do_select_tests e_select-4.9 { 1024 do_select_tests e_select-4.9 { 1025 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { 1025 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { 1026 4,5 f 1 o 7,6 s 3,2 t | 1026 /#,# f 1 o #,# s #,# t/ 1027 } 1027 } 1028 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { 1028 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { 1029 1,2,3,4 10 5,6,7 18 1029 1,2,3,4 10 5,6,7 18 1030 } 1030 } 1031 3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { 1031 3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { 1032 4 1,5 2,6 3,7 1032 4 1,5 2,6 3,7 1033 } 1033 } ................................................................................................................................................................................ 1036 } 1036 } 1037 } 1037 } 1038 1038 1039 # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL 1039 # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL 1040 # values are considered equal. 1040 # values are considered equal. 1041 # 1041 # 1042 do_select_tests e_select-4.10 { 1042 do_select_tests e_select-4.10 { 1043 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1 3 2,4} | 1043 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,# 3 #,#/} 1044 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1} 1044 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1} 1045 } 1045 } 1046 1046 1047 # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation 1047 # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation 1048 # sequence with which to compare text values apply when evaluating 1048 # sequence with which to compare text values apply when evaluating 1049 # expressions in a GROUP BY clause. 1049 # expressions in a GROUP BY clause. 1050 # 1050 # ................................................................................................................................................................................ 1741 1 2 3 1 2 -20 1 4 93 1 5 -1 1741 1 2 3 1 2 -20 1 4 93 1 5 -1 1742 } 1742 } 1743 7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" { 1743 7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" { 1744 2 4 93 2 5 -1 1 2 -20 1 2 3 1744 2 4 93 2 5 -1 1 2 -20 1 2 3 1745 1 2 7 1 2 8 1 4 93 1 5 -1 1745 1 2 7 1 2 8 1 4 93 1 5 -1 1746 } 1746 } 1747 8 "SELECT z, x FROM d1 ORDER BY 2" { 1747 8 "SELECT z, x FROM d1 ORDER BY 2" { 1748 3 1 8 1 7 1 -20 1 | 1748 /# 1 # 1 # 1 # 1 1749 93 1 -1 1 -1 2 93 2 | 1749 # 1 # 1 # 2 # 2/ 1750 } 1750 } 1751 9 "SELECT z, x FROM d1 ORDER BY 1" { 1751 9 "SELECT z, x FROM d1 ORDER BY 1" { 1752 -20 1 -1 2 -1 1 3 1 | 1752 /-20 1 -1 # -1 # 3 1 1753 7 1 8 1 93 2 93 1 | 1753 7 1 8 1 93 # 93 #/ 1754 } 1754 } 1755 } 1755 } 1756 1756 1757 # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier 1757 # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier 1758 # that corresponds to the alias of one of the output columns, then the 1758 # that corresponds to the alias of one of the output columns, then the 1759 # expression is considered an alias for that column. 1759 # expression is considered an alias for that column. 1760 # 1760 # ................................................................................................................................................................................ 1762 1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" { 1762 1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" { 1763 -19 0 0 4 8 9 94 94 1763 -19 0 0 4 8 9 94 94 1764 } 1764 } 1765 2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" { 1765 2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" { 1766 94 94 9 8 4 0 0 -19 1766 94 94 9 8 4 0 0 -19 1767 } 1767 } 1768 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { 1768 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { 1769 3 1 8 1 7 1 -20 1 93 1 -1 1 -1 2 93 2 | 1769 /# 1 # 1 # 1 # 1 # 1 # 1 # 2 # 2/ 1770 } 1770 } 1771 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { 1771 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { 1772 -20 1 -1 2 -1 1 3 1 7 1 8 1 93 2 93 1 | 1772 /-20 1 -1 # -1 # 3 1 7 1 8 1 93 # 93 #/ 1773 } 1773 } 1774 } 1774 } 1775 1775 1776 # EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is 1776 # EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is 1777 # any other expression, it is evaluated and the returned value used to 1777 # any other expression, it is evaluated and the returned value used to 1778 # order the output rows. 1778 # order the output rows. 1779 # 1779 #
Changes to test/fuzzer1.test
1860 INSERT INTO x5_rules VALUES(0, 'a', '0.1.2.3.4.5.6.7.8.9.a', 1); 1860 INSERT INTO x5_rules VALUES(0, 'a', '0.1.2.3.4.5.6.7.8.9.a', 1); 1861 DROP TABLE x5; 1861 DROP TABLE x5; 1862 CREATE VIRTUAL TABLE x5 USING fuzzer(x5_rules); 1862 CREATE VIRTUAL TABLE x5 USING fuzzer(x5_rules); 1863 SELECT length(word) FROM x5 WHERE word MATCH 'a' LIMIT 50; 1863 SELECT length(word) FROM x5 WHERE word MATCH 'a' LIMIT 50; 1864 } {1 21 41 61 81} 1864 } {1 21 41 61 81} 1865 1865 1866 finish_test 1866 finish_test 1867 < 1868 <
Changes to test/lock.test
243 # 243 # 244 do_test lock-2.8 { 244 do_test lock-2.8 { 245 db2 timeout 400 245 db2 timeout 400 246 execsql BEGIN 246 execsql BEGIN 247 execsql {UPDATE t1 SET a = 0 WHERE 0} 247 execsql {UPDATE t1 SET a = 0 WHERE 0} 248 catchsql {BEGIN EXCLUSIVE;} db2 248 catchsql {BEGIN EXCLUSIVE;} db2 249 } {1 {database is locked}} 249 } {1 {database is locked}} > 250 do_test lock-2.8b { > 251 db2 eval {PRAGMA busy_timeout} > 252 } {400} 250 do_test lock-2.9 { 253 do_test lock-2.9 { 251 db2 timeout 0 254 db2 timeout 0 252 execsql COMMIT 255 execsql COMMIT 253 } {} 256 } {} > 257 do_test lock-2.9b { > 258 db2 eval {PRAGMA busy_timeout} > 259 } {0} 254 integrity_check lock-2.10 260 integrity_check lock-2.10 > 261 do_test lock-2.11 { > 262 db2 eval {PRAGMA busy_timeout(400)} > 263 execsql BEGIN > 264 execsql {UPDATE t1 SET a = 0 WHERE 0} > 265 catchsql {BEGIN EXCLUSIVE;} db2 > 266 } {1 {database is locked}} > 267 do_test lock-2.11b { > 268 db2 eval {PRAGMA busy_timeout} > 269 } {400} > 270 do_test lock-2.12 { > 271 db2 eval {PRAGMA busy_timeout(0)} > 272 execsql COMMIT > 273 } {} > 274 do_test lock-2.12b { > 275 db2 eval {PRAGMA busy_timeout} > 276 } {0} > 277 integrity_check lock-2.13 255 278 256 # Try to start two transactions in a row 279 # Try to start two transactions in a row 257 # 280 # 258 do_test lock-3.1 { 281 do_test lock-3.1 { 259 execsql {BEGIN TRANSACTION} 282 execsql {BEGIN TRANSACTION} 260 set r [catch {execsql {BEGIN TRANSACTION}} msg] 283 set r [catch {execsql {BEGIN TRANSACTION}} msg] 261 execsql {ROLLBACK} 284 execsql {ROLLBACK}
Added test/orderby1.test
> 1 # 2012 Sept 27 > 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 file is testing that the optimizations that disable > 13 # ORDER BY clauses when the natural order of a query is correct. > 14 # > 15 > 16 > 17 set testdir [file dirname $argv0] > 18 source $testdir/tester.tcl > 19 set ::testprefix orderby1 > 20 > 21 # Generate test data for a join. Verify that the join gets the > 22 # correct answer. > 23 # > 24 do_test 1.0 { > 25 db eval { > 26 BEGIN; > 27 CREATE TABLE album( > 28 aid INTEGER PRIMARY KEY, > 29 title TEXT UNIQUE NOT NULL > 30 ); > 31 CREATE TABLE track( > 32 tid INTEGER PRIMARY KEY, > 33 aid INTEGER NOT NULL REFERENCES album, > 34 tn INTEGER NOT NULL, > 35 name TEXT, > 36 UNIQUE(aid, tn) > 37 ); > 38 INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three'); > 39 INSERT INTO track VALUES > 40 (NULL, 1, 1, 'one-a'), > 41 (NULL, 2, 2, 'two-b'), > 42 (NULL, 3, 3, 'three-c'), > 43 (NULL, 1, 3, 'one-c'), > 44 (NULL, 2, 1, 'two-a'), > 45 (NULL, 3, 1, 'three-a'); > 46 COMMIT; > 47 } > 48 } {} > 49 do_test 1.1a { > 50 db eval { > 51 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 52 } > 53 } {one-a one-c two-a two-b three-a three-c} > 54 > 55 # Verify that the ORDER BY clause is optimized out > 56 # > 57 do_test 1.1b { > 58 db eval { > 59 EXPLAIN QUERY PLAN > 60 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 61 } > 62 } {~/ORDER BY/} ;# ORDER BY optimized out > 63 > 64 # The same query with ORDER BY clause optimization disabled via + operators > 65 # should give exactly the same answer. > 66 # > 67 do_test 1.2a { > 68 db eval { > 69 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn > 70 } > 71 } {one-a one-c two-a two-b three-a three-c} > 72 > 73 # The output is sorted manually in this case. > 74 # > 75 do_test 1.2b { > 76 db eval { > 77 EXPLAIN QUERY PLAN > 78 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn > 79 } > 80 } {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms > 81 > 82 # The same query with ORDER BY optimizations turned off via built-in test. > 83 # > 84 do_test 1.3a { > 85 optimization_control db order-by-idx-join 0 > 86 db cache flush > 87 db eval { > 88 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 89 } > 90 } {one-a one-c two-a two-b three-a three-c} > 91 do_test 1.3b { > 92 db eval { > 93 EXPLAIN QUERY PLAN > 94 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 95 } > 96 } {/ORDER BY/} ;# separate sorting pass due to disabled optimization > 97 optimization_control db all 1 > 98 db cache flush > 99 > 100 # Reverse order sorts > 101 # > 102 do_test 1.4a { > 103 db eval { > 104 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn > 105 } > 106 } {three-a three-c two-a two-b one-a one-c} > 107 do_test 1.4b { > 108 db eval { > 109 SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn > 110 } > 111 } {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting > 112 do_test 1.4c { > 113 db eval { > 114 EXPLAIN QUERY PLAN > 115 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn > 116 } > 117 } {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC > 118 > 119 > 120 do_test 1.5a { > 121 db eval { > 122 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC > 123 } > 124 } {one-c one-a two-b two-a three-c three-a} > 125 do_test 1.5b { > 126 db eval { > 127 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC > 128 } > 129 } {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting > 130 do_test 1.5c { > 131 db eval { > 132 EXPLAIN QUERY PLAN > 133 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC > 134 } > 135 } {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC > 136 > 137 do_test 1.6a { > 138 db eval { > 139 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC > 140 } > 141 } {three-c three-a two-b two-a one-c one-a} > 142 do_test 1.6b { > 143 db eval { > 144 SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC > 145 } > 146 } {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting > 147 do_test 1.6c { > 148 db eval { > 149 EXPLAIN QUERY PLAN > 150 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC > 151 } > 152 } {~/ORDER BY/} ;# ORDER BY optimized-out > 153 > 154 > 155 # Reconstruct the test data to use indices rather than integer primary keys. > 156 # > 157 do_test 2.0 { > 158 db eval { > 159 BEGIN; > 160 DROP TABLE album; > 161 DROP TABLE track; > 162 CREATE TABLE album( > 163 aid INT PRIMARY KEY, > 164 title TEXT NOT NULL > 165 ); > 166 CREATE INDEX album_i1 ON album(title, aid); > 167 CREATE TABLE track( > 168 aid INTEGER NOT NULL REFERENCES album, > 169 tn INTEGER NOT NULL, > 170 name TEXT, > 171 UNIQUE(aid, tn) > 172 ); > 173 INSERT INTO album VALUES(1, '1-one'), (20, '2-two'), (3, '3-three'); > 174 INSERT INTO track VALUES > 175 (1, 1, 'one-a'), > 176 (20, 2, 'two-b'), > 177 (3, 3, 'three-c'), > 178 (1, 3, 'one-c'), > 179 (20, 1, 'two-a'), > 180 (3, 1, 'three-a'); > 181 COMMIT; > 182 } > 183 } {} > 184 do_test 2.1a { > 185 db eval { > 186 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 187 } > 188 } {one-a one-c two-a two-b three-a three-c} > 189 > 190 # Verify that the ORDER BY clause is optimized out > 191 # > 192 do_test 2.1b { > 193 db eval { > 194 EXPLAIN QUERY PLAN > 195 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 196 } > 197 } {~/ORDER BY/} ;# ORDER BY optimized out > 198 > 199 do_test 2.1c { > 200 db eval { > 201 SELECT name FROM album JOIN track USING (aid) ORDER BY title, aid, tn > 202 } > 203 } {one-a one-c two-a two-b three-a three-c} > 204 do_test 2.1d { > 205 db eval { > 206 EXPLAIN QUERY PLAN > 207 SELECT name FROM album JOIN track USING (aid) ORDER BY title, aid, tn > 208 } > 209 } {~/ORDER BY/} ;# ORDER BY optimized out > 210 > 211 # The same query with ORDER BY clause optimization disabled via + operators > 212 # should give exactly the same answer. > 213 # > 214 do_test 2.2a { > 215 db eval { > 216 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn > 217 } > 218 } {one-a one-c two-a two-b three-a three-c} > 219 > 220 # The output is sorted manually in this case. > 221 # > 222 do_test 2.2b { > 223 db eval { > 224 EXPLAIN QUERY PLAN > 225 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn > 226 } > 227 } {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms > 228 > 229 # The same query with ORDER BY optimizations turned off via built-in test. > 230 # > 231 do_test 2.3a { > 232 optimization_control db order-by-idx-join 0 > 233 db cache flush > 234 db eval { > 235 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 236 } > 237 } {one-a one-c two-a two-b three-a three-c} > 238 do_test 2.3b { > 239 db eval { > 240 EXPLAIN QUERY PLAN > 241 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 242 } > 243 } {/ORDER BY/} ;# separate sorting pass due to disabled optimization > 244 optimization_control db all 1 > 245 db cache flush > 246 > 247 # Reverse order sorts > 248 # > 249 do_test 2.4a { > 250 db eval { > 251 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn > 252 } > 253 } {three-a three-c two-a two-b one-a one-c} > 254 do_test 2.4b { > 255 db eval { > 256 SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn > 257 } > 258 } {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting > 259 do_test 2.4c { > 260 db eval { > 261 EXPLAIN QUERY PLAN > 262 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn > 263 } > 264 } {/ORDER BY/} ;# separate sorting pass due to mixed DESC/ASC > 265 > 266 > 267 do_test 2.5a { > 268 db eval { > 269 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC > 270 } > 271 } {one-c one-a two-b two-a three-c three-a} > 272 do_test 2.5b { > 273 db eval { > 274 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC > 275 } > 276 } {one-c one-a two-b two-a three-c three-a} ;# verify same order after sorting > 277 do_test 2.5c { > 278 db eval { > 279 EXPLAIN QUERY PLAN > 280 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC > 281 } > 282 } {/ORDER BY/} ;# separate sorting pass due to mixed ASC/DESC > 283 > 284 do_test 2.6a { > 285 db eval { > 286 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC > 287 } > 288 } {three-c three-a two-b two-a one-c one-a} > 289 do_test 2.6b { > 290 db eval { > 291 SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC > 292 } > 293 } {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting > 294 do_test 2.6c { > 295 db eval { > 296 EXPLAIN QUERY PLAN > 297 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC > 298 } > 299 } {~/ORDER BY/} ;# ORDER BY optimized out > 300 > 301 > 302 # Generate another test dataset, but this time using mixed ASC/DESC indices. > 303 # > 304 do_test 3.0 { > 305 db eval { > 306 BEGIN; > 307 DROP TABLE album; > 308 DROP TABLE track; > 309 CREATE TABLE album( > 310 aid INTEGER PRIMARY KEY, > 311 title TEXT UNIQUE NOT NULL > 312 ); > 313 CREATE TABLE track( > 314 tid INTEGER PRIMARY KEY, > 315 aid INTEGER NOT NULL REFERENCES album, > 316 tn INTEGER NOT NULL, > 317 name TEXT, > 318 UNIQUE(aid ASC, tn DESC) > 319 ); > 320 INSERT INTO album VALUES(1, '1-one'), (2, '2-two'), (3, '3-three'); > 321 INSERT INTO track VALUES > 322 (NULL, 1, 1, 'one-a'), > 323 (NULL, 2, 2, 'two-b'), > 324 (NULL, 3, 3, 'three-c'), > 325 (NULL, 1, 3, 'one-c'), > 326 (NULL, 2, 1, 'two-a'), > 327 (NULL, 3, 1, 'three-a'); > 328 COMMIT; > 329 } > 330 } {} > 331 do_test 3.1a { > 332 db eval { > 333 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC > 334 } > 335 } {one-c one-a two-b two-a three-c three-a} > 336 > 337 # Verify that the ORDER BY clause is optimized out > 338 # > 339 do_test 3.1b { > 340 db eval { > 341 EXPLAIN QUERY PLAN > 342 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC > 343 } > 344 } {~/ORDER BY/} ;# ORDER BY optimized out > 345 > 346 # The same query with ORDER BY clause optimization disabled via + operators > 347 # should give exactly the same answer. > 348 # > 349 do_test 3.2a { > 350 db eval { > 351 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC > 352 } > 353 } {one-c one-a two-b two-a three-c three-a} > 354 > 355 # The output is sorted manually in this case. > 356 # > 357 do_test 3.2b { > 358 db eval { > 359 EXPLAIN QUERY PLAN > 360 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC > 361 } > 362 } {/ORDER BY/} ;# separate sorting pass due to "+" on ORDER BY terms > 363 > 364 # The same query with ORDER BY optimizations turned off via built-in test. > 365 # > 366 do_test 3.3a { > 367 optimization_control db order-by-idx-join 0 > 368 db cache flush > 369 db eval { > 370 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC > 371 } > 372 } {one-c one-a two-b two-a three-c three-a} > 373 do_test 3.3b { > 374 db eval { > 375 EXPLAIN QUERY PLAN > 376 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC > 377 } > 378 } {/ORDER BY/} ;# separate sorting pass due to disabled optimization > 379 optimization_control db all 1 > 380 db cache flush > 381 > 382 # Without the mixed ASC/DESC on ORDER BY > 383 # > 384 do_test 3.4a { > 385 db eval { > 386 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 387 } > 388 } {one-a one-c two-a two-b three-a three-c} > 389 do_test 3.4b { > 390 db eval { > 391 SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn > 392 } > 393 } {one-a one-c two-a two-b three-a three-c} ;# verify same order after sorting > 394 do_test 3.4c { > 395 db eval { > 396 EXPLAIN QUERY PLAN > 397 SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn > 398 } > 399 } {/ORDER BY/} ;# separate sorting pass due to mismatched DESC/ASC > 400 > 401 > 402 do_test 3.5a { > 403 db eval { > 404 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC > 405 } > 406 } {three-c three-a two-b two-a one-c one-a} > 407 do_test 3.5b { > 408 db eval { > 409 SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC > 410 } > 411 } {three-c three-a two-b two-a one-c one-a} ;# verify same order after sorting > 412 do_test 3.5c { > 413 db eval { > 414 EXPLAIN QUERY PLAN > 415 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC > 416 } > 417 } {/ORDER BY/} ;# separate sorting pass due to mismatched ASC/DESC > 418 > 419 > 420 do_test 3.6a { > 421 db eval { > 422 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn > 423 } > 424 } {three-a three-c two-a two-b one-a one-c} > 425 do_test 3.6b { > 426 db eval { > 427 SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn > 428 } > 429 } {three-a three-c two-a two-b one-a one-c} ;# verify same order after sorting > 430 do_test 3.6c { > 431 db eval { > 432 EXPLAIN QUERY PLAN > 433 SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn > 434 } > 435 } {~/ORDER BY/} ;# inverted ASC/DESC is optimized out > 436 > 437 > 438 finish_test
Added test/orderby2.test
> 1 # 2012 Sept 27 > 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 file is testing that the optimizations that disable > 13 # ORDER BY clauses when the natural order of a query is correct. > 14 # > 15 > 16 > 17 set testdir [file dirname $argv0] > 18 source $testdir/tester.tcl > 19 set ::testprefix orderby2 > 20 > 21 # Generate test data for a join. Verify that the join gets the > 22 # correct answer. > 23 # > 24 do_test 1.0 { > 25 db eval { > 26 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); > 27 INSERT INTO t1 VALUES(1,11), (2,22); > 28 CREATE TABLE t2(d, e, UNIQUE(d,e)); > 29 INSERT INTO t2 VALUES(10, 'ten'), (11,'eleven'), (12,'twelve'), > 30 (11, 'oneteen'); > 31 } > 32 } {} > 33 > 34 do_test 1.1a { > 35 db eval { > 36 SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY d, e; > 37 } > 38 } {eleven oneteen} > 39 do_test 1.1b { > 40 db eval { > 41 EXPLAIN QUERY PLAN > 42 SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY d, e; > 43 } > 44 } {~/ORDER BY/} > 45 > 46 do_test 1.2a { > 47 db eval { > 48 SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY e; > 49 } > 50 } {eleven oneteen} > 51 do_test 1.2b { > 52 db eval { > 53 EXPLAIN QUERY PLAN > 54 SELECT e FROM t1, t2 WHERE a=1 AND d=b ORDER BY e; > 55 } > 56 } {~/ORDER BY/} > 57 > 58 do_test 1.3a { > 59 db eval { > 60 SELECT e, b FROM t1, t2 WHERE a=1 ORDER BY d, e; > 61 } > 62 } {ten 11 eleven 11 oneteen 11 twelve 11} > 63 do_test 1.3b { > 64 db eval { > 65 EXPLAIN QUERY PLAN > 66 SELECT e, b FROM t1, t2 WHERE a=1 ORDER BY d, e; > 67 } > 68 } {~/ORDER BY/} > 69 > 70 # The following tests derived from TH3 test module cov1/where34.test > 71 # > 72 do_test 2.0 { > 73 db eval { > 74 CREATE TABLE t31(a,b); CREATE INDEX t31ab ON t31(a,b); > 75 CREATE TABLE t32(c,d); CREATE INDEX t32cd ON t32(c,d); > 76 CREATE TABLE t33(e,f); CREATE INDEX t33ef ON t33(e,f); > 77 CREATE TABLE t34(g,h); CREATE INDEX t34gh ON t34(g,h); > 78 > 79 INSERT INTO t31 VALUES(1,4), (2,3), (1,3); > 80 INSERT INTO t32 VALUES(4,5), (3,6), (3,7), (4,8); > 81 INSERT INTO t33 VALUES(5,9), (7,10), (6,11), (8,12), (8,13), (7,14); > 82 INSERT INTO t34 VALUES(11,20), (10,21), (12,22), (9,23), (13,24), > 83 (14,25), (12,26); > 84 SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34 > 85 WHERE c=b AND e=d AND g=f > 86 ORDER BY a ASC, c ASC, e DESC, g ASC; > 87 } > 88 } {1,3,7,10 1,3,7,14 1,3,6,11 1,4,8,12 1,4,8,12 1,4,8,13 1,4,5,9 2,3,7,10 2,3,7, > 89 do_test 2.1 { > 90 db eval { > 91 SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34 > 92 WHERE c=b AND e=d AND g=f > 93 ORDER BY +a ASC, +c ASC, +e DESC, +g ASC; > 94 } > 95 } {1,3,7,10 1,3,7,14 1,3,6,11 1,4,8,12 1,4,8,12 1,4,8,13 1,4,5,9 2,3,7,10 2,3,7, > 96 do_test 2.2 { > 97 db eval { > 98 SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34 > 99 WHERE c=b AND e=d AND g=f > 100 ORDER BY a ASC, c ASC, e ASC, g ASC; > 101 } > 102 } {1,3,6,11 1,3,7,10 1,3,7,14 1,4,5,9 1,4,8,12 1,4,8,12 1,4,8,13 2,3,6,11 2,3,7, > 103 do_test 2.3 { > 104 optimization_control db cover-idx-scan off > 105 db cache flush > 106 db eval { > 107 SELECT a||','||c||','||e||','||g FROM t31, t32, t33, t34 > 108 WHERE c=b AND e=d AND g=f > 109 ORDER BY a ASC, c ASC, e ASC, g ASC; > 110 } > 111 } {1,3,6,11 1,3,7,10 1,3,7,14 1,4,5,9 1,4,8,12 1,4,8,12 1,4,8,13 2,3,6,11 2,3,7, > 112 optimization_control db all on > 113 db cache flush > 114 > 115 > 116 > 117 finish_test
Changes to test/tclsqlite.test
315 # modify and reset the NULL representation 315 # modify and reset the NULL representation 316 # 316 # 317 do_test tcl-8.1 { 317 do_test tcl-8.1 { 318 db nullvalue NaN 318 db nullvalue NaN 319 execsql {INSERT INTO t1 VALUES(30,NULL)} 319 execsql {INSERT INTO t1 VALUES(30,NULL)} 320 db eval {SELECT * FROM t1 WHERE b IS NULL} 320 db eval {SELECT * FROM t1 WHERE b IS NULL} 321 } {30 NaN} 321 } {30 NaN} > 322 proc concatFunc args {return [join $args {}]} 322 do_test tcl-8.2 { 323 do_test tcl-8.2 { > 324 db function concat concatFunc > 325 db eval {SELECT concat('a', b, 'z') FROM t1 WHERE b is NULL} > 326 } {aNaNz} > 327 do_test tcl-8.3 { 323 db nullvalue NULL 328 db nullvalue NULL 324 db nullvalue 329 db nullvalue 325 } {NULL} 330 } {NULL} 326 do_test tcl-8.3 { | 331 do_test tcl-8.4 { 327 db nullvalue {} 332 db nullvalue {} 328 db eval {SELECT * FROM t1 WHERE b IS NULL} 333 db eval {SELECT * FROM t1 WHERE b IS NULL} 329 } {30 {}} 334 } {30 {}} > 335 do_test tcl-8.5 { > 336 db function concat concatFunc > 337 db eval {SELECT concat('a', b, 'z') FROM t1 WHERE b is NULL} > 338 } {az} 330 339 331 # Test the return type of user-defined functions 340 # Test the return type of user-defined functions 332 # 341 # 333 do_test tcl-9.1 { 342 do_test tcl-9.1 { 334 db function ret_str {return "hi"} 343 db function ret_str {return "hi"} 335 execsql {SELECT typeof(ret_str())} 344 execsql {SELECT typeof(ret_str())} 336 } {text} 345 } {text}
Changes to test/tester.tcl
534 if {![info exists ::G(match)] || [string match $::G(match) $name]} { 534 if {![info exists ::G(match)] || [string match $::G(match) $name]} { 535 if {[catch {uplevel #0 "$cmd;\n"} result]} { 535 if {[catch {uplevel #0 "$cmd;\n"} result]} { 536 puts "\nError: $result" 536 puts "\nError: $result" 537 fail_test $name 537 fail_test $name 538 } else { 538 } else { 539 if {[regexp {^~?/.*/$} $expected]} { 539 if {[regexp {^~?/.*/$} $expected]} { 540 if {[string index $expected 0]=="~"} { 540 if {[string index $expected 0]=="~"} { 541 set re [string range $expected 2 end-1] | 541 set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]] 542 set ok [expr {![regexp $re $result]}] 542 set ok [expr {![regexp $re $result]}] 543 } else { 543 } else { 544 set re [string range $expected 1 end-1] | 544 set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]] 545 set ok [regexp $re $result] 545 set ok [regexp $re $result] 546 } 546 } 547 } else { 547 } else { 548 set ok [expr {[string compare $result $expected]==0}] 548 set ok [expr {[string compare $result $expected]==0}] 549 } 549 } 550 if {!$ok} { 550 if {!$ok} { 551 # if {![info exists ::testprefix] || $::testprefix eq ""} { 551 # if {![info exists ::testprefix] || $::testprefix eq ""} {
Changes to test/tkt-cbd054fa6b.test
46 do_test tkt-cbd05-1.3 { 46 do_test tkt-cbd05-1.3 { 47 execsql { 47 execsql { 48 SELECT tbl,idx,group_concat(sample,' ') 48 SELECT tbl,idx,group_concat(sample,' ') 49 FROM sqlite_stat3 49 FROM sqlite_stat3 50 WHERE idx = 't1_x' 50 WHERE idx = 't1_x' 51 GROUP BY tbl,idx 51 GROUP BY tbl,idx 52 } 52 } 53 } {t1 t1_x { A B C D E F G H I}} | 53 } {/t1 t1_x .[ ABCDEFGHI]{10}./} 54 54 55 do_test tkt-cbd05-2.1 { 55 do_test tkt-cbd05-2.1 { 56 db eval { 56 db eval { 57 DROP TABLE t1; 57 DROP TABLE t1; 58 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL); 58 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL); 59 CREATE INDEX t1_x ON t1(b); 59 CREATE INDEX t1_x ON t1(b); 60 INSERT INTO t1 VALUES(NULL, X''); 60 INSERT INTO t1 VALUES(NULL, X''); ................................................................................................................................................................................ 78 do_test tkt-cbd05-2.3 { 78 do_test tkt-cbd05-2.3 { 79 execsql { 79 execsql { 80 SELECT tbl,idx,group_concat(sample,' ') 80 SELECT tbl,idx,group_concat(sample,' ') 81 FROM sqlite_stat3 81 FROM sqlite_stat3 82 WHERE idx = 't1_x' 82 WHERE idx = 't1_x' 83 GROUP BY tbl,idx 83 GROUP BY tbl,idx 84 } 84 } 85 } {t1 t1_x { A B C D E F G H I}} | 85 } {/t1 t1_x .[ ABCDEFGHI]{10}./} 86 86 87 finish_test 87 finish_test
Changes to test/where.test
1084 CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE); 1084 CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE); 1085 INSERT INTO t8 VALUES(1,'one'); 1085 INSERT INTO t8 VALUES(1,'one'); 1086 INSERT INTO t8 VALUES(4,'four'); 1086 INSERT INTO t8 VALUES(4,'four'); 1087 } 1087 } 1088 cksort { 1088 cksort { 1089 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b 1089 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b 1090 } 1090 } 1091 } {1/4 1/1 4/4 4/1 sort} | 1091 } {1/4 1/1 4/4 4/1 nosort} 1092 do_test where-14.2 { 1092 do_test where-14.2 { 1093 cksort { 1093 cksort { 1094 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC 1094 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC 1095 } 1095 } 1096 } {1/1 1/4 4/1 4/4 sort} | 1096 } {1/1 1/4 4/1 4/4 nosort} 1097 do_test where-14.3 { 1097 do_test where-14.3 { 1098 cksort { 1098 cksort { 1099 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b 1099 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b 1100 } 1100 } 1101 } {1/4 1/1 4/4 4/1 nosort} 1101 } {1/4 1/1 4/4 4/1 nosort} 1102 do_test where-14.4 { 1102 do_test where-14.4 { 1103 cksort { 1103 cksort { ................................................................................................................................................................................ 1105 } 1105 } 1106 } {1/4 1/1 4/4 4/1 nosort} 1106 } {1/4 1/1 4/4 4/1 nosort} 1107 do_test where-14.5 { 1107 do_test where-14.5 { 1108 # This test case changed from "nosort" to "sort". See ticket 2a5629202f. 1108 # This test case changed from "nosort" to "sort". See ticket 2a5629202f. 1109 cksort { 1109 cksort { 1110 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b 1110 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b 1111 } 1111 } 1112 } {4/4 4/1 1/4 1/1 sort} | 1112 } {/4/[14] 4/[14] 1/[14] 1/[14] sort/} 1113 do_test where-14.6 { 1113 do_test where-14.6 { 1114 # This test case changed from "nosort" to "sort". See ticket 2a5629202f. 1114 # This test case changed from "nosort" to "sort". See ticket 2a5629202f. 1115 cksort { 1115 cksort { 1116 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC 1116 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC 1117 } 1117 } 1118 } {4/4 4/1 1/4 1/1 sort} | 1118 } {/4/[14] 4/[14] 1/[14] 1/[14] sort/} 1119 do_test where-14.7 { 1119 do_test where-14.7 { 1120 cksort { 1120 cksort { 1121 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b 1121 SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b 1122 } 1122 } 1123 } {4/1 4/4 1/1 1/4 sort} 1123 } {4/1 4/4 1/1 1/4 sort} 1124 do_test where-14.7.1 { 1124 do_test where-14.7.1 { 1125 cksort { 1125 cksort {