/ Check-in [fc8ca1a8]
Login

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

Overview
Comment:Merge in fixes (including the corrupt-database crash fix) and performance enhancements from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1:fc8ca1a87e7127bd28f32fd809aec3e24355fbfa
User & Date: drh 2014-03-26 19:43:30
Context
2014-04-03
16:35
Merge all recent changes from trunk, including the fix for the OP_SCopy-vs-OP_Copy problem. check-in: 9515c834 user: drh tags: sessions
2014-03-26
19:43
Merge in fixes (including the corrupt-database crash fix) and performance enhancements from trunk. check-in: fc8ca1a8 user: drh tags: sessions
15:14
Add an extra test case for the potential buffer overread patched by [28ddecff04]. check-in: f585f5d7 user: dan tags: trunk
2014-03-12
10:03
Bump the version number to 3.8.4.1., to conform with trunk. check-in: 42c9d8fc user: drh tags: sessions
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 ext/fts3/fts3.c.

  1406   1406       rc = fts3CreateTables(p);
  1407   1407     }
  1408   1408   
  1409   1409     /* Check to see if a legacy fts3 table has been "upgraded" by the
  1410   1410     ** addition of a %_stat table so that it can use incremental merge.
  1411   1411     */
  1412   1412     if( !isFts4 && !isCreate ){
  1413         -    int rc2 = SQLITE_OK;
  1414         -    fts3DbExec(&rc2, db, "SELECT 1 FROM %Q.'%q_stat' WHERE id=2",
  1415         -               p->zDb, p->zName);
  1416         -    if( rc2==SQLITE_OK ) p->bHasStat = 1;
         1413  +    p->bHasStat = 2;
  1417   1414     }
  1418   1415   
  1419   1416     /* Figure out the page-size for the database. This is required in order to
  1420   1417     ** estimate the cost of loading large doclists from the database.  */
  1421   1418     fts3DatabasePageSize(&rc, p);
  1422   1419     p->nNodeSize = p->nPgsz-35;
  1423   1420   
................................................................................
  3316   3313       if( A>(int)nMinMerge ) rc = sqlite3Fts3Incrmerge(p, A, 8);
  3317   3314     }
  3318   3315     sqlite3Fts3SegmentsClose(p);
  3319   3316     return rc;
  3320   3317   }
  3321   3318   
  3322   3319   /*
  3323         -** Implementation of xBegin() method. This is a no-op.
         3320  +** If it is currently unknown whether or not the FTS table has an %_stat
         3321  +** table (if p->bHasStat==2), attempt to determine this (set p->bHasStat
         3322  +** to 0 or 1). Return SQLITE_OK if successful, or an SQLite error code
         3323  +** if an error occurs.
         3324  +*/
         3325  +static int fts3SetHasStat(Fts3Table *p){
         3326  +  int rc = SQLITE_OK;
         3327  +  if( p->bHasStat==2 ){
         3328  +    const char *zFmt ="SELECT 1 FROM %Q.sqlite_master WHERE tbl_name='%q_stat'";
         3329  +    char *zSql = sqlite3_mprintf(zFmt, p->zDb, p->zName);
         3330  +    if( zSql ){
         3331  +      sqlite3_stmt *pStmt = 0;
         3332  +      rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
         3333  +      if( rc==SQLITE_OK ){
         3334  +        int bHasStat = (sqlite3_step(pStmt)==SQLITE_ROW);
         3335  +        rc = sqlite3_finalize(pStmt);
         3336  +        if( rc==SQLITE_OK ) p->bHasStat = bHasStat;
         3337  +      }
         3338  +      sqlite3_free(zSql);
         3339  +    }else{
         3340  +      rc = SQLITE_NOMEM;
         3341  +    }
         3342  +  }
         3343  +  return rc;
         3344  +}
         3345  +
         3346  +/*
         3347  +** Implementation of xBegin() method. 
  3324   3348   */
  3325   3349   static int fts3BeginMethod(sqlite3_vtab *pVtab){
  3326   3350     Fts3Table *p = (Fts3Table*)pVtab;
  3327   3351     UNUSED_PARAMETER(pVtab);
  3328   3352     assert( p->pSegments==0 );
  3329   3353     assert( p->nPendingData==0 );
  3330   3354     assert( p->inTransaction!=1 );
  3331   3355     TESTONLY( p->inTransaction = 1 );
  3332   3356     TESTONLY( p->mxSavepoint = -1; );
  3333   3357     p->nLeafAdd = 0;
  3334         -  return SQLITE_OK;
         3358  +  return fts3SetHasStat(p);
  3335   3359   }
  3336   3360   
  3337   3361   /*
  3338   3362   ** Implementation of xCommit() method. This is a no-op. The contents of
  3339   3363   ** the pending-terms hash-table have already been flushed into the database
  3340   3364   ** by fts3SyncMethod().
  3341   3365   */
