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

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

Overview
Comment:Merge all the latest changes and enhancements from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | apple-osx
Files: files | file ages | folders
SHA3-256: 521d5186b8258794b21aa63f81883475846cb7d26b3a1ae3c7c47cbb9602b426
User & Date: drh 2019-04-04 21:40:51
Context
2019-04-15
14:49
Bring in the latest enhancements from trunk. check-in: 378230ae user: drh tags: apple-osx
2019-04-04
21:40
Merge all the latest changes and enhancements from trunk. check-in: 521d5186 user: drh tags: apple-osx
20:55
Add further test cases to improve VDBE branch coverage. check-in: 51a95e52 user: dan tags: trunk
2019-04-02
01:00
Merge fixes from trunk. check-in: 20372906 user: drh tags: apple-osx
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  2348   2348         addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
  2349   2349         sqlite3VdbeVerifyAbortable(v, onError);
  2350   2350         addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid);
  2351   2351         VdbeCoverage(v);
  2352   2352         sqlite3RowidConstraint(pParse, onError, pDest);
  2353   2353         sqlite3VdbeJumpHere(v, addr2);
  2354   2354         autoIncStep(pParse, regAutoinc, regRowid);
  2355         -    }else if( pDest->pIndex==0 && !(db->mDbFlags & DBFLAG_Vacuum) ){
         2355  +    }else if( pDest->pIndex==0 && !(db->mDbFlags & DBFLAG_VacuumInto) ){
  2356   2356         addr1 = sqlite3VdbeAddOp2(v, OP_NewRowid, iDest, regRowid);
  2357   2357       }else{
  2358   2358         addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid);
  2359   2359         assert( (pDest->tabFlags & TF_Autoincrement)==0 );
  2360   2360       }
  2361   2361       sqlite3VdbeAddOp3(v, OP_RowData, iSrc, regData, 1);
  2362   2362       if( db->mDbFlags & DBFLAG_Vacuum ){

Changes to src/loadext.c.

   451    451     sqlite3_str_errcode,
   452    452     sqlite3_str_length,
   453    453     sqlite3_str_value,
   454    454     /* Version 3.25.0 and later */
   455    455     sqlite3_create_window_function,
   456    456     /* Version 3.26.0 and later */
   457    457   #ifdef SQLITE_ENABLE_NORMALIZE
   458         -  sqlite3_normalized_sql
          458  +  sqlite3_normalized_sql,
   459    459   #else
   460         -  0
          460  +  0,
   461    461   #endif
          462  +  /* Version 3.28.0 and later */
          463  +  sqlite3_stmt_isexplain,
          464  +  sqlite3_value_frombind
   462    465   };
   463    466   
   464    467   /*
   465    468   ** Attempt to load an SQLite extension library contained in the file
   466    469   ** zFile.  The entry point is zProc.  zProc may be 0 in which case a
   467    470   ** default entry point name (sqlite3_extension_init) is used.  Use
   468    471   ** of the default name is recommended.

Changes to src/parse.y.

  1690   1690   
  1691   1691   frame_opt(A) ::= .                             { 
  1692   1692     A = sqlite3WindowAlloc(pParse, 0, TK_UNBOUNDED, 0, TK_CURRENT, 0, 0);
  1693   1693   }
  1694   1694   frame_opt(A) ::= range_or_rows(X) frame_bound_s(Y) frame_exclude_opt(Z). { 
  1695   1695     A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0, Z);
  1696   1696   }
  1697         -frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z) frame_exclude_opt(W). { 
         1697  +frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND
         1698  +                          frame_bound_e(Z) frame_exclude_opt(W). { 
  1698   1699     A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr, W);
  1699   1700   }
  1700   1701   
  1701         -range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
  1702         -range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }
  1703         -range_or_rows(A) ::= GROUPS.  { A = TK_GROUPS;}
         1702  +range_or_rows(A) ::= RANGE|ROWS|GROUPS(X).   {A = @X; /*A-overwrites-X*/}
  1704   1703   
         1704  +frame_bound_s(A) ::= frame_bound(X).         {A = X;}
         1705  +frame_bound_s(A) ::= UNBOUNDED(X) PRECEDING. {A.eType = @X; A.pExpr = 0;}
         1706  +frame_bound_e(A) ::= frame_bound(X).         {A = X;}
         1707  +frame_bound_e(A) ::= UNBOUNDED(X) FOLLOWING. {A.eType = @X; A.pExpr = 0;}
  1705   1708   
  1706         -frame_bound_s(A) ::= frame_bound(X). { A = X; }
  1707         -frame_bound_s(A) ::= UNBOUNDED PRECEDING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
  1708         -frame_bound_e(A) ::= frame_bound(X). { A = X; }
  1709         -frame_bound_e(A) ::= UNBOUNDED FOLLOWING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
  1710         -
  1711         -frame_bound(A) ::= expr(X) PRECEDING.   { A.eType = TK_PRECEDING; A.pExpr = X; }
  1712         -frame_bound(A) ::= CURRENT ROW.         { A.eType = TK_CURRENT  ; A.pExpr = 0; }
  1713         -frame_bound(A) ::= expr(X) FOLLOWING.   { A.eType = TK_FOLLOWING; A.pExpr = X; }
         1709  +frame_bound(A) ::= expr(X) PRECEDING|FOLLOWING(Y).
         1710  +                                             {A.eType = @Y; A.pExpr = X;}
         1711  +frame_bound(A) ::= CURRENT(X) ROW.           {A.eType = @X; A.pExpr = 0;}
  1714   1712   
  1715   1713   %type frame_exclude_opt {u8}
  1716         -frame_exclude_opt(A) ::= . { A = 0; }
  1717         -frame_exclude_opt(A) ::= EXCLUDE frame_exclude(X). { A = X; }
         1714  +frame_exclude_opt(A) ::= . {A = 0;}
         1715  +frame_exclude_opt(A) ::= EXCLUDE frame_exclude(X). {A = X;}
  1718   1716   
  1719   1717   %type frame_exclude {u8}
  1720         -frame_exclude(A) ::= NO OTHERS.   { A = 0; }
  1721         -frame_exclude(A) ::= CURRENT ROW. { A = TK_CURRENT; }
  1722         -frame_exclude(A) ::= GROUP.       { A = TK_GROUP; }
  1723         -frame_exclude(A) ::= TIES.        { A = TK_TIES; }
         1718  +frame_exclude(A) ::= NO(X) OTHERS.   {A = @X; /*A-overwrites-X*/}
         1719  +frame_exclude(A) ::= CURRENT(X) ROW. {A = @X; /*A-overwrites-X*/}
         1720  +frame_exclude(A) ::= GROUP|TIES(X).  {A = @X; /*A-overwrites-X*/}
  1724   1721   
  1725   1722   
  1726   1723   %type window_clause {Window*}
  1727   1724   %destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);}
  1728   1725   window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; }
  1729   1726   
  1730   1727   %type over_clause {Window*}

