SQLite

      ** POS_END (0) or POS_COLUMN (1). The following block merges the two lists
      ** and writes the results to buffer p. p is left pointing to the byte
      ** after the list written. No terminator (POS_END or POS_COLUMN) is
      ** written to the output.
      */
      fts3GetDeltaVarint(&p1, &i1);
      fts3GetDeltaVarint(&p2, &i2);
      if( i1<2 || i2<2 ){
        break;
      }
      do {
        fts3PutDeltaVarint(&p, &iPrev, (i1<i2) ? i1 : i2); 
        iPrev -= 2;
        if( i1==i2 ){
          fts3ReadNextPos(&p1, &i1);
          fts3ReadNextPos(&p2, &i2);
        }else if( i1<i2 ){

  sqlite3_int64 *piDocid,         /* IN/OUT: Docid pointer */
  u8 *pbEof                       /* OUT: End-of-file flag */
){
  char *p = *ppIter;

  assert( nDoclist>0 );
  assert( *pbEof==0 );
  assert_fts3_nc( p || *piDocid==0 );
  assert( !p || (p>=aDoclist && p<=&aDoclist[nDoclist]) );

  if( p==0 ){
    p = aDoclist;
    p += sqlite3Fts3GetVarint(p, piDocid);
  }else{
    fts3PoslistCopy(0, &p);

** the phrase object passed as the fifth argument according to a NEAR
** condition. For example:
**
**     abc NEAR/5 "def ghi"
**
** Parameter nNear is passed the NEAR distance of the expression (5 in
** the example above). When this function is called, *paPoslist points to
** the position list, and *pnToken is the number of phrase tokens in the
** phrase on the other side of the NEAR operator to pPhrase. For example,
** if pPhrase refers to the "def ghi" phrase, then *paPoslist points to
** the position list associated with phrase "abc".
**
** All positions in the pPhrase position list that are not sufficiently
** close to a position in the *paPoslist position list are removed. If this
** leaves 0 positions, zero is returned. Otherwise, non-zero.

    **
    ** Check to see if iFreeBlk should be coalesced onto the end of iStart.
    */
    if( iFreeBlk && iEnd+3>=iFreeBlk ){
      nFrag = iFreeBlk - iEnd;
      if( iEnd>iFreeBlk ) return SQLITE_CORRUPT_PAGE(pPage);
      iEnd = iFreeBlk + get2byte(&data[iFreeBlk+2]);
      if( iEnd > pPage->pBt->usableSize ){
        return SQLITE_CORRUPT_PAGE(pPage);
      }
      iSize = iEnd - iStart;
      iFreeBlk = get2byte(&data[iFreeBlk]);
    }
  
    /* If iPtr is another freeblock (that is, if iPtr is not the freelist

      }
      if( pWalker->eCode==3 && pExpr->iTable==pWalker->u.iCur ){
        return WRC_Continue;
      }
      /* Fall through */
    case TK_IF_NULL_ROW:
    case TK_REGISTER:
    case TK_DOT:
      testcase( pExpr->op==TK_REGISTER );
      testcase( pExpr->op==TK_IF_NULL_ROW );
      testcase( pExpr->op==TK_DOT );
      pWalker->eCode = 0;
      return WRC_Abort;
    case TK_VARIABLE:
      if( pWalker->eCode==5 ){
        /* Silently convert bound parameters that appear inside of CREATE
        ** statements into a NULL when parsing the CREATE statement text out
        ** of the sqlite_master table */

        pLimit = p->pLimit;
        p->pLimit = 0;
        uniondest.eDest = op;
        ExplainQueryPlan((pParse, 1, "%s USING TEMP B-TREE",
                          selectOpName(p->op)));
        rc = sqlite3Select(pParse, p, &uniondest);
        testcase( rc!=SQLITE_OK );


        assert( p->pOrderBy==0 );
        pDelete = p->pPrior;
        p->pPrior = pPrior;
        p->pOrderBy = 0;
        if( p->op==TK_UNION ){
          p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow);
        }
        sqlite3ExprDelete(db, p->pLimit);

    **   SELECT a+5, b*10 FROM (SELECT x*3 AS a, y+10 AS b FROM t1) WHERE a>b;
    **   \                     \_____________ subquery __________/          /
    **    \_____________________ outer query ______________________________/
    **
    ** We look at every expression in the outer query and every place we see
    ** "a" we substitute "x*3" and every place we see "b" we substitute "y+10".
    */
    if( pSub->pOrderBy && (pParent->selFlags & SF_NoopOrderBy)==0 ){
      /* At this point, any non-zero iOrderByCol values indicate that the
      ** ORDER BY column expression is identical to the iOrderByCol'th
      ** expression returned by SELECT statement pSub. Since these values
      ** do not necessarily correspond to columns in SELECT statement pParent,
      ** zero them before transfering the ORDER BY clause.
      **
      ** Not doing this may cause an error if a subsequent call to this

      pSub->pOrderBy = 0;
    }
    pWhere = pSub->pWhere;
    pSub->pWhere = 0;
    if( isLeftJoin>0 ){
      sqlite3SetJoinExpr(pWhere, iNewParent);
    }
    if( pWhere ){
      if( pParent->pWhere ){
        pParent->pWhere = sqlite3PExpr(pParse, TK_AND, pWhere, pParent->pWhere);
      }else{
        pParent->pWhere = pWhere;
      }
    }
    if( db->mallocFailed==0 ){
      SubstContext x;
      x.pParse = pParse;
      x.iTable = iParent;
      x.iNewTable = iNewParent;
      x.isLeftJoin = isLeftJoin;
      x.pEList = pSub->pEList;

  Token dummy;

  if( p->pPrior==0 ) return WRC_Continue;
  if( p->pOrderBy==0 ) return WRC_Continue;
  for(pX=p; pX && (pX->op==TK_ALL || pX->op==TK_SELECT); pX=pX->pPrior){}
  if( pX==0 ) return WRC_Continue;
  a = p->pOrderBy->a;
#ifndef SQLITE_OMIT_WINDOWFUNC
  /* If iOrderByCol is already non-zero, then it has already been matched
  ** to a result column of the SELECT statement. This occurs when the
  ** SELECT is rewritten for window-functions processing and then passed
  ** to sqlite3SelectPrep() and similar a second time. The rewriting done
  ** by this function is not required in this case. */
  if( a[0].u.x.iOrderByCol ) return WRC_Continue;
#endif
  for(i=p->pOrderBy->nExpr-1; i>=0; i--){
    if( a[i].pExpr->flags & EP_Collate ) break;
  }
  if( i<0 ) return WRC_Continue;

  /* If we reach this point, that means the transformation is required. */

           pDest->eDest==SRT_Queue  || pDest->eDest==SRT_DistFifo ||
           pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo);
    /* If ORDER BY makes no difference in the output then neither does
    ** DISTINCT so it can be removed too. */
    sqlite3ExprListDelete(db, p->pOrderBy);
    p->pOrderBy = 0;
    p->selFlags &= ~SF_Distinct;
    p->selFlags |= SF_NoopOrderBy;
  }
  sqlite3SelectPrep(pParse, p, 0);
  if( pParse->nErr || db->mallocFailed ){
    goto select_end;
  }
  assert( p->pEList!=0 );
#if SELECTTRACE_ENABLED

#define SF_MaybeConvert  0x0008000 /* Need convertCompoundSelectToSubquery() */
#define SF_Converted     0x0010000 /* By convertCompoundSelectToSubquery() */
#define SF_IncludeHidden 0x0020000 /* Include hidden columns in output */
#define SF_ComplexResult 0x0040000 /* Result contains subquery or function */
#define SF_WhereBegin    0x0080000 /* Really a WhereBegin() call.  Debug Only */
#define SF_WinRewrite    0x0100000 /* Window function rewrite accomplished */
#define SF_View          0x0200000 /* SELECT statement is a view */
#define SF_NoopOrderBy   0x0400000 /* ORDER BY is ignored for this query */

/*
** The results of a SELECT can be distributed in several ways, as defined
** by one of the following macros.  The "SRT" prefix means "SELECT Result
** Type".
**
**     SRT_Union       Store results as a key in a temporary index

  }else{
    /* Neither operand is NULL.  Do a comparison. */
    affinity = pOp->p5 & SQLITE_AFF_MASK;
    if( affinity>=SQLITE_AFF_NUMERIC ){
      if( (flags1 | flags3)&MEM_Str ){
        if( (flags1 & (MEM_Int|MEM_IntReal|MEM_Real|MEM_Str))==MEM_Str ){
          applyNumericAffinity(pIn1,0);
          testcase( flags3==pIn3->flags );
          flags3 = pIn3->flags;
        }
        if( (flags3 & (MEM_Int|MEM_IntReal|MEM_Real|MEM_Str))==MEM_Str ){
          applyNumericAffinity(pIn3,0);
        }
      }
      /* Handle the common case of integer comparison here, as an

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

# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
ifcapable !fts3 { finish_test ; return }

set ::testprefix fts3corrupt2
sqlite3_fts3_may_be_corrupt 1

set data [list]
lappend data {*}{
   "amxtvoo adqwroyhz auq aithtir avniqnuynvf axp ahibayfynig agbicpm"
   "ajdtebs anteaxr aieynenwmd awpl alo akxcrwow aoxftge aoqvgul"
   "amcfvdr auz apu aebelm ahuxyz aqc asyafdb agulvhvqu"
   "apepwfyz azkhdvkw aenyelxzbk aslnitbyet aycdsdcpgr aqzzdbc agfi axnypydou"

        catchsql { SELECT * FROM t2 WHERE t2 MATCH 'a*' }
        set {} {}
      } {}
    }
    execsql { UPDATE t2_segdir SET root = $blob WHERE rowid = $rowid }
  }
}






finish_test

do_catchsql_test 37.1 {
  INSERT INTO f VALUES (0,x'00');
} {1 {database disk image is malformed}}

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


do_test 38.0 {
  sqlite3 db {}
  db deserialize [decode_hexdb {
.open --hexdb
| size 24576 pagesize 4096 filename crash-1cc4f8a70485ce.db
| page 1 offset 0
|      0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00   SQLite format 3.

| end crash-1cc4f8a70485ce.db
}]} {}

do_execsql_test 38.1 {
  UPDATE t1 SET b=a;
}

do_catchsql_test 38.2 {
  SELECT b FROM t1 WHERE a MATCH 'e*e*e*e*e*e*e*e*e*e*e*e*e*e*e*e*'
} {1 {database disk image is malformed}}

#-------------------------------------------------------------------------
reset_db
set saved $sqlite_fts3_enable_parentheses
set sqlite_fts3_enable_parentheses 1
do_execsql_test 39.0 {
  CREATE VIRTUAL TABLE t0 USING fts3(
    col0 INTEGER PRIMARY KEY,
    col1 VARCHAR(8),
    col2 BINARY,
    col3 BINARY
  );
  INSERT INTO t0_content VALUES(1,1,'1234','aaaa','bbbb');
  INSERT INTO t0_segdir VALUES(0,0,0,0,'0 42',X'000131030782000103323334050101010200000461616161050101020200000462626262050101030200');
}

do_test 39.1 {
  catch {
    db eval { SELECT rowid FROM t0 WHERE t0 MATCH '1 NEAR 1' }
  }
} 0

do_test 39.2 {
  catch {
    db eval {
  SELECT matchinfo(t0,'yxy') FROM t0 WHERE t0 MATCH x'2b0a312b0a312a312a2a0b5d0a0b0b0a312a0a0b0b0a312a0b310a392a0b0a27312a2a0b5d0a312a0b310a31315d0b310a312a316d2a0b313b15bceaa50a312a0b0a27312a2a0b5d0a312a0b310a312b0b2a310a312a0b2a0b2a0b2e5d0a0bff313336e34a2a312a0b0a3c310b0a0b4b4b0b4b2a4bec40322b2a0b310a0a312a0a0a0a0a0a0a0a0a0b310a312a2a2a0b5d0a0b0b0a312a0b310a312a0b0a4e4541530b310a5df5ced70a0a0a0a0a4f520a0a0a0a0a0a0a312a0b0a4e4541520b310a5d616161610a0a0a0a4f520a0a0a0a0a0a312b0a312a312a0a0a0a0a0a0a004a0b0a310b220a0b0a310a4a22310a0b0a7e6fe0e0e030e0e0e0e0e01176e02000e0e0e0e0e01131320226310a0b0a310a4a22310a0b0a310a766f8b8b4ee0e0300ae0090909090909090909090909090909090909090909090909090909090909090947aaaa540b09090909090909090909090909090909090909090909090909090909090909fae0e0f2f22164e0e0f273e07fefefef7d6dfafafafa6d6d6d6d';
    }
  }
} 0
set sqlite_fts3_enable_parentheses $saved

#-------------------------------------------------------------------------
reset_db
set saved $sqlite_fts3_enable_parentheses
set sqlite_fts3_enable_parentheses 1

do_execsql_test 40.1 {

  CREATE VIRTUAL TABLE t0 USING fts3(col0 INTEGER PRIMARY KEY, col1, col2 ,col3 );
  INSERT INTO t0_segdir VALUES(0,0,0,0,'0 42',
      X'0001310301020001033233340500010102000004616161bc050101020200000462626262050101030200'
  );
}

do_execsql_test 40.2 {
  SELECT 0==matchinfo(t0,'sx') FROM t0 WHERE t0 MATCH '1* 2 3 4 5 6 OR 1';
} 0

set sqlite_fts3_enable_parentheses $saved

finish_test

  SELECT 0.5 COLLATE NOCASE IS TRUE;
  SELECT 0.0 IS FALSE;

  SELECT 0.0 IS FALSE COLLATE NOCASE;
  SELECT 0.0 IS FALSE COLLATE RTRIM;
  SELECT 0.0 IS FALSE COLLATE BINARY;
} {1 1 1   1 1 1  1 1 1}

# 2020-06-12 bug report from Chromium
# https://bugs.chromium.org/p/chromium/issues/detail?id=1094247
do_catchsql_test istrue-800 {
  SELECT 9 IN (false.false);
} {1 {no such column: false.false}}
do_execsql_test istrue-810 {
  CREATE TABLE t8(a INT, true INT, false INT, d INT);
  INSERT INTO t8(a,true,false,d) VALUES(5,6,7,8),(4,3,2,1),('a','b','c','d');
  SELECT * FROM t8 ORDER BY false;
} {4 3 2 1 5 6 7 8 a b c d}
do_catchsql_test istrue-820 {
  SELECT 9 IN (false.false) FROM t8;
} {1 {no such column: false.false}}
do_execsql_test istrue-830 {
  CREATE TABLE false(true INT, false INT, x INT CHECK (5 IN (false.false)));
} {}
do_execsql_test istrue-840 {
  INSERT INTO False VALUES(4,5,6);
} {}
do_catchsql_test istrue-841 {
  INSERT INTO False VALUES(5,6,7);
} {1 {CHECK constraint failed: false}}
do_execsql_test istrue-850 {
  SELECT 9 IN (false.false) FROM false;
} {0}
do_execsql_test istrue-851 {
  SELECT 5 IN (false.false) FROM false;
} {1}

finish_test

reset_db
do_execsql_test select3-9.100 {
  CREATE TABLE t0(c0 REAL, c1 REAL GENERATED ALWAYS AS (c0));
  INSERT INTO t0(c0) VALUES (1);
  SELECT * FROM t0 GROUP BY c0;
} {1.0 1.0}

reset_db
do_execsql_test select3.10.100 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(c, d);
  SELECT max(t1.a), 
         (SELECT 'xyz' FROM (SELECT * FROM t2 WHERE 0) WHERE t1.b=1) 
  FROM t1;
} {{} {}}

finish_test

  DROP TABLE IF EXISTS t2;
  CREATE TABLE t1(a INTEGER);
  CREATE TABLE t2(b TEXT);
  INSERT INTO t2(b) VALUES('12345');
  SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t2) WHERE a=a;
} {12345}

# 2020-06-15 ticket 8f157e8010b22af0
#
reset_db
do_execsql_test 7.1 {
  CREATE TABLE t1(c1);     INSERT INTO t1 VALUES(12),(123),(1234),(NULL),('abc');
  CREATE TABLE t2(c2);     INSERT INTO t2 VALUES(44),(55),(123);
  CREATE TABLE t3(c3,c4);  INSERT INTO t3 VALUES(66,1),(123,2),(77,3);
  CREATE VIEW t4 AS SELECT c3 FROM t3;
  CREATE VIEW t5 AS SELECT c3 FROM t3 ORDER BY c4;
}
do_execsql_test 7.2 {
  SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t4) AND c1=123;
} {123 123}
do_execsql_test 7.3 {
  SELECT * FROM t1, t2 WHERE c1=(SELECT 123 INTERSECT SELECT c2 FROM t5) AND c1=123;
} {123 123}
do_execsql_test 7.4 {
  CREATE TABLE a(b);
  CREATE VIEW c(d) AS SELECT b FROM a ORDER BY b;
  SELECT sum(d) OVER( PARTITION BY(SELECT 0 FROM c JOIN a WHERE b =(SELECT b INTERSECT SELECT d FROM c) AND b = 123)) FROM c;
} {}

finish_test

#
do_execsql_test 9.10 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a, b FLOAT);
  INSERT INTO t1(a) VALUES(''),(NULL),('X'),(NULL);
  SELECT coalesce(max(quote(a)),10) FROM t1 GROUP BY a;
} {NULL '' 'X'}

# 2020-06-14: assert() changed back into testcase()
# ticket 9fb26d37cefaba40
#
reset_db
do_execsql_test 10.1 {
  CREATE TABLE a(b TEXT);  INSERT INTO a VALUES(0),(4),(9);
  CREATE TABLE c(d NUM);
  CREATE VIEW f(g, h) AS SELECT b, 0 FROM a UNION SELECT d, d FROM c;
  SELECT g = g FROM f GROUP BY h;
} {1}

finish_test

do_execsql_test 62.4 {
  SELECT * FROM ( 
      SELECT sum(b) OVER() AS c FROM t1 
      UNION
      SELECT b AS c FROM t1
      ) WHERE c>10;
} {15.0 30.0}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 63.1 {
  CREATE TABLE t1(b, x);
  CREATE TABLE t2(c, d);
  CREATE TABLE t3(e, f);
}

do_execsql_test 63.2 {
  SELECT max(b) OVER(
      ORDER BY SUM(
        (SELECT c FROM t2 UNION SELECT x ORDER BY c)
      )
  ) FROM t1;
} {{}}

do_execsql_test 63.3 {
  SELECT sum(b) over(
      ORDER BY (
        SELECT max(b) OVER(
          ORDER BY sum(
            (SELECT x AS c UNION SELECT 1234 ORDER BY c)
          )
        ) AS e
        ORDER BY e
      )
    )
  FROM t1;
} {{}}

finish_test