SQLite

/*
** Append the contents of SrcList p2 to SrcList p1 and return the resulting
** SrcList. Or, if an error occurs, return NULL. In all cases, p1 and p2
** are deleted by this function.
*/
SrcList *sqlite3SrcListAppendList(Parse *pParse, SrcList *p1, SrcList *p2){
  assert( p1 );
  if( p2 ){
    int nOld = p1->nSrc;
    SrcList *pNew = sqlite3SrcListEnlarge(pParse, p1, p2->nSrc, nOld);
    if( pNew==0 ){
      sqlite3SrcListDelete(pParse->db, p2);
    }else{
      p1 = pNew;
      memcpy(&p1->a[nOld], p2->a, p2->nSrc*sizeof(SrcItem));
      assert( nOld==1 || (p2->nSrc==1 && (p2->a[0].fg.jointype&JT_LTORJ)==0) );
      assert( p1->nSrc>=2 );
      sqlite3DbFree(pParse->db, p2);
      p1->a[0].fg.jointype |= (JT_LTORJ & p1->a[1].fg.jointype);
    }
  }
  return p1;
}

        }else{
          sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
        }
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){
          ExprSetProperty(pExpr, EP_VarSelect);
          pExpr->x.pSelect->selFlags |= SF_Correlated;
          if( pExpr->op==TK_EXISTS ) pParse->bHasExists = 1;
        }
        pNC->ncFlags |= NC_Subquery;
      }
      break;
    }
    case TK_VARIABLE: {
      testcase( pNC->ncFlags & NC_IsCheck );

    if( i==0 ) break;
    i--;
    pItem--;
    if( pItem->pSelect!=0 ) return 0;                             /* (1c-i) */
  }
  return 1;
}

/*
** sqlite3WalkExpr() callback used by exprReferencesTable().
*/
static int exprReferencesTableExprCb(Walker *pWalker, Expr *pExpr){
  if( pExpr->op==TK_COLUMN && pExpr->iTable==pWalker->u.iCur ){
    pWalker->eCode = 1;
  }
  return WRC_Continue;
}

/*
** Return true if the expression passed as the first argument refers
** to cursor number iCur. Otherwise return false.
*/
static int exprReferencesTable(Expr *pExpr, int iCur){
  Walker w;
  memset(&w, 0, sizeof(w));
  w.u.iCur = iCur;
  w.xExprCallback = exprReferencesTableExprCb;
  w.xSelectCallback = sqlite3SelectWalkNoop;
  sqlite3WalkExpr(&w, pExpr);
  return w.eCode;
}

/*
** Index pIdx is a UNIQUE index on the table accessed by cursor number 
** iCsr. This function returns a mask of the index columns that are
** constrained to match a single, non-NULL value by the WHERE clause
** passed as the 4th argument. For example, if the index is:
**
**      CREATE UNIQUE INDEX idx ON tbl(a, b, c);
**
** and pWhere:
**
**      WHERE a=? AND c=?
**
** then this function returns 5.
*/
static u64 findConstIdxTerms(
  Parse *pParse, 
  int iCsr,
  Index *pIdx, 
  Expr *pWhere
){
  u64 m = 0;
  if( pWhere->op==TK_AND ){
    m = findConstIdxTerms(pParse, iCsr, pIdx, pWhere->pLeft);
    m |= findConstIdxTerms(pParse, iCsr, pIdx, pWhere->pRight);
  }else if( pWhere->op==TK_EQ ){
    Expr *pLeft = sqlite3ExprSkipCollateAndLikely(pWhere->pLeft);
    Expr *pRight = sqlite3ExprSkipCollateAndLikely(pWhere->pRight);
    if( pRight->op==TK_COLUMN && pRight->iTable==iCsr ){
      SWAP(Expr*, pLeft, pRight);
    }
    if( pLeft->op==TK_COLUMN 
     && pLeft->iTable==iCsr 
     && exprReferencesTable(pRight, iCsr)==0
    ){
      if( pIdx ){
        int ii;
        for(ii=0; ii<pIdx->nKeyCol; ii++){
          assert( pIdx->azColl[ii] );
          if( pLeft->iColumn==pIdx->aiColumn[ii] ){
            CollSeq *pColl = sqlite3ExprCompareCollSeq(pParse, pWhere);
            if( pColl && sqlite3StrICmp(pColl->zName, pIdx->azColl[ii])==0 ){
              m |= ((u64)1 << ii);
              break;
            }
          }
        }
      }else{
        if( pLeft->iColumn<0 ) m = 1;
      }
    }
  }
  return m;
}