Changes to src/sqlite.h.in.

  4977   4977   ** datatype of the value
  4978   4978   ** <tr><td><b>sqlite3_value_numeric_type&nbsp;&nbsp;</b>
  4979   4979   ** <td>&rarr;&nbsp;&nbsp;<td>Best numeric datatype of the value
  4980   4980   ** <tr><td><b>sqlite3_value_nochange&nbsp;&nbsp;</b>
  4981   4981   ** <td>&rarr;&nbsp;&nbsp;<td>True if the column is unchanged in an UPDATE
  4982   4982   ** against a virtual table.
  4983   4983   ** <tr><td><b>sqlite3_value_frombind&nbsp;&nbsp;</b>
  4984         -** <td>&rarr;&nbsp;&nbsp;<td>True if value originated a bound parameter
         4984  +** <td>&rarr;&nbsp;&nbsp;<td>True if value originated from a [bound parameter]
  4985   4985   ** </table></blockquote>
  4986   4986   **
  4987   4987   ** <b>Details:</b>
  4988   4988   **
  4989   4989   ** These routines extract type, size, and content information from
  4990   4990   ** [protected sqlite3_value] objects.  Protected sqlite3_value objects
  4991   4991   ** are used to pass parameter information into implementation of

Changes to src/sqliteInt.h.

  1577   1577   
  1578   1578   /*
  1579   1579   ** Allowed values for sqlite3.mDbFlags
  1580   1580   */
  1581   1581   #define DBFLAG_SchemaChange   0x0001  /* Uncommitted Hash table changes */
  1582   1582   #define DBFLAG_PreferBuiltin  0x0002  /* Preference to built-in funcs */
  1583   1583   #define DBFLAG_Vacuum         0x0004  /* Currently in a VACUUM */
  1584         -#define DBFLAG_SchemaKnownOk  0x0008  /* Schema is known to be valid */
         1584  +#define DBFLAG_VacuumInto     0x0008  /* Currently running VACUUM INTO */
         1585  +#define DBFLAG_SchemaKnownOk  0x0010  /* Schema is known to be valid */
  1585   1586   
  1586   1587   /*
  1587   1588   ** Bits of the sqlite3.dbOptFlags field that are used by the
  1588   1589   ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
  1589   1590   ** selectively disable various optimizations.
  1590   1591   */
  1591   1592   #define SQLITE_QueryFlattener 0x0001   /* Query flattening */
  1592         -                          /*  0x0002   available for reuse */
         1593  +#define SQLITE_WindowFunc     0x0002   /* Use xInverse for window functions */
  1593   1594   #define SQLITE_GroupByOrder   0x0004   /* GROUPBY cover of ORDERBY */
  1594   1595   #define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
  1595   1596   #define SQLITE_DistinctOpt    0x0010   /* DISTINCT using indexes */
  1596   1597   #define SQLITE_CoverIdxScan   0x0020   /* Covering index scans */
  1597   1598   #define SQLITE_OrderByIdxJoin 0x0040   /* ORDER BY of joins via index */
  1598   1599   #define SQLITE_Transitive     0x0080   /* Transitive constraints */
  1599   1600   #define SQLITE_OmitNoopJoin   0x0100   /* Omit unused tables in joins */

Changes to src/vacuum.c.

   135    135     sqlite3ExprDelete(pParse->db, pInto);
   136    136     return;
   137    137   }
   138    138   
   139    139   /*
   140    140   ** This routine implements the OP_Vacuum opcode of the VDBE.
   141    141   */
   142         -int sqlite3RunVacuum(
          142  +SQLITE_NOINLINE int sqlite3RunVacuum(
   143    143     char **pzErrMsg,        /* Write error message here */
   144    144     sqlite3 *db,            /* Database connection */
   145    145     int iDb,                /* Which attached DB to vacuum */
   146    146     sqlite3_value *pOut     /* Write results here, if not NULL. VACUUM INTO */
   147    147   ){
   148    148     int rc = SQLITE_OK;     /* Return code from service routines */
   149    149     Btree *pMain;           /* The database being vacuumed */
................................................................................
   159    159     int nRes;               /* Bytes of reserved space at the end of each page */
   160    160     int nDb;                /* Number of attached databases */
   161    161     const char *zDbMain;    /* Schema name of database to vacuum */
   162    162     const char *zOut;       /* Name of output file */
   163    163   
   164    164     if( !db->autoCommit ){
   165    165       sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
   166         -    return SQLITE_ERROR;
          166  +    return SQLITE_ERROR; /* IMP: R-12218-18073 */
   167    167     }
   168    168     if( db->nVdbeActive>1 ){
   169    169       sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
   170         -    return SQLITE_ERROR;
          170  +    return SQLITE_ERROR; /* IMP: R-15610-35227 */
   171    171     }
   172    172     saved_openFlags = db->openFlags;
   173    173     if( pOut ){
   174    174       if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){
   175    175         sqlite3SetString(pzErrMsg, db, "non-text filename");
   176    176         return SQLITE_ERROR;
   177    177       }
................................................................................
   226    226       sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp));
   227    227       i64 sz = 0;
   228    228       if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){
   229    229         rc = SQLITE_ERROR;
   230    230         sqlite3SetString(pzErrMsg, db, "output file already exists");
   231    231         goto end_of_vacuum;
   232    232       }
          233  +    db->mDbFlags |= DBFLAG_VacuumInto;
   233    234     }
   234    235     nRes = sqlite3BtreeGetOptimalReserve(pMain);
   235    236   
   236    237     /* A VACUUM cannot change the pagesize of an encrypted database. */
   237    238   #ifdef SQLITE_HAS_CODEC
   238    239     if( db->nextPagesize ){
   239    240       extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*);

