catch { S delete }

  if {$rc} {
    error $changeset
  }
  return $changeset
}


















proc do_then_apply_sql {sql {dbname main}} {
  proc xConflict args { return "OMIT" }
  set rc [catch {
    sqlite3session S db $dbname
    db eval "SELECT name FROM $dbname.sqlite_master WHERE type = 'table'" {
      S attach $name

  catch { S delete }

  if {$rc} {
    error $changeset
  }
  return $changeset
}

proc patchset_from_sql {sql {dbname main}} {
  set rc [catch {
    sqlite3session S db $dbname
    db eval "SELECT name FROM $dbname.sqlite_master WHERE type = 'table'" {
      S attach $name
    }
    db eval $sql
    S patchset
  } patchset]
  catch { S delete }

  if {$rc} {
    error $patchset
  }
  return $patchset
}

proc do_then_apply_sql {sql {dbname main}} {
  proc xConflict args { return "OMIT" }
  set rc [catch {
    sqlite3session S db $dbname
    db eval "SELECT name FROM $dbname.sqlite_master WHERE type = 'table'" {
      S attach $name

# 2018 October 18
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
} 
source [file join [file dirname [info script]] session_common.tcl]
source $testdir/tester.tcl
ifcapable !session {finish_test; return}

set testprefix sessioninvert

proc iter_invert {C} {
  set x [list]
  sqlite3session_foreach -invert c $C { lappend x $c }
  set x
}

proc do_invert_test {tn sql {iter {}}} {

  forcecopy test.db test.db2
  sqlite3 db2 test.db2

  set C [changeset_from_sql $sql]

  forcecopy test.db test.db3
  sqlite3 db3 test.db3
  uplevel [list do_test $tn.1 [list compare_db db db3] {}]

  set I [sqlite3changeset_invert $C]
  sqlite3changeset_apply db $I {}
  uplevel [list do_test $tn.2 [list compare_db db db2] {}]
  
  sqlite3changeset_apply_v2 -invert db3 $C {}
  uplevel [list do_test $tn.3 [list compare_db db db3] {}]

  if {$iter!=""} {
    uplevel [list do_test $tn.4 [list iter_invert $C] [list {*}$iter]]
  }

  catch { db2 close }
  catch { db3 close }
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a PRIMARY KEY, b, c);
  CREATE TABLE t2(d, e, f, PRIMARY KEY(e, f));

  INSERT INTO t1 VALUES(1, 'one', 'i');
  INSERT INTO t1 VALUES(2, 'two', 'ii');
  INSERT INTO t1 VALUES(3, 'three', 'iii');
  INSERT INTO t1 VALUES(4, 'four', 'iv');
  INSERT INTO t1 VALUES(5, 'five', 'v');
  INSERT INTO t1 VALUES(6, 'six', 'vi');

  INSERT INTO t2 SELECT * FROM t1;
}

do_invert_test 1.1 {
  INSERT INTO t1 VALUES(7, 'seven', 'vii');
} {
  {DELETE t1 0 X.. {i 7 t seven t vii} {}}
}

do_invert_test 1.2 {
  DELETE FROM t1 WHERE a<4;
} {
  {INSERT t1 0 X.. {} {i 1 t one t i}}
  {INSERT t1 0 X.. {} {i 2 t two t ii}}
  {INSERT t1 0 X.. {} {i 3 t three t iii}}
}

do_invert_test 1.3 {
  UPDATE t1 SET c=5;
} {
  {UPDATE t1 0 X.. {i 1 {} {} i 5} {{} {} {} {} t i}}
  {UPDATE t1 0 X.. {i 2 {} {} i 5} {{} {} {} {} t ii}}
  {UPDATE t1 0 X.. {i 3 {} {} i 5} {{} {} {} {} t iii}}
  {UPDATE t1 0 X.. {i 4 {} {} i 5} {{} {} {} {} t iv}}
  {UPDATE t1 0 X.. {i 5 {} {} i 5} {{} {} {} {} t v}}
  {UPDATE t1 0 X.. {i 6 {} {} i 5} {{} {} {} {} t vi}}
}

do_invert_test 1.4 {
  UPDATE t1 SET b = a+1 WHERE a%2;
  DELETE FROM t2;
  INSERT INTO t1 VALUES(10, 'ten', NULL);
}

do_invert_test 1.5 {
  UPDATE t2 SET d = d-1;
} {
  {UPDATE t2 0 .XX {i 2 t three t iii} {i 3 {} {} {} {}}}
  {UPDATE t2 0 .XX {i 1 t two t ii} {i 2 {} {} {} {}}}
  {UPDATE t2 0 .XX {i 5 t six t vi} {i 6 {} {} {} {}}}
  {UPDATE t2 0 .XX {i 3 t four t iv} {i 4 {} {} {} {}}}
  {UPDATE t2 0 .XX {i 0 t one t i} {i 1 {} {} {} {}}}
  {UPDATE t2 0 .XX {i 4 t five t v} {i 5 {} {} {} {}}}
}

do_execsql_test 2.0 { 
  ANALYZE;
  PRAGMA writable_schema = 1;
  DROP TABLE IF EXISTS sqlite_stat4;
  SELECT * FROM sqlite_stat1;
} {
  t2 sqlite_autoindex_t2_1 {6 1 1} 
  t1 sqlite_autoindex_t1_1 {6 1}
}

do_invert_test 2.1 {
  INSERT INTO sqlite_stat1 VALUES('t3', 'idx2', '1 2 3');
} {
  {DELETE sqlite_stat1 0 XX. {t t3 t idx2 t {1 2 3}} {}}
}

do_invert_test 2.2 {
  DELETE FROM sqlite_stat1;
} {
  {INSERT sqlite_stat1 0 XX. {} {t t1 t sqlite_autoindex_t1_1 t {6 1}}}
  {INSERT sqlite_stat1 0 XX. {} {t t2 t sqlite_autoindex_t2_1 t {6 1 1}}}
}

do_invert_test 2.3 {
  UPDATE sqlite_stat1 SET stat = 'hello world';
}

do_test 3.0 {
  forcecopy test.db test.db2
  sqlite3 db2 test.db2
  set P [patchset_from_sql {
    INSERT INTO t2 VALUES(1, 2, 3);
    DELETE FROM t2 WHERE d = 3;
  }]

  list [catch { sqlite3changeset_apply_v2 -invert db2 $P {} } msg] $msg
} {1 SQLITE_CORRUPT}

do_test 3.1 {
  list [catch { sqlite3session_foreach -invert db2 $P {} } msg] $msg
} {1 SQLITE_CORRUPT}

do_test 3.2 {
  sqlite3changeset_apply_v2 db2 $P {} 
  compare_db db db2
} {}


finish_test

/*
** Structure for changeset iterators.
*/
struct sqlite3_changeset_iter {
  SessionInput in;                /* Input buffer or stream */
  SessionBuffer tblhdr;           /* Buffer to hold apValue/zTab/abPK/ */
  int bPatchset;                  /* True if this is a patchset */

  int rc;                         /* Iterator error code */
  sqlite3_stmt *pConflict;        /* Points to conflicting row, if any */
  char *zTab;                     /* Current table */
  int nCol;                       /* Number of columns in zTab */
  int op;                         /* Current operation */
  int bIndirect;                  /* True if current change was indirect */
  u8 *abPK;                       /* Primary key array */
................................................................................
**
** If successful, return zero. Otherwise, if an OOM condition is encountered,
** set *pRc to SQLITE_NOMEM and return non-zero.
*/
static int sessionBufferGrow(SessionBuffer *p, int nByte, int *pRc){
  if( *pRc==SQLITE_OK && p->nAlloc-p->nBuf<nByte ){
    u8 *aNew;
    int nNew = p->nAlloc ? p->nAlloc : 128;
    do {
      nNew = nNew*2;
    }while( nNew<(p->nBuf+nByte) );

    aNew = (u8 *)sqlite3_realloc(p->aBuf, nNew);
    if( 0==aNew ){
      *pRc = SQLITE_NOMEM;
    }else{
      p->aBuf = aNew;
      p->nAlloc = nNew;
    }
  }
................................................................................
** Do the work for either sqlite3changeset_start() or start_strm().
*/
static int sessionChangesetStart(
  sqlite3_changeset_iter **pp,    /* OUT: Changeset iterator handle */
  int (*xInput)(void *pIn, void *pData, int *pnData),
  void *pIn,
  int nChangeset,                 /* Size of buffer pChangeset in bytes */
  void *pChangeset                /* Pointer to buffer containing changeset */

){
  sqlite3_changeset_iter *pRet;   /* Iterator to return */
  int nByte;                      /* Number of bytes to allocate for iterator */

  assert( xInput==0 || (pChangeset==0 && nChangeset==0) );

  /* Zero the output variable in case an error occurs. */
................................................................................
  if( !pRet ) return SQLITE_NOMEM;
  memset(pRet, 0, sizeof(sqlite3_changeset_iter));
  pRet->in.aData = (u8 *)pChangeset;
  pRet->in.nData = nChangeset;
  pRet->in.xInput = xInput;
  pRet->in.pIn = pIn;
  pRet->in.bEof = (xInput ? 0 : 1);


  /* Populate the output variable and return success. */
  *pp = pRet;
  return SQLITE_OK;
}

/*
................................................................................
** Create an iterator used to iterate through the contents of a changeset.
*/
int sqlite3changeset_start(
  sqlite3_changeset_iter **pp,    /* OUT: Changeset iterator handle */
  int nChangeset,                 /* Size of buffer pChangeset in bytes */
  void *pChangeset                /* Pointer to buffer containing changeset */
){
  return sessionChangesetStart(pp, 0, 0, nChangeset, pChangeset);









}

/*
** Streaming version of sqlite3changeset_start().
*/
int sqlite3changeset_start_strm(
  sqlite3_changeset_iter **pp,    /* OUT: Changeset iterator handle */
  int (*xInput)(void *pIn, void *pData, int *pnData),
  void *pIn
){
  return sessionChangesetStart(pp, xInput, pIn, 0, 0);









}

/*
** If the SessionInput object passed as the only argument is a streaming
** object and the buffer is full, discard some data to free up space.
*/
static void sessionDiscardData(SessionInput *pIn){
................................................................................
    if( sessionChangesetReadTblhdr(p) ) return p->rc;
    if( (p->rc = sessionInputBuffer(&p->in, 2)) ) return p->rc;
    p->in.iCurrent = p->in.iNext;
    if( p->in.iNext>=p->in.nData ) return SQLITE_DONE;
    op = p->in.aData[p->in.iNext++];
  }

  if( p->zTab==0 ){
    /* The first record in the changeset is not a table header. Must be a
    ** corrupt changeset. */
    assert( p->in.iNext==1 );
    return (p->rc = SQLITE_CORRUPT_BKPT);
  }

  p->op = op;
  p->bIndirect = p->in.aData[p->in.iNext++];
  if( p->op!=SQLITE_UPDATE && p->op!=SQLITE_DELETE && p->op!=SQLITE_INSERT ){
    return (p->rc = SQLITE_CORRUPT_BKPT);
................................................................................
      nVal = p->nCol;
    }
    p->rc = sessionChangesetBufferRecord(&p->in, nVal, pnRec);
    if( p->rc!=SQLITE_OK ) return p->rc;
    *paRec = &p->in.aData[p->in.iNext];
    p->in.iNext += *pnRec;
  }else{



    /* If this is an UPDATE or DELETE, read the old.* record. */
    if( p->op!=SQLITE_INSERT && (p->bPatchset==0 || p->op==SQLITE_DELETE) ){
      u8 *abPK = p->bPatchset ? p->abPK : 0;
      p->rc = sessionReadRecord(&p->in, p->nCol, abPK, p->apValue);
      if( p->rc!=SQLITE_OK ) return p->rc;
    }

    /* If this is an INSERT or UPDATE, read the new.* record. */
    if( p->op!=SQLITE_DELETE ){
      p->rc = sessionReadRecord(&p->in, p->nCol, 0, &p->apValue[p->nCol]);
      if( p->rc!=SQLITE_OK ) return p->rc;
    }

    if( p->bPatchset && p->op==SQLITE_UPDATE ){
      /* If this is an UPDATE that is part of a patchset, then all PK and
      ** modified fields are present in the new.* record. The old.* record
      ** is currently completely empty. This block shifts the PK fields from
      ** new.* to old.*, to accommodate the code that reads these arrays.  */
      for(i=0; i<p->nCol; i++){
        assert( p->apValue[i]==0 );
        if( p->abPK[i] ){

          p->apValue[i] = p->apValue[i+p->nCol];
          if( p->apValue[i]==0 ) return (p->rc = SQLITE_CORRUPT_BKPT);
          p->apValue[i+p->nCol] = 0;
        }
      }



    }
  }

  return SQLITE_ROW;
}

/*
................................................................................
  int rc = SQLITE_OK;

  while( pApply->constraints.nBuf ){
    sqlite3_changeset_iter *pIter2 = 0;
    SessionBuffer cons = pApply->constraints;
    memset(&pApply->constraints, 0, sizeof(SessionBuffer));

    rc = sessionChangesetStart(&pIter2, 0, 0, cons.nBuf, cons.aBuf);
    if( rc==SQLITE_OK ){
      int nByte = 2*pApply->nCol*sizeof(sqlite3_value*);
      int rc2;
      pIter2->bPatchset = bPatchset;
      pIter2->zTab = (char*)zTab;
      pIter2->nCol = pApply->nCol;
      pIter2->abPK = pApply->abPK;
................................................................................
    sqlite3_changeset_iter *p     /* Handle describing change and conflict */
  ),
  void *pCtx,                     /* First argument passed to xConflict */
  void **ppRebase, int *pnRebase,
  int flags
){
  sqlite3_changeset_iter *pIter;  /* Iterator to skip through changeset */  
  int rc = sqlite3changeset_start(&pIter, nChangeset, pChangeset);

  if( rc==SQLITE_OK ){
    rc = sessionChangesetApply(
        db, pIter, xFilter, xConflict, pCtx, ppRebase, pnRebase, flags
    );
  }
  return rc;
}
................................................................................
    sqlite3_changeset_iter *p     /* Handle describing change and conflict */
  ),
  void *pCtx,                     /* First argument passed to xConflict */
  void **ppRebase, int *pnRebase,
  int flags
){
  sqlite3_changeset_iter *pIter;  /* Iterator to skip through changeset */  
  int rc = sqlite3changeset_start_strm(&pIter, xInput, pIn);

  if( rc==SQLITE_OK ){
    rc = sessionChangesetApply(
        db, pIter, xFilter, xConflict, pCtx, ppRebase, pnRebase, flags
    );
  }
  return rc;
}

/*
** Structure for changeset iterators.
*/
struct sqlite3_changeset_iter {
  SessionInput in;                /* Input buffer or stream */
  SessionBuffer tblhdr;           /* Buffer to hold apValue/zTab/abPK/ */
  int bPatchset;                  /* True if this is a patchset */
  int bInvert;                    /* True to invert changeset */
  int rc;                         /* Iterator error code */
  sqlite3_stmt *pConflict;        /* Points to conflicting row, if any */
  char *zTab;                     /* Current table */
  int nCol;                       /* Number of columns in zTab */
  int op;                         /* Current operation */
  int bIndirect;                  /* True if current change was indirect */
  u8 *abPK;                       /* Primary key array */
................................................................................
**
** If successful, return zero. Otherwise, if an OOM condition is encountered,
** set *pRc to SQLITE_NOMEM and return non-zero.
*/
static int sessionBufferGrow(SessionBuffer *p, int nByte, int *pRc){
  if( *pRc==SQLITE_OK && p->nAlloc-p->nBuf<nByte ){
    u8 *aNew;
    i64 nNew = p->nAlloc ? p->nAlloc : 128;
    do {
      nNew = nNew*2;
    }while( (nNew-p->nBuf)<nByte );

    aNew = (u8 *)sqlite3_realloc64(p->aBuf, nNew);
    if( 0==aNew ){
      *pRc = SQLITE_NOMEM;
    }else{
      p->aBuf = aNew;
      p->nAlloc = nNew;
    }
  }
................................................................................
** Do the work for either sqlite3changeset_start() or start_strm().
*/
static int sessionChangesetStart(
  sqlite3_changeset_iter **pp,    /* OUT: Changeset iterator handle */
  int (*xInput)(void *pIn, void *pData, int *pnData),
  void *pIn,
  int nChangeset,                 /* Size of buffer pChangeset in bytes */
  void *pChangeset,               /* Pointer to buffer containing changeset */
  int bInvert                     /* True to invert changeset */
){
  sqlite3_changeset_iter *pRet;   /* Iterator to return */
  int nByte;                      /* Number of bytes to allocate for iterator */

  assert( xInput==0 || (pChangeset==0 && nChangeset==0) );

  /* Zero the output variable in case an error occurs. */
................................................................................
  if( !pRet ) return SQLITE_NOMEM;
  memset(pRet, 0, sizeof(sqlite3_changeset_iter));
  pRet->in.aData = (u8 *)pChangeset;
  pRet->in.nData = nChangeset;
  pRet->in.xInput = xInput;
  pRet->in.pIn = pIn;
  pRet->in.bEof = (xInput ? 0 : 1);
  pRet->bInvert = bInvert;

  /* Populate the output variable and return success. */
  *pp = pRet;
  return SQLITE_OK;
}

/*
................................................................................
** Create an iterator used to iterate through the contents of a changeset.
*/
int sqlite3changeset_start(
  sqlite3_changeset_iter **pp,    /* OUT: Changeset iterator handle */
  int nChangeset,                 /* Size of buffer pChangeset in bytes */
  void *pChangeset                /* Pointer to buffer containing changeset */
){
  return sessionChangesetStart(pp, 0, 0, nChangeset, pChangeset, 0);
}
int sqlite3changeset_start_v2(
  sqlite3_changeset_iter **pp,    /* OUT: Changeset iterator handle */
  int nChangeset,                 /* Size of buffer pChangeset in bytes */
  void *pChangeset,               /* Pointer to buffer containing changeset */
  int flags
){
  int bInvert = !!(flags & SQLITE_CHANGESETSTART_INVERT);
  return sessionChangesetStart(pp, 0, 0, nChangeset, pChangeset, bInvert);
}

/*
** Streaming version of sqlite3changeset_start().
*/
int sqlite3changeset_start_strm(
  sqlite3_changeset_iter **pp,    /* OUT: Changeset iterator handle */
  int (*xInput)(void *pIn, void *pData, int *pnData),
  void *pIn
){
  return sessionChangesetStart(pp, xInput, pIn, 0, 0, 0);
}
int sqlite3changeset_start_v2_strm(
  sqlite3_changeset_iter **pp,    /* OUT: Changeset iterator handle */
  int (*xInput)(void *pIn, void *pData, int *pnData),
  void *pIn,
  int flags
){
  int bInvert = !!(flags & SQLITE_CHANGESETSTART_INVERT);
  return sessionChangesetStart(pp, xInput, pIn, 0, 0, bInvert);
}

/*
** If the SessionInput object passed as the only argument is a streaming
** object and the buffer is full, discard some data to free up space.
*/
static void sessionDiscardData(SessionInput *pIn){
................................................................................
    if( sessionChangesetReadTblhdr(p) ) return p->rc;
    if( (p->rc = sessionInputBuffer(&p->in, 2)) ) return p->rc;
    p->in.iCurrent = p->in.iNext;
    if( p->in.iNext>=p->in.nData ) return SQLITE_DONE;
    op = p->in.aData[p->in.iNext++];
  }

  if( p->zTab==0 || (p->bPatchset && p->bInvert) ){
    /* The first record in the changeset is not a table header. Must be a
    ** corrupt changeset. */
    assert( p->in.iNext==1 || p->zTab );
    return (p->rc = SQLITE_CORRUPT_BKPT);
  }

  p->op = op;
  p->bIndirect = p->in.aData[p->in.iNext++];
  if( p->op!=SQLITE_UPDATE && p->op!=SQLITE_DELETE && p->op!=SQLITE_INSERT ){
    return (p->rc = SQLITE_CORRUPT_BKPT);
................................................................................
      nVal = p->nCol;
    }
    p->rc = sessionChangesetBufferRecord(&p->in, nVal, pnRec);
    if( p->rc!=SQLITE_OK ) return p->rc;
    *paRec = &p->in.aData[p->in.iNext];
    p->in.iNext += *pnRec;
  }else{
    sqlite3_value **apOld = (p->bInvert ? &p->apValue[p->nCol] : p->apValue);
    sqlite3_value **apNew = (p->bInvert ? p->apValue : &p->apValue[p->nCol]);

    /* If this is an UPDATE or DELETE, read the old.* record. */
    if( p->op!=SQLITE_INSERT && (p->bPatchset==0 || p->op==SQLITE_DELETE) ){
      u8 *abPK = p->bPatchset ? p->abPK : 0;
      p->rc = sessionReadRecord(&p->in, p->nCol, abPK, apOld);
      if( p->rc!=SQLITE_OK ) return p->rc;
    }

    /* If this is an INSERT or UPDATE, read the new.* record. */
    if( p->op!=SQLITE_DELETE ){
      p->rc = sessionReadRecord(&p->in, p->nCol, 0, apNew);
      if( p->rc!=SQLITE_OK ) return p->rc;
    }

    if( (p->bPatchset || p->bInvert) && p->op==SQLITE_UPDATE ){
      /* If this is an UPDATE that is part of a patchset, then all PK and
      ** modified fields are present in the new.* record. The old.* record
      ** is currently completely empty. This block shifts the PK fields from
      ** new.* to old.*, to accommodate the code that reads these arrays.  */
      for(i=0; i<p->nCol; i++){
        assert( p->bPatchset==0 || p->apValue[i]==0 );
        if( p->abPK[i] ){
          assert( p->apValue[i]==0 );
          p->apValue[i] = p->apValue[i+p->nCol];
          if( p->apValue[i]==0 ) return (p->rc = SQLITE_CORRUPT_BKPT);
          p->apValue[i+p->nCol] = 0;
        }
      }
    }else if( p->bInvert ){
      if( p->op==SQLITE_INSERT ) p->op = SQLITE_DELETE;
      else if( p->op==SQLITE_DELETE ) p->op = SQLITE_INSERT;
    }
  }

  return SQLITE_ROW;
}

/*
................................................................................
  int rc = SQLITE_OK;

  while( pApply->constraints.nBuf ){
    sqlite3_changeset_iter *pIter2 = 0;
    SessionBuffer cons = pApply->constraints;
    memset(&pApply->constraints, 0, sizeof(SessionBuffer));

    rc = sessionChangesetStart(&pIter2, 0, 0, cons.nBuf, cons.aBuf, 0);
    if( rc==SQLITE_OK ){
      int nByte = 2*pApply->nCol*sizeof(sqlite3_value*);
      int rc2;
      pIter2->bPatchset = bPatchset;
      pIter2->zTab = (char*)zTab;
      pIter2->nCol = pApply->nCol;
      pIter2->abPK = pApply->abPK;
................................................................................
    sqlite3_changeset_iter *p     /* Handle describing change and conflict */
  ),
  void *pCtx,                     /* First argument passed to xConflict */
  void **ppRebase, int *pnRebase,
  int flags
){
  sqlite3_changeset_iter *pIter;  /* Iterator to skip through changeset */  
  int bInverse = !!(flags & SQLITE_CHANGESETAPPLY_INVERT);
  int rc = sessionChangesetStart(&pIter, 0, 0, nChangeset, pChangeset,bInverse);
  if( rc==SQLITE_OK ){
    rc = sessionChangesetApply(
        db, pIter, xFilter, xConflict, pCtx, ppRebase, pnRebase, flags
    );
  }
  return rc;
}
................................................................................
    sqlite3_changeset_iter *p     /* Handle describing change and conflict */
  ),
  void *pCtx,                     /* First argument passed to xConflict */
  void **ppRebase, int *pnRebase,
  int flags
){
  sqlite3_changeset_iter *pIter;  /* Iterator to skip through changeset */  
  int bInverse = !!(flags & SQLITE_CHANGESETAPPLY_INVERT);
  int rc = sessionChangesetStart(&pIter, xInput, pIn, 0, 0, bInverse);
  if( rc==SQLITE_OK ){
    rc = sessionChangesetApply(
        db, pIter, xFilter, xConflict, pCtx, ppRebase, pnRebase, flags
    );
  }
  return rc;
}

