/ Check-in [b415dfb6]
Login

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

Overview
Comment:Merge all fixes and enhancements from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | threads
Files: files | file ages | folders
SHA1: b415dfb6cb0df0c69992ca2bb700c15664f158e6
User & Date: drh 2014-03-25 13:17:41
Context
2014-03-25
14:12
Enable four sorting threads by default in the command-line shell. check-in: 1cab8357 user: drh tags: threads
13:17
Merge all fixes and enhancements from trunk. check-in: b415dfb6 user: drh tags: threads
11:00
Detect when a VdbeCursor is still pointing at a valid row but that row has moved, and invalidated the return from prior sqlite3BtreeDataFetch() or sqlite3BtreeKeyFetch() calls. check-in: e6798871 user: drh tags: trunk
2014-03-17
15:43
Add an experimental multi-threaded capability to vdbesorter.c. check-in: ff0b5c85 user: dan tags: threads
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to VERSION.

     1         -3.8.4.1
            1  +3.8.5

Changes to configure.

     1      1   #! /bin/sh
     2      2   # Guess values for system-dependent variables and create Makefiles.
     3         -# Generated by GNU Autoconf 2.62 for sqlite 3.8.4.1.
            3  +# Generated by GNU Autoconf 2.62 for sqlite 3.8.5.
     4      4   #
     5      5   # Copyright (C) 1992, 1993, 1994, 1995, 1996, 1998, 1999, 2000, 2001,
     6      6   # 2002, 2003, 2004, 2005, 2006, 2007, 2008 Free Software Foundation, Inc.
     7      7   # This configure script is free software; the Free Software Foundation
     8      8   # gives unlimited permission to copy, distribute and modify it.
     9      9   ## --------------------- ##
    10     10   ## M4sh Initialization.  ##
................................................................................
   739    739   MFLAGS=
   740    740   MAKEFLAGS=
   741    741   SHELL=${CONFIG_SHELL-/bin/sh}
   742    742   
   743    743   # Identity of this package.
   744    744   PACKAGE_NAME='sqlite'
   745    745   PACKAGE_TARNAME='sqlite'
   746         -PACKAGE_VERSION='3.8.4.1'
   747         -PACKAGE_STRING='sqlite 3.8.4.1'
          746  +PACKAGE_VERSION='3.8.5'
          747  +PACKAGE_STRING='sqlite 3.8.5'
   748    748   PACKAGE_BUGREPORT=''
   749    749   
   750    750   # Factoring default headers for most tests.
   751    751   ac_includes_default="\
   752    752   #include <stdio.h>
   753    753   #ifdef HAVE_SYS_TYPES_H
   754    754   # include <sys/types.h>