Changes to src/wal.c.

   982    982   ** actually needed.
   983    983   */
   984    984   static void walCleanupHash(Wal *pWal){
   985    985     WalHashLoc sLoc;                /* Hash table location */
   986    986     int iLimit = 0;                 /* Zero values greater than this */
   987    987     int nByte;                      /* Number of bytes to zero in aPgno[] */
   988    988     int i;                          /* Used to iterate through aHash[] */
          989  +  int rc;                         /* Return code form walHashGet() */
   989    990   
   990    991     assert( pWal->writeLock );
   991    992     testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE-1 );
   992    993     testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE );
   993    994     testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE+1 );
   994    995   
   995    996     if( pWal->hdr.mxFrame==0 ) return;
   996    997   
   997    998     /* Obtain pointers to the hash-table and page-number array containing 
   998    999     ** the entry that corresponds to frame pWal->hdr.mxFrame. It is guaranteed
   999         -  ** that the page said hash-table and array reside on is already mapped.
         1000  +  ** that the page said hash-table and array reside on is already mapped.(1)
  1000   1001     */
  1001   1002     assert( pWal->nWiData>walFramePage(pWal->hdr.mxFrame) );
  1002   1003     assert( pWal->apWiData[walFramePage(pWal->hdr.mxFrame)] );
  1003         -  walHashGet(pWal, walFramePage(pWal->hdr.mxFrame), &sLoc);
         1004  +  rc = walHashGet(pWal, walFramePage(pWal->hdr.mxFrame), &sLoc);
         1005  +  if( NEVER(rc) ) return; /* Defense-in-depth, in case (1) above is wrong */
  1004   1006   
  1005   1007     /* Zero all hash-table entries that correspond to frame numbers greater
  1006   1008     ** than pWal->hdr.mxFrame.
  1007   1009     */
  1008   1010     iLimit = pWal->hdr.mxFrame - sLoc.iZero;
  1009   1011     assert( iLimit>0 );
  1010   1012     for(i=0; i<HASHTABLE_NSLOT; i++){

Changes to src/window.c.

  1088   1088     }
  1089   1089   
  1090   1090     pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  1091   1091     if( pWin==0 ) goto windowAllocErr;
  1092   1092     pWin->eFrmType = eType;
  1093   1093     pWin->eStart = eStart;
  1094   1094     pWin->eEnd = eEnd;
  1095         -  if( eExclude==0 && OptimizationDisabled(pParse->db, SQLITE_QueryFlattener) ){
         1095  +  if( eExclude==0 && OptimizationDisabled(pParse->db, SQLITE_WindowFunc) ){
  1096   1096       eExclude = TK_NO;
  1097   1097     }
  1098   1098     pWin->eExclude = eExclude;
  1099   1099     pWin->bImplicitFrame = bImplicitFrame;
  1100   1100     pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  1101   1101     pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
  1102   1102     return pWin;
................................................................................
  2319   2319   **       }
  2320   2320   **       Insert new row into eph table.
  2321   2321   **       if( first row of partition ){
  2322   2322   **         Rewind(csrEnd) ; Rewind(csrStart) ; Rewind(csrCurrent)
  2323   2323   **         regEnd = <expr2>
  2324   2324   **         regStart = <expr1>
  2325   2325   **       }else{
  2326         -**         while( (csrEnd.key + regEnd) <= csrCurrent.key ){
         2326  +**         if( (csrEnd.key + regEnd) <= csrCurrent.key ){
  2327   2327   **           AGGSTEP
  2328   2328   **         }
  2329         -**         RETURN_ROW
  2330   2329   **         while( (csrStart.key + regStart) < csrCurrent.key ){
  2331   2330   **           AGGINVERSE
  2332   2331   **         }
         2332  +**         RETURN_ROW
  2333   2333   **       }
  2334   2334   **     }
  2335   2335   **     flush:
  2336   2336   **       while( (csrEnd.key + regEnd) <= csrCurrent.key ){
  2337   2337   **         AGGSTEP
         2338  +**       }
         2339  +**       while( (csrStart.key + regStart) < csrCurrent.key ){
         2340  +**         AGGINVERSE
  2338   2341   **       }
  2339   2342   **       RETURN_ROW
  2340   2343   **
  2341   2344   **   RANGE BETWEEN <expr1> FOLLOWING AND <expr2> FOLLOWING
  2342   2345   **
  2343   2346   **     ... loop started by sqlite3WhereBegin() ...
  2344   2347   **       if( new partition ){
................................................................................
  2572   2575       sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
  2573   2576       sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
  2574   2577     }
  2575   2578   
  2576   2579     sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
  2577   2580   
  2578   2581     sqlite3VdbeJumpHere(v, addrNe);
         2582  +
         2583  +  /* Beginning of the block executed for the second and subsequent rows. */
  2579   2584     if( regPeer ){
  2580   2585       windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
  2581   2586     }
  2582   2587     if( pMWin->eStart==TK_FOLLOWING ){
  2583   2588       windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  2584   2589       if( pMWin->eEnd!=TK_UNBOUNDED ){
  2585   2590         if( pMWin->eFrmType==TK_RANGE ){
................................................................................
  2593   2598         }else{
  2594   2599           windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
  2595   2600           windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  2596   2601         }
  2597   2602       }
  2598   2603     }else
  2599   2604     if( pMWin->eEnd==TK_PRECEDING ){
         2605  +    int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
  2600   2606       windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
         2607  +    if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  2601   2608       windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  2602         -    windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
         2609  +    if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  2603   2610     }else{
  2604   2611       int addr = 0;
  2605   2612       windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
  2606   2613       if( pMWin->eEnd!=TK_UNBOUNDED ){
  2607   2614         if( pMWin->eFrmType==TK_RANGE ){
  2608   2615           int lbl = 0;
  2609   2616           addr = sqlite3VdbeCurrentAddr(v);
................................................................................
  2638   2645       addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
  2639   2646       sqlite3VdbeJumpHere(v, addrGosubFlush);
  2640   2647     }
  2641   2648   
  2642   2649     addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  2643   2650     VdbeCoverage(v);
  2644   2651     if( pMWin->eEnd==TK_PRECEDING ){
         2652  +    int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
  2645   2653       windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
         2654  +    if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  2646   2655       windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  2647   2656     }else if( pMWin->eStart==TK_FOLLOWING ){
  2648   2657       int addrStart;
  2649   2658       int addrBreak1;
  2650   2659       int addrBreak2;
  2651   2660       int addrBreak3;
  2652   2661       windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);

Changes to test/autoinc.test.

    12     12   # focus of this script is testing the AUTOINCREMENT features.
    13     13   #
    14     14   # $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
           19  +set testprefix autoinc
    19     20   
    20     21   # If the library is not compiled with autoincrement support then
    21     22   # skip all tests in this file.
    22     23   #
    23     24   ifcapable {!autoinc} {
    24     25     finish_test
    25     26     return
................................................................................
   851    852     set res [catch {db eval {
   852    853       INSERT INTO t1(b) VALUES('two'),('three'),('four');
   853    854       INSERT INTO t1(b) VALUES('five');
   854    855       PRAGMA integrity_check;
   855    856     }} msg]
   856    857     lappend res $msg
   857    858   } {0 ok}
          859  +
          860  +#--------------------------------------------------------------------------
          861  +reset_db
          862  +do_execsql_test 13.0 {
          863  +  CREATE TABLE t1(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
          864  +  CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
          865  +  CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
          866  +
          867  +  INSERT INTO t1 VALUES(NULL, 1);
          868  +  INSERT INTO t2 VALUES(NULL, 2);
          869  +  INSERT INTO t3 VALUES(NULL, 3);
          870  +
          871  +  SELECT name FROM sqlite_sequence;
          872  +} {t1 t2 t3}
          873  +
          874  +do_execsql_test 13.1 {
          875  +  UPDATE sqlite_sequence SET name=NULL WHERE name='t2';
          876  +  INSERT INTO t3 VALUES(NULL, 4);
          877  +  DELETE FROM t3;
          878  +  INSERT INTO t3 VALUES(NULL, 5);
          879  +  SELECT * FROM t3;
          880  +} {3 5}
          881  +
   858    882   
   859    883   finish_test

Changes to test/delete4.test.

   178    178   do_execsql_test 6.1 {
   179    179     DROP TABLE IF EXISTS t2;
   180    180     CREATE TABLE t2(x INT);
   181    181     INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
   182    182     DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);
   183    183     SELECT x FROM t2;
   184    184   } {5}
          185  +
          186  +#-------------------------------------------------------------------------
          187  +# Test the effect of failing to find a table row based on an index key
          188  +# within a DELETE. Either because the db is corrupt, or a trigger on another
          189  +# row already deleted the entry, or because a BEFORE trigger on the current
          190  +# row has already deleted it.
          191  +#
          192  +do_execsql_test 7.1.0 {
          193  +  CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;
          194  +  CREATE INDEX t3a ON t3(a);
          195  +  CREATE INDEX t3b ON t3(b);
          196  +
          197  +  INSERT INTO t3 VALUES(1, 1, 1);
          198  +  INSERT INTO t3 VALUES(2, 2, 2);
          199  +  INSERT INTO t3 VALUES(3, 3, 3);
          200  +  INSERT INTO t3 VALUES(4, 4, 1);
          201  +}
          202  +do_execsql_test 7.1.1 {
          203  +  DELETE FROM t3 WHERE a=4 OR b=1;
          204  +}
          205  +do_execsql_test 7.1.2 {
          206  +  SELECT * FROM t3;
          207  +} { 2 2 2   3 3 3 }
          208  +
          209  +do_execsql_test 7.2.0 {
          210  +  CREATE TABLE t4(a PRIMARY KEY, b) WITHOUT ROWID;
          211  +  CREATE INDEX t4i ON t4(b);
          212  +  INSERT INTO t4 VALUES(1, 'hello');
          213  +  INSERT INTO t4 VALUES(2, 'world');
          214  +
          215  +  CREATE TABLE t5(a PRIMARY KEY, b) WITHOUT ROWID;
          216  +  CREATE INDEX t5i ON t5(b);
          217  +  INSERT INTO t5 VALUES(1, 'hello');
          218  +  INSERT INTO t5 VALUES(3, 'world');
          219  +
          220  +  PRAGMA writable_schema = 1;
          221  +  UPDATE sqlite_master SET rootpage = (
          222  +    SELECT rootpage FROM sqlite_master WHERE name = 't5'
          223  +  ) WHERE name = 't4';
          224  +}
          225  +
          226  +db close
          227  +sqlite3 db test.db
          228  +do_execsql_test 7.2.1 {
          229  +  DELETE FROM t4 WHERE b='world'
          230  +}
          231  +reset_db
          232  +
          233  +do_execsql_test 7.3.0 {
          234  +  CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;
          235  +  INSERT INTO t3 VALUES(1, 2, 3);
          236  +  INSERT INTO t3 VALUES(4, 5, 6);
          237  +  INSERT INTO t3 VALUES(7, 8, 9);
          238  +  CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN
          239  +    DELETE FROM t3 WHERE id=old.id+3;
          240  +  END;
          241  +}
          242  +
          243  +do_execsql_test 7.3.1 {
          244  +  DELETE FROM t3 WHERE a IN(2, 5, 8);
          245  +  SELECT * FROM t3;
          246  +} {}
          247  +
          248  +do_execsql_test 7.3.2 {
          249  +  DROP TRIGGER t3t;
          250  +  INSERT INTO t3 VALUES(1, 2, 3);
          251  +  INSERT INTO t3 VALUES(4, 5, 6);
          252  +  INSERT INTO t3 VALUES(7, 8, 9);
          253  +  CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN
          254  +    DELETE FROM t3 WHERE id=old.id;
          255  +  END;
          256  +}
          257  +
          258  +do_execsql_test 7.3.3 {
          259  +  DELETE FROM t3 WHERE a IN(2, 5, 8);
          260  +  SELECT * FROM t3;
          261  +} {}
   185    262   
   186    263   
   187    264   finish_test

Changes to test/e_vacuum.test.

   228    228     INSERT INTO t4(x) VALUES('z');
   229    229     DELETE FROM t4 WHERE x = 'y';
   230    230     SELECT rowid, x FROM t4;
   231    231   } {1 x 3 z}
   232    232   do_execsql_test e_vacuum-3.1.2 {
   233    233     VACUUM;
   234    234     SELECT rowid, x FROM t4;
   235         -} {1 x 3 z}
   236         -# Was: {1 x 2 z}
          235  +} {1 x 2 z}
   237    236   
          237  +# Rowids are preserved if an INTEGER PRIMARY KEY is used
   238    238   do_execsql_test e_vacuum-3.1.3 {
   239    239     CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
   240    240     INSERT INTO t5(x) VALUES('x');
   241    241     INSERT INTO t5(x) VALUES('y');
   242    242     INSERT INTO t5(x) VALUES('z');
   243    243     DELETE FROM t5 WHERE x = 'y';
   244    244     SELECT rowid, x FROM t5;
   245    245   } {1 x 3 z}
   246    246   do_execsql_test e_vacuum-3.1.4 {
   247    247     VACUUM;
   248    248     SELECT rowid, x FROM t5;
   249    249   } {1 x 3 z}
   250    250   
   251         -# EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open
   252         -# transaction, or if there are one or more active SQL statements when it
   253         -# is run.
          251  +# Rowid is preserved for VACUUM INTO
          252  +do_execsql_test e_vacuum-3.1.5 {
          253  +  DROP TABLE t5;
          254  +  CREATE TABLE t5(x);
          255  +  INSERT INTO t5(x) VALUES('x');
          256  +  INSERT INTO t5(x) VALUES('y');
          257  +  INSERT INTO t5(x) VALUES('z');
          258  +  DELETE FROM t5 WHERE x = 'y';
          259  +  SELECT rowid, x FROM t5;
          260  +} {1 x 3 z}
          261  +forcedelete test2.db
          262  +do_execsql_test e_vacuum-3.1.6 {
          263  +  VACUUM INTO 'test2.db';
          264  +  ATTACH 'test2.db' AS aux1;
          265  +  SELECT rowid, x FROM aux1.t5;
          266  +  DETACH aux1;
          267  +} {1 x 3 z}
          268  +
          269  +# Rowids are not renumbered if the table being vacuumed
          270  +# has indexes.
          271  +do_execsql_test e_vacuum-3.1.7 {
          272  +  DROP TABLE t5;
          273  +  CREATE TABLE t5(x,y,z);
          274  +  INSERT INTO t5(x) VALUES('x');
          275  +  INSERT INTO t5(x) VALUES('y');
          276  +  INSERT INTO t5(x) VALUES('z');
          277  +  UPDATE t5 SET y=x, z=random();
          278  +  DELETE FROM t5 WHERE x = 'y';
          279  +  CREATE INDEX t5x ON t5(x);
          280  +  CREATE UNIQUE INDEX t5y ON t5(y);
          281  +  CREATE INDEX t5zxy ON t5(z,x,y);
          282  +  SELECT rowid, x FROM t5;
          283  +} {1 x 3 z}
          284  +do_execsql_test e_vacuum-3.1.8 {
          285  +  VACUUM;
          286  +  SELECT rowid, x FROM t5;
          287  +} {1 x 3 z}
          288  +
          289  +# EVIDENCE-OF: R-12218-18073 A VACUUM will fail if there is an open
          290  +# transaction on the database connection that is attempting to run the
          291  +# VACUUM.
   254    292   #
   255    293   do_execsql_test  e_vacuum-3.2.1.1 { BEGIN } {}
   256    294   do_catchsql_test e_vacuum-3.2.1.2 { 
   257    295     VACUUM 
   258    296   } {1 {cannot VACUUM from within a transaction}}
   259    297   do_execsql_test  e_vacuum-3.2.1.3 { COMMIT } {}
   260    298   do_execsql_test  e_vacuum-3.2.1.4 { VACUUM } {}