** [sqlite3changeset_invert()] functions, all changes within the changeset 
** that apply to a single table are grouped together. This means that when 
** an application iterates through a changeset using an iterator created by 
** this function, all changes that relate to a single table are visited 
** consecutively. There is no chance that the iterator will visit a change 
** the applies to table X, then one for table Y, and then later on visit 
** another change for table X.







*/
int sqlite3changeset_start(
  sqlite3_changeset_iter **pp,    /* OUT: New changeset iterator handle */
  int nChangeset,                 /* Size of changeset blob in bytes */
  void *pChangeset                /* Pointer to blob containing changeset */
);





















/*
** CAPI3REF: Advance A Changeset Iterator
** METHOD: sqlite3_changeset_iter
**
** This function may only be used with iterators created by function
................................................................................
  int(*xConflict)(
    void *pCtx,                   /* Copy of sixth arg to _apply() */
    int eConflict,                /* DATA, MISSING, CONFLICT, CONSTRAINT */
    sqlite3_changeset_iter *p     /* Handle describing change and conflict */
  ),
  void *pCtx,                     /* First argument passed to xConflict */
  void **ppRebase, int *pnRebase, /* OUT: Rebase data */
  int flags                       /* Combination of SESSION_APPLY_* flags */
);

/*
** CAPI3REF: Flags for sqlite3changeset_apply_v2
**
** The following flags may passed via the 9th parameter to
** [sqlite3changeset_apply_v2] and [sqlite3changeset_apply_v2_strm]:
................................................................................
**   Usually, the sessions module encloses all operations performed by
**   a single call to apply_v2() or apply_v2_strm() in a [SAVEPOINT]. The
**   SAVEPOINT is committed if the changeset or patchset is successfully
**   applied, or rolled back if an error occurs. Specifying this flag
**   causes the sessions module to omit this savepoint. In this case, if the
**   caller has an open transaction or savepoint when apply_v2() is called, 
**   it may revert the partially applied changeset by rolling it back.





*/
#define SQLITE_CHANGESETAPPLY_NOSAVEPOINT   0x0001