................................................................................
  3576   3600     sqlite3_vtab *pVtab,            /* Virtual table handle */
  3577   3601     const char *zName               /* New name of table */
  3578   3602   ){
  3579   3603     Fts3Table *p = (Fts3Table *)pVtab;
  3580   3604     sqlite3 *db = p->db;            /* Database connection */
  3581   3605     int rc;                         /* Return Code */
  3582   3606   
         3607  +  /* At this point it must be known if the %_stat table exists or not.
         3608  +  ** So bHasStat may not be 2.  */
         3609  +  rc = fts3SetHasStat(p);
         3610  +  
  3583   3611     /* As it happens, the pending terms table is always empty here. This is
  3584   3612     ** because an "ALTER TABLE RENAME TABLE" statement inside a transaction 
  3585   3613     ** always opens a savepoint transaction. And the xSavepoint() method 
  3586   3614     ** flushes the pending terms table. But leave the (no-op) call to
  3587   3615     ** PendingTermsFlush() in in case that changes.
  3588   3616     */
  3589   3617     assert( p->nPendingData==0 );
  3590         -  rc = sqlite3Fts3PendingTermsFlush(p);
         3618  +  if( rc==SQLITE_OK ){
         3619  +    rc = sqlite3Fts3PendingTermsFlush(p);
         3620  +  }
  3591   3621   
  3592   3622     if( p->zContentTbl==0 ){
  3593   3623       fts3DbExec(&rc, db,
  3594   3624         "ALTER TABLE %Q.'%q_content'  RENAME TO '%q_content';",
  3595   3625         p->zDb, p->zName, zName
  3596   3626       );
  3597   3627     }

Changes to ext/fts3/fts3Int.h.

   219    219     sqlite3_stmt *aStmt[37];
   220    220   
   221    221     char *zReadExprlist;
   222    222     char *zWriteExprlist;
   223    223   
   224    224     int nNodeSize;                  /* Soft limit for node size */
   225    225     u8 bFts4;                       /* True for FTS4, false for FTS3 */
   226         -  u8 bHasStat;                    /* True if %_stat table exists */
          226  +  u8 bHasStat;                    /* True if %_stat table exists (2==unknown) */
   227    227     u8 bHasDocsize;                 /* True if %_docsize table exists */
   228    228     u8 bDescIdx;                    /* True if doclists are in reverse order */
   229    229     u8 bIgnoreSavepoint;            /* True to ignore xSavepoint invocations */
   230    230     int nPgsz;                      /* Page size for host database */
   231    231     char *zSegmentsTbl;             /* Name of %_segments table */
   232    232     sqlite3_blob *pSegments;        /* Blob handle open on %_segments table */
   233    233   

Changes to ext/fts3/fts3_write.c.

  5266   5266     Fts3Table *p = (Fts3Table *)pVtab;
  5267   5267     int rc = SQLITE_OK;             /* Return Code */
  5268   5268     int isRemove = 0;               /* True for an UPDATE or DELETE */
  5269   5269     u32 *aSzIns = 0;                /* Sizes of inserted documents */
  5270   5270     u32 *aSzDel = 0;                /* Sizes of deleted documents */
  5271   5271     int nChng = 0;                  /* Net change in number of documents */
  5272   5272     int bInsertDone = 0;
         5273  +
         5274  +  /* At this point it must be known if the %_stat table exists or not.
         5275  +  ** So bHasStat may not be 2.  */
         5276  +  assert( p->bHasStat==0 || p->bHasStat==1 );
  5273   5277   
  5274   5278     assert( p->pSegments==0 );
  5275   5279     assert( 
  5276   5280         nArg==1                     /* DELETE operations */
  5277   5281      || nArg==(2 + p->nColumn + 3)  /* INSERT or UPDATE operations */
  5278   5282     );
  5279   5283   

Changes to ext/rtree/rtree.c.

  2943   2943   
  2944   2944   /*
  2945   2945   ** This function populates the pRtree->nRowEst variable with an estimate
  2946   2946   ** of the number of rows in the virtual table. If possible, this is based
  2947   2947   ** on sqlite_stat1 data. Otherwise, use RTREE_DEFAULT_ROWEST.
  2948   2948   */
  2949   2949   static int rtreeQueryStat1(sqlite3 *db, Rtree *pRtree){
  2950         -  const char *zSql = "SELECT stat FROM sqlite_stat1 WHERE tbl= ? || '_rowid'";
         2950  +  const char *zFmt = "SELECT stat FROM %Q.sqlite_stat1 WHERE tbl = '%q_rowid'";
         2951  +  char *zSql;
  2951   2952     sqlite3_stmt *p;
  2952   2953     int rc;
  2953   2954     i64 nRow = 0;
  2954   2955   
  2955         -  rc = sqlite3_prepare_v2(db, zSql, -1, &p, 0);
  2956         -  if( rc==SQLITE_OK ){
  2957         -    sqlite3_bind_text(p, 1, pRtree->zName, -1, SQLITE_STATIC);
  2958         -    if( sqlite3_step(p)==SQLITE_ROW ) nRow = sqlite3_column_int64(p, 0);
  2959         -    rc = sqlite3_finalize(p);
  2960         -  }else if( rc!=SQLITE_NOMEM ){
  2961         -    rc = SQLITE_OK;
  2962         -  }
         2956  +  zSql = sqlite3_mprintf(zFmt, pRtree->zDb, pRtree->zName);
         2957  +  if( zSql==0 ){
         2958  +    rc = SQLITE_NOMEM;
         2959  +  }else{
         2960  +    rc = sqlite3_prepare_v2(db, zSql, -1, &p, 0);
         2961  +    if( rc==SQLITE_OK ){
         2962  +      if( sqlite3_step(p)==SQLITE_ROW ) nRow = sqlite3_column_int64(p, 0);
         2963  +      rc = sqlite3_finalize(p);
         2964  +    }else if( rc!=SQLITE_NOMEM ){
         2965  +      rc = SQLITE_OK;
         2966  +    }
  2963   2967   
  2964         -  if( rc==SQLITE_OK ){
  2965         -    if( nRow==0 ){
  2966         -      pRtree->nRowEst = RTREE_DEFAULT_ROWEST;
  2967         -    }else{
  2968         -      pRtree->nRowEst = MAX(nRow, RTREE_MIN_ROWEST);
         2968  +    if( rc==SQLITE_OK ){
         2969  +      if( nRow==0 ){
         2970  +        pRtree->nRowEst = RTREE_DEFAULT_ROWEST;
         2971  +      }else{
         2972  +        pRtree->nRowEst = MAX(nRow, RTREE_MIN_ROWEST);
         2973  +      }
  2969   2974       }
         2975  +    sqlite3_free(zSql);
  2970   2976     }
  2971   2977   
  2972   2978     return rc;
  2973   2979   }
  2974   2980   
  2975   2981   static sqlite3_module rtreeModule = {
  2976   2982     0,                          /* iVersion */
................................................................................
  3231   3237         sqlite3_free(zSql);
  3232   3238       }
  3233   3239     }
  3234   3240   
  3235   3241     if( rc==SQLITE_OK ){
  3236   3242       *ppVtab = (sqlite3_vtab *)pRtree;
  3237   3243     }else{
         3244  +    assert( *ppVtab==0 );
         3245  +    assert( pRtree->nBusy==1 );
  3238   3246       rtreeRelease(pRtree);
  3239   3247     }
  3240   3248     return rc;
  3241   3249   }
  3242   3250   
  3243   3251   
  3244   3252   /*

Changes to ext/rtree/rtreeC.test.

   153    153   do_execsql_test 4.2 {
   154    154     SELECT a, b FROM t1 LEFT JOIN t2 ON (+a = +b);
   155    155   } {1 1 2 {}}
   156    156   
   157    157   do_execsql_test 4.3 {
   158    158     SELECT b, a FROM t2 LEFT JOIN t1 ON (+a = +b);
   159    159   } {1 1 3 {}}
          160  +
          161  +#--------------------------------------------------------------------
          162  +# Test that the sqlite_stat1 data is used correctly.
          163  +#
          164  +reset_db
          165  +do_execsql_test 5.1 {
          166  +  CREATE TABLE t1(x PRIMARY KEY, y);
          167  +  CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2);
          168  +
          169  +  INSERT INTO t1(x) VALUES(1);
          170  +  INSERT INTO t1(x) SELECT x+1 FROM t1;   --   2
          171  +  INSERT INTO t1(x) SELECT x+2 FROM t1;   --   4
          172  +  INSERT INTO t1(x) SELECT x+4 FROM t1;   --   8
          173  +  INSERT INTO t1(x) SELECT x+8 FROM t1;   --  16
          174  +  INSERT INTO t1(x) SELECT x+16 FROM t1;  --  32
          175  +  INSERT INTO t1(x) SELECT x+32 FROM t1;  --  64
          176  +  INSERT INTO t1(x) SELECT x+64 FROM t1;  -- 128
          177  +  INSERT INTO t1(x) SELECT x+128 FROM t1; -- 256
          178  +  INSERT INTO t1(x) SELECT x+256 FROM t1; -- 512
          179  +  INSERT INTO t1(x) SELECT x+512 FROM t1; --1024
          180  +
          181  +  INSERT INTO rt SELECT x, x, x+1 FROM t1 WHERE x<=5;
          182  +}
          183  +
          184  +# First test a query with no ANALYZE data at all. The outer loop is
          185  +# real table "t1".
          186  +#
          187  +do_eqp_test 5.2 {
          188  +  SELECT * FROM t1, rt WHERE x==id;
          189  +} {
          190  +  0 0 0 {SCAN TABLE t1} 
          191  +  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
          192  +}
          193  +
          194  +# Now create enough ANALYZE data to tell SQLite that virtual table "rt"
          195  +# contains very few rows. This causes it to move "rt" to the outer loop.
          196  +#
          197  +do_execsql_test 5.3 {
          198  +  ANALYZE;
          199  +  DELETE FROM sqlite_stat1 WHERE tbl='t1';
          200  +}
          201  +db close
          202  +sqlite3 db test.db
          203  +do_eqp_test 5.4 {
          204  +  SELECT * FROM t1, rt WHERE x==id;
          205  +} {
          206  +  0 0 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:} 
          207  +  0 1 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (x=?)}
          208  +}
          209  +
          210  +# Delete the ANALYZE data. "t1" should be the outer loop again.
          211  +#
          212  +do_execsql_test 5.5 { DROP TABLE sqlite_stat1; }
          213  +db close
          214  +sqlite3 db test.db
          215  +do_eqp_test 5.6 {
          216  +  SELECT * FROM t1, rt WHERE x==id;
          217  +} {
          218  +  0 0 0 {SCAN TABLE t1} 
          219  +  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
          220  +}
          221  +
          222  +# This time create and attach a database that contains ANALYZE data for
          223  +# tables of the same names as those used internally by virtual table
          224  +# "rt". Check that the rtree module is not fooled into using this data.
          225  +# Table "t1" should remain the outer loop.
          226  +#
          227  +do_test 5.7 {
          228  +  db backup test.db2
          229  +  sqlite3 db2 test.db2
          230  +  db2 eval {
          231  +    ANALYZE;
          232  +    DELETE FROM sqlite_stat1 WHERE tbl='t1';
          233  +  }
          234  +  db2 close
          235  +  db close
          236  +  sqlite3 db test.db
          237  +  execsql { ATTACH 'test.db2' AS aux; }
          238  +} {}
          239  +do_eqp_test 5.8 {
          240  +  SELECT * FROM t1, rt WHERE x==id;
          241  +} {
          242  +  0 0 0 {SCAN TABLE t1} 
          243  +  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
          244  +}
          245  +
          246  +#--------------------------------------------------------------------
          247  +# Test that having a second connection drop the sqlite_stat1 table
          248  +# before it is required by rtreeConnect() does not cause problems.
          249  +#
          250  +ifcapable rtree {
          251  +  reset_db
          252  +  do_execsql_test 6.1 {
          253  +    CREATE TABLE t1(x);
          254  +    CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2);
          255  +    INSERT INTO t1 VALUES(1);
          256  +    INSERT INTO rt VALUES(1,2,3);
          257  +    ANALYZE;
          258  +  }
          259  +  db close
          260  +  sqlite3 db test.db
          261  +  do_execsql_test 6.2 { SELECT * FROM t1 } {1}
          262  +  
          263  +  do_test 6.3 {
          264  +    sqlite3 db2 test.db
          265  +    db2 eval { DROP TABLE sqlite_stat1 }
          266  +    db2 close
          267  +    execsql { SELECT * FROM rt }
          268  +  } {1 2.0 3.0}
          269  +  db close
          270  +}
          271  +
   160    272   
   161    273   finish_test
   162    274   

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/main.c.

   796    796         HashElem *p;
   797    797         for(p=sqliteHashFirst(&pSchema->tblHash); p; p=sqliteHashNext(p)){
   798    798           Table *pTab = (Table *)sqliteHashData(p);
   799    799           if( IsVirtual(pTab) ) sqlite3VtabDisconnect(db, pTab);
   800    800         }
   801    801       }
   802    802     }
          803  +  sqlite3VtabUnlockList(db);
   803    804     sqlite3BtreeLeaveAll(db);
   804    805   #else
   805    806     UNUSED_PARAMETER(db);
   806    807   #endif
   807    808   }
   808    809   
   809    810   /*

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.

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

   315    315   void sqlite3ValueApplyAffinity(
   316    316     sqlite3_value *pVal, 
   317    317     u8 affinity, 
   318    318     u8 enc
   319    319   ){
   320    320     applyAffinity((Mem *)pVal, affinity, enc);
   321    321   }
          322  +
          323  +/*
          324  +** Return the numeric type for pMem, either MEM_Int or MEM_Real or both or
          325  +** none.  
          326  +**
          327  +** Unlike applyNumericAffinity(), this routine does not modify pMem->flags.
          328  +** But it does set pMem->r and pMem->u.i appropriately.
          329  +*/
          330  +static u16 numericType(Mem *pMem){
          331  +  if( pMem->flags & (MEM_Int|MEM_Real) ){
          332  +    return pMem->flags & (MEM_Int|MEM_Real);
          333  +  }
          334  +  if( pMem->flags & (MEM_Str|MEM_Blob) ){
          335  +    if( sqlite3AtoF(pMem->z, &pMem->r, pMem->n, pMem->enc)==0 ){
          336  +      return 0;
          337  +    }
          338  +    if( sqlite3Atoi64(pMem->z, &pMem->u.i, pMem->n, pMem->enc)==SQLITE_OK ){
          339  +      return MEM_Int;
          340  +    }
          341  +    return MEM_Real;
          342  +  }
          343  +  return 0;
          344  +}
   322    345   
   323    346   #ifdef SQLITE_DEBUG
   324    347   /*
   325    348   ** Write a nice string representation of the contents of cell pMem
   326    349   ** into buffer zBuf, length nBuf.
   327    350   */
   328    351   void sqlite3VdbeMemPrettyPrint(Mem *pMem, char *zBuf){
................................................................................
  1086   1109     UPDATE_MAX_BLOBSIZE(pOut);
  1087   1110     break;
  1088   1111   }
  1089   1112   
  1090   1113   /* Opcode: Move P1 P2 P3 * *
  1091   1114   ** Synopsis:  r[P2@P3]=r[P1@P3]
  1092   1115   **
  1093         -** Move the values in register P1..P1+P3 over into
  1094         -** registers P2..P2+P3.  Registers P1..P1+P3 are
         1116  +** Move the P3 values in register P1..P1+P3-1 over into
         1117  +** registers P2..P2+P3-1.  Registers P1..P1+P3-1 are
  1095   1118   ** left holding a NULL.  It is an error for register ranges
  1096         -** P1..P1+P3 and P2..P2+P3 to overlap.
         1119  +** P1..P1+P3-1 and P2..P2+P3-1 to overlap.  It is an error
         1120  +** for P3 to be less than 1.
  1097   1121   */
  1098   1122   case OP_Move: {
  1099   1123     char *zMalloc;   /* Holding variable for allocated memory */
  1100   1124     int n;           /* Number of registers left to copy */
  1101   1125     int p1;          /* Register to copy from */
  1102   1126     int p2;          /* Register to copy to */
  1103   1127   
  1104   1128     n = pOp->p3;
  1105   1129     p1 = pOp->p1;
  1106   1130     p2 = pOp->p2;
  1107         -  assert( n>=0 && p1>0 && p2>0 );
         1131  +  assert( n>0 && p1>0 && p2>0 );
  1108   1132     assert( p1+n<=p2 || p2+n<=p1 );
  1109   1133   
  1110   1134     pIn1 = &aMem[p1];
  1111   1135     pOut = &aMem[p2];
  1112   1136     do{
  1113   1137       assert( pOut<=&aMem[(p->nMem-p->nCursor)] );
  1114   1138       assert( pIn1<=&aMem[(p->nMem-p->nCursor)] );
................................................................................
  1124   1148   #endif
  1125   1149       pIn1->flags = MEM_Undefined;
  1126   1150       pIn1->xDel = 0;
  1127   1151       pIn1->zMalloc = zMalloc;
  1128   1152       REGISTER_TRACE(p2++, pOut);
  1129   1153       pIn1++;
  1130   1154       pOut++;
  1131         -  }while( n-- );
         1155  +  }while( --n );
  1132   1156     break;
  1133   1157   }
  1134   1158   
  1135   1159   /* Opcode: Copy P1 P2 P3 * *
  1136   1160   ** Synopsis: r[P2@P3+1]=r[P1@P3+1]
  1137   1161   **
  1138   1162   ** Make a copy of registers P1..P1+P3 into registers P2..P2+P3.
................................................................................
  1356   1380   */
  1357   1381   case OP_Add:                   /* same as TK_PLUS, in1, in2, out3 */
  1358   1382   case OP_Subtract:              /* same as TK_MINUS, in1, in2, out3 */
  1359   1383   case OP_Multiply:              /* same as TK_STAR, in1, in2, out3 */
  1360   1384   case OP_Divide:                /* same as TK_SLASH, in1, in2, out3 */
  1361   1385   case OP_Remainder: {           /* same as TK_REM, in1, in2, out3 */
  1362   1386     char bIntint;   /* Started out as two integer operands */
  1363         -  int flags;      /* Combined MEM_* flags from both inputs */
         1387  +  u16 flags;      /* Combined MEM_* flags from both inputs */
         1388  +  u16 type1;      /* Numeric type of left operand */
         1389  +  u16 type2;      /* Numeric type of right operand */
  1364   1390     i64 iA;         /* Integer value of left operand */
  1365   1391     i64 iB;         /* Integer value of right operand */
  1366   1392     double rA;      /* Real value of left operand */
  1367   1393     double rB;      /* Real value of right operand */
  1368   1394   
  1369   1395     pIn1 = &aMem[pOp->p1];
  1370         -  applyNumericAffinity(pIn1);
         1396  +  type1 = numericType(pIn1);
  1371   1397     pIn2 = &aMem[pOp->p2];
  1372         -  applyNumericAffinity(pIn2);
         1398  +  type2 = numericType(pIn2);
  1373   1399     pOut = &aMem[pOp->p3];
  1374   1400     flags = pIn1->flags | pIn2->flags;
  1375   1401     if( (flags & MEM_Null)!=0 ) goto arithmetic_result_is_null;
  1376         -  if( (pIn1->flags & pIn2->flags & MEM_Int)==MEM_Int ){
         1402  +  if( (type1 & type2 & MEM_Int)!=0 ){
  1377   1403       iA = pIn1->u.i;
  1378   1404       iB = pIn2->u.i;
  1379   1405       bIntint = 1;
  1380   1406       switch( pOp->opcode ){
  1381   1407         case OP_Add:       if( sqlite3AddInt64(&iB,iA) ) goto fp_math;  break;
  1382   1408         case OP_Subtract:  if( sqlite3SubInt64(&iB,iA) ) goto fp_math;  break;
  1383   1409         case OP_Multiply:  if( sqlite3MulInt64(&iB,iA) ) goto fp_math;  break;
................................................................................
  1425   1451       MemSetTypeFlag(pOut, MEM_Int);
  1426   1452   #else
  1427   1453       if( sqlite3IsNaN(rB) ){
  1428   1454         goto arithmetic_result_is_null;
  1429   1455       }
  1430   1456       pOut->r = rB;
  1431   1457       MemSetTypeFlag(pOut, MEM_Real);
  1432         -    if( (flags & MEM_Real)==0 && !bIntint ){
         1458  +    if( ((type1|type2)&MEM_Real)==0 && !bIntint ){
  1433   1459         sqlite3VdbeIntegerAffinity(pOut);
  1434   1460       }
  1435   1461   #endif
  1436   1462     }
  1437   1463     break;
  1438   1464   
  1439   1465   arithmetic_result_is_null:
................................................................................
  2001   2027     assert( pOp->p4type==P4_INTARRAY );
  2002   2028     assert( pOp->p4.ai );
  2003   2029     aPermute = pOp->p4.ai;
  2004   2030     break;
  2005   2031   }
  2006   2032   
  2007   2033   /* Opcode: Compare P1 P2 P3 P4 P5
         2034  +** Synopsis: r[P1@P3] <-> r[P2@P3]
  2008   2035   **
  2009   2036   ** Compare two vectors of registers in reg(P1)..reg(P1+P3-1) (call this
  2010   2037   ** vector "A") and in reg(P2)..reg(P2+P3-1) ("B").  Save the result of
  2011   2038   ** the comparison for use by the next OP_Jump instruct.
  2012   2039   **
  2013   2040   ** If P5 has the OPFLAG_PERMUTE bit set, then the order of comparison is
  2014   2041   ** determined by the most recent OP_Permutation operator.  If the
................................................................................
  3336   3363         SQLITE_OPEN_DELETEONCLOSE |
  3337   3364         SQLITE_OPEN_TRANSIENT_DB;
  3338   3365     assert( pOp->p1>=0 );
  3339   3366     assert( pOp->p2>=0 );
  3340   3367     pCx = allocateCursor(p, pOp->p1, pOp->p2, -1, 1);
  3341   3368     if( pCx==0 ) goto no_mem;
  3342   3369     pCx->nullRow = 1;
         3370  +  pCx->isEphemeral = 1;
  3343   3371     rc = sqlite3BtreeOpen(db->pVfs, 0, db, &pCx->pBt, 
  3344   3372                           BTREE_OMIT_JOURNAL | BTREE_SINGLE | pOp->p5, vfsFlags);
  3345   3373     if( rc==SQLITE_OK ){
  3346   3374       rc = sqlite3BtreeBeginTrans(pCx->pBt, 1);
  3347   3375     }
  3348   3376     if( rc==SQLITE_OK ){
  3349   3377       /* If a transient index is required, create it by calling
................................................................................
  3826   3854       assert( pC->rowidIsValid==0 );
  3827   3855     }
  3828   3856     pC->seekResult = res;
  3829   3857     break;
  3830   3858   }
  3831   3859   
  3832   3860   /* Opcode: Sequence P1 P2 * * *
  3833         -** Synopsis: r[P2]=rowid
         3861  +** Synopsis: r[P2]=cursor[P1].ctr++
  3834   3862   **
  3835   3863   ** Find the next available sequence number for cursor P1.
  3836   3864   ** Write the sequence number into register P2.
  3837   3865   ** The sequence number on the cursor is incremented after this
  3838   3866   ** instruction.  
  3839   3867   */
  3840   3868   case OP_Sequence: {           /* out2-prerelease */
................................................................................
  4568   4596   */
  4569   4597   case OP_SorterNext: {  /* jump */
  4570   4598     VdbeCursor *pC;
  4571   4599     int res;
  4572   4600   
  4573   4601     pC = p->apCsr[pOp->p1];
  4574   4602     assert( isSorter(pC) );
         4603  +  res = 0;
  4575   4604     rc = sqlite3VdbeSorterNext(db, pC, &res);
  4576   4605     goto next_tail;
  4577   4606   case OP_PrevIfOpen:    /* jump */
  4578   4607   case OP_NextIfOpen:    /* jump */
  4579   4608     if( p->apCsr[pOp->p1]==0 ) break;
  4580   4609     /* Fall through */
  4581   4610   case OP_Prev:          /* jump */
................................................................................
  4925   4954         assert( memIsValid(&aMem[pOp->p3]) );
  4926   4955         memAboutToChange(p, &aMem[pOp->p3]);
  4927   4956         aMem[pOp->p3].u.i += nChange;
  4928   4957       }
  4929   4958     }
  4930   4959     break;
  4931   4960   }
         4961  +
         4962  +/* Opcode: ResetSorter P1 * * * *
         4963  +**
         4964  +** Delete all contents from the ephemeral table or sorter
         4965  +** that is open on cursor P1.
         4966  +**
         4967  +** This opcode only works for cursors used for sorting and
         4968  +** opened with OP_OpenEphemeral or OP_SorterOpen.
         4969  +*/
         4970  +case OP_ResetSorter: {
         4971  +  VdbeCursor *pC;
         4972  + 
         4973  +  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
         4974  +  pC = p->apCsr[pOp->p1];
         4975  +  assert( pC!=0 );
         4976  +  if( pC->pSorter ){
         4977  +    sqlite3VdbeSorterReset(db, pC->pSorter);
         4978  +  }else{
         4979  +    assert( pC->isEphemeral );
         4980  +    rc = sqlite3BtreeClearTableOfCursor(pC->pCursor);
         4981  +  }
         4982  +  break;
         4983  +}
  4932   4984   
  4933   4985   /* Opcode: CreateTable P1 P2 * * *
  4934   4986   ** Synopsis: r[P2]=root iDb=P1
  4935   4987   **
  4936   4988   ** Allocate a new table in the main database file if P1==0 or in the
  4937   4989   ** auxiliary database file if P1==1 or in an attached database if
  4938   4990   ** 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 */
................................................................................
   454    455   void sqlite3VdbeFrameDelete(VdbeFrame*);
   455    456   int sqlite3VdbeFrameRestore(VdbeFrame *);
   456    457   void sqlite3VdbePreUpdateHook(
   457    458       Vdbe *, VdbeCursor *, int, const char*, Table *, i64, int);
   458    459   int sqlite3VdbeTransferError(Vdbe *p);
   459    460   
   460    461   int sqlite3VdbeSorterInit(sqlite3 *, VdbeCursor *);
          462  +void sqlite3VdbeSorterReset(sqlite3 *, VdbeSorter *);
   461    463   void sqlite3VdbeSorterClose(sqlite3 *, VdbeCursor *);
   462    464   int sqlite3VdbeSorterRowkey(const VdbeCursor *, Mem *);
   463    465   int sqlite3VdbeSorterNext(sqlite3 *, const VdbeCursor *, int *);
   464    466   int sqlite3VdbeSorterRewind(sqlite3 *, const VdbeCursor *, int *);
   465    467   int sqlite3VdbeSorterWrite(sqlite3 *, const VdbeCursor *, Mem *);
   466    468   int sqlite3VdbeSorterCompare(const VdbeCursor *, Mem *, int, int *);
   467    469   

Changes to src/vdbeaux.c.

   780    780     }
   781    781     assert( p->nOp>0 );
   782    782     assert( addr<p->nOp );
   783    783     if( addr<0 ){
   784    784       addr = p->nOp - 1;
   785    785     }
   786    786     pOp = &p->aOp[addr];
   787         -  assert( pOp->p4type==P4_NOTUSED || pOp->p4type==P4_INT32 );
          787  +  assert( pOp->p4type==P4_NOTUSED
          788  +       || pOp->p4type==P4_INT32
          789  +       || pOp->p4type==P4_KEYINFO );
   788    790     freeP4(db, pOp->p4type, pOp->p4.p);
   789    791     pOp->p4.p = 0;
   790    792     if( n==P4_INT32 ){
   791    793       /* Note: this cast is safe, because the origin data point was an int
   792    794       ** that was cast to a (const char *). */
   793    795       pOp->p4.i = SQLITE_PTR_TO_INT(zP4);
   794    796       pOp->p4type = P4_INT32;
................................................................................
  2730   2732       p->cacheStatus = CACHE_STALE;
  2731   2733     }else if( p->pCursor ){
  2732   2734       int hasMoved;
  2733   2735       int rc = sqlite3BtreeCursorHasMoved(p->pCursor, &hasMoved);
  2734   2736       if( rc ) return rc;
  2735   2737       if( hasMoved ){
  2736   2738         p->cacheStatus = CACHE_STALE;
  2737         -      p->nullRow = 1;
         2739  +      if( hasMoved==2 ) p->nullRow = 1;
  2738   2740       }
  2739   2741     }
  2740   2742     return SQLITE_OK;
  2741   2743   }
  2742   2744   
  2743   2745   /*
  2744   2746   ** The following functions:
................................................................................
  3430   3432       szHdr1 = aKey1[0];
  3431   3433       d1 = szHdr1 + sqlite3VdbeSerialTypeLen(s1);
  3432   3434       i = 1;
  3433   3435       pRhs++;
  3434   3436     }else{
  3435   3437       idx1 = getVarint32(aKey1, szHdr1);
  3436   3438       d1 = szHdr1;
         3439  +    if( d1>(unsigned)nKey1 ) return 1;  /* Corruption */
  3437   3440       i = 0;
  3438   3441     }
  3439   3442   
  3440   3443     VVA_ONLY( mem1.zMalloc = 0; ) /* Only needed by assert() statements */
  3441   3444     assert( pPKey2->pKeyInfo->nField+pPKey2->pKeyInfo->nXField>=pPKey2->nField 
  3442   3445          || CORRUPT_DB );
  3443   3446     assert( pPKey2->pKeyInfo->aSortOrder!=0 );
