/ Check-in [25102203]
Login

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

Overview
Comment:Merge latest trunk changes with this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 251022034219819a1dc356542770ff46e3147a080f072eb20af6106771dadd92
User & Date: dan 2018-06-07 20:35:28
Context
2018-06-08
11:45
Fixes to allow group_concat() to be used as a window function. check-in: 89bbc9ba user: dan tags: exp-window-functions
2018-06-07
20:35
Merge latest trunk changes with this branch. check-in: 25102203 user: dan tags: exp-window-functions
20:08
Add window functions lag() and lead(). check-in: ef342070 user: dan tags: exp-window-functions
18:13
The IN-early-out optimization: When doing a look-up on a multi-column index and an IN operator is used on a column other than the left-most column, then if no rows match against the first IN value, check to make sure there exist rows that match the columns to the right before continuing with the next IN value. check-in: 09fffbdf user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -3.24.0
            1  +3.25.0

Changes to configure.

     1      1   #! /bin/sh
     2      2   # Guess values for system-dependent variables and create Makefiles.
     3         -# Generated by GNU Autoconf 2.69 for sqlite 3.24.0.
            3  +# Generated by GNU Autoconf 2.69 for sqlite 3.25.0.
     4      4   #
     5      5   #
     6      6   # Copyright (C) 1992-1996, 1998-2012 Free Software Foundation, Inc.
     7      7   #
     8      8   #
     9      9   # This configure script is free software; the Free Software Foundation
    10     10   # gives unlimited permission to copy, distribute and modify it.
................................................................................
   722    722   subdirs=
   723    723   MFLAGS=
   724    724   MAKEFLAGS=
   725    725   
   726    726   # Identity of this package.
   727    727   PACKAGE_NAME='sqlite'
   728    728   PACKAGE_TARNAME='sqlite'
   729         -PACKAGE_VERSION='3.24.0'
   730         -PACKAGE_STRING='sqlite 3.24.0'
          729  +PACKAGE_VERSION='3.25.0'
          730  +PACKAGE_STRING='sqlite 3.25.0'
   731    731   PACKAGE_BUGREPORT=''
   732    732   PACKAGE_URL=''
   733    733   
   734    734   # Factoring default headers for most tests.
   735    735   ac_includes_default="\
   736    736   #include <stdio.h>
   737    737   #ifdef HAVE_SYS_TYPES_H
