/ Check-in [17fd8f3c]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Merge the virtual table query planner enhancement, the RTREE cost estimate fix, and the statement journal spill delay enhancement from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 17fd8f3cf0ec565e08403dc8e10a1cffc2bbe165
User & Date: drh 2016-03-07 17:49:17
Context
2016-03-16
01:16
Merge all recent enhancements from trunk. check-in: 6a7ee04b user: drh tags: sessions
2016-03-07
17:49
Merge the virtual table query planner enhancement, the RTREE cost estimate fix, and the statement journal spill delay enhancement from trunk. check-in: 17fd8f3c user: drh tags: sessions
17:37
Add the SQLITE_CONFIG_STMTJRNL_SPILL option for sqlite3_config(). check-in: b6c42024 user: drh tags: trunk
2016-03-04
16:42
Merge recent enhancements from trunk. Default page size is 4096. Writes to statement journals are avoided. check-in: 456df336 user: drh tags: sessions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   171    171            expr.lo fault.lo fkey.lo \
   172    172            fts3.lo fts3_aux.lo fts3_expr.lo fts3_hash.lo fts3_icu.lo \
   173    173            fts3_porter.lo fts3_snippet.lo fts3_tokenizer.lo fts3_tokenizer1.lo \
   174    174            fts3_tokenize_vtab.lo \
   175    175            fts3_unicode.lo fts3_unicode2.lo fts3_write.lo \
   176    176   	 fts5.lo \
   177    177            func.lo global.lo hash.lo \
   178         -         icu.lo insert.lo journal.lo json1.lo legacy.lo loadext.lo \
          178  +         icu.lo insert.lo json1.lo legacy.lo loadext.lo \
   179    179            main.lo malloc.lo mem0.lo mem1.lo mem2.lo mem3.lo mem5.lo \
   180    180            memjournal.lo \
   181    181            mutex.lo mutex_noop.lo mutex_unix.lo mutex_w32.lo \
   182    182            notify.lo opcodes.lo os.lo os_unix.lo os_win.lo \
   183    183            pager.lo parse.lo pcache.lo pcache1.lo pragma.lo prepare.lo printf.lo \
   184    184            random.lo resolve.lo rowset.lo rtree.lo \
   185    185            sqlite3session.lo select.lo sqlite3rbu.lo status.lo \
................................................................................
   223    223     $(TOP)/src/fkey.c \
   224    224     $(TOP)/src/func.c \
   225    225     $(TOP)/src/global.c \
   226    226     $(TOP)/src/hash.c \
   227    227     $(TOP)/src/hash.h \
   228    228     $(TOP)/src/hwtime.h \
   229    229     $(TOP)/src/insert.c \
   230         -  $(TOP)/src/journal.c \
   231    230     $(TOP)/src/legacy.c \
   232    231     $(TOP)/src/loadext.c \
   233    232     $(TOP)/src/main.c \
   234    233     $(TOP)/src/malloc.c \
   235    234     $(TOP)/src/mem0.c \
   236    235     $(TOP)/src/mem1.c \
   237    236     $(TOP)/src/mem2.c \
................................................................................
   742    741   
   743    742   hash.lo:	$(TOP)/src/hash.c $(HDR)
   744    743   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/hash.c
   745    744   
   746    745   insert.lo:	$(TOP)/src/insert.c $(HDR)
   747    746   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/insert.c
   748    747   
   749         -journal.lo:	$(TOP)/src/journal.c $(HDR)
   750         -	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/journal.c
   751         -
   752    748   legacy.lo:	$(TOP)/src/legacy.c $(HDR)
   753    749   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/legacy.c
   754    750   
   755    751   loadext.lo:	$(TOP)/src/loadext.c $(HDR)
   756    752   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/loadext.c
   757    753   
   758    754   main.lo:	$(TOP)/src/main.c $(HDR)

Changes to Makefile.msc.

   991    991            callback.lo complete.lo ctime.lo date.lo dbstat.lo delete.lo \
   992    992            expr.lo fault.lo fkey.lo \
   993    993            fts3.lo fts3_aux.lo fts3_expr.lo fts3_hash.lo fts3_icu.lo \
   994    994            fts3_porter.lo fts3_snippet.lo fts3_tokenizer.lo fts3_tokenizer1.lo \
   995    995            fts3_tokenize_vtab.lo fts3_unicode.lo fts3_unicode2.lo fts3_write.lo \
   996    996            fts5.lo \
   997    997            func.lo global.lo hash.lo \
   998         -         icu.lo insert.lo journal.lo legacy.lo loadext.lo \
          998  +         icu.lo insert.lo legacy.lo loadext.lo \
   999    999            main.lo malloc.lo mem0.lo mem1.lo mem2.lo mem3.lo mem5.lo \
  1000   1000            memjournal.lo \
  1001   1001            mutex.lo mutex_noop.lo mutex_unix.lo mutex_w32.lo \
  1002   1002            notify.lo opcodes.lo os.lo os_unix.lo os_win.lo \
  1003   1003            pager.lo pcache.lo pcache1.lo pragma.lo prepare.lo printf.lo \
  1004   1004            random.lo resolve.lo rowset.lo rtree.lo \
  1005   1005            sqlite3session.lo select.lo sqlite3rbu.lo status.lo \
................................................................................
  1056   1056     $(TOP)\src\expr.c \
  1057   1057     $(TOP)\src\fault.c \
  1058   1058     $(TOP)\src\fkey.c \
  1059   1059     $(TOP)\src\func.c \
  1060   1060     $(TOP)\src\global.c \
  1061   1061     $(TOP)\src\hash.c \
  1062   1062     $(TOP)\src\insert.c \
  1063         -  $(TOP)\src\journal.c \
  1064   1063     $(TOP)\src\legacy.c \
  1065   1064     $(TOP)\src\loadext.c \
  1066   1065     $(TOP)\src\main.c \
  1067   1066     $(TOP)\src\malloc.c \
  1068   1067     $(TOP)\src\mem0.c \
  1069   1068     $(TOP)\src\mem1.c \
  1070   1069     $(TOP)\src\mem2.c \
................................................................................
  1632   1631   
  1633   1632   hash.lo:	$(TOP)\src\hash.c $(HDR)
  1634   1633   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\hash.c
  1635   1634   
  1636   1635   insert.lo:	$(TOP)\src\insert.c $(HDR)
  1637   1636   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\insert.c
  1638   1637   
  1639         -journal.lo:	$(TOP)\src\journal.c $(HDR)
  1640         -	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\journal.c
  1641         -
  1642   1638   legacy.lo:	$(TOP)\src\legacy.c $(HDR)
  1643   1639   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\legacy.c
  1644   1640   
  1645   1641   loadext.lo:	$(TOP)\src\loadext.c $(HDR)
  1646   1642   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\loadext.c
  1647   1643   
  1648   1644   main.lo:	$(TOP)\src\main.c $(HDR)