................................................................................
  3585   3588   }
  3586   3589   
  3587   3590   /*
  3588   3591   ** This function is an optimized version of sqlite3VdbeRecordCompare() 
  3589   3592   ** that (a) the first field of pPKey2 is an integer, and (b) the 
  3590   3593   ** size-of-header varint at the start of (pKey1/nKey1) fits in a single
  3591   3594   ** byte (i.e. is less than 128).
         3595  +**
         3596  +** To avoid concerns about buffer overreads, this routine is only used
         3597  +** on schemas where the maximum valid header size is 63 bytes or less.
  3592   3598   */
  3593   3599   static int vdbeRecordCompareInt(
  3594   3600     int nKey1, const void *pKey1, /* Left key */
  3595   3601     const UnpackedRecord *pPKey2, /* Right key */
  3596   3602     int bSkip                     /* Ignored */
  3597   3603   ){
  3598   3604     const u8 *aKey = &((const u8*)pKey1)[*(const u8*)pKey1 & 0x3F];
................................................................................
  3601   3607     u32 y;
  3602   3608     u64 x;
  3603   3609     i64 v = pPKey2->aMem[0].u.i;
  3604   3610     i64 lhs;
  3605   3611     UNUSED_PARAMETER(bSkip);
  3606   3612   
  3607   3613     assert( bSkip==0 );
         3614  +  assert( (*(u8*)pKey1)<=0x3F || CORRUPT_DB );
  3608   3615     switch( serial_type ){
  3609   3616       case 1: { /* 1-byte signed integer */
  3610   3617         lhs = ONE_BYTE_INT(aKey);
  3611   3618         testcase( lhs<0 );
  3612   3619         break;
  3613   3620       }
  3614   3621       case 2: { /* 2-byte signed integer */

Changes to src/vdbesort.c.

   499    499     SorterRecord *p;
   500    500     SorterRecord *pNext;
   501    501     for(p=pRecord; p; p=pNext){
   502    502       pNext = p->pNext;
   503    503       sqlite3DbFree(db, p);
   504    504     }
   505    505   }
          506  +
          507  +/*
          508  +** Reset a sorting cursor back to its original empty state.
          509  +*/
          510  +void sqlite3VdbeSorterReset(sqlite3 *db, VdbeSorter *pSorter){
          511  +  if( pSorter->aIter ){
          512  +    int i;
          513  +    for(i=0; i<pSorter->nTree; i++){
          514  +      vdbeSorterIterZero(db, &pSorter->aIter[i]);
          515  +    }
          516  +    sqlite3DbFree(db, pSorter->aIter);
          517  +    pSorter->aIter = 0;
          518  +  }
          519  +  if( pSorter->pTemp1 ){
          520  +    sqlite3OsCloseFree(pSorter->pTemp1);
          521  +    pSorter->pTemp1 = 0;
          522  +  }
          523  +  vdbeSorterRecordFree(db, pSorter->pRecord);
          524  +  pSorter->pRecord = 0;
          525  +  pSorter->iWriteOff = 0;
          526  +  pSorter->iReadOff = 0;
          527  +  pSorter->nInMemory = 0;
          528  +  pSorter->nTree = 0;
          529  +  pSorter->nPMA = 0;
          530  +  pSorter->aTree = 0;
          531  +}
          532  +
   506    533   
   507    534   /*
   508    535   ** Free any cursor components allocated by sqlite3VdbeSorterXXX routines.
   509    536   */
   510    537   void sqlite3VdbeSorterClose(sqlite3 *db, VdbeCursor *pCsr){
   511    538     VdbeSorter *pSorter = pCsr->pSorter;
   512    539     if( pSorter ){
   513         -    if( pSorter->aIter ){
   514         -      int i;
   515         -      for(i=0; i<pSorter->nTree; i++){
   516         -        vdbeSorterIterZero(db, &pSorter->aIter[i]);
   517         -      }
   518         -      sqlite3DbFree(db, pSorter->aIter);
   519         -    }
   520         -    if( pSorter->pTemp1 ){
   521         -      sqlite3OsCloseFree(pSorter->pTemp1);
   522         -    }
   523         -    vdbeSorterRecordFree(db, pSorter->pRecord);
          540  +    sqlite3VdbeSorterReset(db, pSorter);
   524    541       sqlite3DbFree(db, pSorter->pUnpacked);
   525    542       sqlite3DbFree(db, pSorter);
   526    543       pCsr->pSorter = 0;
   527    544     }
   528    545   }
   529    546   
   530    547   /*
................................................................................
   952    969   */
   953    970   int sqlite3VdbeSorterNext(sqlite3 *db, const VdbeCursor *pCsr, int *pbEof){
   954    971     VdbeSorter *pSorter = pCsr->pSorter;
   955    972     int rc;                         /* Return code */
   956    973   
   957    974     if( pSorter->aTree ){
   958    975       int iPrev = pSorter->aTree[1];/* Index of iterator to advance */
   959         -    int i;                        /* Index of aTree[] to recalculate */
   960         -
   961    976       rc = vdbeSorterIterNext(db, &pSorter->aIter[iPrev]);
   962         -    for(i=(pSorter->nTree+iPrev)/2; rc==SQLITE_OK && i>0; i=i/2){
   963         -      rc = vdbeSorterDoCompare(pCsr, i);
   964         -    }
          977  +    if( rc==SQLITE_OK ){
          978  +      int i;                      /* Index of aTree[] to recalculate */
          979  +      VdbeSorterIter *pIter1;     /* First iterator to compare */
          980  +      VdbeSorterIter *pIter2;     /* Second iterator to compare */
          981  +      u8 *pKey2;                  /* To pIter2->aKey, or 0 if record cached */
          982  +
          983  +      /* Find the first two iterators to compare. The one that was just
          984  +      ** advanced (iPrev) and the one next to it in the array.  */
          985  +      pIter1 = &pSorter->aIter[(iPrev & 0xFFFE)];
          986  +      pIter2 = &pSorter->aIter[(iPrev | 0x0001)];
          987  +      pKey2 = pIter2->aKey;
          988  +
          989  +      for(i=(pSorter->nTree+iPrev)/2; i>0; i=i/2){
          990  +        /* Compare pIter1 and pIter2. Store the result in variable iRes. */
          991  +        int iRes;
          992  +        if( pIter1->pFile==0 ){
          993  +          iRes = +1;
          994  +        }else if( pIter2->pFile==0 ){
          995  +          iRes = -1;
          996  +        }else{
          997  +          vdbeSorterCompare(pCsr, 0, 
          998  +              pIter1->aKey, pIter1->nKey, pKey2, pIter2->nKey, &iRes
          999  +          );
         1000  +        }
         1001  +
         1002  +        /* If pIter1 contained the smaller value, set aTree[i] to its index.
         1003  +        ** Then set pIter2 to the next iterator to compare to pIter1. In this
         1004  +        ** case there is no cache of pIter2 in pSorter->pUnpacked, so set
         1005  +        ** pKey2 to point to the record belonging to pIter2.
         1006  +        **
         1007  +        ** Alternatively, if pIter2 contains the smaller of the two values,
         1008  +        ** set aTree[i] to its index and update pIter1. If vdbeSorterCompare()
         1009  +        ** was actually called above, then pSorter->pUnpacked now contains
         1010  +        ** a value equivalent to pIter2. So set pKey2 to NULL to prevent
         1011  +        ** vdbeSorterCompare() from decoding pIter2 again.  */
         1012  +        if( iRes<=0 ){
         1013  +          pSorter->aTree[i] = (int)(pIter1 - pSorter->aIter);
         1014  +          pIter2 = &pSorter->aIter[ pSorter->aTree[i ^ 0x0001] ];
         1015  +          pKey2 = pIter2->aKey;
         1016  +        }else{
         1017  +          if( pIter1->pFile ) pKey2 = 0;
         1018  +          pSorter->aTree[i] = (int)(pIter2 - pSorter->aIter);
         1019  +          pIter1 = &pSorter->aIter[ pSorter->aTree[i ^ 0x0001] ];
         1020  +        }
   965   1021   
   966         -    *pbEof = (pSorter->aIter[pSorter->aTree[1]].pFile==0);
         1022  +      }
         1023  +      *pbEof = (pSorter->aIter[pSorter->aTree[1]].pFile==0);
         1024  +    }
   967   1025     }else{
   968   1026       SorterRecord *pFree = pSorter->pRecord;
   969   1027       pSorter->pRecord = pFree->pNext;
   970   1028       pFree->pNext = 0;
   971   1029       vdbeSorterRecordFree(db, pFree);
   972   1030       *pbEof = !pSorter->pRecord;
   973   1031       rc = SQLITE_OK;

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/corruptI.test.

    28     28   # Initialize the database.
    29     29   #
    30     30   do_execsql_test 1.1 {
    31     31     PRAGMA page_size=1024;
    32     32     PRAGMA auto_vacuum=0;
    33     33     CREATE TABLE t1(a);
    34     34     CREATE INDEX i1 ON t1(a);
    35         -  INSERT INTO t1 VALUES('a');
           35  +  INSERT INTO t1 VALUES('abcdefghijklmnop');
    36     36   } {}
    37     37   db close
    38     38   
    39     39   do_test 1.2 {
    40     40     set offset [hexio_get_int [hexio_read test.db [expr 2*1024 + 8] 2]]
    41     41     set off [expr 2*1024 + $offset + 1]
    42         -  hexio_write test.db $off FF06
    43         -
    44         -  breakpoint
           42  +  hexio_write test.db $off 7f06
           43  +  sqlite3 db test.db
           44  +  catchsql { SELECT * FROM t1 WHERE a = 10 }
           45  +} {0 {}}
    45     46   
           47  +do_test 1.3 {
           48  +  db close
           49  +  set offset [hexio_get_int [hexio_read test.db [expr 2*1024 + 8] 2]]
           50  +  set off [expr 2*1024 + $offset + 1]
           51  +  hexio_write test.db $off FFFF7f02
    46     52     sqlite3 db test.db
    47     53     catchsql { SELECT * FROM t1 WHERE a = 10 }
           54  +} {0 {}}
           55  +
           56  +do_test 2.0 {
           57  +  execsql {
           58  +    CREATE TABLE r(x);
           59  +    INSERT INTO r VALUES('ABCDEFGHIJK');
           60  +    CREATE INDEX r1 ON r(x);
           61  +  }
           62  +  set pg [db one {SELECT rootpage FROM sqlite_master WHERE name = 'r1'}]
           63  +} {5}
           64  +
           65  +do_test 2.1 {
           66  +  db close
           67  +  set offset [hexio_get_int [hexio_read test.db [expr (5-1)*1024 + 8] 2]]
           68  +  set off [expr (5-1)*1024 + $offset + 1]
           69  +  hexio_write test.db $off FFFF0004
           70  +  sqlite3 db test.db
           71  +  catchsql { SELECT * FROM r WHERE x >= 10.0 }
           72  +} {1 {database disk image is malformed}}
           73  +
           74  +do_test 2.2 {
           75  +  catchsql { SELECT * FROM r WHERE x >= 10 }
    48     76   } {1 {database disk image is malformed}}
    49     77   
    50     78   
    51     79   finish_test
    52         -

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/fts3ao.test.

   214    214     SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%';
   215    215   } {6 0}
   216    216   do_execsql_test 5.2 {
   217    217     ALTER TABLE t7 RENAME TO t8;
   218    218     SELECT count(*) FROM sqlite_master WHERE name LIKE 't7%';
   219    219     SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%';
   220    220   } {0 6}
          221  +
          222  +# At one point this was causing a memory leak.
          223  +#
          224  +foreach {tn sql} {
          225  +  1 {}
          226  +  2 { INSERT INTO ft(ft) VALUES('merge=2,2'); }
          227  +} {
          228  +  reset_db
          229  +  do_execsql_test 6.$tn.1 "
          230  +    CREATE TABLE t1(x);
          231  +    CREATE VIRTUAL TABLE ft USING fts3;
          232  +    INSERT INTO ft VALUES('hello world');
          233  +    $sql
          234  +  "
          235  +
          236  +  db close
          237  +  sqlite3 db test.db
          238  +  do_execsql_test 6.$tn.2 { SELECT * FROM t1 } {}
          239  +
          240  +  do_test 6.$tn.3 {
          241  +    sqlite3 db2 test.db
          242  +    db2 eval { DROP TABLE t1 }
          243  +    db2 close
          244  +    set stmt [sqlite3_prepare db { SELECT * FROM ft } -1 dummy]
          245  +    sqlite3_finalize $stmt
          246  +  } {SQLITE_OK}
          247  +  db close
          248  +}
   221    249   
   222    250   finish_test

Changes to test/fts3d.test.

   350    350   } {fts_content fts_segdir fts_segments}
   351    351   do_test fts3d-6.5 {
   352    352     db eval {
   353    353       ALTER TABLE fts RENAME TO xyz;
   354    354       SELECT name FROM sqlite_master WHERE name GLOB '???_*' ORDER BY 1;
   355    355     }
   356    356   } {xyz_content xyz_segdir xyz_segments}
          357  +
          358  +# ALTER TABLE RENAME on an FTS3 table following an incr-merge op.
          359  +#
          360  +do_test fts3d-6.6 {
          361  +  execsql { INSERT INTO xyz(xyz) VALUES('merge=2,2') }
          362  +  sqlite3 db test.db
          363  +  execsql { 
          364  +    ALTER TABLE xyz RENAME TO ott;
          365  +    SELECT name FROM sqlite_master WHERE name GLOB '???_*' ORDER BY 1;
          366  +  }
          367  +} {ott_content ott_segdir ott_segments ott_stat}
   357    368    
   358    369   
   359    370   finish_test

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/speedtest1.c.

   470    470       sqlite3_bind_text(g.pStmt, 3, zNum, -1, SQLITE_STATIC);
   471    471       speedtest1_run();
   472    472     }
   473    473     speedtest1_exec("COMMIT");
   474    474     speedtest1_end_test();
   475    475   
   476    476   
   477         -  n = g.szTest/2;
          477  +  n = 25;
   478    478     speedtest1_begin_test(130, "%d SELECTS, numeric BETWEEN, unindexed", n);
   479    479     speedtest1_exec("BEGIN");
   480    480     speedtest1_prepare(
   481    481       "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
   482    482       " WHERE b BETWEEN ?1 AND ?2; -- %d times", n
   483    483     );
   484    484     for(i=1; i<=n; i++){
................................................................................
   488    488       sqlite3_bind_int(g.pStmt, 2, x2);
   489    489       speedtest1_run();
   490    490     }
   491    491     speedtest1_exec("COMMIT");
   492    492     speedtest1_end_test();
   493    493   
   494    494   
   495         -  n = g.szTest/5;
          495  +  n = 10;
   496    496     speedtest1_begin_test(140, "%d SELECTS, LIKE, unindexed", n);
   497    497     speedtest1_exec("BEGIN");
   498    498     speedtest1_prepare(
   499    499       "SELECT count(*), avg(b), sum(length(c)) FROM t1\n"
   500    500       " WHERE c LIKE ?1; -- %d times", n
   501    501     );
          502  +  for(i=1; i<=n; i++){
          503  +    x1 = speedtest1_random()%maxb;
          504  +    zNum[0] = '%';
          505  +    len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
          506  +    zNum[len] = '%';
          507  +    zNum[len+1] = 0;
          508  +    sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
          509  +    speedtest1_run();
          510  +  }
          511  +  speedtest1_exec("COMMIT");
          512  +  speedtest1_end_test();
          513  +
          514  +
          515  +  n = 10;
          516  +  speedtest1_begin_test(142, "%d SELECTS w/ORDER BY, unindexed", n);
          517  +  speedtest1_exec("BEGIN");
          518  +  speedtest1_prepare(
          519  +    "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n"
          520  +    " ORDER BY a; -- %d times", n
          521  +  );
          522  +  for(i=1; i<=n; i++){
          523  +    x1 = speedtest1_random()%maxb;
          524  +    zNum[0] = '%';
          525  +    len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
          526  +    zNum[len] = '%';
          527  +    zNum[len+1] = 0;
          528  +    sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);
          529  +    speedtest1_run();
          530  +  }
          531  +  speedtest1_exec("COMMIT");
          532  +  speedtest1_end_test();
          533  +
          534  +  n = 10; //g.szTest/5;
          535  +  speedtest1_begin_test(145, "%d SELECTS w/ORDER BY and LIMIT, unindexed", n);
          536  +  speedtest1_exec("BEGIN");
          537  +  speedtest1_prepare(
          538  +    "SELECT a, b, c FROM t1 WHERE c LIKE ?1\n"
          539  +    " ORDER BY a LIMIT 10; -- %d times", n
          540  +  );
   502    541     for(i=1; i<=n; i++){
   503    542       x1 = speedtest1_random()%maxb;
   504    543       zNum[0] = '%';
   505    544       len = speedtest1_numbername(i, zNum+1, sizeof(zNum)-2);
   506    545       zNum[len] = '%';
   507    546       zNum[len+1] = 0;
   508    547       sqlite3_bind_text(g.pStmt, 1, zNum, len, SQLITE_STATIC);

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}

