/ Check-in [854a54c6]
Login

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

Overview
Comment:Merge latest trunk with this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | defrag-opt
Files: files | file ages | folders
SHA1:854a54c6c21e800b0cd999023014813f7c50b23f
User & Date: dan 2014-10-22 18:42:31
Context
2014-10-24
16:40
Fix some minor formatting and code organization issues. check-in: eab8706d user: dan tags: defrag-opt
2014-10-22
18:42
Merge latest trunk with this branch. check-in: 854a54c6 user: dan tags: defrag-opt
15:27
Take steps to avoid misestimating range query costs based on STAT4 data due to the roundoff error of converting from integers to LogEst and back to integers. check-in: 3c933bf9 user: drh tags: trunk
2014-10-14
17:27
Fix some code duplication issues on this branch. Add minor optimizations to the new code. check-in: 58d7793b user: dan tags: defrag-opt
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/analyze.c.

  1595   1595           nRow = pFinal->anLt[iCol];
  1596   1596           nDist100 = (i64)100 * pFinal->anDLt[iCol];
  1597   1597           nSample--;
  1598   1598         }else{
  1599   1599           nRow = pIdx->aiRowEst[0];
  1600   1600           nDist100 = ((i64)100 * pIdx->aiRowEst[0]) / pIdx->aiRowEst[iCol+1];
  1601   1601         }
         1602  +      pIdx->nRowEst0 = nRow;
  1602   1603   
  1603   1604         /* Set nSum to the number of distinct (iCol+1) field prefixes that
  1604   1605         ** occur in the stat4 table for this index. Set sumEq to the sum of 
  1605   1606         ** the nEq values for column iCol for the same set (adding the value 
  1606   1607         ** only once where there exist duplicate prefixes).  */
  1607   1608         for(i=0; i<nSample; i++){
  1608   1609           if( i==(pIdx->nSample-1)

Changes to src/btree.c.

  2140   2140   #else
  2141   2141     return 1;
  2142   2142   #endif
  2143   2143   }
  2144   2144   
  2145   2145   /*
  2146   2146   ** Make sure pBt->pTmpSpace points to an allocation of 
  2147         -** MX_CELL_SIZE(pBt) bytes.
         2147  +** MX_CELL_SIZE(pBt) bytes with a 4-byte prefix for a left-child
         2148  +** pointer.
  2148   2149   */
  2149   2150   static void allocateTempSpace(BtShared *pBt){
  2150   2151     if( !pBt->pTmpSpace ){
  2151   2152       pBt->pTmpSpace = sqlite3PageMalloc( pBt->pageSize );
  2152   2153   
  2153   2154       /* One of the uses of pBt->pTmpSpace is to format cells before
  2154   2155       ** inserting them into a leaf page (function fillInCell()). If
................................................................................
  2155   2156       ** a cell is less than 4 bytes in size, it is rounded up to 4 bytes
  2156   2157       ** by the various routines that manipulate binary cells. Which
  2157   2158       ** can mean that fillInCell() only initializes the first 2 or 3
  2158   2159       ** bytes of pTmpSpace, but that the first 4 bytes are copied from
  2159   2160       ** it into a database page. This is not actually a problem, but it
  2160   2161       ** does cause a valgrind error when the 1 or 2 bytes of unitialized 
  2161   2162       ** data is passed to system call write(). So to avoid this error,
  2162         -    ** zero the first 4 bytes of temp space here.  */
  2163         -    if( pBt->pTmpSpace ) memset(pBt->pTmpSpace, 0, 4);
         2163  +    ** zero the first 4 bytes of temp space here.
         2164  +    **
         2165  +    ** Also:  Provide four bytes of initialized space before the
         2166  +    ** beginning of pTmpSpace as an area available to prepend the
         2167  +    ** left-child pointer to the beginning of a cell.
         2168  +    */
         2169  +    if( pBt->pTmpSpace ){
         2170  +      memset(pBt->pTmpSpace, 0, 8);
         2171  +      pBt->pTmpSpace += 4;
         2172  +    }
  2164   2173     }
  2165   2174   }
  2166   2175   
  2167   2176   /*
  2168   2177   ** Free the pBt->pTmpSpace allocation
  2169   2178   */
  2170   2179   static void freeTempSpace(BtShared *pBt){
         2180  +  if( pBt->pTmpSpace ){
         2181  +    pBt->pTmpSpace -= 4;
  2171   2182     sqlite3PageFree( pBt->pTmpSpace);
  2172   2183     pBt->pTmpSpace = 0;
  2173   2184   }
         2185  +}
  2174   2186   
  2175   2187   /*
  2176   2188   ** Close an open database and invalidate all cursors.
  2177   2189   */
  2178   2190   int sqlite3BtreeClose(Btree *p){
  2179   2191     BtShared *pBt = p->pBt;
  2180   2192     BtCursor *pCur;

Changes to src/btreeInt.h.

   432    432     Bitvec *pHasContent;  /* Set of pages moved to free-list this transaction */
   433    433   #ifndef SQLITE_OMIT_SHARED_CACHE
   434    434     int nRef;             /* Number of references to this structure */
   435    435     BtShared *pNext;      /* Next on a list of sharable BtShared structs */
   436    436     BtLock *pLock;        /* List of locks held on this shared-btree struct */
   437    437     Btree *pWriter;       /* Btree with currently open write transaction */
   438    438   #endif
   439         -  u8 *pTmpSpace;        /* BtShared.pageSize bytes of space for tmp use */
          439  +  u8 *pTmpSpace;        /* Temp space sufficient to hold a single cell */
   440    440   };
   441    441   
   442    442   /*
   443    443   ** Allowed values for BtShared.btsFlags
   444    444   */
   445    445   #define BTS_READ_ONLY        0x0001   /* Underlying file is readonly */
   446    446   #define BTS_PAGESIZE_FIXED   0x0002   /* Page size can no longer be changed */

Changes to src/os_win.c.

   939    939   #else
   940    940     { "WaitForSingleObject",     (SYSCALL)0,                       0 },
   941    941   #endif
   942    942   
   943    943   #define osWaitForSingleObject ((DWORD(WINAPI*)(HANDLE, \
   944    944           DWORD))aSyscall[63].pCurrent)
   945    945   
          946  +#if !SQLITE_OS_WINCE
   946    947     { "WaitForSingleObjectEx",   (SYSCALL)WaitForSingleObjectEx,   0 },
          948  +#else
          949  +  { "WaitForSingleObjectEx",   (SYSCALL)0,                       0 },
          950  +#endif
   947    951   
   948    952   #define osWaitForSingleObjectEx ((DWORD(WINAPI*)(HANDLE,DWORD, \
   949    953           BOOL))aSyscall[64].pCurrent)
   950    954   
   951    955   #if SQLITE_OS_WINRT
   952    956     { "SetFilePointerEx",        (SYSCALL)SetFilePointerEx,        0 },
   953    957   #else
................................................................................
  1282   1286     assert( sleepObj!=NULL );
  1283   1287     osWaitForSingleObjectEx(sleepObj, milliseconds, FALSE);
  1284   1288   #else
  1285   1289     osSleep(milliseconds);
  1286   1290   #endif
  1287   1291   }
  1288   1292   
  1289         -#if SQLITE_MAX_WORKER_THREADS>0 && !SQLITE_OS_WINRT && SQLITE_THREADSAFE>0
         1293  +#if SQLITE_MAX_WORKER_THREADS>0 && !SQLITE_OS_WINCE && !SQLITE_OS_WINRT && \
         1294  +        SQLITE_THREADSAFE>0
  1290   1295   DWORD sqlite3Win32Wait(HANDLE hObject){
  1291   1296     DWORD rc;
  1292   1297     while( (rc = osWaitForSingleObjectEx(hObject, INFINITE,
  1293   1298                                          TRUE))==WAIT_IO_COMPLETION ){}
  1294   1299     return rc;
  1295   1300   }
  1296   1301   #endif