Changes to ext/rtree/rtree.c.

  1737   1737   
  1738   1738     pIdxInfo->idxNum = 2;
  1739   1739     pIdxInfo->needToFreeIdxStr = 1;
  1740   1740     if( iIdx>0 && 0==(pIdxInfo->idxStr = sqlite3_mprintf("%s", zIdxStr)) ){
  1741   1741       return SQLITE_NOMEM;
  1742   1742     }
  1743   1743   
  1744         -  nRow = pRtree->nRowEst / (iIdx + 1);
         1744  +  nRow = pRtree->nRowEst >> (iIdx/2);
  1745   1745     pIdxInfo->estimatedCost = (double)6.0 * (double)nRow;
  1746   1746     setEstimatedRows(pIdxInfo, nRow);
  1747   1747   
  1748   1748     return rc;
  1749   1749   }
  1750   1750   
  1751   1751   /*

Changes to main.mk.

    58     58            callback.o complete.o ctime.o date.o dbstat.o delete.o expr.o \
    59     59   	 fault.o fkey.o \
    60     60            fts3.o fts3_aux.o fts3_expr.o fts3_hash.o fts3_icu.o fts3_porter.o \
    61     61            fts3_snippet.o fts3_tokenizer.o fts3_tokenizer1.o \
    62     62            fts3_tokenize_vtab.o \
    63     63   	 fts3_unicode.o fts3_unicode2.o \
    64     64            fts3_write.o fts5.o func.o global.o hash.o \
    65         -         icu.o insert.o journal.o json1.o legacy.o loadext.o \
           65  +         icu.o insert.o json1.o legacy.o loadext.o \
    66     66            main.o malloc.o mem0.o mem1.o mem2.o mem3.o mem5.o \
    67     67            memjournal.o \
    68     68            mutex.o mutex_noop.o mutex_unix.o mutex_w32.o \
    69     69            notify.o opcodes.o os.o os_unix.o os_win.o \
    70     70            pager.o pcache.o pcache1.o pragma.o prepare.o printf.o \
    71     71            random.o resolve.o rowset.o rtree.o select.o sqlite3rbu.o status.o \
    72     72            table.o threads.o tokenize.o treeview.o trigger.o \
................................................................................
   102    102     $(TOP)/src/fkey.c \
   103    103     $(TOP)/src/func.c \
   104    104     $(TOP)/src/global.c \
   105    105     $(TOP)/src/hash.c \
   106    106     $(TOP)/src/hash.h \
   107    107     $(TOP)/src/hwtime.h \
   108    108     $(TOP)/src/insert.c \
   109         -  $(TOP)/src/journal.c \
   110    109     $(TOP)/src/legacy.c \
   111    110     $(TOP)/src/loadext.c \
   112    111     $(TOP)/src/main.c \
   113    112     $(TOP)/src/malloc.c \
   114    113     $(TOP)/src/mem0.c \
   115    114     $(TOP)/src/mem1.c \
   116    115     $(TOP)/src/mem2.c \

Changes to src/global.c.

   155    155   /* The minimum PMA size is set to this value multiplied by the database
   156    156   ** page size in bytes.
   157    157   */
   158    158   #ifndef SQLITE_SORTER_PMASZ
   159    159   # define SQLITE_SORTER_PMASZ 250
   160    160   #endif
   161    161   
          162  +/* Statement journals spill to disk when their size exceeds the following
          163  +** threashold (in bytes). 0 means that statement journals are created and
          164  +** written to disk immediately (the default behavior for SQLite versions
          165  +** before 3.12.0).  -1 means always keep the entire statement journal in
          166  +** memory.  (The statement journal is also always held entirely in memory
          167  +** if journal_mode=MEMORY or if temp_store=MEMORY, regardless of this
          168  +** setting.)
          169  +*/
          170  +#ifndef SQLITE_STMTJRNL_SPILL 
          171  +# define SQLITE_STMTJRNL_SPILL (64*1024)
          172  +#endif
          173  +
   162    174   /*
   163    175   ** The following singleton contains the global configuration for
   164    176   ** the SQLite library.
   165    177   */
   166    178   SQLITE_WSD struct Sqlite3Config sqlite3Config = {
   167    179      SQLITE_DEFAULT_MEMSTATUS,  /* bMemstat */
   168    180      1,                         /* bCoreMutex */
................................................................................
   169    181      SQLITE_THREADSAFE==1,      /* bFullMutex */
   170    182      SQLITE_USE_URI,            /* bOpenUri */
   171    183      SQLITE_ALLOW_COVERING_INDEX_SCAN,   /* bUseCis */
   172    184      0x7ffffffe,                /* mxStrlen */
   173    185      0,                         /* neverCorrupt */
   174    186      128,                       /* szLookaside */
   175    187      500,                       /* nLookaside */
          188  +   SQLITE_STMTJRNL_SPILL,     /* nStmtSpill */
   176    189      {0,0,0,0,0,0,0,0},         /* m */
   177    190      {0,0,0,0,0,0,0,0,0},       /* mutex */
   178    191      {0,0,0,0,0,0,0,0,0,0,0,0,0},/* pcache2 */
   179    192      (void*)0,                  /* pHeap */
   180    193      0,                         /* nHeap */
   181    194      0, 0,                      /* mnHeap, mxHeap */
   182    195      SQLITE_DEFAULT_MMAP_SIZE,  /* szMmap */

Deleted src/journal.c.

     1         -/*
     2         -** 2007 August 22
     3         -**
     4         -** The author disclaims copyright to this source code.  In place of
     5         -** a legal notice, here is a blessing:
     6         -**
     7         -**    May you do good and not evil.
     8         -**    May you find forgiveness for yourself and forgive others.
     9         -**    May you share freely, never taking more than you give.
    10         -**
    11         -*************************************************************************
    12         -**
    13         -** This file implements a special kind of sqlite3_file object used
    14         -** by SQLite to create journal files if the atomic-write optimization
    15         -** is enabled.
    16         -**
    17         -** The distinctive characteristic of this sqlite3_file is that the
    18         -** actual on disk file is created lazily. When the file is created,
    19         -** the caller specifies a buffer size for an in-memory buffer to
    20         -** be used to service read() and write() requests. The actual file
    21         -** on disk is not created or populated until either:
    22         -**
    23         -**   1) The in-memory representation grows too large for the allocated 
    24         -**      buffer, or
    25         -**   2) The sqlite3JournalCreate() function is called.
    26         -*/
    27         -#if 0 
    28         -#ifdef SQLITE_ENABLE_ATOMIC_WRITE
    29         -#include "sqliteInt.h"
    30         -
    31         -
    32         -/*
    33         -** A JournalFile object is a subclass of sqlite3_file used by
    34         -** as an open file handle for journal files.
    35         -*/
    36         -struct JournalFile {
    37         -  sqlite3_io_methods *pMethod;    /* I/O methods on journal files */
    38         -  int nBuf;                       /* Size of zBuf[] in bytes */
    39         -  char *zBuf;                     /* Space to buffer journal writes */
    40         -  int iSize;                      /* Amount of zBuf[] currently used */
    41         -  int flags;                      /* xOpen flags */
    42         -  sqlite3_vfs *pVfs;              /* The "real" underlying VFS */
    43         -  sqlite3_file *pReal;            /* The "real" underlying file descriptor */
    44         -  const char *zJournal;           /* Name of the journal file */
    45         -};
    46         -typedef struct JournalFile JournalFile;
    47         -
    48         -/*
    49         -** If it does not already exists, create and populate the on-disk file 
    50         -** for JournalFile p.
    51         -*/
    52         -static int createFile(JournalFile *p){
    53         -  int rc = SQLITE_OK;
    54         -  if( !p->pReal ){
    55         -    sqlite3_file *pReal = (sqlite3_file *)&p[1];
    56         -    rc = sqlite3OsOpen(p->pVfs, p->zJournal, pReal, p->flags, 0);
    57         -    if( rc==SQLITE_OK ){
    58         -      p->pReal = pReal;
    59         -      if( p->iSize>0 ){
    60         -        assert(p->iSize<=p->nBuf);
    61         -        rc = sqlite3OsWrite(p->pReal, p->zBuf, p->iSize, 0);
    62         -      }
    63         -      if( rc!=SQLITE_OK ){
    64         -        /* If an error occurred while writing to the file, close it before
    65         -        ** returning. This way, SQLite uses the in-memory journal data to 
    66         -        ** roll back changes made to the internal page-cache before this
    67         -        ** function was called.  */
    68         -        sqlite3OsClose(pReal);
    69         -        p->pReal = 0;
    70         -      }
    71         -    }
    72         -  }
    73         -  return rc;
    74         -}
    75         -
    76         -/*
    77         -** Close the file.
    78         -*/
    79         -static int jrnlClose(sqlite3_file *pJfd){
    80         -  JournalFile *p = (JournalFile *)pJfd;
    81         -  if( p->pReal ){
    82         -    sqlite3OsClose(p->pReal);
    83         -  }
    84         -  sqlite3_free(p->zBuf);
    85         -  return SQLITE_OK;
    86         -}
    87         -
    88         -/*
    89         -** Read data from the file.
    90         -*/
    91         -static int jrnlRead(
    92         -  sqlite3_file *pJfd,    /* The journal file from which to read */
    93         -  void *zBuf,            /* Put the results here */
    94         -  int iAmt,              /* Number of bytes to read */
    95         -  sqlite_int64 iOfst     /* Begin reading at this offset */
    96         -){
    97         -  int rc = SQLITE_OK;
    98         -  JournalFile *p = (JournalFile *)pJfd;
    99         -  if( p->pReal ){
   100         -    rc = sqlite3OsRead(p->pReal, zBuf, iAmt, iOfst);
   101         -  }else if( (iAmt+iOfst)>p->iSize ){
   102         -    rc = SQLITE_IOERR_SHORT_READ;
   103         -  }else{
   104         -    memcpy(zBuf, &p->zBuf[iOfst], iAmt);
   105         -  }
   106         -  return rc;
   107         -}
   108         -
   109         -/*
   110         -** Write data to the file.
   111         -*/
   112         -static int jrnlWrite(
   113         -  sqlite3_file *pJfd,    /* The journal file into which to write */
   114         -  const void *zBuf,      /* Take data to be written from here */
   115         -  int iAmt,              /* Number of bytes to write */
   116         -  sqlite_int64 iOfst     /* Begin writing at this offset into the file */
   117         -){
   118         -  int rc = SQLITE_OK;
   119         -  JournalFile *p = (JournalFile *)pJfd;
   120         -  if( !p->pReal && (iOfst+iAmt)>p->nBuf ){
   121         -    rc = createFile(p);
   122         -  }
   123         -  if( rc==SQLITE_OK ){
   124         -    if( p->pReal ){
   125         -      rc = sqlite3OsWrite(p->pReal, zBuf, iAmt, iOfst);
   126         -    }else{
   127         -      memcpy(&p->zBuf[iOfst], zBuf, iAmt);
   128         -      if( p->iSize<(iOfst+iAmt) ){
   129         -        p->iSize = (iOfst+iAmt);
   130         -      }
   131         -    }
   132         -  }
   133         -  return rc;
   134         -}
   135         -
   136         -/*
   137         -** Truncate the file.
   138         -*/
   139         -static int jrnlTruncate(sqlite3_file *pJfd, sqlite_int64 size){
   140         -  int rc = SQLITE_OK;
   141         -  JournalFile *p = (JournalFile *)pJfd;
   142         -  if( p->pReal ){
   143         -    rc = sqlite3OsTruncate(p->pReal, size);
   144         -  }else if( size<p->iSize ){
   145         -    p->iSize = size;
   146         -  }
   147         -  return rc;
   148         -}
   149         -
   150         -/*
   151         -** Sync the file.
   152         -*/
   153         -static int jrnlSync(sqlite3_file *pJfd, int flags){
   154         -  int rc;
   155         -  JournalFile *p = (JournalFile *)pJfd;
   156         -  if( p->pReal ){
   157         -    rc = sqlite3OsSync(p->pReal, flags);
   158         -  }else{
   159         -    rc = SQLITE_OK;
   160         -  }
   161         -  return rc;
   162         -}
   163         -
   164         -/*
   165         -** Query the size of the file in bytes.
   166         -*/
   167         -static int jrnlFileSize(sqlite3_file *pJfd, sqlite_int64 *pSize){
   168         -  int rc = SQLITE_OK;
   169         -  JournalFile *p = (JournalFile *)pJfd;
   170         -  if( p->pReal ){
   171         -    rc = sqlite3OsFileSize(p->pReal, pSize);
   172         -  }else{
   173         -    *pSize = (sqlite_int64) p->iSize;
   174         -  }
   175         -  return rc;
   176         -}
   177         -
   178         -/*
   179         -** Table of methods for JournalFile sqlite3_file object.
   180         -*/
   181         -static struct sqlite3_io_methods JournalFileMethods = {
   182         -  1,             /* iVersion */
   183         -  jrnlClose,     /* xClose */
   184         -  jrnlRead,      /* xRead */
   185         -  jrnlWrite,     /* xWrite */
   186         -  jrnlTruncate,  /* xTruncate */
   187         -  jrnlSync,      /* xSync */
   188         -  jrnlFileSize,  /* xFileSize */
   189         -  0,             /* xLock */
   190         -  0,             /* xUnlock */
   191         -  0,             /* xCheckReservedLock */
   192         -  0,             /* xFileControl */
   193         -  0,             /* xSectorSize */
   194         -  0,             /* xDeviceCharacteristics */
   195         -  0,             /* xShmMap */
   196         -  0,             /* xShmLock */
   197         -  0,             /* xShmBarrier */
   198         -  0              /* xShmUnmap */
   199         -};
   200         -
   201         -/* 
   202         -** Open a journal file.
   203         -*/
   204         -int sqlite3JournalOpen(
   205         -  sqlite3_vfs *pVfs,         /* The VFS to use for actual file I/O */
   206         -  const char *zName,         /* Name of the journal file */
   207         -  sqlite3_file *pJfd,        /* Preallocated, blank file handle */
   208         -  int flags,                 /* Opening flags */
   209         -  int nBuf                   /* Bytes buffered before opening the file */
   210         -){
   211         -  JournalFile *p = (JournalFile *)pJfd;
   212         -  memset(p, 0, sqlite3JournalSize(pVfs));
   213         -  if( nBuf>0 ){
   214         -    p->zBuf = sqlite3MallocZero(nBuf);
   215         -    if( !p->zBuf ){
   216         -      return SQLITE_NOMEM_BKPT;
   217         -    }
   218         -  }else{
   219         -    return sqlite3OsOpen(pVfs, zName, pJfd, flags, 0);
   220         -  }
   221         -  p->pMethod = &JournalFileMethods;
   222         -  p->nBuf = nBuf;
   223         -  p->flags = flags;
   224         -  p->zJournal = zName;
   225         -  p->pVfs = pVfs;
   226         -  return SQLITE_OK;
   227         -}
   228         -
   229         -/*
   230         -** If the argument p points to a JournalFile structure, and the underlying
   231         -** file has not yet been created, create it now.
   232         -*/
   233         -int sqlite3JournalCreate(sqlite3_file *p){
   234         -  if( p->pMethods!=&JournalFileMethods ){
   235         -    return SQLITE_OK;
   236         -  }
   237         -  return createFile((JournalFile *)p);
   238         -}
   239         -
   240         -/*
   241         -** The file-handle passed as the only argument is guaranteed to be an open
   242         -** file. It may or may not be of class JournalFile. If the file is a
   243         -** JournalFile, and the underlying file on disk has not yet been opened,
   244         -** return 0. Otherwise, return 1.
   245         -*/
   246         -int sqlite3JournalExists(sqlite3_file *p){
   247         -  return (p->pMethods!=&JournalFileMethods || ((JournalFile *)p)->pReal!=0);
   248         -}
   249         -
   250         -/* 
   251         -** Return the number of bytes required to store a JournalFile that uses vfs
   252         -** pVfs to create the underlying on-disk files.
   253         -*/
   254         -int sqlite3JournalSize(sqlite3_vfs *pVfs){
   255         -  return (pVfs->szOsFile+sizeof(JournalFile));
   256         -}
   257         -#endif
   258         -#endif

Changes to src/main.c.

   628    628       }
   629    629   #endif
   630    630   
   631    631       case SQLITE_CONFIG_PMASZ: {
   632    632         sqlite3GlobalConfig.szPma = va_arg(ap, unsigned int);
   633    633         break;
   634    634       }
          635  +
          636  +    case SQLITE_CONFIG_STMTJRNL_SPILL: {
          637  +      sqlite3GlobalConfig.nStmtSpill = va_arg(ap, int);
          638  +      break;
          639  +    }
   635    640   
   636    641       default: {
   637    642         rc = SQLITE_ERROR;
   638    643         break;
   639    644       }
   640    645     }
   641    646     va_end(ap);

