SQLite

    ** See ticket http://www.sqlite.org/src/info/f2369304e4
    **
    ** 2022-02-04:  Do not push down slices of a row-value comparison.
    ** In other words, "w" or "y" may not be a slice of a vector.  Otherwise,
    ** the initialization of the right-hand operand of the vector comparison
    ** might not occur, or might occur only in an OR branch that is not
    ** taken.  dbsqlfuzz 80a9fade844b4fb43564efc972bcb2c68270f5d1.
    **
    ** 2022-03-03:  Do not push down expressions that involve subqueries.
    ** The subquery might get coded as a subroutine.  Any table-references
    ** in the subquery might be resolved to index-references for the index on
    ** the OR branch in which the subroutine is coded.  But if the subroutine
    ** is invoked from a different OR branch that uses a different index, such
    ** index-references will not work.  tag-20220303a
    ** https://sqlite.org/forum/forumpost/36937b197273d403
    */
    if( pWC->nTerm>1 ){
      int iTerm;
      for(iTerm=0; iTerm<pWC->nTerm; iTerm++){
        Expr *pExpr = pWC->a[iTerm].pExpr;
        if( &pWC->a[iTerm] == pTerm ) continue;
        testcase( pWC->a[iTerm].wtFlags & TERM_VIRTUAL );
        testcase( pWC->a[iTerm].wtFlags & TERM_CODED );
        testcase( pWC->a[iTerm].wtFlags & TERM_SLICE );
        if( (pWC->a[iTerm].wtFlags & (TERM_VIRTUAL|TERM_CODED|TERM_SLICE))!=0 ){
          continue;
        }
        if( (pWC->a[iTerm].eOperator & WO_SINGLE)==0 ) continue;
        if( ExprHasProperty(pExpr, EP_Subquery) ) continue;  /* tag-20220303a */
        pExpr = sqlite3ExprDup(db, pExpr, 0);
        pAndExpr = sqlite3ExprAnd(pParse, pAndExpr, pExpr);
      }
      if( pAndExpr ){
        /* The extra 0x10000 bit on the opcode is masked off and does not
        ** become part of the new Expr.op.  However, it does make the
        ** op==TK_AND comparison inside of sqlite3PExpr() false, and this

  |  |--INDEX 1
  |  |  `--SEARCH t301 USING COVERING INDEX t301_c4 (c4=?)
  |  `--INDEX 2
  |     `--SEARCH t301 USING INTEGER PRIMARY KEY (rowid=?)
  |--SEARCH t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)
  `--USE TEMP B-TREE FOR ORDER BY
}

# 2022-03-03 https://sqlite.org/forum/forumpost/36937b197273d403
#
# In the multi-index OR, if there is an auxiliary WHERE clause term
# that includes a subquery and that subquery is pushed down into the
# OR-clause subqueries, WHERE subquery might get coded as a subroutine.
# In that case, the covering-index optimizer will attempt to change
# table-references into index-references.  But it will do so for the
# index of the OR branch in which the subquery is coded.  If the
# subquery subroutine is called from a different OR branch, the
# index might be different and the index-reference will no longer
# work.  tag-20220303a
#
reset_db
do_execsql_test 4.1 {
  CREATE TABLE t0(w);
  INSERT INTO t0(w) VALUES(1);
  CREATE TABLE t1(x INT, y INT PRIMARY KEY, z); 
  INSERT INTO t1 VALUES(0,111,222);
  CREATE INDEX t1zxy ON t1(z,x,y);
  SELECT y FROM t1
   WHERE (z=222 OR y=111)
     AND (false OR EXISTS(SELECT 1 FROM t0 WHERE t1.y));
} {111}

finish_test