Changes to test/vtab_shared.test.

    11     11   # This file tests interactions between the virtual table and
    12     12   # shared-schema functionality.
    13     13   #
    14     14   # $Id: vtab_shared.test,v 1.3 2009/07/24 17:58:53 danielk1977 Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
           18  +set testprefix vtab_shared
    18     19   
    19     20   ifcapable !vtab||!shared_cache {
    20     21     finish_test
    21     22     return
    22     23   }
    23     24   
    24     25   db close
................................................................................
   224    225       INSERT INTO t3 VALUES(4, 5, 6);
   225    226       SELECT * FROM t3;
   226    227     }
   227    228   } {1 2 3 4 5 6}
   228    229   
   229    230   db close
   230    231   db2 close
          232  +
          233  +#---------------------------------------------------------------
          234  +# Test calling sqlite3_close() with vtabs on the disconnect list.
          235  +#
          236  +ifcapable rtree {
          237  +  reset_db
          238  +  do_test 2.1.1 {
          239  +    sqlite3 db  test.db
          240  +    sqlite3 db2 test.db
          241  +  
          242  +    # Create a virtual table using [db]. 
          243  +    execsql {
          244  +      CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2);
          245  +      INSERT INTO rt VALUES(1, 2 ,3);
          246  +      SELECT * FROM rt;
          247  +    }
          248  +  
          249  +    # Drop the virtual table using [db2]. The sqlite3_vtab object belonging
          250  +    # to [db] is moved to the sqlite3.pDisconnect list.
          251  +    execsql { DROP TABLE rt } db2
          252  +  
          253  +    # Immediately close [db]. At one point this would fail due to the 
          254  +    # unfinalized statements held by the un-xDisconnect()ed sqlite3_vtab.
          255  +    db close
          256  +  } {}
          257  +  db2 close
          258  +}
          259  +
          260  +ifcapable fts3 {
          261  +  # Same test as above, except using fts3 instead of rtree.
          262  +  reset_db
          263  +  do_test 2.2.1 {
          264  +    sqlite3 db  test.db
          265  +    sqlite3 db2 test.db
          266  +    execsql {
          267  +      CREATE VIRTUAL TABLE ft USING fts3;
          268  +      INSERT INTO ft VALUES('hello world');
          269  +      SELECT * FROM ft;
          270  +    } 
          271  +    execsql { DROP TABLE ft } db2
          272  +    db close
          273  +  } {}
          274  +  db2 close
          275  +}
          276  +
   231    277   sqlite3_enable_shared_cache 0
   232    278   finish_test
          279  +

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