/* 
** CAPI3REF: Constants Passed To The Conflict Handler
**
** Values that may be passed as the second argument to a conflict-handler.
**
** <dl>
................................................................................
  int (*xOutput)(void *pOut, const void *pData, int nData),
  void *pOut
);
int sqlite3changeset_start_strm(
  sqlite3_changeset_iter **pp,
  int (*xInput)(void *pIn, void *pData, int *pnData),
  void *pIn






);
int sqlite3session_changeset_strm(
  sqlite3_session *pSession,
  int (*xOutput)(void *pOut, const void *pData, int nData),
  void *pOut
);
int sqlite3session_patchset_strm(

** [sqlite3changeset_invert()] functions, all changes within the changeset 
** that apply to a single table are grouped together. This means that when 
** an application iterates through a changeset using an iterator created by 
** this function, all changes that relate to a single table are visited 
** consecutively. There is no chance that the iterator will visit a change 
** the applies to table X, then one for table Y, and then later on visit 
** another change for table X.
**
** The behavior of sqlite3changeset_start_v2() and its streaming equivalent
** may be modified by passing a combination of
** [SQLITE_CHANGESETSTART_INVERT | supported flags] as the 4th parameter.
**
** Note that the sqlite3changeset_start_v2() API is still <b>experimental</b>
** and therefore subject to change.
*/
int sqlite3changeset_start(
  sqlite3_changeset_iter **pp,    /* OUT: New changeset iterator handle */
  int nChangeset,                 /* Size of changeset blob in bytes */
  void *pChangeset                /* Pointer to blob containing changeset */
);
int sqlite3changeset_start_v2(
  sqlite3_changeset_iter **pp,    /* OUT: New changeset iterator handle */
  int nChangeset,                 /* Size of changeset blob in bytes */
  void *pChangeset,               /* Pointer to blob containing changeset */
  int flags                       /* SESSION_CHANGESETSTART_* flags */
);

