SQLite

**
** 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 && p->pSrc->nSrc>0 && 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;
          sqlite3SelectDelete(pParse->db, pSub);
        }
      }
    }
  }
}

/*

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



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