/*
** Argument pWhere is the WHERE clause belonging to SELECT statement p. This
** function attempts to transform expressions of the form:
**
**     EXISTS (SELECT ...)
**
** into joins. For example, given
**
**    CREATE TABLE sailors(sid INTEGER PRIMARY KEY, name TEXT);
**    CREATE TABLE reserves(sid INT, day DATE, PRIMARY KEY(sid, day));
**
**    SELECT name FROM sailors AS S WHERE EXISTS (
**      SELECT * FROM reserves AS R WHERE S.sid = R.sid AND R.day = '2022-10-25'
**    );
**
** the SELECT statement may be transformed as follows:
**
**    SELECT name FROM sailors AS S, reserves AS R
**      WHERE S.sid = R.sid AND R.day = '2022-10-25';
*/
static void existsToJoin(Parse *pParse, Select *p, Expr *pWhere){
  if( pWhere 
   && !ExprHasProperty(pWhere, EP_OuterON|EP_InnerON) 
   && p->pSrc->nSrc>0
   && p->pSrc->nSrc<BMS
   && pParse->db->mallocFailed==0 
  ){
    if( pWhere->op==TK_AND ){
      Expr *pRight = pWhere->pRight;
      existsToJoin(pParse, p, pWhere->pLeft);
      existsToJoin(pParse, p, pRight);
    }
    else if( pWhere->op==TK_EXISTS ){
      Select *pSub = pWhere->x.pSelect;
      if( pSub->pSrc->nSrc==1 
       && (pSub->selFlags & (SF_Aggregate|SF_Correlated))==SF_Correlated
       && pSub->pWhere
      ){
        int bTransform = 0;       /* True if EXISTS can be made into join */
        Table *pTab = pSub->pSrc->a[0].pTab;
        int iCsr = pSub->pSrc->a[0].iCursor;
        Index *pIdx;
        if( HasRowid(pTab) && findConstIdxTerms(pParse, iCsr, 0,pSub->pWhere) ){
          bTransform = 1;
        }
        for(pIdx=pTab->pIndex; pIdx && bTransform==0; pIdx=pIdx->pNext){
          if( pIdx->onError && pIdx->nKeyCol<=63 ){
            u64 c = findConstIdxTerms(pParse, iCsr, pIdx, pSub->pWhere);
            if( c==((u64)1 << pIdx->nKeyCol)-1 ){
              bTransform = 1;
            }
          }
        }
        if( bTransform ){
          memset(pWhere, 0, sizeof(*pWhere));
          pWhere->op = TK_INTEGER;
          pWhere->u.iValue = 1;
          ExprSetProperty(pWhere, EP_IntValue);

          assert( p->pWhere!=0 );
          p->pSrc = sqlite3SrcListAppendList(pParse, p->pSrc, pSub->pSrc);
          p->pWhere = sqlite3PExpr(pParse, TK_AND, p->pWhere, pSub->pWhere);

          pSub->pWhere = 0;
          pSub->pSrc = 0;
          sqlite3ParserAddCleanup(pParse, sqlite3SelectDeleteGeneric, pSub);
#if TREETRACE_ENABLED
          if( sqlite3TreeTrace & 0x100000 ){
            TREETRACE(0x100000,pParse,p,
                      ("After EXISTS-to-JOIN optimization:\n"));
            sqlite3TreeViewSelect(0, p, 0);
          }
#endif
        }
      }
    }
  }
}