/*
** CAPI3REF: Flags for sqlite3changeset_start_v2
**
** The following flags may passed via the 4th parameter to
** [sqlite3changeset_start_v2] and [sqlite3changeset_start_v2_strm]:
**
** <dt>SQLITE_CHANGESETAPPLY_INVERT <dd>
**   Invert the changeset while iterating through it. This is equivalent to
**   inverting a changeset using sqlite3changeset_invert() before applying it.
**   It is an error to specify this flag with a patchset.
*/
#define SQLITE_CHANGESETSTART_INVERT        0x0002


/*
** CAPI3REF: Advance A Changeset Iterator
** METHOD: sqlite3_changeset_iter
**
** This function may only be used with iterators created by function
................................................................................
  int(*xConflict)(
    void *pCtx,                   /* Copy of sixth arg to _apply() */
    int eConflict,                /* DATA, MISSING, CONFLICT, CONSTRAINT */
    sqlite3_changeset_iter *p     /* Handle describing change and conflict */
  ),
  void *pCtx,                     /* First argument passed to xConflict */
  void **ppRebase, int *pnRebase, /* OUT: Rebase data */
  int flags                       /* SESSION_CHANGESETAPPLY_* flags */
);

/*
** CAPI3REF: Flags for sqlite3changeset_apply_v2
**
** The following flags may passed via the 9th parameter to
** [sqlite3changeset_apply_v2] and [sqlite3changeset_apply_v2_strm]:
................................................................................
**   Usually, the sessions module encloses all operations performed by
**   a single call to apply_v2() or apply_v2_strm() in a [SAVEPOINT]. The
**   SAVEPOINT is committed if the changeset or patchset is successfully
**   applied, or rolled back if an error occurs. Specifying this flag
**   causes the sessions module to omit this savepoint. In this case, if the
**   caller has an open transaction or savepoint when apply_v2() is called, 
**   it may revert the partially applied changeset by rolling it back.
**
** <dt>SQLITE_CHANGESETAPPLY_INVERT <dd>
**   Invert the changeset before applying it. This is equivalent to inverting
**   a changeset using sqlite3changeset_invert() before applying it. It is
**   an error to specify this flag with a patchset.
*/
#define SQLITE_CHANGESETAPPLY_NOSAVEPOINT   0x0001
#define SQLITE_CHANGESETAPPLY_INVERT        0x0002

