SQLite

      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 ){

  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.

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*}

** 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

/*
** 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 */

  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*);

** 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++){

  }

  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);

# 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

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

  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 } {}

                           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

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

} {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
  #

    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

} {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

  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

             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

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
  14608 57 57   14608 58 58   15241 54 54   15870 53 53   16499 52 52
  17126 49 49   17126 50 50   17126 51 51   17733 44 44   17733 45 45
  17733 46 46   17733 47 47   17733 48 48   18176 42 42   18176 43 43
  18597 40 40   18597 41 41   18996 39 39   19395 37 37   19395 38 38
  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

  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

  { "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 '_'