Changes to src/pager.c.

  1937   1937         assert( pPager->journalMode==PAGER_JOURNALMODE_MEMORY );
  1938   1938         sqlite3OsClose(pPager->jfd);
  1939   1939       }else if( pPager->journalMode==PAGER_JOURNALMODE_TRUNCATE ){
  1940   1940         if( pPager->journalOff==0 ){
  1941   1941           rc = SQLITE_OK;
  1942   1942         }else{
  1943   1943           rc = sqlite3OsTruncate(pPager->jfd, 0);
         1944  +        if( rc==SQLITE_OK && pPager->fullSync ){
         1945  +          /* Make sure the new file size is written into the inode right away.
         1946  +          ** Otherwise the journal might resurrect following a power loss and
         1947  +          ** cause the last transaction to roll back.  See
         1948  +          ** https://bugzilla.mozilla.org/show_bug.cgi?id=1072773
         1949  +          */
         1950  +          rc = sqlite3OsSync(pPager->jfd, pPager->syncFlags);
         1951  +        }
  1944   1952         }
  1945   1953         pPager->journalOff = 0;
  1946   1954       }else if( pPager->journalMode==PAGER_JOURNALMODE_PERSIST
  1947   1955         || (pPager->exclusiveMode && pPager->journalMode!=PAGER_JOURNALMODE_WAL)
  1948   1956       ){
  1949   1957         rc = zeroJournalHdr(pPager, hasMaster);
  1950   1958         pPager->journalOff = 0;

Changes to src/shell.c.

   878    878   #endif
   879    879         if( p->cnt++==0 && p->showHeader ){
   880    880           for(i=0; i<nArg; i++){
   881    881             output_csv(p, azCol[i] ? azCol[i] : "", i<nArg-1);
   882    882           }
   883    883           fprintf(p->out,"%s",p->newline);
   884    884         }
   885         -      if( azArg>0 ){
          885  +      if( nArg>0 ){
   886    886           for(i=0; i<nArg; i++){
   887    887             output_csv(p, azArg[i], i<nArg-1);
   888    888           }
   889    889           fprintf(p->out,"%s",p->newline);
   890    890         }
   891    891   #if defined(WIN32) || defined(_WIN32)
   892    892         fflush(p->out);

Changes to src/sqliteInt.h.

  1797   1797     unsigned isResized:1;    /* True if resizeIndexObject() has been called */
  1798   1798     unsigned isCovering:1;   /* True if this is a covering index */
  1799   1799   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  1800   1800     int nSample;             /* Number of elements in aSample[] */
  1801   1801     int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  1802   1802     tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
  1803   1803     IndexSample *aSample;    /* Samples of the left-most key */
  1804         -  tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this table */
         1804  +  tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this index */
         1805  +  tRowcnt nRowEst0;        /* Non-logarithmic number of rows in the index */
  1805   1806   #endif
  1806   1807   };
  1807   1808   
  1808   1809   /*
  1809   1810   ** Allowed values for Index.idxType
  1810   1811   */
  1811   1812   #define SQLITE_IDXTYPE_APPDEF      0   /* Created using CREATE INDEX */

Changes to src/threads.c.

    94     94   }
    95     95   
    96     96   #endif /* SQLITE_OS_UNIX && defined(SQLITE_MUTEX_PTHREADS) */
    97     97   /******************************** End Unix Pthreads *************************/
    98     98   
    99     99   
   100    100   /********************************* Win32 Threads ****************************/
   101         -#if SQLITE_OS_WIN && !SQLITE_OS_WINRT && SQLITE_THREADSAFE>0
          101  +#if SQLITE_OS_WIN && !SQLITE_OS_WINCE && !SQLITE_OS_WINRT && SQLITE_THREADSAFE>0
   102    102   
   103    103   #define SQLITE_THREADS_IMPLEMENTED 1  /* Prevent the single-thread code below */
   104    104   #include <process.h>
   105    105   
   106    106   /* A running thread */
   107    107   struct SQLiteThread {
   108    108     void *tid;               /* The thread handle */
................................................................................
   187    187       assert( bRc );
   188    188     }
   189    189     if( rc==WAIT_OBJECT_0 ) *ppOut = p->pResult;
   190    190     sqlite3_free(p);
   191    191     return (rc==WAIT_OBJECT_0) ? SQLITE_OK : SQLITE_ERROR;
   192    192   }
   193    193   
   194         -#endif /* SQLITE_OS_WIN && !SQLITE_OS_WINRT */
          194  +#endif /* SQLITE_OS_WIN && !SQLITE_OS_WINCE && !SQLITE_OS_WINRT */
   195    195   /******************************** End Win32 Threads *************************/
   196    196   
   197    197   
   198    198   /********************************* Single-Threaded **************************/
   199    199   #ifndef SQLITE_THREADS_IMPLEMENTED
   200    200   /*
   201    201   ** This implementation does not actually create a new thread.  It does the

Changes to src/vdbe.c.

  2413   2413         pC->nHdrParsed = i;
  2414   2414         pC->iHdrOffset = (u32)(zHdr - zData);
  2415   2415         if( pC->aRow==0 ){
  2416   2416           sqlite3VdbeMemRelease(&sMem);
  2417   2417           sMem.flags = MEM_Null;
  2418   2418         }
  2419   2419     
  2420         -      /* If we have read more header data than was contained in the header,
  2421         -      ** or if the end of the last field appears to be past the end of the
  2422         -      ** record, or if the end of the last field appears to be before the end
  2423         -      ** of the record (when all fields present), then we must be dealing 
  2424         -      ** with a corrupt database.
         2420  +      /* The record is corrupt if any of the following are true:
         2421  +      ** (1) the bytes of the header extend past the declared header size
         2422  +      **          (zHdr>zEndHdr)
         2423  +      ** (2) the entire header was used but not all data was used
         2424  +      **          (zHdr==zEndHdr && offset!=pC->payloadSize)
         2425  +      ** (3) the end of the data extends beyond the end of the record.
         2426  +      **          (offset > pC->payloadSize)
  2425   2427         */
  2426         -      if( (zHdr > zEndHdr)
         2428  +      if( (zHdr>=zEndHdr && (zHdr>zEndHdr || offset!=pC->payloadSize))
  2427   2429          || (offset > pC->payloadSize)
  2428         -       || (zHdr==zEndHdr && offset!=pC->payloadSize)
  2429   2430         ){
  2430   2431           rc = SQLITE_CORRUPT_BKPT;
  2431   2432           goto op_column_error;
  2432   2433         }
  2433   2434       }
  2434   2435   
  2435   2436       /* If after trying to extra new entries from the header, nHdrParsed is
................................................................................
  2612   2613   
  2613   2614     /* Loop through the elements that will make up the record to figure
  2614   2615     ** out how much space is required for the new record.
  2615   2616     */
  2616   2617     pRec = pLast;
  2617   2618     do{
  2618   2619       assert( memIsValid(pRec) );
  2619         -    serial_type = sqlite3VdbeSerialType(pRec, file_format);
         2620  +    pRec->uTemp = serial_type = sqlite3VdbeSerialType(pRec, file_format);
  2620   2621       len = sqlite3VdbeSerialTypeLen(serial_type);
  2621   2622       if( pRec->flags & MEM_Zero ){
  2622   2623         if( nData ){
  2623   2624           sqlite3VdbeMemExpandBlob(pRec);
  2624   2625         }else{
  2625   2626           nZero += pRec->u.nZero;
  2626   2627           len -= pRec->u.nZero;
................................................................................
  2661   2662   
  2662   2663     /* Write the record */
  2663   2664     i = putVarint32(zNewRecord, nHdr);
  2664   2665     j = nHdr;
  2665   2666     assert( pData0<=pLast );
  2666   2667     pRec = pData0;
  2667   2668     do{
  2668         -    serial_type = sqlite3VdbeSerialType(pRec, file_format);
         2669  +    serial_type = pRec->uTemp;
  2669   2670       i += putVarint32(&zNewRecord[i], serial_type);            /* serial type */
  2670   2671       j += sqlite3VdbeSerialPut(&zNewRecord[j], pRec, serial_type); /* content */
  2671   2672     }while( (++pRec)<=pLast );
  2672   2673     assert( i==nHdr );
  2673   2674     assert( j==nByte );
  2674   2675   
  2675   2676     assert( pOp->p3>0 && pOp->p3<=(p->nMem-p->nCursor) );

Changes to src/vdbeInt.h.

   171    171     u16 flags;          /* Some combination of MEM_Null, MEM_Str, MEM_Dyn, etc. */
   172    172     u8  enc;            /* SQLITE_UTF8, SQLITE_UTF16BE, SQLITE_UTF16LE */
   173    173     int n;              /* Number of characters in string value, excluding '\0' */
   174    174     char *z;            /* String or BLOB value */
   175    175     /* ShallowCopy only needs to copy the information above */
   176    176     char *zMalloc;      /* Space to hold MEM_Str or MEM_Blob if szMalloc>0 */
   177    177     int szMalloc;       /* Size of the zMalloc allocation */
   178         -  int iPadding1;      /* Padding for 8-byte alignment */
          178  +  u32 uTemp;          /* Transient storage for serial_type in OP_MakeRecord */
   179    179     sqlite3 *db;        /* The associated database connection */
   180    180     void (*xDel)(void*);/* Destructor for Mem.z - only valid if MEM_Dyn */
   181    181   #ifdef SQLITE_DEBUG
   182    182     Mem *pScopyFrom;    /* This Mem is a shallow copy of pScopyFrom */
   183    183     void *pFiller;      /* So that sizeof(Mem) is a multiple of 8 */
   184    184   #endif
   185    185   };

Changes to src/vdbemem.c.

   139    139         pMem->szMalloc = 0;
   140    140         return SQLITE_NOMEM;
   141    141       }else{
   142    142         pMem->szMalloc = sqlite3DbMallocSize(pMem->db, pMem->zMalloc);
   143    143       }
   144    144     }
   145    145   
   146         -  if( pMem->z && bPreserve && pMem->z!=pMem->zMalloc ){
          146  +  if( bPreserve && pMem->z && pMem->z!=pMem->zMalloc ){
   147    147       memcpy(pMem->zMalloc, pMem->z, pMem->n);
   148    148     }
   149    149     if( (pMem->flags&MEM_Dyn)!=0 ){
   150    150       assert( pMem->xDel!=0 && pMem->xDel!=SQLITE_DYNAMIC );
   151    151       pMem->xDel((void *)(pMem->z));
   152    152     }
   153    153   

Changes to src/vdbesort.c.

  2288   2288   
  2289   2289     rc = vdbeSorterMergeTreeBuild(pSorter, &pMain);
  2290   2290     if( rc==SQLITE_OK ){
  2291   2291   #if SQLITE_MAX_WORKER_THREADS
  2292   2292       assert( pSorter->bUseThreads==0 || pSorter->nTask>1 );
  2293   2293       if( pSorter->bUseThreads ){
  2294   2294         int iTask;
  2295         -      PmaReader *pReadr;
         2295  +      PmaReader *pReadr = 0;
  2296   2296         SortSubtask *pLast = &pSorter->aTask[pSorter->nTask-1];
  2297   2297         rc = vdbeSortAllocUnpacked(pLast);
  2298   2298         if( rc==SQLITE_OK ){
  2299   2299           pReadr = (PmaReader*)sqlite3DbMallocZero(db, sizeof(PmaReader));
  2300   2300           pSorter->pReader = pReadr;
  2301   2301           if( pReadr==0 ) rc = SQLITE_NOMEM;
  2302   2302         }

Changes to src/vtab.c.

   515    515         *pzErr = sqlite3MPrintf(db, "%s", zErr);
   516    516         sqlite3_free(zErr);
   517    517       }
   518    518       sqlite3DbFree(db, pVTable);
   519    519     }else if( ALWAYS(pVTable->pVtab) ){
   520    520       /* Justification of ALWAYS():  A correct vtab constructor must allocate
   521    521       ** the sqlite3_vtab object if successful.  */
          522  +    memset(pVTable->pVtab, 0, sizeof(pVTable->pVtab[0]));
   522    523       pVTable->pVtab->pModule = pMod->pModule;
   523    524       pVTable->nRef = 1;
   524    525       if( sCtx.pTab ){
   525    526         const char *zFormat = "vtable constructor did not declare schema: %s";
   526    527         *pzErr = sqlite3MPrintf(db, zFormat, pTab->zName);
   527    528         sqlite3VtabUnlock(pVTable);
   528    529         rc = SQLITE_ERROR;

Changes to src/where.c.

  2194   2194           aff = SQLITE_AFF_INTEGER;
  2195   2195         }else{
  2196   2196           aff = p->pTable->aCol[p->aiColumn[nEq]].affinity;
  2197   2197         }
  2198   2198         /* Determine iLower and iUpper using ($P) only. */
  2199   2199         if( nEq==0 ){
  2200   2200           iLower = 0;
  2201         -        iUpper = sqlite3LogEstToInt(p->aiRowLogEst[0]);
         2201  +        iUpper = p->nRowEst0;
  2202   2202         }else{
  2203   2203           /* Note: this call could be optimized away - since the same values must 
  2204   2204           ** have been requested when testing key $P in whereEqualScanEst().  */
  2205   2205           whereKeyStats(pParse, p, pRec, 0, a);
  2206   2206           iLower = a[0];
  2207   2207           iUpper = a[0] + a[1];
  2208   2208         }
................................................................................
  2634   2634     int nReg;                     /* Number of registers to allocate */
  2635   2635     char *zAff;                   /* Affinity string to return */
  2636   2636   
  2637   2637     /* This module is only called on query plans that use an index. */
  2638   2638     pLoop = pLevel->pWLoop;
  2639   2639     assert( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 );
  2640   2640     nEq = pLoop->u.btree.nEq;
  2641         -  nSkip = pLoop->u.btree.nSkip;
         2641  +  nSkip = pLoop->nSkip;
  2642   2642     pIdx = pLoop->u.btree.pIndex;
  2643   2643     assert( pIdx!=0 );
  2644   2644   
  2645   2645     /* Figure out how many memory cells we will need then allocate them.
  2646   2646     */
  2647   2647     regBase = pParse->nMem + 1;
  2648   2648     nReg = pLoop->u.btree.nEq + nExtraReg;
................................................................................
  2748   2748   ** string similar to:
  2749   2749   **
  2750   2750   **   "a=? AND b>?"
  2751   2751   */
  2752   2752   static void explainIndexRange(StrAccum *pStr, WhereLoop *pLoop, Table *pTab){
  2753   2753     Index *pIndex = pLoop->u.btree.pIndex;
  2754   2754     u16 nEq = pLoop->u.btree.nEq;
  2755         -  u16 nSkip = pLoop->u.btree.nSkip;
         2755  +  u16 nSkip = pLoop->nSkip;
  2756   2756     int i, j;
  2757   2757     Column *aCol = pTab->aCol;
  2758   2758     i16 *aiColumn = pIndex->aiColumn;
  2759   2759   
  2760   2760     if( nEq==0 && (pLoop->wsFlags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ) return;
  2761   2761     sqlite3StrAccumAppend(pStr, " (", 2);
  2762   2762     for(i=0; i<nEq; i++){
................................................................................
  3185   3185       char *zStartAff;             /* Affinity for start of range constraint */
  3186   3186       char cEndAff = 0;            /* Affinity for end of range constraint */
  3187   3187       u8 bSeekPastNull = 0;        /* True to seek past initial nulls */
  3188   3188       u8 bStopAtNull = 0;          /* Add condition to terminate at NULLs */
  3189   3189   
  3190   3190       pIdx = pLoop->u.btree.pIndex;
  3191   3191       iIdxCur = pLevel->iIdxCur;
  3192         -    assert( nEq>=pLoop->u.btree.nSkip );
         3192  +    assert( nEq>=pLoop->nSkip );
  3193   3193   
  3194   3194       /* If this loop satisfies a sort order (pOrderBy) request that 
  3195   3195       ** was passed to this function to implement a "SELECT min(x) ..." 
  3196   3196       ** query, then the caller will only allow the loop to run for
  3197   3197       ** a single iteration. This means that the first row returned
  3198   3198       ** should not have a NULL value stored in 'x'. If column 'x' is
  3199   3199       ** the first one after the nEq equality constraints in the index,
................................................................................
  3202   3202       assert( pWInfo->pOrderBy==0
  3203   3203            || pWInfo->pOrderBy->nExpr==1
  3204   3204            || (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 );
  3205   3205       if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0
  3206   3206        && pWInfo->nOBSat>0
  3207   3207        && (pIdx->nKeyCol>nEq)
  3208   3208       ){
  3209         -      assert( pLoop->u.btree.nSkip==0 );
         3209  +      assert( pLoop->nSkip==0 );
  3210   3210         bSeekPastNull = 1;
  3211   3211         nExtraReg = 1;
  3212   3212       }
  3213   3213   
  3214   3214       /* Find any inequality constraint terms for the start and end 
  3215   3215       ** of the range. 
  3216   3216       */
................................................................................
  3823   3823       }else{
  3824   3824         z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask);
  3825   3825       }
  3826   3826       sqlite3DebugPrintf(" %-19s", z);
  3827   3827       sqlite3_free(z);
  3828   3828     }
  3829   3829     if( p->wsFlags & WHERE_SKIPSCAN ){
  3830         -    sqlite3DebugPrintf(" f %05x %d-%d", p->wsFlags, p->nLTerm,p->u.btree.nSkip);
         3830  +    sqlite3DebugPrintf(" f %05x %d-%d", p->wsFlags, p->nLTerm,p->nSkip);
  3831   3831     }else{
  3832   3832       sqlite3DebugPrintf(" f %05x N %d", p->wsFlags, p->nLTerm);
  3833   3833     }
  3834   3834     sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut);
  3835   3835     if( p->nLTerm && (sqlite3WhereTrace & 0x100)!=0 ){
  3836   3836       int i;
  3837   3837       for(i=0; i<p->nLTerm; i++){
................................................................................
  3952   3952   ** relationship is inverted and needs to be adjusted.
  3953   3953   */
  3954   3954   static int whereLoopCheaperProperSubset(
  3955   3955     const WhereLoop *pX,       /* First WhereLoop to compare */
  3956   3956     const WhereLoop *pY        /* Compare against this WhereLoop */
  3957   3957   ){
  3958   3958     int i, j;
  3959         -  if( pX->nLTerm >= pY->nLTerm ) return 0; /* X is not a subset of Y */
         3959  +  if( pX->nLTerm-pX->nSkip >= pY->nLTerm-pY->nSkip ){
         3960  +    return 0; /* X is not a subset of Y */
         3961  +  }
  3960   3962     if( pX->rRun >= pY->rRun ){
  3961   3963       if( pX->rRun > pY->rRun ) return 0;    /* X costs more than Y */
  3962   3964       if( pX->nOut > pY->nOut ) return 0;    /* X costs more than Y */
  3963   3965     }
  3964   3966     for(i=pX->nLTerm-1; i>=0; i--){
         3967  +    if( pX->aLTerm[i]==0 ) continue;
  3965   3968       for(j=pY->nLTerm-1; j>=0; j--){
  3966   3969         if( pY->aLTerm[j]==pX->aLTerm[i] ) break;
  3967   3970       }
  3968   3971       if( j<0 ) return 0;  /* X not a subset of Y since term X[i] not used by Y */
  3969   3972     }
  3970   3973     return 1;  /* All conditions meet */
  3971   3974   }
................................................................................
  3979   3982   **
  3980   3983   **   (2) pTemplate costs more than any other WhereLoops for which pTemplate
  3981   3984   **       is a proper subset.
  3982   3985   **
  3983   3986   ** To say "WhereLoop X is a proper subset of Y" means that X uses fewer
  3984   3987   ** WHERE clause terms than Y and that every WHERE clause term used by X is
  3985   3988   ** also used by Y.
  3986         -**
  3987         -** This adjustment is omitted for SKIPSCAN loops.  In a SKIPSCAN loop, the
  3988         -** WhereLoop.nLTerm field is not an accurate measure of the number of WHERE
  3989         -** clause terms covered, since some of the first nLTerm entries in aLTerm[]
  3990         -** will be NULL (because they are skipped).  That makes it more difficult
  3991         -** to compare the loops.  We could add extra code to do the comparison, and
  3992         -** perhaps we will someday.  But SKIPSCAN is sufficiently uncommon, and this
  3993         -** adjustment is sufficient minor, that it is very difficult to construct
  3994         -** a test case where the extra code would improve the query plan.  Better
  3995         -** to avoid the added complexity and just omit cost adjustments to SKIPSCAN
  3996         -** loops.
  3997   3989   */
  3998   3990   static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
  3999   3991     if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
  4000         -  if( (pTemplate->wsFlags & WHERE_SKIPSCAN)!=0 ) return;
  4001   3992     for(; p; p=p->pNextLoop){
  4002   3993       if( p->iTab!=pTemplate->iTab ) continue;
  4003   3994       if( (p->wsFlags & WHERE_INDEXED)==0 ) continue;
  4004         -    if( (p->wsFlags & WHERE_SKIPSCAN)!=0 ) continue;
  4005   3995       if( whereLoopCheaperProperSubset(p, pTemplate) ){
  4006   3996         /* Adjust pTemplate cost downward so that it is cheaper than its 
  4007         -      ** subset p */
         3997  +      ** subset p.  Except, do not adjust the cost estimate downward for
         3998  +      ** a loop that skips more columns. */
         3999  +      if( pTemplate->nSkip>p->nSkip ) continue;
         4000  +      WHERETRACE(0x80,("subset cost adjustment %d,%d to %d,%d\n",
         4001  +                       pTemplate->rRun, pTemplate->nOut, p->rRun, p->nOut-1));
  4008   4002         pTemplate->rRun = p->rRun;
  4009   4003         pTemplate->nOut = p->nOut - 1;
  4010   4004       }else if( whereLoopCheaperProperSubset(pTemplate, p) ){
  4011   4005         /* Adjust pTemplate cost upward so that it is costlier than p since
  4012   4006         ** pTemplate is a proper subset of p */
         4007  +      WHERETRACE(0x80,("subset cost adjustment %d,%d to %d,%d\n",
         4008  +                       pTemplate->rRun, pTemplate->nOut, p->rRun, p->nOut+1));
  4013   4009         pTemplate->rRun = p->rRun;
  4014   4010         pTemplate->nOut = p->nOut + 1;
  4015   4011       }
  4016   4012     }
  4017   4013   }
  4018   4014   
  4019   4015   /*
................................................................................
  4291   4287     WhereLoop *pNew;                /* Template WhereLoop under construction */
  4292   4288     WhereTerm *pTerm;               /* A WhereTerm under consideration */
  4293   4289     int opMask;                     /* Valid operators for constraints */
  4294   4290     WhereScan scan;                 /* Iterator for WHERE terms */
  4295   4291     Bitmask saved_prereq;           /* Original value of pNew->prereq */
  4296   4292     u16 saved_nLTerm;               /* Original value of pNew->nLTerm */
  4297   4293     u16 saved_nEq;                  /* Original value of pNew->u.btree.nEq */
  4298         -  u16 saved_nSkip;                /* Original value of pNew->u.btree.nSkip */
         4294  +  u16 saved_nSkip;                /* Original value of pNew->nSkip */
  4299   4295     u32 saved_wsFlags;              /* Original value of pNew->wsFlags */
  4300   4296     LogEst saved_nOut;              /* Original value of pNew->nOut */
  4301   4297     int iCol;                       /* Index of the column in the table */
  4302   4298     int rc = SQLITE_OK;             /* Return code */
  4303   4299     LogEst rSize;                   /* Number of rows in the table */
  4304   4300     LogEst rLogSize;                /* Logarithm of table size */
  4305   4301     WhereTerm *pTop = 0, *pBtm = 0; /* Top and bottom range constraints */
................................................................................
  4320   4316   
  4321   4317     assert( pNew->u.btree.nEq<pProbe->nColumn );
  4322   4318     iCol = pProbe->aiColumn[pNew->u.btree.nEq];
  4323   4319   
  4324   4320     pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
  4325   4321                           opMask, pProbe);
  4326   4322     saved_nEq = pNew->u.btree.nEq;
  4327         -  saved_nSkip = pNew->u.btree.nSkip;
         4323  +  saved_nSkip = pNew->nSkip;
  4328   4324     saved_nLTerm = pNew->nLTerm;
  4329   4325     saved_wsFlags = pNew->wsFlags;
  4330   4326     saved_prereq = pNew->prereq;
  4331   4327     saved_nOut = pNew->nOut;
  4332   4328     pNew->rSetup = 0;
  4333   4329     rSize = pProbe->aiRowLogEst[0];
  4334   4330     rLogSize = estLog(rSize);
  4335         -
  4336         -  /* Consider using a skip-scan if there are no WHERE clause constraints
  4337         -  ** available for the left-most terms of the index, and if the average
  4338         -  ** number of repeats in the left-most terms is at least 18. 
  4339         -  **
  4340         -  ** The magic number 18 is selected on the basis that scanning 17 rows
  4341         -  ** is almost always quicker than an index seek (even though if the index
  4342         -  ** contains fewer than 2^17 rows we assume otherwise in other parts of
  4343         -  ** the code). And, even if it is not, it should not be too much slower. 
  4344         -  ** On the other hand, the extra seeks could end up being significantly
  4345         -  ** more expensive.  */
  4346         -  assert( 42==sqlite3LogEst(18) );
  4347         -  if( saved_nEq==saved_nSkip
  4348         -   && saved_nEq+1<pProbe->nKeyCol
  4349         -   && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */
  4350         -   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  4351         -  ){
  4352         -    LogEst nIter;
  4353         -    pNew->u.btree.nEq++;
  4354         -    pNew->u.btree.nSkip++;
  4355         -    pNew->aLTerm[pNew->nLTerm++] = 0;
  4356         -    pNew->wsFlags |= WHERE_SKIPSCAN;
  4357         -    nIter = pProbe->aiRowLogEst[saved_nEq] - pProbe->aiRowLogEst[saved_nEq+1];
  4358         -    if( pTerm ){
  4359         -      /* TUNING:  When estimating skip-scan for a term that is also indexable,
  4360         -      ** multiply the cost of the skip-scan by 2.0, to make it a little less
  4361         -      ** desirable than the regular index lookup. */
  4362         -      nIter += 10;  assert( 10==sqlite3LogEst(2) );
  4363         -    }
  4364         -    pNew->nOut -= nIter;
  4365         -    /* TUNING:  Because uncertainties in the estimates for skip-scan queries,
  4366         -    ** add a 1.375 fudge factor to make skip-scan slightly less likely. */
  4367         -    nIter += 5;
  4368         -    whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nIter + nInMul);
  4369         -    pNew->nOut = saved_nOut;
  4370         -    pNew->u.btree.nEq = saved_nEq;
  4371         -    pNew->u.btree.nSkip = saved_nSkip;
  4372         -  }
  4373   4331     for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
  4374   4332       u16 eOp = pTerm->eOperator;   /* Shorthand for pTerm->eOperator */
  4375   4333       LogEst rCostIdx;
  4376   4334       LogEst nOutUnadjusted;        /* nOut before IN() and WHERE adjustments */
  4377   4335       int nIn = 0;
  4378   4336   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  4379   4337       int nRecValid = pBuilder->nRecValid;