/* 
** CAPI3REF: Constants Passed To The Conflict Handler
**
** Values that may be passed as the second argument to a conflict-handler.
**
** <dl>
................................................................................
  int (*xOutput)(void *pOut, const void *pData, int nData),
  void *pOut
);
int sqlite3changeset_start_strm(
  sqlite3_changeset_iter **pp,
  int (*xInput)(void *pIn, void *pData, int *pnData),
  void *pIn
);
int sqlite3changeset_start_v2_strm(
  sqlite3_changeset_iter **pp,
  int (*xInput)(void *pIn, void *pData, int *pnData),
  void *pIn,
  int flags
);
int sqlite3session_changeset_strm(
  sqlite3_session *pSession,
  int (*xOutput)(void *pOut, const void *pData, int nData),
  void *pOut
);
int sqlite3session_patchset_strm(

  int nRebase = 0;
  int flags = 0;                  /* Flags for apply_v2() */

  memset(&sStr, 0, sizeof(sStr));
  sStr.nStream = test_tcl_integer(interp, SESSION_STREAM_TCL_VAR);

  /* Check for the -nosavepoint flag */

  if( bV2 && objc>1 ){
    const char *z1 = Tcl_GetString(objv[1]);
    int n = strlen(z1);
    if( n>1 && n<=12 && 0==sqlite3_strnicmp("-nosavepoint", z1, n) ){
      flags = SQLITE_CHANGESETAPPLY_NOSAVEPOINT;
      objc--;
      objv++;










    }
  }

  if( objc!=4 && objc!=5 ){
    const char *zMsg;
    if( bV2 ){

      zMsg = "?-nosavepoint? DB CHANGESET CONFLICT-SCRIPT ?FILTER-SCRIPT?";
    }else{
      zMsg = "DB CHANGESET CONFLICT-SCRIPT ?FILTER-SCRIPT?";
    }
    Tcl_WrongNumArgs(interp, 1, objv, zMsg);
    return TCL_ERROR;
  }
  if( 0==Tcl_GetCommandInfo(interp, Tcl_GetString(objv[1]), &info) ){
................................................................................
  int nChangeset;
  sqlite3_changeset_iter *pIter;
  int rc;
  Tcl_Obj *pVarname;
  Tcl_Obj *pCS;
  Tcl_Obj *pScript;
  int isCheckNext = 0;


  TestStreamInput sStr;
  memset(&sStr, 0, sizeof(sStr));

  if( objc>1 ){
    char *zOpt = Tcl_GetString(objv[1]);






    isCheckNext = (strcmp(zOpt, "-next")==0);


  }



  if( objc!=4+isCheckNext ){
    Tcl_WrongNumArgs(interp, 1, objv, "?-next? VARNAME CHANGESET SCRIPT");

    return TCL_ERROR;
  }

  pVarname = objv[1+isCheckNext];
  pCS = objv[2+isCheckNext];
  pScript = objv[3+isCheckNext];

  pChangeset = (void *)Tcl_GetByteArrayFromObj(pCS, &nChangeset);
  sStr.nStream = test_tcl_integer(interp, SESSION_STREAM_TCL_VAR);


  if( sStr.nStream==0 ){









    rc = sqlite3changeset_start(&pIter, nChangeset, pChangeset);
  }else{
    sStr.aData = (unsigned char*)pChangeset;
    sStr.nData = nChangeset;
    rc = sqlite3changeset_start_strm(&pIter, testStreamInput, (void*)&sStr);

  }
  if( rc!=SQLITE_OK ){
    return test_session_error(interp, rc, 0);
  }

  while( SQLITE_ROW==sqlite3changeset_next(pIter) ){
    int nCol;                     /* Number of columns in table */

  int nRebase = 0;
  int flags = 0;                  /* Flags for apply_v2() */

  memset(&sStr, 0, sizeof(sStr));
  sStr.nStream = test_tcl_integer(interp, SESSION_STREAM_TCL_VAR);

  /* Check for the -nosavepoint flag */
  if( bV2 ){
    if( objc>1 ){
      const char *z1 = Tcl_GetString(objv[1]);
      int n = strlen(z1);
      if( n>1 && n<=12 && 0==sqlite3_strnicmp("-nosavepoint", z1, n) ){
        flags |= SQLITE_CHANGESETAPPLY_NOSAVEPOINT;
        objc--;
        objv++;
      }
    }
    if( objc>1 ){
      const char *z1 = Tcl_GetString(objv[1]);
      int n = strlen(z1);
      if( n>1 && n<=7 && 0==sqlite3_strnicmp("-invert", z1, n) ){
        flags |= SQLITE_CHANGESETAPPLY_INVERT;
        objc--;
        objv++;
      }
    }
  }

  if( objc!=4 && objc!=5 ){
    const char *zMsg;
    if( bV2 ){
      zMsg = "?-nosavepoint? ?-inverse? "
        "DB CHANGESET CONFLICT-SCRIPT ?FILTER-SCRIPT?";
    }else{
      zMsg = "DB CHANGESET CONFLICT-SCRIPT ?FILTER-SCRIPT?";
    }
    Tcl_WrongNumArgs(interp, 1, objv, zMsg);
    return TCL_ERROR;
  }
  if( 0==Tcl_GetCommandInfo(interp, Tcl_GetString(objv[1]), &info) ){
................................................................................
  int nChangeset;
  sqlite3_changeset_iter *pIter;
  int rc;
  Tcl_Obj *pVarname;
  Tcl_Obj *pCS;
  Tcl_Obj *pScript;
  int isCheckNext = 0;
  int isInvert = 0;

  TestStreamInput sStr;
  memset(&sStr, 0, sizeof(sStr));

  while( objc>1 ){
    char *zOpt = Tcl_GetString(objv[1]);
    int nOpt = strlen(zOpt);
    if( zOpt[0]!='-' ) break;
    if( nOpt<=7 && 0==sqlite3_strnicmp(zOpt, "-invert", nOpt) ){
      isInvert = 1;
    }else
    if( nOpt<=5 && 0==sqlite3_strnicmp(zOpt, "-next", nOpt) ){
      isCheckNext = 1;
    }else{
      break;
    }
    objv++;
    objc--;
  }
  if( objc!=4 ){
    Tcl_WrongNumArgs(
        interp, 1, objv, "?-next? ?-invert? VARNAME CHANGESET SCRIPT");
    return TCL_ERROR;
  }

  pVarname = objv[1];
  pCS = objv[2];
  pScript = objv[3];

  pChangeset = (void *)Tcl_GetByteArrayFromObj(pCS, &nChangeset);
  sStr.nStream = test_tcl_integer(interp, SESSION_STREAM_TCL_VAR);
  if( isInvert ){
    int f = SQLITE_CHANGESETSTART_INVERT;
    if( sStr.nStream==0 ){
      rc = sqlite3changeset_start_v2(&pIter, nChangeset, pChangeset, f);
    }else{
      void *pCtx = (void*)&sStr;
      sStr.aData = (unsigned char*)pChangeset;
      sStr.nData = nChangeset;
      rc = sqlite3changeset_start_v2_strm(&pIter, testStreamInput, pCtx, f);
    }
  }else{
    if( sStr.nStream==0 ){
      rc = sqlite3changeset_start(&pIter, nChangeset, pChangeset);
    }else{
      sStr.aData = (unsigned char*)pChangeset;
      sStr.nData = nChangeset;
      rc = sqlite3changeset_start_strm(&pIter, testStreamInput, (void*)&sStr);
    }
  }
  if( rc!=SQLITE_OK ){
    return test_session_error(interp, rc, 0);
  }

  while( SQLITE_ROW==sqlite3changeset_next(pIter) ){
    int nCol;                     /* Number of columns in table */

  "ENABLE_FTS3_TOKENIZER",
#endif
#if SQLITE_ENABLE_FTS4
  "ENABLE_FTS4",
#endif
#if SQLITE_ENABLE_FTS5
  "ENABLE_FTS5",



#endif
#if SQLITE_ENABLE_HIDDEN_COLUMNS
  "ENABLE_HIDDEN_COLUMNS",
#endif
#if SQLITE_ENABLE_ICU
  "ENABLE_ICU",
#endif

  "ENABLE_FTS3_TOKENIZER",
#endif
#if SQLITE_ENABLE_FTS4
  "ENABLE_FTS4",
#endif
#if SQLITE_ENABLE_FTS5
  "ENABLE_FTS5",
#endif
#if SQLITE_ENABLE_GEOPOLY
  "ENABLE_GEOPOLY",
#endif
#if SQLITE_ENABLE_HIDDEN_COLUMNS
  "ENABLE_HIDDEN_COLUMNS",
#endif
#if SQLITE_ENABLE_ICU
  "ENABLE_ICU",
#endif

#endif

  return 1;
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */

/*
** A structure to keep track of all of the column values that fixed to
** a known value due to WHERE clause constraints of the form COLUMN=VALUE.
*/
typedef struct WhereConst WhereConst;
struct WhereConst {
  Parse *pParse;   /* Parsing context */
  int nConst;      /* Number for COLUMN=CONSTANT terms */
  int nChng;       /* Number of times a constant is propagated */
  Expr **apExpr;   /* [i*2] is COLUMN and [i*2+1] is VALUE */
};

/*
** Add a new entry to the pConst object

*/
static void constInsert(
  WhereConst *pConst,
  Expr *pColumn,
  Expr *pValue

){















  pConst->nConst++;
  pConst->apExpr = sqlite3DbReallocOrFree(pConst->pParse->db, pConst->apExpr,
                         pConst->nConst*2*sizeof(Expr*));
  if( pConst->apExpr==0 ){
    pConst->nConst = 0;
  }else{

#endif

  return 1;
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */

/*
** A structure to keep track of all of the column values that are fixed to
** a known value due to WHERE clause constraints of the form COLUMN=VALUE.
*/
typedef struct WhereConst WhereConst;
struct WhereConst {
  Parse *pParse;   /* Parsing context */
  int nConst;      /* Number for COLUMN=CONSTANT terms */
  int nChng;       /* Number of times a constant is propagated */
  Expr **apExpr;   /* [i*2] is COLUMN and [i*2+1] is VALUE */
};

/*
** Add a new entry to the pConst object.  Except, do not add duplicate
** pColumn entires.
*/
static void constInsert(
  WhereConst *pConst,      /* The WhereConst into which we are inserting */

  Expr *pColumn,           /* The COLUMN part of the constraint */
  Expr *pValue             /* The VALUE part of the constraint */
){
  int i;
  assert( pColumn->op==TK_COLUMN );

  /* 2018-10-25 ticket [cf5ed20f]
  ** Make sure the same pColumn is not inserted more than once */
  for(i=0; i<pConst->nConst; i++){
    const Expr *pExpr = pConst->apExpr[i*2];
    assert( pExpr->op==TK_COLUMN );
    if( pExpr->iTable==pColumn->iTable
     && pExpr->iColumn==pColumn->iColumn
    ){
      return;  /* Already present.  Return without doing anything. */
    }
  }

  pConst->nConst++;
  pConst->apExpr = sqlite3DbReallocOrFree(pConst->pParse->db, pConst->apExpr,
                         pConst->nConst*2*sizeof(Expr*));
  if( pConst->apExpr==0 ){
    pConst->nConst = 0;
  }else{

** any SQL window functions, this function is a no-op. Otherwise, it 
** rewrites the SELECT statement so that window function xStep functions
** are invoked in the correct order as described under "SELECT REWRITING"
** at the top of this file.
*/
int sqlite3WindowRewrite(Parse *pParse, Select *p){
  int rc = SQLITE_OK;
  if( p->pWin ){
    Vdbe *v = sqlite3GetVdbe(pParse);
    sqlite3 *db = pParse->db;
    Select *pSub = 0;             /* The subquery */
    SrcList *pSrc = p->pSrc;
    Expr *pWhere = p->pWhere;
    ExprList *pGroupBy = p->pGroupBy;
    Expr *pHaving = p->pHaving;

** any SQL window functions, this function is a no-op. Otherwise, it 
** rewrites the SELECT statement so that window function xStep functions
** are invoked in the correct order as described under "SELECT REWRITING"
** at the top of this file.
*/
int sqlite3WindowRewrite(Parse *pParse, Select *p){
  int rc = SQLITE_OK;
  if( p->pWin && p->pPrior==0 ){
    Vdbe *v = sqlite3GetVdbe(pParse);
    sqlite3 *db = pParse->db;
    Select *pSub = 0;             /* The subquery */
    SrcList *pSrc = p->pSrc;
    Expr *pWhere = p->pWhere;
    ExprList *pGroupBy = p->pGroupBy;
    Expr *pHaving = p->pHaving;

  }
}

#-------------------------------------------------------------------------
# The following tests focus on DBSTATUS_CACHE_USED_SHARED
#
ifcapable shared_cache {
  if {[permutation]=="memsys3"
      || [permutation]=="memsys5"
      || $::tcl_platform(os)=="Linux"} {
    proc do_cacheused_test {tn db res} {
      set cu [sqlite3_db_status $db SQLITE_DBSTATUS_CACHE_USED 0]
      set pcu [sqlite3_db_status $db SQLITE_DBSTATUS_CACHE_USED_SHARED 0]
      set cu [lindex $cu 1]
      set pcu [lindex $pcu 1]
      uplevel [list do_test $tn [list list $cu $pcu] "#/$res/"]
    }

  }
}

#-------------------------------------------------------------------------
# The following tests focus on DBSTATUS_CACHE_USED_SHARED
#
ifcapable shared_cache {
  if {([permutation]=="memsys3"
      || [permutation]=="memsys5"
      || $::tcl_platform(os)=="Linux") && ![sqlite3 -has-codec]} {
    proc do_cacheused_test {tn db res} {
      set cu [sqlite3_db_status $db SQLITE_DBSTATUS_CACHE_USED 0]
      set pcu [sqlite3_db_status $db SQLITE_DBSTATUS_CACHE_USED_SHARED 0]
      set cu [lindex $cu 1]
      set pcu [lindex $pcu 1]
      uplevel [list do_test $tn [list list $cu $pcu] "#/$res/"]
    }

# This file implements regression tests for SQLite library.
#
# This file implements tests for PRAGMA data_version command.
#

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






do_execsql_test pragma3-100 {
  PRAGMA data_version;
} {1}
do_execsql_test pragma3-101 {
  PRAGMA temp.data_version;
} {1}

# This file implements regression tests for SQLite library.
#
# This file implements tests for PRAGMA data_version command.
#

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


if {[sqlite3 -has-codec]} {
  finish_test
  return
}

do_execsql_test pragma3-100 {
  PRAGMA data_version;
} {1}
do_execsql_test pragma3-101 {
  PRAGMA temp.data_version;
} {1}

} {1 {no such table: t1}}
do_test shared-$av-16.8 {
  file exists test1.db
} {0}  ;# Verify that the database is in-memory

# Shared cache on named memory databases attached to readonly connections.
#

do_test shared-$av-16.8.1 {
  db1 close
  db2 close

  sqlite3 db test1.db
  db eval { 
    CREATE TABLE yy(a, b);
    INSERT INTO yy VALUES(77, 88);
  }
  db close

  sqlite3 db1 test1.db -uri 1 -readonly 1
  sqlite3 db2 test2.db -uri 1 

  db1 eval { 
    ATTACH 'file:mem?mode=memory&cache=shared' AS shared; 
    CREATE TABLE shared.xx(a, b);
    INSERT INTO xx VALUES(55, 66);
  }
  db2 eval { 
    ATTACH 'file:mem?mode=memory&cache=shared' AS shared;
    SELECT * FROM xx;
  }
} {55 66}

do_test shared-$av-16.8.2 { db1 eval { SELECT * FROM yy } } {77 88}
do_test shared-$av-16.8.3 { 
  list [catch {db1 eval { INSERT INTO yy VALUES(1, 2) }} msg] $msg
} {1 {attempt to write a readonly database}}

db1 close
db2 close


}  ;# end of autovacuum on/off loop

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test

} {1 {no such table: t1}}
do_test shared-$av-16.8 {
  file exists test1.db
} {0}  ;# Verify that the database is in-memory

# Shared cache on named memory databases attached to readonly connections.
#
if {![sqlite3 -has-codec]} {
  do_test shared-$av-16.8.1 {
    db1 close
    db2 close
  
    sqlite3 db test1.db
    db eval { 
      CREATE TABLE yy(a, b);
      INSERT INTO yy VALUES(77, 88);
    }
    db close
  
    sqlite3 db1 test1.db -uri 1 -readonly 1
    sqlite3 db2 test2.db -uri 1 
  
    db1 eval { 
      ATTACH 'file:mem?mode=memory&cache=shared' AS shared; 
      CREATE TABLE shared.xx(a, b);
      INSERT INTO xx VALUES(55, 66);
    }
    db2 eval { 
      ATTACH 'file:mem?mode=memory&cache=shared' AS shared;
      SELECT * FROM xx;
    }
  } {55 66}
  
  do_test shared-$av-16.8.2 { db1 eval { SELECT * FROM yy } } {77 88}
  do_test shared-$av-16.8.3 { 
    list [catch {db1 eval { INSERT INTO yy VALUES(1, 2) }} msg] $msg
  } {1 {attempt to write a readonly database}}
  
  db1 close
  db2 close
}

}  ;# end of autovacuum on/off loop

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test

    FROM A,
         (SELECT id,yy,zz FROM C) subq,
         B
   WHERE A.id='1'
     AND A.id=subq.yy
     AND B.id=subq.zz;
} {1}  












finish_test

    FROM A,
         (SELECT id,yy,zz FROM C) subq,
         B
   WHERE A.id='1'
     AND A.id=subq.yy
     AND B.id=subq.zz;
} {1}  

# 2018-10-25: Ticket [cf5ed20f]
# Incorrect join result with duplicate WHERE clause constraint.
#
do_execsql_test 400 {
  CREATE TABLE x(a, b, c);
  CREATE TABLE y(a, b);
  INSERT INTO x VALUES (1, 0, 1);
  INSERT INTO y VALUES (1, 2);
  SELECT x.a FROM x JOIN y ON x.c = y.a WHERE x.b = 1 AND x.b = 1;
} {}

finish_test

do_execsql_test 12.110 {
  INSERT INTO t1 VALUES(6, 'F', 'three');
  INSERT INTO t1 VALUES(7, 'G', 'one');
  SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
    FROM t1 WHERE id>1
   ORDER BY b LIMIT 2;
} {2 B two 3 C three}














































finish_test

do_execsql_test 12.110 {
  INSERT INTO t1 VALUES(6, 'F', 'three');
  INSERT INTO t1 VALUES(7, 'G', 'one');
  SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
    FROM t1 WHERE id>1
   ORDER BY b LIMIT 2;
} {2 B two 3 C three}

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

do_execsql_test 13.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a int, b int);
  INSERT INTO t1 VALUES(1,11);
  INSERT INTO t1 VALUES(2,12);
}

do_execsql_test 13.2.1 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1;
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2   2 1   1 2
}
do_execsql_test 13.2.2 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    UNION ALL
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2   2 1   1 2
}
do_execsql_test 13.3 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    UNION 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   1 2   2 1   2 2  
}

do_execsql_test 13.4 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    EXCEPT 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2 
}

do_execsql_test 13.5 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    INTERSECT 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
}

finish_test