/ Check-in [521d5186]
Login

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 Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

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







|







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

Changes to src/loadext.c.

451
452
453
454
455
456
457
458
459
460
461



462
463
464
465
466
467
468
  sqlite3_str_errcode,
  sqlite3_str_length,
  sqlite3_str_value,
  /* Version 3.25.0 and later */
  sqlite3_create_window_function,
  /* Version 3.26.0 and later */
#ifdef SQLITE_ENABLE_NORMALIZE
  sqlite3_normalized_sql
#else
  0
#endif



};

/*
** Attempt to load an SQLite extension library contained in the file
** zFile.  The entry point is zProc.  zProc may be 0 in which case a
** default entry point name (sqlite3_extension_init) is used.  Use
** of the default name is recommended.







|

|

>
>
>







451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
  sqlite3_str_errcode,
  sqlite3_str_length,
  sqlite3_str_value,
  /* Version 3.25.0 and later */
  sqlite3_create_window_function,
  /* Version 3.26.0 and later */
#ifdef SQLITE_ENABLE_NORMALIZE
  sqlite3_normalized_sql,
#else
  0,
#endif
  /* Version 3.28.0 and later */
  sqlite3_stmt_isexplain,
  sqlite3_value_frombind
};

/*
** Attempt to load an SQLite extension library contained in the file
** zFile.  The entry point is zProc.  zProc may be 0 in which case a
** default entry point name (sqlite3_extension_init) is used.  Use
** of the default name is recommended.

Changes to src/parse.y.

1690
1691
1692
1693
1694
1695
1696
1697

1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713


1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730

frame_opt(A) ::= .                             { 
  A = sqlite3WindowAlloc(pParse, 0, TK_UNBOUNDED, 0, TK_CURRENT, 0, 0);
}
frame_opt(A) ::= range_or_rows(X) frame_bound_s(Y) frame_exclude_opt(Z). { 
  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0, Z);
}
frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z) frame_exclude_opt(W). { 

  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr, W);
}

range_or_rows(A) ::= RANGE.   { A = TK_RANGE; }
range_or_rows(A) ::= ROWS.    { A = TK_ROWS;  }
range_or_rows(A) ::= GROUPS.  { A = TK_GROUPS;}


frame_bound_s(A) ::= frame_bound(X). { A = X; }
frame_bound_s(A) ::= UNBOUNDED PRECEDING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}
frame_bound_e(A) ::= frame_bound(X). { A = X; }
frame_bound_e(A) ::= UNBOUNDED FOLLOWING. {A.eType = TK_UNBOUNDED; A.pExpr = 0;}

frame_bound(A) ::= expr(X) PRECEDING.   { A.eType = TK_PRECEDING; A.pExpr = X; }
frame_bound(A) ::= CURRENT ROW.         { A.eType = TK_CURRENT  ; A.pExpr = 0; }
frame_bound(A) ::= expr(X) FOLLOWING.   { A.eType = TK_FOLLOWING; A.pExpr = X; }



%type frame_exclude_opt {u8}
frame_exclude_opt(A) ::= . { A = 0; }
frame_exclude_opt(A) ::= EXCLUDE frame_exclude(X). { A = X; }

%type frame_exclude {u8}
frame_exclude(A) ::= NO OTHERS.   { A = 0; }
frame_exclude(A) ::= CURRENT ROW. { A = TK_CURRENT; }
frame_exclude(A) ::= GROUP.       { A = TK_GROUP; }
frame_exclude(A) ::= TIES.        { A = TK_TIES; }


%type window_clause {Window*}
%destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);}
window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; }

%type over_clause {Window*}







|
>



|
<
<

<
|
|
|
|

|
<
<
>
>


|
|


|
|
|
<







1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702


1703

1704
1705
1706
1707
1708
1709


1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720

1721
1722
1723
1724
1725
1726
1727

frame_opt(A) ::= .                             { 
  A = sqlite3WindowAlloc(pParse, 0, TK_UNBOUNDED, 0, TK_CURRENT, 0, 0);
}
frame_opt(A) ::= range_or_rows(X) frame_bound_s(Y) frame_exclude_opt(Z). { 
  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0, Z);
}
frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND
                          frame_bound_e(Z) frame_exclude_opt(W). { 
  A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr, W);
}

range_or_rows(A) ::= RANGE|ROWS|GROUPS(X).   {A = @X; /*A-overwrites-X*/}




frame_bound_s(A) ::= frame_bound(X).         {A = X;}
frame_bound_s(A) ::= UNBOUNDED(X) PRECEDING. {A.eType = @X; A.pExpr = 0;}
frame_bound_e(A) ::= frame_bound(X).         {A = X;}
frame_bound_e(A) ::= UNBOUNDED(X) FOLLOWING. {A.eType = @X; A.pExpr = 0;}

frame_bound(A) ::= expr(X) PRECEDING|FOLLOWING(Y).


                                             {A.eType = @Y; A.pExpr = X;}
frame_bound(A) ::= CURRENT(X) ROW.           {A.eType = @X; A.pExpr = 0;}

%type frame_exclude_opt {u8}
frame_exclude_opt(A) ::= . {A = 0;}
frame_exclude_opt(A) ::= EXCLUDE frame_exclude(X). {A = X;}

%type frame_exclude {u8}
frame_exclude(A) ::= NO(X) OTHERS.   {A = @X; /*A-overwrites-X*/}
frame_exclude(A) ::= CURRENT(X) ROW. {A = @X; /*A-overwrites-X*/}
frame_exclude(A) ::= GROUP|TIES(X).  {A = @X; /*A-overwrites-X*/}



%type window_clause {Window*}
%destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);}
window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; }

%type over_clause {Window*}

Changes to src/sqlite.h.in.

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







|







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

Changes to src/sqliteInt.h.

1577
1578
1579
1580
1581
1582
1583

1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599

/*
** Allowed values for sqlite3.mDbFlags
*/
#define DBFLAG_SchemaChange   0x0001  /* Uncommitted Hash table changes */
#define DBFLAG_PreferBuiltin  0x0002  /* Preference to built-in funcs */
#define DBFLAG_Vacuum         0x0004  /* Currently in a VACUUM */

#define DBFLAG_SchemaKnownOk  0x0008  /* Schema is known to be valid */

/*
** Bits of the sqlite3.dbOptFlags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
** selectively disable various optimizations.
*/
#define SQLITE_QueryFlattener 0x0001   /* Query flattening */
                          /*  0x0002   available for reuse */
#define SQLITE_GroupByOrder   0x0004   /* GROUPBY cover of ORDERBY */
#define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
#define SQLITE_DistinctOpt    0x0010   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0020   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0040   /* ORDER BY of joins via index */
#define SQLITE_Transitive     0x0080   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0100   /* Omit unused tables in joins */







>
|







|







1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600