Changes to test/in.test.

   709    709                              WHERE name='Bob'
   710    710                            ) AS 't'
   711    711                      WHERE x=1
   712    712                    )
   713    713                AND t6.id IN (1,id)
   714    714            );
   715    715   } {1 Alice}
          716  +
          717  +#-------------------------------------------------------------------------
          718  +reset_db
          719  +do_execsql_test in-16.0 {
          720  +  CREATE TABLE x1(a, b);
          721  +  INSERT INTO x1(a) VALUES(1), (2), (3), (4), (5), (6);
          722  +  CREATE INDEX x1i ON x1(a, b);
          723  +}
          724  +
          725  +do_execsql_test in-16.1 {
          726  +  SELECT * FROM x1 
          727  +  WHERE a IN (SELECT a FROM x1 WHERE (a%2)==0) 
          728  +  ORDER BY a DESC, b;
          729  +} {6 {} 4 {} 2 {}}
          730  +
          731  +do_execsql_test in-16.2 {
          732  +  SELECT * FROM x1 
          733  +  WHERE a IN (SELECT a FROM x1 WHERE (a%7)==0) 
          734  +  ORDER BY a DESC, b;
          735  +} {}
          736  +
   716    737   
   717    738   
   718    739   finish_test

Changes to test/insert4.test.

   594    594   do_test 10.3 {
   595    595     execsql { PRAGMA integrity_check }
   596    596     set sqlite3_xferopt_count 0
   597    597     execsql { INSERT INTO x     SELECT * FROM t8 }
   598    598     set sqlite3_xferopt_count
   599    599   } {1}
   600    600   
          601  +#-------------------------------------------------------------------------
          602  +# xfer transfer between tables where the source has an empty partial index.
          603  +#
          604  +do_execsql_test 11.0 {
          605  +  CREATE TABLE t9(a, b, c);
          606  +  CREATE INDEX t9a ON t9(a);
          607  +  CREATE INDEX t9b ON t9(b) WHERE c=0;
          608  +
          609  +  INSERT INTO t9 VALUES(1, 1, 1);
          610  +  INSERT INTO t9 VALUES(2, 2, 2);
          611  +  INSERT INTO t9 VALUES(3, 3, 3);
          612  +
          613  +  CREATE TABLE t10(a, b, c);
          614  +  CREATE INDEX t10a ON t10(a);
          615  +  CREATE INDEX t10b ON t10(b) WHERE c=0;
          616  +
          617  +  INSERT INTO t10 SELECT * FROM t9;
          618  +  SELECT * FROM t10;
          619  +  PRAGMA integrity_check;
          620  +} {1 1 1  2 2 2  3 3 3  ok}
   601    621   
   602    622   finish_test