................................................................................
  1461   1461   #
  1462   1462   # Report the --help message.
  1463   1463   #
  1464   1464   if test "$ac_init_help" = "long"; then
  1465   1465     # Omit some internal or obsolete options to make the list less imposing.
  1466   1466     # This message is too long to be a string in the A/UX 3.1 sh.
  1467   1467     cat <<_ACEOF
  1468         -\`configure' configures sqlite 3.24.0 to adapt to many kinds of systems.
         1468  +\`configure' configures sqlite 3.25.0 to adapt to many kinds of systems.
  1469   1469   
  1470   1470   Usage: $0 [OPTION]... [VAR=VALUE]...
  1471   1471   
  1472   1472   To assign environment variables (e.g., CC, CFLAGS...), specify them as
  1473   1473   VAR=VALUE.  See below for descriptions of some of the useful variables.
  1474   1474   
  1475   1475   Defaults for the options are specified in brackets.
................................................................................
  1526   1526     --build=BUILD     configure for building on BUILD [guessed]
  1527   1527     --host=HOST       cross-compile to build programs to run on HOST [BUILD]
  1528   1528   _ACEOF
  1529   1529   fi
  1530   1530   
  1531   1531   if test -n "$ac_init_help"; then
  1532   1532     case $ac_init_help in
  1533         -     short | recursive ) echo "Configuration of sqlite 3.24.0:";;
         1533  +     short | recursive ) echo "Configuration of sqlite 3.25.0:";;
  1534   1534      esac
  1535   1535     cat <<\_ACEOF
  1536   1536   
  1537   1537   Optional Features:
  1538   1538     --disable-option-checking  ignore unrecognized --enable/--with options
  1539   1539     --disable-FEATURE       do not include FEATURE (same as --enable-FEATURE=no)
  1540   1540     --enable-FEATURE[=ARG]  include FEATURE [ARG=yes]
................................................................................
  1651   1651       cd "$ac_pwd" || { ac_status=$?; break; }
  1652   1652     done
  1653   1653   fi
  1654   1654   
  1655   1655   test -n "$ac_init_help" && exit $ac_status
  1656   1656   if $ac_init_version; then
  1657   1657     cat <<\_ACEOF
  1658         -sqlite configure 3.24.0
         1658  +sqlite configure 3.25.0
  1659   1659   generated by GNU Autoconf 2.69
  1660   1660   
  1661   1661   Copyright (C) 2012 Free Software Foundation, Inc.
  1662   1662   This configure script is free software; the Free Software Foundation
  1663   1663   gives unlimited permission to copy, distribute and modify it.
  1664   1664   _ACEOF
  1665   1665     exit
................................................................................
  2070   2070     eval $as_lineno_stack; ${as_lineno_stack:+:} unset as_lineno
  2071   2071   
  2072   2072   } # ac_fn_c_check_header_mongrel
  2073   2073   cat >config.log <<_ACEOF
  2074   2074   This file contains any messages produced by compilers while
  2075   2075   running configure, to aid debugging if configure makes a mistake.
  2076   2076   
  2077         -It was created by sqlite $as_me 3.24.0, which was
         2077  +It was created by sqlite $as_me 3.25.0, which was
  2078   2078   generated by GNU Autoconf 2.69.  Invocation command line was
  2079   2079   
  2080   2080     $ $0 $@
  2081   2081   
  2082   2082   _ACEOF
  2083   2083   exec 5>>config.log
  2084   2084   {
................................................................................
 12238  12238   test $as_write_fail = 0 && chmod +x $CONFIG_STATUS || ac_write_fail=1
 12239  12239   
 12240  12240   cat >>$CONFIG_STATUS <<\_ACEOF || ac_write_fail=1
 12241  12241   # Save the log message, to keep $0 and so on meaningful, and to
 12242  12242   # report actual input values of CONFIG_FILES etc. instead of their
 12243  12243   # values after options handling.
 12244  12244   ac_log="
 12245         -This file was extended by sqlite $as_me 3.24.0, which was
        12245  +This file was extended by sqlite $as_me 3.25.0, which was
 12246  12246   generated by GNU Autoconf 2.69.  Invocation command line was
 12247  12247   
 12248  12248     CONFIG_FILES    = $CONFIG_FILES
 12249  12249     CONFIG_HEADERS  = $CONFIG_HEADERS
 12250  12250     CONFIG_LINKS    = $CONFIG_LINKS
 12251  12251     CONFIG_COMMANDS = $CONFIG_COMMANDS
 12252  12252     $ $0 $@
................................................................................
 12304  12304   
 12305  12305   Report bugs to the package provider."
 12306  12306   
 12307  12307   _ACEOF
 12308  12308   cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1
 12309  12309   ac_cs_config="`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`"
 12310  12310   ac_cs_version="\\
 12311         -sqlite config.status 3.24.0
        12311  +sqlite config.status 3.25.0
 12312  12312   configured by $0, generated by GNU Autoconf 2.69,
 12313  12313     with options \\"\$ac_cs_config\\"
 12314  12314   
 12315  12315   Copyright (C) 2012 Free Software Foundation, Inc.
 12316  12316   This config.status script is free software; the Free Software Foundation
 12317  12317   gives unlimited permission to copy, distribute and modify it."
 12318  12318   

Changes to src/alter.c.

   138    138           zOutput = zOut;
   139    139           zInput = &z[n];
   140    140         }
   141    141         sqlite3DbFree(db, zParent);
   142    142       }
   143    143     }
   144    144   
   145         -  zResult = sqlite3MPrintf(db, "%s%s", (zOutput?zOutput:""), zInput), 
          145  +  zResult = sqlite3MPrintf(db, "%s%s", (zOutput?zOutput:""), zInput);
   146    146     sqlite3_result_text(context, zResult, -1, SQLITE_DYNAMIC);
   147    147     sqlite3DbFree(db, zOutput);
   148    148   }
   149    149   #endif
   150    150   
   151    151   #ifndef SQLITE_OMIT_TRIGGER
   152    152   /* This function is used by SQL generated to implement the

Changes to src/backup.c.

   378    378       }
   379    379   
   380    380       /* If there is no open read-transaction on the source database, open
   381    381       ** one now. If a transaction is opened here, then it will be closed
   382    382       ** before this function exits.
   383    383       */
   384    384       if( rc==SQLITE_OK && 0==sqlite3BtreeIsInReadTrans(p->pSrc) ){
   385         -      rc = sqlite3BtreeBeginTrans(p->pSrc, 0);
          385  +      rc = sqlite3BtreeBeginTrans(p->pSrc, 0, 0);
   386    386         bCloseTrans = 1;
   387    387       }
   388    388   
   389    389       /* If the destination database has not yet been locked (i.e. if this
   390    390       ** is the first call to backup_step() for the current backup operation),
   391    391       ** try to set its page size to the same as the source database. This
   392    392       ** is especially important on ZipVFS systems, as in that case it is
................................................................................
   394    394       ** writing to it with another.  */
   395    395       if( p->bDestLocked==0 && rc==SQLITE_OK && setDestPgsz(p)==SQLITE_NOMEM ){
   396    396         rc = SQLITE_NOMEM;
   397    397       }
   398    398   
   399    399       /* Lock the destination database, if it is not locked already. */
   400    400       if( SQLITE_OK==rc && p->bDestLocked==0
   401         -     && SQLITE_OK==(rc = sqlite3BtreeBeginTrans(p->pDest, 2)) 
          401  +     && SQLITE_OK==(rc = sqlite3BtreeBeginTrans(p->pDest, 2,
          402  +                                                (int*)&p->iDestSchema)) 
   402    403       ){
   403    404         p->bDestLocked = 1;
   404         -      sqlite3BtreeGetMeta(p->pDest, BTREE_SCHEMA_VERSION, &p->iDestSchema);
   405    405       }
   406    406   
   407    407       /* Do not allow backup if the destination database is in WAL mode
   408    408       ** and the page sizes are different between source and destination */
   409    409       pgszSrc = sqlite3BtreeGetPageSize(p->pSrc);
   410    410       pgszDest = sqlite3BtreeGetPageSize(p->pDest);
   411    411       destMode = sqlite3PagerGetJournalMode(sqlite3BtreePager(p->pDest));

Changes to src/btree.c.

  3296   3296   ** a reserved lock.  B tries to promote to exclusive but is blocked because
  3297   3297   ** of A's read lock.  A tries to promote to reserved but is blocked by B.
  3298   3298   ** One or the other of the two processes must give way or there can be
  3299   3299   ** no progress.  By returning SQLITE_BUSY and not invoking the busy callback
  3300   3300   ** when A already has a read lock, we encourage A to give up and let B
  3301   3301   ** proceed.
  3302   3302   */
  3303         -int sqlite3BtreeBeginTrans(Btree *p, int wrflag){
         3303  +int sqlite3BtreeBeginTrans(Btree *p, int wrflag, int *pSchemaVersion){
  3304   3304     BtShared *pBt = p->pBt;
  3305   3305     int rc = SQLITE_OK;
  3306   3306   
  3307   3307     sqlite3BtreeEnter(p);
  3308   3308     btreeIntegrity(p);
  3309   3309   
  3310   3310     /* If the btree is already in a write-transaction, or it
................................................................................
  3424   3424           }
  3425   3425         }
  3426   3426       }
  3427   3427     }
  3428   3428   
  3429   3429   
  3430   3430   trans_begun:
  3431         -  if( rc==SQLITE_OK && wrflag ){
  3432         -    /* This call makes sure that the pager has the correct number of
  3433         -    ** open savepoints. If the second parameter is greater than 0 and
  3434         -    ** the sub-journal is not already open, then it will be opened here.
  3435         -    */
  3436         -    rc = sqlite3PagerOpenSavepoint(pBt->pPager, p->db->nSavepoint);
         3431  +  if( rc==SQLITE_OK ){
         3432  +    if( pSchemaVersion ){
         3433  +      *pSchemaVersion = get4byte(&pBt->pPage1->aData[40]);
         3434  +    }
         3435  +    if( wrflag ){
         3436  +      /* This call makes sure that the pager has the correct number of
         3437  +      ** open savepoints. If the second parameter is greater than 0 and
         3438  +      ** the sub-journal is not already open, then it will be opened here.
         3439  +      */
         3440  +      rc = sqlite3PagerOpenSavepoint(pBt->pPager, p->db->nSavepoint);
         3441  +    }
  3437   3442     }
  3438   3443   
  3439   3444     btreeIntegrity(p);
  3440   3445     sqlite3BtreeLeave(p);
  3441   3446     return rc;
  3442   3447   }
  3443   3448   
................................................................................
 10087  10092   
 10088  10093     /* If setting the version fields to 1, do not automatically open the
 10089  10094     ** WAL connection, even if the version fields are currently set to 2.
 10090  10095     */
 10091  10096     pBt->btsFlags &= ~BTS_NO_WAL;
 10092  10097     if( iVersion==1 ) pBt->btsFlags |= BTS_NO_WAL;
 10093  10098   
 10094         -  rc = sqlite3BtreeBeginTrans(pBtree, 0);
        10099  +  rc = sqlite3BtreeBeginTrans(pBtree, 0, 0);
 10095  10100     if( rc==SQLITE_OK ){
 10096  10101       u8 *aData = pBt->pPage1->aData;
 10097  10102       if( aData[18]!=(u8)iVersion || aData[19]!=(u8)iVersion ){
 10098         -      rc = sqlite3BtreeBeginTrans(pBtree, 2);
        10103  +      rc = sqlite3BtreeBeginTrans(pBtree, 2, 0);
 10099  10104         if( rc==SQLITE_OK ){
 10100  10105           rc = sqlite3PagerWrite(pBt->pPage1->pDbPage);
 10101  10106           if( rc==SQLITE_OK ){
 10102  10107             aData[18] = (u8)iVersion;
 10103  10108             aData[19] = (u8)iVersion;
 10104  10109           }
 10105  10110         }

Changes to src/btree.h.

    74     74   int sqlite3BtreeMaxPageCount(Btree*,int);
    75     75   u32 sqlite3BtreeLastPage(Btree*);
    76     76   int sqlite3BtreeSecureDelete(Btree*,int);
    77     77   int sqlite3BtreeGetOptimalReserve(Btree*);
    78     78   int sqlite3BtreeGetReserveNoMutex(Btree *p);
    79     79   int sqlite3BtreeSetAutoVacuum(Btree *, int);
    80     80   int sqlite3BtreeGetAutoVacuum(Btree *);
    81         -int sqlite3BtreeBeginTrans(Btree*,int);
           81  +int sqlite3BtreeBeginTrans(Btree*,int,int*);
    82     82   int sqlite3BtreeCommitPhaseOne(Btree*, const char *zMaster);
    83     83   int sqlite3BtreeCommitPhaseTwo(Btree*, int);
    84     84   int sqlite3BtreeCommit(Btree*);
    85     85   int sqlite3BtreeRollback(Btree*,int,int);
    86     86   int sqlite3BtreeBeginStmt(Btree*,int);
    87     87   int sqlite3BtreeCreateTable(Btree*, int*, int flags);
    88     88   int sqlite3BtreeIsInTrans(Btree*);

Changes to src/dbpage.c.

   365    365   */
   366    366   static int dbpageBegin(sqlite3_vtab *pVtab){
   367    367     DbpageTable *pTab = (DbpageTable *)pVtab;
   368    368     sqlite3 *db = pTab->db;
   369    369     int i;
   370    370     for(i=0; i<db->nDb; i++){
   371    371       Btree *pBt = db->aDb[i].pBt;
   372         -    if( pBt ) sqlite3BtreeBeginTrans(pBt, 1);
          372  +    if( pBt ) sqlite3BtreeBeginTrans(pBt, 1, 0);
   373    373     }
   374    374     return SQLITE_OK;
   375    375   }
   376    376   
   377    377   
   378    378   /*
   379    379   ** Invoke this routine to register the "dbpage" virtual table module

Changes to src/main.c.

  4111   4111     sqlite3_mutex_enter(db->mutex);
  4112   4112   
  4113   4113     if( db->autoCommit==0 ){
  4114   4114       int iDb = sqlite3FindDbName(db, zDb);
  4115   4115       if( iDb==0 || iDb>1 ){
  4116   4116         Btree *pBt = db->aDb[iDb].pBt;
  4117   4117         if( 0==sqlite3BtreeIsInTrans(pBt) ){
  4118         -        rc = sqlite3BtreeBeginTrans(pBt, 0);
         4118  +        rc = sqlite3BtreeBeginTrans(pBt, 0, 0);
  4119   4119           if( rc==SQLITE_OK ){
  4120   4120             rc = sqlite3PagerSnapshotGet(sqlite3BtreePager(pBt), ppSnapshot);
  4121   4121           }
  4122   4122         }
  4123   4123       }
  4124   4124     }
  4125   4125   
................................................................................
  4149   4149       int iDb;
  4150   4150       iDb = sqlite3FindDbName(db, zDb);
  4151   4151       if( iDb==0 || iDb>1 ){
  4152   4152         Btree *pBt = db->aDb[iDb].pBt;
  4153   4153         if( 0==sqlite3BtreeIsInReadTrans(pBt) ){
  4154   4154           rc = sqlite3PagerSnapshotOpen(sqlite3BtreePager(pBt), pSnapshot);
  4155   4155           if( rc==SQLITE_OK ){
  4156         -          rc = sqlite3BtreeBeginTrans(pBt, 0);
         4156  +          rc = sqlite3BtreeBeginTrans(pBt, 0, 0);
  4157   4157             sqlite3PagerSnapshotOpen(sqlite3BtreePager(pBt), 0);
  4158   4158           }
  4159   4159         }
  4160   4160       }
  4161   4161     }
  4162   4162   
  4163   4163     sqlite3_mutex_leave(db->mutex);
................................................................................
  4181   4181   #endif
  4182   4182   
  4183   4183     sqlite3_mutex_enter(db->mutex);
  4184   4184     iDb = sqlite3FindDbName(db, zDb);
  4185   4185     if( iDb==0 || iDb>1 ){
  4186   4186       Btree *pBt = db->aDb[iDb].pBt;
  4187   4187       if( 0==sqlite3BtreeIsInReadTrans(pBt) ){
  4188         -      rc = sqlite3BtreeBeginTrans(pBt, 0);
         4188  +      rc = sqlite3BtreeBeginTrans(pBt, 0, 0);
  4189   4189         if( rc==SQLITE_OK ){
  4190   4190           rc = sqlite3PagerSnapshotRecover(sqlite3BtreePager(pBt));
  4191   4191           sqlite3BtreeCommit(pBt);
  4192   4192         }
  4193   4193       }
  4194   4194     }
  4195   4195     sqlite3_mutex_leave(db->mutex);

Changes to src/pragma.c.

  1560   1560           for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
  1561   1561             sqlite3VdbeAddOp2(v, OP_Integer, 0, 8+j); /* index entries counter */
  1562   1562           }
  1563   1563           assert( pParse->nMem>=8+j );
  1564   1564           assert( sqlite3NoTempsInRange(pParse,1,7+j) );
  1565   1565           sqlite3VdbeAddOp2(v, OP_Rewind, iDataCur, 0); VdbeCoverage(v);
  1566   1566           loopTop = sqlite3VdbeAddOp2(v, OP_AddImm, 7, 1);
         1567  +        if( !isQuick ){
         1568  +          /* Sanity check on record header decoding */
         1569  +          sqlite3VdbeAddOp3(v, OP_Column, iDataCur, pTab->nCol-1, 3);
         1570  +          sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);
         1571  +        }
  1567   1572           /* Verify that all NOT NULL columns really are NOT NULL */
  1568   1573           for(j=0; j<pTab->nCol; j++){
  1569   1574             char *zErr;
  1570   1575             int jmp2;
  1571   1576             if( j==pTab->iPKey ) continue;
  1572   1577             if( pTab->aCol[j].notNull==0 ) continue;
  1573   1578             sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, j, 3);
................................................................................
  1602   1607               integrityCheckResultRow(v);
  1603   1608               sqlite3VdbeResolveLabel(v, addrCkOk);
  1604   1609               sqlite3ExprCachePop(pParse);
  1605   1610             }
  1606   1611             sqlite3ExprListDelete(db, pCheck);
  1607   1612           }
  1608   1613           if( !isQuick ){ /* Omit the remaining tests for quick_check */
  1609         -          /* Sanity check on record header decoding */
  1610         -          sqlite3VdbeAddOp3(v, OP_Column, iDataCur, pTab->nCol-1, 3);
  1611         -          sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);
  1612   1614             /* Validate index entries for the current row */
  1613   1615             for(j=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, j++){
  1614   1616               int jmp2, jmp3, jmp4, jmp5;
  1615   1617               int ckUniq = sqlite3VdbeMakeLabel(v);
  1616   1618               if( pPk==pIdx ) continue;
  1617   1619               r1 = sqlite3GenerateIndexKey(pParse, pIdx, iDataCur, 0, 0, &jmp3,
  1618   1620                                            pPrior, r1);

Changes to src/prepare.c.

   184    184     }
   185    185   
   186    186     /* If there is not already a read-only (or read-write) transaction opened
   187    187     ** on the b-tree database, open one now. If a transaction is opened, it 
   188    188     ** will be closed before this function returns.  */
   189    189     sqlite3BtreeEnter(pDb->pBt);
   190    190     if( !sqlite3BtreeIsInReadTrans(pDb->pBt) ){
   191         -    rc = sqlite3BtreeBeginTrans(pDb->pBt, 0);
          191  +    rc = sqlite3BtreeBeginTrans(pDb->pBt, 0, 0);
   192    192       if( rc!=SQLITE_OK ){
   193    193         sqlite3SetString(pzErrMsg, db, sqlite3ErrStr(rc));
   194    194         goto initone_error_out;
   195    195       }
   196    196       openedTransaction = 1;
   197    197     }
   198    198   
................................................................................
   429    429       Btree *pBt = db->aDb[iDb].pBt;     /* Btree database to read cookie from */
   430    430       if( pBt==0 ) continue;
   431    431   
   432    432       /* If there is not already a read-only (or read-write) transaction opened
   433    433       ** on the b-tree database, open one now. If a transaction is opened, it 
   434    434       ** will be closed immediately after reading the meta-value. */
   435    435       if( !sqlite3BtreeIsInReadTrans(pBt) ){
   436         -      rc = sqlite3BtreeBeginTrans(pBt, 0);
          436  +      rc = sqlite3BtreeBeginTrans(pBt, 0, 0);
   437    437         if( rc==SQLITE_NOMEM || rc==SQLITE_IOERR_NOMEM ){
   438    438           sqlite3OomFault(db);
   439    439         }
   440    440         if( rc!=SQLITE_OK ) return;
   441    441         openedTransaction = 1;
   442    442       }
   443    443   

Changes to src/select.c.

  5099   5099     for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
  5100   5100       ExprList *pList = pF->pExpr->x.pList;
  5101   5101       assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) );
  5102   5102       sqlite3VdbeAddOp2(v, OP_AggFinal, pF->iMem, pList ? pList->nExpr : 0);
  5103   5103       sqlite3VdbeAppendP4(v, pF->pFunc, P4_FUNCDEF);
  5104   5104     }
  5105   5105   }
         5106  +
  5106   5107   
  5107   5108   /*
  5108   5109   ** Update the accumulator memory cells for an aggregate based on
  5109   5110   ** the current cursor position.
         5111  +**
         5112  +** If regAcc is non-zero and there are no min() or max() aggregates
         5113  +** in pAggInfo, then only populate the pAggInfo->nAccumulator accumulator
         5114  +** registers i register regAcc contains 0. The caller will take care
         5115  +** of setting and clearing regAcc.
  5110   5116   */
  5111         -static void updateAccumulator(Parse *pParse, AggInfo *pAggInfo){
         5117  +static void updateAccumulator(Parse *pParse, int regAcc, AggInfo *pAggInfo){
  5112   5118     Vdbe *v = pParse->pVdbe;
  5113   5119     int i;
  5114   5120     int regHit = 0;
  5115   5121     int addrHitTest = 0;
  5116   5122     struct AggInfo_func *pF;
  5117   5123     struct AggInfo_col *pC;
  5118   5124   
................................................................................
  5168   5174     ** to pC->iMem. But by the time the value is used, the original register
  5169   5175     ** may have been used, invalidating the underlying buffer holding the
  5170   5176     ** text or blob value. See ticket [883034dcb5].
  5171   5177     **
  5172   5178     ** Another solution would be to change the OP_SCopy used to copy cached
  5173   5179     ** values to an OP_Copy.
  5174   5180     */
         5181  +  if( regHit==0 && pAggInfo->nAccumulator ){
         5182  +    regHit = regAcc;
         5183  +  }
  5175   5184     if( regHit ){
  5176   5185       addrHitTest = sqlite3VdbeAddOp1(v, OP_If, regHit); VdbeCoverage(v);
  5177   5186     }
  5178   5187     sqlite3ExprCacheClear(pParse);
  5179   5188     for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){
  5180   5189       sqlite3ExprCode(pParse, pC->pExpr, pC->iMem);
  5181   5190     }
................................................................................
  6063   6072         addrReset = sqlite3VdbeMakeLabel(v);
  6064   6073         iAMem = pParse->nMem + 1;
  6065   6074         pParse->nMem += pGroupBy->nExpr;
  6066   6075         iBMem = pParse->nMem + 1;
  6067   6076         pParse->nMem += pGroupBy->nExpr;
  6068   6077         sqlite3VdbeAddOp2(v, OP_Integer, 0, iAbortFlag);
  6069   6078         VdbeComment((v, "clear abort flag"));
  6070         -      sqlite3VdbeAddOp2(v, OP_Integer, 0, iUseFlag);
  6071         -      VdbeComment((v, "indicate accumulator empty"));
  6072   6079         sqlite3VdbeAddOp3(v, OP_Null, 0, iAMem, iAMem+pGroupBy->nExpr-1);
  6073   6080   
  6074   6081         /* Begin a loop that will extract all source rows in GROUP BY order.
  6075   6082         ** This might involve two separate loops with an OP_Sort in between, or
  6076   6083         ** it might be a single loop that uses an index to extract information
  6077   6084         ** in the right order to begin with.
  6078   6085         */
................................................................................
  6197   6204         sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
  6198   6205         VdbeComment((v, "reset accumulator"));
  6199   6206   
  6200   6207         /* Update the aggregate accumulators based on the content of
  6201   6208         ** the current row
  6202   6209         */
  6203   6210         sqlite3VdbeJumpHere(v, addr1);
  6204         -      updateAccumulator(pParse, &sAggInfo);
         6211  +      updateAccumulator(pParse, iUseFlag, &sAggInfo);
  6205   6212         sqlite3VdbeAddOp2(v, OP_Integer, 1, iUseFlag);
  6206   6213         VdbeComment((v, "indicate data in accumulator"));
  6207   6214   
  6208   6215         /* End of the loop
  6209   6216         */
  6210   6217         if( groupBySort ){
  6211   6218           sqlite3VdbeAddOp2(v, OP_SorterNext, sAggInfo.sortingIdx, addrTopOfLoop);
................................................................................
  6249   6256         sqlite3VdbeAddOp1(v, OP_Return, regOutputRow);
  6250   6257         VdbeComment((v, "end groupby result generator"));
  6251   6258   
  6252   6259         /* Generate a subroutine that will reset the group-by accumulator
  6253   6260         */
  6254   6261         sqlite3VdbeResolveLabel(v, addrReset);
  6255   6262         resetAccumulator(pParse, &sAggInfo);
         6263  +      sqlite3VdbeAddOp2(v, OP_Integer, 0, iUseFlag);
         6264  +      VdbeComment((v, "indicate accumulator empty"));
  6256   6265         sqlite3VdbeAddOp1(v, OP_Return, regReset);
  6257   6266        
  6258   6267       } /* endif pGroupBy.  Begin aggregate queries without GROUP BY: */
  6259   6268       else {
  6260   6269   #ifndef SQLITE_OMIT_BTREECOUNT
  6261   6270         Table *pTab;
  6262   6271         if( (pTab = isSimpleCount(p, &sAggInfo))!=0 ){
................................................................................
  6314   6323           }
  6315   6324           sqlite3VdbeAddOp2(v, OP_Count, iCsr, sAggInfo.aFunc[0].iMem);
  6316   6325           sqlite3VdbeAddOp1(v, OP_Close, iCsr);
  6317   6326           explainSimpleCount(pParse, pTab, pBest);
  6318   6327         }else
  6319   6328   #endif /* SQLITE_OMIT_BTREECOUNT */
  6320   6329         {
         6330  +        int regAcc = 0;           /* "populate accumulators" flag */
         6331  +
         6332  +        /* If there are accumulator registers but no min() or max() functions,
         6333  +        ** allocate register regAcc. Register regAcc will contain 0 the first
         6334  +        ** time the inner loop runs, and 1 thereafter. The code generated
         6335  +        ** by updateAccumulator() only updates the accumulator registers if
         6336  +        ** regAcc contains 0.  */
         6337  +        if( sAggInfo.nAccumulator ){
         6338  +          for(i=0; i<sAggInfo.nFunc; i++){
         6339  +            if( sAggInfo.aFunc[i].pFunc->funcFlags&SQLITE_FUNC_NEEDCOLL ) break;
         6340  +          }
         6341  +          if( i==sAggInfo.nFunc ){
         6342  +            regAcc = ++pParse->nMem;
         6343  +            sqlite3VdbeAddOp2(v, OP_Integer, 0, regAcc);
         6344  +          }
         6345  +        }
         6346  +
  6321   6347           /* This case runs if the aggregate has no GROUP BY clause.  The
  6322   6348           ** processing is much simpler since there is only a single row
  6323   6349           ** of output.
  6324   6350           */
  6325   6351           assert( p->pGroupBy==0 );
  6326   6352           resetAccumulator(pParse, &sAggInfo);
  6327   6353   
................................................................................
  6335   6361   
  6336   6362           SELECTTRACE(1,pParse,p,("WhereBegin\n"));
  6337   6363           pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMaxOrderBy,
  6338   6364                                      0, minMaxFlag, 0);
  6339   6365           if( pWInfo==0 ){
  6340   6366             goto select_end;
  6341   6367           }
  6342         -        updateAccumulator(pParse, &sAggInfo);
         6368  +        updateAccumulator(pParse, regAcc, &sAggInfo);
         6369  +        if( regAcc ) sqlite3VdbeAddOp2(v, OP_Integer, 1, regAcc);
  6343   6370           if( sqlite3WhereIsOrdered(pWInfo)>0 ){
  6344   6371             sqlite3VdbeGoto(v, sqlite3WhereBreakLabel(pWInfo));
  6345   6372             VdbeComment((v, "%s() by index",
  6346   6373                   (minMaxFlag==WHERE_ORDERBY_MIN?"min":"max")));
  6347   6374           }
  6348   6375           sqlite3WhereEnd(pWInfo);
  6349   6376           finalizeAggFunctions(pParse, &sAggInfo);

Changes to src/shell.c.in.

  2563   2563   static void explain_data_prepare(ShellState *p, sqlite3_stmt *pSql){
  2564   2564     const char *zSql;               /* The text of the SQL statement */
  2565   2565     const char *z;                  /* Used to check if this is an EXPLAIN */
  2566   2566     int *abYield = 0;               /* True if op is an OP_Yield */
  2567   2567     int nAlloc = 0;                 /* Allocated size of p->aiIndent[], abYield */
  2568   2568     int iOp;                        /* Index of operation in p->aiIndent[] */
  2569   2569   
  2570         -  const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext",
  2571         -                           "NextIfOpen", "PrevIfOpen", 0 };
         2570  +  const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext", 0 };
  2572   2571     const char *azYield[] = { "Yield", "SeekLT", "SeekGT", "RowSetRead",
  2573   2572                               "Rewind", 0 };
  2574   2573     const char *azGoto[] = { "Goto", 0 };
  2575   2574   
  2576   2575     /* Try to figure out if this is really an EXPLAIN statement. If this
  2577   2576     ** cannot be verified, return early.  */
  2578   2577     if( sqlite3_column_count(pSql)!=8 ){
................................................................................
  2968   2967             sqlite3_free(zEQP);
  2969   2968           }
  2970   2969           if( pArg->autoEQP>=AUTOEQP_trigger && triggerEQP==0 ){
  2971   2970             sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, 0, 0);
  2972   2971             /* Reprepare pStmt before reactiving trace modes */
  2973   2972             sqlite3_finalize(pStmt);
  2974   2973             sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
         2974  +          if( pArg ) pArg->pStmt = pStmt;
  2975   2975           }
  2976   2976           restore_debug_trace_modes();
  2977   2977         }
  2978   2978   
  2979   2979         if( pArg ){
  2980   2980           pArg->cMode = pArg->mode;
  2981   2981           if( pArg->autoExplain ){
................................................................................
  5280   5280   ** Implementation of .ar "eXtract" command. 
  5281   5281   */
  5282   5282   static int arExtractCommand(ArCommand *pAr){
  5283   5283     const char *zSql1 = 
  5284   5284       "SELECT "
  5285   5285       " ($dir || name),"
  5286   5286       " writefile(($dir || name), %s, mode, mtime) "
  5287         -    "FROM %s WHERE (%s) AND (data IS NULL OR $dirOnly = 0)";
         5287  +    "FROM %s WHERE (%s) AND (data IS NULL OR $dirOnly = 0)"
         5288  +    " AND name NOT GLOB '*..[/\\]*'";
  5288   5289   
  5289   5290     const char *azExtraArg[] = { 
  5290   5291       "sqlar_uncompress(data, sz)",
  5291   5292       "data"
  5292   5293     };
  5293   5294   
  5294   5295     sqlite3_stmt *pSql = 0;

Changes to src/test3.c.

   129    129     if( argc!=2 ){
   130    130       Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
   131    131          " ID\"", 0);
   132    132       return TCL_ERROR;
   133    133     }
   134    134     pBt = sqlite3TestTextToPtr(argv[1]);
   135    135     sqlite3BtreeEnter(pBt);
   136         -  rc = sqlite3BtreeBeginTrans(pBt, 1);
          136  +  rc = sqlite3BtreeBeginTrans(pBt, 1, 0);
   137    137     sqlite3BtreeLeave(pBt);
   138    138     if( rc!=SQLITE_OK ){
   139    139       Tcl_AppendResult(interp, sqlite3ErrName(rc), 0);
   140    140       return TCL_ERROR;
   141    141     }
   142    142     return TCL_OK;
   143    143   }

Changes to src/vacuum.c.

   220    220   
   221    221     /* Begin a transaction and take an exclusive lock on the main database
   222    222     ** file. This is done before the sqlite3BtreeGetPageSize(pMain) call below,
   223    223     ** to ensure that we do not try to change the page-size on a WAL database.
   224    224     */
   225    225     rc = execSql(db, pzErrMsg, "BEGIN");
   226    226     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   227         -  rc = sqlite3BtreeBeginTrans(pMain, 2);
          227  +  rc = sqlite3BtreeBeginTrans(pMain, 2, 0);
   228    228     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   229    229   
   230    230     /* Do not attempt to change the page size for a WAL database */
   231    231     if( sqlite3PagerGetJournalMode(sqlite3BtreePager(pMain))
   232    232                                                  ==PAGER_JOURNALMODE_WAL ){
   233    233       db->nextPagesize = 0;
   234    234     }

Changes to src/vdbe.c.

  3198   3198   ** if the schema generation counter in P4 differs from the current
  3199   3199   ** generation counter, then an SQLITE_SCHEMA error is raised and execution
  3200   3200   ** halts.  The sqlite3_step() wrapper function might then reprepare the
  3201   3201   ** statement and rerun it from the beginning.
  3202   3202   */
  3203   3203   case OP_Transaction: {
  3204   3204     Btree *pBt;
  3205         -  int iMeta;
  3206         -  int iGen;
         3205  +  int iMeta = 0;
  3207   3206   
  3208   3207     assert( p->bIsReader );
  3209   3208     assert( p->readOnly==0 || pOp->p2==0 );
  3210   3209     assert( pOp->p1>=0 && pOp->p1<db->nDb );
  3211   3210     assert( DbMaskTest(p->btreeMask, pOp->p1) );
  3212   3211     if( pOp->p2 && (db->flags & SQLITE_QueryOnly)!=0 ){
  3213   3212       rc = SQLITE_READONLY;
  3214   3213       goto abort_due_to_error;
  3215   3214     }
  3216   3215     pBt = db->aDb[pOp->p1].pBt;
  3217   3216   
  3218   3217     if( pBt ){
  3219         -    rc = sqlite3BtreeBeginTrans(pBt, pOp->p2);
         3218  +    rc = sqlite3BtreeBeginTrans(pBt, pOp->p2, &iMeta);
  3220   3219       testcase( rc==SQLITE_BUSY_SNAPSHOT );
  3221   3220       testcase( rc==SQLITE_BUSY_RECOVERY );
  3222   3221       if( rc!=SQLITE_OK ){
  3223   3222         if( (rc&0xff)==SQLITE_BUSY ){
  3224   3223           p->pc = (int)(pOp - aOp);
  3225   3224           p->rc = rc;
  3226   3225           goto vdbe_return;
................................................................................
  3245   3244   
  3246   3245         /* Store the current value of the database handles deferred constraint
  3247   3246         ** counter. If the statement transaction needs to be rolled back,
  3248   3247         ** the value of this counter needs to be restored too.  */
  3249   3248         p->nStmtDefCons = db->nDeferredCons;
  3250   3249         p->nStmtDefImmCons = db->nDeferredImmCons;
  3251   3250       }
  3252         -
  3253         -    /* Gather the schema version number for checking:
         3251  +  }
         3252  +  assert( pOp->p5==0 || pOp->p4type==P4_INT32 );
         3253  +  if( pOp->p5
         3254  +   && (iMeta!=pOp->p3
         3255  +      || db->aDb[pOp->p1].pSchema->iGeneration!=pOp->p4.i)
         3256  +  ){
         3257  +    /*
  3254   3258       ** IMPLEMENTATION-OF: R-03189-51135 As each SQL statement runs, the schema
  3255   3259       ** version is checked to ensure that the schema has not changed since the
  3256   3260       ** SQL statement was prepared.
  3257   3261       */
  3258         -    sqlite3BtreeGetMeta(pBt, BTREE_SCHEMA_VERSION, (u32 *)&iMeta);
  3259         -    iGen = db->aDb[pOp->p1].pSchema->iGeneration;
  3260         -  }else{
  3261         -    iGen = iMeta = 0;
  3262         -  }
  3263         -  assert( pOp->p5==0 || pOp->p4type==P4_INT32 );
  3264         -  if( pOp->p5 && (iMeta!=pOp->p3 || iGen!=pOp->p4.i) ){
  3265   3262       sqlite3DbFree(db, p->zErrMsg);
  3266   3263       p->zErrMsg = sqlite3DbStrDup(db, "database schema has changed");
  3267   3264       /* If the schema-cookie from the database file matches the cookie 
  3268   3265       ** stored with the in-memory representation of the schema, do
  3269   3266       ** not reload the schema from the database file.
  3270   3267       **
  3271   3268       ** If virtual-tables are in use, this is not just an optimization.
................................................................................
  3366   3363   ** P2 in a database file.  The database file is determined by P3. 
  3367   3364   ** P3==0 means the main database, P3==1 means the database used for 
  3368   3365   ** temporary tables, and P3>1 means used the corresponding attached
  3369   3366   ** database.  Give the new cursor an identifier of P1.  The P1
  3370   3367   ** values need not be contiguous but all P1 values should be small integers.
  3371   3368   ** It is an error for P1 to be negative.
  3372   3369   **
  3373         -** If P5!=0 then use the content of register P2 as the root page, not
  3374         -** the value of P2 itself.
  3375         -**
  3376         -** There will be a read lock on the database whenever there is an
  3377         -** open cursor.  If the database was unlocked prior to this instruction
  3378         -** then a read lock is acquired as part of this instruction.  A read
  3379         -** lock allows other processes to read the database but prohibits
  3380         -** any other process from modifying the database.  The read lock is
  3381         -** released when all cursors are closed.  If this instruction attempts
  3382         -** to get a read lock but fails, the script terminates with an
  3383         -** SQLITE_BUSY error code.
         3370  +** Allowed P5 bits:
         3371  +** <ul>
         3372  +** <li>  <b>0x02 OPFLAG_SEEKEQ</b>: This cursor will only be used for
         3373  +**       equality lookups (implemented as a pair of opcodes OP_SeekGE/OP_IdxGT
         3374  +**       of OP_SeekLE/OP_IdxGT)
         3375  +** </ul>
  3384   3376   **
  3385   3377   ** The P4 value may be either an integer (P4_INT32) or a pointer to
  3386   3378   ** a KeyInfo structure (P4_KEYINFO). If it is a pointer to a KeyInfo 
  3387         -** structure, then said structure defines the content and collating 
  3388         -** sequence of the index being opened. Otherwise, if P4 is an integer 
  3389         -** value, it is set to the number of columns in the table.
         3379  +** object, then table being opened must be an [index b-tree] where the
         3380  +** KeyInfo object defines the content and collating 
         3381  +** sequence of that index b-tree. Otherwise, if P4 is an integer 
         3382  +** value, then the table being opened must be a [table b-tree] with a
         3383  +** number of columns no less than the value of P4.
  3390   3384   **
  3391   3385   ** See also: OpenWrite, ReopenIdx
  3392   3386   */
  3393   3387   /* Opcode: ReopenIdx P1 P2 P3 P4 P5
  3394   3388   ** Synopsis: root=P2 iDb=P3
  3395   3389   **
  3396         -** The ReopenIdx opcode works exactly like ReadOpen except that it first
  3397         -** checks to see if the cursor on P1 is already open with a root page
  3398         -** number of P2 and if it is this opcode becomes a no-op.  In other words,
         3390  +** The ReopenIdx opcode works like OP_OpenRead except that it first
         3391  +** checks to see if the cursor on P1 is already open on the same
         3392  +** b-tree and if it is this opcode becomes a no-op.  In other words,
  3399   3393   ** if the cursor is already open, do not reopen it.
  3400   3394   **
  3401         -** The ReopenIdx opcode may only be used with P5==0 and with P4 being
  3402         -** a P4_KEYINFO object.  Furthermore, the P3 value must be the same as
  3403         -** every other ReopenIdx or OpenRead for the same cursor number.
         3395  +** The ReopenIdx opcode may only be used with P5==0 or P5==OPFLAG_SEEKEQ
         3396  +** and with P4 being a P4_KEYINFO object.  Furthermore, the P3 value must
         3397  +** be the same as every other ReopenIdx or OpenRead for the same cursor
         3398  +** number.
  3404   3399   **
  3405         -** See the OpenRead opcode documentation for additional information.
         3400  +** Allowed P5 bits:
         3401  +** <ul>
         3402  +** <li>  <b>0x02 OPFLAG_SEEKEQ</b>: This cursor will only be used for
         3403  +**       equality lookups (implemented as a pair of opcodes OP_SeekGE/OP_IdxGT
         3404  +**       of OP_SeekLE/OP_IdxGT)
         3405  +** </ul>
         3406  +**
         3407  +** See also: OP_OpenRead, OP_OpenWrite
  3406   3408   */
  3407   3409   /* Opcode: OpenWrite P1 P2 P3 P4 P5
  3408   3410   ** Synopsis: root=P2 iDb=P3
  3409   3411   **
  3410   3412   ** Open a read/write cursor named P1 on the table or index whose root
  3411         -** page is P2.  Or if P5!=0 use the content of register P2 to find the
  3412         -** root page.
         3413  +** page is P2 (or whose root page is held in register P2 if the
         3414  +** OPFLAG_P2ISREG bit is set in P5 - see below).
  3413   3415   **
  3414   3416   ** The P4 value may be either an integer (P4_INT32) or a pointer to
  3415   3417   ** a KeyInfo structure (P4_KEYINFO). If it is a pointer to a KeyInfo 
  3416         -** structure, then said structure defines the content and collating 
  3417         -** sequence of the index being opened. Otherwise, if P4 is an integer 
  3418         -** value, it is set to the number of columns in the table, or to the
  3419         -** largest index of any column of the table that is actually used.
         3418  +** object, then table being opened must be an [index b-tree] where the
         3419  +** KeyInfo object defines the content and collating 
         3420  +** sequence of that index b-tree. Otherwise, if P4 is an integer 
         3421  +** value, then the table being opened must be a [table b-tree] with a
         3422  +** number of columns no less than the value of P4.
  3420   3423   **
  3421         -** This instruction works just like OpenRead except that it opens the cursor
  3422         -** in read/write mode.  For a given table, there can be one or more read-only
  3423         -** cursors or a single read/write cursor but not both.
         3424  +** Allowed P5 bits:
         3425  +** <ul>
         3426  +** <li>  <b>0x02 OPFLAG_SEEKEQ</b>: This cursor will only be used for
         3427  +**       equality lookups (implemented as a pair of opcodes OP_SeekGE/OP_IdxGT
         3428  +**       of OP_SeekLE/OP_IdxGT)
         3429  +** <li>  <b>0x08 OPFLAG_FORDELETE</b>: This cursor is used only to seek
         3430  +**       and subsequently delete entries in an index btree.  This is a
         3431  +**       hint to the storage engine that the storage engine is allowed to
         3432  +**       ignore.  The hint is not used by the official SQLite b*tree storage
         3433  +**       engine, but is used by COMDB2.
         3434  +** <li>  <b>0x10 OPFLAG_P2ISREG</b>: Use the content of register P2
         3435  +**       as the root page, not the value of P2 itself.
         3436  +** </ul>
  3424   3437   **
  3425         -** See also OpenRead.
         3438  +** This instruction works like OpenRead except that it opens the cursor
         3439  +** in read/write mode.
         3440  +**
         3441  +** See also: OP_OpenRead, OP_ReopenIdx
  3426   3442   */
  3427   3443   case OP_ReopenIdx: {
  3428   3444     int nField;
  3429   3445     KeyInfo *pKeyInfo;
  3430   3446     int p2;
  3431   3447     int iDb;
  3432   3448     int wrFlag;
................................................................................
  3474   3490       }
  3475   3491     }else{
  3476   3492       wrFlag = 0;
  3477   3493     }
  3478   3494     if( pOp->p5 & OPFLAG_P2ISREG ){
  3479   3495       assert( p2>0 );
  3480   3496       assert( p2<=(p->nMem+1 - p->nCursor) );
         3497  +    assert( pOp->opcode==OP_OpenWrite );
  3481   3498       pIn2 = &aMem[p2];
  3482   3499       assert( memIsValid(pIn2) );
  3483   3500       assert( (pIn2->flags & MEM_Int)!=0 );
  3484   3501       sqlite3VdbeMemIntegerify(pIn2);
  3485   3502       p2 = (int)pIn2->u.i;
  3486   3503       /* The p2 value always comes from a prior OP_CreateBtree opcode and
  3487   3504       ** that opcode will always set the p2 value to 2 or more or else fail.
................................................................................
  3602   3619     pCx = allocateCursor(p, pOp->p1, pOp->p2, -1, CURTYPE_BTREE);
  3603   3620     if( pCx==0 ) goto no_mem;
  3604   3621     pCx->nullRow = 1;
  3605   3622     pCx->isEphemeral = 1;
  3606   3623     rc = sqlite3BtreeOpen(db->pVfs, 0, db, &pCx->pBtx, 
  3607   3624                           BTREE_OMIT_JOURNAL | BTREE_SINGLE | pOp->p5, vfsFlags);
  3608   3625     if( rc==SQLITE_OK ){
  3609         -    rc = sqlite3BtreeBeginTrans(pCx->pBtx, 1);
         3626  +    rc = sqlite3BtreeBeginTrans(pCx->pBtx, 1, 0);
  3610   3627     }
  3611   3628     if( rc==SQLITE_OK ){
  3612   3629       /* If a transient index is required, create it by calling
  3613   3630       ** sqlite3BtreeCreateTable() with the BTREE_BLOBKEY flag before
  3614   3631       ** opening it. If a transient table is required, just use the
  3615   3632       ** automatically created table with root-page 1 (an BLOB_INTKEY table).
  3616   3633       */
................................................................................
  4009   4026       goto jump_to_p2;
  4010   4027     }else if( eqOnly ){
  4011   4028       assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT );
  4012   4029       pOp++; /* Skip the OP_IdxLt or OP_IdxGT that follows */
  4013   4030     }
  4014   4031     break;
  4015   4032   }
         4033  +
         4034  +/* Opcode: SeekHit P1 P2 * * *
         4035  +** Synopsis: seekHit=P2
         4036  +**
         4037  +** Set the seekHit flag on cursor P1 to the value in P2.
         4038  +** The seekHit flag is used by the IfNoHope opcode.
         4039  +**
         4040  +** P1 must be a valid b-tree cursor.  P2 must be a boolean value,
         4041  +** either 0 or 1.
         4042  +*/
         4043  +case OP_SeekHit: {
         4044  +  VdbeCursor *pC;
         4045  +  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
         4046  +  pC = p->apCsr[pOp->p1];
         4047  +  assert( pC!=0 );
         4048  +  assert( pOp->p2==0 || pOp->p2==1 );
         4049  +  pC->seekHit = pOp->p2 & 1;
         4050  +  break;
         4051  +}
  4016   4052   
  4017   4053   /* Opcode: Found P1 P2 P3 P4 *
  4018   4054   ** Synopsis: key=r[P3@P4]
  4019   4055   **
  4020   4056   ** If P4==0 then register P3 holds a blob constructed by MakeRecord.  If
  4021   4057   ** P4>0 then register P3 is the first of P4 registers that form an unpacked
  4022   4058   ** record.
................................................................................
  4044   4080   ** falls through to the next instruction and P1 is left pointing at the
  4045   4081   ** matching entry.
  4046   4082   **
  4047   4083   ** This operation leaves the cursor in a state where it cannot be
  4048   4084   ** advanced in either direction.  In other words, the Next and Prev
  4049   4085   ** opcodes do not work after this operation.
  4050   4086   **
  4051         -** See also: Found, NotExists, NoConflict
         4087  +** See also: Found, NotExists, NoConflict, IfNoHope
         4088  +*/
         4089  +/* Opcode: IfNoHope P1 P2 P3 P4 *
         4090  +** Synopsis: key=r[P3@P4]
         4091  +**
         4092  +** Register P3 is the first of P4 registers that form an unpacked
         4093  +** record.
         4094  +**
         4095  +** Cursor P1 is on an index btree.  If the seekHit flag is set on P1, then
         4096  +** this opcode is a no-op.  But if the seekHit flag of P1 is clear, then
         4097  +** check to see if there is any entry in P1 that matches the
         4098  +** prefix identified by P3 and P4.  If no entry matches the prefix,
         4099  +** jump to P2.  Otherwise fall through.
         4100  +**
         4101  +** This opcode behaves like OP_NotFound if the seekHit
         4102  +** flag is clear and it behaves like OP_Noop if the seekHit flag is set.
         4103  +**
         4104  +** This opcode is used in IN clause processing for a multi-column key.
         4105  +** If an IN clause is attached to an element of the key other than the
         4106  +** left-most element, and if there are no matches on the most recent
         4107  +** seek over the whole key, then it might be that one of the key element
         4108  +** to the left is prohibiting a match, and hence there is "no hope" of
         4109  +** any match regardless of how many IN clause elements are checked.
         4110  +** In such a case, we abandon the IN clause search early, using this
         4111  +** opcode.  The opcode name comes from the fact that the
         4112  +** jump is taken if there is "no hope" of achieving a match.
         4113  +**
         4114  +** See also: NotFound, SeekHit
  4052   4115   */
  4053   4116   /* Opcode: NoConflict P1 P2 P3 P4 *
  4054   4117   ** Synopsis: key=r[P3@P4]
  4055   4118   **
  4056   4119   ** If P4==0 then register P3 holds a blob constructed by MakeRecord.  If
  4057   4120   ** P4>0 then register P3 is the first of P4 registers that form an unpacked
  4058   4121   ** record.
................................................................................
  4069   4132   **
  4070   4133   ** This operation leaves the cursor in a state where it cannot be
  4071   4134   ** advanced in either direction.  In other words, the Next and Prev
  4072   4135   ** opcodes do not work after this operation.
  4073   4136   **
  4074   4137   ** See also: NotFound, Found, NotExists
  4075   4138   */
         4139  +case OP_IfNoHope: {     /* jump, in3 */
         4140  +  VdbeCursor *pC;
         4141  +  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
         4142  +  pC = p->apCsr[pOp->p1];
         4143  +  assert( pC!=0 );
         4144  +  if( pC->seekHit ) break;
         4145  +  /* Fall through into OP_NotFound */
         4146  +}
  4076   4147   case OP_NoConflict:     /* jump, in3 */
  4077   4148   case OP_NotFound:       /* jump, in3 */
  4078   4149   case OP_Found: {        /* jump, in3 */
  4079   4150     int alreadyExists;
  4080   4151     int takeJump;
  4081   4152     int ii;
  4082   4153     VdbeCursor *pC;
................................................................................
  4217   4288   case OP_NotExists:          /* jump, in3 */
  4218   4289     pIn3 = &aMem[pOp->p3];
  4219   4290     assert( pIn3->flags & MEM_Int );
  4220   4291     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  4221   4292     pC = p->apCsr[pOp->p1];
  4222   4293     assert( pC!=0 );
  4223   4294   #ifdef SQLITE_DEBUG
  4224         -  pC->seekOp = 0;
         4295  +  pC->seekOp = OP_SeekRowid;
  4225   4296   #endif
  4226   4297     assert( pC->isTable );
  4227   4298     assert( pC->eCurType==CURTYPE_BTREE );
  4228   4299     pCrsr = pC->uc.pCursor;
  4229   4300     assert( pCrsr!=0 );
  4230   4301     res = 0;
  4231   4302     iKey = pIn3->u.i;
................................................................................
  4871   4942     assert( pC!=0 );
  4872   4943     pC->nullRow = 1;
  4873   4944     pC->cacheStatus = CACHE_STALE;
  4874   4945     if( pC->eCurType==CURTYPE_BTREE ){
  4875   4946       assert( pC->uc.pCursor!=0 );
  4876   4947       sqlite3BtreeClearCursor(pC->uc.pCursor);
  4877   4948     }
         4949  +#ifdef SQLITE_DEBUG
         4950  +  if( pC->seekOp==0 ) pC->seekOp = OP_NullRow;
         4951  +#endif
  4878   4952     break;
  4879   4953   }
  4880   4954   
  4881   4955   /* Opcode: SeekEnd P1 * * * *
  4882   4956   **
  4883   4957   ** Position cursor P1 at the end of the btree for the purpose of
  4884   4958   ** appending a new entry onto the btree.
................................................................................
  5058   5132   **
  5059   5133   ** P4 is always of type P4_ADVANCE. The function pointer points to
  5060   5134   ** sqlite3BtreeNext().
  5061   5135   **
  5062   5136   ** If P5 is positive and the jump is taken, then event counter
  5063   5137   ** number P5-1 in the prepared statement is incremented.
  5064   5138   **
  5065         -** See also: Prev, NextIfOpen
  5066         -*/
  5067         -/* Opcode: NextIfOpen P1 P2 P3 P4 P5
  5068         -**
  5069         -** This opcode works just like Next except that if cursor P1 is not
  5070         -** open it behaves a no-op.
         5139  +** See also: Prev
  5071   5140   */
  5072   5141   /* Opcode: Prev P1 P2 P3 P4 P5
  5073   5142   **
  5074   5143   ** Back up cursor P1 so that it points to the previous key/data pair in its
  5075   5144   ** table or index.  If there is no previous key/value pairs then fall through
  5076   5145   ** to the following instruction.  But if the cursor backup was successful,
  5077   5146   ** jump immediately to P2.
................................................................................
  5091   5160   **
  5092   5161   ** P4 is always of type P4_ADVANCE. The function pointer points to
  5093   5162   ** sqlite3BtreePrevious().
  5094   5163   **
  5095   5164   ** If P5 is positive and the jump is taken, then event counter
  5096   5165   ** number P5-1 in the prepared statement is incremented.
  5097   5166   */
  5098         -/* Opcode: PrevIfOpen P1 P2 P3 P4 P5
  5099         -**
  5100         -** This opcode works just like Prev except that if cursor P1 is not
  5101         -** open it behaves a no-op.
  5102         -*/
  5103   5167   /* Opcode: SorterNext P1 P2 * * P5
  5104   5168   **
  5105   5169   ** This opcode works just like OP_Next except that P1 must be a
  5106   5170   ** sorter object for which the OP_SorterSort opcode has been
  5107   5171   ** invoked.  This opcode advances the cursor to the next sorted
  5108   5172   ** record, or jumps to P2 if there are no more sorted records.
  5109   5173   */
................................................................................
  5110   5174   case OP_SorterNext: {  /* jump */
  5111   5175     VdbeCursor *pC;
  5112   5176   
  5113   5177     pC = p->apCsr[pOp->p1];
  5114   5178     assert( isSorter(pC) );
  5115   5179     rc = sqlite3VdbeSorterNext(db, pC);
  5116   5180     goto next_tail;
  5117         -case OP_PrevIfOpen:    /* jump */
  5118         -case OP_NextIfOpen:    /* jump */
  5119         -  if( p->apCsr[pOp->p1]==0 ) break;
  5120         -  /* Fall through */
  5121   5181   case OP_Prev:          /* jump */
  5122   5182   case OP_Next:          /* jump */
  5123   5183     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  5124   5184     assert( pOp->p5<ArraySize(p->aCounter) );
  5125   5185     pC = p->apCsr[pOp->p1];
  5126   5186     assert( pC!=0 );
  5127   5187     assert( pC->deferredMoveto==0 );
  5128   5188     assert( pC->eCurType==CURTYPE_BTREE );
  5129   5189     assert( pOp->opcode!=OP_Next || pOp->p4.xAdvance==sqlite3BtreeNext );
  5130   5190     assert( pOp->opcode!=OP_Prev || pOp->p4.xAdvance==sqlite3BtreePrevious );
  5131         -  assert( pOp->opcode!=OP_NextIfOpen || pOp->p4.xAdvance==sqlite3BtreeNext );
  5132         -  assert( pOp->opcode!=OP_PrevIfOpen || pOp->p4.xAdvance==sqlite3BtreePrevious);
  5133   5191   
  5134         -  /* The Next opcode is only used after SeekGT, SeekGE, and Rewind.
         5192  +  /* The Next opcode is only used after SeekGT, SeekGE, Rewind, and Found.
  5135   5193     ** The Prev opcode is only used after SeekLT, SeekLE, and Last. */
  5136         -  assert( pOp->opcode!=OP_Next || pOp->opcode!=OP_NextIfOpen
         5194  +  assert( pOp->opcode!=OP_Next
  5137   5195          || pC->seekOp==OP_SeekGT || pC->seekOp==OP_SeekGE
  5138         -       || pC->seekOp==OP_Rewind || pC->seekOp==OP_Found);
  5139         -  assert( pOp->opcode!=OP_Prev || pOp->opcode!=OP_PrevIfOpen
         5196  +       || pC->seekOp==OP_Rewind || pC->seekOp==OP_Found 
         5197  +       || pC->seekOp==OP_NullRow);
         5198  +  assert( pOp->opcode!=OP_Prev
  5140   5199          || pC->seekOp==OP_SeekLT || pC->seekOp==OP_SeekLE
  5141         -       || pC->seekOp==OP_Last );
         5200  +       || pC->seekOp==OP_Last 
         5201  +       || pC->seekOp==OP_NullRow);
  5142   5202   
  5143   5203     rc = pOp->p4.xAdvance(pC->uc.pCursor, pOp->p3);
  5144   5204   next_tail:
  5145   5205     pC->cacheStatus = CACHE_STALE;
  5146   5206     VdbeBranchTaken(rc==SQLITE_OK,2);
  5147   5207     if( rc==SQLITE_OK ){
  5148   5208       pC->nullRow = 0;

Changes to src/vdbeInt.h.

    81     81   #ifdef SQLITE_DEBUG
    82     82     u8 seekOp;              /* Most recent seek operation on this cursor */
    83     83     u8 wrFlag;              /* The wrFlag argument to sqlite3BtreeCursor() */
    84     84   #endif
    85     85     Bool isEphemeral:1;     /* True for an ephemeral table */
    86     86     Bool useRandomRowid:1;  /* Generate new record numbers semi-randomly */
    87     87     Bool isOrdered:1;       /* True if the table is not BTREE_UNORDERED */
           88  +  Bool seekHit:1;         /* See the OP_SeekHit and OP_IfNoHope opcodes */
    88     89     Btree *pBtx;            /* Separate file holding temporary table */
    89     90     i64 seqCount;           /* Sequence counter */
    90     91     int *aAltMap;           /* Mapping from table to index column numbers */
    91     92   
    92     93     /* Cached OP_Column parse information is only valid if cacheStatus matches
    93     94     ** Vdbe.cacheCtr.  Vdbe.cacheCtr will never take on the value of
    94     95     ** CACHE_STALE (0) and so setting cacheStatus=CACHE_STALE guarantees that

Changes to src/vdbeaux.c.

   685    685           case OP_Vacuum:
   686    686           case OP_JournalMode: {
   687    687             p->readOnly = 0;
   688    688             p->bIsReader = 1;
   689    689             break;
   690    690           }
   691    691           case OP_Next:
   692         -        case OP_NextIfOpen:
   693    692           case OP_SorterNext: {
   694    693             pOp->p4.xAdvance = sqlite3BtreeNext;
   695    694             pOp->p4type = P4_ADVANCE;
   696    695             /* The code generator never codes any of these opcodes as a jump
   697    696             ** to a label.  They are always coded as a jump backwards to a 
   698    697             ** known address */
   699    698             assert( pOp->p2>=0 );
   700    699             break;
   701    700           }
   702         -        case OP_Prev:
   703         -        case OP_PrevIfOpen: {
          701  +        case OP_Prev: {
   704    702             pOp->p4.xAdvance = sqlite3BtreePrevious;
   705    703             pOp->p4type = P4_ADVANCE;
   706    704             /* The code generator never codes any of these opcodes as a jump
   707    705             ** to a label.  They are always coded as a jump backwards to a 
   708    706             ** known address */
   709    707             assert( pOp->p2>=0 );
   710    708             break;
................................................................................
  4125   4123   ){
  4126   4124     u32 d1;                         /* Offset into aKey[] of next data element */
  4127   4125     int i;                          /* Index of next field to compare */
  4128   4126     u32 szHdr1;                     /* Size of record header in bytes */
  4129   4127     u32 idx1;                       /* Offset of first type in header */
  4130   4128     int rc = 0;                     /* Return value */
  4131   4129     Mem *pRhs = pPKey2->aMem;       /* Next field of pPKey2 to compare */
  4132         -  KeyInfo *pKeyInfo = pPKey2->pKeyInfo;
         4130  +  KeyInfo *pKeyInfo;
  4133   4131     const unsigned char *aKey1 = (const unsigned char *)pKey1;
  4134   4132     Mem mem1;
  4135   4133   
  4136   4134     /* If bSkip is true, then the caller has already determined that the first
  4137   4135     ** two elements in the keys are equal. Fix the various stack variables so
  4138   4136     ** that this routine begins comparing at the second field. */
  4139   4137     if( bSkip ){
................................................................................
  4220   4218         }else{
  4221   4219           mem1.n = (serial_type - 12) / 2;
  4222   4220           testcase( (d1+mem1.n)==(unsigned)nKey1 );
  4223   4221           testcase( (d1+mem1.n+1)==(unsigned)nKey1 );
  4224   4222           if( (d1+mem1.n) > (unsigned)nKey1 ){
  4225   4223             pPKey2->errCode = (u8)SQLITE_CORRUPT_BKPT;
  4226   4224             return 0;                /* Corruption */
  4227         -        }else if( pKeyInfo->aColl[i] ){
         4225  +        }else if( (pKeyInfo = pPKey2->pKeyInfo)->aColl[i] ){
  4228   4226             mem1.enc = pKeyInfo->enc;
  4229   4227             mem1.db = pKeyInfo->db;
  4230   4228             mem1.flags = MEM_Str;
  4231   4229             mem1.z = (char*)&aKey1[d1];
  4232   4230             rc = vdbeCompareMemString(
  4233   4231                 &mem1, pRhs, pKeyInfo->aColl[i], &pPKey2->errCode
  4234   4232             );
................................................................................
  4271   4269       /* RHS is null */
  4272   4270       else{
  4273   4271         serial_type = aKey1[idx1];
  4274   4272         rc = (serial_type!=0);
  4275   4273       }
  4276   4274   
  4277   4275       if( rc!=0 ){
  4278         -      if( pKeyInfo->aSortOrder[i] ){
         4276  +      if( pPKey2->pKeyInfo->aSortOrder[i] ){
  4279   4277           rc = -rc;
  4280   4278         }
  4281   4279         assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, rc) );
  4282   4280         assert( mem1.szMalloc==0 );  /* See comment below */
  4283   4281         return rc;
  4284   4282       }
  4285   4283   
  4286   4284       i++;
         4285  +    if( i==pPKey2->nField ) break;
  4287   4286       pRhs++;
  4288   4287       d1 += sqlite3VdbeSerialTypeLen(serial_type);
  4289   4288       idx1 += sqlite3VarintLen(serial_type);
  4290         -  }while( idx1<(unsigned)szHdr1 && i<pPKey2->nField && d1<=(unsigned)nKey1 );
         4289  +  }while( idx1<(unsigned)szHdr1 && d1<=(unsigned)nKey1 );
  4291   4290   
  4292   4291     /* No memory allocation is ever used on mem1.  Prove this using
  4293   4292     ** the following assert().  If the assert() fails, it indicates a
  4294   4293     ** memory leak and a need to call sqlite3VdbeMemRelease(&mem1).  */
  4295   4294     assert( mem1.szMalloc==0 );
  4296   4295   
  4297   4296     /* rc==0 here means that one or both of the keys ran out of fields and
  4298   4297     ** all the fields up to that point were equal. Return the default_rc
  4299   4298     ** value.  */
  4300   4299     assert( CORRUPT_DB 
  4301   4300          || vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, pPKey2->default_rc) 
  4302         -       || pKeyInfo->db->mallocFailed
         4301  +       || pPKey2->pKeyInfo->db->mallocFailed
  4303   4302     );
  4304   4303     pPKey2->eqSeen = 1;
  4305   4304     return pPKey2->default_rc;
  4306   4305   }
  4307   4306   int sqlite3VdbeRecordCompare(
  4308   4307     int nKey1, const void *pKey1,   /* Left key */
  4309   4308     UnpackedRecord *pPKey2          /* Right key */
................................................................................
  4621   4620       return SQLITE_CORRUPT_BKPT;
  4622   4621     }
  4623   4622     sqlite3VdbeMemInit(&m, db, 0);
  4624   4623     rc = sqlite3VdbeMemFromBtree(pCur, 0, (u32)nCellKey, &m);
  4625   4624     if( rc ){
  4626   4625       return rc;
  4627   4626     }
  4628         -  *res = sqlite3VdbeRecordCompare(m.n, m.z, pUnpacked);
         4627  +  *res = sqlite3VdbeRecordCompareWithSkip(m.n, m.z, pUnpacked, 0);
  4629   4628     sqlite3VdbeMemRelease(&m);
  4630   4629     return SQLITE_OK;
  4631   4630   }
  4632   4631   
  4633   4632   /*
  4634   4633   ** This routine sets the value to be returned by subsequent calls to
  4635   4634   ** sqlite3_changes() on the database handle 'db'. 

Changes to src/where.c.

  5079   5079       if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
  5080   5080         struct InLoop *pIn;
  5081   5081         int j;
  5082   5082         sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
  5083   5083         for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
  5084   5084           sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
  5085   5085           if( pIn->eEndLoopOp!=OP_Noop ){
         5086  +          if( pIn->nPrefix ){
         5087  +            assert( pLoop->wsFlags & WHERE_IN_EARLYOUT );
         5088  +            sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur,
         5089  +                              sqlite3VdbeCurrentAddr(v)+2,
         5090  +                              pIn->iBase, pIn->nPrefix);
         5091  +            VdbeCoverage(v);
         5092  +          }
  5086   5093             sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
  5087   5094             VdbeCoverage(v);
  5088         -          VdbeCoverageIf(v, pIn->eEndLoopOp==OP_PrevIfOpen);
  5089         -          VdbeCoverageIf(v, pIn->eEndLoopOp==OP_NextIfOpen);
         5095  +          VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Prev);
         5096  +          VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Next);
  5090   5097           }
  5091   5098           sqlite3VdbeJumpHere(v, pIn->addrInTop-1);
  5092   5099         }
  5093   5100       }
  5094   5101       sqlite3VdbeResolveLabel(v, pLevel->addrBrk);
  5095   5102       if( pLevel->addrSkip ){
  5096   5103         sqlite3VdbeGoto(v, pLevel->addrSkip);

Changes to src/whereInt.h.

    78     78     int p1, p2;           /* Operands of the opcode used to ends the loop */
    79     79     union {               /* Information that depends on pWLoop->wsFlags */
    80     80       struct {
    81     81         int nIn;              /* Number of entries in aInLoop[] */
    82     82         struct InLoop {
    83     83           int iCur;              /* The VDBE cursor used by this IN operator */
    84     84           int addrInTop;         /* Top of the IN loop */
           85  +        int iBase;             /* Base register of multi-key index record */
           86  +        int nPrefix;           /* Number of prior entires in the key */
    85     87           u8 eEndLoopOp;         /* IN Loop terminator. OP_Next or OP_Prev */
    86     88         } *aInLoop;           /* Information about each nested IN operator */
    87     89       } in;                 /* Used when pWLoop->wsFlags&WHERE_IN_ABLE */
    88     90       Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
    89     91     } u;
    90     92     struct WhereLoop *pWLoop;  /* The selected WhereLoop object */
    91     93     Bitmask notReady;          /* FROM entries not usable at this level */
................................................................................
   551    553   #define WHERE_IN_ABLE      0x00000800  /* Able to support an IN operator */
   552    554   #define WHERE_ONEROW       0x00001000  /* Selects no more than one row */
   553    555   #define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
   554    556   #define WHERE_AUTO_INDEX   0x00004000  /* Uses an ephemeral index */
   555    557   #define WHERE_SKIPSCAN     0x00008000  /* Uses the skip-scan algorithm */
   556    558   #define WHERE_UNQ_WANTED   0x00010000  /* WHERE_ONEROW would have been helpful*/
   557    559   #define WHERE_PARTIALIDX   0x00020000  /* The automatic index is partial */
          560  +#define WHERE_IN_EARLYOUT  0x00040000  /* Perhaps quit IN loops early */

Changes to src/wherecode.c.

   587    587             }else{
   588    588               int iCol = aiMap ? aiMap[iMap++] : 0;
   589    589               pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut);
   590    590             }
   591    591             sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
   592    592             if( i==iEq ){
   593    593               pIn->iCur = iTab;
   594         -            pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
          594  +            pIn->eEndLoopOp = bRev ? OP_Prev : OP_Next;
          595  +            if( iEq>0 && (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){
          596  +              pIn->iBase = iReg - i;
          597  +              pIn->nPrefix = i;
          598  +              pLoop->wsFlags |= WHERE_IN_EARLYOUT;
          599  +            }else{
          600  +              pIn->nPrefix = 0;
          601  +            }
   595    602             }else{
   596    603               pIn->eEndLoopOp = OP_Noop;
   597    604             }
   598    605             pIn++;
   599    606           }
   600    607         }
   601    608       }else{
................................................................................
  1654   1661       }
  1655   1662       codeApplyAffinity(pParse, regBase, nConstraint - bSeekPastNull, zStartAff);
  1656   1663       if( pLoop->nSkip>0 && nConstraint==pLoop->nSkip ){
  1657   1664         /* The skip-scan logic inside the call to codeAllEqualityConstraints()
  1658   1665         ** above has already left the cursor sitting on the correct row,
  1659   1666         ** so no further seeking is needed */
  1660   1667       }else{
         1668  +      if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
         1669  +        sqlite3VdbeAddOp1(v, OP_SeekHit, iIdxCur);
         1670  +      }
  1661   1671         op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
  1662   1672         assert( op!=0 );
  1663   1673         sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
  1664   1674         VdbeCoverage(v);
  1665   1675         VdbeCoverageIf(v, op==OP_Rewind);  testcase( op==OP_Rewind );
  1666   1676         VdbeCoverageIf(v, op==OP_Last);    testcase( op==OP_Last );
  1667   1677         VdbeCoverageIf(v, op==OP_SeekGT);  testcase( op==OP_SeekGT );
................................................................................
  1716   1726         op = aEndOp[bRev*2 + endEq];
  1717   1727         sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
  1718   1728         testcase( op==OP_IdxGT );  VdbeCoverageIf(v, op==OP_IdxGT );
  1719   1729         testcase( op==OP_IdxGE );  VdbeCoverageIf(v, op==OP_IdxGE );
  1720   1730         testcase( op==OP_IdxLT );  VdbeCoverageIf(v, op==OP_IdxLT );
  1721   1731         testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
  1722   1732       }
         1733  +
         1734  +    if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
         1735  +      sqlite3VdbeAddOp2(v, OP_SeekHit, iIdxCur, 1);
         1736  +    }
  1723   1737   
  1724   1738       /* Seek the table cursor, if required */
  1725   1739       if( omitTable ){
  1726   1740         /* pIdx is a covering index.  No need to access the main table. */
  1727   1741       }else if( HasRowid(pIdx->pTable) ){
  1728   1742         if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE) || (
  1729   1743             (pWInfo->wctrlFlags & WHERE_SEEK_UNIQ_TABLE) 

Changes to test/aggnested.test.

    61     61       NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1));
    62     62       REPLACE INTO t2 VALUES(1,88,888,8888);
    63     63       REPLACE INTO t2 VALUES(2,99,999,9999);
    64     64       SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),
    65     65               t1.* 
    66     66       FROM t1;
    67     67     }
    68         -} {A,B,B 3 33 333 3333}
           68  +} {A,B,B 1 11 111 1111}
    69     69   db2 close
    70     70   
    71     71   ##################### Test cases for ticket [bfbf38e5e9956ac69f] ############
    72     72   #
    73     73   # This first test case is the original problem report:
    74     74   do_test aggnested-3.0 {
    75     75     db eval {

Changes to test/e_select.test.

   797    797   do_select_tests e_select-4.1 {
   798    798     1  "SELECT * FROM z1 LIMIT 1"             {51.65 -59.58 belfries}
   799    799     2  "SELECT * FROM z1,z2 LIMIT 1"          {51.65 -59.58 belfries {} 21}
   800    800     3  "SELECT z1.* FROM z1,z2 LIMIT 1"       {51.65 -59.58 belfries}
   801    801     4  "SELECT z2.* FROM z1,z2 LIMIT 1"       {{} 21}
   802    802     5  "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}
   803    803   
   804         -  6  "SELECT count(*), * FROM z1"           {6 63 born -26}
          804  +  6  "SELECT count(*), * FROM z1"           {6 51.65 -59.58 belfries}
   805    805     7  "SELECT max(a), * FROM z1"             {63 63 born -26}
   806    806     8  "SELECT *, min(a) FROM z1"             {-5 {} 75 -5}
   807    807   
   808    808     9  "SELECT *,* FROM z1,z2 LIMIT 1" {        
   809    809        51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
   810    810     }
   811    811     10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
................................................................................
   935    935     CREATE TABLE a2(one PRIMARY KEY, three);
   936    936     INSERT INTO a2 VALUES(1, 1);
   937    937     INSERT INTO a2 VALUES(3, 2);
   938    938     INSERT INTO a2 VALUES(6, 3);
   939    939     INSERT INTO a2 VALUES(10, 4);
   940    940   } {}
   941    941   do_select_tests e_select-4.6 {
   942         -  1 "SELECT one, two, count(*) FROM a1"                        {4 10 4} 
   943         -  2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {2 3 2} 
          942  +  1 "SELECT one, two, count(*) FROM a1"                        {1 1 4}
          943  +  2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {1 1 2}
   944    944     3 "SELECT one, two, count(*) FROM a1 WHERE one>3"            {4 10 1} 
   945         -  4 "SELECT *, count(*) FROM a1 JOIN a2"                       {4 10 10 4 16} 
   946         -  5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
   947         -  6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
   948         -  7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6}
          945  +  4 "SELECT *, count(*) FROM a1 JOIN a2"                       {1 1 1 1 16}
          946  +  5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {1 1 1 3}
          947  +  6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {1 1 1 3}
          948  +  7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 1 1}
   949    949   }
   950    950   
   951    951   # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then
   952    952   # each non-aggregate expression is evaluated against a row consisting
   953    953   # entirely of NULL values.
   954    954   #
   955    955   do_select_tests e_select-4.7 {
................................................................................
  1124   1124     1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
  1125   1125     1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
  1126   1126     1.4  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}
  1127   1127   
  1128   1128     2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
  1129   1129     2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}
  1130   1130   
  1131         -  2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6"  {9 36}
         1131  +  2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING j>6"  {5 10}
  1132   1132   }
  1133   1133   
  1134   1134   # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then
  1135   1135   # evaluated once for each group of rows.
  1136   1136   #
  1137   1137   # EVIDENCE-OF: R-53735-47017 If the expression is an aggregate
  1138   1138   # expression, it is evaluated across all rows in the group.
................................................................................
  1150   1150   # arbitrarily chosen row from within the group.
  1151   1151   #
  1152   1152   # EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate
  1153   1153   # expression in the result-set, then all such expressions are evaluated
  1154   1154   # for the same row.
  1155   1155   #
  1156   1156   do_select_tests e_select-4.15 {
  1157         -  1  "SELECT i, j FROM c2 GROUP BY i%2"             {8 28   9 36}
  1158         -  2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28}
  1159         -  3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  1160         -  4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
         1157  +  1  "SELECT i, j FROM c2 GROUP BY i%2"             {2 1 1 0}
         1158  +  2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {2 1 1 0}
         1159  +  3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {}
         1160  +  4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {}
  1161   1161     5  "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"
  1162         -        {2 5 boron   2 2 helium   1 3 lithium}
         1162  +        {2 4 beryllium 2 1 hydrogen 1 3 lithium}
  1163   1163   } 
  1164   1164   
  1165   1165   # EVIDENCE-OF: R-19334-12811 Each group of input dataset rows
  1166   1166   # contributes a single row to the set of result rows.
  1167   1167   #
  1168   1168   # EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the
  1169   1169   # DISTINCT keyword, the number of rows returned by an aggregate query

Added test/in6.test.

            1  +# 2018-06-07
            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  +# A multi-key index that uses an IN operator on one of the keys other
           13  +# than the left-most key is able to abort the IN-operator loop early
           14  +# if key terms further to the left do not match.
           15  +#
           16  +# Call this the "multikey-IN-operator early-out optimization" or
           17  +# just "IN-early-out" optimization for short.
           18  +#
           19  +
           20  +set testdir [file dirname $argv0]
           21  +source $testdir/tester.tcl
           22  +set testprefix in6
           23  +
           24  +do_test in6-1.1 {
           25  +  db eval {
           26  +    CREATE TABLE t1(a,b,c,d);
           27  +    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
           28  +      INSERT INTO t1(a,b,c,d)
           29  +        SELECT 100, 200+x/2, 300+x/5, x FROM c;
           30  +    CREATE INDEX t1abc ON t1(a,b,c);
           31  +  }
           32  +  set ::sqlite_search_count 0
           33  +  db eval {
           34  +    SELECT d FROM t1
           35  +     WHERE a=99
           36  +       AND b IN (200,205,201,204)
           37  +       AND c IN (304,302,309,308);
           38  +  }
           39  +} {}
           40  +do_test in6-1.2 {
           41  +  set ::sqlite_search_count
           42  +} {0}  ;# Without the IN-early-out optimization, this value would be 15
           43  +
           44  +# The multikey-IN-operator early-out optimization does not apply
           45  +# when the IN operator is on the left-most column of the index.
           46  +#
           47  +do_test in6-1.3 {
           48  +  db eval {
           49  +    EXPLAIN
           50  +    SELECT d FROM t1
           51  +      WHERE a IN (98,99,100,101)
           52  +        AND b=200 AND c=300;
           53  +  }
           54  +} {~/(IfNoHope|SeekHit)/}
           55  +
           56  +set sqlite_search_count 0
           57  +do_execsql_test in6-1.4 {
           58  + SELECT d FROM t1
           59  +  WHERE a=100
           60  +    AND b IN (200,201,202,204)
           61  +    AND c IN (300,302,301,305)
           62  +  ORDER BY +d;
           63  +} {1 2 3 4 5 8 9}
           64  +do_test in6-1.5 {
           65  +  set ::sqlite_search_count
           66  +} {39}
           67  +
           68  +do_execsql_test in6-2.1 {
           69  +  CREATE TABLE t2(e INT UNIQUE, f TEXT);
           70  +  SELECT d, f FROM t1 LEFT JOIN t2 ON (e=d)
           71  +  WHERE a=100
           72  +    AND b IN (200,201,202,204)
           73  +    AND c IN (300,302,301,305)
           74  +  ORDER BY +d;
           75  +} {1 {} 2 {} 3 {} 4 {} 5 {} 8 {} 9 {}}
           76  +
           77  +finish_test

Changes to test/select5.test.

   150    150       SELECT a, b FROM t2 GROUP BY a, b;
   151    151     } 
   152    152   } {1 2 1 4 6 4}
   153    153   do_test select5-5.5 {
   154    154     execsql {
   155    155       SELECT a, b FROM t2 GROUP BY a;
   156    156     } 
   157         -} {1 4 6 4}
          157  +} {1 2 6 4}
   158    158   
   159    159   # Test rendering of columns for the GROUP BY clause.
   160    160   #
   161    161   do_test select5-5.11 {
   162    162     execsql {
   163    163       SELECT max(c), b*a, b, a FROM t2 GROUP BY b*a, b, a
   164    164     }

Changes to test/shell1.test.

   632    632   do_test shell1-3.23b.3 {
   633    633     catchcmd "test.db" ".stats OFF"
   634    634   } {0 {}}
   635    635   do_test shell1-3.23b.4 {
   636    636     # too many arguments
   637    637     catchcmd "test.db" ".stats OFF BAD"
   638    638   } {1 {Usage: .stats ?on|off?}}
          639  +
          640  +# Ticket 7be932dfa60a8a6b3b26bcf7623ec46e0a403ddb 2018-06-07
          641  +# Adverse interaction between .stats and .eqp
          642  +#
          643  +do_test shell1-3.23b.5 {
          644  +  catchcmd "test.db" [string map {"\n    " "\n"} {
          645  +    CREATE TEMP TABLE t1(x);
          646  +    INSERT INTO t1 VALUES(1),(2);
          647  +    .stats on
          648  +    .eqp full
          649  +    SELECT * FROM t1;
          650  +  }]
          651  +} {/1\n2\n/}
   639    652   
   640    653   # .tables ?TABLE?        List names of tables
   641    654   #                          If TABLE specified, only list tables matching
   642    655   #                          LIKE pattern TABLE.
   643    656   do_test shell1-3.24.1 {
   644    657     catchcmd "test.db" ".tables"
   645    658   } {0 {}}

Changes to test/where.test.

   486    486         SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
   487    487       }
   488    488     } {2 1 9 3 1 16 6}
   489    489     do_test where-5.14 {
   490    490       count {
   491    491         SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
   492    492       }
   493         -  } {2 1 9 5}
          493  +  } {2 1 9 4}
   494    494     do_test where-5.15 {
   495    495       count {
   496    496         SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
   497    497       }
   498         -  } {2 1 9 3 1 16 9}
          498  +  } {2 1 9 3 1 16 8}
   499    499     do_test where-5.100 {
   500    500       db eval {
   501    501         SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
   502    502          ORDER BY x, y
   503    503       }
   504    504     } {2 1 9 54 5 3025 62 5 3969}
   505    505     do_test where-5.101 {