/*
** Allowed values for sqlite3.mDbFlags
*/
#define DBFLAG_SchemaChange   0x0001  /* Uncommitted Hash table changes */
#define DBFLAG_PreferBuiltin  0x0002  /* Preference to built-in funcs */
#define DBFLAG_Vacuum         0x0004  /* Currently in a VACUUM */
#define DBFLAG_VacuumInto     0x0008  /* Currently running VACUUM INTO */
#define DBFLAG_SchemaKnownOk  0x0010  /* Schema is known to be valid */

/*
** Bits of the sqlite3.dbOptFlags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
** selectively disable various optimizations.
*/
#define SQLITE_QueryFlattener 0x0001   /* Query flattening */
#define SQLITE_WindowFunc     0x0002   /* Use xInverse for window functions */
#define SQLITE_GroupByOrder   0x0004   /* GROUPBY cover of ORDERBY */
#define SQLITE_FactorOutConst 0x0008   /* Constant factoring */
#define SQLITE_DistinctOpt    0x0010   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0020   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0040   /* ORDER BY of joins via index */
#define SQLITE_Transitive     0x0080   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0100   /* Omit unused tables in joins */

Changes to src/vacuum.c.

135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
...
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
...
226
227
228
229
230
231
232

233
234
235
236
237
238
239
  sqlite3ExprDelete(pParse->db, pInto);
  return;
}

/*
** This routine implements the OP_Vacuum opcode of the VDBE.
*/
int sqlite3RunVacuum(
  char **pzErrMsg,        /* Write error message here */
  sqlite3 *db,            /* Database connection */
  int iDb,                /* Which attached DB to vacuum */
  sqlite3_value *pOut     /* Write results here, if not NULL. VACUUM INTO */
){
  int rc = SQLITE_OK;     /* Return code from service routines */
  Btree *pMain;           /* The database being vacuumed */
................................................................................
  int nRes;               /* Bytes of reserved space at the end of each page */
  int nDb;                /* Number of attached databases */
  const char *zDbMain;    /* Schema name of database to vacuum */
  const char *zOut;       /* Name of output file */

  if( !db->autoCommit ){
    sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
    return SQLITE_ERROR;
  }
  if( db->nVdbeActive>1 ){
    sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
    return SQLITE_ERROR;
  }
  saved_openFlags = db->openFlags;
  if( pOut ){
    if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){
      sqlite3SetString(pzErrMsg, db, "non-text filename");
      return SQLITE_ERROR;
    }
................................................................................
    sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp));
    i64 sz = 0;
    if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){
      rc = SQLITE_ERROR;
      sqlite3SetString(pzErrMsg, db, "output file already exists");
      goto end_of_vacuum;
    }

  }
  nRes = sqlite3BtreeGetOptimalReserve(pMain);

  /* A VACUUM cannot change the pagesize of an encrypted database. */
#ifdef SQLITE_HAS_CODEC
  if( db->nextPagesize ){
    extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*);







|







 







|



|







 







>







135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
...
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
...
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
  sqlite3ExprDelete(pParse->db, pInto);
  return;
}

/*
** This routine implements the OP_Vacuum opcode of the VDBE.
*/
SQLITE_NOINLINE int sqlite3RunVacuum(
  char **pzErrMsg,        /* Write error message here */
  sqlite3 *db,            /* Database connection */
  int iDb,                /* Which attached DB to vacuum */
  sqlite3_value *pOut     /* Write results here, if not NULL. VACUUM INTO */
){
  int rc = SQLITE_OK;     /* Return code from service routines */
  Btree *pMain;           /* The database being vacuumed */
................................................................................
  int nRes;               /* Bytes of reserved space at the end of each page */
  int nDb;                /* Number of attached databases */
  const char *zDbMain;    /* Schema name of database to vacuum */
  const char *zOut;       /* Name of output file */

  if( !db->autoCommit ){
    sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
    return SQLITE_ERROR; /* IMP: R-12218-18073 */
  }
  if( db->nVdbeActive>1 ){
    sqlite3SetString(pzErrMsg, db,"cannot VACUUM - SQL statements in progress");
    return SQLITE_ERROR; /* IMP: R-15610-35227 */
  }
  saved_openFlags = db->openFlags;
  if( pOut ){
    if( sqlite3_value_type(pOut)!=SQLITE_TEXT ){
      sqlite3SetString(pzErrMsg, db, "non-text filename");
      return SQLITE_ERROR;
    }
................................................................................
    sqlite3_file *id = sqlite3PagerFile(sqlite3BtreePager(pTemp));
    i64 sz = 0;
    if( id->pMethods!=0 && (sqlite3OsFileSize(id, &sz)!=SQLITE_OK || sz>0) ){
      rc = SQLITE_ERROR;
      sqlite3SetString(pzErrMsg, db, "output file already exists");
      goto end_of_vacuum;
    }
    db->mDbFlags |= DBFLAG_VacuumInto;
  }
  nRes = sqlite3BtreeGetOptimalReserve(pMain);

  /* A VACUUM cannot change the pagesize of an encrypted database. */
#ifdef SQLITE_HAS_CODEC
  if( db->nextPagesize ){
    extern void sqlite3CodecGetKey(sqlite3*, int, void**, int*);

Changes to src/wal.c.

982
983
984
985
986
987
988

989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003

1004
1005
1006
1007
1008
1009
1010
** actually needed.
*/
static void walCleanupHash(Wal *pWal){
  WalHashLoc sLoc;                /* Hash table location */
  int iLimit = 0;                 /* Zero values greater than this */
  int nByte;                      /* Number of bytes to zero in aPgno[] */
  int i;                          /* Used to iterate through aHash[] */


  assert( pWal->writeLock );
  testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE-1 );
  testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE );
  testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE+1 );

  if( pWal->hdr.mxFrame==0 ) return;

  /* Obtain pointers to the hash-table and page-number array containing 
  ** the entry that corresponds to frame pWal->hdr.mxFrame. It is guaranteed
  ** that the page said hash-table and array reside on is already mapped.
  */
  assert( pWal->nWiData>walFramePage(pWal->hdr.mxFrame) );
  assert( pWal->apWiData[walFramePage(pWal->hdr.mxFrame)] );
  walHashGet(pWal, walFramePage(pWal->hdr.mxFrame), &sLoc);


  /* Zero all hash-table entries that correspond to frame numbers greater
  ** than pWal->hdr.mxFrame.
  */
  iLimit = pWal->hdr.mxFrame - sLoc.iZero;
  assert( iLimit>0 );
  for(i=0; i<HASHTABLE_NSLOT; i++){







>










|



|
>







982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
** actually needed.
*/
static void walCleanupHash(Wal *pWal){
  WalHashLoc sLoc;                /* Hash table location */
  int iLimit = 0;                 /* Zero values greater than this */
  int nByte;                      /* Number of bytes to zero in aPgno[] */
  int i;                          /* Used to iterate through aHash[] */
  int rc;                         /* Return code form walHashGet() */

  assert( pWal->writeLock );
  testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE-1 );
  testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE );
  testcase( pWal->hdr.mxFrame==HASHTABLE_NPAGE_ONE+1 );

  if( pWal->hdr.mxFrame==0 ) return;

  /* Obtain pointers to the hash-table and page-number array containing 
  ** the entry that corresponds to frame pWal->hdr.mxFrame. It is guaranteed
  ** that the page said hash-table and array reside on is already mapped.(1)
  */
  assert( pWal->nWiData>walFramePage(pWal->hdr.mxFrame) );
  assert( pWal->apWiData[walFramePage(pWal->hdr.mxFrame)] );
  rc = walHashGet(pWal, walFramePage(pWal->hdr.mxFrame), &sLoc);
  if( NEVER(rc) ) return; /* Defense-in-depth, in case (1) above is wrong */

  /* Zero all hash-table entries that correspond to frame numbers greater
  ** than pWal->hdr.mxFrame.
  */
  iLimit = pWal->hdr.mxFrame - sLoc.iZero;
  assert( iLimit>0 );
  for(i=0; i<HASHTABLE_NSLOT; i++){

Changes to src/window.c.

1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
....
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332

2333
2334
2335
2336
2337



2338
2339
2340
2341
2342
2343
2344
....
2572
2573
2574
2575
2576
2577
2578


2579
2580
2581
2582
2583
2584
2585
....
2593
2594
2595
2596
2597
2598
2599

2600

2601
2602
2603
2604
2605
2606
2607
2608
2609
....
2638
2639
2640
2641
2642
2643
2644

2645

2646
2647
2648
2649
2650
2651
2652
  }

  pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  if( pWin==0 ) goto windowAllocErr;
  pWin->eFrmType = eType;
  pWin->eStart = eStart;
  pWin->eEnd = eEnd;
  if( eExclude==0 && OptimizationDisabled(pParse->db, SQLITE_QueryFlattener) ){
    eExclude = TK_NO;
  }
  pWin->eExclude = eExclude;
  pWin->bImplicitFrame = bImplicitFrame;
  pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
  return pWin;