Changes to test/pragma.test.

   247    247   } {0}
   248    248   do_test pragma-1.14.4 {
   249    249     execsql {
   250    250       PRAGMA synchronous=10;
   251    251       PRAGMA synchronous;
   252    252     }
   253    253   } {2}
          254  +
          255  +do_execsql_test 1.15.1 {
          256  +  PRAGMA default_cache_size = 0;
          257  +}
          258  +do_execsql_test 1.15.2 {
          259  +  PRAGMA default_cache_size;
          260  +} $DFLT_CACHE_SZ
          261  +do_execsql_test 1.15.3 {
          262  +  PRAGMA default_cache_size = -500;
          263  +}
          264  +do_execsql_test 1.15.4 {
          265  +  PRAGMA default_cache_size;
          266  +} 500
          267  +do_execsql_test 1.15.3 {
          268  +  PRAGMA default_cache_size = 500;
          269  +}
          270  +do_execsql_test 1.15.4 {
          271  +  PRAGMA default_cache_size;
          272  +} 500
          273  +db close
          274  +hexio_write test.db 48 FFFFFF00
          275  +sqlite3 db test.db
          276  +do_execsql_test 1.15.4 {
          277  +  PRAGMA default_cache_size;
          278  +} 256
   254    279   } ;# ifcapable pager_pragmas
   255    280   
   256    281   # Test turning "flag" pragmas on and off.
   257    282   #
   258    283   ifcapable debug {
   259    284     # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
   260    285     #

Changes to test/skipscan2.test.

   196    196       execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') }
   197    197     }
   198    198     execsql { ANALYZE }
   199    199   } {}
   200    200   do_eqp_test skipscan2-3.3eqp {
   201    201     SELECT * FROM t3 WHERE b=42;
   202    202   } {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)}
          203  +
   203    204   
   204    205   
   205    206   finish_test