................................................................................
  4528   4486       pNew->nOut = saved_nOut;
  4529   4487   #ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  4530   4488       pBuilder->nRecValid = nRecValid;
  4531   4489   #endif
  4532   4490     }
  4533   4491     pNew->prereq = saved_prereq;
  4534   4492     pNew->u.btree.nEq = saved_nEq;
  4535         -  pNew->u.btree.nSkip = saved_nSkip;
         4493  +  pNew->nSkip = saved_nSkip;
  4536   4494     pNew->wsFlags = saved_wsFlags;
  4537   4495     pNew->nOut = saved_nOut;
  4538   4496     pNew->nLTerm = saved_nLTerm;
         4497  +
         4498  +  /* Consider using a skip-scan if there are no WHERE clause constraints
         4499  +  ** available for the left-most terms of the index, and if the average
         4500  +  ** number of repeats in the left-most terms is at least 18. 
         4501  +  **
         4502  +  ** The magic number 18 is selected on the basis that scanning 17 rows
         4503  +  ** is almost always quicker than an index seek (even though if the index
         4504  +  ** contains fewer than 2^17 rows we assume otherwise in other parts of
         4505  +  ** the code). And, even if it is not, it should not be too much slower. 
         4506  +  ** On the other hand, the extra seeks could end up being significantly
         4507  +  ** more expensive.  */
         4508  +  assert( 42==sqlite3LogEst(18) );
         4509  +  if( saved_nEq==saved_nSkip
         4510  +   && saved_nEq+1<pProbe->nKeyCol
         4511  +   && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */
         4512  +   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
         4513  +  ){
         4514  +    LogEst nIter;
         4515  +    pNew->u.btree.nEq++;
         4516  +    pNew->nSkip++;
         4517  +    pNew->aLTerm[pNew->nLTerm++] = 0;
         4518  +    pNew->wsFlags |= WHERE_SKIPSCAN;
         4519  +    nIter = pProbe->aiRowLogEst[saved_nEq] - pProbe->aiRowLogEst[saved_nEq+1];
         4520  +    pNew->nOut -= nIter;
         4521  +    /* TUNING:  Because uncertainties in the estimates for skip-scan queries,
         4522  +    ** add a 1.375 fudge factor to make skip-scan slightly less likely. */
         4523  +    nIter += 5;
         4524  +    whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nIter + nInMul);
         4525  +    pNew->nOut = saved_nOut;
         4526  +    pNew->u.btree.nEq = saved_nEq;
         4527  +    pNew->nSkip = saved_nSkip;
         4528  +    pNew->wsFlags = saved_wsFlags;
         4529  +  }
         4530  +
  4539   4531     return rc;
  4540   4532   }
  4541   4533   
  4542   4534   /*
  4543   4535   ** Return True if it is possible that pIndex might be useful in
  4544   4536   ** implementing the ORDER BY clause in pBuilder.
  4545   4537   **
................................................................................
  4710   4702       /* Generate auto-index WhereLoops */
  4711   4703       WhereTerm *pTerm;
  4712   4704       WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
  4713   4705       for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
  4714   4706         if( pTerm->prereqRight & pNew->maskSelf ) continue;
  4715   4707         if( termCanDriveIndex(pTerm, pSrc, 0) ){
  4716   4708           pNew->u.btree.nEq = 1;
  4717         -        pNew->u.btree.nSkip = 0;
         4709  +        pNew->nSkip = 0;
  4718   4710           pNew->u.btree.pIndex = 0;
  4719   4711           pNew->nLTerm = 1;
  4720   4712           pNew->aLTerm[0] = pTerm;
  4721   4713           /* TUNING: One-time cost for computing the automatic index is
  4722   4714           ** estimated to be X*N*log2(N) where N is the number of rows in
  4723   4715           ** the table being indexed and where X is 7 (LogEst=28) for normal
  4724   4716           ** tables or 1.375 (LogEst=4) for views and subqueries.  The value
................................................................................
  4751   4743       if( pProbe->pPartIdxWhere!=0
  4752   4744        && !whereUsablePartialIndex(pSrc->iCursor, pWC, pProbe->pPartIdxWhere) ){
  4753   4745         testcase( pNew->iTab!=pSrc->iCursor );  /* See ticket [98d973b8f5] */
  4754   4746         continue;  /* Partial index inappropriate for this query */
  4755   4747       }
  4756   4748       rSize = pProbe->aiRowLogEst[0];
  4757   4749       pNew->u.btree.nEq = 0;
  4758         -    pNew->u.btree.nSkip = 0;
         4750  +    pNew->nSkip = 0;
  4759   4751       pNew->nLTerm = 0;
  4760   4752       pNew->iSortIdx = 0;
  4761   4753       pNew->rSetup = 0;
  4762   4754       pNew->prereq = mExtra;
  4763   4755       pNew->nOut = rSize;
  4764   4756       pNew->u.btree.pIndex = pProbe;
  4765   4757       b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor);