................................................................................
**       }
**       Insert new row into eph table.
**       if( first row of partition ){
**         Rewind(csrEnd) ; Rewind(csrStart) ; Rewind(csrCurrent)
**         regEnd = <expr2>
**         regStart = <expr1>
**       }else{
**         while( (csrEnd.key + regEnd) <= csrCurrent.key ){
**           AGGSTEP
**         }
**         RETURN_ROW
**         while( (csrStart.key + regStart) < csrCurrent.key ){
**           AGGINVERSE
**         }

**       }
**     }
**     flush:
**       while( (csrEnd.key + regEnd) <= csrCurrent.key ){
**         AGGSTEP



**       }
**       RETURN_ROW
**
**   RANGE BETWEEN <expr1> FOLLOWING AND <expr2> FOLLOWING
**
**     ... loop started by sqlite3WhereBegin() ...
**       if( new partition ){
................................................................................
    sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
    sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
  }

  sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);

  sqlite3VdbeJumpHere(v, addrNe);


  if( regPeer ){
    windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eFrmType==TK_RANGE ){
................................................................................
      }else{
        windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
      }
    }
  }else
  if( pMWin->eEnd==TK_PRECEDING ){

    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);

    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
    windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  }else{
    int addr = 0;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eFrmType==TK_RANGE ){
        int lbl = 0;
        addr = sqlite3VdbeCurrentAddr(v);
................................................................................
    addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
    sqlite3VdbeJumpHere(v, addrGosubFlush);
  }

  addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  VdbeCoverage(v);
  if( pMWin->eEnd==TK_PRECEDING ){

    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);

    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  }else if( pMWin->eStart==TK_FOLLOWING ){
    int addrStart;
    int addrBreak1;
    int addrBreak2;
    int addrBreak3;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);







|







 







|


<



>





>
>
>







 







>
>







 







>

>

|







 







>

>







1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
....
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328

2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
....
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
....
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
....
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
  }

  pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  if( pWin==0 ) goto windowAllocErr;
  pWin->eFrmType = eType;
  pWin->eStart = eStart;
  pWin->eEnd = eEnd;
  if( eExclude==0 && OptimizationDisabled(pParse->db, SQLITE_WindowFunc) ){
    eExclude = TK_NO;
  }
  pWin->eExclude = eExclude;
  pWin->bImplicitFrame = bImplicitFrame;
  pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
  pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
  return pWin;
................................................................................
**       }
**       Insert new row into eph table.
**       if( first row of partition ){
**         Rewind(csrEnd) ; Rewind(csrStart) ; Rewind(csrCurrent)
**         regEnd = <expr2>
**         regStart = <expr1>
**       }else{
**         if( (csrEnd.key + regEnd) <= csrCurrent.key ){
**           AGGSTEP
**         }

**         while( (csrStart.key + regStart) < csrCurrent.key ){
**           AGGINVERSE
**         }
**         RETURN_ROW
**       }
**     }
**     flush:
**       while( (csrEnd.key + regEnd) <= csrCurrent.key ){
**         AGGSTEP
**       }
**       while( (csrStart.key + regStart) < csrCurrent.key ){
**         AGGINVERSE
**       }
**       RETURN_ROW
**
**   RANGE BETWEEN <expr1> FOLLOWING AND <expr2> FOLLOWING
**
**     ... loop started by sqlite3WhereBegin() ...
**       if( new partition ){
................................................................................
    sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
    sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
  }

  sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);

  sqlite3VdbeJumpHere(v, addrNe);

  /* Beginning of the block executed for the second and subsequent rows. */
  if( regPeer ){
    windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eFrmType==TK_RANGE ){
................................................................................
      }else{
        windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
      }
    }
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
    if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
    if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  }else{
    int addr = 0;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eFrmType==TK_RANGE ){
        int lbl = 0;
        addr = sqlite3VdbeCurrentAddr(v);
................................................................................
    addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
    sqlite3VdbeJumpHere(v, addrGosubFlush);
  }

  addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  VdbeCoverage(v);
  if( pMWin->eEnd==TK_PRECEDING ){
    int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
    if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  }else if( pMWin->eStart==TK_FOLLOWING ){
    int addrStart;
    int addrBreak1;
    int addrBreak2;
    int addrBreak3;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);

Changes to test/autoinc.test.

12
13
14
15
16
17
18

19
20
21
22
23
24
25
...
851
852
853
854
855
856
857
858























859
# focus of this script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


# If the library is not compiled with autoincrement support then
# skip all tests in this file.
#
ifcapable {!autoinc} {
  finish_test
  return
................................................................................
  set res [catch {db eval {
    INSERT INTO t1(b) VALUES('two'),('three'),('four');
    INSERT INTO t1(b) VALUES('five');
    PRAGMA integrity_check;
  }} msg]
  lappend res $msg
} {0 ok}
























finish_test







>







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
# focus of this script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix autoinc

# If the library is not compiled with autoincrement support then
# skip all tests in this file.
#
ifcapable {!autoinc} {
  finish_test
  return
................................................................................
  set res [catch {db eval {
    INSERT INTO t1(b) VALUES('two'),('three'),('four');
    INSERT INTO t1(b) VALUES('five');
    PRAGMA integrity_check;
  }} msg]
  lappend res $msg
} {0 ok}