Changes to src/memjournal.c.

    56     56   ** This structure is a subclass of sqlite3_file. Each open memory-journal
    57     57   ** is an instance of this class.
    58     58   */
    59     59   struct MemJournal {
    60     60     const sqlite3_io_methods *pMethod; /* Parent class. MUST BE FIRST */
    61     61     int nChunkSize;                 /* In-memory chunk-size */
    62     62   
    63         -  int nBuf;                       /* Bytes of data before flushing */
           63  +  int nSpill;                     /* Bytes of data before flushing */
    64     64     int nSize;                      /* Bytes of data currently in memory */
    65     65     FileChunk *pFirst;              /* Head of in-memory chunk-list */
    66     66     FilePoint endpoint;             /* Pointer to the end of the file */
    67     67     FilePoint readpoint;            /* Pointer to the end of the last xRead() */
    68     68   
    69     69     int flags;                      /* xOpen flags */
    70     70     sqlite3_vfs *pVfs;              /* The "real" underlying VFS */
................................................................................
   105    105         pChunk = p->readpoint.pChunk;
   106    106       }
   107    107   
   108    108       iChunkOffset = (int)(iOfst%p->nChunkSize);
   109    109       do {
   110    110         int iSpace = p->nChunkSize - iChunkOffset;
   111    111         int nCopy = MIN(nRead, (p->nChunkSize - iChunkOffset));
   112         -      memcpy(zOut, &pChunk->zChunk[iChunkOffset], nCopy);
          112  +      memcpy(zOut, (u8*)pChunk->zChunk + iChunkOffset, nCopy);
   113    113         zOut += nCopy;
   114    114         nRead -= iSpace;
   115    115         iChunkOffset = 0;
   116    116       } while( nRead>=0 && (pChunk=pChunk->pNext)!=0 && nRead>0 );
   117    117       p->readpoint.iOffset = iOfst+iAmt;
   118    118       p->readpoint.pChunk = pChunk;
   119    119     }