Changes to test/triggerC.test.

  1052   1052   } {1 {1st ORDER BY term does not match any column in the result set}}
  1053   1053   
  1054   1054   do_catchsql_test 16.2 {
  1055   1055     SELECT count(*) FROM sqlite_master 
  1056   1056     GROUP BY raise(IGNORE) 
  1057   1057     HAVING raise(ABORT, 'msg');
  1058   1058   } {1 {RAISE() may only be used within a trigger-program}}
         1059  +
         1060  +#-------------------------------------------------------------------------
         1061  +# Datatype mismatch on IPK when there are BEFORE triggers.
         1062  +#
         1063  +do_execsql_test 17.0 {
         1064  +  CREATE TABLE xyz(x INTEGER PRIMARY KEY, y, z);
         1065  +  CREATE TRIGGER xyz_tr BEFORE INSERT ON xyz BEGIN
         1066  +    SELECT new.x;
         1067  +  END;
         1068  +}
         1069  +do_catchsql_test 17.1 {
         1070  +  INSERT INTO xyz VALUES('hello', 2, 3);
         1071  +} {1 {datatype mismatch}}
         1072  +
  1059   1073   
  1060   1074   finish_test
  1061   1075   

Changes to test/window1.test.

  1077   1077     SELECT min(x) FROM t1;
  1078   1078   } {1}
  1079   1079   do_execsql_test 27.2 {
  1080   1080     SELECT min(x) OVER win FROM t1
  1081   1081     WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  1082   1082   } {1 1 1 2 3 4}
  1083   1083   
         1084  +#-------------------------------------------------------------------------
         1085  +
         1086  +reset_db
         1087  +do_execsql_test 28.1.1 {
         1088  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
         1089  +  INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0);
         1090  +  INSERT INTO t1 VALUES (13,'M', 'cc', NULL);
         1091  +}
         1092  +
         1093  +do_execsql_test 28.1.2 {
         1094  +  SELECT group_concat(b,'') OVER w1 FROM t1
         1095  +    WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING)
         1096  +} {
         1097  +  {} {}
         1098  +}
         1099  +
         1100  +do_execsql_test 28.2.1 {
         1101  +  CREATE TABLE t2(a TEXT, b INTEGER);
         1102  +  INSERT INTO t2 VALUES('A', NULL);
         1103  +  INSERT INTO t2 VALUES('B', NULL);
         1104  +}
         1105  +
         1106  +do_execsql_test 28.2.1 {
         1107  +  DROP TABLE IF EXISTS t1;
         1108  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
         1109  +  INSERT INTO t1 VALUES
         1110  +    (10,'J', 'cc', NULL),
         1111  +    (11,'K', 'cc', 'xyz'),
         1112  +    (13,'M', 'cc', NULL);
         1113  +}
         1114  +
         1115  +do_execsql_test 28.2.2 {
         1116  +  SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
         1117  +    WINDOW w1 AS
         1118  +    (ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
         1119  +    ORDER BY c, d, a;
         1120  +} {
         1121  +  10 J cc NULL JM |
         1122  +  13 M cc NULL JM | 
         1123  +  11 K cc 'xyz' K |
         1124  +}
         1125  +
         1126  +#-------------------------------------------------------------------------
         1127  +reset_db
         1128  +
         1129  +do_execsql_test 29.1 {
         1130  +  DROP TABLE IF EXISTS t1;
         1131  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
         1132  +  INSERT INTO t1 VALUES
         1133  +    (1, 'A', 'aa', 2.5),
         1134  +    (2, 'B', 'bb', 3.75),
         1135  +    (3, 'C', 'cc', 1.0),
         1136  +    (4, 'D', 'cc', 8.25),
         1137  +    (5, 'E', 'bb', 6.5),
         1138  +    (6, 'F', 'aa', 6.5),
         1139  +    (7, 'G', 'aa', 6.0),
         1140  +    (8, 'H', 'bb', 9.0),
         1141  +    (9, 'I', 'aa', 3.75),
         1142  +    (10,'J', 'cc', NULL),
         1143  +    (11,'K', 'cc', 'xyz'),
         1144  +    (12,'L', 'cc', 'xyZ'),
         1145  +    (13,'M', 'cc', NULL);
         1146  +}
         1147  +
         1148  +do_execsql_test 29.2 {
         1149  +  SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
         1150  +    WINDOW w1 AS
         1151  +    (PARTITION BY c ORDER BY d DESC
         1152  +     RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
         1153  +    ORDER BY c, d, a;
         1154  +} {
         1155  +  1 A aa 2.5 FG | 
         1156  +  9 I aa 3.75 F | 
         1157  +  7 G aa 6 {} | 
         1158  +  6 F aa 6.5 {} | 
         1159  +  2 B bb 3.75 HE |
         1160  +  5 E bb 6.5 H | 
         1161  +  8 H bb 9 {} | 
         1162  +  10 J cc NULL JM | 
         1163  +  13 M cc NULL JM | 
         1164  +  3 C cc 1 {} | 
         1165  +  4 D cc 8.25 {} | 
         1166  +  12 L cc 'xyZ' L | 
         1167  +  11 K cc 'xyz' K |
         1168  +}
  1084   1169   
  1085   1170   finish_test
  1086   1171   
  1087   1172   

Changes to test/window8.tcl.

   264    264                dense_rank() OVER win
   265    265         FROM t3
   266    266         WINDOW win AS ( $frame $ex )
   267    267         ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
   268    268       "
   269    269     }
   270    270   }
          271  +
          272  +==========
          273  +
          274  +execsql_test 6.0 {
          275  +  DROP TABLE IF EXISTS t2;
          276  +  CREATE TABLE t2(a TEXT, b INTEGER);
          277  +  INSERT INTO t2 VALUES('A', NULL);
          278  +  INSERT INTO t2 VALUES('B', NULL);
          279  +  INSERT INTO t2 VALUES('C', 1);
          280  +}
          281  +
          282  +execsql_test 6.1 {
          283  +  SELECT string_agg(a, '.') OVER (
          284  +    ORDER BY b NULLS FIRST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
          285  +  )
          286  +  FROM t2
          287  +}
          288  +
          289  +execsql_test 6.2 {
          290  +  SELECT string_agg(a, '.') OVER (
          291  +    ORDER BY b DESC NULLS LAST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
          292  +  )
          293  +  FROM t2
          294  +}
          295  +
   271    296   
   272    297   finish_test
   273    298   
   274    299   