#--------------------------------------------------------------------------
reset_db
do_execsql_test 13.0 {
  CREATE TABLE t1(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
  CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
  CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, j);

  INSERT INTO t1 VALUES(NULL, 1);
  INSERT INTO t2 VALUES(NULL, 2);
  INSERT INTO t3 VALUES(NULL, 3);

  SELECT name FROM sqlite_sequence;
} {t1 t2 t3}

do_execsql_test 13.1 {
  UPDATE sqlite_sequence SET name=NULL WHERE name='t2';
  INSERT INTO t3 VALUES(NULL, 4);
  DELETE FROM t3;
  INSERT INTO t3 VALUES(NULL, 5);
  SELECT * FROM t3;
} {3 5}


finish_test

Changes to test/delete4.test.

178
179
180
181
182
183
184
185





186








































































187
do_execsql_test 6.1 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x INT);
  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);
  SELECT x FROM t2;
} {5}















































































finish_test








>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
do_execsql_test 6.1 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x INT);
  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);
  SELECT x FROM t2;
} {5}

#-------------------------------------------------------------------------
# Test the effect of failing to find a table row based on an index key
# within a DELETE. Either because the db is corrupt, or a trigger on another
# row already deleted the entry, or because a BEFORE trigger on the current
# row has already deleted it.
#
do_execsql_test 7.1.0 {
  CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;
  CREATE INDEX t3a ON t3(a);
  CREATE INDEX t3b ON t3(b);

  INSERT INTO t3 VALUES(1, 1, 1);
  INSERT INTO t3 VALUES(2, 2, 2);
  INSERT INTO t3 VALUES(3, 3, 3);
  INSERT INTO t3 VALUES(4, 4, 1);
}
do_execsql_test 7.1.1 {
  DELETE FROM t3 WHERE a=4 OR b=1;
}
do_execsql_test 7.1.2 {
  SELECT * FROM t3;
} { 2 2 2   3 3 3 }

do_execsql_test 7.2.0 {
  CREATE TABLE t4(a PRIMARY KEY, b) WITHOUT ROWID;
  CREATE INDEX t4i ON t4(b);
  INSERT INTO t4 VALUES(1, 'hello');
  INSERT INTO t4 VALUES(2, 'world');

  CREATE TABLE t5(a PRIMARY KEY, b) WITHOUT ROWID;
  CREATE INDEX t5i ON t5(b);
  INSERT INTO t5 VALUES(1, 'hello');
  INSERT INTO t5 VALUES(3, 'world');

  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET rootpage = (
    SELECT rootpage FROM sqlite_master WHERE name = 't5'
  ) WHERE name = 't4';
}

db close
sqlite3 db test.db
do_execsql_test 7.2.1 {
  DELETE FROM t4 WHERE b='world'
}
reset_db

do_execsql_test 7.3.0 {
  CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
  CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN
    DELETE FROM t3 WHERE id=old.id+3;
  END;
}

do_execsql_test 7.3.1 {
  DELETE FROM t3 WHERE a IN(2, 5, 8);
  SELECT * FROM t3;
} {}

do_execsql_test 7.3.2 {
  DROP TRIGGER t3t;
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
  CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN
    DELETE FROM t3 WHERE id=old.id;
  END;
}

do_execsql_test 7.3.3 {
  DELETE FROM t3 WHERE a IN(2, 5, 8);
  SELECT * FROM t3;
} {}


finish_test

Changes to test/e_vacuum.test.

228
229
230
231
232
233
234
235
236
237

238
239
240
241
242
243
244
245
246
247
248
249
250






































251
252
253


254
255
256
257
258
259
260
  INSERT INTO t4(x) VALUES('z');
  DELETE FROM t4 WHERE x = 'y';
  SELECT rowid, x FROM t4;
} {1 x 3 z}
do_execsql_test e_vacuum-3.1.2 {
  VACUUM;
  SELECT rowid, x FROM t4;
} {1 x 3 z}
# Was: {1 x 2 z}


do_execsql_test e_vacuum-3.1.3 {
  CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
  INSERT INTO t5(x) VALUES('x');
  INSERT INTO t5(x) VALUES('y');
  INSERT INTO t5(x) VALUES('z');
  DELETE FROM t5 WHERE x = 'y';
  SELECT rowid, x FROM t5;
} {1 x 3 z}
do_execsql_test e_vacuum-3.1.4 {
  VACUUM;
  SELECT rowid, x FROM t5;
} {1 x 3 z}







































# EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open
# transaction, or if there are one or more active SQL statements when it
# is run.


#
do_execsql_test  e_vacuum-3.2.1.1 { BEGIN } {}
do_catchsql_test e_vacuum-3.2.1.2 { 
  VACUUM 
} {1 {cannot VACUUM from within a transaction}}
do_execsql_test  e_vacuum-3.2.1.3 { COMMIT } {}
do_execsql_test  e_vacuum-3.2.1.4 { VACUUM } {}







<
|

>













>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
>
>







228
229
230
231
232
233
234

235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289


290
291
292
293
294
295
296
297
298
  INSERT INTO t4(x) VALUES('z');
  DELETE FROM t4 WHERE x = 'y';
  SELECT rowid, x FROM t4;
} {1 x 3 z}
do_execsql_test e_vacuum-3.1.2 {
  VACUUM;
  SELECT rowid, x FROM t4;

} {1 x 2 z}

# Rowids are preserved if an INTEGER PRIMARY KEY is used
do_execsql_test e_vacuum-3.1.3 {
  CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
  INSERT INTO t5(x) VALUES('x');
  INSERT INTO t5(x) VALUES('y');
  INSERT INTO t5(x) VALUES('z');
  DELETE FROM t5 WHERE x = 'y';
  SELECT rowid, x FROM t5;
} {1 x 3 z}
do_execsql_test e_vacuum-3.1.4 {
  VACUUM;
  SELECT rowid, x FROM t5;
} {1 x 3 z}

# Rowid is preserved for VACUUM INTO
do_execsql_test e_vacuum-3.1.5 {
  DROP TABLE t5;
  CREATE TABLE t5(x);
  INSERT INTO t5(x) VALUES('x');
  INSERT INTO t5(x) VALUES('y');
  INSERT INTO t5(x) VALUES('z');
  DELETE FROM t5 WHERE x = 'y';
  SELECT rowid, x FROM t5;
} {1 x 3 z}
forcedelete test2.db
do_execsql_test e_vacuum-3.1.6 {
  VACUUM INTO 'test2.db';
  ATTACH 'test2.db' AS aux1;
  SELECT rowid, x FROM aux1.t5;
  DETACH aux1;
} {1 x 3 z}