................................................................................
   133    133     } 
   134    134     p->pFirst = 0;
   135    135   }
   136    136   
   137    137   /*
   138    138   ** Flush the contents of memory to a real file on disk.
   139    139   */
   140         -static int createFile(MemJournal *p){
          140  +static int memjrnlCreateFile(MemJournal *p){
   141    141     int rc = SQLITE_OK;
   142    142     if( !p->pReal ){
   143    143       sqlite3_file *pReal = (sqlite3_file *)&p[1];
   144    144       rc = sqlite3OsOpen(p->pVfs, p->zJournal, pReal, p->flags, 0);
   145    145       if( rc==SQLITE_OK ){
   146    146         int nChunk = p->nChunkSize;
   147    147         i64 iOff = 0;
................................................................................
   149    149         p->pReal = pReal;
   150    150         for(pIter=p->pFirst; pIter && rc==SQLITE_OK; pIter=pIter->pNext){
   151    151           int nWrite = nChunk;
   152    152           if( pIter==p->endpoint.pChunk ){
   153    153             nWrite = p->endpoint.iOffset % p->nChunkSize;
   154    154             if( nWrite==0 ) nWrite = p->nChunkSize;
   155    155           }
   156         -        rc = sqlite3OsWrite(pReal, pIter->zChunk, nWrite, iOff);
          156  +        rc = sqlite3OsWrite(pReal, (u8*)pIter->zChunk, nWrite, iOff);
   157    157           iOff += nWrite;
   158    158         }
   159    159         if( rc!=SQLITE_OK ){
   160    160           /* If an error occurred while writing to the file, close it before
   161    161           ** returning. This way, SQLite uses the in-memory journal data to 
   162    162           ** roll back changes made to the internal page-cache before this
   163    163           ** function was called.  */
................................................................................
   188    188   
   189    189     /* If the file has already been created on disk. */
   190    190     if( p->pReal ){
   191    191       return sqlite3OsWrite(p->pReal, zBuf, iAmt, iOfst);
   192    192     }
   193    193   
   194    194     /* If the file should be created now. */
   195         -  else if( p->nBuf>0 && (iAmt+iOfst)>p->nBuf ){
   196         -    int rc = createFile(p);
          195  +  else if( p->nSpill>0 && (iAmt+iOfst)>p->nSpill ){
          196  +    int rc = memjrnlCreateFile(p);
   197    197       if( rc==SQLITE_OK ){
   198    198         rc = memjrnlWrite(pJfd, zBuf, iAmt, iOfst);
   199    199       }
   200    200       return rc;
   201    201     }
   202    202   
   203    203     /* If the contents of this write should be stored in memory */
................................................................................
   206    206       ** access writes are not required. The only exception to this is when
   207    207       ** the in-memory journal is being used by a connection using the
   208    208       ** atomic-write optimization. In this case the first 28 bytes of the
   209    209       ** journal file may be written as part of committing the transaction. */ 
   210    210       assert( iOfst==p->endpoint.iOffset || iOfst==0 );
   211    211       if( iOfst==0 && p->pFirst ){
   212    212         assert( p->nChunkSize>iAmt );
   213         -      memcpy(p->pFirst->zChunk, zBuf, iAmt);
          213  +      memcpy((u8*)p->pFirst->zChunk, zBuf, iAmt);
   214    214       }else{
   215    215         while( nWrite>0 ){
   216    216           FileChunk *pChunk = p->endpoint.pChunk;
   217    217           int iChunkOffset = (int)(p->endpoint.iOffset%p->nChunkSize);
   218    218           int iSpace = MIN(nWrite, p->nChunkSize - iChunkOffset);
   219    219   
   220    220           if( iChunkOffset==0 ){
................................................................................
   230    230             }else{
   231    231               assert( !p->pFirst );
   232    232               p->pFirst = pNew;
   233    233             }
   234    234             p->endpoint.pChunk = pNew;
   235    235           }
   236    236   
   237         -        memcpy(&p->endpoint.pChunk->zChunk[iChunkOffset], zWrite, iSpace);
          237  +        memcpy((u8*)p->endpoint.pChunk->zChunk + iChunkOffset, zWrite, iSpace);
   238    238           zWrite += iSpace;
   239    239           nWrite -= iSpace;
   240    240           p->endpoint.iOffset += iSpace;
   241    241         }
   242    242         p->nSize = iAmt + iOfst;
   243    243       }
   244    244     }
................................................................................
   329    329     0                 /* xUnfetch */
   330    330   };
   331    331   
   332    332   /* 
   333    333   ** Open a journal file. 
   334    334   **
   335    335   ** The behaviour of the journal file depends on the value of parameter 
   336         -** nBuf. If nBuf is 0, then the journal file is always create and 
   337         -** accessed using the underlying VFS. If nBuf is less than zero, then
   338         -** all content is always stored in main-memory. Finally, if nBuf is a
          336  +** nSpill. If nSpill is 0, then the journal file is always create and 
          337  +** accessed using the underlying VFS. If nSpill is less than zero, then
          338  +** all content is always stored in main-memory. Finally, if nSpill is a
   339    339   ** positive value, then the journal file is initially created in-memory
   340    340   ** but may be flushed to disk later on. In this case the journal file is
   341         -** flushed to disk either when it grows larger than nBuf bytes in size,
          341  +** flushed to disk either when it grows larger than nSpill bytes in size,
   342    342   ** or when sqlite3JournalCreate() is called.
   343    343   */
   344    344   int sqlite3JournalOpen(
   345    345     sqlite3_vfs *pVfs,         /* The VFS to use for actual file I/O */
   346    346     const char *zName,         /* Name of the journal file */
   347    347     sqlite3_file *pJfd,        /* Preallocated, blank file handle */
   348    348     int flags,                 /* Opening flags */
   349         -  int nBuf                   /* Bytes buffered before opening the file */
          349  +  int nSpill                 /* Bytes buffered before opening the file */
   350    350   ){
   351    351     MemJournal *p = (MemJournal*)pJfd;
   352    352   
   353         -  /* Zero the file-handle object. If nBuf was passed zero, initialize
          353  +  /* Zero the file-handle object. If nSpill was passed zero, initialize
   354    354     ** it using the sqlite3OsOpen() function of the underlying VFS. In this
   355    355     ** case none of the code in this module is executed as a result of calls
   356    356     ** made on the journal file-handle.  */
   357    357     memset(p, 0, sizeof(MemJournal) + (pVfs ? pVfs->szOsFile : 0));
   358         -  if( nBuf==0 ){
          358  +  if( nSpill==0 ){
   359    359       return sqlite3OsOpen(pVfs, zName, pJfd, flags, 0);
   360    360     }
   361    361   
   362         -  if( nBuf>0 ){
   363         -    p->nChunkSize = nBuf;
          362  +  if( nSpill>0 ){
          363  +    p->nChunkSize = nSpill;
   364    364     }else{
   365    365       p->nChunkSize = 8 + MEMJOURNAL_DFLT_FILECHUNKSIZE - sizeof(FileChunk);
   366    366       assert( MEMJOURNAL_DFLT_FILECHUNKSIZE==fileChunkSize(p->nChunkSize) );
   367    367     }
   368    368   
   369    369     p->pMethod = (const sqlite3_io_methods*)&MemJournalMethods;
   370         -  p->nBuf = nBuf;
          370  +  p->nSpill = nSpill;
   371    371     p->flags = flags;
   372    372     p->zJournal = zName;
   373    373     p->pVfs = pVfs;
   374    374     return SQLITE_OK;
   375    375   }
   376    376   
   377    377   /*
................................................................................
   381    381     sqlite3JournalOpen(0, 0, pJfd, 0, -1);
   382    382   }
   383    383   
   384    384   #ifdef SQLITE_ENABLE_ATOMIC_WRITE
   385    385   /*
   386    386   ** If the argument p points to a MemJournal structure that is not an 
   387    387   ** in-memory-only journal file (i.e. is one that was opened with a +ve
   388         -** nBuf parameter), and the underlying file has not yet been created, 
          388  +** nSpill parameter), and the underlying file has not yet been created, 
   389    389   ** create it now.
   390    390   */
   391    391   int sqlite3JournalCreate(sqlite3_file *p){
   392    392     int rc = SQLITE_OK;
   393         -  if( p->pMethods==&MemJournalMethods && ((MemJournal*)p)->nBuf>0 ){
   394         -    rc = createFile((MemJournal*)p);
          393  +  if( p->pMethods==&MemJournalMethods && ((MemJournal*)p)->nSpill>0 ){
          394  +    rc = memjrnlCreateFile((MemJournal*)p);
   395    395     }
   396    396     return rc;
   397    397   }
   398    398   #endif
   399    399   
   400    400   /*
   401    401   ** The file-handle passed as the only argument is open on a journal file.

Changes to src/pager.c.

  4354   4354   */
  4355   4355   static int openSubJournal(Pager *pPager){
  4356   4356     int rc = SQLITE_OK;
  4357   4357     if( !isOpen(pPager->sjfd) ){
  4358   4358       const int flags =  SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_READWRITE 
  4359   4359         | SQLITE_OPEN_CREATE | SQLITE_OPEN_EXCLUSIVE 
  4360   4360         | SQLITE_OPEN_DELETEONCLOSE;
  4361         -    int nBuf = 64*1024;
         4361  +    int nStmtSpill = sqlite3Config.nStmtSpill;
  4362   4362       if( pPager->journalMode==PAGER_JOURNALMODE_MEMORY || pPager->subjInMemory ){
  4363         -      nBuf = -1;
         4363  +      nStmtSpill = -1;
  4364   4364       }
  4365         -    rc = sqlite3JournalOpen(pPager->pVfs, 0, pPager->sjfd, flags, nBuf);
         4365  +    rc = sqlite3JournalOpen(pPager->pVfs, 0, pPager->sjfd, flags, nStmtSpill);
  4366   4366     }
  4367   4367     return rc;
  4368   4368   }
  4369   4369   
  4370   4370   /*
  4371   4371   ** Append a record of the current state of page pPg to the sub-journal. 
  4372   4372   **

Changes to src/sqlite.h.in.

  1816   1816   ** is an unsigned integer and sets the "Minimum PMA Size" for the multithreaded
  1817   1817   ** sorter to that integer.  The default minimum PMA Size is set by the
  1818   1818   ** [SQLITE_SORTER_PMASZ] compile-time option.  New threads are launched
  1819   1819   ** to help with sort operations when multithreaded sorting
  1820   1820   ** is enabled (using the [PRAGMA threads] command) and the amount of content
  1821   1821   ** to be sorted exceeds the page size times the minimum of the
  1822   1822   ** [PRAGMA cache_size] setting and this value.
         1823  +**
         1824  +** [[SQLITE_CONFIG_STMTJRNL_SPILL]]
         1825  +** <dt>SQLITE_CONFIG_STMTJRNL_SPILL
         1826  +** <dd>^The SQLITE_CONFIG_STMTJRNL_SPILL option takes a single parameter which
         1827  +** becomes the [statement journal] spill-to-disk threshold.  
         1828  +** [Statement journals] are held in memory until their size (in bytes)
         1829  +** exceeds this threshold, at which point they are written to disk.
         1830  +** Or if the threshold is -1, statement journals are always held
         1831  +** exclusively in memory.
         1832  +** Since many statement journals never become large, setting the spill
         1833  +** threshold to a value such as 64KiB can greatly reduce the amount of
         1834  +** I/O required to support statement rollback.
         1835  +** The default value for this setting is controlled by the
         1836  +** [SQLITE_STMTJRNL_SPILL] compile-time option.
  1823   1837   ** </dl>
  1824   1838   */
  1825   1839   #define SQLITE_CONFIG_SINGLETHREAD  1  /* nil */
  1826   1840   #define SQLITE_CONFIG_MULTITHREAD   2  /* nil */
  1827   1841   #define SQLITE_CONFIG_SERIALIZED    3  /* nil */
  1828   1842   #define SQLITE_CONFIG_MALLOC        4  /* sqlite3_mem_methods* */
  1829   1843   #define SQLITE_CONFIG_GETMALLOC     5  /* sqlite3_mem_methods* */
................................................................................
  1843   1857   #define SQLITE_CONFIG_GETPCACHE2   19  /* sqlite3_pcache_methods2* */
  1844   1858   #define SQLITE_CONFIG_COVERING_INDEX_SCAN 20  /* int */
  1845   1859   #define SQLITE_CONFIG_SQLLOG       21  /* xSqllog, void* */
  1846   1860   #define SQLITE_CONFIG_MMAP_SIZE    22  /* sqlite3_int64, sqlite3_int64 */
  1847   1861   #define SQLITE_CONFIG_WIN32_HEAPSIZE      23  /* int nByte */
  1848   1862   #define SQLITE_CONFIG_PCACHE_HDRSZ        24  /* int *psz */
  1849   1863   #define SQLITE_CONFIG_PMASZ               25  /* unsigned int szPma */
         1864  +#define SQLITE_CONFIG_STMTJRNL_SPILL      26  /* int nByte */
  1850   1865   
  1851   1866   /*
  1852   1867   ** CAPI3REF: Database Connection Configuration Options
  1853   1868   **
  1854   1869   ** These constants are the available integer configuration options that
  1855   1870   ** can be passed as the second argument to the [sqlite3_db_config()] interface.
  1856   1871   **

Changes to src/sqliteInt.h.

  3049   3049     int bFullMutex;                   /* True to enable full mutexing */
  3050   3050     int bOpenUri;                     /* True to interpret filenames as URIs */
  3051   3051     int bUseCis;                      /* Use covering indices for full-scans */
  3052   3052     int mxStrlen;                     /* Maximum string length */
  3053   3053     int neverCorrupt;                 /* Database is always well-formed */
  3054   3054     int szLookaside;                  /* Default lookaside buffer size */
  3055   3055     int nLookaside;                   /* Default lookaside buffer count */
         3056  +  int nStmtSpill;                   /* Stmt-journal spill-to-disk threshold */
  3056   3057     sqlite3_mem_methods m;            /* Low-level memory allocation interface */
  3057   3058     sqlite3_mutex_methods mutex;      /* Low-level mutex interface */
  3058   3059     sqlite3_pcache_methods2 pcache2;  /* Low-level page-cache interface */
  3059   3060     void *pHeap;                      /* Heap storage space */
  3060   3061     int nHeap;                        /* Size of pHeap[] */
  3061   3062     int mnReq, mxReq;                 /* Min and max heap requests sizes */
  3062   3063     sqlite3_int64 szMmap;             /* mmap() space per open file */
................................................................................
  3693   3694   int sqlite3Utf8CharLen(const char *pData, int nByte);
  3694   3695   u32 sqlite3Utf8Read(const u8**);
  3695   3696   LogEst sqlite3LogEst(u64);
  3696   3697   LogEst sqlite3LogEstAdd(LogEst,LogEst);
  3697   3698   #ifndef SQLITE_OMIT_VIRTUALTABLE
  3698   3699   LogEst sqlite3LogEstFromDouble(double);
  3699   3700   #endif
  3700         -#if defined(SQLITE_ENABLE_STMT_SCANSTAT) || \
         3701  +#if defined(SQLITE_ENABLE_STMT_SCANSTATUS) || \
  3701   3702       defined(SQLITE_ENABLE_STAT3_OR_STAT4) || \
  3702   3703       defined(SQLITE_EXPLAIN_ESTIMATED_ROWS)
  3703   3704   u64 sqlite3LogEstToInt(LogEst);
  3704   3705   #endif
  3705   3706   
  3706   3707   /*
  3707   3708   ** Routines to read and write variable-length integers.  These used to

Changes to src/test_bestindex.c.

   111    111   };
   112    112   
   113    113   /* A tcl cursor object */
   114    114   struct tcl_cursor {
   115    115     sqlite3_vtab_cursor base;
   116    116     sqlite3_stmt *pStmt;            /* Read data from here */
   117    117   };
          118  +
          119  +/*
          120  +** Dequote string z in place.
          121  +*/
          122  +static void tclDequote(char *z){
          123  +  char q = z[0];
          124  +
          125  +  /* Set stack variable q to the close-quote character */
          126  +  if( q=='[' || q=='\'' || q=='"' || q=='`' ){
          127  +    int iIn = 1;
          128  +    int iOut = 0;
          129  +    if( q=='[' ) q = ']';  
          130  +
          131  +    while( ALWAYS(z[iIn]) ){
          132  +      if( z[iIn]==q ){
          133  +        if( z[iIn+1]!=q ){
          134  +          /* Character iIn was the close quote. */
          135  +          iIn++;
          136  +          break;
          137  +        }else{
          138  +          /* Character iIn and iIn+1 form an escaped quote character. Skip
          139  +          ** the input cursor past both and copy a single quote character 
          140  +          ** to the output buffer. */
          141  +          iIn += 2;
          142  +          z[iOut++] = q;
          143  +        }
          144  +      }else{
          145  +        z[iOut++] = z[iIn++];
          146  +      }
          147  +    }
          148  +
          149  +    z[iOut] = '\0';
          150  +  }
          151  +}
   118    152   
   119    153   /*
   120    154   ** This function is the implementation of both the xConnect and xCreate
   121    155   ** methods of the fs virtual table.
   122    156   **
   123    157   ** The argv[] array contains the following:
   124    158   **
................................................................................
   131    165     sqlite3 *db,
   132    166     void *pAux,
   133    167     int argc, const char *const*argv,
   134    168     sqlite3_vtab **ppVtab,
   135    169     char **pzErr
   136    170   ){
   137    171     Tcl_Interp *interp = (Tcl_Interp*)pAux;
   138         -  tcl_vtab *pTab;
   139         -  const char *zCmd;
          172  +  tcl_vtab *pTab = 0;
          173  +  char *zCmd = 0;
   140    174     Tcl_Obj *pScript = 0;
   141         -  int rc;
          175  +  int rc = SQLITE_OK;
   142    176   
   143    177     if( argc!=4 ){
   144    178       *pzErr = sqlite3_mprintf("wrong number of arguments");
   145    179       return SQLITE_ERROR;
   146    180     }
   147         -  zCmd = argv[3];
   148    181   
          182  +  zCmd = sqlite3_malloc(strlen(argv[3])+1);
   149    183     pTab = (tcl_vtab*)sqlite3_malloc(sizeof(tcl_vtab));
   150         -  if( pTab==0 ) return SQLITE_NOMEM;
   151         -  memset(pTab, 0, sizeof(tcl_vtab));
   152         -
   153         -  pTab->pCmd = Tcl_NewStringObj(zCmd, -1);
   154         -  pTab->interp = interp;
   155         -  pTab->db = db;
   156         -  Tcl_IncrRefCount(pTab->pCmd);
   157         -
   158         -  pScript = Tcl_DuplicateObj(pTab->pCmd);
   159         -  Tcl_IncrRefCount(pScript);
   160         -  Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xConnect", -1));
   161         -
   162         -  rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL);
   163         -  if( rc!=TCL_OK ){
   164         -    *pzErr = sqlite3_mprintf("%s", Tcl_GetStringResult(interp));
   165         -    rc = SQLITE_ERROR;
          184  +  if( zCmd && pTab ){
          185  +    memcpy(zCmd, argv[3], strlen(argv[3])+1);
          186  +    tclDequote(zCmd);
          187  +    memset(pTab, 0, sizeof(tcl_vtab));
          188  +
          189  +    pTab->pCmd = Tcl_NewStringObj(zCmd, -1);
          190  +    pTab->interp = interp;
          191  +    pTab->db = db;
          192  +    Tcl_IncrRefCount(pTab->pCmd);
          193  +
          194  +    pScript = Tcl_DuplicateObj(pTab->pCmd);
          195  +    Tcl_IncrRefCount(pScript);
          196  +    Tcl_ListObjAppendElement(interp, pScript, Tcl_NewStringObj("xConnect", -1));
          197  +
          198  +    rc = Tcl_EvalObjEx(interp, pScript, TCL_EVAL_GLOBAL);
          199  +    if( rc!=TCL_OK ){
          200  +      *pzErr = sqlite3_mprintf("%s", Tcl_GetStringResult(interp));
          201  +      rc = SQLITE_ERROR;
          202  +    }else{
          203  +      rc = sqlite3_declare_vtab(db, Tcl_GetStringResult(interp));
          204  +    }
          205  +
          206  +    if( rc!=SQLITE_OK ){
          207  +      sqlite3_free(pTab);
          208  +      pTab = 0;
          209  +    }
   166    210     }else{
   167         -    rc = sqlite3_declare_vtab(db, Tcl_GetStringResult(interp));
          211  +    rc = SQLITE_NOMEM;
   168    212     }
   169    213   
   170         -  if( rc!=SQLITE_OK ){
   171         -    sqlite3_free(pTab);
   172         -    pTab = 0;
   173         -  }
   174         -
          214  +  sqlite3_free(zCmd);
   175    215     *ppVtab = &pTab->base;
   176    216     return rc;
   177    217   }
   178    218   
   179    219   /* The xDisconnect and xDestroy methods are also the same */
   180    220   static int tclDisconnect(sqlite3_vtab *pVtab){
   181    221     tcl_vtab *pTab = (tcl_vtab*)pVtab;
................................................................................
   450    490           }else
   451    491           if( sqlite3_stricmp("idxstr", zCmd)==0 ){
   452    492             sqlite3_free(pIdxInfo->idxStr);
   453    493             pIdxInfo->idxStr = sqlite3_mprintf("%s", Tcl_GetString(p));
   454    494             pIdxInfo->needToFreeIdxStr = 1;
   455    495           }else
   456    496           if( sqlite3_stricmp("rows", zCmd)==0 ){
   457         -          rc = Tcl_GetWideIntFromObj(interp, p, &pIdxInfo->estimatedRows);
          497  +          Tcl_WideInt x = 0;
          498  +          rc = Tcl_GetWideIntFromObj(interp, p, &x);
          499  +          pIdxInfo->estimatedRows = (tRowcnt)x;
   458    500           }else
   459    501           if( sqlite3_stricmp("use", zCmd)==0 
   460    502            || sqlite3_stricmp("omit", zCmd)==0 
   461    503           ){
   462    504             int iCons;
   463    505             rc = Tcl_GetIntFromObj(interp, p, &iCons);
   464    506             if( rc==SQLITE_OK ){

Changes to src/util.c.

  1089   1089   
  1090   1090   /*
  1091   1091   ** Return the number of bytes that will be needed to store the given
  1092   1092   ** 64-bit integer.
  1093   1093   */
  1094   1094   int sqlite3VarintLen(u64 v){
  1095   1095     int i;
  1096         -  for(i=1; (v >>= 7)!=0; i++){ assert( i<9 ); }
         1096  +  for(i=1; (v >>= 7)!=0; i++){ assert( i<10 ); }
  1097   1097     return i;
  1098   1098   }
  1099   1099   
  1100   1100   
  1101   1101   /*
  1102   1102   ** Read or write a four-byte big-endian integer value.
  1103   1103   */
................................................................................
  1404   1404     if( x<=2000000000 ) return sqlite3LogEst((u64)x);
  1405   1405     memcpy(&a, &x, 8);
  1406   1406     e = (a>>52) - 1022;
  1407   1407     return e*10;
  1408   1408   }
  1409   1409   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  1410   1410   
  1411         -#if defined(SQLITE_ENABLE_STMT_SCANSTAT) || \
         1411  +#if defined(SQLITE_ENABLE_STMT_SCANSTATUS) || \
  1412   1412       defined(SQLITE_ENABLE_STAT3_OR_STAT4) || \
  1413   1413       defined(SQLITE_EXPLAIN_ESTIMATED_ROWS)
  1414   1414   /*
  1415   1415   ** Convert a LogEst into an integer.
  1416   1416   **
  1417   1417   ** Note that this routine is only used when one or more of various
  1418   1418   ** non-standard compile-time options is enabled.

Changes to src/where.c.

  2744   2744       ** considered. */
  2745   2745       if( pSrc->pIBIndex ) break;
  2746   2746     }
  2747   2747     return rc;
  2748   2748   }
  2749   2749   
  2750   2750   #ifndef SQLITE_OMIT_VIRTUALTABLE
         2751  +
         2752  +/*
         2753  +** Argument pIdxInfo is already populated with all constraints that may
         2754  +** be used by the virtual table identified by pBuilder->pNew->iTab. This
         2755  +** function marks a subset of those constraints usable, invokes the
         2756  +** xBestIndex method and adds the returned plan to pBuilder.
         2757  +**
         2758  +** A constraint is marked usable if:
         2759  +**
         2760  +**   * Argument mUsable indicates that its prerequisites are available, and
         2761  +**
         2762  +**   * It is not one of the operators specified in the mExclude mask passed
         2763  +**     as the fourth argument (which in practice is either WO_IN or 0).
         2764  +**
         2765  +** Argument mExtra is a mask of tables that must be scanned before the
         2766  +** virtual table in question. These are added to the plans prerequisites
         2767  +** before it is added to pBuilder.
         2768  +**
         2769  +** Output parameter *pbIn is set to true if the plan added to pBuilder
         2770  +** uses one or more WO_IN terms, or false otherwise.
         2771  +*/
         2772  +static int whereLoopAddVirtualOne(
         2773  +  WhereLoopBuilder *pBuilder,
         2774  +  Bitmask mExtra,                 /* Mask of tables that must be used. */
         2775  +  Bitmask mUsable,                /* Mask of usable prereqs */
         2776  +  u16 mExclude,                   /* Exclude terms for this operator */
         2777  +  sqlite3_index_info *pIdxInfo,   /* Populated object for xBestIndex */
         2778  +  int *pbIn                       /* OUT: True if plan uses an IN(...) op */
         2779  +){
         2780  +  WhereClause *pWC = pBuilder->pWC;
         2781  +  struct sqlite3_index_constraint *pIdxCons;
         2782  +  struct sqlite3_index_constraint_usage *pUsage = pIdxInfo->aConstraintUsage;
         2783  +  int i;
         2784  +  int mxTerm;
         2785  +  int rc = SQLITE_OK;
         2786  +  WhereLoop *pNew = pBuilder->pNew;
         2787  +  Parse *pParse = pBuilder->pWInfo->pParse;
         2788  +  struct SrcList_item *pSrc = &pBuilder->pWInfo->pTabList->a[pNew->iTab];
         2789  +  int nConstraint = pIdxInfo->nConstraint;
         2790  +
         2791  +  assert( (mUsable & mExtra)==mExtra );
         2792  +  *pbIn = 0;
         2793  +  pNew->prereq = mExtra;
         2794  +
         2795  +  /* Set the usable flag on the subset of constraints identified by 
         2796  +  ** arguments mUsable and mExclude. */
         2797  +  pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
         2798  +  for(i=0; i<nConstraint; i++, pIdxCons++){
         2799  +    WhereTerm *pTerm = &pWC->a[pIdxCons->iTermOffset];
         2800  +    pIdxCons->usable = 0;
         2801  +    if( (pTerm->prereqRight & mUsable)==pTerm->prereqRight 
         2802  +     && (pTerm->eOperator & mExclude)==0
         2803  +    ){
         2804  +      pIdxCons->usable = 1;
         2805  +    }
         2806  +  }
         2807  +
         2808  +  /* Initialize the output fields of the sqlite3_index_info structure */
         2809  +  memset(pUsage, 0, sizeof(pUsage[0])*nConstraint);
         2810  +  if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
         2811  +  pIdxInfo->idxStr = 0;
         2812  +  pIdxInfo->idxNum = 0;
         2813  +  pIdxInfo->needToFreeIdxStr = 0;
         2814  +  pIdxInfo->orderByConsumed = 0;
         2815  +  pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
         2816  +  pIdxInfo->estimatedRows = 25;
         2817  +  pIdxInfo->idxFlags = 0;
         2818  +  pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed;
         2819  +
         2820  +  /* Invoke the virtual table xBestIndex() method */
         2821  +  rc = vtabBestIndex(pParse, pSrc->pTab, pIdxInfo);
         2822  +  if( rc ) return rc;
         2823  +
         2824  +  mxTerm = -1;
         2825  +  assert( pNew->nLSlot>=nConstraint );
         2826  +  for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
         2827  +  pNew->u.vtab.omitMask = 0;
         2828  +  pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
         2829  +  for(i=0; i<nConstraint; i++, pIdxCons++){
         2830  +    int iTerm;
         2831  +    if( (iTerm = pUsage[i].argvIndex - 1)>=0 ){
         2832  +      WhereTerm *pTerm;
         2833  +      int j = pIdxCons->iTermOffset;
         2834  +      if( iTerm>=nConstraint
         2835  +       || j<0
         2836  +       || j>=pWC->nTerm
         2837  +       || pNew->aLTerm[iTerm]!=0
         2838  +      ){
         2839  +        rc = SQLITE_ERROR;
         2840  +        sqlite3ErrorMsg(pParse,"%s.xBestIndex() malfunction",pSrc->pTab->zName);
         2841  +        return rc;
         2842  +      }
         2843  +      testcase( iTerm==nConstraint-1 );
         2844  +      testcase( j==0 );
         2845  +      testcase( j==pWC->nTerm-1 );
         2846  +      pTerm = &pWC->a[j];
         2847  +      pNew->prereq |= pTerm->prereqRight;
         2848  +      assert( iTerm<pNew->nLSlot );
         2849  +      pNew->aLTerm[iTerm] = pTerm;
         2850  +      if( iTerm>mxTerm ) mxTerm = iTerm;
         2851  +      testcase( iTerm==15 );
         2852  +      testcase( iTerm==16 );
         2853  +      if( iTerm<16 && pUsage[i].omit ) pNew->u.vtab.omitMask |= 1<<iTerm;
         2854  +      if( (pTerm->eOperator & WO_IN)!=0 ){
         2855  +        /* A virtual table that is constrained by an IN clause may not
         2856  +        ** consume the ORDER BY clause because (1) the order of IN terms
         2857  +        ** is not necessarily related to the order of output terms and
         2858  +        ** (2) Multiple outputs from a single IN value will not merge
         2859  +        ** together.  */
         2860  +        pIdxInfo->orderByConsumed = 0;
         2861  +        pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE;
         2862  +        *pbIn = 1;
         2863  +      }
         2864  +    }
         2865  +  }
         2866  +
         2867  +  pNew->nLTerm = mxTerm+1;
         2868  +  assert( pNew->nLTerm<=pNew->nLSlot );
         2869  +  pNew->u.vtab.idxNum = pIdxInfo->idxNum;
         2870  +  pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
         2871  +  pIdxInfo->needToFreeIdxStr = 0;
         2872  +  pNew->u.vtab.idxStr = pIdxInfo->idxStr;
         2873  +  pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ?
         2874  +      pIdxInfo->nOrderBy : 0);
         2875  +  pNew->rSetup = 0;
         2876  +  pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost);
         2877  +  pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows);
         2878  +
         2879  +  /* Set the WHERE_ONEROW flag if the xBestIndex() method indicated
         2880  +  ** that the scan will visit at most one row. Clear it otherwise. */
         2881  +  if( pIdxInfo->idxFlags & SQLITE_INDEX_SCAN_UNIQUE ){
         2882  +    pNew->wsFlags |= WHERE_ONEROW;
         2883  +  }else{
         2884  +    pNew->wsFlags &= ~WHERE_ONEROW;
         2885  +  }
         2886  +  whereLoopInsert(pBuilder, pNew);
         2887  +  if( pNew->u.vtab.needFree ){
         2888  +    sqlite3_free(pNew->u.vtab.idxStr);
         2889  +    pNew->u.vtab.needFree = 0;
         2890  +  }
         2891  +
         2892  +  return SQLITE_OK;
         2893  +}
         2894  +
         2895  +
  2751   2896   /*
  2752   2897   ** Add all WhereLoop objects for a table of the join identified by
  2753   2898   ** pBuilder->pNew->iTab.  That table is guaranteed to be a virtual table.
  2754   2899   **
  2755   2900   ** If there are no LEFT or CROSS JOIN joins in the query, both mExtra and
  2756   2901   ** mUnusable are set to 0. Otherwise, mExtra is a mask of all FROM clause
  2757   2902   ** entries that occur before the virtual table in the FROM clause and are
................................................................................
  2774   2919   ** mUnusable should always be configured as "not-usable" for xBestIndex.
  2775   2920   */
  2776   2921   static int whereLoopAddVirtual(
  2777   2922     WhereLoopBuilder *pBuilder,  /* WHERE clause information */
  2778   2923     Bitmask mExtra,              /* Tables that must be scanned before this one */
  2779   2924     Bitmask mUnusable            /* Tables that must be scanned after this one */
  2780   2925   ){
         2926  +  int rc = SQLITE_OK;          /* Return code */
  2781   2927     WhereInfo *pWInfo;           /* WHERE analysis context */
  2782   2928     Parse *pParse;               /* The parsing context */
  2783   2929     WhereClause *pWC;            /* The WHERE clause */
  2784   2930     struct SrcList_item *pSrc;   /* The FROM clause term to search */
  2785         -  Table *pTab;
  2786         -  sqlite3 *db;
  2787         -  sqlite3_index_info *pIdxInfo;
  2788         -  struct sqlite3_index_constraint *pIdxCons;
  2789         -  struct sqlite3_index_constraint_usage *pUsage;
  2790         -  WhereTerm *pTerm;
  2791         -  int i, j;
  2792         -  int iTerm, mxTerm;
  2793         -  int nConstraint;
  2794         -  int seenIn = 0;              /* True if an IN operator is seen */
  2795         -  int seenVar = 0;             /* True if a non-constant constraint is seen */
  2796         -  int iPhase;                  /* 0: const w/o IN, 1: const, 2: no IN,  2: IN */
         2931  +  sqlite3_index_info *p;       /* Object to pass to xBestIndex() */
         2932  +  int nConstraint;             /* Number of constraints in p */
         2933  +  int bIn;                     /* True if plan uses IN(...) operator */
  2797   2934     WhereLoop *pNew;
  2798         -  int rc = SQLITE_OK;
         2935  +  Bitmask mBest;               /* Tables used by best possible plan */
  2799   2936   
  2800   2937     assert( (mExtra & mUnusable)==0 );
  2801   2938     pWInfo = pBuilder->pWInfo;
  2802   2939     pParse = pWInfo->pParse;
  2803         -  db = pParse->db;
  2804   2940     pWC = pBuilder->pWC;
  2805   2941     pNew = pBuilder->pNew;
  2806   2942     pSrc = &pWInfo->pTabList->a[pNew->iTab];
  2807         -  pTab = pSrc->pTab;
  2808         -  assert( IsVirtual(pTab) );
  2809         -  pIdxInfo = allocateIndexInfo(pParse, pWC, mUnusable, pSrc,pBuilder->pOrderBy);
  2810         -  if( pIdxInfo==0 ) return SQLITE_NOMEM_BKPT;
  2811         -  pNew->prereq = 0;
         2943  +  assert( IsVirtual(pSrc->pTab) );
         2944  +  p = allocateIndexInfo(pParse, pWC, mUnusable, pSrc,pBuilder->pOrderBy);
         2945  +  if( p==0 ) return SQLITE_NOMEM_BKPT;
  2812   2946     pNew->rSetup = 0;
  2813   2947     pNew->wsFlags = WHERE_VIRTUALTABLE;
  2814   2948     pNew->nLTerm = 0;
  2815   2949     pNew->u.vtab.needFree = 0;
  2816         -  pUsage = pIdxInfo->aConstraintUsage;
  2817         -  nConstraint = pIdxInfo->nConstraint;
  2818         -  if( whereLoopResize(db, pNew, nConstraint) ){
  2819         -    sqlite3DbFree(db, pIdxInfo);
         2950  +  nConstraint = p->nConstraint;
         2951  +  if( whereLoopResize(pParse->db, pNew, nConstraint) ){
         2952  +    sqlite3DbFree(pParse->db, p);
  2820   2953       return SQLITE_NOMEM_BKPT;
  2821   2954     }
  2822   2955   
  2823         -  for(iPhase=0; iPhase<=3; iPhase++){
  2824         -    if( !seenIn && (iPhase&1)!=0 ){
  2825         -      iPhase++;
  2826         -      if( iPhase>3 ) break;
  2827         -    }
  2828         -    if( !seenVar && iPhase>1 ) break;
  2829         -    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  2830         -    for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
  2831         -      j = pIdxCons->iTermOffset;
  2832         -      pTerm = &pWC->a[j];
  2833         -      switch( iPhase ){
  2834         -        case 0:    /* Constants without IN operator */
  2835         -          pIdxCons->usable = 0;
  2836         -          if( (pTerm->eOperator & WO_IN)!=0 ){
  2837         -            seenIn = 1;
  2838         -          }
  2839         -          if( (pTerm->prereqRight & ~mExtra)!=0 ){
  2840         -            seenVar = 1;
  2841         -          }else if( (pTerm->eOperator & WO_IN)==0 ){
  2842         -            pIdxCons->usable = 1;
  2843         -          }
  2844         -          break;
  2845         -        case 1:    /* Constants with IN operators */
  2846         -          assert( seenIn );
  2847         -          pIdxCons->usable = (pTerm->prereqRight & ~mExtra)==0;
  2848         -          break;
  2849         -        case 2:    /* Variables without IN */
  2850         -          assert( seenVar );
  2851         -          pIdxCons->usable = (pTerm->eOperator & WO_IN)==0;
  2852         -          break;
  2853         -        default:   /* Variables with IN */
  2854         -          assert( seenVar && seenIn );
  2855         -          pIdxCons->usable = 1;
  2856         -          break;
  2857         -      }
  2858         -    }
  2859         -    memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint);
  2860         -    if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
  2861         -    pIdxInfo->idxStr = 0;
  2862         -    pIdxInfo->idxNum = 0;
  2863         -    pIdxInfo->needToFreeIdxStr = 0;
  2864         -    pIdxInfo->orderByConsumed = 0;
  2865         -    pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
  2866         -    pIdxInfo->estimatedRows = 25;
  2867         -    pIdxInfo->idxFlags = 0;
  2868         -    pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed;
  2869         -    rc = vtabBestIndex(pParse, pTab, pIdxInfo);
  2870         -    if( rc ) goto whereLoopAddVtab_exit;
  2871         -    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  2872         -    pNew->prereq = mExtra;
  2873         -    mxTerm = -1;
  2874         -    assert( pNew->nLSlot>=nConstraint );
  2875         -    for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
  2876         -    pNew->u.vtab.omitMask = 0;
  2877         -    for(i=0; i<nConstraint; i++, pIdxCons++){
  2878         -      if( (iTerm = pUsage[i].argvIndex - 1)>=0 ){
  2879         -        j = pIdxCons->iTermOffset;
  2880         -        if( iTerm>=nConstraint
  2881         -         || j<0
  2882         -         || j>=pWC->nTerm
  2883         -         || pNew->aLTerm[iTerm]!=0
  2884         -        ){
  2885         -          rc = SQLITE_ERROR;
  2886         -          sqlite3ErrorMsg(pParse, "%s.xBestIndex() malfunction", pTab->zName);
  2887         -          goto whereLoopAddVtab_exit;
  2888         -        }
  2889         -        testcase( iTerm==nConstraint-1 );
  2890         -        testcase( j==0 );
  2891         -        testcase( j==pWC->nTerm-1 );
  2892         -        pTerm = &pWC->a[j];
  2893         -        pNew->prereq |= pTerm->prereqRight;
  2894         -        assert( iTerm<pNew->nLSlot );
  2895         -        pNew->aLTerm[iTerm] = pTerm;
  2896         -        if( iTerm>mxTerm ) mxTerm = iTerm;
  2897         -        testcase( iTerm==15 );
  2898         -        testcase( iTerm==16 );
  2899         -        if( iTerm<16 && pUsage[i].omit ) pNew->u.vtab.omitMask |= 1<<iTerm;
  2900         -        if( (pTerm->eOperator & WO_IN)!=0 ){
  2901         -          /* A virtual table that is constrained by an IN clause may not
  2902         -          ** consume the ORDER BY clause because (1) the order of IN terms
  2903         -          ** is not necessarily related to the order of output terms and
  2904         -          ** (2) Multiple outputs from a single IN value will not merge
  2905         -          ** together.  */
  2906         -          pIdxInfo->orderByConsumed = 0;
  2907         -          pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE;
  2908         -        }
  2909         -      }
  2910         -    }
  2911         -    if( i>=nConstraint ){
  2912         -      pNew->nLTerm = mxTerm+1;
  2913         -      assert( pNew->nLTerm<=pNew->nLSlot );
  2914         -      pNew->u.vtab.idxNum = pIdxInfo->idxNum;
  2915         -      pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
  2916         -      pIdxInfo->needToFreeIdxStr = 0;
  2917         -      pNew->u.vtab.idxStr = pIdxInfo->idxStr;
  2918         -      pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ?
  2919         -                                      pIdxInfo->nOrderBy : 0);
  2920         -      pNew->rSetup = 0;
  2921         -      pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost);
  2922         -      pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows);
  2923         -
  2924         -      /* Set the WHERE_ONEROW flag if the xBestIndex() method indicated
  2925         -      ** that the scan will visit at most one row. Clear it otherwise. */
  2926         -      if( pIdxInfo->idxFlags & SQLITE_INDEX_SCAN_UNIQUE ){
  2927         -        pNew->wsFlags |= WHERE_ONEROW;
  2928         -      }else{
  2929         -        pNew->wsFlags &= ~WHERE_ONEROW;
  2930         -      }
  2931         -      whereLoopInsert(pBuilder, pNew);
  2932         -      if( pNew->u.vtab.needFree ){
  2933         -        sqlite3_free(pNew->u.vtab.idxStr);
  2934         -        pNew->u.vtab.needFree = 0;
  2935         -      }
  2936         -    }
  2937         -  }  
  2938         -
  2939         -whereLoopAddVtab_exit:
  2940         -  if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
  2941         -  sqlite3DbFree(db, pIdxInfo);
         2956  +  /* First call xBestIndex() with all constraints usable. */
         2957  +  rc = whereLoopAddVirtualOne(pBuilder, mExtra, (Bitmask)(-1), 0, p, &bIn);
         2958  +  mBest = pNew->prereq & ~mExtra;
         2959  +
         2960  +  /* If the call to xBestIndex() with all terms enabled produced a plan
         2961  +  ** that does not require any source tables, there is no point in making
         2962  +  ** any further calls - if the xBestIndex() method is sane they will all
         2963  +  ** return the same plan anyway.
         2964  +  */
         2965  +  if( mBest ){
         2966  +    int seenZero = 0;             /* True if a plan with no prereqs seen */
         2967  +    int seenZeroNoIN = 0;         /* Plan with no prereqs and no IN(...) seen */
         2968  +    Bitmask mPrev = 0;
         2969  +    Bitmask mBestNoIn = 0;
         2970  +
         2971  +    /* If the plan produced by the earlier call uses an IN(...) term, call
         2972  +    ** xBestIndex again, this time with IN(...) terms disabled. */
         2973  +    if( rc==SQLITE_OK && bIn ){
         2974  +      rc = whereLoopAddVirtualOne(pBuilder, mExtra, (Bitmask)-1, WO_IN, p,&bIn);
         2975  +      mBestNoIn = pNew->prereq & ~mExtra;
         2976  +      if( mBestNoIn==0 ){
         2977  +        seenZero = 1;
         2978  +        if( bIn==0 ) seenZeroNoIN = 1;
         2979  +      }
         2980  +    }
         2981  +
         2982  +    /* Call xBestIndex once for each distinct value of (prereqRight & ~mExtra) 
         2983  +    ** in the set of terms that apply to the current virtual table.  */
         2984  +    while( rc==SQLITE_OK ){
         2985  +      int i;
         2986  +      Bitmask mNext = (Bitmask)(-1);
         2987  +      assert( mNext>0 );
         2988  +      for(i=0; i<nConstraint; i++){
         2989  +        Bitmask mThis = (
         2990  +            pWC->a[p->aConstraint[i].iTermOffset].prereqRight & ~mExtra
         2991  +        );
         2992  +        if( mThis>mPrev && mThis<mNext ) mNext = mThis;
         2993  +      }
         2994  +      mPrev = mNext;
         2995  +      if( mNext==(Bitmask)(-1) ) break;
         2996  +      if( mNext==mBest || mNext==mBestNoIn ) continue;
         2997  +      rc = whereLoopAddVirtualOne(pBuilder, mExtra, mNext|mExtra, 0, p, &bIn);
         2998  +      if( pNew->prereq==mExtra ){
         2999  +        seenZero = 1;
         3000  +        if( bIn==0 ) seenZeroNoIN = 1;
         3001  +      }
         3002  +    }
         3003  +
         3004  +    /* If the calls to xBestIndex() in the above loop did not find a plan
         3005  +    ** that requires no source tables at all (i.e. one guaranteed to be
         3006  +    ** usable), make a call here with all source tables disabled */
         3007  +    if( rc==SQLITE_OK && seenZero==0 ){
         3008  +      rc = whereLoopAddVirtualOne(pBuilder, mExtra, mExtra, 0, p, &bIn);
         3009  +      if( bIn==0 ) seenZeroNoIN = 1;
         3010  +    }
         3011  +
         3012  +    /* If the calls to xBestIndex() have so far failed to find a plan
         3013  +    ** that requires no source tables at all and does not use an IN(...)
         3014  +    ** operator, make a final call to obtain one here.  */
         3015  +    if( rc==SQLITE_OK && seenZeroNoIN==0 ){
         3016  +      rc = whereLoopAddVirtualOne(pBuilder, mExtra, mExtra, WO_IN, p, &bIn);
         3017  +    }
         3018  +  }
         3019  +
         3020  +  if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr);
         3021  +  sqlite3DbFree(pParse->db, p);
  2942   3022     return rc;
  2943   3023   }
  2944   3024   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  2945   3025   
  2946   3026   /*
  2947   3027   ** Add WhereLoop entries to handle OR terms.  This works for either
  2948   3028   ** btrees or virtual tables.

Added test/bestindex2.test.

            1  +# 2016 March 3
            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  +set testdir [file dirname $argv0]
           13  +source $testdir/tester.tcl
           14  +set testprefix bestindex2
           15  +
           16  +
           17  +#-------------------------------------------------------------------------
           18  +# Virtual table callback for table named $tbl, with the columns specified
           19  +# by list argument $cols. e.g. if the function is invoked as:
           20  +#
           21  +#   vtab_cmd t1 {a b c} ...
           22  +#
           23  +# The table created is:
           24  +#
           25  +#      "CREATE TABLE t1 (a, b, c)"
           26  +#
           27  +# The tables xBestIndex method behaves as if all possible combinations of
           28  +# "=" constraints (but no others) may be optimized. The cost of a full table
           29  +# scan is:
           30  +#
           31  +#      "WHERE 1"                "cost 1000000 rows 1000000"
           32  +#
           33  +# If one or more "=" constraints are in use, the cost and estimated number
           34  +# of rows returned are both is (11 - nCons)*1000, where nCons is the number
           35  +# of constraints used. e.g.
           36  +#
           37  +#   "WHERE a=? AND b=?"    ->   "cost  900 rows  900"
           38  +#   "WHERE c=? AND b<?"    ->   "cost 1000 rows 1000"
           39  +#  
           40  +proc vtab_cmd {tbl cols method args} {
           41  +  switch -- $method {
           42  +    xConnect {
           43  +      return "CREATE TABLE $tbl ([join $cols ,])"
           44  +    }
           45  +    xBestIndex {
           46  +      foreach {clist orderby mask} $args {}
           47  +
           48  +      set cons [list]
           49  +      set used [list]
           50  +
           51  +      for {set i 0} {$i < [llength $clist]} {incr i} {
           52  +        array unset C
           53  +        array set C [lindex $clist $i]
           54  +        if {$C(op)=="eq" && $C(usable) && [lsearch $cons $C(column)]<0} {
           55  +          lappend used use $i
           56  +          lappend cons $C(column)
           57  +        }
           58  +      }
           59  +
           60  +      set nCons [llength $cons]
           61  +      if {$nCons==0} {
           62  +        return "cost 1000000 rows 1000000"
           63  +      } else {
           64  +        set cost [expr (11-$nCons) * 1000]
           65  +        set ret [concat $used "cost $cost rows $cost"]
           66  +
           67  +        set txt [list]
           68  +        foreach c $cons { lappend txt "[lindex $cols $c]=?" }
           69  +        lappend ret idxstr "indexed([join $txt { AND }])"
           70  +
           71  +        return $ret
           72  +      }
           73  +    }
           74  +  }
           75  +  return ""
           76  +}
           77  +
           78  +register_tcl_module db
           79  +
           80  +do_execsql_test 1.0 {
           81  +  CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd t1 {a b}");
           82  +  CREATE VIRTUAL TABLE t2 USING tcl("vtab_cmd t2 {c d}");
           83  +  CREATE VIRTUAL TABLE t3 USING tcl("vtab_cmd t3 {e f}");
           84  +}
           85  +
           86  +do_eqp_test 1.1 {
           87  +  SELECT * FROM t1 WHERE a='abc'
           88  +} {
           89  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
           90  +}
           91  +do_eqp_test 1.2 {
           92  +  SELECT * FROM t1 WHERE a='abc' AND b='def'
           93  +} {
           94  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)}
           95  +}
           96  +do_eqp_test 1.3 {
           97  +  SELECT * FROM t1 WHERE a='abc' AND a='def'
           98  +} {
           99  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
          100  +}
          101  +do_eqp_test 1.4 {
          102  +  SELECT * FROM t1,t2 WHERE c=a
          103  +} {
          104  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
          105  +  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)}
          106  +}
          107  +
          108  +do_eqp_test 1.5 {
          109  +  SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d
          110  +} {
          111  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
          112  +  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
          113  +  0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
          114  +}
          115  +
          116  +do_eqp_test 1.6 {
          117  +  SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d
          118  +} {
          119  +  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
          120  +  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
          121  +  0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
          122  +}
          123  +
          124  +do_execsql_test 1.7.1 {
          125  +  CREATE TABLE x1(a, b);
          126  +}
          127  +do_eqp_test 1.7.2 {
          128  +  SELECT * FROM x1 CROSS JOIN t1, t2, t3 
          129  +    WHERE t1.a = t2.c AND t1.b = t3.e
          130  +} {
          131  +  0 0 0 {SCAN TABLE x1} 
          132  +  0 1 1 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:}
          133  +  0 2 2 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
          134  +  0 3 3 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
          135  +}
          136  +
          137  +finish_test
          138  +

Changes to tool/mksqlite3c.tcl.

   322    322      vdbemem.c
   323    323      vdbeaux.c
   324    324      vdbeapi.c
   325    325      vdbetrace.c
   326    326      vdbe.c
   327    327      vdbeblob.c
   328    328      vdbesort.c
   329         -   journal.c
   330    329      memjournal.c
   331    330   
   332    331      walker.c
   333    332      resolve.c
   334    333      expr.c
   335    334      alter.c
   336    335      analyze.c