................................................................................
  5301   5293         rev = revSet = 0;
  5302   5294         distinctColumns = 0;
  5303   5295         for(j=0; j<nColumn; j++){
  5304   5296           u8 bOnce;   /* True to run the ORDER BY search loop */
  5305   5297   
  5306   5298           /* Skip over == and IS NULL terms */
  5307   5299           if( j<pLoop->u.btree.nEq
  5308         -         && pLoop->u.btree.nSkip==0
         5300  +         && pLoop->nSkip==0
  5309   5301            && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0
  5310   5302           ){
  5311   5303             if( i & WO_ISNULL ){
  5312   5304               testcase( isOrderDistinct );
  5313   5305               isOrderDistinct = 0;
  5314   5306             }
  5315   5307             continue;  
................................................................................
  5755   5747               }
  5756   5748             }
  5757   5749           }
  5758   5750         }
  5759   5751       }
  5760   5752   
  5761   5753   #ifdef WHERETRACE_ENABLED  /* >=2 */
  5762         -    if( sqlite3WhereTrace>=2 ){
         5754  +    if( sqlite3WhereTrace & 0x02 ){
  5763   5755         sqlite3DebugPrintf("---- after round %d ----\n", iLoop);
  5764   5756         for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){
  5765   5757           sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c",
  5766   5758              wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
  5767   5759              pTo->isOrdered>=0 ? (pTo->isOrdered+'0') : '?');
  5768   5760           if( pTo->isOrdered>0 ){
  5769   5761             sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop);
................................................................................
  5874   5866     pTab = pItem->pTab;
  5875   5867     if( IsVirtual(pTab) ) return 0;
  5876   5868     if( pItem->zIndex ) return 0;
  5877   5869     iCur = pItem->iCursor;
  5878   5870     pWC = &pWInfo->sWC;
  5879   5871     pLoop = pBuilder->pNew;
  5880   5872     pLoop->wsFlags = 0;
  5881         -  pLoop->u.btree.nSkip = 0;
         5873  +  pLoop->nSkip = 0;
  5882   5874     pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ, 0);
  5883   5875     if( pTerm ){
  5884   5876       pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
  5885   5877       pLoop->aLTerm[0] = pTerm;
  5886   5878       pLoop->nLTerm = 1;
  5887   5879       pLoop->u.btree.nEq = 1;
  5888   5880       /* TUNING: Cost of a rowid lookup is 10 */
  5889   5881       pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  5890   5882     }else{
  5891   5883       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  5892   5884         assert( pLoop->aLTermSpace==pLoop->aLTerm );
  5893         -      assert( ArraySize(pLoop->aLTermSpace)==4 );
  5894   5885         if( !IsUniqueIndex(pIdx)
  5895   5886          || pIdx->pPartIdxWhere!=0 
  5896   5887          || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
  5897   5888         ) continue;
  5898   5889         for(j=0; j<pIdx->nKeyCol; j++){
  5899   5890           pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
  5900   5891           if( pTerm==0 ) break;

Changes to src/whereInt.h.

   111    111     u8 iSortIdx;          /* Sorting index number.  0==None */
   112    112     LogEst rSetup;        /* One-time setup cost (ex: create transient index) */
   113    113     LogEst rRun;          /* Cost of running each loop */
   114    114     LogEst nOut;          /* Estimated number of output rows */
   115    115     union {
   116    116       struct {               /* Information for internal btree tables */
   117    117         u16 nEq;               /* Number of equality constraints */
   118         -      u16 nSkip;             /* Number of initial index columns to skip */
   119    118         Index *pIndex;         /* Index used, or NULL */
   120    119       } btree;
   121    120       struct {               /* Information for virtual tables */
   122    121         int idxNum;            /* Index number */
   123    122         u8 needFree;           /* True if sqlite3_free(idxStr) is needed */
   124    123         i8 isOrdered;          /* True if satisfies ORDER BY */
   125    124         u16 omitMask;          /* Terms that may be omitted */
   126    125         char *idxStr;          /* Index identifier string */
   127    126       } vtab;
   128    127     } u;
   129    128     u32 wsFlags;          /* WHERE_* flags describing the plan */
   130    129     u16 nLTerm;           /* Number of entries in aLTerm[] */
          130  +  u16 nSkip;            /* Number of NULL aLTerm[] entries */
   131    131     /**** whereLoopXfer() copies fields above ***********************/
   132    132   # define WHERE_LOOP_XFER_SZ offsetof(WhereLoop,nLSlot)
   133    133     u16 nLSlot;           /* Number of slots allocated for aLTerm[] */
   134    134     WhereTerm **aLTerm;   /* WhereTerms used */
   135    135     WhereLoop *pNextLoop; /* Next WhereLoop object in the WhereClause */
   136         -  WhereTerm *aLTermSpace[4];  /* Initial aLTerm[] space */
          136  +  WhereTerm *aLTermSpace[3];  /* Initial aLTerm[] space */
   137    137   };
   138    138   
   139    139   /* This object holds the prerequisites and the cost of running a
   140    140   ** subquery on one operand of an OR operator in the WHERE clause.
   141    141   ** See WhereOrSet for additional information 
   142    142   */
   143    143   struct WhereOrCost {

Changes to test/lock5.test.

   150    150   } {}
   151    151   
   152    152   #####################################################################
   153    153   
   154    154   do_test lock5-none.1 {
   155    155     sqlite3 db test.db -vfs unix-none
   156    156     sqlite3 db2 test.db -vfs unix-none
          157  +  execsql { PRAGMA mmap_size = 0 } db2
   157    158     execsql {
   158    159       BEGIN;
   159    160       INSERT INTO t1 VALUES(3, 4);
   160    161     }
   161    162   } {}
   162    163   do_test lock5-none.2 {
   163    164     execsql { SELECT * FROM t1 }
   164    165   } {1 2 3 4}
   165         -do_test lock5-flock.3 {
   166         -  execsql { SELECT * FROM t1 } db2
          166  +do_test lock5-none.3 {
          167  +  execsql { SELECT * FROM t1; } db2
   167    168   } {1 2}
   168    169   do_test lock5-none.4 {
   169    170     execsql { 
   170    171       BEGIN;
   171    172       SELECT * FROM t1;
   172    173     } db2
   173    174   } {1 2}
................................................................................
   179    180   ifcapable memorymanage {
   180    181     do_test lock5-none.6 {
   181    182       sqlite3_release_memory 1000000
   182    183       execsql {SELECT * FROM t1} db2
   183    184     } {1 2 3 4}
   184    185   }
   185    186   
   186         -do_test lock5-flock.X {
          187  +do_test lock5-none.X {
   187    188     db close
   188    189     db2 close
   189    190   } {}
   190    191   
   191    192   ifcapable lock_proxy_pragmas {
   192    193     set env(SQLITE_FORCE_PROXY_LOCKING) $::using_proxy
   193    194   }
   194    195   
   195    196   finish_test

Changes to test/releasetest.tcl.

   192    192       "Secure-Delete"           test
   193    193       "Unlock-Notify"           "QUICKTEST_INCLUDE=notify2.test test"
   194    194       "Update-Delete-Limit"     test
   195    195       "Extra-Robustness"        test
   196    196       "Device-Two"              test
   197    197       "Ftrapv"                  test
   198    198       "No-lookaside"            test
   199         -    "Default"                 "threadtest test"
          199  +    "Devkit"                  test
          200  +    "Default"                 "threadtest fulltest"
   200    201       "Device-One"              fulltest
   201    202     }
   202    203     Linux-i686 {
   203    204       "Devkit"                  test
   204    205       "Unlock-Notify"           "QUICKTEST_INCLUDE=notify2.test test"
   205    206       "Device-One"              test
   206    207       "Device-Two"              test

Added test/skipscan6.test.

            1  +# 2014-10-21
            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  +#
           12  +# This file implements tests of the "skip-scan" query strategy. In 
           13  +# particular, this file verifies that use of all columns of an index
           14  +# is always preferred over the use of a skip-scan on some columns of
           15  +# the same index.  Because of difficulties in scoring a skip-scan,
           16  +# the skip-scan can sometimes come out with a lower raw score when
           17  +# using STAT4.  But the query planner should detect this and use the
           18  +# full index rather than the skip-scan.
           19  +#
           20  +
           21  +set testdir [file dirname $argv0]
           22  +source $testdir/tester.tcl
           23  +set testprefix skipscan6
           24  +
           25  +ifcapable !stat4 {
           26  +  finish_test
           27  +  return
           28  +}
           29  +
           30  +do_execsql_test 1.1 {
           31  +  CREATE TABLE t1(
           32  +    aa int,
           33  +    bb int,
           34  +    cc int,
           35  +    dd int,
           36  +    ee int
           37  +  );
           38  +  CREATE INDEX ix on t1(aa, bb, cc,  dd DESC);
           39  +  ANALYZE sqlite_master;
           40  +  INSERT INTO sqlite_stat1 VALUES('t1','ix','2695116 1347558 264 18 2');
           41  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 196859 196859 32 1','0 15043 15043 92468 92499','0 19 286 81846 92499',X'0609010804031552977BD725BD28');
           42  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 14687 161 1 1','0 289067 299306 299457 299457','0 199 6772 273984 299457',X'060902020403013406314D67456415B819');
           43  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 19313 19308 22 1','0 325815 325815 343725 343746','0 261 9545 315009 343746',X'060902080403018A49B0A3AD1ED931');
           44  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 25047 9051 15 1','0 350443 350443 356590 356604','0 266 9795 325519 356604',X'06090208040301914C2DD2E91F93CF');
           45  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 42327 9906 7 1','0 376381 376381 380291 380297','0 268 10100 344232 380297',X'06090208040301934BF672511F7ED3');
           46  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 24513 2237 1 1','0 455150 467779 470015 470015','0 286 10880 425401 470015',X'06090202040301A703464A28F2611EF1EE');
           47  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18730 18724 15 1','0 479663 479663 498271 498285','0 287 10998 450793 498285',X'06090208040301A8494AF3A41EC50C');
           48  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 119603 47125 1 1','0 572425 572425 598915 598915','0 404 14230 546497 598915',X'06090208040302474FD1929A03194F');
           49  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 1454 1454 1 1','0 898346 898346 898373 898373','0 952 31165 827562 898373',X'06090208040304FD53F6A2A2097F64');
           50  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 57138 7069 1 1','0 1122389 1122389 1129457 1129457','0 1967 46801 1045943 1129457',X'06090208040309884BC4C52F1F6EB7');
           51  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 285 11 1 1','0 1197683 1197824 1197831 1197831','0 2033 50990 1112280 1197831',X'06090202040309D80346503FE2A9038E4F');
           52  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 25365 9773 1 1','0 1301013 1301013 1310785 1310785','0 2561 58806 1217877 1310785',X'0609020804030C5F4C8F88AB0AF2A2');
           53  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 45180 7222 1 1','0 1326378 1326378 1333599 1333599','0 2562 59921 1240187 1333599',X'0609020804030C604CAB75490B0351');
           54  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 8537 41 1 1','0 1496959 1497288 1497289 1497289','0 3050 68246 1394126 1497289',X'0609020204030EA0057F527459B0257C4B');
           55  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 26139 26131 17 1','0 1507977 1507977 1520578 1520594','0 3074 69188 1416111 1520594',X'0609020804030EB95169453423D4EA');
           56  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 102894 29678 1 1','0 1537421 1550467 1564894 1564894','0 3109 69669 1459820 1564894',X'0609020204030EE3183652A6ED3006EBCB');
           57  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 319 3 1 1','0 1796728 1796746 1796747 1796747','0 3650 86468 1682243 1796747',X'0609020204031163033550D0C41018C28D');
           58  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 127 127 1 1','0 2096194 2096194 2096205 2096205','0 5145 106437 1951535 2096205',X'060902080403180F53BB1AF727EE50');
           59  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 66574 5252 1 1','0 2230524 2265961 2271212 2271212','0 5899 114976 2085829 2271212',X'0609020204031B8A05195009976D223B90');
           60  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 19440 19440 1 1','0 2391680 2391680 2395663 2395663','0 6718 123714 2184781 2395663',X'0609020804031F7452E00A7B07431A');
           61  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18321 2177 1 1','0 2522928 2523231 2525407 2525407','0 7838 139084 2299958 2525407',X'06090201040324A7475231103B1AA7B8');
           62  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 22384 1361 1 1','0 2541249 2544834 2546194 2546194','0 7839 139428 2308416 2546194',X'06090202040324A8011652323D4B1AA9EB');
           63  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18699 855 1 1','0 2563633 2578178 2579032 2579032','0 7840 139947 2321671 2579032',X'06090202040324A9077452323D7D1052C5');
           64  +  INSERT INTO sqlite_stat4 VALUES('t1','ix','17965 1579 1579 1 1','2677151 2690666 2690666 2692244 2692244','1 9870 153959 2418294 2692244',X'060102080403021B8A4FE1AB84032B35');
           65  +  ANALYZE sqlite_master;
           66  +} {}
           67  +do_execsql_test 1.2 {
           68  +  EXPLAIN QUERY PLAN
           69  +  SELECT COUNT(*)
           70  +    FROM t1
           71  +   WHERE bb=21
           72  +     AND aa=1
           73  +     AND dd BETWEEN 1413833728 and 1413837331;
           74  +} {/INDEX ix .aa=. AND bb=../}
           75  +
           76  +do_execsql_test 2.1 {
           77  +  DROP INDEX ix;
           78  +  CREATE INDEX good on t1(bb, aa, dd DESC);
           79  +  CREATE INDEX bad on t1(aa, bb, cc,  dd DESC);
           80  +  DELETE FROM sqlite_stat1;
           81  +  DELETE FROM sqlite_stat4;
           82  +  INSERT INTO sqlite_stat1 VALUES('t1','good','2695116 299 264 2');
           83  +  INSERT INTO sqlite_stat1 VALUES('t1','bad','2695116 1347558 264 18 2');
           84  +  INSERT INTO sqlite_stat4 VALUES('t1','good','197030 196859 32 1','15086 15086 92511 92536','19 25 81644 92536',X'05010904031552977BD725BD22');
           85  +  INSERT INTO sqlite_stat4 VALUES('t1','good','14972 14687 1 1','289878 289878 299457 299457','199 244 267460 299457',X'050209040301344F7E569402C419');
           86  +  INSERT INTO sqlite_stat4 VALUES('t1','good','19600 19313 22 1','327127 327127 346222 346243','261 319 306884 346243',X'0502090403018A49503BC01EC577');
           87  +  INSERT INTO sqlite_stat4 VALUES('t1','good','25666 25047 15 1','352087 352087 372692 372706','266 327 325601 372706',X'050209040301914C2DD2E91F93CF');
           88  +  INSERT INTO sqlite_stat4 VALUES('t1','good','42392 42327 26 1','378657 378657 382547 382572','268 331 333529 382572',X'05020904030193533B2FE326ED48');
           89  +  INSERT INTO sqlite_stat4 VALUES('t1','good','24619 24513 11 1','457872 457872 461748 461758','286 358 399322 461758',X'050209040301A752B1557825EA7C');
           90  +  INSERT INTO sqlite_stat4 VALUES('t1','good','18969 18730 15 1','482491 482491 501105 501119','287 360 433605 501119',X'050209040301A8494AF3A41EC50C');
           91  +  INSERT INTO sqlite_stat4 VALUES('t1','good','119710 119603 1 1','576500 576500 598915 598915','404 505 519877 598915',X'05020904030247539A7A7912F617');
           92  +  INSERT INTO sqlite_stat4 VALUES('t1','good','11955 11946 1 1','889796 889796 898373 898373','938 1123 794694 898373',X'050209040304EF4DF9C4150BBB28');
           93  +  INSERT INTO sqlite_stat4 VALUES('t1','good','57197 57138 24 1','1129865 1129865 1151492 1151515','1967 2273 1027048 1151515',X'05020904030988533510BC26E20A');
           94  +  INSERT INTO sqlite_stat4 VALUES('t1','good','3609 3543 1 1','1196265 1196265 1197831 1197831','2002 2313 1070108 1197831',X'050209040309B050E95CD718D94D');
           95  +  INSERT INTO sqlite_stat4 VALUES('t1','good','25391 25365 13 1','1309378 1309378 1315567 1315579','2561 2936 1178358 1315579',X'05020904030C5F53DF9E13283570');
           96  +  INSERT INTO sqlite_stat4 VALUES('t1','good','45232 45180 17 1','1334769 1334769 1337946 1337962','2562 2938 1198998 1337962',X'05020904030C60541CACEE28BCAC');
           97  +  INSERT INTO sqlite_stat4 VALUES('t1','good','5496 5493 1 1','1495882 1495882 1497289 1497289','3043 3479 1348695 1497289',X'05020904030E99515C62AD0F0B34');
           98  +  INSERT INTO sqlite_stat4 VALUES('t1','good','26348 26139 17 1','1517381 1517381 1529990 1530006','3074 3519 1378320 1530006',X'05020904030EB95169453423D4EA');
           99  +  INSERT INTO sqlite_stat4 VALUES('t1','good','102927 102894 10 1','1547088 1547088 1649950 1649959','3109 3559 1494260 1649959',X'05020904030EE34D309F671FFA47');
          100  +  INSERT INTO sqlite_stat4 VALUES('t1','good','3602 3576 1 1','1793873 1793873 1796747 1796747','3601 4128 1630783 1796747',X'050209040311294FE88B432219B9');
          101  +  INSERT INTO sqlite_stat4 VALUES('t1','good','154 154 1 1','2096059 2096059 2096205 2096205','5037 5779 1893039 2096205',X'050209040317994EFF05A016DCED');
          102  +  INSERT INTO sqlite_stat4 VALUES('t1','good','68153 66574 60 1','2244039 2244039 2268892 2268951','5899 6749 2027553 2268951',X'05020904031B8A532DBC5A26D2BA');
          103  +  INSERT INTO sqlite_stat4 VALUES('t1','good','321 321 1 1','2395618 2395618 2395663 2395663','6609 7528 2118435 2395663',X'05020904031EFA54078EEE1E2D65');
          104  +  INSERT INTO sqlite_stat4 VALUES('t1','good','19449 19440 22 1','2407769 2407769 2426049 2426070','6718 7651 2146904 2426070',X'05020904031F7450E6118C2336BD');
          105  +  INSERT INTO sqlite_stat4 VALUES('t1','good','18383 18321 56 1','2539949 2539949 2551080 2551135','7838 8897 2245459 2551135',X'050209040324A752EA2E1E2642B2');
          106  +  INSERT INTO sqlite_stat4 VALUES('t1','good','22479 22384 60 1','2558332 2558332 2565233 2565292','7839 8899 2251202 2565292',X'050209040324A853926538279A5F');
          107  +  INSERT INTO sqlite_stat4 VALUES('t1','good','18771 18699 63 1','2580811 2580811 2596914 2596976','7840 8901 2263572 2596976',X'050209040324A9526C1DE9256E72');
          108  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 196859 196859 32 1','0 15043 15043 92468 92499','0 19 286 81846 92499',X'0609010804031552977BD725BD28');
          109  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 14687 161 1 1','0 289067 299306 299457 299457','0 199 6772 273984 299457',X'060902020403013406314D67456415B819');
          110  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 19313 19308 22 1','0 325815 325815 343725 343746','0 261 9545 315009 343746',X'060902080403018A49B0A3AD1ED931');
          111  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 25047 9051 15 1','0 350443 350443 356590 356604','0 266 9795 325519 356604',X'06090208040301914C2DD2E91F93CF');
          112  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 42327 9906 7 1','0 376381 376381 380291 380297','0 268 10100 344232 380297',X'06090208040301934BF672511F7ED3');
          113  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 24513 2237 1 1','0 455150 467779 470015 470015','0 286 10880 425401 470015',X'06090202040301A703464A28F2611EF1EE');
          114  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18730 18724 15 1','0 479663 479663 498271 498285','0 287 10998 450793 498285',X'06090208040301A8494AF3A41EC50C');
          115  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 119603 47125 1 1','0 572425 572425 598915 598915','0 404 14230 546497 598915',X'06090208040302474FD1929A03194F');
          116  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 1454 1454 1 1','0 898346 898346 898373 898373','0 952 31165 827562 898373',X'06090208040304FD53F6A2A2097F64');
          117  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 57138 7069 1 1','0 1122389 1122389 1129457 1129457','0 1967 46801 1045943 1129457',X'06090208040309884BC4C52F1F6EB7');
          118  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 285 11 1 1','0 1197683 1197824 1197831 1197831','0 2033 50990 1112280 1197831',X'06090202040309D80346503FE2A9038E4F');
          119  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 25365 9773 1 1','0 1301013 1301013 1310785 1310785','0 2561 58806 1217877 1310785',X'0609020804030C5F4C8F88AB0AF2A2');
          120  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 45180 7222 1 1','0 1326378 1326378 1333599 1333599','0 2562 59921 1240187 1333599',X'0609020804030C604CAB75490B0351');
          121  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 8537 41 1 1','0 1496959 1497288 1497289 1497289','0 3050 68246 1394126 1497289',X'0609020204030EA0057F527459B0257C4B');
          122  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 26139 26131 17 1','0 1507977 1507977 1520578 1520594','0 3074 69188 1416111 1520594',X'0609020804030EB95169453423D4EA');
          123  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 102894 29678 1 1','0 1537421 1550467 1564894 1564894','0 3109 69669 1459820 1564894',X'0609020204030EE3183652A6ED3006EBCB');
          124  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 319 3 1 1','0 1796728 1796746 1796747 1796747','0 3650 86468 1682243 1796747',X'0609020204031163033550D0C41018C28D');
          125  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 127 127 1 1','0 2096194 2096194 2096205 2096205','0 5145 106437 1951535 2096205',X'060902080403180F53BB1AF727EE50');
          126  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 66574 5252 1 1','0 2230524 2265961 2271212 2271212','0 5899 114976 2085829 2271212',X'0609020204031B8A05195009976D223B90');
          127  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 19440 19440 1 1','0 2391680 2391680 2395663 2395663','0 6718 123714 2184781 2395663',X'0609020804031F7452E00A7B07431A');
          128  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18321 2177 1 1','0 2522928 2523231 2525407 2525407','0 7838 139084 2299958 2525407',X'06090201040324A7475231103B1AA7B8');
          129  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 22384 1361 1 1','0 2541249 2544834 2546194 2546194','0 7839 139428 2308416 2546194',X'06090202040324A8011652323D4B1AA9EB');
          130  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18699 855 1 1','0 2563633 2578178 2579032 2579032','0 7840 139947 2321671 2579032',X'06090202040324A9077452323D7D1052C5');
          131  +  INSERT INTO sqlite_stat4 VALUES('t1','bad','17965 1579 1579 1 1','2677151 2690666 2690666 2692244 2692244','1 9870 153959 2418294 2692244',X'060102080403021B8A4FE1AB84032B35');
          132  +  ANALYZE sqlite_master;
          133  +} {}
          134  +do_execsql_test 2.2 {
          135  +  EXPLAIN QUERY PLAN
          136  +  SELECT COUNT(*)
          137  +    FROM t1
          138  +   WHERE bb=21
          139  +     AND aa=1
          140  +     AND dd BETWEEN 1413833728 and 1413837331;
          141  +} {/INDEX good .bb=. AND aa=. AND dd>. AND dd<../}
          142  +
          143  +
          144  +
          145  +finish_test

Changes to test/sort.test.

    11     11   #
    12     12   # This file implements regression tests for SQLite library.  The
    13     13   # focus of this file is testing the sorter (code in vdbesort.c).
    14     14   #
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
           18  +set testprefix sort
    18     19   
    19     20   # Create a bunch of data to sort against
    20     21   #
    21     22   do_test sort-1.0 {
    22     23     execsql {
    23     24       CREATE TABLE t1(
    24     25          n int,

Changes to tool/vdbe-compress.tcl.

   106    106       append afterUnion $line\n
   107    107       set vlist {}
   108    108     } elseif {[llength $vlist]>0} {
   109    109       append line " "
   110    110       foreach v $vlist {
   111    111         regsub -all "(\[^a-zA-Z0-9>.\])${v}(\\W)" $line "\\1u.$sname.$v\\2" line
   112    112         regsub -all "(\[^a-zA-Z0-9>.\])${v}(\\W)" $line "\\1u.$sname.$v\\2" line
          113  +
          114  +      # The expressions above fail to catch instance of variable "abc" in
          115  +      # expressions like (32>abc). The following expression makes those
          116  +      # substitutions.
          117  +      regsub -all "(\[^-\])>${v}(\\W)" $line "\\1>u.$sname.$v\\2" line
   113    118       }
   114    119       append afterUnion [string trimright $line]\n
   115    120     } elseif {$line=="" && [eof stdin]} {
   116    121       # no-op
   117    122     } else {
   118    123       append afterUnion $line\n
   119    124     }