# Rowids are not renumbered if the table being vacuumed
# has indexes.
do_execsql_test e_vacuum-3.1.7 {
  DROP TABLE t5;
  CREATE TABLE t5(x,y,z);
  INSERT INTO t5(x) VALUES('x');
  INSERT INTO t5(x) VALUES('y');
  INSERT INTO t5(x) VALUES('z');
  UPDATE t5 SET y=x, z=random();
  DELETE FROM t5 WHERE x = 'y';
  CREATE INDEX t5x ON t5(x);
  CREATE UNIQUE INDEX t5y ON t5(y);
  CREATE INDEX t5zxy ON t5(z,x,y);
  SELECT rowid, x FROM t5;
} {1 x 3 z}
do_execsql_test e_vacuum-3.1.8 {
  VACUUM;
  SELECT rowid, x FROM t5;
} {1 x 3 z}

# EVIDENCE-OF: R-12218-18073 A VACUUM will fail if there is an open


# transaction on the database connection that is attempting to run the
# VACUUM.
#
do_execsql_test  e_vacuum-3.2.1.1 { BEGIN } {}
do_catchsql_test e_vacuum-3.2.1.2 { 
  VACUUM 
} {1 {cannot VACUUM from within a transaction}}
do_execsql_test  e_vacuum-3.2.1.3 { COMMIT } {}
do_execsql_test  e_vacuum-3.2.1.4 { VACUUM } {}

Changes to test/in.test.

709
710
711
712
713
714
715





















716
717
718
                           WHERE name='Bob'
                         ) AS 't'
                   WHERE x=1
                 )
             AND t6.id IN (1,id)
         );
} {1 Alice}























finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
                           WHERE name='Bob'
                         ) AS 't'
                   WHERE x=1
                 )
             AND t6.id IN (1,id)
         );
} {1 Alice}

#-------------------------------------------------------------------------
reset_db
do_execsql_test in-16.0 {
  CREATE TABLE x1(a, b);
  INSERT INTO x1(a) VALUES(1), (2), (3), (4), (5), (6);
  CREATE INDEX x1i ON x1(a, b);
}

do_execsql_test in-16.1 {
  SELECT * FROM x1 
  WHERE a IN (SELECT a FROM x1 WHERE (a%2)==0) 
  ORDER BY a DESC, b;
} {6 {} 4 {} 2 {}}

do_execsql_test in-16.2 {
  SELECT * FROM x1 
  WHERE a IN (SELECT a FROM x1 WHERE (a%7)==0) 
  ORDER BY a DESC, b;
} {}



finish_test

Changes to test/insert4.test.

594
595
596
597
598
599
600


601


















602
do_test 10.3 {
  execsql { PRAGMA integrity_check }
  set sqlite3_xferopt_count 0
  execsql { INSERT INTO x     SELECT * FROM t8 }
  set sqlite3_xferopt_count
} {1}






















finish_test







>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
do_test 10.3 {
  execsql { PRAGMA integrity_check }
  set sqlite3_xferopt_count 0
  execsql { INSERT INTO x     SELECT * FROM t8 }
  set sqlite3_xferopt_count
} {1}

#-------------------------------------------------------------------------
# xfer transfer between tables where the source has an empty partial index.
#
do_execsql_test 11.0 {
  CREATE TABLE t9(a, b, c);
  CREATE INDEX t9a ON t9(a);
  CREATE INDEX t9b ON t9(b) WHERE c=0;

  INSERT INTO t9 VALUES(1, 1, 1);
  INSERT INTO t9 VALUES(2, 2, 2);
  INSERT INTO t9 VALUES(3, 3, 3);

  CREATE TABLE t10(a, b, c);
  CREATE INDEX t10a ON t10(a);
  CREATE INDEX t10b ON t10(b) WHERE c=0;

  INSERT INTO t10 SELECT * FROM t9;
  SELECT * FROM t10;
  PRAGMA integrity_check;
} {1 1 1  2 2 2  3 3 3  ok}

finish_test

Changes to test/pragma.test.

247
248
249
250
251
252
253

























254
255
256
257
258
259
260
} {0}
do_test pragma-1.14.4 {
  execsql {
    PRAGMA synchronous=10;
    PRAGMA synchronous;
  }
} {2}

























} ;# ifcapable pager_pragmas

# Test turning "flag" pragmas on and off.
#
ifcapable debug {
  # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
  #







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
} {0}
do_test pragma-1.14.4 {
  execsql {
    PRAGMA synchronous=10;
    PRAGMA synchronous;
  }
} {2}

do_execsql_test 1.15.1 {
  PRAGMA default_cache_size = 0;
}
do_execsql_test 1.15.2 {
  PRAGMA default_cache_size;
} $DFLT_CACHE_SZ
do_execsql_test 1.15.3 {
  PRAGMA default_cache_size = -500;
}
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 500
do_execsql_test 1.15.3 {
  PRAGMA default_cache_size = 500;
}
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 500
db close
hexio_write test.db 48 FFFFFF00
sqlite3 db test.db
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 256
} ;# ifcapable pager_pragmas

# Test turning "flag" pragmas on and off.
#
ifcapable debug {
  # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
  #

Changes to test/skipscan2.test.

196
197
198
199
200
201
202

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



finish_test







>



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



finish_test

Changes to test/triggerC.test.

1052
1053
1054
1055
1056
1057
1058














1059
1060
1061
} {1 {1st ORDER BY term does not match any column in the result set}}

do_catchsql_test 16.2 {
  SELECT count(*) FROM sqlite_master 
  GROUP BY raise(IGNORE) 
  HAVING raise(ABORT, 'msg');
} {1 {RAISE() may only be used within a trigger-program}}















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>



1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
} {1 {1st ORDER BY term does not match any column in the result set}}

do_catchsql_test 16.2 {
  SELECT count(*) FROM sqlite_master 
  GROUP BY raise(IGNORE) 
  HAVING raise(ABORT, 'msg');
} {1 {RAISE() may only be used within a trigger-program}}

#-------------------------------------------------------------------------
# Datatype mismatch on IPK when there are BEFORE triggers.
#
do_execsql_test 17.0 {
  CREATE TABLE xyz(x INTEGER PRIMARY KEY, y, z);
  CREATE TRIGGER xyz_tr BEFORE INSERT ON xyz BEGIN
    SELECT new.x;
  END;
}
do_catchsql_test 17.1 {
  INSERT INTO xyz VALUES('hello', 2, 3);
} {1 {datatype mismatch}}


finish_test

Changes to test/window1.test.

1077
1078
1079
1080
1081
1082
1083

1084




















































































1085
1086
1087
  SELECT min(x) FROM t1;
} {1}
do_execsql_test 27.2 {
  SELECT min(x) OVER win FROM t1
  WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
} {1 1 1 2 3 4}























































































finish_test









>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
  SELECT min(x) FROM t1;
} {1}
do_execsql_test 27.2 {
  SELECT min(x) OVER win FROM t1
  WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
} {1 1 1 2 3 4}

#-------------------------------------------------------------------------

reset_db
do_execsql_test 28.1.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
  INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0);
  INSERT INTO t1 VALUES (13,'M', 'cc', NULL);
}

do_execsql_test 28.1.2 {
  SELECT group_concat(b,'') OVER w1 FROM t1
    WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING)
} {
  {} {}
}

do_execsql_test 28.2.1 {
  CREATE TABLE t2(a TEXT, b INTEGER);
  INSERT INTO t2 VALUES('A', NULL);
  INSERT INTO t2 VALUES('B', NULL);
}