................................................................................
  1479   1479   #
  1480   1480   # Report the --help message.
  1481   1481   #
  1482   1482   if test "$ac_init_help" = "long"; then
  1483   1483     # Omit some internal or obsolete options to make the list less imposing.
  1484   1484     # This message is too long to be a string in the A/UX 3.1 sh.
  1485   1485     cat <<_ACEOF
  1486         -\`configure' configures sqlite 3.8.4.1 to adapt to many kinds of systems.
         1486  +\`configure' configures sqlite 3.8.5 to adapt to many kinds of systems.
  1487   1487   
  1488   1488   Usage: $0 [OPTION]... [VAR=VALUE]...
  1489   1489   
  1490   1490   To assign environment variables (e.g., CC, CFLAGS...), specify them as
  1491   1491   VAR=VALUE.  See below for descriptions of some of the useful variables.
  1492   1492   
  1493   1493   Defaults for the options are specified in brackets.
................................................................................
  1544   1544     --build=BUILD     configure for building on BUILD [guessed]
  1545   1545     --host=HOST       cross-compile to build programs to run on HOST [BUILD]
  1546   1546   _ACEOF
  1547   1547   fi
  1548   1548   
  1549   1549   if test -n "$ac_init_help"; then
  1550   1550     case $ac_init_help in
  1551         -     short | recursive ) echo "Configuration of sqlite 3.8.4.1:";;
         1551  +     short | recursive ) echo "Configuration of sqlite 3.8.5:";;
  1552   1552      esac
  1553   1553     cat <<\_ACEOF
  1554   1554   
  1555   1555   Optional Features:
  1556   1556     --disable-option-checking  ignore unrecognized --enable/--with options
  1557   1557     --disable-FEATURE       do not include FEATURE (same as --enable-FEATURE=no)
  1558   1558     --enable-FEATURE[=ARG]  include FEATURE [ARG=yes]
................................................................................
  1660   1660       cd "$ac_pwd" || { ac_status=$?; break; }
  1661   1661     done
  1662   1662   fi
  1663   1663   
  1664   1664   test -n "$ac_init_help" && exit $ac_status
  1665   1665   if $ac_init_version; then
  1666   1666     cat <<\_ACEOF
  1667         -sqlite configure 3.8.4.1
         1667  +sqlite configure 3.8.5
  1668   1668   generated by GNU Autoconf 2.62
  1669   1669   
  1670   1670   Copyright (C) 1992, 1993, 1994, 1995, 1996, 1998, 1999, 2000, 2001,
  1671   1671   2002, 2003, 2004, 2005, 2006, 2007, 2008 Free Software Foundation, Inc.
  1672   1672   This configure script is free software; the Free Software Foundation
  1673   1673   gives unlimited permission to copy, distribute and modify it.
  1674   1674   _ACEOF
  1675   1675     exit
  1676   1676   fi
  1677   1677   cat >config.log <<_ACEOF
  1678   1678   This file contains any messages produced by compilers while
  1679   1679   running configure, to aid debugging if configure makes a mistake.
  1680   1680   
  1681         -It was created by sqlite $as_me 3.8.4.1, which was
         1681  +It was created by sqlite $as_me 3.8.5, which was
  1682   1682   generated by GNU Autoconf 2.62.  Invocation command line was
  1683   1683   
  1684   1684     $ $0 $@
  1685   1685   
  1686   1686   _ACEOF
  1687   1687   exec 5>>config.log
  1688   1688   {
................................................................................
 14017  14017   
 14018  14018   exec 6>&1
 14019  14019   
 14020  14020   # Save the log message, to keep $[0] and so on meaningful, and to
 14021  14021   # report actual input values of CONFIG_FILES etc. instead of their
 14022  14022   # values after options handling.
 14023  14023   ac_log="
 14024         -This file was extended by sqlite $as_me 3.8.4.1, which was
        14024  +This file was extended by sqlite $as_me 3.8.5, which was
 14025  14025   generated by GNU Autoconf 2.62.  Invocation command line was
 14026  14026   
 14027  14027     CONFIG_FILES    = $CONFIG_FILES
 14028  14028     CONFIG_HEADERS  = $CONFIG_HEADERS
 14029  14029     CONFIG_LINKS    = $CONFIG_LINKS
 14030  14030     CONFIG_COMMANDS = $CONFIG_COMMANDS
 14031  14031     $ $0 $@
................................................................................
 14070  14070   $config_commands
 14071  14071   
 14072  14072   Report bugs to <bug-autoconf@gnu.org>."
 14073  14073   
 14074  14074   _ACEOF
 14075  14075   cat >>$CONFIG_STATUS <<_ACEOF || ac_write_fail=1
 14076  14076   ac_cs_version="\\
 14077         -sqlite config.status 3.8.4.1
        14077  +sqlite config.status 3.8.5
 14078  14078   configured by $0, generated by GNU Autoconf 2.62,
 14079  14079     with options \\"`$as_echo "$ac_configure_args" | sed 's/^ //; s/[\\""\`\$]/\\\\&/g'`\\"
 14080  14080   
 14081  14081   Copyright (C) 2008 Free Software Foundation, Inc.
 14082  14082   This config.status script is free software; the Free Software Foundation
 14083  14083   gives unlimited permission to copy, distribute and modify it."
 14084  14084   

Changes to src/btree.c.

   742    742   
   743    743   /*
   744    744   ** Determine whether or not a cursor has moved from the position it
   745    745   ** was last placed at.  Cursors can move when the row they are pointing
   746    746   ** at is deleted out from under them.
   747    747   **
   748    748   ** This routine returns an error code if something goes wrong.  The
   749         -** integer *pHasMoved is set to one if the cursor has moved and 0 if not.
          749  +** integer *pHasMoved is set as follows:
          750  +**
          751  +**    0:   The cursor is unchanged
          752  +**    1:   The cursor is still pointing at the same row, but the pointers
          753  +**         returned by sqlite3BtreeKeyFetch() or sqlite3BtreeDataFetch()
          754  +**         might now be invalid because of a balance() or other change to the
          755  +**         b-tree.
          756  +**    2:   The cursor is no longer pointing to the row.  The row might have
          757  +**         been deleted out from under the cursor.
   750    758   */
   751    759   int sqlite3BtreeCursorHasMoved(BtCursor *pCur, int *pHasMoved){
   752    760     int rc;
   753    761   
          762  +  if( pCur->eState==CURSOR_VALID ){
          763  +    *pHasMoved = 0;
          764  +    return SQLITE_OK;
          765  +  }
   754    766     rc = restoreCursorPosition(pCur);
   755    767     if( rc ){
   756         -    *pHasMoved = 1;
          768  +    *pHasMoved = 2;
   757    769       return rc;
   758    770     }
   759    771     if( pCur->eState!=CURSOR_VALID || NEVER(pCur->skipNext!=0) ){
          772  +    *pHasMoved = 2;
          773  +  }else{
   760    774       *pHasMoved = 1;
   761         -  }else{
   762         -    *pHasMoved = 0;
   763    775     }
   764    776     return SQLITE_OK;
   765    777   }
   766    778   
   767    779   #ifndef SQLITE_OMIT_AUTOVACUUM
   768    780   /*
   769    781   ** Given a page number of a regular database page, return the page
................................................................................
  2157   2169     assert( sqlite3_mutex_held(p->db->mutex) );
  2158   2170     sqlite3BtreeEnter(p);
  2159   2171     sqlite3PagerSetCachesize(pBt->pPager, mxPage);
  2160   2172     sqlite3BtreeLeave(p);
  2161   2173     return SQLITE_OK;
  2162   2174   }
  2163   2175   
         2176  +#if SQLITE_MAX_MMAP_SIZE>0
  2164   2177   /*
  2165   2178   ** Change the limit on the amount of the database file that may be
  2166   2179   ** memory mapped.
  2167   2180   */
  2168   2181   int sqlite3BtreeSetMmapLimit(Btree *p, sqlite3_int64 szMmap){
  2169   2182     BtShared *pBt = p->pBt;
  2170   2183     assert( sqlite3_mutex_held(p->db->mutex) );
  2171   2184     sqlite3BtreeEnter(p);
  2172   2185     sqlite3PagerSetMmapLimit(pBt->pPager, szMmap);
  2173   2186     sqlite3BtreeLeave(p);
  2174   2187     return SQLITE_OK;
  2175   2188   }
         2189  +#endif /* SQLITE_MAX_MMAP_SIZE>0 */
  2176   2190   
  2177   2191   /*
  2178   2192   ** Change the way data is synced to disk in order to increase or decrease
  2179   2193   ** how well the database resists damage due to OS crashes and power
  2180   2194   ** failures.  Level 1 is the same as asynchronous (no syncs() occur and
  2181   2195   ** there is a high probability of damage)  Level 2 is the default.  There
  2182   2196   ** is a very low but non-zero probability of damage.  Level 3 reduces the
................................................................................
  4182   4196     u32 *pAmt            /* Write the number of available bytes here */
  4183   4197   ){
  4184   4198     assert( pCur!=0 && pCur->iPage>=0 && pCur->apPage[pCur->iPage]);
  4185   4199     assert( pCur->eState==CURSOR_VALID );
  4186   4200     assert( sqlite3_mutex_held(pCur->pBtree->db->mutex) );
  4187   4201     assert( cursorHoldsMutex(pCur) );
  4188   4202     assert( pCur->aiIdx[pCur->iPage]<pCur->apPage[pCur->iPage]->nCell );
         4203  +  assert( pCur->info.nSize>0 );
         4204  +#if 0
  4189   4205     if( pCur->info.nSize==0 ){
  4190   4206       btreeParseCell(pCur->apPage[pCur->iPage], pCur->aiIdx[pCur->iPage],
  4191   4207                      &pCur->info);
  4192   4208     }
         4209  +#endif
  4193   4210     *pAmt = pCur->info.nLocal;
  4194   4211     return (void*)(pCur->info.pCell + pCur->info.nHeader);
  4195   4212   }
  4196   4213   
  4197   4214   
  4198   4215   /*
  4199   4216   ** For the entry that cursor pCur is point to, return as
................................................................................
  7418   7435       ** a no-op).  */
  7419   7436       invalidateIncrblobCursors(p, 0, 1);
  7420   7437       rc = clearDatabasePage(pBt, (Pgno)iTable, 0, pnChange);
  7421   7438     }
  7422   7439     sqlite3BtreeLeave(p);
  7423   7440     return rc;
  7424   7441   }
         7442  +
         7443  +/*
         7444  +** Delete all information from the single table that pCur is open on.
         7445  +**
         7446  +** This routine only work for pCur on an ephemeral table.
         7447  +*/
         7448  +int sqlite3BtreeClearTableOfCursor(BtCursor *pCur){
         7449  +  return sqlite3BtreeClearTable(pCur->pBtree, pCur->pgnoRoot, 0);
         7450  +}
  7425   7451   
  7426   7452   /*
  7427   7453   ** Erase all information in a table and add the root of the table to
  7428   7454   ** the freelist.  Except, the root of the principle table (the one on
  7429   7455   ** page 1) is never added to the freelist.
  7430   7456   **
  7431   7457   ** This routine will fail with SQLITE_LOCKED if there are any open

Changes to src/btree.h.

    59     59   #define BTREE_OMIT_JOURNAL  1  /* Do not create or use a rollback journal */
    60     60   #define BTREE_MEMORY        2  /* This is an in-memory DB */
    61     61   #define BTREE_SINGLE        4  /* The file contains at most 1 b-tree */
    62     62   #define BTREE_UNORDERED     8  /* Use of a hash implementation is OK */
    63     63   
    64     64   int sqlite3BtreeClose(Btree*);
    65     65   int sqlite3BtreeSetCacheSize(Btree*,int);
    66         -int sqlite3BtreeSetMmapLimit(Btree*,sqlite3_int64);
           66  +#if SQLITE_MAX_MMAP_SIZE>0
           67  +  int sqlite3BtreeSetMmapLimit(Btree*,sqlite3_int64);
           68  +#endif
    67     69   int sqlite3BtreeSetPagerFlags(Btree*,unsigned);
    68     70   int sqlite3BtreeSyncDisabled(Btree*);
    69     71   int sqlite3BtreeSetPageSize(Btree *p, int nPagesize, int nReserve, int eFix);
    70     72   int sqlite3BtreeGetPageSize(Btree*);
    71     73   int sqlite3BtreeMaxPageCount(Btree*,int);
    72     74   u32 sqlite3BtreeLastPage(Btree*);
    73     75   int sqlite3BtreeSecureDelete(Btree*,int);
................................................................................
   109    111   ** indices.)
   110    112   */
   111    113   #define BTREE_INTKEY     1    /* Table has only 64-bit signed integer keys */
   112    114   #define BTREE_BLOBKEY    2    /* Table has keys only - no data */
   113    115   
   114    116   int sqlite3BtreeDropTable(Btree*, int, int*);
   115    117   int sqlite3BtreeClearTable(Btree*, int, int*);
          118  +int sqlite3BtreeClearTableOfCursor(BtCursor*);
   116    119   void sqlite3BtreeTripAllCursors(Btree*, int);
   117    120   
   118    121   void sqlite3BtreeGetMeta(Btree *pBtree, int idx, u32 *pValue);
   119    122   int sqlite3BtreeUpdateMeta(Btree*, int idx, u32 value);
   120    123   
   121    124   int sqlite3BtreeNewDb(Btree *p);
   122    125   

Changes to src/expr.c.

    29     29   ** SELECT * FROM t1 WHERE a;
    30     30   ** SELECT a AS b FROM t1 WHERE b;
    31     31   ** SELECT * FROM t1 WHERE (select a from t1);
    32     32   */
    33     33   char sqlite3ExprAffinity(Expr *pExpr){
    34     34     int op;
    35     35     pExpr = sqlite3ExprSkipCollate(pExpr);
           36  +  if( pExpr->flags & EP_Generic ) return SQLITE_AFF_NONE;
    36     37     op = pExpr->op;
    37     38     if( op==TK_SELECT ){
    38     39       assert( pExpr->flags&EP_xIsSelect );
    39     40       return sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr);
    40     41     }
    41     42   #ifndef SQLITE_OMIT_CAST
    42     43     if( op==TK_CAST ){
................................................................................
    61     62   ** Set the collating sequence for expression pExpr to be the collating
    62     63   ** sequence named by pToken.   Return a pointer to a new Expr node that
    63     64   ** implements the COLLATE operator.
    64     65   **
    65     66   ** If a memory allocation error occurs, that fact is recorded in pParse->db
    66     67   ** and the pExpr parameter is returned unchanged.
    67     68   */
    68         -Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr *pExpr, Token *pCollName){
           69  +Expr *sqlite3ExprAddCollateToken(
           70  +  Parse *pParse,           /* Parsing context */
           71  +  Expr *pExpr,             /* Add the "COLLATE" clause to this expression */
           72  +  const Token *pCollName   /* Name of collating sequence */
           73  +){
    69     74     if( pCollName->n>0 ){
    70     75       Expr *pNew = sqlite3ExprAlloc(pParse->db, TK_COLLATE, pCollName, 1);
    71     76       if( pNew ){
    72     77         pNew->pLeft = pExpr;
    73     78         pNew->flags |= EP_Collate|EP_Skip;
    74     79         pExpr = pNew;
    75     80       }
................................................................................
   114    119   */
   115    120   CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr){
   116    121     sqlite3 *db = pParse->db;
   117    122     CollSeq *pColl = 0;
   118    123     Expr *p = pExpr;
   119    124     while( p ){
   120    125       int op = p->op;
          126  +    if( p->flags & EP_Generic ) break;
   121    127       if( op==TK_CAST || op==TK_UPLUS ){
   122    128         p = p->pLeft;
   123    129         continue;
   124    130       }
   125    131       if( op==TK_COLLATE || (op==TK_REGISTER && p->op2==TK_COLLATE) ){
   126    132         pColl = sqlite3GetCollSeq(pParse, ENC(db), 0, p->u.zToken);
   127    133         break;
................................................................................
   945    951   ExprList *sqlite3ExprListDup(sqlite3 *db, ExprList *p, int flags){
   946    952     ExprList *pNew;
   947    953     struct ExprList_item *pItem, *pOldItem;
   948    954     int i;
   949    955     if( p==0 ) return 0;
   950    956     pNew = sqlite3DbMallocRaw(db, sizeof(*pNew) );
   951    957     if( pNew==0 ) return 0;
   952         -  pNew->iECursor = 0;
   953    958     pNew->nExpr = i = p->nExpr;
   954    959     if( (flags & EXPRDUP_REDUCE)==0 ) for(i=1; i<p->nExpr; i+=i){}
   955    960     pNew->a = pItem = sqlite3DbMallocRaw(db,  i*sizeof(p->a[0]) );
   956    961     if( pItem==0 ){
   957    962       sqlite3DbFree(db, pNew);
   958    963       return 0;
   959    964     } 
................................................................................
  1058   1063     pNew->pLimit = sqlite3ExprDup(db, p->pLimit, flags);
  1059   1064     pNew->pOffset = sqlite3ExprDup(db, p->pOffset, flags);
  1060   1065     pNew->iLimit = 0;
  1061   1066     pNew->iOffset = 0;
  1062   1067     pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  1063   1068     pNew->addrOpenEphm[0] = -1;
  1064   1069     pNew->addrOpenEphm[1] = -1;
  1065         -  pNew->addrOpenEphm[2] = -1;
  1066   1070     pNew->nSelectRow = p->nSelectRow;
  1067   1071     pNew->pWith = withDup(db, p->pWith);
  1068   1072     return pNew;
  1069   1073   }
  1070   1074   #else
  1071   1075   Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){
  1072   1076     assert( p==0 );
................................................................................
  1626   1630       u32 savedNQueryLoop = pParse->nQueryLoop;
  1627   1631       int rMayHaveNull = 0;
  1628   1632       eType = IN_INDEX_EPH;
  1629   1633       if( prNotFound ){
  1630   1634         *prNotFound = rMayHaveNull = ++pParse->nMem;
  1631   1635         sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound);
  1632   1636       }else{
  1633         -      testcase( pParse->nQueryLoop>0 );
  1634   1637         pParse->nQueryLoop = 0;
  1635   1638         if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){
  1636   1639           eType = IN_INDEX_ROWID;
  1637   1640         }
  1638   1641       }
  1639   1642       sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
  1640   1643       pParse->nQueryLoop = savedNQueryLoop;
................................................................................
  2331   2334   ** Generate code to move content from registers iFrom...iFrom+nReg-1
  2332   2335   ** over to iTo..iTo+nReg-1. Keep the column cache up-to-date.
  2333   2336   */
  2334   2337   void sqlite3ExprCodeMove(Parse *pParse, int iFrom, int iTo, int nReg){
  2335   2338     int i;
  2336   2339     struct yColCache *p;
  2337   2340     assert( iFrom>=iTo+nReg || iFrom+nReg<=iTo );
  2338         -  sqlite3VdbeAddOp3(pParse->pVdbe, OP_Move, iFrom, iTo, nReg-1);
         2341  +  sqlite3VdbeAddOp3(pParse->pVdbe, OP_Move, iFrom, iTo, nReg);
  2339   2342     for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
  2340   2343       int x = p->iReg;
  2341   2344       if( x>=iFrom && x<iFrom+nReg ){
  2342   2345         p->iReg += iTo-iFrom;
  2343   2346       }
  2344   2347     }
  2345   2348   }
................................................................................
  2732   2735               testcase( pDef->funcFlags & OPFLAG_LENGTHARG );
  2733   2736               pFarg->a[0].pExpr->op2 = 
  2734   2737                     pDef->funcFlags & (OPFLAG_LENGTHARG|OPFLAG_TYPEOFARG);
  2735   2738             }
  2736   2739           }
  2737   2740   
  2738   2741           sqlite3ExprCachePush(pParse);     /* Ticket 2ea2425d34be */
  2739         -        sqlite3ExprCodeExprList(pParse, pFarg, r1, 
         2742  +        sqlite3ExprCodeExprList(pParse, pFarg, r1,
  2740   2743                                   SQLITE_ECEL_DUP|SQLITE_ECEL_FACTOR);
  2741   2744           sqlite3ExprCachePop(pParse, 1);   /* Ticket 2ea2425d34be */
  2742   2745         }else{
  2743   2746           r1 = 0;
  2744   2747         }
  2745   2748   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2746   2749         /* Possibly overload the function if the first argument is

Changes to src/os_unix.c.

   319    319   */
   320    320   static int posixFchown(int fd, uid_t uid, gid_t gid){
   321    321     return geteuid() ? 0 : fchown(fd,uid,gid);
   322    322   }
   323    323   
   324    324   /* Forward reference */
   325    325   static int openDirectory(const char*, int*);
          326  +static int unixGetpagesize(void);
   326    327   
   327    328   /*
   328    329   ** Many system calls are accessed through pointer-to-functions so that
   329    330   ** they may be overridden at runtime to facilitate fault injection during
   330    331   ** testing and sandboxing.  The following array holds the names and pointers
   331    332   ** to all overrideable system calls.
   332    333   */
................................................................................
   441    442   #if HAVE_MREMAP
   442    443     { "mremap",       (sqlite3_syscall_ptr)mremap,          0 },
   443    444   #else
   444    445     { "mremap",       (sqlite3_syscall_ptr)0,               0 },
   445    446   #endif
   446    447   #define osMremap ((void*(*)(void*,size_t,size_t,int,...))aSyscall[23].pCurrent)
   447    448   #endif
          449  +
          450  +  { "getpagesize",  (sqlite3_syscall_ptr)unixGetpagesize, 0 },
          451  +#define osGetpagesize ((int(*)(void))aSyscall[24].pCurrent)
   448    452   
   449    453   }; /* End of the overrideable system calls */
   450    454   
   451    455   /*
   452    456   ** This is the xSetSystemCall() method of sqlite3_vfs for all of the
   453    457   ** "unix" VFSes.  Return SQLITE_OK opon successfully updating the
   454    458   ** system call pointer, or SQLITE_NOTFOUND if there is no configurable
................................................................................
  4101   4105              pShmNode->sharedMask, pShmNode->exclMask));
  4102   4106     }
  4103   4107   #endif
  4104   4108   
  4105   4109     return rc;        
  4106   4110   }
  4107   4111   
         4112  +/*
         4113  +** Return the system page size.
         4114  +**
         4115  +** This function should not be called directly by other code in this file. 
         4116  +** Instead, it should be called via macro osGetpagesize().
         4117  +*/
         4118  +static int unixGetpagesize(void){
         4119  +#if defined(_BSD_SOURCE)
         4120  +  return getpagesize();
         4121  +#else
         4122  +  return (int)sysconf(_SC_PAGESIZE);
         4123  +#endif
         4124  +}
         4125  +
         4126  +/*
         4127  +** Return the minimum number of 32KB shm regions that should be mapped at
         4128  +** a time, assuming that each mapping must be an integer multiple of the
         4129  +** current system page-size.
         4130  +**
         4131  +** Usually, this is 1. The exception seems to be systems that are configured
         4132  +** to use 64KB pages - in this case each mapping must cover at least two
         4133  +** shm regions.
         4134  +*/
         4135  +static int unixShmRegionPerMap(void){
         4136  +  int shmsz = 32*1024;            /* SHM region size */
         4137  +  int pgsz = osGetpagesize();   /* System page size */
         4138  +  assert( ((pgsz-1)&pgsz)==0 );   /* Page size must be a power of 2 */
         4139  +  if( pgsz<shmsz ) return 1;
         4140  +  return pgsz/shmsz;
         4141  +}
  4108   4142   
  4109   4143   /*
  4110   4144   ** Purge the unixShmNodeList list of all entries with unixShmNode.nRef==0.
  4111   4145   **
  4112   4146   ** This is not a VFS shared-memory method; it is a utility function called
  4113   4147   ** by VFS shared-memory methods.
  4114   4148   */
  4115   4149   static void unixShmPurge(unixFile *pFd){
  4116   4150     unixShmNode *p = pFd->pInode->pShmNode;
  4117   4151     assert( unixMutexHeld() );
  4118   4152     if( p && p->nRef==0 ){
         4153  +    int nShmPerMap = unixShmRegionPerMap();
  4119   4154       int i;
  4120   4155       assert( p->pInode==pFd->pInode );
  4121   4156       sqlite3_mutex_free(p->mutex);
  4122         -    for(i=0; i<p->nRegion; i++){
         4157  +    for(i=0; i<p->nRegion; i+=nShmPerMap){
  4123   4158         if( p->h>=0 ){
  4124   4159           osMunmap(p->apRegion[i], p->szRegion);
  4125   4160         }else{
  4126   4161           sqlite3_free(p->apRegion[i]);
  4127   4162         }
  4128   4163       }
  4129   4164       sqlite3_free(p->apRegion);
................................................................................
  4322   4357     int bExtend,                    /* True to extend file if necessary */
  4323   4358     void volatile **pp              /* OUT: Mapped memory */
  4324   4359   ){
  4325   4360     unixFile *pDbFd = (unixFile*)fd;
  4326   4361     unixShm *p;
  4327   4362     unixShmNode *pShmNode;
  4328   4363     int rc = SQLITE_OK;
         4364  +  int nShmPerMap = unixShmRegionPerMap();
         4365  +  int nReqRegion;
  4329   4366   
  4330   4367     /* If the shared-memory file has not yet been opened, open it now. */
  4331   4368     if( pDbFd->pShm==0 ){
  4332   4369       rc = unixOpenSharedMemory(pDbFd);
  4333   4370       if( rc!=SQLITE_OK ) return rc;
  4334   4371     }
  4335   4372   
................................................................................
  4337   4374     pShmNode = p->pShmNode;
  4338   4375     sqlite3_mutex_enter(pShmNode->mutex);
  4339   4376     assert( szRegion==pShmNode->szRegion || pShmNode->nRegion==0 );
  4340   4377     assert( pShmNode->pInode==pDbFd->pInode );
  4341   4378     assert( pShmNode->h>=0 || pDbFd->pInode->bProcessLock==1 );
  4342   4379     assert( pShmNode->h<0 || pDbFd->pInode->bProcessLock==0 );
  4343   4380   
  4344         -  if( pShmNode->nRegion<=iRegion ){
         4381  +  /* Minimum number of regions required to be mapped. */
         4382  +  nReqRegion = ((iRegion+nShmPerMap) / nShmPerMap) * nShmPerMap;
         4383  +
         4384  +  if( pShmNode->nRegion<nReqRegion ){
  4345   4385       char **apNew;                      /* New apRegion[] array */
  4346         -    int nByte = (iRegion+1)*szRegion;  /* Minimum required file size */
         4386  +    int nByte = nReqRegion*szRegion;   /* Minimum required file size */
  4347   4387       struct stat sStat;                 /* Used by fstat() */
  4348   4388   
  4349   4389       pShmNode->szRegion = szRegion;
  4350   4390   
  4351   4391       if( pShmNode->h>=0 ){
  4352   4392         /* The requested region is not mapped into this processes address space.
  4353   4393         ** Check to see if it has been allocated (i.e. if the wal-index file is
................................................................................
  4388   4428             }
  4389   4429           }
  4390   4430         }
  4391   4431       }
  4392   4432   
  4393   4433       /* Map the requested memory region into this processes address space. */
  4394   4434       apNew = (char **)sqlite3_realloc(
  4395         -        pShmNode->apRegion, (iRegion+1)*sizeof(char *)
         4435  +        pShmNode->apRegion, nReqRegion*sizeof(char *)
  4396   4436       );
  4397   4437       if( !apNew ){
  4398   4438         rc = SQLITE_IOERR_NOMEM;
  4399   4439         goto shmpage_out;
  4400   4440       }
  4401   4441       pShmNode->apRegion = apNew;
  4402         -    while(pShmNode->nRegion<=iRegion){
         4442  +    while( pShmNode->nRegion<nReqRegion ){
         4443  +      int nMap = szRegion*nShmPerMap;
         4444  +      int i;
  4403   4445         void *pMem;
  4404   4446         if( pShmNode->h>=0 ){
  4405         -        pMem = osMmap(0, szRegion,
         4447  +        pMem = osMmap(0, nMap,
  4406   4448               pShmNode->isReadonly ? PROT_READ : PROT_READ|PROT_WRITE, 
  4407   4449               MAP_SHARED, pShmNode->h, szRegion*(i64)pShmNode->nRegion
  4408   4450           );
  4409   4451           if( pMem==MAP_FAILED ){
  4410   4452             rc = unixLogError(SQLITE_IOERR_SHMMAP, "mmap", pShmNode->zFilename);
  4411   4453             goto shmpage_out;
  4412   4454           }
................................................................................
  4414   4456           pMem = sqlite3_malloc(szRegion);
  4415   4457           if( pMem==0 ){
  4416   4458             rc = SQLITE_NOMEM;
  4417   4459             goto shmpage_out;
  4418   4460           }
  4419   4461           memset(pMem, 0, szRegion);
  4420   4462         }
  4421         -      pShmNode->apRegion[pShmNode->nRegion] = pMem;
  4422         -      pShmNode->nRegion++;
         4463  +
         4464  +      for(i=0; i<nShmPerMap; i++){
         4465  +        pShmNode->apRegion[pShmNode->nRegion+i] = &((char*)pMem)[szRegion*i];
         4466  +      }
         4467  +      pShmNode->nRegion += nShmPerMap;
  4423   4468       }
  4424   4469     }
  4425   4470   
  4426   4471   shmpage_out:
  4427   4472     if( pShmNode->nRegion>iRegion ){
  4428   4473       *pp = pShmNode->apRegion[iRegion];
  4429   4474     }else{
................................................................................
  4629   4674       osMunmap(pFd->pMapRegion, pFd->mmapSizeActual);
  4630   4675       pFd->pMapRegion = 0;
  4631   4676       pFd->mmapSize = 0;
  4632   4677       pFd->mmapSizeActual = 0;
  4633   4678     }
  4634   4679   }
  4635   4680   
  4636         -/*
  4637         -** Return the system page size.
  4638         -*/
  4639         -static int unixGetPagesize(void){
  4640         -#if HAVE_MREMAP
  4641         -  return 512;
  4642         -#elif defined(_BSD_SOURCE)
  4643         -  return getpagesize();
  4644         -#else
  4645         -  return (int)sysconf(_SC_PAGESIZE);
  4646         -#endif
  4647         -}
  4648         -
  4649   4681   /*
  4650   4682   ** Attempt to set the size of the memory mapping maintained by file 
  4651   4683   ** descriptor pFd to nNew bytes. Any existing mapping is discarded.
  4652   4684   **
  4653   4685   ** If successful, this function sets the following variables:
  4654   4686   **
  4655   4687   **       unixFile.pMapRegion
................................................................................
  4678   4710     assert( nNew>0 );
  4679   4711     assert( pFd->mmapSizeActual>=pFd->mmapSize );
  4680   4712     assert( MAP_FAILED!=0 );
  4681   4713   
  4682   4714     if( (pFd->ctrlFlags & UNIXFILE_RDONLY)==0 ) flags |= PROT_WRITE;
  4683   4715   
  4684   4716     if( pOrig ){
  4685         -    const int szSyspage = unixGetPagesize();
         4717  +#if HAVE_MREMAP
         4718  +    i64 nReuse = pFd->mmapSize;
         4719  +#else
         4720  +    const int szSyspage = osGetpagesize();
  4686   4721       i64 nReuse = (pFd->mmapSize & ~(szSyspage-1));
         4722  +#endif
  4687   4723       u8 *pReq = &pOrig[nReuse];
  4688   4724   
  4689   4725       /* Unmap any pages of the existing mapping that cannot be reused. */
  4690   4726       if( nReuse!=nOrig ){
  4691   4727         osMunmap(pReq, nOrig-nReuse);
  4692   4728       }
  4693   4729   
................................................................................
  7425   7461       UNIXVFS("unix-proxy",    proxyIoFinder ),
  7426   7462   #endif
  7427   7463     };
  7428   7464     unsigned int i;          /* Loop counter */
  7429   7465   
  7430   7466     /* Double-check that the aSyscall[] array has been constructed
  7431   7467     ** correctly.  See ticket [bb3a86e890c8e96ab] */
  7432         -  assert( ArraySize(aSyscall)==24 );
         7468  +  assert( ArraySize(aSyscall)==25 );
  7433   7469   
  7434   7470     /* Register all VFSes defined in the aVfs[] array */
  7435   7471     for(i=0; i<(sizeof(aVfs)/sizeof(sqlite3_vfs)); i++){
  7436   7472       sqlite3_vfs_register(&aVfs[i], i==0);
  7437   7473     }
  7438   7474     return SQLITE_OK; 
  7439   7475   }

Changes to src/parse.y.

  1016   1016         **      expr1 NOT IN ()
  1017   1017         **
  1018   1018         ** simplify to constants 0 (false) and 1 (true), respectively,
  1019   1019         ** regardless of the value of expr1.
  1020   1020         */
  1021   1021         A.pExpr = sqlite3PExpr(pParse, TK_INTEGER, 0, 0, &sqlite3IntTokens[N]);
  1022   1022         sqlite3ExprDelete(pParse->db, X.pExpr);
         1023  +    }else if( Y->nExpr==1 ){
         1024  +      /* Expressions of the form:
         1025  +      **
         1026  +      **      expr1 IN (?1)
         1027  +      **      expr1 NOT IN (?2)
         1028  +      **
         1029  +      ** with exactly one value on the RHS can be simplified to something
         1030  +      ** like this:
         1031  +      **
         1032  +      **      expr1 == ?1
         1033  +      **      expr1 <> ?2
         1034  +      **
         1035  +      ** But, the RHS of the == or <> is marked with the EP_Generic flag
         1036  +      ** so that it may not contribute to the computation of comparison
         1037  +      ** affinity or the collating sequence to use for comparison.  Otherwise,
         1038  +      ** the semantics would be subtly different from IN or NOT IN.
         1039  +      */
         1040  +      Expr *pRHS = Y->a[0].pExpr;
         1041  +      Y->a[0].pExpr = 0;
         1042  +      sqlite3ExprListDelete(pParse->db, Y);
         1043  +      /* pRHS cannot be NULL because a malloc error would have been detected
         1044  +      ** before now and control would have never reached this point */
         1045  +      if( ALWAYS(pRHS) ){
         1046  +        pRHS->flags &= ~EP_Collate;
         1047  +        pRHS->flags |= EP_Generic;
         1048  +      }
         1049  +      A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0);
  1023   1050       }else{
  1024   1051         A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
  1025   1052         if( A.pExpr ){
  1026   1053           A.pExpr->x.pList = Y;
  1027   1054           sqlite3ExprSetHeight(pParse, A.pExpr);
  1028   1055         }else{
  1029   1056           sqlite3ExprListDelete(pParse->db, Y);

Changes to src/pragma.c.

  1871   1871         /* Do the b-tree integrity checks */
  1872   1872         sqlite3VdbeAddOp3(v, OP_IntegrityCk, 2, cnt, 1);
  1873   1873         sqlite3VdbeChangeP5(v, (u8)i);
  1874   1874         addr = sqlite3VdbeAddOp1(v, OP_IsNull, 2); VdbeCoverage(v);
  1875   1875         sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0,
  1876   1876            sqlite3MPrintf(db, "*** in database %s ***\n", db->aDb[i].zName),
  1877   1877            P4_DYNAMIC);
  1878         -      sqlite3VdbeAddOp2(v, OP_Move, 2, 4);
         1878  +      sqlite3VdbeAddOp3(v, OP_Move, 2, 4, 1);
  1879   1879         sqlite3VdbeAddOp3(v, OP_Concat, 4, 3, 2);
  1880   1880         sqlite3VdbeAddOp2(v, OP_ResultRow, 2, 1);
  1881   1881         sqlite3VdbeJumpHere(v, addr);
  1882   1882   
  1883   1883         /* Make sure all the indices are constructed correctly.
  1884   1884         */
  1885   1885         for(x=sqliteHashFirst(pTbls); x && !isQuick; x=sqliteHashNext(x)){

Changes to src/printf.c.

   130    130     d = digit;
   131    131     digit += '0';
   132    132     *val = (*val - d)*10.0;
   133    133     return (char)digit;
   134    134   }
   135    135   #endif /* SQLITE_OMIT_FLOATING_POINT */
   136    136   
   137         -/*
   138         -** Append N space characters to the given string buffer.
   139         -*/
   140         -void sqlite3AppendSpace(StrAccum *pAccum, int N){
   141         -  static const char zSpaces[] = "                             ";
   142         -  while( N>=(int)sizeof(zSpaces)-1 ){
   143         -    sqlite3StrAccumAppend(pAccum, zSpaces, sizeof(zSpaces)-1);
   144         -    N -= sizeof(zSpaces)-1;
   145         -  }
   146         -  if( N>0 ){
   147         -    sqlite3StrAccumAppend(pAccum, zSpaces, N);
   148         -  }
   149         -}
   150         -
   151    137   /*
   152    138   ** Set the StrAccum object to an error mode.
   153    139   */
   154    140   static void setStrAccumError(StrAccum *p, u8 eError){
   155    141     p->accError = eError;
   156    142     p->nAlloc = 0;
   157    143   }
................................................................................
   233    219       }
   234    220       useIntern = bFlags & SQLITE_PRINTF_INTERNAL;
   235    221     }else{
   236    222       bArgList = useIntern = 0;
   237    223     }
   238    224     for(; (c=(*fmt))!=0; ++fmt){
   239    225       if( c!='%' ){
   240         -      int amt;
   241    226         bufpt = (char *)fmt;
   242         -      amt = 1;
   243         -      while( (c=(*++fmt))!='%' && c!=0 ) amt++;
   244         -      sqlite3StrAccumAppend(pAccum, bufpt, amt);
          227  +      while( (c=(*++fmt))!='%' && c!=0 ){};
          228  +      sqlite3StrAccumAppend(pAccum, bufpt, (int)(fmt - bufpt));
   245    229         if( c==0 ) break;
   246    230       }
   247    231       if( (c=(*++fmt))==0 ){
   248    232         sqlite3StrAccumAppend(pAccum, "%", 1);
   249    233         break;
   250    234       }
   251    235       /* Find out what flags are present */
................................................................................
   418    402             if( x>=4 || (longvalue/10)%10==1 ){
   419    403               x = 0;
   420    404             }
   421    405             *(--bufpt) = zOrd[x*2+1];
   422    406             *(--bufpt) = zOrd[x*2];
   423    407           }
   424    408           {
   425         -          register const char *cset;      /* Use registers for speed */
   426         -          register int base;
   427         -          cset = &aDigits[infop->charset];
   428         -          base = infop->base;
          409  +          const char *cset = &aDigits[infop->charset];
          410  +          u8 base = infop->base;
   429    411             do{                                           /* Convert to ascii */
   430    412               *(--bufpt) = cset[longvalue%base];
   431    413               longvalue = longvalue/base;
   432    414             }while( longvalue>0 );
   433    415           }
   434    416           length = (int)(&zOut[nOut-1]-bufpt);
   435    417           for(idx=precision-length; idx>0; idx--){
................................................................................
   725    707         }
   726    708       }/* End switch over the format type */
   727    709       /*
   728    710       ** The text of the conversion is pointed to by "bufpt" and is
   729    711       ** "length" characters long.  The field width is "width".  Do
   730    712       ** the output.
   731    713       */
   732         -    if( !flag_leftjustify ){
   733         -      register int nspace;
   734         -      nspace = width-length;
   735         -      if( nspace>0 ){
   736         -        sqlite3AppendSpace(pAccum, nspace);
   737         -      }
   738         -    }
   739         -    if( length>0 ){
   740         -      sqlite3StrAccumAppend(pAccum, bufpt, length);
   741         -    }
   742         -    if( flag_leftjustify ){
   743         -      register int nspace;
   744         -      nspace = width-length;
   745         -      if( nspace>0 ){
   746         -        sqlite3AppendSpace(pAccum, nspace);
   747         -      }
   748         -    }
          714  +    width -= length;
          715  +    if( width>0 && !flag_leftjustify ) sqlite3AppendSpace(pAccum, width);
          716  +    sqlite3StrAccumAppend(pAccum, bufpt, length);
          717  +    if( width>0 && flag_leftjustify ) sqlite3AppendSpace(pAccum, width);
          718  +
   749    719       if( zExtra ) sqlite3_free(zExtra);
   750    720     }/* End for loop over the format string */
   751    721   } /* End of function */
   752    722   
   753    723   /*
   754         -** Append N bytes of text from z to the StrAccum object.
          724  +** Enlarge the memory allocation on a StrAccum object so that it is
          725  +** able to accept at least N more bytes of text.
          726  +**
          727  +** Return the number of bytes of text that StrAccum is able to accept
          728  +** after the attempted enlargement.  The value returned might be zero.
          729  +*/
          730  +static int sqlite3StrAccumEnlarge(StrAccum *p, int N){
          731  +  char *zNew;
          732  +  assert( p->nChar+N >= p->nAlloc ); /* Only called if really needed */
          733  +  if( p->accError ){
          734  +    testcase(p->accError==STRACCUM_TOOBIG);
          735  +    testcase(p->accError==STRACCUM_NOMEM);
          736  +    return 0;
          737  +  }
          738  +  if( !p->useMalloc ){
          739  +    N = p->nAlloc - p->nChar - 1;
          740  +    setStrAccumError(p, STRACCUM_TOOBIG);
          741  +    return N;
          742  +  }else{
          743  +    char *zOld = (p->zText==p->zBase ? 0 : p->zText);
          744  +    i64 szNew = p->nChar;
          745  +    szNew += N + 1;
          746  +    if( szNew > p->mxAlloc ){
          747  +      sqlite3StrAccumReset(p);
          748  +      setStrAccumError(p, STRACCUM_TOOBIG);
          749  +      return 0;
          750  +    }else{
          751  +      p->nAlloc = (int)szNew;
          752  +    }
          753  +    if( p->useMalloc==1 ){
          754  +      zNew = sqlite3DbRealloc(p->db, zOld, p->nAlloc);
          755  +    }else{
          756  +      zNew = sqlite3_realloc(zOld, p->nAlloc);
          757  +    }
          758  +    if( zNew ){
          759  +      if( zOld==0 && p->nChar>0 ) memcpy(zNew, p->zText, p->nChar);
          760  +      p->zText = zNew;
          761  +    }else{
          762  +      sqlite3StrAccumReset(p);
          763  +      setStrAccumError(p, STRACCUM_NOMEM);
          764  +      return 0;
          765  +    }
          766  +  }
          767  +  return N;
          768  +}
          769  +
          770  +/*
          771  +** Append N space characters to the given string buffer.
          772  +*/
          773  +void sqlite3AppendSpace(StrAccum *p, int N){
          774  +  if( p->nChar+N >= p->nAlloc && (N = sqlite3StrAccumEnlarge(p, N))<=0 ) return;
          775  +  while( (N--)>0 ) p->zText[p->nChar++] = ' ';
          776  +}
          777  +
          778  +/*
          779  +** The StrAccum "p" is not large enough to accept N new bytes of z[].
          780  +** So enlarge if first, then do the append.
          781  +**
          782  +** This is a helper routine to sqlite3StrAccumAppend() that does special-case
          783  +** work (enlarging the buffer) using tail recursion, so that the
          784  +** sqlite3StrAccumAppend() routine can use fast calling semantics.
          785  +*/
          786  +static void enlargeAndAppend(StrAccum *p, const char *z, int N){
          787  +  N = sqlite3StrAccumEnlarge(p, N);
          788  +  if( N>0 ){
          789  +    memcpy(&p->zText[p->nChar], z, N);
          790  +    p->nChar += N;
          791  +  }
          792  +}
          793  +
          794  +/*
          795  +** Append N bytes of text from z to the StrAccum object.  Increase the
          796  +** size of the memory allocation for StrAccum if necessary.
   755    797   */
   756    798   void sqlite3StrAccumAppend(StrAccum *p, const char *z, int N){
   757    799     assert( z!=0 );
   758    800     assert( p->zText!=0 || p->nChar==0 || p->accError );
   759    801     assert( N>=0 );
   760    802     assert( p->accError==0 || p->nAlloc==0 );
   761    803     if( p->nChar+N >= p->nAlloc ){
   762         -    char *zNew;
   763         -    if( p->accError ){
   764         -      testcase(p->accError==STRACCUM_TOOBIG);
   765         -      testcase(p->accError==STRACCUM_NOMEM);
   766         -      return;
   767         -    }
   768         -    if( !p->useMalloc ){
   769         -      N = p->nAlloc - p->nChar - 1;
   770         -      setStrAccumError(p, STRACCUM_TOOBIG);
   771         -      if( N<=0 ){
   772         -        return;
   773         -      }
   774         -    }else{
   775         -      char *zOld = (p->zText==p->zBase ? 0 : p->zText);
   776         -      i64 szNew = p->nChar;
   777         -      szNew += N + 1;
   778         -      if( szNew > p->mxAlloc ){
   779         -        sqlite3StrAccumReset(p);
   780         -        setStrAccumError(p, STRACCUM_TOOBIG);
   781         -        return;
   782         -      }else{
   783         -        p->nAlloc = (int)szNew;
   784         -      }
   785         -      if( p->useMalloc==1 ){
   786         -        zNew = sqlite3DbRealloc(p->db, zOld, p->nAlloc);
   787         -      }else{
   788         -        zNew = sqlite3_realloc(zOld, p->nAlloc);
   789         -      }
   790         -      if( zNew ){
   791         -        if( zOld==0 && p->nChar>0 ) memcpy(zNew, p->zText, p->nChar);
   792         -        p->zText = zNew;
   793         -      }else{
   794         -        sqlite3StrAccumReset(p);
   795         -        setStrAccumError(p, STRACCUM_NOMEM);
   796         -        return;
   797         -      }
   798         -    }
          804  +    enlargeAndAppend(p,z,N);
          805  +    return;
   799    806     }
   800    807     assert( p->zText );
   801    808     memcpy(&p->zText[p->nChar], z, N);
   802    809     p->nChar += N;
   803    810   }
   804    811   
   805    812   /*

Changes to src/select.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   */
    15     15   #include "sqliteInt.h"
    16     16   
           17  +/*
           18  +** An instance of the following object is used to record information about
           19  +** how to process the DISTINCT keyword, to simplify passing that information
           20  +** into the selectInnerLoop() routine.
           21  +*/
           22  +typedef struct DistinctCtx DistinctCtx;
           23  +struct DistinctCtx {
           24  +  u8 isTnct;      /* True if the DISTINCT keyword is present */
           25  +  u8 eTnctType;   /* One of the WHERE_DISTINCT_* operators */
           26  +  int tabTnct;    /* Ephemeral table used for DISTINCT processing */
           27  +  int addrTnct;   /* Address of OP_OpenEphemeral opcode for tabTnct */
           28  +};
           29  +
           30  +/*
           31  +** An instance of the following object is used to record information about
           32  +** the ORDER BY (or GROUP BY) clause of query is being coded.
           33  +*/
           34  +typedef struct SortCtx SortCtx;
           35  +struct SortCtx {
           36  +  ExprList *pOrderBy;   /* The ORDER BY (or GROUP BY clause) */
           37  +  int nOBSat;           /* Number of ORDER BY terms satisfied by indices */
           38  +  int iECursor;         /* Cursor number for the sorter */
           39  +  int regReturn;        /* Register holding block-output return address */
           40  +  int labelBkOut;       /* Start label for the block-output subroutine */
           41  +  int addrSortIndex;    /* Address of the OP_SorterOpen or OP_OpenEphemeral */
           42  +  u8 sortFlags;         /* Zero or more SORTFLAG_* bits */
           43  +};
           44  +#define SORTFLAG_UseSorter  0x01   /* Use SorterOpen instead of OpenEphemeral */
    17     45   
    18     46   /*
    19     47   ** Delete all the content of a Select structure but do not deallocate
    20     48   ** the select structure itself.
    21     49   */
    22     50   static void clearSelect(sqlite3 *db, Select *p){
    23     51     sqlite3ExprListDelete(db, p->pEList);
................................................................................
    83    111     pNew->selFlags = selFlags;
    84    112     pNew->op = TK_SELECT;
    85    113     pNew->pLimit = pLimit;
    86    114     pNew->pOffset = pOffset;
    87    115     assert( pOffset==0 || pLimit!=0 );
    88    116     pNew->addrOpenEphm[0] = -1;
    89    117     pNew->addrOpenEphm[1] = -1;
    90         -  pNew->addrOpenEphm[2] = -1;
    91    118     if( db->mallocFailed ) {
    92    119       clearSelect(db, pNew);
    93    120       if( pNew!=&standin ) sqlite3DbFree(db, pNew);
    94    121       pNew = 0;
    95    122     }else{
    96    123       assert( pNew->pSrc!=0 || pParse->nErr>0 );
    97    124     }
................................................................................
   415    442                        isOuter, &p->pWhere);
   416    443         }
   417    444       }
   418    445     }
   419    446     return 0;
   420    447   }
   421    448   
          449  +/* Forward reference */
          450  +static KeyInfo *keyInfoFromExprList(
          451  +  Parse *pParse,       /* Parsing context */
          452  +  ExprList *pList,     /* Form the KeyInfo object from this ExprList */
          453  +  int iStart,          /* Begin with this column of pList */
          454  +  int nExtra           /* Add this many extra columns to the end */
          455  +);
          456  +
   422    457   /*
   423         -** Insert code into "v" that will push the record on the top of the
   424         -** stack into the sorter.
          458  +** Insert code into "v" that will push the record in register regData
          459  +** into the sorter.
   425    460   */
   426    461   static void pushOntoSorter(
   427    462     Parse *pParse,         /* Parser context */
   428         -  ExprList *pOrderBy,    /* The ORDER BY clause */
          463  +  SortCtx *pSort,        /* Information about the ORDER BY clause */
   429    464     Select *pSelect,       /* The whole SELECT statement */
   430    465     int regData            /* Register holding data to be sorted */
   431    466   ){
   432    467     Vdbe *v = pParse->pVdbe;
   433         -  int nExpr = pOrderBy->nExpr;
          468  +  int nExpr = pSort->pOrderBy->nExpr;
   434    469     int regBase = sqlite3GetTempRange(pParse, nExpr+2);
   435    470     int regRecord = sqlite3GetTempReg(pParse);
          471  +  int nOBSat = pSort->nOBSat;
   436    472     int op;
   437    473     sqlite3ExprCacheClear(pParse);
   438         -  sqlite3ExprCodeExprList(pParse, pOrderBy, regBase, 0);
   439         -  sqlite3VdbeAddOp2(v, OP_Sequence, pOrderBy->iECursor, regBase+nExpr);
          474  +  sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, 0);
          475  +  sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
   440    476     sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+1, 1);
   441         -  sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase, nExpr + 2, regRecord);
   442         -  if( pSelect->selFlags & SF_UseSorter ){
          477  +  sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nExpr+2-nOBSat, regRecord);
          478  +  if( nOBSat>0 ){
          479  +    int regPrevKey;   /* The first nOBSat columns of the previous row */
          480  +    int addrFirst;    /* Address of the OP_IfNot opcode */
          481  +    int addrJmp;      /* Address of the OP_Jump opcode */
          482  +    VdbeOp *pOp;      /* Opcode that opens the sorter */
          483  +    int nKey;         /* Number of sorting key columns, including OP_Sequence */
          484  +    KeyInfo *pKI;     /* Original KeyInfo on the sorter table */
          485  +
          486  +    regPrevKey = pParse->nMem+1;
          487  +    pParse->nMem += pSort->nOBSat;
          488  +    nKey = nExpr - pSort->nOBSat + 1;
          489  +    addrFirst = sqlite3VdbeAddOp1(v, OP_IfNot, regBase+nExpr); VdbeCoverage(v);
          490  +    sqlite3VdbeAddOp3(v, OP_Compare, regPrevKey, regBase, pSort->nOBSat);
          491  +    pOp = sqlite3VdbeGetOp(v, pSort->addrSortIndex);
          492  +    if( pParse->db->mallocFailed ) return;
          493  +    pOp->p2 = nKey + 1;
          494  +    pKI = pOp->p4.pKeyInfo;
          495  +    memset(pKI->aSortOrder, 0, pKI->nField); /* Makes OP_Jump below testable */
          496  +    sqlite3VdbeChangeP4(v, -1, (char*)pKI, P4_KEYINFO);
          497  +    pOp->p4.pKeyInfo = keyInfoFromExprList(pParse, pSort->pOrderBy, nOBSat, 1);
          498  +    addrJmp = sqlite3VdbeCurrentAddr(v);
          499  +    sqlite3VdbeAddOp3(v, OP_Jump, addrJmp+1, 0, addrJmp+1); VdbeCoverage(v);
          500  +    pSort->labelBkOut = sqlite3VdbeMakeLabel(v);
          501  +    pSort->regReturn = ++pParse->nMem;
          502  +    sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut);
          503  +    sqlite3VdbeAddOp1(v, OP_ResetSorter, pSort->iECursor);
          504  +    sqlite3VdbeJumpHere(v, addrFirst);
          505  +    sqlite3VdbeAddOp3(v, OP_Move, regBase, regPrevKey, pSort->nOBSat);
          506  +    sqlite3VdbeJumpHere(v, addrJmp);
          507  +  }
          508  +  if( pSort->sortFlags & SORTFLAG_UseSorter ){
   443    509       op = OP_SorterInsert;
   444    510     }else{
   445    511       op = OP_IdxInsert;
   446    512     }
   447         -  sqlite3VdbeAddOp2(v, op, pOrderBy->iECursor, regRecord);
   448         -  sqlite3ReleaseTempReg(pParse, regRecord);
   449         -  sqlite3ReleaseTempRange(pParse, regBase, nExpr+2);
          513  +  sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord);
          514  +  if( nOBSat==0 ){
          515  +    sqlite3ReleaseTempReg(pParse, regRecord);
          516  +    sqlite3ReleaseTempRange(pParse, regBase, nExpr+2);
          517  +  }
   450    518     if( pSelect->iLimit ){
   451    519       int addr1, addr2;
   452    520       int iLimit;
   453    521       if( pSelect->iOffset ){
   454    522         iLimit = pSelect->iOffset+1;
   455    523       }else{
   456    524         iLimit = pSelect->iLimit;
   457    525       }
   458    526       addr1 = sqlite3VdbeAddOp1(v, OP_IfZero, iLimit); VdbeCoverage(v);
   459    527       sqlite3VdbeAddOp2(v, OP_AddImm, iLimit, -1);
   460    528       addr2 = sqlite3VdbeAddOp0(v, OP_Goto);
   461    529       sqlite3VdbeJumpHere(v, addr1);
   462         -    sqlite3VdbeAddOp1(v, OP_Last, pOrderBy->iECursor);
   463         -    sqlite3VdbeAddOp1(v, OP_Delete, pOrderBy->iECursor);
          530  +    sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor);
          531  +    sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor);
   464    532       sqlite3VdbeJumpHere(v, addr2);
   465    533     }
   466    534   }
   467    535   
   468    536   /*
   469    537   ** Add code to implement the OFFSET
   470    538   */
   471    539   static void codeOffset(
   472    540     Vdbe *v,          /* Generate code into this VM */
   473    541     int iOffset,      /* Register holding the offset counter */
   474    542     int iContinue     /* Jump here to skip the current record */
   475    543   ){
   476         -  if( iOffset>0 && iContinue!=0 ){
          544  +  if( iOffset>0 ){
   477    545       int addr;
   478    546       sqlite3VdbeAddOp2(v, OP_AddImm, iOffset, -1);
   479    547       addr = sqlite3VdbeAddOp1(v, OP_IfNeg, iOffset); VdbeCoverage(v);
   480    548       sqlite3VdbeAddOp2(v, OP_Goto, 0, iContinue);
   481    549       VdbeComment((v, "skip OFFSET records"));
   482    550       sqlite3VdbeJumpHere(v, addr);
   483    551     }
................................................................................
   530    598       return 1;
   531    599     }else{
   532    600       return 0;
   533    601     }
   534    602   }
   535    603   #endif
   536    604   
   537         -/*
   538         -** An instance of the following object is used to record information about
   539         -** how to process the DISTINCT keyword, to simplify passing that information
   540         -** into the selectInnerLoop() routine.
   541         -*/
   542         -typedef struct DistinctCtx DistinctCtx;
   543         -struct DistinctCtx {
   544         -  u8 isTnct;      /* True if the DISTINCT keyword is present */
   545         -  u8 eTnctType;   /* One of the WHERE_DISTINCT_* operators */
   546         -  int tabTnct;    /* Ephemeral table used for DISTINCT processing */
   547         -  int addrTnct;   /* Address of OP_OpenEphemeral opcode for tabTnct */
   548         -};
   549         -
   550    605   /*
   551    606   ** This routine generates the code for the inside of the inner loop
   552    607   ** of a SELECT.
   553    608   **
   554    609   ** If srcTab is negative, then the pEList expressions
   555    610   ** are evaluated in order to get the data for this row.  If srcTab is
   556    611   ** zero or more, then data is pulled from srcTab and pEList is used only 
................................................................................
   557    612   ** to get number columns and the datatype for each column.
   558    613   */
   559    614   static void selectInnerLoop(
   560    615     Parse *pParse,          /* The parser context */
   561    616     Select *p,              /* The complete select statement being coded */
   562    617     ExprList *pEList,       /* List of values being extracted */
   563    618     int srcTab,             /* Pull data from this table */
   564         -  ExprList *pOrderBy,     /* If not NULL, sort results using this key */
          619  +  SortCtx *pSort,         /* If not NULL, info on how to process ORDER BY */
   565    620     DistinctCtx *pDistinct, /* If not NULL, info on how to process DISTINCT */
   566    621     SelectDest *pDest,      /* How to dispose of the results */
   567    622     int iContinue,          /* Jump here to continue with next row */
   568    623     int iBreak              /* Jump here to break out of the inner loop */
   569    624   ){
   570    625     Vdbe *v = pParse->pVdbe;
   571    626     int i;
................................................................................
   574    629     int eDest = pDest->eDest;   /* How to dispose of results */
   575    630     int iParm = pDest->iSDParm; /* First argument to disposal method */
   576    631     int nResultCol;             /* Number of result columns */
   577    632   
   578    633     assert( v );
   579    634     assert( pEList!=0 );
   580    635     hasDistinct = pDistinct ? pDistinct->eTnctType : WHERE_DISTINCT_NOOP;
   581         -  if( pOrderBy==0 && !hasDistinct ){
          636  +  if( pSort && pSort->pOrderBy==0 ) pSort = 0;
          637  +  if( pSort==0 && !hasDistinct ){
          638  +    assert( iContinue!=0 );
   582    639       codeOffset(v, p->iOffset, iContinue);
   583    640     }
   584    641   
   585    642     /* Pull the requested columns.
   586    643     */
   587    644     nResultCol = pEList->nExpr;
   588    645   
................................................................................
   664    721   
   665    722         default: {
   666    723           assert( pDistinct->eTnctType==WHERE_DISTINCT_UNORDERED );
   667    724           codeDistinct(pParse, pDistinct->tabTnct, iContinue, nResultCol, regResult);
   668    725           break;
   669    726         }
   670    727       }
   671         -    if( pOrderBy==0 ){
          728  +    if( pSort==0 ){
   672    729         codeOffset(v, p->iOffset, iContinue);
   673    730       }
   674    731     }
   675    732   
   676    733     switch( eDest ){
   677    734       /* In this mode, write each query result to the key of the temporary
   678    735       ** table iParm.
................................................................................
   695    752         sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nResultCol);
   696    753         break;
   697    754       }
   698    755   #endif /* SQLITE_OMIT_COMPOUND_SELECT */
   699    756   
   700    757       /* Store the result as data using a unique key.
   701    758       */
   702         -    case SRT_DistTable:
          759  +    case SRT_Fifo:
          760  +    case SRT_DistFifo:
   703    761       case SRT_Table:
   704    762       case SRT_EphemTab: {
   705    763         int r1 = sqlite3GetTempReg(pParse);
   706    764         testcase( eDest==SRT_Table );
   707    765         testcase( eDest==SRT_EphemTab );
   708    766         sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r1);
   709    767   #ifndef SQLITE_OMIT_CTE
   710         -      if( eDest==SRT_DistTable ){
   711         -        /* If the destination is DistTable, then cursor (iParm+1) is open
          768  +      if( eDest==SRT_DistFifo ){
          769  +        /* If the destination is DistFifo, then cursor (iParm+1) is open
   712    770           ** on an ephemeral index. If the current row is already present
   713    771           ** in the index, do not write it to the output. If not, add the
   714    772           ** current row to the index and proceed with writing it to the
   715    773           ** output table as well.  */
   716    774           int addr = sqlite3VdbeCurrentAddr(v) + 4;
   717    775           sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0); VdbeCoverage(v);
   718    776           sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm+1, r1);
   719         -        assert( pOrderBy==0 );
          777  +        assert( pSort==0 );
   720    778         }
   721    779   #endif
   722         -      if( pOrderBy ){
   723         -        pushOntoSorter(pParse, pOrderBy, p, r1);
          780  +      if( pSort ){
          781  +        pushOntoSorter(pParse, pSort, p, r1);
   724    782         }else{
   725    783           int r2 = sqlite3GetTempReg(pParse);
   726    784           sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
   727    785           sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
   728    786           sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
   729    787           sqlite3ReleaseTempReg(pParse, r2);
   730    788         }
................................................................................
   737    795       ** then there should be a single item on the stack.  Write this
   738    796       ** item into the set table with bogus data.
   739    797       */
   740    798       case SRT_Set: {
   741    799         assert( nResultCol==1 );
   742    800         pDest->affSdst =
   743    801                     sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affSdst);
   744         -      if( pOrderBy ){
          802  +      if( pSort ){
   745    803           /* At first glance you would think we could optimize out the
   746    804           ** ORDER BY in this case since the order of entries in the set
   747    805           ** does not matter.  But there might be a LIMIT clause, in which
   748    806           ** case the order does matter */
   749         -        pushOntoSorter(pParse, pOrderBy, p, regResult);
          807  +        pushOntoSorter(pParse, pSort, p, regResult);
   750    808         }else{
   751    809           int r1 = sqlite3GetTempReg(pParse);
   752    810           sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult,1,r1, &pDest->affSdst, 1);
   753    811           sqlite3ExprCacheAffinityChange(pParse, regResult, 1);
   754    812           sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
   755    813           sqlite3ReleaseTempReg(pParse, r1);
   756    814         }
................................................................................
   767    825   
   768    826       /* If this is a scalar select that is part of an expression, then
   769    827       ** store the results in the appropriate memory cell and break out
   770    828       ** of the scan loop.
   771    829       */
   772    830       case SRT_Mem: {
   773    831         assert( nResultCol==1 );
   774         -      if( pOrderBy ){
   775         -        pushOntoSorter(pParse, pOrderBy, p, regResult);
          832  +      if( pSort ){
          833  +        pushOntoSorter(pParse, pSort, p, regResult);
   776    834         }else{
   777    835           sqlite3ExprCodeMove(pParse, regResult, iParm, 1);
   778    836           /* The LIMIT clause will jump out of the loop for us */
   779    837         }
   780    838         break;
   781    839       }
   782    840   #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
   783    841   
   784    842       case SRT_Coroutine:       /* Send data to a co-routine */
   785    843       case SRT_Output: {        /* Return the results */
   786    844         testcase( eDest==SRT_Coroutine );
   787    845         testcase( eDest==SRT_Output );
   788         -      if( pOrderBy ){
          846  +      if( pSort ){
   789    847           int r1 = sqlite3GetTempReg(pParse);
   790    848           sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r1);
   791         -        pushOntoSorter(pParse, pOrderBy, p, r1);
          849  +        pushOntoSorter(pParse, pSort, p, r1);
   792    850           sqlite3ReleaseTempReg(pParse, r1);
   793    851         }else if( eDest==SRT_Coroutine ){
   794    852           sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm);
   795    853         }else{
   796    854           sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, nResultCol);
   797    855           sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol);
   798    856         }
................................................................................
   861    919   #endif
   862    920     }
   863    921   
   864    922     /* Jump to the end of the loop if the LIMIT is reached.  Except, if
   865    923     ** there is a sorter, in which case the sorter has already limited
   866    924     ** the output for us.
   867    925     */
   868         -  if( pOrderBy==0 && p->iLimit ){
          926  +  if( pSort==0 && p->iLimit ){
   869    927       sqlite3VdbeAddOp3(v, OP_IfZero, p->iLimit, iBreak, -1); VdbeCoverage(v);
   870    928     }
   871    929   }
   872    930   
   873    931   /*
   874    932   ** Allocate a KeyInfo object sufficient for an index of N key columns and
   875    933   ** X extra columns.
................................................................................
   932    990   ** then the KeyInfo structure is appropriate for initializing a virtual
   933    991   ** index to implement a DISTINCT test.
   934    992   **
   935    993   ** Space to hold the KeyInfo structure is obtain from malloc.  The calling
   936    994   ** function is responsible for seeing that this structure is eventually
   937    995   ** freed.
   938    996   */
   939         -static KeyInfo *keyInfoFromExprList(Parse *pParse, ExprList *pList, int nExtra){
          997  +static KeyInfo *keyInfoFromExprList(
          998  +  Parse *pParse,       /* Parsing context */
          999  +  ExprList *pList,     /* Form the KeyInfo object from this ExprList */
         1000  +  int iStart,          /* Begin with this column of pList */
         1001  +  int nExtra           /* Add this many extra columns to the end */
         1002  +){
   940   1003     int nExpr;
   941   1004     KeyInfo *pInfo;
   942   1005     struct ExprList_item *pItem;
   943   1006     sqlite3 *db = pParse->db;
   944   1007     int i;
   945   1008   
   946   1009     nExpr = pList->nExpr;
   947         -  pInfo = sqlite3KeyInfoAlloc(db, nExpr+nExtra, 1);
         1010  +  pInfo = sqlite3KeyInfoAlloc(db, nExpr+nExtra-iStart, 1);
   948   1011     if( pInfo ){
   949   1012       assert( sqlite3KeyInfoIsWriteable(pInfo) );
   950         -    for(i=0, pItem=pList->a; i<nExpr; i++, pItem++){
         1013  +    for(i=iStart, pItem=pList->a+iStart; i<nExpr; i++, pItem++){
   951   1014         CollSeq *pColl;
   952   1015         pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr);
   953   1016         if( !pColl ) pColl = db->pDfltColl;
   954         -      pInfo->aColl[i] = pColl;
   955         -      pInfo->aSortOrder[i] = pItem->sortOrder;
         1017  +      pInfo->aColl[i-iStart] = pColl;
         1018  +      pInfo->aSortOrder[i-iStart] = pItem->sortOrder;
   956   1019       }
   957   1020     }
   958   1021     return pInfo;
   959   1022   }
   960   1023   
   961   1024   #ifndef SQLITE_OMIT_COMPOUND_SELECT
   962   1025   /*
................................................................................
  1050   1113   ** then the results were placed in a sorter.  After the loop is terminated
  1051   1114   ** we need to run the sorter and output the results.  The following
  1052   1115   ** routine generates the code needed to do that.
  1053   1116   */
  1054   1117   static void generateSortTail(
  1055   1118     Parse *pParse,    /* Parsing context */
  1056   1119     Select *p,        /* The SELECT statement */
  1057         -  Vdbe *v,          /* Generate code into this VDBE */
         1120  +  SortCtx *pSort,   /* Information on the ORDER BY clause */
  1058   1121     int nColumn,      /* Number of columns of data */
  1059   1122     SelectDest *pDest /* Write the sorted results here */
  1060   1123   ){
         1124  +  Vdbe *v = pParse->pVdbe;                     /* The prepared statement */
  1061   1125     int addrBreak = sqlite3VdbeMakeLabel(v);     /* Jump here to exit loop */
  1062   1126     int addrContinue = sqlite3VdbeMakeLabel(v);  /* Jump here for next cycle */
  1063   1127     int addr;
         1128  +  int addrOnce = 0;
  1064   1129     int iTab;
  1065   1130     int pseudoTab = 0;
  1066         -  ExprList *pOrderBy = p->pOrderBy;
  1067         -
         1131  +  ExprList *pOrderBy = pSort->pOrderBy;
  1068   1132     int eDest = pDest->eDest;
  1069   1133     int iParm = pDest->iSDParm;
  1070         -
  1071   1134     int regRow;
  1072   1135     int regRowid;
         1136  +  int nKey;
  1073   1137   
  1074         -  iTab = pOrderBy->iECursor;
         1138  +  if( pSort->labelBkOut ){
         1139  +    sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut);
         1140  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrBreak);
         1141  +    sqlite3VdbeResolveLabel(v, pSort->labelBkOut);
         1142  +    addrOnce = sqlite3CodeOnce(pParse); VdbeCoverage(v);
         1143  +  }
         1144  +  iTab = pSort->iECursor;
  1075   1145     regRow = sqlite3GetTempReg(pParse);
  1076   1146     if( eDest==SRT_Output || eDest==SRT_Coroutine ){
  1077   1147       pseudoTab = pParse->nTab++;
  1078   1148       sqlite3VdbeAddOp3(v, OP_OpenPseudo, pseudoTab, regRow, nColumn);
  1079   1149       regRowid = 0;
  1080   1150     }else{
  1081   1151       regRowid = sqlite3GetTempReg(pParse);
  1082   1152     }
  1083         -  if( p->selFlags & SF_UseSorter ){
         1153  +  nKey = pOrderBy->nExpr - pSort->nOBSat;
         1154  +  if( pSort->sortFlags & SORTFLAG_UseSorter ){
  1084   1155       int regSortOut = ++pParse->nMem;
  1085   1156       int ptab2 = pParse->nTab++;
  1086         -    sqlite3VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, pOrderBy->nExpr+2);
         1157  +    sqlite3VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, nKey+2);
         1158  +    if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce);
  1087   1159       addr = 1 + sqlite3VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak);
  1088   1160       VdbeCoverage(v);
  1089   1161       codeOffset(v, p->iOffset, addrContinue);
  1090   1162       sqlite3VdbeAddOp2(v, OP_SorterData, iTab, regSortOut);
  1091         -    sqlite3VdbeAddOp3(v, OP_Column, ptab2, pOrderBy->nExpr+1, regRow);
         1163  +    sqlite3VdbeAddOp3(v, OP_Column, ptab2, nKey+1, regRow);
  1092   1164       sqlite3VdbeChangeP5(v, OPFLAG_CLEARCACHE);
  1093   1165     }else{
         1166  +    if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce);
  1094   1167       addr = 1 + sqlite3VdbeAddOp2(v, OP_Sort, iTab, addrBreak); VdbeCoverage(v);
  1095   1168       codeOffset(v, p->iOffset, addrContinue);
  1096         -    sqlite3VdbeAddOp3(v, OP_Column, iTab, pOrderBy->nExpr+1, regRow);
         1169  +    sqlite3VdbeAddOp3(v, OP_Column, iTab, nKey+1, regRow);
  1097   1170     }
  1098   1171     switch( eDest ){
  1099   1172       case SRT_Table:
  1100   1173       case SRT_EphemTab: {
  1101   1174         testcase( eDest==SRT_Table );
  1102   1175         testcase( eDest==SRT_EphemTab );
  1103   1176         sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid);
................................................................................
  1144   1217     }
  1145   1218     sqlite3ReleaseTempReg(pParse, regRow);
  1146   1219     sqlite3ReleaseTempReg(pParse, regRowid);
  1147   1220   
  1148   1221     /* The bottom of the loop
  1149   1222     */
  1150   1223     sqlite3VdbeResolveLabel(v, addrContinue);
  1151         -  if( p->selFlags & SF_UseSorter ){
         1224  +  if( pSort->sortFlags & SORTFLAG_UseSorter ){
  1152   1225       sqlite3VdbeAddOp2(v, OP_SorterNext, iTab, addr); VdbeCoverage(v);
  1153   1226     }else{
  1154   1227       sqlite3VdbeAddOp2(v, OP_Next, iTab, addr); VdbeCoverage(v);
  1155   1228     }
         1229  +  if( pSort->regReturn ) sqlite3VdbeAddOp1(v, OP_Return, pSort->regReturn);
  1156   1230     sqlite3VdbeResolveLabel(v, addrBreak);
  1157   1231     if( eDest==SRT_Output || eDest==SRT_Coroutine ){
  1158   1232       sqlite3VdbeAddOp2(v, OP_Close, pseudoTab, 0);
  1159   1233     }
  1160   1234   }
  1161   1235   
  1162   1236   /*
................................................................................
  1830   1904     Select *pSetup = p->pPrior;   /* The setup query */
  1831   1905     int addrTop;                  /* Top of the loop */
  1832   1906     int addrCont, addrBreak;      /* CONTINUE and BREAK addresses */
  1833   1907     int iCurrent = 0;             /* The Current table */
  1834   1908     int regCurrent;               /* Register holding Current table */
  1835   1909     int iQueue;                   /* The Queue table */
  1836   1910     int iDistinct = 0;            /* To ensure unique results if UNION */
  1837         -  int eDest = SRT_Table;        /* How to write to Queue */
         1911  +  int eDest = SRT_Fifo;         /* How to write to Queue */
  1838   1912     SelectDest destQueue;         /* SelectDest targetting the Queue table */
  1839   1913     int i;                        /* Loop counter */
  1840   1914     int rc;                       /* Result code */
  1841   1915     ExprList *pOrderBy;           /* The ORDER BY clause */
  1842   1916     Expr *pLimit, *pOffset;       /* Saved LIMIT and OFFSET */
  1843   1917     int regLimit, regOffset;      /* Registers used by LIMIT and OFFSET */
  1844   1918   
................................................................................
  1862   1936         iCurrent = pSrc->a[i].iCursor;
  1863   1937         break;
  1864   1938       }
  1865   1939     }
  1866   1940   
  1867   1941     /* Allocate cursors numbers for Queue and Distinct.  The cursor number for
  1868   1942     ** the Distinct table must be exactly one greater than Queue in order
  1869         -  ** for the SRT_DistTable and SRT_DistQueue destinations to work. */
         1943  +  ** for the SRT_DistFifo and SRT_DistQueue destinations to work. */
  1870   1944     iQueue = pParse->nTab++;
  1871   1945     if( p->op==TK_UNION ){
  1872         -    eDest = pOrderBy ? SRT_DistQueue : SRT_DistTable;
         1946  +    eDest = pOrderBy ? SRT_DistQueue : SRT_DistFifo;
  1873   1947       iDistinct = pParse->nTab++;
  1874   1948     }else{
  1875         -    eDest = pOrderBy ? SRT_Queue : SRT_Table;
         1949  +    eDest = pOrderBy ? SRT_Queue : SRT_Fifo;
  1876   1950     }
  1877   1951     sqlite3SelectDestInit(&destQueue, eDest, iQueue);
  1878   1952   
  1879   1953     /* Allocate cursors for Current, Queue, and Distinct. */
  1880   1954     regCurrent = ++pParse->nMem;
  1881   1955     sqlite3VdbeAddOp3(v, OP_OpenPseudo, iCurrent, regCurrent, nCol);
  1882   1956     if( pOrderBy ){
................................................................................
  1934   2008     p->pPrior = pSetup;
  1935   2009   
  1936   2010     /* Keep running the loop until the Queue is empty */
  1937   2011     sqlite3VdbeAddOp2(v, OP_Goto, 0, addrTop);
  1938   2012     sqlite3VdbeResolveLabel(v, addrBreak);
  1939   2013   
  1940   2014   end_of_recursive_query:
         2015  +  sqlite3ExprListDelete(pParse->db, p->pOrderBy);
  1941   2016     p->pOrderBy = pOrderBy;
  1942   2017     p->pLimit = pLimit;
  1943   2018     p->pOffset = pOffset;
  1944   2019     return;
  1945   2020   }
  1946   2021   #endif /* SQLITE_OMIT_CTE */
  1947   2022   
................................................................................
  4305   4380         Expr *pE = pFunc->pExpr;
  4306   4381         assert( !ExprHasProperty(pE, EP_xIsSelect) );
  4307   4382         if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
  4308   4383           sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
  4309   4384              "argument");
  4310   4385           pFunc->iDistinct = -1;
  4311   4386         }else{
  4312         -        KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList, 0);
         4387  +        KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pE->x.pList, 0, 0);
  4313   4388           sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pFunc->iDistinct, 0, 0,
  4314   4389                             (char*)pKeyInfo, P4_KEYINFO);
  4315   4390         }
  4316   4391       }
  4317   4392     }
  4318   4393   }
  4319   4394   
................................................................................
  4460   4535     int i, j;              /* Loop counters */
  4461   4536     WhereInfo *pWInfo;     /* Return from sqlite3WhereBegin() */
  4462   4537     Vdbe *v;               /* The virtual machine under construction */
  4463   4538     int isAgg;             /* True for select lists like "count(*)" */
  4464   4539     ExprList *pEList;      /* List of columns to extract. */
  4465   4540     SrcList *pTabList;     /* List of tables to select from */
  4466   4541     Expr *pWhere;          /* The WHERE clause.  May be NULL */
  4467         -  ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
  4468   4542     ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
  4469   4543     Expr *pHaving;         /* The HAVING clause.  May be NULL */
  4470   4544     int rc = 1;            /* Value to return from this function */
  4471         -  int addrSortIndex;     /* Address of an OP_OpenEphemeral instruction */
  4472   4545     DistinctCtx sDistinct; /* Info on how to code the DISTINCT keyword */
         4546  +  SortCtx sSort;         /* Info on how to code the ORDER BY clause */
  4473   4547     AggInfo sAggInfo;      /* Information used by aggregate queries */
  4474   4548     int iEnd;              /* Address of the end of the query */
  4475   4549     sqlite3 *db;           /* The database connection */
  4476   4550   
  4477   4551   #ifndef SQLITE_OMIT_EXPLAIN
  4478   4552     int iRestoreSelectId = pParse->iSelectId;
  4479   4553     pParse->iSelectId = pParse->iNextSelectId++;
................................................................................
  4482   4556     db = pParse->db;
  4483   4557     if( p==0 || db->mallocFailed || pParse->nErr ){
  4484   4558       return 1;
  4485   4559     }
  4486   4560     if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  4487   4561     memset(&sAggInfo, 0, sizeof(sAggInfo));
  4488   4562   
         4563  +  assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistFifo );
         4564  +  assert( p->pOrderBy==0 || pDest->eDest!=SRT_Fifo );
         4565  +  assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistQueue );
         4566  +  assert( p->pOrderBy==0 || pDest->eDest!=SRT_Queue );
  4489   4567     if( IgnorableOrderby(pDest) ){
  4490   4568       assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
  4491         -           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard);
         4569  +           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard ||
         4570  +           pDest->eDest==SRT_Queue  || pDest->eDest==SRT_DistFifo ||
         4571  +           pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo);
  4492   4572       /* If ORDER BY makes no difference in the output then neither does
  4493   4573       ** DISTINCT so it can be removed too. */
  4494   4574       sqlite3ExprListDelete(db, p->pOrderBy);
  4495   4575       p->pOrderBy = 0;
  4496   4576       p->selFlags &= ~SF_Distinct;
  4497   4577     }
  4498   4578     sqlite3SelectPrep(pParse, p, 0);
  4499         -  pOrderBy = p->pOrderBy;
         4579  +  memset(&sSort, 0, sizeof(sSort));
         4580  +  sSort.pOrderBy = p->pOrderBy;
  4500   4581     pTabList = p->pSrc;
  4501   4582     pEList = p->pEList;
  4502   4583     if( pParse->nErr || db->mallocFailed ){
  4503   4584       goto select_end;
  4504   4585     }
  4505   4586     isAgg = (p->selFlags & SF_Aggregate)!=0;
  4506   4587     assert( pEList!=0 );
................................................................................
  4614   4695       }
  4615   4696       if( /*pParse->nErr ||*/ db->mallocFailed ){
  4616   4697         goto select_end;
  4617   4698       }
  4618   4699       pParse->nHeight -= sqlite3SelectExprHeight(p);
  4619   4700       pTabList = p->pSrc;
  4620   4701       if( !IgnorableOrderby(pDest) ){
  4621         -      pOrderBy = p->pOrderBy;
         4702  +      sSort.pOrderBy = p->pOrderBy;
  4622   4703       }
  4623   4704     }
  4624   4705     pEList = p->pEList;
  4625   4706   #endif
  4626   4707     pWhere = p->pWhere;
  4627   4708     pGroupBy = p->pGroupBy;
  4628   4709     pHaving = p->pHaving;
................................................................................
  4641   4722     /* If there is both a GROUP BY and an ORDER BY clause and they are
  4642   4723     ** identical, then disable the ORDER BY clause since the GROUP BY
  4643   4724     ** will cause elements to come out in the correct order.  This is
  4644   4725     ** an optimization - the correct answer should result regardless.
  4645   4726     ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
  4646   4727     ** to disable this optimization for testing purposes.
  4647   4728     */
  4648         -  if( sqlite3ExprListCompare(p->pGroupBy, pOrderBy, -1)==0
         4729  +  if( sqlite3ExprListCompare(p->pGroupBy, sSort.pOrderBy, -1)==0
  4649   4730            && OptimizationEnabled(db, SQLITE_GroupByOrder) ){
  4650         -    pOrderBy = 0;
         4731  +    sSort.pOrderBy = 0;
  4651   4732     }
  4652   4733   
  4653   4734     /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 
  4654   4735     ** if the select-list is the same as the ORDER BY list, then this query
  4655   4736     ** can be rewritten as a GROUP BY. In other words, this:
  4656   4737     **
  4657   4738     **     SELECT DISTINCT xyz FROM ... ORDER BY xyz
................................................................................
  4662   4743     **
  4663   4744     ** The second form is preferred as a single index (or temp-table) may be 
  4664   4745     ** used for both the ORDER BY and DISTINCT processing. As originally 
  4665   4746     ** written the query must use a temp-table for at least one of the ORDER 
  4666   4747     ** BY and DISTINCT, and an index or separate temp-table for the other.
  4667   4748     */
  4668   4749     if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct 
  4669         -   && sqlite3ExprListCompare(pOrderBy, p->pEList, -1)==0
         4750  +   && sqlite3ExprListCompare(sSort.pOrderBy, p->pEList, -1)==0
  4670   4751     ){
  4671   4752       p->selFlags &= ~SF_Distinct;
  4672   4753       p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0);
  4673   4754       pGroupBy = p->pGroupBy;
  4674         -    pOrderBy = 0;
         4755  +    sSort.pOrderBy = 0;
  4675   4756       /* Notice that even thought SF_Distinct has been cleared from p->selFlags,
  4676   4757       ** the sDistinct.isTnct is still set.  Hence, isTnct represents the
  4677   4758       ** original setting of the SF_Distinct flag, not the current setting */
  4678   4759       assert( sDistinct.isTnct );
  4679   4760     }
  4680   4761   
  4681   4762     /* If there is an ORDER BY clause, then this sorting
  4682   4763     ** index might end up being unused if the data can be 
  4683   4764     ** extracted in pre-sorted order.  If that is the case, then the
  4684   4765     ** OP_OpenEphemeral instruction will be changed to an OP_Noop once
  4685   4766     ** we figure out that the sorting index is not needed.  The addrSortIndex
  4686   4767     ** variable is used to facilitate that change.
  4687   4768     */
  4688         -  if( pOrderBy ){
         4769  +  if( sSort.pOrderBy ){
  4689   4770       KeyInfo *pKeyInfo;
  4690         -    pKeyInfo = keyInfoFromExprList(pParse, pOrderBy, 0);
  4691         -    pOrderBy->iECursor = pParse->nTab++;
  4692         -    p->addrOpenEphm[2] = addrSortIndex =
         4771  +    pKeyInfo = keyInfoFromExprList(pParse, sSort.pOrderBy, 0, 0);
         4772  +    sSort.iECursor = pParse->nTab++;
         4773  +    sSort.addrSortIndex =
  4693   4774         sqlite3VdbeAddOp4(v, OP_OpenEphemeral,
  4694         -                           pOrderBy->iECursor, pOrderBy->nExpr+2, 0,
         4775  +                           sSort.iECursor, sSort.pOrderBy->nExpr+2, 0,
  4695   4776                              (char*)pKeyInfo, P4_KEYINFO);
  4696   4777     }else{
  4697         -    addrSortIndex = -1;
         4778  +    sSort.addrSortIndex = -1;
  4698   4779     }
  4699   4780   
  4700   4781     /* If the output is destined for a temporary table, open that table.
  4701   4782     */
  4702   4783     if( pDest->eDest==SRT_EphemTab ){
  4703   4784       sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pDest->iSDParm, pEList->nExpr);
  4704   4785     }
  4705   4786   
  4706   4787     /* Set the limiter.
  4707   4788     */
  4708   4789     iEnd = sqlite3VdbeMakeLabel(v);
  4709   4790     p->nSelectRow = LARGEST_INT64;
  4710   4791     computeLimitRegisters(pParse, p, iEnd);
  4711         -  if( p->iLimit==0 && addrSortIndex>=0 ){
  4712         -    sqlite3VdbeGetOp(v, addrSortIndex)->opcode = OP_SorterOpen;
  4713         -    p->selFlags |= SF_UseSorter;
         4792  +  if( p->iLimit==0 && sSort.addrSortIndex>=0 ){
         4793  +    sqlite3VdbeGetOp(v, sSort.addrSortIndex)->opcode = OP_SorterOpen;
         4794  +    sSort.sortFlags |= SORTFLAG_UseSorter;
  4714   4795     }
  4715   4796   
  4716   4797     /* Open a virtual index to use for the distinct set.
  4717   4798     */
  4718   4799     if( p->selFlags & SF_Distinct ){
  4719   4800       sDistinct.tabTnct = pParse->nTab++;
  4720   4801       sDistinct.addrTnct = sqlite3VdbeAddOp4(v, OP_OpenEphemeral,
  4721   4802                                   sDistinct.tabTnct, 0, 0,
  4722         -                                (char*)keyInfoFromExprList(pParse, p->pEList, 0),
         4803  +                                (char*)keyInfoFromExprList(pParse, p->pEList,0,0),
  4723   4804                                   P4_KEYINFO);
  4724   4805       sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
  4725   4806       sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED;
  4726   4807     }else{
  4727   4808       sDistinct.eTnctType = WHERE_DISTINCT_NOOP;
  4728   4809     }
  4729   4810   
  4730   4811     if( !isAgg && pGroupBy==0 ){
  4731   4812       /* No aggregate functions and no GROUP BY clause */
  4732   4813       u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0);
  4733   4814   
  4734   4815       /* Begin the database scan. */
  4735         -    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pOrderBy, p->pEList,
  4736         -                               wctrlFlags, 0);
         4816  +    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, sSort.pOrderBy,
         4817  +                               p->pEList, wctrlFlags, 0);
  4737   4818       if( pWInfo==0 ) goto select_end;
  4738   4819       if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){
  4739   4820         p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo);
  4740   4821       }
  4741   4822       if( sDistinct.isTnct && sqlite3WhereIsDistinct(pWInfo) ){
  4742   4823         sDistinct.eTnctType = sqlite3WhereIsDistinct(pWInfo);
  4743   4824       }
  4744         -    if( pOrderBy && sqlite3WhereIsOrdered(pWInfo) ) pOrderBy = 0;
         4825  +    if( sSort.pOrderBy ){
         4826  +      sSort.nOBSat = sqlite3WhereIsOrdered(pWInfo);
         4827  +      if( sSort.nOBSat==sSort.pOrderBy->nExpr ){
         4828  +        sSort.pOrderBy = 0;
         4829  +      }
         4830  +    }
  4745   4831   
  4746   4832       /* If sorting index that was created by a prior OP_OpenEphemeral 
  4747   4833       ** instruction ended up not being needed, then change the OP_OpenEphemeral
  4748   4834       ** into an OP_Noop.
  4749   4835       */
  4750         -    if( addrSortIndex>=0 && pOrderBy==0 ){
  4751         -      sqlite3VdbeChangeToNoop(v, addrSortIndex);
  4752         -      p->addrOpenEphm[2] = -1;
         4836  +    if( sSort.addrSortIndex>=0 && sSort.pOrderBy==0 ){
         4837  +      sqlite3VdbeChangeToNoop(v, sSort.addrSortIndex);
  4753   4838       }
  4754   4839   
  4755   4840       /* Use the standard inner loop. */
  4756         -    selectInnerLoop(pParse, p, pEList, -1, pOrderBy, &sDistinct, pDest,
         4841  +    selectInnerLoop(pParse, p, pEList, -1, &sSort, &sDistinct, pDest,
  4757   4842                       sqlite3WhereContinueLabel(pWInfo),
  4758   4843                       sqlite3WhereBreakLabel(pWInfo));
  4759   4844   
  4760   4845       /* End the database scan loop.
  4761   4846       */
  4762   4847       sqlite3WhereEnd(pWInfo);
  4763   4848     }else{
................................................................................
  4805   4890       sNC.pParse = pParse;
  4806   4891       sNC.pSrcList = pTabList;
  4807   4892       sNC.pAggInfo = &sAggInfo;
  4808   4893       sAggInfo.mnReg = pParse->nMem+1;
  4809   4894       sAggInfo.nSortingColumn = pGroupBy ? pGroupBy->nExpr+1 : 0;
  4810   4895       sAggInfo.pGroupBy = pGroupBy;
  4811   4896       sqlite3ExprAnalyzeAggList(&sNC, pEList);
  4812         -    sqlite3ExprAnalyzeAggList(&sNC, pOrderBy);
         4897  +    sqlite3ExprAnalyzeAggList(&sNC, sSort.pOrderBy);
  4813   4898       if( pHaving ){
  4814   4899         sqlite3ExprAnalyzeAggregates(&sNC, pHaving);
  4815   4900       }
  4816   4901       sAggInfo.nAccumulator = sAggInfo.nColumn;
  4817   4902       for(i=0; i<sAggInfo.nFunc; i++){
  4818   4903         assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) );
  4819   4904         sNC.ncFlags |= NC_InAggFunc;
................................................................................
  4839   4924   
  4840   4925         /* If there is a GROUP BY clause we might need a sorting index to
  4841   4926         ** implement it.  Allocate that sorting index now.  If it turns out
  4842   4927         ** that we do not need it after all, the OP_SorterOpen instruction
  4843   4928         ** will be converted into a Noop.  
  4844   4929         */
  4845   4930         sAggInfo.sortingIdx = pParse->nTab++;
  4846         -      pKeyInfo = keyInfoFromExprList(pParse, pGroupBy, 0);
         4931  +      pKeyInfo = keyInfoFromExprList(pParse, pGroupBy, 0, 0);
  4847   4932         addrSortingIdx = sqlite3VdbeAddOp4(v, OP_SorterOpen, 
  4848   4933             sAggInfo.sortingIdx, sAggInfo.nSortingColumn, 
  4849   4934             0, (char*)pKeyInfo, P4_KEYINFO);
  4850   4935   
  4851   4936         /* Initialize memory locations used by GROUP BY aggregate processing
  4852   4937         */
  4853   4938         iUseFlag = ++pParse->nMem;
................................................................................
  4868   4953   
  4869   4954         /* Begin a loop that will extract all source rows in GROUP BY order.
  4870   4955         ** This might involve two separate loops with an OP_Sort in between, or
  4871   4956         ** it might be a single loop that uses an index to extract information
  4872   4957         ** in the right order to begin with.
  4873   4958         */
  4874   4959         sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
  4875         -      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0, 
         4960  +      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0,
  4876   4961                                    WHERE_GROUPBY, 0);
  4877   4962         if( pWInfo==0 ) goto select_end;
  4878         -      if( sqlite3WhereIsOrdered(pWInfo) ){
         4963  +      if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){
  4879   4964           /* The optimizer is able to deliver rows in group by order so
  4880   4965           ** we do not have to sort.  The OP_OpenEphemeral table will be
  4881   4966           ** cancelled later because we still need to use the pKeyInfo
  4882   4967           */
  4883   4968           groupBySort = 0;
  4884   4969         }else{
  4885   4970           /* Rows are coming out in undetermined order.  We have to push
................................................................................
  5022   5107         sqlite3VdbeResolveLabel(v, addrOutputRow);
  5023   5108         addrOutputRow = sqlite3VdbeCurrentAddr(v);
  5024   5109         sqlite3VdbeAddOp2(v, OP_IfPos, iUseFlag, addrOutputRow+2); VdbeCoverage(v);
  5025   5110         VdbeComment((v, "Groupby result generator entry point"));
  5026   5111         sqlite3VdbeAddOp1(v, OP_Return, regOutputRow);
  5027   5112         finalizeAggFunctions(pParse, &sAggInfo);
  5028   5113         sqlite3ExprIfFalse(pParse, pHaving, addrOutputRow+1, SQLITE_JUMPIFNULL);
  5029         -      selectInnerLoop(pParse, p, p->pEList, -1, pOrderBy,
         5114  +      selectInnerLoop(pParse, p, p->pEList, -1, &sSort,
  5030   5115                         &sDistinct, pDest,
  5031   5116                         addrOutputRow+1, addrSetAbort);
  5032   5117         sqlite3VdbeAddOp1(v, OP_Return, regOutputRow);
  5033   5118         VdbeComment((v, "end groupby result generator"));
  5034   5119   
  5035   5120         /* Generate a subroutine that will reset the group-by accumulator
  5036   5121         */
................................................................................
  5154   5239           pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMax,0,flag,0);
  5155   5240           if( pWInfo==0 ){
  5156   5241             sqlite3ExprListDelete(db, pDel);
  5157   5242             goto select_end;
  5158   5243           }
  5159   5244           updateAccumulator(pParse, &sAggInfo);
  5160   5245           assert( pMinMax==0 || pMinMax->nExpr==1 );
  5161         -        if( sqlite3WhereIsOrdered(pWInfo) ){
         5246  +        if( sqlite3WhereIsOrdered(pWInfo)>0 ){
  5162   5247             sqlite3VdbeAddOp2(v, OP_Goto, 0, sqlite3WhereBreakLabel(pWInfo));
  5163   5248             VdbeComment((v, "%s() by index",
  5164   5249                   (flag==WHERE_ORDERBY_MIN?"min":"max")));
  5165   5250           }
  5166   5251           sqlite3WhereEnd(pWInfo);
  5167   5252           finalizeAggFunctions(pParse, &sAggInfo);
  5168   5253         }
  5169   5254   
  5170         -      pOrderBy = 0;
         5255  +      sSort.pOrderBy = 0;
  5171   5256         sqlite3ExprIfFalse(pParse, pHaving, addrEnd, SQLITE_JUMPIFNULL);
  5172   5257         selectInnerLoop(pParse, p, p->pEList, -1, 0, 0, 
  5173   5258                         pDest, addrEnd, addrEnd);
  5174   5259         sqlite3ExprListDelete(db, pDel);
  5175   5260       }
  5176   5261       sqlite3VdbeResolveLabel(v, addrEnd);
  5177   5262       
................................................................................
  5180   5265     if( sDistinct.eTnctType==WHERE_DISTINCT_UNORDERED ){
  5181   5266       explainTempTable(pParse, "DISTINCT");
  5182   5267     }
  5183   5268   
  5184   5269     /* If there is an ORDER BY clause, then we need to sort the results
  5185   5270     ** and send them to the callback one by one.
  5186   5271     */
  5187         -  if( pOrderBy ){
  5188         -    explainTempTable(pParse, "ORDER BY");
  5189         -    generateSortTail(pParse, p, v, pEList->nExpr, pDest);
         5272  +  if( sSort.pOrderBy ){
         5273  +    explainTempTable(pParse, sSort.nOBSat>0 ? "RIGHT PART OF ORDER BY":"ORDER BY");
         5274  +    generateSortTail(pParse, p, &sSort, pEList->nExpr, pDest);
  5190   5275     }
  5191   5276   
  5192   5277     /* Jump here to skip this query
  5193   5278     */
  5194   5279     sqlite3VdbeResolveLabel(v, iEnd);
  5195   5280   
  5196   5281     /* The SELECT was successfully coded.   Set the return code to 0

Changes to src/shell.c.

  1191   1191   static void explain_data_prepare(struct callback_data *p, sqlite3_stmt *pSql){
  1192   1192     const char *zSql;               /* The text of the SQL statement */
  1193   1193     const char *z;                  /* Used to check if this is an EXPLAIN */
  1194   1194     int *abYield = 0;               /* True if op is an OP_Yield */
  1195   1195     int nAlloc = 0;                 /* Allocated size of p->aiIndent[], abYield */
  1196   1196     int iOp;                        /* Index of operation in p->aiIndent[] */
  1197   1197   
  1198         -  const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext", 0 };
         1198  +  const char *azNext[] = { "Next", "Prev", "VPrev", "VNext", "SorterNext",
         1199  +                           "NextIfOpen", "PrevIfOpen", 0 };
  1199   1200     const char *azYield[] = { "Yield", "SeekLt", "SeekGt", "RowSetRead", "Rewind", 0 };
  1200   1201     const char *azGoto[] = { "Goto", 0 };
  1201   1202   
  1202   1203     /* Try to figure out if this is really an EXPLAIN statement. If this
  1203   1204     ** cannot be verified, return early.  */
  1204   1205     zSql = sqlite3_sql(pSql);
  1205   1206     if( zSql==0 ) return;

Changes to src/sqliteInt.h.

  1890   1890   #define EP_Agg       0x000002 /* Contains one or more aggregate functions */
  1891   1891   #define EP_Resolved  0x000004 /* IDs have been resolved to COLUMNs */
  1892   1892   #define EP_Error     0x000008 /* Expression contains one or more errors */
  1893   1893   #define EP_Distinct  0x000010 /* Aggregate function with DISTINCT keyword */
  1894   1894   #define EP_VarSelect 0x000020 /* pSelect is correlated, not constant */
  1895   1895   #define EP_DblQuoted 0x000040 /* token.z was originally in "..." */
  1896   1896   #define EP_InfixFunc 0x000080 /* True for an infix function: LIKE, GLOB, etc */
  1897         -#define EP_Collate   0x000100 /* Tree contains a TK_COLLATE opeartor */
  1898         -      /* unused      0x000200 */
         1897  +#define EP_Collate   0x000100 /* Tree contains a TK_COLLATE operator */
         1898  +#define EP_Generic   0x000200 /* Ignore COLLATE or affinity on this tree */
  1899   1899   #define EP_IntValue  0x000400 /* Integer value contained in u.iValue */
  1900   1900   #define EP_xIsSelect 0x000800 /* x.pSelect is valid (otherwise x.pList is) */
  1901   1901   #define EP_Skip      0x001000 /* COLLATE, AS, or UNLIKELY */
  1902   1902   #define EP_Reduced   0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */
  1903   1903   #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */
  1904   1904   #define EP_Static    0x008000 /* Held in memory not obtained from malloc() */
  1905   1905   #define EP_MemToken  0x010000 /* Need to sqlite3DbFree() Expr.zToken */
................................................................................
  1955   1955   ** column expression as it exists in a SELECT statement.  However, if
  1956   1956   ** the bSpanIsTab flag is set, then zSpan is overloaded to mean the name
  1957   1957   ** of the result column in the form: DATABASE.TABLE.COLUMN.  This later
  1958   1958   ** form is used for name resolution with nested FROM clauses.
  1959   1959   */
  1960   1960   struct ExprList {
  1961   1961     int nExpr;             /* Number of expressions on the list */
  1962         -  int iECursor;          /* VDBE Cursor associated with this ExprList */
  1963   1962     struct ExprList_item { /* For each expression in the list */
  1964   1963       Expr *pExpr;            /* The list of expressions */
  1965   1964       char *zName;            /* Token associated with this expression */
  1966   1965       char *zSpan;            /* Original text of the expression */
  1967   1966       u8 sortOrder;           /* 1 for DESC or 0 for ASC */
  1968   1967       unsigned done :1;       /* A flag to indicate when processing is finished */
  1969   1968       unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */
................................................................................
  2179   2178   ** sequences for the ORDER BY clause.
  2180   2179   */
  2181   2180   struct Select {
  2182   2181     ExprList *pEList;      /* The fields of the result */
  2183   2182     u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  2184   2183     u16 selFlags;          /* Various SF_* values */
  2185   2184     int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  2186         -  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
         2185  +  int addrOpenEphm[2];   /* OP_OpenEphem opcodes related to this select */
  2187   2186     u64 nSelectRow;        /* Estimated number of result rows */
  2188   2187     SrcList *pSrc;         /* The FROM clause */
  2189   2188     Expr *pWhere;          /* The WHERE clause */
  2190   2189     ExprList *pGroupBy;    /* The GROUP BY clause */
  2191   2190     Expr *pHaving;         /* The HAVING clause */
  2192   2191     ExprList *pOrderBy;    /* The ORDER BY clause */
  2193   2192     Select *pPrior;        /* Prior select in a compound select statement */
................................................................................
  2203   2202   */
  2204   2203   #define SF_Distinct        0x0001  /* Output should be DISTINCT */
  2205   2204   #define SF_Resolved        0x0002  /* Identifiers have been resolved */
  2206   2205   #define SF_Aggregate       0x0004  /* Contains aggregate functions */
  2207   2206   #define SF_UsesEphemeral   0x0008  /* Uses the OpenEphemeral opcode */
  2208   2207   #define SF_Expanded        0x0010  /* sqlite3SelectExpand() called on this */
  2209   2208   #define SF_HasTypeInfo     0x0020  /* FROM subqueries have Table metadata */
  2210         -#define SF_UseSorter       0x0040  /* Sort using a sorter */
         2209  +                    /*     0x0040  NOT USED */
  2211   2210   #define SF_Values          0x0080  /* Synthesized from VALUES clause */
  2212         -#define SF_Materialize     0x0100  /* NOT USED */
         2211  +                    /*     0x0100  NOT USED */
  2213   2212   #define SF_NestedFrom      0x0200  /* Part of a parenthesized FROM clause */
  2214   2213   #define SF_MaybeConvert    0x0400  /* Need convertCompoundSelectToSubquery() */
  2215   2214   #define SF_Recursive       0x0800  /* The recursive part of a recursive CTE */
  2216   2215   #define SF_Compound        0x1000  /* Part of a compound query */
  2217   2216   
  2218   2217   
  2219   2218   /*
................................................................................
  2258   2257   **     SRT_Coroutine   Generate a co-routine that returns a new row of
  2259   2258   **                     results each time it is invoked.  The entry point
  2260   2259   **                     of the co-routine is stored in register pDest->iSDParm
  2261   2260   **                     and the result row is stored in pDest->nDest registers
  2262   2261   **                     starting with pDest->iSdst.
  2263   2262   **
  2264   2263   **     SRT_Table       Store results in temporary table pDest->iSDParm.
  2265         -**                     This is like SRT_EphemTab except that the table
  2266         -**                     is assumed to already be open.
         2264  +**     SRT_Fifo        This is like SRT_EphemTab except that the table
         2265  +**                     is assumed to already be open.  SRT_Fifo has
         2266  +**                     the additional property of being able to ignore
         2267  +**                     the ORDER BY clause.
  2267   2268   **
  2268         -**     SRT_DistTable   Store results in a temporary table pDest->iSDParm.
         2269  +**     SRT_DistFifo    Store results in a temporary table pDest->iSDParm.
  2269   2270   **                     But also use temporary table pDest->iSDParm+1 as
  2270   2271   **                     a record of all prior results and ignore any duplicate
  2271         -**                     rows.  Name means:  "Distinct Table".
         2272  +**                     rows.  Name means:  "Distinct Fifo".
  2272   2273   **
  2273   2274   **     SRT_Queue       Store results in priority queue pDest->iSDParm (really
  2274   2275   **                     an index).  Append a sequence number so that all entries
  2275   2276   **                     are distinct.
  2276   2277   **
  2277   2278   **     SRT_DistQueue   Store results in priority queue pDest->iSDParm only if
  2278   2279   **                     the same record has never been stored before.  The
  2279   2280   **                     index at pDest->iSDParm+1 hold all prior stores.
  2280   2281   */
  2281   2282   #define SRT_Union        1  /* Store result as keys in an index */
  2282   2283   #define SRT_Except       2  /* Remove result from a UNION index */
  2283   2284   #define SRT_Exists       3  /* Store 1 if the result is not empty */
  2284   2285   #define SRT_Discard      4  /* Do not save the results anywhere */
         2286  +#define SRT_Fifo         5  /* Store result as data with an automatic rowid */
         2287  +#define SRT_DistFifo     6  /* Like SRT_Fifo, but unique results only */
         2288  +#define SRT_Queue        7  /* Store result in an queue */
         2289  +#define SRT_DistQueue    8  /* Like SRT_Queue, but unique results only */
  2285   2290   
  2286   2291   /* The ORDER BY clause is ignored for all of the above */
  2287         -#define IgnorableOrderby(X) ((X->eDest)<=SRT_Discard)
         2292  +#define IgnorableOrderby(X) ((X->eDest)<=SRT_DistQueue)
  2288   2293   
  2289         -#define SRT_Output       5  /* Output each row of result */
  2290         -#define SRT_Mem          6  /* Store result in a memory cell */
  2291         -#define SRT_Set          7  /* Store results as keys in an index */
  2292         -#define SRT_EphemTab     8  /* Create transient tab and store like SRT_Table */
  2293         -#define SRT_Coroutine    9  /* Generate a single row of result */
  2294         -#define SRT_Table       10  /* Store result as data with an automatic rowid */
  2295         -#define SRT_DistTable   11  /* Like SRT_Table, but unique results only */
  2296         -#define SRT_Queue       12  /* Store result in an queue */
  2297         -#define SRT_DistQueue   13  /* Like SRT_Queue, but unique results only */
         2294  +#define SRT_Output       9  /* Output each row of result */
         2295  +#define SRT_Mem         10  /* Store result in a memory cell */
         2296  +#define SRT_Set         11  /* Store results as keys in an index */
         2297  +#define SRT_EphemTab    12  /* Create transient tab and store like SRT_Table */
         2298  +#define SRT_Coroutine   13  /* Generate a single row of result */
         2299  +#define SRT_Table       14  /* Store result as data with an automatic rowid */
  2298   2300   
  2299   2301   /*
  2300   2302   ** An instance of this object describes where to put of the results of
  2301   2303   ** a SELECT statement.
  2302   2304   */
  2303   2305   struct SelectDest {
  2304   2306     u8 eDest;            /* How to dispose of the results.  On of SRT_* above. */
................................................................................
  2388   2390     char *zErrMsg;       /* An error message */
  2389   2391     Vdbe *pVdbe;         /* An engine for executing database bytecode */
  2390   2392     int rc;              /* Return code from execution */
  2391   2393     u8 colNamesSet;      /* TRUE after OP_ColumnName has been issued to pVdbe */
  2392   2394     u8 checkSchema;      /* Causes schema cookie check after an error */
  2393   2395     u8 nested;           /* Number of nested calls to the parser/code generator */
  2394   2396     u8 nTempReg;         /* Number of temporary registers in aTempReg[] */
  2395         -  u8 nColCache;        /* Number of entries in aColCache[] */
  2396         -  u8 iColCache;        /* Next entry in aColCache[] to replace */
  2397   2397     u8 isMultiWrite;     /* True if statement may modify/insert multiple rows */
  2398   2398     u8 mayAbort;         /* True if statement may throw an ABORT exception */
  2399   2399     u8 hasCompound;      /* Need to invoke convertCompoundSelectToSubquery() */
  2400   2400     u8 okConstFactor;    /* OK to factor out constants */
  2401   2401     int aTempReg[8];     /* Holding area for temporary registers */
  2402   2402     int nRangeReg;       /* Size of the temporary register block */
  2403   2403     int iRangeReg;       /* First register in temporary register block */
................................................................................
  3288   3288   #endif
  3289   3289   
  3290   3290   const char *sqlite3ErrStr(int);
  3291   3291   int sqlite3ReadSchema(Parse *pParse);
  3292   3292   CollSeq *sqlite3FindCollSeq(sqlite3*,u8 enc, const char*,int);
  3293   3293   CollSeq *sqlite3LocateCollSeq(Parse *pParse, const char*zName);
  3294   3294   CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr);
  3295         -Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr*, Token*);
         3295  +Expr *sqlite3ExprAddCollateToken(Parse *pParse, Expr*, const Token*);
  3296   3296   Expr *sqlite3ExprAddCollateString(Parse*,Expr*,const char*);
  3297   3297   Expr *sqlite3ExprSkipCollate(Expr*);
  3298   3298   int sqlite3CheckCollSeq(Parse *, CollSeq *);
  3299   3299   int sqlite3CheckObjectName(Parse *, const char *);
  3300   3300   void sqlite3VdbeSetChanges(sqlite3 *, int);
  3301   3301   int sqlite3AddInt64(i64*,i64);
  3302   3302   int sqlite3SubInt64(i64*,i64);

Changes to src/test_syscall.c.

    63     63   **
    64     64   **   test_syscall exists SYSTEM-CALL
    65     65   **     Return true if the named system call exists. Or false otherwise.
    66     66   **
    67     67   **   test_syscall list
    68     68   **     Return a list of all system calls. The list is constructed using
    69     69   **     the xNextSystemCall() VFS method.
           70  +**
           71  +**   test_syscall pagesize PGSZ
           72  +**     If PGSZ is a power of two greater than 256, install a wrapper around
           73  +**     OS function getpagesize() that reports the system page size as PGSZ.
           74  +**     Or, if PGSZ is less than zero, remove any wrapper already installed.
    70     75   */
    71     76   
    72     77   #include "sqliteInt.h"
    73     78   #include "sqlite3.h"
    74     79   #include "tcl.h"
    75     80   #include <stdlib.h>
    76     81   #include <string.h>
................................................................................
    85     90   #include <sys/types.h>
    86     91   #include <errno.h>
    87     92   
    88     93   static struct TestSyscallGlobal {
    89     94     int bPersist;                   /* 1 for persistent errors, 0 for transient */
    90     95     int nCount;                     /* Fail after this many more calls */
    91     96     int nFail;                      /* Number of failures that have occurred */
    92         -} gSyscall = { 0, 0 };
           97  +  int pgsz;
           98  +  sqlite3_syscall_ptr orig_getpagesize;
           99  +} gSyscall = { 0, 0, 0, 0, 0 };
    93    100   
    94    101   static int ts_open(const char *, int, int);
    95    102   static int ts_close(int fd);
    96    103   static int ts_access(const char *zPath, int mode);
    97    104   static char *ts_getcwd(char *zPath, size_t nPath);
    98    105   static int ts_stat(const char *zPath, struct stat *p);
    99    106   static int ts_fstat(int fd, struct stat *p);
................................................................................
   645    652       return TCL_ERROR;
   646    653     }
   647    654   
   648    655     pVfs = sqlite3_vfs_find(0);
   649    656     Tcl_SetObjResult(interp, Tcl_NewStringObj(pVfs->zName, -1));
   650    657     return TCL_OK;
   651    658   }
          659  +
          660  +static int ts_getpagesize(void){
          661  +  return gSyscall.pgsz;
          662  +}
          663  +
          664  +static int test_syscall_pagesize(
          665  +  void * clientData,
          666  +  Tcl_Interp *interp,
          667  +  int objc,
          668  +  Tcl_Obj *CONST objv[]
          669  +){
          670  +  sqlite3_vfs *pVfs = sqlite3_vfs_find(0);
          671  +  int pgsz;
          672  +  if( objc!=3 ){
          673  +    Tcl_WrongNumArgs(interp, 2, objv, "PGSZ");
          674  +    return TCL_ERROR;
          675  +  }
          676  +  if( Tcl_GetIntFromObj(interp, objv[2], &pgsz) ){
          677  +    return TCL_ERROR;
          678  +  }
          679  +
          680  +  if( pgsz<0 ){
          681  +    if( gSyscall.orig_getpagesize ){
          682  +      pVfs->xSetSystemCall(pVfs, "getpagesize", gSyscall.orig_getpagesize);
          683  +    }
          684  +  }else{
          685  +    if( pgsz<512 || (pgsz & (pgsz-1)) ){
          686  +      Tcl_AppendResult(interp, "pgsz out of range", 0);
          687  +      return TCL_ERROR;
          688  +    }
          689  +    gSyscall.orig_getpagesize = pVfs->xGetSystemCall(pVfs, "getpagesize");
          690  +    gSyscall.pgsz = pgsz;
          691  +    pVfs->xSetSystemCall(
          692  +        pVfs, "getpagesize", (sqlite3_syscall_ptr)ts_getpagesize
          693  +    );
          694  +  }
          695  +
          696  +  return TCL_OK;
          697  +}
   652    698   
   653    699   static int test_syscall(
   654    700     void * clientData,
   655    701     Tcl_Interp *interp,
   656    702     int objc,
   657    703     Tcl_Obj *CONST objv[]
   658    704   ){
................................................................................
   664    710       { "install",    test_syscall_install },
   665    711       { "uninstall",  test_syscall_uninstall },
   666    712       { "reset",      test_syscall_reset },
   667    713       { "errno",      test_syscall_errno },
   668    714       { "exists",     test_syscall_exists },
   669    715       { "list",       test_syscall_list },
   670    716       { "defaultvfs", test_syscall_defaultvfs },
          717  +    { "pagesize",   test_syscall_pagesize },
   671    718       { 0, 0 }
   672    719     };
   673    720     int iCmd;
   674    721     int rc;
   675    722   
   676    723     if( objc<2 ){
   677    724       Tcl_WrongNumArgs(interp, 1, objv, "SUB-COMMAND ...");

Changes to src/vdbe.c.

   305    305   void sqlite3ValueApplyAffinity(
   306    306     sqlite3_value *pVal, 
   307    307     u8 affinity, 
   308    308     u8 enc
   309    309   ){
   310    310     applyAffinity((Mem *)pVal, affinity, enc);
   311    311   }
          312  +
          313  +/*
          314  +** Return the numeric type for pMem, either MEM_Int or MEM_Real or both or
          315  +** none.  
          316  +**
          317  +** Unlike applyNumericAffinity(), this routine does not modify pMem->flags.
          318  +** But it does set pMem->r and pMem->u.i appropriately.
          319  +*/
          320  +static u16 numericType(Mem *pMem){
          321  +  if( pMem->flags & (MEM_Int|MEM_Real) ){
          322  +    return pMem->flags & (MEM_Int|MEM_Real);
          323  +  }
          324  +  if( pMem->flags & (MEM_Str|MEM_Blob) ){
          325  +    if( sqlite3AtoF(pMem->z, &pMem->r, pMem->n, pMem->enc)==0 ){
          326  +      return 0;
          327  +    }
          328  +    if( sqlite3Atoi64(pMem->z, &pMem->u.i, pMem->n, pMem->enc)==SQLITE_OK ){
          329  +      return MEM_Int;
          330  +    }
          331  +    return MEM_Real;
          332  +  }
          333  +  return 0;
          334  +}
   312    335   
   313    336   #ifdef SQLITE_DEBUG
   314    337   /*
   315    338   ** Write a nice string representation of the contents of cell pMem
   316    339   ** into buffer zBuf, length nBuf.
   317    340   */
   318    341   void sqlite3VdbeMemPrettyPrint(Mem *pMem, char *zBuf){
................................................................................
  1076   1099     UPDATE_MAX_BLOBSIZE(pOut);
  1077   1100     break;
  1078   1101   }
  1079   1102   
  1080   1103   /* Opcode: Move P1 P2 P3 * *
  1081   1104   ** Synopsis:  r[P2@P3]=r[P1@P3]
  1082   1105   **
  1083         -** Move the values in register P1..P1+P3 over into
  1084         -** registers P2..P2+P3.  Registers P1..P1+P3 are
         1106  +** Move the P3 values in register P1..P1+P3-1 over into
         1107  +** registers P2..P2+P3-1.  Registers P1..P1+P3-1 are
  1085   1108   ** left holding a NULL.  It is an error for register ranges
  1086         -** P1..P1+P3 and P2..P2+P3 to overlap.
         1109  +** P1..P1+P3-1 and P2..P2+P3-1 to overlap.  It is an error
         1110  +** for P3 to be less than 1.
  1087   1111   */
  1088   1112   case OP_Move: {
  1089   1113     char *zMalloc;   /* Holding variable for allocated memory */
  1090   1114     int n;           /* Number of registers left to copy */
  1091   1115     int p1;          /* Register to copy from */
  1092   1116     int p2;          /* Register to copy to */
  1093   1117   
  1094   1118     n = pOp->p3;
  1095   1119     p1 = pOp->p1;
  1096   1120     p2 = pOp->p2;
  1097         -  assert( n>=0 && p1>0 && p2>0 );
         1121  +  assert( n>0 && p1>0 && p2>0 );
  1098   1122     assert( p1+n<=p2 || p2+n<=p1 );
  1099   1123   
  1100   1124     pIn1 = &aMem[p1];
  1101   1125     pOut = &aMem[p2];
  1102   1126     do{
  1103   1127       assert( pOut<=&aMem[(p->nMem-p->nCursor)] );
  1104   1128       assert( pIn1<=&aMem[(p->nMem-p->nCursor)] );
................................................................................
  1114   1138   #endif
  1115   1139       pIn1->flags = MEM_Undefined;
  1116   1140       pIn1->xDel = 0;
  1117   1141       pIn1->zMalloc = zMalloc;
  1118   1142       REGISTER_TRACE(p2++, pOut);
  1119   1143       pIn1++;
  1120   1144       pOut++;
  1121         -  }while( n-- );
         1145  +  }while( --n );
  1122   1146     break;
  1123   1147   }
  1124   1148   
  1125   1149   /* Opcode: Copy P1 P2 P3 * *
  1126   1150   ** Synopsis: r[P2@P3+1]=r[P1@P3+1]
  1127   1151   **
  1128   1152   ** Make a copy of registers P1..P1+P3 into registers P2..P2+P3.
................................................................................
  1346   1370   */
  1347   1371   case OP_Add:                   /* same as TK_PLUS, in1, in2, out3 */
  1348   1372   case OP_Subtract:              /* same as TK_MINUS, in1, in2, out3 */
  1349   1373   case OP_Multiply:              /* same as TK_STAR, in1, in2, out3 */
  1350   1374   case OP_Divide:                /* same as TK_SLASH, in1, in2, out3 */
  1351   1375   case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
  1352   1376     char bIntint;   /* Started out as two integer operands */
  1353         -  int flags;      /* Combined MEM_* flags from both inputs */
         1377  +  u16 flags;      /* Combined MEM_* flags from both inputs */
         1378  +  u16 type1;      /* Numeric type of left operand */
         1379  +  u16 type2;      /* Numeric type of right operand */
  1354   1380     i64 iA;         /* Integer value of left operand */
  1355   1381     i64 iB;         /* Integer value of right operand */
  1356   1382     double rA;      /* Real value of left operand */
  1357   1383     double rB;      /* Real value of right operand */
  1358   1384   
  1359   1385     pIn1 = &aMem[pOp->p1];
  1360         -  applyNumericAffinity(pIn1);
         1386  +  type1 = numericType(pIn1);
  1361   1387     pIn2 = &aMem[pOp->p2];
  1362         -  applyNumericAffinity(pIn2);
         1388  +  type2 = numericType(pIn2);
  1363   1389     pOut = &aMem[pOp->p3];
  1364   1390     flags = pIn1->flags | pIn2->flags;
  1365   1391     if( (flags & MEM_Null)!=0 ) goto arithmetic_result_is_null;
  1366         -  if( (pIn1->flags & pIn2->flags & MEM_Int)==MEM_Int ){
         1392  +  if( (type1 & type2 & MEM_Int)!=0 ){
  1367   1393       iA = pIn1->u.i;
  1368   1394       iB = pIn2->u.i;
  1369   1395       bIntint = 1;
  1370   1396       switch( pOp->opcode ){
  1371   1397         case OP_Add:       if( sqlite3AddInt64(&iB,iA) ) goto fp_math;  break;
  1372   1398         case OP_Subtract:  if( sqlite3SubInt64(&iB,iA) ) goto fp_math;  break;
  1373   1399         case OP_Multiply:  if( sqlite3MulInt64(&iB,iA) ) goto fp_math;  break;
................................................................................
  1415   1441       MemSetTypeFlag(pOut, MEM_Int);
  1416   1442   #else
  1417   1443       if( sqlite3IsNaN(rB) ){
  1418   1444         goto arithmetic_result_is_null;
  1419   1445       }
  1420   1446       pOut->r = rB;
  1421   1447       MemSetTypeFlag(pOut, MEM_Real);
  1422         -    if( (flags & MEM_Real)==0 && !bIntint ){
         1448  +    if( ((type1|type2)&MEM_Real)==0 && !bIntint ){
  1423   1449         sqlite3VdbeIntegerAffinity(pOut);
  1424   1450       }
  1425   1451   #endif
  1426   1452     }
  1427   1453     break;
  1428   1454   
  1429   1455   arithmetic_result_is_null:
................................................................................
  1991   2017     assert( pOp->p4type==P4_INTARRAY );
  1992   2018     assert( pOp->p4.ai );
  1993   2019     aPermute = pOp->p4.ai;
  1994   2020     break;
  1995   2021   }
  1996   2022   
  1997   2023   /* Opcode: Compare P1 P2 P3 P4 P5
         2024  +** Synopsis: r[P1@P3] <-> r[P2@P3]
  1998   2025   **
  1999   2026   ** Compare two vectors of registers in reg(P1)..reg(P1+P3-1) (call this
  2000   2027   ** vector "A") and in reg(P2)..reg(P2+P3-1) ("B").  Save the result of
  2001   2028   ** the comparison for use by the next OP_Jump instruct.
  2002   2029   **
  2003   2030   ** If P5 has the OPFLAG_PERMUTE bit set, then the order of comparison is
  2004   2031   ** determined by the most recent OP_Permutation operator.  If the
................................................................................
  3326   3353         SQLITE_OPEN_DELETEONCLOSE |
  3327   3354         SQLITE_OPEN_TRANSIENT_DB;
  3328   3355     assert( pOp->p1>=0 );
  3329   3356     assert( pOp->p2>=0 );
  3330   3357     pCx = allocateCursor(p, pOp->p1, pOp->p2, -1, 1);
  3331   3358     if( pCx==0 ) goto no_mem;
  3332   3359     pCx->nullRow = 1;
         3360  +  pCx->isEphemeral = 1;
  3333   3361     rc = sqlite3BtreeOpen(db->pVfs, 0, db, &pCx->pBt, 
  3334   3362                           BTREE_OMIT_JOURNAL | BTREE_SINGLE | pOp->p5, vfsFlags);
  3335   3363     if( rc==SQLITE_OK ){
  3336   3364       rc = sqlite3BtreeBeginTrans(pCx->pBt, 1);
  3337   3365     }
  3338   3366     if( rc==SQLITE_OK ){
  3339   3367       /* If a transient index is required, create it by calling
................................................................................
  3816   3844       assert( pC->rowidIsValid==0 );
  3817   3845     }
  3818   3846     pC->seekResult = res;
  3819   3847     break;
  3820   3848   }
  3821   3849   
  3822   3850   /* Opcode: Sequence P1 P2 * * *
  3823         -** Synopsis: r[P2]=rowid
         3851  +** Synopsis: r[P2]=cursor[P1].ctr++
  3824   3852   **
  3825   3853   ** Find the next available sequence number for cursor P1.
  3826   3854   ** Write the sequence number into register P2.
  3827   3855   ** The sequence number on the cursor is incremented after this
  3828   3856   ** instruction.  
  3829   3857   */
  3830   3858   case OP_Sequence: {           /* out2-prerelease */
................................................................................
  4507   4535   */
  4508   4536   case OP_SorterNext: {  /* jump */
  4509   4537     VdbeCursor *pC;
  4510   4538     int res;
  4511   4539   
  4512   4540     pC = p->apCsr[pOp->p1];
  4513   4541     assert( isSorter(pC) );
         4542  +  res = 0;
  4514   4543     rc = sqlite3VdbeSorterNext(db, pC, &res);
  4515   4544     goto next_tail;
  4516   4545   case OP_PrevIfOpen:    /* jump */
  4517   4546   case OP_NextIfOpen:    /* jump */
  4518   4547     if( p->apCsr[pOp->p1]==0 ) break;
  4519   4548     /* Fall through */
  4520   4549   case OP_Prev:          /* jump */
................................................................................
  4864   4893         assert( memIsValid(&aMem[pOp->p3]) );
  4865   4894         memAboutToChange(p, &aMem[pOp->p3]);
  4866   4895         aMem[pOp->p3].u.i += nChange;
  4867   4896       }
  4868   4897     }
  4869   4898     break;
  4870   4899   }
         4900  +
         4901  +/* Opcode: ResetSorter P1 * * * *
         4902  +**
         4903  +** Delete all contents from the ephemeral table or sorter
         4904  +** that is open on cursor P1.
         4905  +**
         4906  +** This opcode only works for cursors used for sorting and
         4907  +** opened with OP_OpenEphemeral or OP_SorterOpen.
         4908  +*/
         4909  +case OP_ResetSorter: {
         4910  +  VdbeCursor *pC;
         4911  + 
         4912  +  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
         4913  +  pC = p->apCsr[pOp->p1];
         4914  +  assert( pC!=0 );
         4915  +  if( pC->pSorter ){
         4916  +    sqlite3VdbeSorterReset(db, pC->pSorter);
         4917  +  }else{
         4918  +    assert( pC->isEphemeral );
         4919  +    rc = sqlite3BtreeClearTableOfCursor(pC->pCursor);
         4920  +  }
         4921  +  break;
         4922  +}
  4871   4923   
  4872   4924   /* Opcode: CreateTable P1 P2 * * *
  4873   4925   ** Synopsis: r[P2]=root iDb=P1
  4874   4926   **
  4875   4927   ** Allocate a new table in the main database file if P1==0 or in the
  4876   4928   ** auxiliary database file if P1==1 or in an attached database if
  4877   4929   ** P1>1.  Write the root page number of the new table into

Changes to src/vdbeInt.h.

    68     68     int pseudoTableReg;   /* Register holding pseudotable content. */
    69     69     i16 nField;           /* Number of fields in the header */
    70     70     u16 nHdrParsed;       /* Number of header fields parsed so far */
    71     71     i8 iDb;               /* Index of cursor database in db->aDb[] (or -1) */
    72     72     u8 nullRow;           /* True if pointing to a row with no data */
    73     73     u8 rowidIsValid;      /* True if lastRowid is valid */
    74     74     u8 deferredMoveto;    /* A call to sqlite3BtreeMoveto() is needed */
           75  +  Bool isEphemeral:1;   /* True for an ephemeral table */
    75     76     Bool useRandomRowid:1;/* Generate new record numbers semi-randomly */
    76     77     Bool isTable:1;       /* True if a table requiring integer keys */
    77     78     Bool isOrdered:1;     /* True if the underlying table is BTREE_UNORDERED */
    78     79     sqlite3_vtab_cursor *pVtabCursor;  /* The cursor for a virtual table */
    79     80     i64 seqCount;         /* Sequence counter */
    80     81     i64 movetoTarget;     /* Argument to the deferred sqlite3BtreeMoveto() */
    81     82     i64 lastRowid;        /* Rowid being deleted by OP_Delete */
................................................................................
   433    434   int sqlite3VdbeMemGrow(Mem *pMem, int n, int preserve);
   434    435   int sqlite3VdbeCloseStatement(Vdbe *, int);
   435    436   void sqlite3VdbeFrameDelete(VdbeFrame*);
   436    437   int sqlite3VdbeFrameRestore(VdbeFrame *);
   437    438   int sqlite3VdbeTransferError(Vdbe *p);
   438    439   
   439    440   int sqlite3VdbeSorterInit(sqlite3 *, VdbeCursor *);
          441  +void sqlite3VdbeSorterReset(sqlite3 *, VdbeSorter *);
   440    442   void sqlite3VdbeSorterClose(sqlite3 *, VdbeCursor *);
   441    443   int sqlite3VdbeSorterRowkey(const VdbeCursor *, Mem *);
   442    444   int sqlite3VdbeSorterNext(sqlite3 *, const VdbeCursor *, int *);
   443    445   int sqlite3VdbeSorterRewind(sqlite3 *, const VdbeCursor *, int *);
   444    446   int sqlite3VdbeSorterWrite(sqlite3 *, const VdbeCursor *, Mem *);
   445    447   int sqlite3VdbeSorterCompare(const VdbeCursor *, Mem *, int, int *);
   446    448   

Changes to src/vdbeaux.c.

   779    779     }
   780    780     assert( p->nOp>0 );
   781    781     assert( addr<p->nOp );
   782    782     if( addr<0 ){
   783    783       addr = p->nOp - 1;
   784    784     }
   785    785     pOp = &p->aOp[addr];
   786         -  assert( pOp->p4type==P4_NOTUSED || pOp->p4type==P4_INT32 );
          786  +  assert( pOp->p4type==P4_NOTUSED
          787  +       || pOp->p4type==P4_INT32
          788  +       || pOp->p4type==P4_KEYINFO );
   787    789     freeP4(db, pOp->p4type, pOp->p4.p);
   788    790     pOp->p4.p = 0;
   789    791     if( n==P4_INT32 ){
   790    792       /* Note: this cast is safe, because the origin data point was an int
   791    793       ** that was cast to a (const char *). */
   792    794       pOp->p4.i = SQLITE_PTR_TO_INT(zP4);
   793    795       pOp->p4type = P4_INT32;
................................................................................
  2729   2731       p->cacheStatus = CACHE_STALE;
  2730   2732     }else if( p->pCursor ){
  2731   2733       int hasMoved;
  2732   2734       int rc = sqlite3BtreeCursorHasMoved(p->pCursor, &hasMoved);
  2733   2735       if( rc ) return rc;
  2734   2736       if( hasMoved ){
  2735   2737         p->cacheStatus = CACHE_STALE;
  2736         -      p->nullRow = 1;
         2738  +      if( hasMoved==2 ) p->nullRow = 1;
  2737   2739       }
  2738   2740     }
  2739   2741     return SQLITE_OK;
  2740   2742   }
  2741   2743   
  2742   2744   /*
  2743   2745   ** The following functions:

Changes to src/vdbesort.c.

   593    593   
   594    594   /*
   595    595   ** Free all resources owned by the object indicated by argument pThread. All 
   596    596   ** fields of *pThread are zeroed before returning.
   597    597   */
   598    598   static void vdbeSorterThreadCleanup(sqlite3 *db, SorterThread *pThread){
   599    599     sqlite3DbFree(db, pThread->pUnpacked);
          600  +  pThread->pUnpacked = 0;
   600    601     vdbeSorterRecordFree(0, pThread->pList);
          602  +  pThread->pList = 0;
   601    603     if( pThread->pTemp1 ){
   602    604       sqlite3OsCloseFree(pThread->pTemp1);
          605  +    pThread->pTemp1 = 0;
   603    606     }
   604         -  memset(pThread, 0, sizeof(SorterThread));
   605    607   }
   606    608   
   607    609   /*
   608    610   ** Join all threads.  
   609    611   */
   610    612   static int vdbeSorterJoinAll(VdbeSorter *pSorter, int rcin){
   611    613     int rc = rcin;
................................................................................
   638    640   
   639    641     pNew = (SorterMerger*)sqlite3MallocZero(nByte);
   640    642     if( pNew ){
   641    643       pNew->nTree = N;
   642    644       pNew->aIter = (VdbeSorterIter*)&pNew[1];
   643    645       pNew->aTree = (int*)&pNew->aIter[N];
   644    646     }
   645         -
   646    647     return pNew;
   647    648   }
          649  +
          650  +/*
          651  +** Reset a merger
          652  +*/
          653  +static void vdbeSorterMergerReset(SorterMerger *pMerger){
          654  +  int i;
          655  +  if( pMerger ){
          656  +    for(i=0; i<pMerger->nTree; i++){
          657  +      vdbeSorterIterZero(&pMerger->aIter[i]);
          658  +    }
          659  +  }
          660  +}
          661  +
   648    662   
   649    663   /*
   650    664   ** Free the SorterMerger object passed as the only argument.
   651    665   */
   652    666   static void vdbeSorterMergerFree(SorterMerger *pMerger){
   653         -  if( pMerger ){
   654         -    int i;
   655         -    for(i=0; i<pMerger->nTree; i++){
   656         -      vdbeSorterIterZero(&pMerger->aIter[i]);
   657         -    }
   658         -    sqlite3_free(pMerger);
          667  +  vdbeSorterMergerReset(pMerger);
          668  +  sqlite3_free(pMerger);
          669  +}
          670  +
          671  +/*
          672  +** Reset a sorting cursor back to its original empty state.
          673  +*/
          674  +void sqlite3VdbeSorterReset(sqlite3 *db, VdbeSorter *pSorter){
          675  +  int i;
          676  +  vdbeSorterJoinAll(pSorter, SQLITE_OK);
          677  +  for(i=0; i<SQLITE_MAX_SORTER_THREAD; i++){
          678  +    SorterThread *pThread = &pSorter->aThread[i];
          679  +    vdbeSorterThreadCleanup(db, pThread);
   659    680     }
          681  +  vdbeSorterRecordFree(0, pSorter->pRecord);
          682  +  vdbeSorterMergerReset(pSorter->pMerger);
          683  +  pSorter->pRecord = 0;
          684  +  pSorter->nInMemory = 0;
          685  +  pSorter->bUsePMA = 0;
   660    686   }
   661    687   
   662    688   /*
   663    689   ** Free any cursor components allocated by sqlite3VdbeSorterXXX routines.
   664    690   */
   665    691   void sqlite3VdbeSorterClose(sqlite3 *db, VdbeCursor *pCsr){
   666    692     VdbeSorter *pSorter = pCsr->pSorter;
   667    693     if( pSorter ){
   668         -    int i;
   669         -    vdbeSorterJoinAll(pSorter, SQLITE_OK);
   670         -    for(i=0; i<SQLITE_MAX_SORTER_THREAD; i++){
   671         -      SorterThread *pThread = &pSorter->aThread[i];
   672         -      vdbeSorterThreadCleanup(db, pThread);
   673         -    }
   674         -
   675         -    vdbeSorterRecordFree(0, pSorter->pRecord);
          694  +    sqlite3VdbeSorterReset(db, pSorter);
   676    695       vdbeSorterMergerFree(pSorter->pMerger);
   677    696       sqlite3DbFree(db, pSorter);
   678    697       pCsr->pSorter = 0;
   679    698     }
   680    699   }
   681    700   
   682    701   /*

Changes to src/where.c.

    35     35   }
    36     36   
    37     37   /*
    38     38   ** Return TRUE if the WHERE clause returns rows in ORDER BY order.
    39     39   ** Return FALSE if the output needs to be sorted.
    40     40   */
    41     41   int sqlite3WhereIsOrdered(WhereInfo *pWInfo){
    42         -  return pWInfo->bOBSat!=0;
           42  +  return pWInfo->nOBSat;
    43     43   }
    44     44   
    45     45   /*
    46     46   ** Return the VDBE address or label to jump to in order to continue
    47     47   ** immediately with the next row of a WHERE clause.
    48     48   */
    49     49   int sqlite3WhereContinueLabel(WhereInfo *pWInfo){
           50  +  assert( pWInfo->iContinue!=0 );
    50     51     return pWInfo->iContinue;
    51     52   }
    52     53   
    53     54   /*
    54     55   ** Return the VDBE address or label to jump to in order to break
    55     56   ** out of a WHERE loop.
    56     57   */
................................................................................
  3032   3033       ** was passed to this function to implement a "SELECT min(x) ..." 
  3033   3034       ** query, then the caller will only allow the loop to run for
  3034   3035       ** a single iteration. This means that the first row returned
  3035   3036       ** should not have a NULL value stored in 'x'. If column 'x' is
  3036   3037       ** the first one after the nEq equality constraints in the index,
  3037   3038       ** this requires some special handling.
  3038   3039       */
         3040  +    assert( pWInfo->pOrderBy==0
         3041  +         || pWInfo->pOrderBy->nExpr==1
         3042  +         || (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 );
  3039   3043       if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0
  3040         -     && (pWInfo->bOBSat!=0)
         3044  +     && pWInfo->nOBSat>0
  3041   3045        && (pIdx->nKeyCol>nEq)
  3042   3046       ){
  3043   3047         assert( pLoop->u.btree.nSkip==0 );
  3044   3048         bSeekPastNull = 1;
  3045   3049         nExtraReg = 1;
  3046   3050       }
  3047   3051   
................................................................................
  3204   3208         pLevel->op = OP_Noop;
  3205   3209       }else if( bRev ){
  3206   3210         pLevel->op = OP_Prev;
  3207   3211       }else{
  3208   3212         pLevel->op = OP_Next;
  3209   3213       }
  3210   3214       pLevel->p1 = iIdxCur;
  3211         -    assert( (WHERE_UNQ_WANTED>>16)==1 );
  3212         -    pLevel->p3 = (pLoop->wsFlags>>16)&1;
         3215  +    pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0;
  3213   3216       if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
  3214   3217         pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
  3215   3218       }else{
  3216   3219         assert( pLevel->p5==0 );
  3217   3220       }
  3218   3221     }else
  3219   3222   
................................................................................
  4006   4009         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  4007   4010           /* "x IN (SELECT ...)":  TUNING: the SELECT returns 25 rows */
  4008   4011           nIn = 46;  assert( 46==sqlite3LogEst(25) );
  4009   4012         }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  4010   4013           /* "x IN (value, value, ...)" */
  4011   4014           nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
  4012   4015         }
         4016  +      assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
         4017  +                        ** changes "x IN (?)" into "x=?". */
  4013   4018         pNew->rRun += nIn;
  4014   4019         pNew->u.btree.nEq++;
  4015   4020         pNew->nOut = nRowEst + nInMul + nIn;
  4016   4021       }else if( pTerm->eOperator & (WO_EQ) ){
  4017   4022         assert(
  4018   4023           (pNew->wsFlags & (WHERE_COLUMN_NULL|WHERE_COLUMN_IN|WHERE_SKIPSCAN))!=0
  4019   4024           || nInMul==0
................................................................................
  4502   4507       if( i>=nConstraint ){
  4503   4508         pNew->nLTerm = mxTerm+1;
  4504   4509         assert( pNew->nLTerm<=pNew->nLSlot );
  4505   4510         pNew->u.vtab.idxNum = pIdxInfo->idxNum;
  4506   4511         pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
  4507   4512         pIdxInfo->needToFreeIdxStr = 0;
  4508   4513         pNew->u.vtab.idxStr = pIdxInfo->idxStr;
  4509         -      pNew->u.vtab.isOrdered = (u8)((pIdxInfo->nOrderBy!=0)
  4510         -                                     && pIdxInfo->orderByConsumed);
         4514  +      pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ?
         4515  +                                      pIdxInfo->nOrderBy : 0);
  4511   4516         pNew->rSetup = 0;
  4512   4517         pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost);
  4513   4518         pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows);
  4514   4519         whereLoopInsert(pBuilder, pNew);
  4515   4520         if( pNew->u.vtab.needFree ){
  4516   4521           sqlite3_free(pNew->u.vtab.idxStr);
  4517   4522           pNew->u.vtab.needFree = 0;
................................................................................
  4664   4669     whereLoopClear(db, pNew);
  4665   4670     return rc;
  4666   4671   }
  4667   4672   
  4668   4673   /*
  4669   4674   ** Examine a WherePath (with the addition of the extra WhereLoop of the 5th
  4670   4675   ** parameters) to see if it outputs rows in the requested ORDER BY
  4671         -** (or GROUP BY) without requiring a separate sort operation.  Return:
         4676  +** (or GROUP BY) without requiring a separate sort operation.  Return N:
  4672   4677   ** 
  4673         -**    0:  ORDER BY is not satisfied.  Sorting required
  4674         -**    1:  ORDER BY is satisfied.      Omit sorting
  4675         -**   -1:  Unknown at this time
         4678  +**   N>0:   N terms of the ORDER BY clause are satisfied
         4679  +**   N==0:  No terms of the ORDER BY clause are satisfied
         4680  +**   N<0:   Unknown yet how many terms of ORDER BY might be satisfied.   
  4676   4681   **
  4677   4682   ** Note that processing for WHERE_GROUPBY and WHERE_DISTINCTBY is not as
  4678   4683   ** strict.  With GROUP BY and DISTINCT the only requirement is that
  4679   4684   ** equivalent rows appear immediately adjacent to one another.  GROUP BY
  4680   4685   ** and DISTINT do not require rows to appear in any particular order as long
  4681   4686   ** as equivelent rows are grouped together.  Thus for GROUP BY and DISTINCT
  4682   4687   ** the pOrderBy terms can be matched in any order.  With ORDER BY, the 
  4683   4688   ** pOrderBy terms must be matched in strict left-to-right order.
  4684   4689   */
  4685         -static int wherePathSatisfiesOrderBy(
         4690  +static i8 wherePathSatisfiesOrderBy(
  4686   4691     WhereInfo *pWInfo,    /* The WHERE clause */
  4687   4692     ExprList *pOrderBy,   /* ORDER BY or GROUP BY or DISTINCT clause to check */
  4688   4693     WherePath *pPath,     /* The WherePath to check */
  4689   4694     u16 wctrlFlags,       /* Might contain WHERE_GROUPBY or WHERE_DISTINCTBY */
  4690   4695     u16 nLoop,            /* Number of entries in pPath->aLoop[] */
  4691   4696     WhereLoop *pLast,     /* Add this WhereLoop to the end of pPath->aLoop[] */
  4692   4697     Bitmask *pRevMask     /* OUT: Mask of WhereLoops to run in reverse order */
................................................................................
  4862   4867             if( iColumn>=0 ){
  4863   4868               pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  4864   4869               if( !pColl ) pColl = db->pDfltColl;
  4865   4870               if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
  4866   4871             }
  4867   4872             isMatch = 1;
  4868   4873             break;
         4874  +        }
         4875  +        if( isMatch && (pWInfo->wctrlFlags & WHERE_GROUPBY)==0 ){
         4876  +          /* Make sure the sort order is compatible in an ORDER BY clause.
         4877  +          ** Sort order is irrelevant for a GROUP BY clause. */
         4878  +          if( revSet ){
         4879  +            if( (rev ^ revIdx)!=pOrderBy->a[i].sortOrder ) isMatch = 0;
         4880  +          }else{
         4881  +            rev = revIdx ^ pOrderBy->a[i].sortOrder;
         4882  +            if( rev ) *pRevMask |= MASKBIT(iLoop);
         4883  +            revSet = 1;
         4884  +          }
  4869   4885           }
  4870   4886           if( isMatch ){
  4871   4887             if( iColumn<0 ){
  4872   4888               testcase( distinctColumns==0 );
  4873   4889               distinctColumns = 1;
  4874   4890             }
  4875   4891             obSat |= MASKBIT(i);
  4876         -          if( (pWInfo->wctrlFlags & WHERE_GROUPBY)==0 ){
  4877         -            /* Make sure the sort order is compatible in an ORDER BY clause.
  4878         -            ** Sort order is irrelevant for a GROUP BY clause. */
  4879         -            if( revSet ){
  4880         -              if( (rev ^ revIdx)!=pOrderBy->a[i].sortOrder ) return 0;
  4881         -            }else{
  4882         -              rev = revIdx ^ pOrderBy->a[i].sortOrder;
  4883         -              if( rev ) *pRevMask |= MASKBIT(iLoop);
  4884         -              revSet = 1;
  4885         -            }
  4886         -          }
  4887   4892           }else{
  4888   4893             /* No match found */
  4889   4894             if( j==0 || j<nKeyCol ){
  4890   4895               testcase( isOrderDistinct!=0 );
  4891   4896               isOrderDistinct = 0;
  4892   4897             }
  4893   4898             break;
................................................................................
  4911   4916           if( mTerm==0 && !sqlite3ExprIsConstant(p) ) continue;
  4912   4917           if( (mTerm&~orderDistinctMask)==0 ){
  4913   4918             obSat |= MASKBIT(i);
  4914   4919           }
  4915   4920         }
  4916   4921       }
  4917   4922     } /* End the loop over all WhereLoops from outer-most down to inner-most */
  4918         -  if( obSat==obDone ) return 1;
  4919         -  if( !isOrderDistinct ) return 0;
         4923  +  if( obSat==obDone ) return nOrderBy;
         4924  +  if( !isOrderDistinct ){
         4925  +    for(i=nOrderBy-1; i>0; i--){
         4926  +      Bitmask m = MASKBIT(i) - 1;
         4927  +      if( (obSat&m)==m ) return i;
         4928  +    }
         4929  +    return 0;
         4930  +  }
  4920   4931     return -1;
  4921   4932   }
  4922   4933   
  4923   4934   #ifdef WHERETRACE_ENABLED
  4924   4935   /* For debugging use only: */
  4925   4936   static const char *wherePathName(WherePath *pPath, int nLoop, WhereLoop *pLast){
  4926   4937     static char zName[65];
................................................................................
  4949   4960     int mxChoice;             /* Maximum number of simultaneous paths tracked */
  4950   4961     int nLoop;                /* Number of terms in the join */
  4951   4962     Parse *pParse;            /* Parsing context */
  4952   4963     sqlite3 *db;              /* The database connection */
  4953   4964     int iLoop;                /* Loop counter over the terms of the join */
  4954   4965     int ii, jj;               /* Loop counters */
  4955   4966     int mxI = 0;              /* Index of next entry to replace */
         4967  +  int nOrderBy;             /* Number of ORDER BY clause terms */
  4956   4968     LogEst rCost;             /* Cost of a path */
  4957   4969     LogEst nOut;              /* Number of outputs */
  4958   4970     LogEst mxCost = 0;        /* Maximum cost of a set of paths */
  4959   4971     LogEst mxOut = 0;         /* Maximum nOut value on the set of paths */
  4960         -  LogEst rSortCost;         /* Cost to do a sort */
  4961   4972     int nTo, nFrom;           /* Number of valid entries in aTo[] and aFrom[] */
  4962   4973     WherePath *aFrom;         /* All nFrom paths at the previous level */
  4963   4974     WherePath *aTo;           /* The nTo best paths at the current level */
  4964   4975     WherePath *pFrom;         /* An element of aFrom[] that we are working on */
  4965   4976     WherePath *pTo;           /* An element of aTo[] that we are working on */
  4966   4977     WhereLoop *pWLoop;        /* One of the WhereLoop objects */
  4967   4978     WhereLoop **pX;           /* Used to divy up the pSpace memory */
................................................................................
  4995   5006     ** of computing an automatic index is not paid back within the first 25
  4996   5007     ** rows, then do not use the automatic index. */
  4997   5008     aFrom[0].nRow = MIN(pParse->nQueryLoop, 46);  assert( 46==sqlite3LogEst(25) );
  4998   5009     nFrom = 1;
  4999   5010   
  5000   5011     /* Precompute the cost of sorting the final result set, if the caller
  5001   5012     ** to sqlite3WhereBegin() was concerned about sorting */
  5002         -  rSortCost = 0;
  5003   5013     if( pWInfo->pOrderBy==0 || nRowEst==0 ){
  5004         -    aFrom[0].isOrderedValid = 1;
         5014  +    aFrom[0].isOrdered = 0;
         5015  +    nOrderBy = 0;
  5005   5016     }else{
  5006         -    /* TUNING: Estimated cost of sorting is 48*N*log2(N) where N is the
  5007         -    ** number of output rows. The 48 is the expected size of a row to sort. 
  5008         -    ** FIXME:  compute a better estimate of the 48 multiplier based on the
  5009         -    ** result set expressions. */
  5010         -    rSortCost = nRowEst + estLog(nRowEst);
  5011         -    WHERETRACE(0x002,("---- sort cost=%-3d\n", rSortCost));
         5017  +    aFrom[0].isOrdered = -1;
         5018  +    nOrderBy = pWInfo->pOrderBy->nExpr;
  5012   5019     }
  5013   5020   
  5014   5021     /* Compute successively longer WherePaths using the previous generation
  5015   5022     ** of WherePaths as the basis for the next.  Keep track of the mxChoice
  5016   5023     ** best paths at each generation */
  5017   5024     for(iLoop=0; iLoop<nLoop; iLoop++){
  5018   5025       nTo = 0;
  5019   5026       for(ii=0, pFrom=aFrom; ii<nFrom; ii++, pFrom++){
  5020   5027         for(pWLoop=pWInfo->pLoops; pWLoop; pWLoop=pWLoop->pNextLoop){
  5021   5028           Bitmask maskNew;
  5022   5029           Bitmask revMask = 0;
  5023         -        u8 isOrderedValid = pFrom->isOrderedValid;
  5024         -        u8 isOrdered = pFrom->isOrdered;
         5030  +        i8 isOrdered = pFrom->isOrdered;
  5025   5031           if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue;
  5026   5032           if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue;
  5027   5033           /* At this point, pWLoop is a candidate to be the next loop. 
  5028   5034           ** Compute its cost */
  5029   5035           rCost = sqlite3LogEstAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow);
  5030   5036           rCost = sqlite3LogEstAdd(rCost, pFrom->rCost);
  5031   5037           nOut = pFrom->nRow + pWLoop->nOut;
  5032   5038           maskNew = pFrom->maskLoop | pWLoop->maskSelf;
  5033         -        if( !isOrderedValid ){
  5034         -          switch( wherePathSatisfiesOrderBy(pWInfo,
         5039  +        if( isOrdered<0 ){
         5040  +          isOrdered = wherePathSatisfiesOrderBy(pWInfo,
  5035   5041                          pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags,
  5036         -                       iLoop, pWLoop, &revMask) ){
  5037         -            case 1:  /* Yes.  pFrom+pWLoop does satisfy the ORDER BY clause */
  5038         -              isOrdered = 1;
  5039         -              isOrderedValid = 1;
  5040         -              break;
  5041         -            case 0:  /* No.  pFrom+pWLoop will require a separate sort */
  5042         -              isOrdered = 0;
  5043         -              isOrderedValid = 1;
  5044         -              rCost = sqlite3LogEstAdd(rCost, rSortCost);
  5045         -              break;
  5046         -            default: /* Cannot tell yet.  Try again on the next iteration */
  5047         -              break;
         5042  +                       iLoop, pWLoop, &revMask);
         5043  +          if( isOrdered>=0 && isOrdered<nOrderBy ){
         5044  +            /* TUNING: Estimated cost of sorting cost as roughly N*log(N).
         5045  +            ** If some but not all of the columns are in sorted order, then
         5046  +            ** scale down the log(N) term. */
         5047  +            LogEst rScale = sqlite3LogEst((nOrderBy-isOrdered)*100/nOrderBy);
         5048  +            LogEst rSortCost = nRowEst + estLog(nRowEst) + rScale - 66;
         5049  +            /* TUNING: The cost of implementing DISTINCT using a B-TREE is
         5050  +            ** also N*log(N) but it has a larger constant of proportionality.
         5051  +            ** Multiply by 3.0. */
         5052  +            if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){
         5053  +              rSortCost += 16;
         5054  +            }
         5055  +            WHERETRACE(0x002,
         5056  +               ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n",
         5057  +                rSortCost, (nOrderBy-isOrdered), nOrderBy, rCost,
         5058  +                sqlite3LogEstAdd(rCost,rSortCost)));
         5059  +            rCost = sqlite3LogEstAdd(rCost, rSortCost);
  5048   5060             }
  5049   5061           }else{
  5050   5062             revMask = pFrom->revLoop;
  5051   5063           }
  5052   5064           /* Check to see if pWLoop should be added to the mxChoice best so far */
  5053   5065           for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){
  5054   5066             if( pTo->maskLoop==maskNew
  5055         -           && pTo->isOrderedValid==isOrderedValid
         5067  +           && ((pTo->isOrdered^isOrdered)&80)==0
  5056   5068              && ((pTo->rCost<=rCost && pTo->nRow<=nOut) ||
  5057   5069                   (pTo->rCost>=rCost && pTo->nRow>=nOut))
  5058   5070             ){
  5059   5071               testcase( jj==nTo-1 );
  5060   5072               break;
  5061   5073             }
  5062   5074           }
  5063   5075           if( jj>=nTo ){
  5064   5076             if( nTo>=mxChoice && rCost>=mxCost ){
  5065   5077   #ifdef WHERETRACE_ENABLED /* 0x4 */
  5066   5078               if( sqlite3WhereTrace&0x4 ){
  5067   5079                 sqlite3DebugPrintf("Skip   %s cost=%-3d,%3d order=%c\n",
  5068   5080                     wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
  5069         -                  isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
         5081  +                  isOrdered>=0 ? isOrdered+'0' : '?');
  5070   5082               }
  5071   5083   #endif
  5072   5084               continue;
  5073   5085             }
  5074   5086             /* Add a new Path to the aTo[] set */
  5075   5087             if( nTo<mxChoice ){
  5076   5088               /* Increase the size of the aTo set by one */
................................................................................
  5080   5092               jj = mxI;
  5081   5093             }
  5082   5094             pTo = &aTo[jj];
  5083   5095   #ifdef WHERETRACE_ENABLED /* 0x4 */
  5084   5096             if( sqlite3WhereTrace&0x4 ){
  5085   5097               sqlite3DebugPrintf("New    %s cost=%-3d,%3d order=%c\n",
  5086   5098                   wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
  5087         -                isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
         5099  +                isOrdered>=0 ? isOrdered+'0' : '?');
  5088   5100             }
  5089   5101   #endif
  5090   5102           }else{
  5091   5103             if( pTo->rCost<=rCost && pTo->nRow<=nOut ){
  5092   5104   #ifdef WHERETRACE_ENABLED /* 0x4 */
  5093   5105               if( sqlite3WhereTrace&0x4 ){
  5094   5106                 sqlite3DebugPrintf(
  5095   5107                     "Skip   %s cost=%-3d,%3d order=%c",
  5096   5108                     wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
  5097         -                  isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
         5109  +                  isOrdered>=0 ? isOrdered+'0' : '?');
  5098   5110                 sqlite3DebugPrintf("   vs %s cost=%-3d,%d order=%c\n",
  5099   5111                     wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
  5100         -                  pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
         5112  +                  pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?');
  5101   5113               }
  5102   5114   #endif
  5103   5115               testcase( pTo->rCost==rCost );
  5104   5116               continue;
  5105   5117             }
  5106   5118             testcase( pTo->rCost==rCost+1 );
  5107   5119             /* A new and better score for a previously created equivalent path */
  5108   5120   #ifdef WHERETRACE_ENABLED /* 0x4 */
  5109   5121             if( sqlite3WhereTrace&0x4 ){
  5110   5122               sqlite3DebugPrintf(
  5111   5123                   "Update %s cost=%-3d,%3d order=%c",
  5112   5124                   wherePathName(pFrom, iLoop, pWLoop), rCost, nOut,
  5113         -                isOrderedValid ? (isOrdered ? 'Y' : 'N') : '?');
         5125  +                isOrdered>=0 ? isOrdered+'0' : '?');
  5114   5126               sqlite3DebugPrintf("  was %s cost=%-3d,%3d order=%c\n",
  5115   5127                   wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
  5116         -                pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
         5128  +                pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?');
  5117   5129             }
  5118   5130   #endif
  5119   5131           }
  5120   5132           /* pWLoop is a winner.  Add it to the set of best so far */
  5121   5133           pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf;
  5122   5134           pTo->revLoop = revMask;
  5123   5135           pTo->nRow = nOut;
  5124   5136           pTo->rCost = rCost;
  5125         -        pTo->isOrderedValid = isOrderedValid;
  5126   5137           pTo->isOrdered = isOrdered;
  5127   5138           memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop);
  5128   5139           pTo->aLoop[iLoop] = pWLoop;
  5129   5140           if( nTo>=mxChoice ){
  5130   5141             mxI = 0;
  5131   5142             mxCost = aTo[0].rCost;
  5132   5143             mxOut = aTo[0].nRow;
................................................................................
  5143   5154   
  5144   5155   #ifdef WHERETRACE_ENABLED  /* >=2 */
  5145   5156       if( sqlite3WhereTrace>=2 ){
  5146   5157         sqlite3DebugPrintf("---- after round %d ----\n", iLoop);
  5147   5158         for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){
  5148   5159           sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c",
  5149   5160              wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
  5150         -           pTo->isOrderedValid ? (pTo->isOrdered ? 'Y' : 'N') : '?');
  5151         -        if( pTo->isOrderedValid && pTo->isOrdered ){
         5161  +           pTo->isOrdered>=0 ? (pTo->isOrdered+'0') : '?');
         5162  +        if( pTo->isOrdered>0 ){
  5152   5163             sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop);
  5153   5164           }else{
  5154   5165             sqlite3DebugPrintf("\n");
  5155   5166           }
  5156   5167         }
  5157   5168       }
  5158   5169   #endif
................................................................................
  5187   5198      && (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0
  5188   5199      && pWInfo->eDistinct==WHERE_DISTINCT_NOOP
  5189   5200      && nRowEst
  5190   5201     ){
  5191   5202       Bitmask notUsed;
  5192   5203       int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pResultSet, pFrom,
  5193   5204                    WHERE_DISTINCTBY, nLoop-1, pFrom->aLoop[nLoop-1], &notUsed);
  5194         -    if( rc==1 ) pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
         5205  +    if( rc==pWInfo->pResultSet->nExpr ){
         5206  +      pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
         5207  +    }
  5195   5208     }
  5196         -  if( pFrom->isOrdered ){
         5209  +  if( pWInfo->pOrderBy ){
  5197   5210       if( pWInfo->wctrlFlags & WHERE_DISTINCTBY ){
  5198         -      pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
         5211  +      if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){
         5212  +        pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
         5213  +      }
  5199   5214       }else{
  5200         -      pWInfo->bOBSat = 1;
         5215  +      pWInfo->nOBSat = pFrom->isOrdered;
         5216  +      if( pWInfo->nOBSat<0 ) pWInfo->nOBSat = 0;
  5201   5217         pWInfo->revMask = pFrom->revLoop;
  5202   5218       }
  5203   5219     }
  5204   5220     pWInfo->nRowOut = pFrom->nRow;
  5205   5221   
  5206   5222     /* Free temporary memory and return success */
  5207   5223     sqlite3DbFree(db, pSpace);
................................................................................
  5278   5294     }
  5279   5295     if( pLoop->wsFlags ){
  5280   5296       pLoop->nOut = (LogEst)1;
  5281   5297       pWInfo->a[0].pWLoop = pLoop;
  5282   5298       pLoop->maskSelf = getMask(&pWInfo->sMaskSet, iCur);
  5283   5299       pWInfo->a[0].iTabCur = iCur;
  5284   5300       pWInfo->nRowOut = 1;
  5285         -    if( pWInfo->pOrderBy ) pWInfo->bOBSat =  1;
         5301  +    if( pWInfo->pOrderBy ) pWInfo->nOBSat =  pWInfo->pOrderBy->nExpr;
  5286   5302       if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){
  5287   5303         pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  5288   5304       }
  5289   5305   #ifdef SQLITE_DEBUG
  5290   5306       pLoop->cId = '0';
  5291   5307   #endif
  5292   5308       return 1;
................................................................................
  5382   5398   ** be used to compute the appropriate cursor depending on which index is
  5383   5399   ** used.
  5384   5400   */
  5385   5401   WhereInfo *sqlite3WhereBegin(
  5386   5402     Parse *pParse,        /* The parser context */
  5387   5403     SrcList *pTabList,    /* FROM clause: A list of all tables to be scanned */
  5388   5404     Expr *pWhere,         /* The WHERE clause */
  5389         -  ExprList *pOrderBy,   /* An ORDER BY clause, or NULL */
         5405  +  ExprList *pOrderBy,   /* An ORDER BY (or GROUP BY) clause, or NULL */
  5390   5406     ExprList *pResultSet, /* Result set of the query */
  5391   5407     u16 wctrlFlags,       /* One of the WHERE_* flags defined in sqliteInt.h */
  5392   5408     int iIdxCur           /* If WHERE_ONETABLE_ONLY is set, index cursor number */
  5393   5409   ){
  5394   5410     int nByteWInfo;            /* Num. bytes allocated for WhereInfo struct */
  5395   5411     int nTabList;              /* Number of elements in pTabList */
  5396   5412     WhereInfo *pWInfo;         /* Will become the return value of this function */
................................................................................
  5404   5420     sqlite3 *db;               /* Database connection */
  5405   5421     int rc;                    /* Return code */
  5406   5422   
  5407   5423   
  5408   5424     /* Variable initialization */
  5409   5425     db = pParse->db;
  5410   5426     memset(&sWLB, 0, sizeof(sWLB));
         5427  +
         5428  +  /* An ORDER/GROUP BY clause of more than 63 terms cannot be optimized */
         5429  +  testcase( pOrderBy && pOrderBy->nExpr==BMS-1 );
         5430  +  if( pOrderBy && pOrderBy->nExpr>=BMS ) pOrderBy = 0;
  5411   5431     sWLB.pOrderBy = pOrderBy;
  5412   5432   
  5413   5433     /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via
  5414   5434     ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */
  5415   5435     if( OptimizationDisabled(db, SQLITE_DistinctOpt) ){
  5416   5436       wctrlFlags &= ~WHERE_WANT_DISTINCT;
  5417   5437     }
................................................................................
  5448   5468     }
  5449   5469     pWInfo->aiCurOnePass[0] = pWInfo->aiCurOnePass[1] = -1;
  5450   5470     pWInfo->nLevel = nTabList;
  5451   5471     pWInfo->pParse = pParse;
  5452   5472     pWInfo->pTabList = pTabList;
  5453   5473     pWInfo->pOrderBy = pOrderBy;
  5454   5474     pWInfo->pResultSet = pResultSet;
  5455         -  pWInfo->iBreak = sqlite3VdbeMakeLabel(v);
         5475  +  pWInfo->iBreak = pWInfo->iContinue = sqlite3VdbeMakeLabel(v);
  5456   5476     pWInfo->wctrlFlags = wctrlFlags;
  5457   5477     pWInfo->savedNQueryLoop = pParse->nQueryLoop;
  5458   5478     pMaskSet = &pWInfo->sMaskSet;
  5459   5479     sWLB.pWInfo = pWInfo;
  5460   5480     sWLB.pWC = &pWInfo->sWC;
  5461   5481     sWLB.pNew = (WhereLoop*)(((char*)pWInfo)+nByteWInfo);
  5462   5482     assert( EIGHT_BYTE_ALIGNMENT(sWLB.pNew) );
................................................................................
  5482   5502         sWLB.pWC->a[ii].wtFlags |= TERM_CODED;
  5483   5503       }
  5484   5504     }
  5485   5505   
  5486   5506     /* Special case: No FROM clause
  5487   5507     */
  5488   5508     if( nTabList==0 ){
  5489         -    if( pOrderBy ) pWInfo->bOBSat = 1;
         5509  +    if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr;
  5490   5510       if( wctrlFlags & WHERE_WANT_DISTINCT ){
  5491   5511         pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  5492   5512       }
  5493   5513     }
  5494   5514   
  5495   5515     /* Assign a bit from the bitmask to every term in the FROM clause.
  5496   5516     **
................................................................................
  5593   5613     if( pParse->nErr || NEVER(db->mallocFailed) ){
  5594   5614       goto whereBeginError;
  5595   5615     }
  5596   5616   #ifdef WHERETRACE_ENABLED /* !=0 */
  5597   5617     if( sqlite3WhereTrace ){
  5598   5618       int ii;
  5599   5619       sqlite3DebugPrintf("---- Solution nRow=%d", pWInfo->nRowOut);
  5600         -    if( pWInfo->bOBSat ){
  5601         -      sqlite3DebugPrintf(" ORDERBY=0x%llx", pWInfo->revMask);
         5620  +    if( pWInfo->nOBSat>0 ){
         5621  +      sqlite3DebugPrintf(" ORDERBY=%d,0x%llx", pWInfo->nOBSat, pWInfo->revMask);
  5602   5622       }
  5603   5623       switch( pWInfo->eDistinct ){
  5604   5624         case WHERE_DISTINCT_UNIQUE: {
  5605   5625           sqlite3DebugPrintf("  DISTINCT=unique");
  5606   5626           break;
  5607   5627         }
  5608   5628         case WHERE_DISTINCT_ORDERED: {

Changes to src/whereInt.h.

   117    117         u16 nEq;               /* Number of equality constraints */
   118    118         u16 nSkip;             /* Number of initial index columns to skip */
   119    119         Index *pIndex;         /* Index used, or NULL */
   120    120       } btree;
   121    121       struct {               /* Information for virtual tables */
   122    122         int idxNum;            /* Index number */
   123    123         u8 needFree;           /* True if sqlite3_free(idxStr) is needed */
   124         -      u8 isOrdered;          /* True if satisfies ORDER BY */
          124  +      i8 isOrdered;          /* True if satisfies ORDER BY */
   125    125         u16 omitMask;          /* Terms that may be omitted */
   126    126         char *idxStr;          /* Index identifier string */
   127    127       } vtab;
   128    128     } u;
   129    129     u32 wsFlags;          /* WHERE_* flags describing the plan */
   130    130     u16 nLTerm;           /* Number of entries in aLTerm[] */
   131    131     /**** whereLoopXfer() copies fields above ***********************/
................................................................................
   179    179   ** at the end is the choosen query plan.
   180    180   */
   181    181   struct WherePath {
   182    182     Bitmask maskLoop;     /* Bitmask of all WhereLoop objects in this path */
   183    183     Bitmask revLoop;      /* aLoop[]s that should be reversed for ORDER BY */
   184    184     LogEst nRow;          /* Estimated number of rows generated by this path */
   185    185     LogEst rCost;         /* Total cost of this path */
   186         -  u8 isOrdered;         /* True if this path satisfies ORDER BY */
   187         -  u8 isOrderedValid;    /* True if the isOrdered field is valid */
          186  +  i8 isOrdered;         /* No. of ORDER BY terms satisfied. -1 for unknown */
   188    187     WhereLoop **aLoop;    /* Array of WhereLoop objects implementing this path */
   189    188   };
   190    189   
   191    190   /*
   192    191   ** The query generator uses an array of instances of this structure to
   193    192   ** help it analyze the subexpressions of the WHERE clause.  Each WHERE
   194    193   ** clause subexpression is separated from the others by AND operators,
................................................................................
   394    393     SrcList *pTabList;        /* List of tables in the join */
   395    394     ExprList *pOrderBy;       /* The ORDER BY clause or NULL */
   396    395     ExprList *pResultSet;     /* Result set. DISTINCT operates on these */
   397    396     WhereLoop *pLoops;        /* List of all WhereLoop objects */
   398    397     Bitmask revMask;          /* Mask of ORDER BY terms that need reversing */
   399    398     LogEst nRowOut;           /* Estimated number of output rows */
   400    399     u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
   401         -  u8 bOBSat;                /* ORDER BY satisfied by indices */
          400  +  i8 nOBSat;                /* Number of ORDER BY terms satisfied by indices */
   402    401     u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
   403    402     u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
   404    403     u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
   405    404     u8 nLevel;                /* Number of nested loop */
   406    405     int iTop;                 /* The very beginning of the WHERE loop */
   407    406     int iContinue;            /* Jump here to continue with next record */
   408    407     int iBreak;               /* Jump here to break out of the loop */

Changes to test/distinct.test.

   158    158     INSERT INTO t1 VALUES('a', 'b', 'c');
   159    159     INSERT INTO t1 VALUES('A', 'B', 'C');
   160    160   }
   161    161   
   162    162   foreach {tn sql temptables res} {
   163    163     1   "a, b FROM t1"                                       {}      {A B a b}
   164    164     2   "b, a FROM t1"                                       {}      {B A b a}
   165         -  3   "a, b, c FROM t1"                                    {hash}  {a b c A B C}
          165  +  3   "a, b, c FROM t1"                                    {hash}  {A B C a b c}
   166    166     4   "a, b, c FROM t1 ORDER BY a, b, c"                   {btree} {A B C a b c}
   167    167     5   "b FROM t1 WHERE a = 'a'"                            {}      {b}
   168    168     6   "b FROM t1 ORDER BY +b COLLATE binary"          {btree hash} {B b}
   169    169     7   "a FROM t1"                                          {}      {A a}
   170    170     8   "b COLLATE nocase FROM t1"                           {}      {b}
   171    171     9   "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {}      {b}
   172    172   } {

Changes to test/in4.test.

   154    154   } {}
   155    155   do_test in4-3.11 {
   156    156     execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}
   157    157   } {1 1 1}
   158    158   do_test in4-3.12 {
   159    159     execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()}
   160    160   } {}
          161  +
          162  +# Tests for "... IN (?)" and "... NOT IN (?)".  In other words, tests
          163  +# for when the RHS of IN is a single expression.  This should work the
          164  +# same as the == and <> operators.
          165  +#
          166  +do_execsql_test in4-3.21 {
          167  +  SELECT * FROM t3 WHERE x=10 AND y IN (10);
          168  +} {10 10 10}
          169  +do_execsql_test in4-3.22 {
          170  +  SELECT * FROM t3 WHERE x IN (10) AND y=10;
          171  +} {10 10 10}
          172  +do_execsql_test in4-3.23 {
          173  +  SELECT * FROM t3 WHERE x IN (10) AND y IN (10);
          174  +} {10 10 10}
          175  +do_execsql_test in4-3.24 {
          176  +  SELECT * FROM t3 WHERE x=1 AND y NOT IN (10);
          177  +} {1 1 1}
          178  +do_execsql_test in4-3.25 {
          179  +  SELECT * FROM t3 WHERE x  NOT IN (10) AND y=1;
          180  +} {1 1 1}
          181  +do_execsql_test in4-3.26 {
          182  +  SELECT * FROM t3 WHERE x NOT IN (10) AND y NOT IN (10);
          183  +} {1 1 1}
          184  +
          185  +# The query planner recognizes that "x IN (?)" only generates a
          186  +# single match and can use this information to optimize-out ORDER BY
          187  +# clauses.
          188  +#
          189  +do_execsql_test in4-3.31 {
          190  +  DROP INDEX t3i1;
          191  +  CREATE UNIQUE INDEX t3xy ON t3(x,y);
          192  +
          193  +  SELECT *, '|' FROM t3 A, t3 B
          194  +   WHERE A.x=10 AND A.y IN (10)
          195  +     AND B.x=1 AND B.y IN (1);
          196  +} {10 10 10 1 1 1 |}
          197  +do_execsql_test in4-3.32 {
          198  +  EXPLAIN QUERY PLAN
          199  +  SELECT *, '|' FROM t3 A, t3 B
          200  +   WHERE A.x=10 AND A.y IN (10)
          201  +     AND B.x=1 AND B.y IN (1);
          202  +} {~/B-TREE/}  ;# No separate sorting pass
          203  +do_execsql_test in4-3.33 {
          204  +  SELECT *, '|' FROM t3 A, t3 B
          205  +   WHERE A.x IN (10) AND A.y=10
          206  +     AND B.x IN (1) AND B.y=1;
          207  +} {10 10 10 1 1 1 |}
          208  +do_execsql_test in4-3.34 {
          209  +  EXPLAIN QUERY PLAN
          210  +  SELECT *, '|' FROM t3 A, t3 B
          211  +   WHERE A.x IN (10) AND A.y=10
          212  +     AND B.x IN (1) AND B.y=1;
          213  +} {~/B-TREE/}  ;# No separate sorting pass
          214  +
          215  +# An expression of the form "x IN (?,?)" creates an ephemeral table to
          216  +# hold the list of values on the RHS.  But "x IN (?)" does not create
          217  +# an ephemeral table.
          218  +#
          219  +do_execsql_test in4-3.41 {
          220  +  SELECT * FROM t3 WHERE x IN (10,11);
          221  +} {10 10 10}
          222  +do_execsql_test in4-3.42 {
          223  +  EXPLAIN
          224  +  SELECT * FROM t3 WHERE x IN (10,11);
          225  +} {/OpenEphemeral/}
          226  +do_execsql_test in4-3.43 {
          227  +  SELECT * FROM t3 WHERE x IN (10);
          228  +} {10 10 10}
          229  +do_execsql_test in4-3.44 {
          230  +  EXPLAIN
          231  +  SELECT * FROM t3 WHERE x IN (10);
          232  +} {~/OpenEphemeral/}
          233  +do_execsql_test in4-3.45 {
          234  +  SELECT * FROM t3 WHERE x NOT IN (10,11);
          235  +} {1 1 1}
          236  +do_execsql_test in4-3.46 {
          237  +  EXPLAIN
          238  +  SELECT * FROM t3 WHERE x NOT IN (10,11);
          239  +} {/OpenEphemeral/}
          240  +do_execsql_test in4-3.47 {
          241  +  SELECT * FROM t3 WHERE x NOT IN (10);
          242  +} {1 1 1}
          243  +do_execsql_test in4-3.48 {
          244  +  EXPLAIN
          245  +  SELECT * FROM t3 WHERE x NOT IN (10);
          246  +} {~/OpenEphemeral/}
          247  +
          248  +# Make sure that when "x IN (?)" is converted into "x==?" that collating
          249  +# sequence and affinity computations do not get messed up.
          250  +#
          251  +do_execsql_test in4-4.1 {
          252  +  CREATE TABLE t4a(a TEXT, b TEXT COLLATE nocase, c);
          253  +  INSERT INTO t4a VALUES('ABC','abc',1);
          254  +  INSERT INTO t4a VALUES('def','xyz',2);
          255  +  INSERT INTO t4a VALUES('ghi','ghi',3);
          256  +  SELECT c FROM t4a WHERE a=b ORDER BY c;
          257  +} {3}
          258  +do_execsql_test in4-4.2 {
          259  +  SELECT c FROM t4a WHERE b=a ORDER BY c;
          260  +} {1 3}
          261  +do_execsql_test in4-4.3 {
          262  +  SELECT c FROM t4a WHERE (a||'')=b ORDER BY c;
          263  +} {1 3}
          264  +do_execsql_test in4-4.4 {
          265  +  SELECT c FROM t4a WHERE (a||'')=(b||'') ORDER BY c;
          266  +} {3}
          267  +do_execsql_test in4-4.5 {
          268  +  SELECT c FROM t4a WHERE a IN (b) ORDER BY c;
          269  +} {3}
          270  +do_execsql_test in4-4.6 {
          271  +  SELECT c FROM t4a WHERE (a||'') IN (b) ORDER BY c;
          272  +} {3}
          273  +
          274  +
          275  +do_execsql_test in4-4.11 {
          276  +  CREATE TABLE t4b(a TEXT, b NUMERIC, c);
          277  +  INSERT INTO t4b VALUES('1.0',1,4);
          278  +  SELECT c FROM t4b WHERE a=b;
          279  +} {4}
          280  +do_execsql_test in4-4.12 {
          281  +  SELECT c FROM t4b WHERE b=a;
          282  +} {4}
          283  +do_execsql_test in4-4.13 {
          284  +  SELECT c FROM t4b WHERE +a=b;
          285  +} {4}
          286  +do_execsql_test in4-4.14 {
          287  +  SELECT c FROM t4b WHERE a=+b;
          288  +} {}
          289  +do_execsql_test in4-4.15 {
          290  +  SELECT c FROM t4b WHERE +b=a;
          291  +} {}
          292  +do_execsql_test in4-4.16 {
          293  +  SELECT c FROM t4b WHERE b=+a;
          294  +} {4}
          295  +do_execsql_test in4-4.17 {
          296  +  SELECT c FROM t4b WHERE a IN (b);
          297  +} {}
          298  +do_execsql_test in4-4.18 {
          299  +  SELECT c FROM t4b WHERE b IN (a);
          300  +} {4}
          301  +do_execsql_test in4-4.19 {
          302  +  SELECT c FROM t4b WHERE +b IN (a);
          303  +} {}
          304  +
          305  +do_execsql_test in4-5.1 {
          306  +  CREATE TABLE t5(c INTEGER PRIMARY KEY, d TEXT COLLATE nocase);
          307  +  INSERT INTO t5 VALUES(17, 'fuzz');
          308  +  SELECT 1 FROM t5 WHERE 'fuzz' IN (d);  -- match
          309  +  SELECT 2 FROM t5 WHERE 'FUZZ' IN (d);  -- no match
          310  +  SELECT 3 FROM t5 WHERE d IN ('fuzz');  -- match
          311  +  SELECT 4 FROM t5 WHERE d IN ('FUZZ');  -- match
          312  +} {1 3 4}
          313  +
          314  +# An expression of the form "x IN (y)" can be used as "x=y" by the
          315  +# query planner when computing transitive constraints or to run the
          316  +# query using an index on y.
          317  +#
          318  +do_execsql_test in4-6.1 {
          319  +  CREATE TABLE t6a(a INTEGER PRIMARY KEY, b);
          320  +  INSERT INTO t6a VALUES(1,2),(3,4),(5,6);
          321  +  CREATE TABLE t6b(c INTEGER PRIMARY KEY, d);
          322  +  INSERT INTO t6b VALUES(4,44),(5,55),(6,66);
          323  +
          324  +  SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c);
          325  +} {3 4 4 44}
          326  +do_execsql_test in4-6.1-eqp {
          327  +  EXPLAIN QUERY PLAN
          328  +  SELECT * FROM t6a, t6b WHERE a=3 AND b IN (c);
          329  +} {~/SCAN/}
          330  +do_execsql_test in4-6.2 {
          331  +  SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b);
          332  +} {3 4 4 44}
          333  +do_execsql_test in4-6.2-eqp {
          334  +  EXPLAIN QUERY PLAN
          335  +  SELECT * FROM t6a, t6b WHERE a=3 AND c IN (b);
          336  +} {~/SCAN/}
          337  +
   161    338   
   162    339   finish_test

Changes to test/limit.test.

   611    611   } {32}
   612    612   do_test limit-13.72 {
   613    613     db eval {SELECT z FROM v13c LIMIT 2 OFFSET 7}
   614    614   } {32}
   615    615   do_test limit-13.81 {
   616    616     db eval {SELECT z FROM v13c LIMIT 1 OFFSET 8}
   617    617   } {}
          618  +
          619  +do_execsql_test limit-14.1 {
          620  +  SELECT 123 LIMIT 1 OFFSET 0
          621  +} {123}
          622  +do_execsql_test limit-14.2 {
          623  +  SELECT 123 LIMIT 1 OFFSET 1
          624  +} {}
          625  +do_execsql_test limit-14.3 {
          626  +  SELECT 123 LIMIT 0 OFFSET 0
          627  +} {}
          628  +do_execsql_test limit-14.4 {
          629  +  SELECT 123 LIMIT 0 OFFSET 1
          630  +} {}
          631  +do_execsql_test limit-14.6 {
          632  +  SELECT 123 LIMIT -1 OFFSET 0
          633  +} {123}
          634  +do_execsql_test limit-14.7 {
          635  +  SELECT 123 LIMIT -1 OFFSET 1
          636  +} {}
          637  +
   618    638   
   619    639   finish_test

Changes to test/orderby5.test.

    60     60     EXPLAIN QUERY PLAN
    61     61     SELECT DISTINCT c, b, a FROM t1 WHERE a=0;
    62     62   } {~/B-TREE/}
    63     63   do_execsql_test 1.7 {
    64     64     EXPLAIN QUERY PLAN
    65     65     SELECT DISTINCT c, b, a FROM t1 WHERE +a=0;
    66     66   } {/B-TREE/}
    67         -do_execsql_test 2.1 {
           67  +
           68  +# In some cases, it is faster to do repeated index lookups than it is to
           69  +# sort.  But in other cases, it is faster to sort than to do repeated index
           70  +# lookups.
           71  +#
           72  +do_execsql_test 2.1a {
           73  +  CREATE TABLE t2(a,b,c);
           74  +  CREATE INDEX t2bc ON t2(b,c);
           75  +  ANALYZE;
           76  +  INSERT INTO sqlite_stat1 VALUES('t1','t1bc','1000000 10 9');
           77  +  INSERT INTO sqlite_stat1 VALUES('t2','t2bc','100 10 5');
           78  +  ANALYZE sqlite_master;
           79  +
           80  +  EXPLAIN QUERY PLAN
           81  +  SELECT * FROM t2 WHERE a=0 ORDER BY a, b, c;
           82  +} {~/B-TREE/}
           83  +do_execsql_test 2.1b {
    68     84     EXPLAIN QUERY PLAN
    69     85     SELECT * FROM t1 WHERE a=0 ORDER BY a, b, c;
    70         -} {~/B-TREE/}
           86  +} {/B-TREE/}
           87  +
           88  +
    71     89   do_execsql_test 2.2 {
    72     90     EXPLAIN QUERY PLAN
    73     91     SELECT * FROM t1 WHERE +a=0 ORDER BY a, b, c;
    74     92   } {/B-TREE/}
    75     93   do_execsql_test 2.3 {
    76     94     EXPLAIN QUERY PLAN
    77     95     SELECT * FROM t1 WHERE a=0 ORDER BY b, a, c;

Added test/orderby6.test.

            1  +# 2014-03-21
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing that the block-sort optimization.
           13  +#
           14  +
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set ::testprefix orderby6
           19  +
           20  +# Run all tests twice.  Once with a normal table and a second time
           21  +# with a WITHOUT ROWID table
           22  +#
           23  +foreach {tn rowidclause} {1 {} 2 {WITHOUT ROWID}} {
           24  +
           25  +  # Construct a table with 1000 rows and a split primary key
           26  +  #
           27  +  reset_db
           28  +  do_test $tn.1 {
           29  +    db eval "CREATE TABLE t1(a,b,c,PRIMARY KEY(b,c)) $rowidclause;"
           30  +    db eval {
           31  +      WITH RECURSIVE
           32  +       cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)
           33  +     INSERT INTO t1 SELECT x, x%40, x/40 FROM cnt;
           34  +    }
           35  +  } {}
           36  +
           37  +  # Run various ORDER BY queries that can benefit from block-sort.
           38  +  # Compare the output to the same output using a full-sort enforced
           39  +  # by adding + to each term of the ORDER BY clause.
           40  +  #
           41  +  do_execsql_test $tn.2 {
           42  +    SELECT b,a,c FROM t1 ORDER BY b,a,c;
           43  +  } [db eval {SELECT b,a,c FROM t1 ORDER BY +b,+a,+c}]
           44  +  do_execsql_test $tn.3 {
           45  +    SELECT b,a,c FROM t1 ORDER BY b,c DESC,a;
           46  +  } [db eval {SELECT b,a,c FROM t1 ORDER BY +b,+c DESC,+a}]
           47  +  do_execsql_test $tn.4 {
           48  +    SELECT b,a,c FROM t1 ORDER BY b DESC,c,a;
           49  +  } [db eval {SELECT b,a,c FROM t1 ORDER BY +b DESC,+c,+a}]
           50  +  do_execsql_test $tn.5 {
           51  +    SELECT b,a,c FROM t1 ORDER BY b DESC,a,c;
           52  +  } [db eval {SELECT b,a,c FROM t1 ORDER BY +b DESC,+a,+c}]
           53  +
           54  +  # LIMIT and OFFSET clauses on block-sort queries.
           55  +  #
           56  +  do_execsql_test $tn.11 {
           57  +    SELECT a FROM t1 ORDER BY b, a LIMIT 10 OFFSET 20;
           58  +  } {840 880 920 960 1000 1 41 81 121 161}
           59  +  do_execsql_test $tn.11x {
           60  +    SELECT a FROM t1 ORDER BY +b, a LIMIT 10 OFFSET 20;
           61  +  } {840 880 920 960 1000 1 41 81 121 161}
           62  +
           63  +  do_execsql_test $tn.12 {
           64  +    SELECT a FROM t1 ORDER BY b DESC, a LIMIT 10 OFFSET 20;
           65  +  } {839 879 919 959 999 38 78 118 158 198}
           66  +  do_execsql_test $tn.12 {
           67  +    SELECT a FROM t1 ORDER BY +b DESC, a LIMIT 10 OFFSET 20;
           68  +  } {839 879 919 959 999 38 78 118 158 198}
           69  +
           70  +  do_execsql_test $tn.13 {
           71  +    SELECT a FROM t1 ORDER BY b, a DESC LIMIT 10 OFFSET 45;
           72  +  } {161 121 81 41 1 962 922 882 842 802}
           73  +  do_execsql_test $tn.13x {
           74  +    SELECT a FROM t1 ORDER BY +b, a DESC LIMIT 10 OFFSET 45;
           75  +  } {161 121 81 41 1 962 922 882 842 802}
           76  +
           77  +  do_execsql_test $tn.14 {
           78  +    SELECT a FROM t1 ORDER BY b DESC, a LIMIT 10 OFFSET 45;
           79  +  } {838 878 918 958 998 37 77 117 157 197}
           80  +  do_execsql_test $tn.14x {
           81  +    SELECT a FROM t1 ORDER BY +b DESC, a LIMIT 10 OFFSET 45;
           82  +  } {838 878 918 958 998 37 77 117 157 197}
           83  +
           84  +  # Many test cases where the LIMIT+OFFSET window is in various
           85  +  # alignments with block-sort boundaries.
           86  +  #
           87  +  foreach {tx limit offset orderby} {
           88  +     1  10 24 {+b,+a}
           89  +     2  10 25 {+b,+a}
           90  +     3  10 26 {+b,+a}
           91  +     4  10 39 {+b,+a}
           92  +     5  10 40 {+b,+a}
           93  +     6  10 41 {+b,+a}
           94  +     7  27 24 {+b,+a}
           95  +     8  27 49 {+b,+a}
           96  +     11 10 24 {+b DESC,+a}
           97  +     12 10 25 {+b DESC,+a}
           98  +     13 10 26 {+b DESC,+a}
           99  +     14 10 39 {+b DESC,+a}
          100  +     15 10 40 {+b DESC,+a}
          101  +     16 10 41 {+b DESC,+a}
          102  +     17 27 24 {+b DESC,+a}
          103  +     18 27 49 {+b DESC,+a}
          104  +     21 10 24 {+b,+a DESC}
          105  +     22 10 25 {+b,+a DESC}
          106  +     23 10 26 {+b,+a DESC}
          107  +     24 10 39 {+b,+a DESC}
          108  +     25 10 40 {+b,+a DESC}
          109  +     26 10 41 {+b,+a DESC}
          110  +     27 27 24 {+b,+a DESC}
          111  +     28 27 49 {+b,+a DESC}
          112  +     31 10 24 {+b DESC,+a DESC}
          113  +     32 10 25 {+b DESC,+a DESC}
          114  +     33 10 26 {+b DESC,+a DESC}
          115  +     34 10 39 {+b DESC,+a DESC}
          116  +     35 10 40 {+b DESC,+a DESC}
          117  +     36 10 41 {+b DESC,+a DESC}
          118  +     37 27 24 {+b DESC,+a DESC}
          119  +     38 27 49 {+b DESC,+a DESC}
          120  +  } {
          121  +    set sql1 "SELECT a FROM t1 ORDER BY $orderby LIMIT $limit OFFSET $offset;"
          122  +    set sql2 [string map {+ {}} $sql1]
          123  +    # puts $sql2\n$sql1\n[db eval $sql2]
          124  +    do_test $tn.21.$tx {db eval $::sql2} [db eval $sql1]
          125  +  }
          126  +
          127  +  ########################################################################
          128  +  # A second test table, t2, has many columns open to sorting.
          129  +  do_test $tn.31 {
          130  +    db eval "CREATE TABLE t2(a,b,c,d,e,f,PRIMARY KEY(b,c,d,e,f)) $rowidclause;"
          131  +    db eval {
          132  +      WITH RECURSIVE
          133  +       cnt(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM cnt WHERE x<242)
          134  +     INSERT INTO t2 SELECT x,  x%3, (x/3)%3, (x/9)%3, (x/27)%3, (x/81)%3
          135  +                      FROM cnt;
          136  +    }
          137  +  } {}
          138  +
          139  +  do_execsql_test $tn.32 {
          140  +    SELECT a FROM t2 ORDER BY b,c,d,e,f;
          141  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}]
          142  +  do_execsql_test $tn.33 {
          143  +    SELECT a FROM t2 ORDER BY b,c,d,e,+f;
          144  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}]
          145  +  do_execsql_test $tn.34 {
          146  +    SELECT a FROM t2 ORDER BY b,c,d,+e,+f;
          147  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}]
          148  +  do_execsql_test $tn.35 {
          149  +    SELECT a FROM t2 ORDER BY b,c,+d,+e,+f;
          150  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}]
          151  +  do_execsql_test $tn.36 {
          152  +    SELECT a FROM t2 ORDER BY b,+c,+d,+e,+f;
          153  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f;}]
          154  +
          155  +  do_execsql_test $tn.37 {
          156  +    SELECT a FROM t2 ORDER BY b,c,d,e,f DESC;
          157  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f DESC;}]
          158  +  do_execsql_test $tn.38 {
          159  +    SELECT a FROM t2 ORDER BY b,c,d,e DESC,f;
          160  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e DESC,+f;}]
          161  +  do_execsql_test $tn.39 {
          162  +    SELECT a FROM t2 ORDER BY b,c,d DESC,e,f;
          163  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d DESC,+e,+f;}]
          164  +  do_execsql_test $tn.40 {
          165  +    SELECT a FROM t2 ORDER BY b,c DESC,d,e,f;
          166  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c DESC,+d,+e,+f;}]
          167  +  do_execsql_test $tn.41 {
          168  +    SELECT a FROM t2 ORDER BY b DESC,c,d,e,f;
          169  +  } [db eval {SELECT a FROM t2 ORDER BY +b DESC,+c,+d,+e,+f;}]
          170  +
          171  +  do_execsql_test $tn.42 {
          172  +    SELECT a FROM t2 ORDER BY b DESC,c DESC,d,e,f LIMIT 31;
          173  +  } [db eval {SELECT a FROM t2 ORDER BY +b DESC,+c DESC,+d,+e,+f LIMIT 31}]
          174  +  do_execsql_test $tn.43 {
          175  +    SELECT a FROM t2 ORDER BY b,c,d,e,f DESC LIMIT 8 OFFSET 7;
          176  +  } [db eval {SELECT a FROM t2 ORDER BY +b,+c,+d,+e,+f DESC LIMIT 8 OFFSET 7}]
          177  +
          178  +
          179  +}
          180  +
          181  +
          182  +
          183  +finish_test

Changes to test/syscall.test.

    57     57   # Tests for the xNextSystemCall method.
    58     58   #
    59     59   foreach s {
    60     60       open close access getcwd stat fstat ftruncate
    61     61       fcntl read pread write pwrite fchmod fallocate
    62     62       pread64 pwrite64 unlink openDirectory mkdir rmdir 
    63     63       statvfs fchown umask mmap munmap mremap
           64  +    getpagesize
    64     65   } {
    65     66     if {[test_syscall exists $s]} {lappend syscall_list $s}
    66     67   }
    67     68   do_test 3.1 { lsort [test_syscall list] } [lsort $syscall_list]
    68     69   
    69     70   #-------------------------------------------------------------------------
    70     71   # This test verifies that if a call to open() fails and errno is set to

Added test/tkt-a8a0d2996a.test.

            1  +# 2014-03-24
            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  +# Tests to verify that arithmetic operators do not change the type of
           13  +# input operands.  Ticket [a8a0d2996a]
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set testprefix tkt-a8a0d2996a
           19  +
           20  +do_execsql_test 1.0 {
           21  +  CREATE TABLE t(x,y);
           22  +  INSERT INTO t VALUES('1','1');
           23  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1';
           24  +} {text text}
           25  +do_execsql_test 1.1 {
           26  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1';
           27  +} {text text}
           28  +do_execsql_test 1.2 {
           29  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1';
           30  +} {text text}
           31  +do_execsql_test 1.3 {
           32  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1';
           33  +} {text text}
           34  +do_execsql_test 1.4 {
           35  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1';
           36  +} {text text}
           37  +
           38  +do_execsql_test 2.0 {
           39  +  UPDATE t SET x='1xyzzy';
           40  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1';
           41  +} {text text}
           42  +do_execsql_test 2.1 {
           43  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1';
           44  +} {text text}
           45  +do_execsql_test 2.2 {
           46  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1';
           47  +} {text text}
           48  +do_execsql_test 2.3 {
           49  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1';
           50  +} {text text}
           51  +do_execsql_test 2.4 {
           52  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1';
           53  +} {text text}
           54  +
           55  +
           56  +do_execsql_test 3.0 {
           57  +  UPDATE t SET x='1.0';
           58  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1';
           59  +} {text text}
           60  +do_execsql_test 3.1 {
           61  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x-0 AND y=='1';
           62  +} {text text}
           63  +do_execsql_test 3.2 {
           64  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x*1 AND y=='1';
           65  +} {text text}
           66  +do_execsql_test 3.3 {
           67  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x/1 AND y=='1';
           68  +} {text text}
           69  +do_execsql_test 3.4 {
           70  +  SELECT typeof(x), typeof(y) FROM t WHERE 1=x%4 AND y=='1';
           71  +} {text text}
           72  +
           73  +do_execsql_test 4.0 {
           74  +  SELECT 1+1.;
           75  +} {2.0}
           76  +do_execsql_test 4.1 {
           77  +  SELECT '1.23e64'/'1.0000e+62';
           78  +} {123.0}
           79  +do_execsql_test 4.2 {
           80  +  SELECT '100x'+'-2y';
           81  +} {98}
           82  +do_execsql_test 4.3 {
           83  +  SELECT '100x'+'4.5y';
           84  +} {104.5}
           85  +do_execsql_test 4.4 {
           86  +  SELECT '-9223372036854775807x'-'1x';
           87  +} {-9.22337203685478e+18}
           88  +do_execsql_test 4.5 {
           89  +  SELECT '9223372036854775806x'+'1x';
           90  +} {9.22337203685478e+18}
           91  +do_execsql_test 4.6 {
           92  +  SELECT '1234x'/'10y';
           93  +} {123.4}

Added test/wal64k.test.

            1  +# 2010 April 13
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the operation of the library in
           13  +# "PRAGMA journal_mode=WAL" mode.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set testprefix wal64k
           19  +
           20  +ifcapable !wal {finish_test ; return }
           21  +
           22  +db close
           23  +test_syscall pagesize 65536
           24  +sqlite3 db test.db
           25  +
           26  +do_execsql_test 1.0 { 
           27  +  PRAGMA journal_mode = WAL;
           28  +  CREATE TABLE t1(x);
           29  +  CREATE INDEX i1 ON t1(x);
           30  +} {wal}
           31  +do_test 1.1 { file size test.db-shm } {65536}
           32  +
           33  +do_test 1.2 {
           34  +  execsql BEGIN
           35  +  while {[file size test.db-shm]==65536} {
           36  +    execsql { INSERT INTO t1 VALUES( randstr(900,1100) ) }
           37  +  }
           38  +  execsql COMMIT
           39  +  file size test.db-shm
           40  +} {131072}
           41  +
           42  +integrity_check 1.3
           43  +
           44  +db close
           45  +test_syscall pagesize -1
           46  +finish_test
           47  +

Changes to test/whereG.test.

    91     91   
    92     92   do_eqp_test whereG-1.5 {
    93     93     SELECT DISTINCT aname
    94     94       FROM album, composer, track
    95     95      WHERE cname LIKE '%bach%'
    96     96        AND composer.cid=track.cid
    97     97        AND album.aid=track.aid;
    98         -} {/.*track.*composer.*album.*/}
           98  +} {/.*track.*(composer.*album|album.*composer).*/}
    99     99   do_execsql_test whereG-1.6 {
   100    100     SELECT DISTINCT aname
   101    101       FROM album, composer, track
   102    102      WHERE cname LIKE '%bach%'
   103    103        AND composer.cid=track.cid
   104    104        AND album.aid=track.aid;
   105    105   } {{Mass in B Minor, BWV 232}}
................................................................................
   106    106   
   107    107   do_eqp_test whereG-1.7 {
   108    108     SELECT DISTINCT aname
   109    109       FROM album, composer, track
   110    110      WHERE cname LIKE '%bach%'
   111    111        AND unlikely(composer.cid=track.cid)
   112    112        AND unlikely(album.aid=track.aid);
   113         -} {/.*track.*composer.*album.*/}
          113  +} {/.*track.*(composer.*album|album.*composer).*/}
   114    114   do_execsql_test whereG-1.8 {
   115    115     SELECT DISTINCT aname
   116    116       FROM album, composer, track
   117    117      WHERE cname LIKE '%bach%'
   118    118        AND unlikely(composer.cid=track.cid)
   119    119        AND unlikely(album.aid=track.aid);
   120    120   } {{Mass in B Minor, BWV 232}}

Changes to test/with2.test.

   381    381   do_execsql_test 7.5 {
   382    382     SELECT * FROM t6 WHERE y IN (
   383    383       WITH ss(x) AS ( VALUES(7) UNION ALL SELECT x+7 FROM ss WHERE x<49 )
   384    384       SELECT x FROM ss
   385    385     )
   386    386   } {14 28 42}
   387    387   
          388  +#-------------------------------------------------------------------------
          389  +# At one point the following was causing an assertion failure and a 
          390  +# memory leak.
          391  +#
          392  +do_execsql_test 8.1 {
          393  +  CREATE TABLE t7(y);
          394  +  INSERT INTO t7 VALUES(NULL);
          395  +  CREATE VIEW v AS SELECT * FROM t7 ORDER BY y;
          396  +}
          397  +
          398  +do_execsql_test 8.2 {
          399  +  WITH q(a) AS (
          400  +    SELECT 1
          401  +    UNION 
          402  +    SELECT a+1 FROM q, v WHERE a<5
          403  +  )
          404  +  SELECT * FROM q;
          405  +} {1 2 3 4 5}
          406  +
          407  +do_execsql_test 8.3 {
          408  +  WITH q(a) AS (
          409  +    SELECT 1
          410  +    UNION ALL
          411  +    SELECT a+1 FROM q, v WHERE a<5
          412  +  )
          413  +  SELECT * FROM q;
          414  +} {1 2 3 4 5}
   388    415   
   389    416   
   390    417   finish_test
   391    418