/*
** Generate code for the SELECT statement given in the p argument. 
**
** The results are returned according to the SelectDest structure.
** See comments in sqliteInt.h for further information.
**

      sqlite3TreeViewSelect(0, p, 0);
    }
#endif
    if( p->pNext==0 ) ExplainQueryPlanPop(pParse);
    return rc;
  }
#endif

  /* If there may be an "EXISTS (SELECT ...)" in the WHERE clause, attempt
  ** to change it into a join.  */
  if( pParse->bHasExists && OptimizationEnabled(db,SQLITE_ExistsToJoin) ){
    existsToJoin(pParse, p, p->pWhere);
    pTabList = p->pSrc;
  }

  /* Do the WHERE-clause constant propagation optimization if this is
  ** a join.  No need to speed time on this operation for non-join queries
  ** as the equivalent optimization will be handled by query planner in
  ** sqlite3WhereBegin().
  */
  if( p->pWhere!=0

**   0x00002000     Constant propagation
**   0x00004000     Push-down optimization
**   0x00008000     After all FROM-clause analysis
**   0x00010000     Beginning of DELETE/INSERT/UPDATE processing
**   0x00020000     Transform DISTINCT into GROUP BY
**   0x00040000     SELECT tree dump after all code has been generated
**   0x00080000     NOT NULL strength reduction
**   0x00100000     EXISTS-to-JOIN optimization
*/

/*
** Macros for "wheretrace"
*/
extern u32 sqlite3WhereTrace;
#if defined(SQLITE_DEBUG) \

#define SQLITE_ReleaseReg     0x00400000 /* Use OP_ReleaseReg for testing */
#define SQLITE_FlttnUnionAll  0x00800000 /* Disable the UNION ALL flattener */
   /* TH3 expects this value  ^^^^^^^^^^ See flatten04.test */
#define SQLITE_IndexedExpr    0x01000000 /* Pull exprs from index when able */
#define SQLITE_Coroutines     0x02000000 /* Co-routines for subqueries */
#define SQLITE_NullUnusedCols 0x04000000 /* NULL unused columns in subqueries */
#define SQLITE_OnePass        0x08000000 /* Single-pass DELETE and UPDATE */
#define SQLITE_ExistsToJoin   0x10000000 /* The EXISTS-to-JOIN optimization */
#define SQLITE_AllOpts        0xffffffff /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
#define OptimizationEnabled(db, mask)   (((db)->dbOptFlags&(mask))==0)

  u8 mayAbort;         /* True if statement may throw an ABORT exception */
  u8 hasCompound;      /* Need to invoke convertCompoundSelectToSubquery() */
  u8 okConstFactor;    /* OK to factor out constants */
  u8 disableLookaside; /* Number of times lookaside has been disabled */
  u8 prepFlags;        /* SQLITE_PREPARE_* flags */
  u8 withinRJSubrtn;   /* Nesting level for RIGHT JOIN body subroutines */
  u8 bHasWith;         /* True if statement contains WITH */
  u8 bHasExists;       /* Has a correlated "EXISTS (SELECT ....)" expression */
  u8 mSubrtnSig;       /* mini Bloom filter on available SubrtnSig.selId */
#if defined(SQLITE_DEBUG) || defined(SQLITE_COVERAGE_TEST)
  u8 earlyCleanup;     /* OOM inside sqlite3ParserAddCleanup() */
#endif
#ifdef SQLITE_DEBUG
  u8 ifNotExists;      /* Might be true if IF NOT EXISTS.  Assert()s only */
#endif

# 2024 May 25
#
# 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.
#
#***********************************************************************
#

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


do_execsql_test 1.0 {
  CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
  INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
  CREATE INDEX x1b ON x1(b);

  CREATE TABLE x2(x, y);
  INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
}

do_execsql_test 1.1 {
  SELECT 1 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=5)
} {1}

do_execsql_test 1.2 {
  SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
} {1 2 3 4 5 6}

# With "a=x", the UNIQUE index means the EXIST can be transformed to a join.
# So no "SUBQUERY". With "b=x", the index is not UNIQUE and so there is a
# "SUBQUERY".
do_execsql_test 1.3.1 {
  EXPLAIN QUERY PLAN
  SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
} {~/SUBQUERY/}
do_execsql_test 1.3.2 {
  EXPLAIN QUERY PLAN
  SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE b=x)
} {/SUBQUERY/}