do_execsql_test 28.2.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
  INSERT INTO t1 VALUES
    (10,'J', 'cc', NULL),
    (11,'K', 'cc', 'xyz'),
    (13,'M', 'cc', NULL);
}

do_execsql_test 28.2.2 {
  SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
    WINDOW w1 AS
    (ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
    ORDER BY c, d, a;
} {
  10 J cc NULL JM |
  13 M cc NULL JM | 
  11 K cc 'xyz' K |
}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 29.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
  INSERT INTO t1 VALUES
    (1, 'A', 'aa', 2.5),
    (2, 'B', 'bb', 3.75),
    (3, 'C', 'cc', 1.0),
    (4, 'D', 'cc', 8.25),
    (5, 'E', 'bb', 6.5),
    (6, 'F', 'aa', 6.5),
    (7, 'G', 'aa', 6.0),
    (8, 'H', 'bb', 9.0),
    (9, 'I', 'aa', 3.75),
    (10,'J', 'cc', NULL),
    (11,'K', 'cc', 'xyz'),
    (12,'L', 'cc', 'xyZ'),
    (13,'M', 'cc', NULL);
}

do_execsql_test 29.2 {
  SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
    WINDOW w1 AS
    (PARTITION BY c ORDER BY d DESC
     RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
    ORDER BY c, d, a;
} {
  1 A aa 2.5 FG | 
  9 I aa 3.75 F | 
  7 G aa 6 {} | 
  6 F aa 6.5 {} | 
  2 B bb 3.75 HE |
  5 E bb 6.5 H | 
  8 H bb 9 {} | 
  10 J cc NULL JM | 
  13 M cc NULL JM | 
  3 C cc 1 {} | 
  4 D cc 8.25 {} | 
  12 L cc 'xyZ' L | 
  11 K cc 'xyz' K |
}

finish_test


Changes to test/window8.tcl.

264
265
266
267
268
269
270

























271
272
273
274
             dense_rank() OVER win
      FROM t3
      WINDOW win AS ( $frame $ex )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
    "
  }
}


























finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
             dense_rank() OVER win
      FROM t3
      WINDOW win AS ( $frame $ex )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
    "
  }
}

==========

execsql_test 6.0 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a TEXT, b INTEGER);
  INSERT INTO t2 VALUES('A', NULL);
  INSERT INTO t2 VALUES('B', NULL);
  INSERT INTO t2 VALUES('C', 1);
}

execsql_test 6.1 {
  SELECT string_agg(a, '.') OVER (
    ORDER BY b NULLS FIRST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
  )
  FROM t2
}

execsql_test 6.2 {
  SELECT string_agg(a, '.') OVER (
    ORDER BY b DESC NULLS LAST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
  )
  FROM t2
}


finish_test


Changes to test/window8.test.

3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
3847
3848
3849
3850
3851
....
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
....
4162
4163
4164
4165
4166
4167
4168
4169
4170
4171
4172
4173
4174
4175
4176
....
4186
4187
4188
4189
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200
....
4477
4478
4479
4480
4481
4482
4483
4484
4485
4486
4487
4488
4489
4490
4491
....
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
....
4800
4801
4802
4803
4804
4805
4806
4807
4808
4809
4810
4811
4812
4813
4814
....
4824
4825
4826
4827
4828
4829
4830
4831
4832
4833
4834
4835
4836
4837
4838
....
4843
4844
4845
4846
4847
4848
4849
4850
























4851

do_execsql_test 5.1.7.1 {
  SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2 , 3
} {979 102 83   979 102 83   979 102 83   979 102 83   979 102 83   979 102 83
  979 102 83   979 113 81   979 113 82   979 133 80   979 148 79   979 158 77
  979 158 78   979 160 77   979 208 76   979 223 75   979 224 74   979 234 73
  979 238 72   979 239 71   979 247 70   979 250 69   979 252 68   979 256 67
  979 257 66   979 295 65   979 309 64   979 330 63   979 335 62   979 336 61
  979 346 60   979 354 59   979 355 58   979 355 58   979 393 56   979 393 57
................................................................................

do_execsql_test 5.1.7.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2 , 3
} {3830 89 89   4741 88 88   5640 84 84   5640 85 85   5640 86 86   5640 87 87
  6485 81 81   6485 82 82   6485 83 83   7324 80 80   8163 78 78   8163 79 79
  8968 73 73   8968 74 74   8968 75 75   8968 76 76   8968 77 77   9745 69 69
  9745 70 70   9745 71 71   9745 72 72   10504 65 65   10504 66 66
  10504 67 67   10504 68 68   11215 64 64   11920 63 63   12603 62 62
  13274 60 60   13274 61 61   13941 59 59   14608 55 55   14608 56 56
................................................................................

do_execsql_test 5.2.7.1 {
  SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2 , 3
} {963 929 6   979 102 82   979 102 82   979 102 82   979 102 82   979 102 82
  979 102 83   979 113 80   979 113 81   979 113 82   979 133 79   979 148 78
  979 158 76   979 158 77   979 160 76   979 208 75   979 223 74   979 224 73
  979 234 72   979 238 71   979 239 70   979 247 69   979 250 68   979 252 67
  979 256 66   979 257 65   979 295 64   979 309 64   979 330 62   979 335 61
  979 336 60   979 346 59   979 354 59   979 355 57   979 355 57   979 393 55
................................................................................

do_execsql_test 5.2.7.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2 , 3
} {2851 89 89   3778 88 88   4681 87 87   5556 83 83   5574 82 82   5586 81 81
  5640 84 84   5640 85 85   5640 86 86   7324 80 80   8123 77 77   8129 73 73
  8129 74 74   8163 78 78   8163 79 79   8940 71 71   8968 75 75   8968 76 76
  9727 66 66   9745 69 69   9745 70 70   9745 72 72   10504 65 65
  10504 67 67   10504 68 68   11215 64 64   11844 62 62   11920 63 63
  13274 60 60   13274 61 61   13897 58 58   13903 57 57   13925 56 56
................................................................................

do_execsql_test 5.3.7.1 {
  SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP  )
      ORDER BY 1 , 2 , 3
} {963 929 6   979 102 82   979 102 82   979 102 82   979 102 82   979 102 82
  979 102 83   979 113 80   979 113 81   979 113 82   979 133 79   979 148 78
  979 158 76   979 158 77   979 160 76   979 208 75   979 223 74   979 224 73
  979 234 72   979 238 71   979 239 70   979 247 69   979 250 68   979 252 67
  979 256 66   979 257 65   979 295 64   979 309 64   979 330 62   979 335 61
  979 336 60   979 346 59   979 354 59   979 355 57   979 355 57   979 393 55
................................................................................

do_execsql_test 5.3.7.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP  )
      ORDER BY 1 , 2 , 3
} {2851 89 89   3778 88 88   4681 87 87   5556 83 83   5574 82 82   5586 81 81
  5640 84 84   5640 85 85   5640 86 86   7324 80 80   8123 77 77   8129 73 73
  8129 74 74   8163 78 78   8163 79 79   8940 71 71   8968 75 75   8968 76 76
  9727 66 66   9745 69 69   9745 70 70   9745 72 72   10504 65 65
  10504 67 67   10504 68 68   11215 64 64   11844 62 62   11920 63 63
  13274 60 60   13274 61 61   13897 58 58   13903 57 57   13925 56 56
................................................................................

do_execsql_test 5.4.7.1 {
  SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES  )
      ORDER BY 1 , 2 , 3
} {979 102 83   979 102 83   979 102 83   979 102 83   979 102 83   979 102 83
  979 102 83   979 113 81   979 113 82   979 133 80   979 148 79   979 158 77
  979 158 78   979 160 77   979 208 76   979 223 75   979 224 74   979 234 73
  979 238 72   979 239 71   979 247 70   979 250 69   979 252 68   979 256 67
  979 257 66   979 295 65   979 309 64   979 330 63   979 335 62   979 336 61
  979 346 60   979 354 59   979 355 58   979 355 58   979 393 56   979 393 57
................................................................................

do_execsql_test 5.4.7.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES  )
      ORDER BY 1 , 2 , 3
} {3830 89 89   4741 88 88   5640 84 84   5640 85 85   5640 86 86   5640 87 87
  6485 81 81   6485 82 82   6485 83 83   7324 80 80   8163 78 78   8163 79 79
  8968 73 73   8968 74 74   8968 75 75   8968 76 76   8968 77 77   9745 69 69
  9745 70 70   9745 71 71   9745 72 72   10504 65 65   10504 66 66
  10504 67 67   10504 68 68   11215 64 64   11920 63 63   12603 62 62
  13274 60 60   13274 61 61   13941 59 59   14608 55 55   14608 56 56
................................................................................
  19788 36 36   20181 35 35   20536 34 34   20891 30 30   20891 31 31
  20891 32 32   20891 33 33   21226 28 28   21226 29 29   21535 27 27
  21830 26 26   22087 22 22   22087 23 23   22087 24 24   22087 25 25
  22334 21 21   22573 17 17   22573 18 18   22573 19 19   22573 20 20
  22796 11 11   22796 12 12   22796 13 13   22796 14 14   22796 15 15
  22796 16 16   22929 10 10   23042 9 9   23155 1 1   23155 2 2   23155 3 3
  23155 4 4   23155 5 5   23155 6 6   23155 7 7   23155 8 8}

