Changes to test/window8.test.

  3837   3837   
  3838   3838   do_execsql_test 5.1.7.1 {
  3839   3839     SELECT max(c) OVER win,
  3840   3840                min(c) OVER win,
  3841   3841                count(a) OVER win
  3842   3842         FROM t3
  3843   3843         WINDOW win AS (  ORDER BY c , b , a 
  3844         -        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS  )
         3844  +        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
  3845   3845         ORDER BY 1 , 2 , 3
  3846   3846   } {979 102 83   979 102 83   979 102 83   979 102 83   979 102 83   979 102 83
  3847   3847     979 102 83   979 113 81   979 113 82   979 133 80   979 148 79   979 158 77
  3848   3848     979 158 78   979 160 77   979 208 76   979 223 75   979 224 74   979 234 73
  3849   3849     979 238 72   979 239 71   979 247 70   979 250 69   979 252 68   979 256 67
  3850   3850     979 257 66   979 295 65   979 309 64   979 330 63   979 335 62   979 336 61
  3851   3851     979 346 60   979 354 59   979 355 58   979 355 58   979 393 56   979 393 57
................................................................................
  3861   3861   
  3862   3862   do_execsql_test 5.1.7.2 {
  3863   3863     SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
  3864   3864                rank() OVER win,
  3865   3865                dense_rank() OVER win
  3866   3866         FROM t3
  3867   3867         WINDOW win AS (  ORDER BY c , b , a 
  3868         -        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS  )
         3868  +        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
  3869   3869         ORDER BY 1 , 2 , 3
  3870   3870   } {3830 89 89   4741 88 88   5640 84 84   5640 85 85   5640 86 86   5640 87 87
  3871   3871     6485 81 81   6485 82 82   6485 83 83   7324 80 80   8163 78 78   8163 79 79
  3872   3872     8968 73 73   8968 74 74   8968 75 75   8968 76 76   8968 77 77   9745 69 69
  3873   3873     9745 70 70   9745 71 71   9745 72 72   10504 65 65   10504 66 66
  3874   3874     10504 67 67   10504 68 68   11215 64 64   11920 63 63   12603 62 62
  3875   3875     13274 60 60   13274 61 61   13941 59 59   14608 55 55   14608 56 56
................................................................................
  4162   4162   
  4163   4163   do_execsql_test 5.2.7.1 {
  4164   4164     SELECT max(c) OVER win,
  4165   4165                min(c) OVER win,
  4166   4166                count(a) OVER win
  4167   4167         FROM t3
  4168   4168         WINDOW win AS (  ORDER BY c , b , a 
  4169         -        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW  )
         4169  +        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
  4170   4170         ORDER BY 1 , 2 , 3
  4171   4171   } {963 929 6   979 102 82   979 102 82   979 102 82   979 102 82   979 102 82
  4172   4172     979 102 83   979 113 80   979 113 81   979 113 82   979 133 79   979 148 78
  4173   4173     979 158 76   979 158 77   979 160 76   979 208 75   979 223 74   979 224 73
  4174   4174     979 234 72   979 238 71   979 239 70   979 247 69   979 250 68   979 252 67
  4175   4175     979 256 66   979 257 65   979 295 64   979 309 64   979 330 62   979 335 61
  4176   4176     979 336 60   979 346 59   979 354 59   979 355 57   979 355 57   979 393 55
................................................................................
  4186   4186   
  4187   4187   do_execsql_test 5.2.7.2 {
  4188   4188     SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
  4189   4189                rank() OVER win,
  4190   4190                dense_rank() OVER win
  4191   4191         FROM t3
  4192   4192         WINDOW win AS (  ORDER BY c , b , a 
  4193         -        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW  )
         4193  +        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
  4194   4194         ORDER BY 1 , 2 , 3
  4195   4195   } {2851 89 89   3778 88 88   4681 87 87   5556 83 83   5574 82 82   5586 81 81
  4196   4196     5640 84 84   5640 85 85   5640 86 86   7324 80 80   8123 77 77   8129 73 73
  4197   4197     8129 74 74   8163 78 78   8163 79 79   8940 71 71   8968 75 75   8968 76 76
  4198   4198     9727 66 66   9745 69 69   9745 70 70   9745 72 72   10504 65 65
  4199   4199     10504 67 67   10504 68 68   11215 64 64   11844 62 62   11920 63 63
  4200   4200     13274 60 60   13274 61 61   13897 58 58   13903 57 57   13925 56 56
................................................................................
  4477   4477   
  4478   4478   do_execsql_test 5.3.7.1 {
  4479   4479     SELECT max(c) OVER win,
  4480   4480                min(c) OVER win,
  4481   4481                count(a) OVER win
  4482   4482         FROM t3
  4483   4483         WINDOW win AS (  ORDER BY c , b , a 
  4484         -        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP  )
         4484  +        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
  4485   4485         ORDER BY 1 , 2 , 3
  4486   4486   } {963 929 6   979 102 82   979 102 82   979 102 82   979 102 82   979 102 82
  4487   4487     979 102 83   979 113 80   979 113 81   979 113 82   979 133 79   979 148 78
  4488   4488     979 158 76   979 158 77   979 160 76   979 208 75   979 223 74   979 224 73
  4489   4489     979 234 72   979 238 71   979 239 70   979 247 69   979 250 68   979 252 67
  4490   4490     979 256 66   979 257 65   979 295 64   979 309 64   979 330 62   979 335 61
  4491   4491     979 336 60   979 346 59   979 354 59   979 355 57   979 355 57   979 393 55
................................................................................
  4501   4501   
  4502   4502   do_execsql_test 5.3.7.2 {
  4503   4503     SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
  4504   4504                rank() OVER win,
  4505   4505                dense_rank() OVER win
  4506   4506         FROM t3
  4507   4507         WINDOW win AS (  ORDER BY c , b , a 
  4508         -        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP  )
         4508  +        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
  4509   4509         ORDER BY 1 , 2 , 3
  4510   4510   } {2851 89 89   3778 88 88   4681 87 87   5556 83 83   5574 82 82   5586 81 81
  4511   4511     5640 84 84   5640 85 85   5640 86 86   7324 80 80   8123 77 77   8129 73 73
  4512   4512     8129 74 74   8163 78 78   8163 79 79   8940 71 71   8968 75 75   8968 76 76
  4513   4513     9727 66 66   9745 69 69   9745 70 70   9745 72 72   10504 65 65
  4514   4514     10504 67 67   10504 68 68   11215 64 64   11844 62 62   11920 63 63
  4515   4515     13274 60 60   13274 61 61   13897 58 58   13903 57 57   13925 56 56
................................................................................
  4800   4800   
  4801   4801   do_execsql_test 5.4.7.1 {
  4802   4802     SELECT max(c) OVER win,
  4803   4803                min(c) OVER win,
  4804   4804                count(a) OVER win
  4805   4805         FROM t3
  4806   4806         WINDOW win AS (  ORDER BY c , b , a 
  4807         -        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES  )
         4807  +        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
  4808   4808         ORDER BY 1 , 2 , 3
  4809   4809   } {979 102 83   979 102 83   979 102 83   979 102 83   979 102 83   979 102 83
  4810   4810     979 102 83   979 113 81   979 113 82   979 133 80   979 148 79   979 158 77
  4811   4811     979 158 78   979 160 77   979 208 76   979 223 75   979 224 74   979 234 73
  4812   4812     979 238 72   979 239 71   979 247 70   979 250 69   979 252 68   979 256 67
  4813   4813     979 257 66   979 295 65   979 309 64   979 330 63   979 335 62   979 336 61
  4814   4814     979 346 60   979 354 59   979 355 58   979 355 58   979 393 56   979 393 57
................................................................................
  4824   4824   
  4825   4825   do_execsql_test 5.4.7.2 {
  4826   4826     SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
  4827   4827                rank() OVER win,
  4828   4828                dense_rank() OVER win
  4829   4829         FROM t3
  4830   4830         WINDOW win AS (  ORDER BY c , b , a 
  4831         -        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES  )
         4831  +        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
  4832   4832         ORDER BY 1 , 2 , 3
  4833   4833   } {3830 89 89   4741 88 88   5640 84 84   5640 85 85   5640 86 86   5640 87 87
  4834   4834     6485 81 81   6485 82 82   6485 83 83   7324 80 80   8163 78 78   8163 79 79
  4835   4835     8968 73 73   8968 74 74   8968 75 75   8968 76 76   8968 77 77   9745 69 69
  4836   4836     9745 70 70   9745 71 71   9745 72 72   10504 65 65   10504 66 66
  4837   4837     10504 67 67   10504 68 68   11215 64 64   11920 63 63   12603 62 62
  4838   4838     13274 60 60   13274 61 61   13941 59 59   14608 55 55   14608 56 56
................................................................................
  4843   4843     19788 36 36   20181 35 35   20536 34 34   20891 30 30   20891 31 31
  4844   4844     20891 32 32   20891 33 33   21226 28 28   21226 29 29   21535 27 27
  4845   4845     21830 26 26   22087 22 22   22087 23 23   22087 24 24   22087 25 25
  4846   4846     22334 21 21   22573 17 17   22573 18 18   22573 19 19   22573 20 20
  4847   4847     22796 11 11   22796 12 12   22796 13 13   22796 14 14   22796 15 15
  4848   4848     22796 16 16   22929 10 10   23042 9 9   23155 1 1   23155 2 2   23155 3 3
  4849   4849     23155 4 4   23155 5 5   23155 6 6   23155 7 7   23155 8 8}
         4850  +
         4851  +#==========================================================================
         4852  +
         4853  +do_execsql_test 6.0 {
         4854  +  DROP TABLE IF EXISTS t2;
         4855  +  CREATE TABLE t2(a TEXT, b INTEGER);
         4856  +  INSERT INTO t2 VALUES('A', NULL);
         4857  +  INSERT INTO t2 VALUES('B', NULL);
         4858  +  INSERT INTO t2 VALUES('C', 1);
         4859  +} {}
         4860  +
         4861  +do_execsql_test 6.1 {
         4862  +  SELECT group_concat(a, '.') OVER (
         4863  +    ORDER BY b  RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
         4864  +  )
         4865  +  FROM t2
         4866  +} {A.B   A.B   {}}
         4867  +
         4868  +do_execsql_test 6.2 {
         4869  +  SELECT group_concat(a, '.') OVER (
         4870  +    ORDER BY b DESC  RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
         4871  +  )
         4872  +  FROM t2
         4873  +} {{}   A.B   A.B}
  4850   4874   
  4851   4875   finish_test

Changes to test/without_rowid1.test.

   350    350     CREATE UNIQUE INDEX t2b ON t2(b);
   351    351     UPDATE t2 SET b=1 WHERE b='';
   352    352   }
   353    353   
   354    354   do_execsql_test 10.1 {
   355    355     DELETE FROM t2 WHERE b=1
   356    356   }
          357  +
          358  +#-------------------------------------------------------------------------
          359  +# UNIQUE constraint violation in an UPDATE with a multi-column PK.
          360  +#
          361  +reset_db
          362  +do_execsql_test 10.0 {
          363  +  CREATE TABLE t1(a, b, c UNIQUE, PRIMARY KEY(a, b)) WITHOUT ROWID;
          364  +  INSERT INTO t1 VALUES('a', 'a', 1);
          365  +  INSERT INTO t1 VALUES('a', 'b', 2);
          366  +  INSERT INTO t1 VALUES('b', 'a', 3);
          367  +  INSERT INTO t1 VALUES('b', 'b', 4);
          368  +}
          369  +
          370  +do_catchsql_test 10.1 {
          371  +  UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'a');
          372  +} {0 {}}
          373  +do_catchsql_test 10.2 {
          374  +  UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'b');
          375  +} {1 {UNIQUE constraint failed: t1.c}}
          376  +do_catchsql_test 10.3 {
          377  +  UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'a');
          378  +} {1 {UNIQUE constraint failed: t1.c}}
          379  +do_catchsql_test 10.4 {
          380  +  UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'b');
          381  +} {1 {UNIQUE constraint failed: t1.c}}
          382  +do_catchsql_test 10.5 {
          383  +  UPDATE t1 SET c=1 WHERE (a, b) = ('c', 'c');
          384  +} {0 {}}
          385  +
          386  +do_execsql_test 10.6 {
          387  +  CREATE TRIGGER t1_tr BEFORE UPDATE ON t1 BEGIN
          388  +    DELETE FROM t1 WHERE a = new.a;
          389  +  END;
          390  +  UPDATE t1 SET c = c+1 WHERE a = 'a';
          391  +  SELECT * FROM t1;
          392  +} {b a 3  b b 4}
   357    393   
   358    394     
   359    395   finish_test

Changes to tool/mkkeywordhash.c.

   289    289     { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
   290    290     { "UPDATE",           "TK_UPDATE",       ALWAYS                 },
   291    291     { "USING",            "TK_USING",        ALWAYS                 },
   292    292     { "VACUUM",           "TK_VACUUM",       VACUUM                 },
   293    293     { "VALUES",           "TK_VALUES",       ALWAYS                 },
   294    294     { "VIEW",             "TK_VIEW",         VIEW                   },
   295    295     { "VIRTUAL",          "TK_VIRTUAL",      VTAB                   },
          296  +  { "WHEN",             "TK_WHEN",         ALWAYS                 },
          297  +  { "WHERE",            "TK_WHERE",        ALWAYS                 },
   296    298     { "WINDOW",           "TK_WINDOW",       WINDOWFUNC             },
   297    299     { "WITH",             "TK_WITH",         CTE                    },
   298    300     { "WITHOUT",          "TK_WITHOUT",      ALWAYS                 },
   299         -  { "WHEN",             "TK_WHEN",         ALWAYS                 },
   300         -  { "WHERE",            "TK_WHERE",        ALWAYS                 },
   301    301   };
   302    302   
   303    303   /* Number of keywords */
   304    304   static int nKeyword = (sizeof(aKeywordTable)/sizeof(aKeywordTable[0]));
   305    305   
   306    306   /* Map all alphabetic characters into lower-case for hashing.  This is
   307    307   ** only valid for alphabetics.  In particular it does not work for '_'