do_execsql_test 1.4.1 {
  EXPLAIN QUERY PLAN
  SELECT * FROM x2 WHERE x=1 AND EXISTS (SELECT 1 FROM x1 WHERE a=x)
} {~/SUBQUERY/}
do_execsql_test 1.4.2 {
  EXPLAIN QUERY PLAN
  SELECT * FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y=2
} {~/SUBQUERY/}

do_execsql_test 1.5 {
  SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x)
} {3}

#-------------------------------------------------------------------------
do_execsql_test 2.0 {
  CREATE TABLE t1(a, b);
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000
  ) INSERT INTO t1 SELECT i, i FROM s;

  CREATE TABLE t2(c, d);
  WITH s(i) AS (
    SELECT 10 UNION ALL SELECT i+10 FROM s WHERE i<1000
  ) INSERT INTO t2 SELECT i, i FROM s;
}

do_execsql_test 2.1 {
  SELECT count(*) FROM t1;
  SELECT count(*) FROM t2;
} {1000 100}

do_execsql_test 2.2 {
  SELECT count(*) FROM t1, t2 WHERE a=c;
} {100}

do_execsql_test 2.3 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
} {100}
do_eqp_test 2.4 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a)
} {SCAN t1}

do_execsql_test 2.5 {
  CREATE UNIQUE INDEX t2c ON t2(c);
  CREATE UNIQUE INDEX t1a ON t1(a);
}

do_eqp_test 2.4.1 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
} {SCAN t1*SEARCH t2}
do_execsql_test 2.4.2 {
  ANALYZE;
}
do_eqp_test 2.4.3 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
} {SCAN t2*SEARCH t1}
do_execsql_test 2.4.4 {
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c=a);
} {100}

do_execsql_test 2.5.1 {
  EXPLAIN QUERY PLAN
  SELECT count(*) FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.rowid=a);
} {~/SUBQUERY/}

#-------------------------------------------------------------------------
proc do_subquery_test {tn bSub sql res} {
  set r1(0) ~/SUBQUERY/
  set r1(1) /SUBQUERY/
  do_execsql_test $tn.1 "explain query plan $sql" $r1($bSub)
  do_execsql_test $tn.2 $sql $res
}

do_execsql_test 3.0 {
  CREATE TABLE y1(a, b, c);
  CREATE TABLE y2(x, y, z);
  CREATE UNIQUE INDEX y2zy ON y2(z, y);

  INSERT INTO y1 VALUES(1, 1, 1);
  INSERT INTO y1 VALUES(2, 2, 2);
  INSERT INTO y1 VALUES(3, 3, 3);
  INSERT INTO y1 VALUES(4, 4, 4);

  INSERT INTO y2 VALUES(1, 1, 1);
  INSERT INTO y2 VALUES(3, 3, 3);
}

do_subquery_test 3.1 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=a AND y=b AND x=z
  )
} {
  1 1 1          3 3 3
}    

do_subquery_test 3.2 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND x=z
  )
} {
  1 1 1          3 3 3
}    

do_subquery_test 3.3 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=max(a,b) AND y=min(b,a) AND c!=3
  )
} {
  1 1 1
}    

do_subquery_test 3.4 1 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=max(a,b) AND b=3
  )
} {
  3 3 3
}    

do_subquery_test 3.5 0 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=a-1 AND y=a-1
  )
} {
  2 2 2
  4 4 4
}    

do_subquery_test 3.6 1 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 WHERE z=a-1 AND y+1=a
  )
} {
  2 2 2
  4 4 4
}    

do_subquery_test 3.7 1 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT count(*) FROM y2 WHERE z=a-1 AND y=a-1
  )
} {
  1 1 1
  2 2 2
  3 3 3
  4 4 4
}    

do_subquery_test 3.8 1 {
  SELECT * FROM y1 WHERE EXISTS ( SELECT a+1 FROM y2 )
} {
  1 1 1
  2 2 2
  3 3 3
  4 4 4
}    