finish_test







|







 







|







 







|







 







|







 







|







 







|







 







|







 







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
3847
3848
3849
3850
3851
....
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
....
4162
4163
4164
4165
4166
4167
4168
4169
4170
4171
4172
4173
4174
4175
4176
....
4186
4187
4188
4189
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200
....
4477
4478
4479
4480
4481
4482
4483
4484
4485
4486
4487
4488
4489
4490
4491
....
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
....
4800
4801
4802
4803
4804
4805
4806
4807
4808
4809
4810
4811
4812
4813
4814
....
4824
4825
4826
4827
4828
4829
4830
4831
4832
4833
4834
4835
4836
4837
4838
....
4843
4844
4845
4846
4847
4848
4849
4850
4851
4852
4853
4854
4855
4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866
4867
4868
4869
4870
4871
4872
4873
4874
4875

do_execsql_test 5.1.7.1 {
  SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2 , 3
} {979 102 83   979 102 83   979 102 83   979 102 83   979 102 83   979 102 83
  979 102 83   979 113 81   979 113 82   979 133 80   979 148 79   979 158 77
  979 158 78   979 160 77   979 208 76   979 223 75   979 224 74   979 234 73
  979 238 72   979 239 71   979 247 70   979 250 69   979 252 68   979 256 67
  979 257 66   979 295 65   979 309 64   979 330 63   979 335 62   979 336 61
  979 346 60   979 354 59   979 355 58   979 355 58   979 393 56   979 393 57
................................................................................

do_execsql_test 5.1.7.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2 , 3
} {3830 89 89   4741 88 88   5640 84 84   5640 85 85   5640 86 86   5640 87 87
  6485 81 81   6485 82 82   6485 83 83   7324 80 80   8163 78 78   8163 79 79
  8968 73 73   8968 74 74   8968 75 75   8968 76 76   8968 77 77   9745 69 69
  9745 70 70   9745 71 71   9745 72 72   10504 65 65   10504 66 66
  10504 67 67   10504 68 68   11215 64 64   11920 63 63   12603 62 62
  13274 60 60   13274 61 61   13941 59 59   14608 55 55   14608 56 56
................................................................................

do_execsql_test 5.2.7.1 {
  SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2 , 3
} {963 929 6   979 102 82   979 102 82   979 102 82   979 102 82   979 102 82
  979 102 83   979 113 80   979 113 81   979 113 82   979 133 79   979 148 78
  979 158 76   979 158 77   979 160 76   979 208 75   979 223 74   979 224 73
  979 234 72   979 238 71   979 239 70   979 247 69   979 250 68   979 252 67
  979 256 66   979 257 65   979 295 64   979 309 64   979 330 62   979 335 61
  979 336 60   979 346 59   979 354 59   979 355 57   979 355 57   979 393 55
................................................................................

do_execsql_test 5.2.7.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2 , 3
} {2851 89 89   3778 88 88   4681 87 87   5556 83 83   5574 82 82   5586 81 81
  5640 84 84   5640 85 85   5640 86 86   7324 80 80   8123 77 77   8129 73 73
  8129 74 74   8163 78 78   8163 79 79   8940 71 71   8968 75 75   8968 76 76
  9727 66 66   9745 69 69   9745 70 70   9745 72 72   10504 65 65
  10504 67 67   10504 68 68   11215 64 64   11844 62 62   11920 63 63
  13274 60 60   13274 61 61   13897 58 58   13903 57 57   13925 56 56
................................................................................

do_execsql_test 5.3.7.1 {
  SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2 , 3
} {963 929 6   979 102 82   979 102 82   979 102 82   979 102 82   979 102 82
  979 102 83   979 113 80   979 113 81   979 113 82   979 133 79   979 148 78
  979 158 76   979 158 77   979 160 76   979 208 75   979 223 74   979 224 73
  979 234 72   979 238 71   979 239 70   979 247 69   979 250 68   979 252 67
  979 256 66   979 257 65   979 295 64   979 309 64   979 330 62   979 335 61
  979 336 60   979 346 59   979 354 59   979 355 57   979 355 57   979 393 55
................................................................................

do_execsql_test 5.3.7.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2 , 3
} {2851 89 89   3778 88 88   4681 87 87   5556 83 83   5574 82 82   5586 81 81
  5640 84 84   5640 85 85   5640 86 86   7324 80 80   8123 77 77   8129 73 73
  8129 74 74   8163 78 78   8163 79 79   8940 71 71   8968 75 75   8968 76 76
  9727 66 66   9745 69 69   9745 70 70   9745 72 72   10504 65 65
  10504 67 67   10504 68 68   11215 64 64   11844 62 62   11920 63 63
  13274 60 60   13274 61 61   13897 58 58   13903 57 57   13925 56 56
................................................................................

do_execsql_test 5.4.7.1 {
  SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2 , 3
} {979 102 83   979 102 83   979 102 83   979 102 83   979 102 83   979 102 83
  979 102 83   979 113 81   979 113 82   979 133 80   979 148 79   979 158 77
  979 158 78   979 160 77   979 208 76   979 223 75   979 224 74   979 234 73
  979 238 72   979 239 71   979 247 70   979 250 69   979 252 68   979 256 67
  979 257 66   979 295 65   979 309 64   979 330 63   979 335 62   979 336 61
  979 346 60   979 354 59   979 355 58   979 355 58   979 393 56   979 393 57
................................................................................

do_execsql_test 5.4.7.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c , b , a 
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2 , 3
} {3830 89 89   4741 88 88   5640 84 84   5640 85 85   5640 86 86   5640 87 87
  6485 81 81   6485 82 82   6485 83 83   7324 80 80   8163 78 78   8163 79 79
  8968 73 73   8968 74 74   8968 75 75   8968 76 76   8968 77 77   9745 69 69
  9745 70 70   9745 71 71   9745 72 72   10504 65 65   10504 66 66
  10504 67 67   10504 68 68   11215 64 64   11920 63 63   12603 62 62
  13274 60 60   13274 61 61   13941 59 59   14608 55 55   14608 56 56
................................................................................
  19788 36 36   20181 35 35   20536 34 34   20891 30 30   20891 31 31
  20891 32 32   20891 33 33   21226 28 28   21226 29 29   21535 27 27
  21830 26 26   22087 22 22   22087 23 23   22087 24 24   22087 25 25
  22334 21 21   22573 17 17   22573 18 18   22573 19 19   22573 20 20
  22796 11 11   22796 12 12   22796 13 13   22796 14 14   22796 15 15
  22796 16 16   22929 10 10   23042 9 9   23155 1 1   23155 2 2   23155 3 3
  23155 4 4   23155 5 5   23155 6 6   23155 7 7   23155 8 8}

#==========================================================================

do_execsql_test 6.0 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(a TEXT, b INTEGER);
  INSERT INTO t2 VALUES('A', NULL);
  INSERT INTO t2 VALUES('B', NULL);
  INSERT INTO t2 VALUES('C', 1);
} {}

do_execsql_test 6.1 {
  SELECT group_concat(a, '.') OVER (
    ORDER BY b  RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
  )
  FROM t2
} {A.B   A.B   {}}

do_execsql_test 6.2 {
  SELECT group_concat(a, '.') OVER (
    ORDER BY b DESC  RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
  )
  FROM t2
} {{}   A.B   A.B}

finish_test

Changes to test/without_rowid1.test.

350
351
352
353
354
355
356
357


358


































359
  CREATE UNIQUE INDEX t2b ON t2(b);
  UPDATE t2 SET b=1 WHERE b='';
}

do_execsql_test 10.1 {
  DELETE FROM t2 WHERE b=1
}



  


































finish_test








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
  CREATE UNIQUE INDEX t2b ON t2(b);
  UPDATE t2 SET b=1 WHERE b='';
}