do_subquery_test 3.9 1 {
  SELECT * FROM y1 WHERE EXISTS ( 
      SELECT 1 FROM y2 one, y2 two WHERE one.z=a-1 AND one.y=a-1
  )
} {
  2 2 2
  4 4 4
}    

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE tx1(a TEXT COLLATE nocase, b TEXT);
  CREATE UNIQUE INDEX tx1ab ON tx1(a, b);

  INSERT INTO tx1 VALUES('a', 'a');
  INSERT INTO tx1 VALUES('B', 'b');
  INSERT INTO tx1 VALUES('c', 'c');
  INSERT INTO tx1 VALUES('D', 'd');
  INSERT INTO tx1 VALUES('e', 'e');

  CREATE TABLE tx2(x, y);
  INSERT INTO tx2 VALUES('A', 'a');
  INSERT INTO tx2 VALUES('b', 'b');
  INSERT INTO tx2 VALUES('C', 'c');
  INSERT INTO tx2 VALUES('D', 'd');
}

do_subquery_test 4.1 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND b=y
  )
} {
  A a
  b b
  C c
  D d
}

do_subquery_test 4.1.1 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE (a COLLATE nocase)=x AND b=y
  )
} {
  A a   b b   C c   D d
}
do_subquery_test 4.1.2 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND (b COLLATE binary)=y
  )
} {
  A a   b b   C c   D d
}
do_subquery_test 4.1.1 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE x=(a COLLATE nocase) AND b=y
  )
} {
  A a   b b   C c   D d
}
do_subquery_test 4.1.2 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND y=(b COLLATE binary)
  )
} {
  A a   b b   C c   D d
}

do_subquery_test 4.2 1 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND b=y COLLATE nocase
  )
} {
  A a
  b b
  C c
  D d
}

do_execsql_test 4.3 {
  DROP INDEX tx1ab;
  CREATE UNIQUE INDEX tx1ab ON tx1(a COLLATE binary, b);
}

do_subquery_test 4.4 1 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x AND b=y
  )
} {
  A a
  b b
  C c
  D d
}

do_subquery_test 4.4 0 {
  SELECT * FROM tx2 WHERE EXISTS (
    SELECT 1 FROM tx1 WHERE a=x COLLATE binary AND b=y
  )
} {
  D d
}

do_subquery_test 4.4 1 {
  SELECT EXISTS ( SELECT x FROM tx1 ) FROM tx2
} {
  1 1 1 1
}

do_subquery_test 4.4 1 {
  SELECT (SELECT EXISTS ( SELECT x FROM tx1 ) WHERE 1) FROM tx2
} {
  1 1 1 1
}

#-------------------------------------------------------------------------
proc cols {s f} {
  set lCols [list]
  for {set i $s} {$i<=$f} {incr i} {
    lappend lCols [format "c%02d" $i]
  }
  join $lCols ", "
}
proc vals {n val} {
  set lVal [list]
  for {set i 0} {$i<$n} {incr i} {
    lappend lVal $val
  }
  join $lVal ", "
}
proc exprs {s f} {
  set lExpr [list]
  for {set i $s} {$i<=$f} {incr i} {
    lappend lExpr [format "c%02d = o" $i]
  }
  join $lExpr " AND "
}


do_execsql_test 5.0 "
  CREATE TABLE a1( [cols 0 99] );
"
do_execsql_test 5.1 "
  -- 63 column index
  CREATE UNIQUE INDEX a1idx1 ON a1( [cols 0 62] );
"
do_execsql_test 5.2 "
  -- 64 column index
  CREATE UNIQUE INDEX a1idx2 ON a1( [cols 10 73] );
"
do_execsql_test 5.2 "
  -- 65 column index
  CREATE UNIQUE INDEX a1idx3 ON a1( [cols 20 84] );
"

do_test 5.3 {
  foreach v {1 2 3 4 5 6} {
    execsql "INSERT INTO a1 VALUES( [vals 100 $v] )"
  }
} {}

do_execsql_test 5.4 {
  CREATE TABLE a2(o);
  INSERT INTO a2 VALUES(2), (5); 
}

do_subquery_test 5.5 0 "
  SELECT o FROM a2 WHERE EXISTS (
    SELECT 1 FROM a1 WHERE [exprs 0 62]
  )
" {
  2 5
}

do_subquery_test 5.6 1 "
  SELECT o FROM a2 WHERE EXISTS (
    SELECT 1 FROM a1 WHERE [exprs 10 73]
  )
" {
  2 5
}

do_subquery_test 5.7 1 "
  SELECT o FROM a2 WHERE EXISTS (
    SELECT 1 FROM a1 WHERE [exprs 20 84]
  )
" {
  2 5
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(a, b UNIQUE, c UNIQUE);
  CREATE TABLE t2(a INfEGER PRIMARY KEY, b);
  CREATE UNIQUE INDEX t2b ON t2(b);
}

do_catchsql_test 6.1 {
  SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c COLLATE f = a)
} {1 {no such collation sequence: f}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 7.0 {
  CREATE TABLE t1(x);
  CREATE TABLE t2(y UNIQUE);

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

  SELECT * FROM t1 one LEFT JOIN t1 two ON (one.x=two.x AND EXISTS (
    SELECT 1 FROM t2 WHERE y=one.x
  ));
} {
  1 1
  2 {}
}



finish_test

# 2024 June 14
#
# 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.
#
#***********************************************************************
#

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


do_execsql_test 1.0 {
  CREATE TABLE x1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
  INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
  CREATE INDEX x1b ON x1(b);

  CREATE TABLE x2(x, y);
  INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
}

do_execsql_test 1.1 {
  SELECT * FROM x1 WHERE EXISTS (SELECT 1 FROM x2 WHERE a!=123)
} {1 2   3 4   5 6}

do_execsql_test 1.2 {
  CREATE TABLE x3(u, v);
  CREATE INDEX x3u ON x3(u);
  INSERT INTO x3 VALUES
    (1, 1), (1, 2), (1, 3),
    (2, 1), (2, 2), (2, 3);
}

do_execsql_test 1.3 {
  SELECT * FROM x1 WHERE EXISTS (
    SELECT 1 FROM x3 WHERE u IN (1, 2, 3, 4) AND v=b
  );
} {
  1 2
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 2.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX t1ab ON t1(a,b);

  INSERT INTO t1 VALUES
      ('abc', 1, 1),
      ('abc', 2, 2),
      ('abc', 2, 3),

      ('def', 1, 1),
      ('def', 2, 2),
      ('def', 2, 3);

  CREATE TABLE t2(x, y);
  INSERT INTO t2 VALUES(1, 1), (2, 2), (3, 3);

  ANALYZE;
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1 VALUES('t1','t1ab','10000 5000 2');
  ANALYZE sqlite_master;
}


do_execsql_test 2.1 {
  SELECT a,b,c FROM t1 WHERE b=2 ORDER BY a
} {
  abc 2 2
  abc 2 3
  def 2 2
  def 2 3
}

do_execsql_test 2.2 {
  SELECT x, y FROM t2 WHERE EXISTS (
    SELECT 1 FROM t1 WHERE b=x
  )
} {
  1 1
  2 2
}



finish_test

# 2024 May 25
#
# 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.
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/malloc_common.tcl
set testprefix existsfault

db close
sqlite3_shutdown
sqlite3_config_lookaside 0 0
sqlite3_initialize
autoinstall_test_functions
sqlite3 db test.db

do_execsql_test 1.0 {
  CREATE TABLE x1(a, b);
  INSERT INTO x1 VALUES(1, 2), (3, 4), (5, 6);
  CREATE UNIQUE INDEX x1a ON x1(a);
  CREATE INDEX x1b ON x1(b);

  CREATE TABLE x2(x, y);
  INSERT INTO x2 VALUES(1, 2), (3, 4), (5, 6);
}

do_faultsim_test 1 -faults oom* -prep {
  sqlite3 db test.db
  execsql { SELECT * FROM sqlite_schema }
} -body {
  execsql {
    SELECT count(*) FROM x2 WHERE EXISTS (SELECT 1 FROM x1 WHERE a=x) AND y!=11
  }
} -test {
  faultsim_test_result {0 3}
}

finish_test