do_execsql_test 10.1 {
  DELETE FROM t2 WHERE b=1
}

#-------------------------------------------------------------------------
# UNIQUE constraint violation in an UPDATE with a multi-column PK.
#
reset_db
do_execsql_test 10.0 {
  CREATE TABLE t1(a, b, c UNIQUE, PRIMARY KEY(a, b)) WITHOUT ROWID;
  INSERT INTO t1 VALUES('a', 'a', 1);
  INSERT INTO t1 VALUES('a', 'b', 2);
  INSERT INTO t1 VALUES('b', 'a', 3);
  INSERT INTO t1 VALUES('b', 'b', 4);
}

do_catchsql_test 10.1 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'a');
} {0 {}}
do_catchsql_test 10.2 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'b');
} {1 {UNIQUE constraint failed: t1.c}}
do_catchsql_test 10.3 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'a');
} {1 {UNIQUE constraint failed: t1.c}}
do_catchsql_test 10.4 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'b');
} {1 {UNIQUE constraint failed: t1.c}}
do_catchsql_test 10.5 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('c', 'c');
} {0 {}}

do_execsql_test 10.6 {
  CREATE TRIGGER t1_tr BEFORE UPDATE ON t1 BEGIN
    DELETE FROM t1 WHERE a = new.a;
  END;
  UPDATE t1 SET c = c+1 WHERE a = 'a';
  SELECT * FROM t1;
} {b a 3  b b 4}

  
finish_test

Changes to tool/mkkeywordhash.c.

289
290
291
292
293
294
295


296
297
298
299
300
301
302
303
304
305
306
307
  { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
  { "UPDATE",           "TK_UPDATE",       ALWAYS                 },
  { "USING",            "TK_USING",        ALWAYS                 },
  { "VACUUM",           "TK_VACUUM",       VACUUM                 },
  { "VALUES",           "TK_VALUES",       ALWAYS                 },
  { "VIEW",             "TK_VIEW",         VIEW                   },
  { "VIRTUAL",          "TK_VIRTUAL",      VTAB                   },


  { "WINDOW",           "TK_WINDOW",       WINDOWFUNC             },
  { "WITH",             "TK_WITH",         CTE                    },
  { "WITHOUT",          "TK_WITHOUT",      ALWAYS                 },
  { "WHEN",             "TK_WHEN",         ALWAYS                 },
  { "WHERE",            "TK_WHERE",        ALWAYS                 },
};

/* Number of keywords */
static int nKeyword = (sizeof(aKeywordTable)/sizeof(aKeywordTable[0]));

/* Map all alphabetic characters into lower-case for hashing.  This is
** only valid for alphabetics.  In particular it does not work for '_'







>
>



<
<







289
290
291
292
293
294
295
296
297
298
299
300


301
302
303
304
305
306
307
  { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
  { "UPDATE",           "TK_UPDATE",       ALWAYS                 },
  { "USING",            "TK_USING",        ALWAYS                 },
  { "VACUUM",           "TK_VACUUM",       VACUUM                 },
  { "VALUES",           "TK_VALUES",       ALWAYS                 },
  { "VIEW",             "TK_VIEW",         VIEW                   },
  { "VIRTUAL",          "TK_VIRTUAL",      VTAB                   },
  { "WHEN",             "TK_WHEN",         ALWAYS                 },
  { "WHERE",            "TK_WHERE",        ALWAYS                 },
  { "WINDOW",           "TK_WINDOW",       WINDOWFUNC             },
  { "WITH",             "TK_WITH",         CTE                    },
  { "WITHOUT",          "TK_WITHOUT",      ALWAYS                 },


};

/* Number of keywords */
static int nKeyword = (sizeof(aKeywordTable)/sizeof(aKeywordTable[0]));

/* Map all alphabetic characters into lower-case for hashing.  This is
** only valid for alphabetics.  In particular it does not work for '_'