SQLite

** references within expression pExpr have already been successfully
** resolved to this table. This function sets the pSrc->a[0].colUsed
** field to reflect the set of table columns used by pExpr (and no 
** others).
*/
static void deleteSetColUsed(Parse *pParse, SrcList *pSrc, Expr *pExpr){
  Walker w;
  assert( pSrc->nSrc==1 );
  memset(&w, 0, sizeof(w));
  w.pParse = pParse;
  w.u.pSrcList = pSrc;
  w.xExprCallback = deleteSetColUsedExpr;
  w.xSelectCallback2 = deleteSetColUsedSelect;
  pSrc->a[0].colUsed = 0;
  sqlite3WalkExpr(&w, pExpr);

    int rc = SQLITE_OK;           /* Return code */
    Expr *pExpr = 0;              /* OR'd expression */
    int i;                        /* Counter variable */
    assert( !IsVirtual(pTab) && bComplex==0 );

    /* This loop iterates once for each OR-connected term in the WHERE clause */
    for(i=0; rc==SQLITE_OK && (pExpr=sqlite3WhereSplitExpr(pWInfo, i)); i++){
      if( db->mallocFailed==0 ){
        deleteSetColUsed(pParse, pTabList, pExpr);
        rc = deleteFrom(pParse, pTabList, pExpr, 0, 0, 0, memCnt, nIdx);
      }
      sqlite3ExprDelete(db, pExpr);
    }
    sqlite3WhereInfoFree(db, pWInfo);
    return rc;
  }

  /* Keep track of the number of rows to be deleted */

int sqlite3WhereIsSorted(WhereInfo*);
int sqlite3WhereContinueLabel(WhereInfo*);
int sqlite3WhereBreakLabel(WhereInfo*);
int sqlite3WhereOkOnePass(WhereInfo*, int*);
#define ONEPASS_OFF          0    /* Use of ONEPASS not allowed */
#define ONEPASS_SINGLE       1    /* ONEPASS valid for a single row update */
#define ONEPASS_MULTI        2    /* ONEPASS is valid for multiple rows */
#define ONEPASS_SPLIT_DELETE 3    /* DELETE should be split into multiple ops */
void sqlite3ExprCodeLoadIndexColumn(Parse*, Index*, int, int, int);
int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8);
void sqlite3ExprCodeGetColumnToReg(Parse*, Table*, int, int, int);
void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
void sqlite3ExprCodeMove(Parse*, int, int, int);
void sqlite3ExprCacheStore(Parse*, int, int, int);
void sqlite3ExprCachePush(Parse*);

** OR-connected sub-expressions are numbered contiguously starting from 
** zero. The sub-expression to return is identified by the iExpr parameter
** passed to this function. If iExpr is equal to or greater than the number
** of sub-expressions, NULL is returned. Otherwise, a pointer to a copy of 
** sub-expression iExpr is returned. The caller is responsible for eventually
** deleting this object using sqlite3ExprDelete().
**
** If an OOM error occurs, the mallocFailed field of the database handle
** (pWInfo->pParse->db->mallocFailed) is set to record the error. Even
** if an OOM error occurs, this function may return a non-NULL pointer. In
** this case the caller is still responsible for deleting the returned
** object, even though it is not safe to use.
*/
Expr *sqlite3WhereSplitExpr(WhereInfo *pWInfo, int iExpr){
  sqlite3 *db = pWInfo->pParse->db;
  WhereLoop *pLoop = pWInfo->a[0].pWLoop;
  WhereTerm *pTerm = pLoop->aLTerm[0];
  WhereClause *pOrWC = &pTerm->u.pOrInfo->wc;
  Expr *pExpr = 0;

  if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 ){
    int wsFlags = pWInfo->a[0].pWLoop->wsFlags;
    int bOnerow = (wsFlags & WHERE_ONEROW)!=0;
    if( bOnerow || ( (wctrlFlags & WHERE_ONEPASS_MULTIROW)
       && 0==(wsFlags & WHERE_VIRTUALTABLE)
    )){
      if( (wsFlags & WHERE_MULTI_OR) && (wctrlFlags & WHERE_ONEPASS_MULTIROW) ){
        /* This call is being made as part of a DELETE program and the
        ** optimizer has indicated that the OR-optimization is the best
        ** approach. In this case it is better to let the caller generate
        ** a separate loop for each OR'd term than to actually go ahead
        ** and code the OR-optimized loop. Set the value returned by
        ** sqlite3WhereOkOnePass() to ONEPASS_SPLIT_DELETE to communicate 
        ** this to the caller and return early.  */
        pWInfo->eOnePass = ONEPASS_SPLIT_DELETE;
        return pWInfo;
      }
      pWInfo->eOnePass = bOnerow ? ONEPASS_SINGLE : ONEPASS_MULTI;
      if( HasRowid(pTabList->a[0].pTab) && (wsFlags & WHERE_IDX_ONLY) ){
        if( wctrlFlags & WHERE_ONEPASS_MULTIROW ){
          bFordelete = OPFLAG_FORDELETE;

    do_execsql_test 5.$tn.$tn2.2 { 
      SELECT count(*), md5sum(i, a, b, c) FROM t5 
    } $res($tn2)
     
    execsql ROLLBACK
  }
}

#-------------------------------------------------------------------------
# Test that nested OR optimizations work.
#
do_execsql_test 6.0 {
  CREATE TABLE t6(a, b, c, d);
  CREATE INDEX t6a ON t6(a);
  CREATE INDEX t6b ON t6(b);
  CREATE INDEX t6c ON t6(c);
  WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000 ) 
  INSERT INTO t6 SELECT i, i*2, i*3, i%2 FROM s;
}

proc do_61_test {tn sql lDel} {
  uplevel [list do_execsql_test $tn "
    BEGIN;
      $sql;
      WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000 ) 
      SELECT i FROM s EXCEPT SELECT a FROM t6 ORDER BY 1;
    ROLLBACK;
  " $lDel]
}

do_61_test 6.1 {
  DELETE FROM t6 WHERE a=22 OR b=90 OR (d=0 AND (b=400 OR c=303));
} {22 45 200}

do_61_test 6.2 {
  DELETE FROM t6 WHERE a=22 OR b=90 OR (d=1 AND (b=400 OR c=303));
} {22 45 101}

do_61_test 6.3 {
  DELETE FROM t6 WHERE (d=0 AND (a=100 OR b=150)) OR (d=1 AND (b=50 OR c=603));
} {25 100 201}

finish_test

# 2015 December 10
#
# 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. The focus
# of this file is fault-injection into DELETE statements.
#

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

do_execsql_test 1.0 {
  CREATE TABLE t6(a, b, c, d);
  CREATE INDEX t6a ON t6(a);
  CREATE INDEX t6b ON t6(b);
  CREATE INDEX t6c ON t6(c);
  WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000 ) 
  INSERT INTO t6 SELECT i, i*2, i*3, i%2 FROM s;
}
faultsim_save_and_close

proc deleted_t6_rows {} {
  db eval {
    WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000 ) 
    SELECT i FROM s EXCEPT SELECT a FROM t6 ORDER BY 1;
  }
}

do_faultsim_test 1 -faults oom-t* -prep {
  faultsim_restore_and_reopen
} -body {
  execsql {
    DELETE FROM t6 WHERE (d=0 AND (a=100 OR b=150)) OR (d=1 AND (b=50 OR c=603))
  }
} -test {
  faultsim_test_result {0 {}}
  if {$testrc==0} {
    set lDel [deleted_t6_rows]
    if {$lDel != "25 100 201"} { error "lDel is $lDel" }
  }
}

finish_test

  2 { (x=? OR z=?) AND y=? }   
    { t3* t3* t3x* t3x* t3xy t3xy* t3zx* t3zx* t3zy t3zy* }

  3 { (y=? OR z=?) AND x=? }   { t3 t3x t3xy* t3zx* t3zy* }
} {
  do_adp_test 2.3.$tn "DELETE FROM t3 WHERE $where" $res
}

do_execsql_test 2.4.0 {
  CREATE TABLE t6(a, b, c, d);
  CREATE INDEX t6a ON t6(a);
  CREATE INDEX t6b ON t6(b);
  CREATE INDEX t6c ON t6(c);
}

do_adp_test 2.4.1 {
  DELETE FROM t6 WHERE a=22 OR b=90 OR (d=0 AND (b=400 OR c=303));
} [lsort {
  t6* t6a t6b* t6c*
  t6* t6a* t6b t6c*
  t6 t6a* t6b t6c*
  t6 t6a* t6b* t6c
}]

do_adp_test 2.4.2 {
  DELETE FROM t6 WHERE a=22 OR b=90 OR (d=1 AND (b=400 OR c=303));
} [lsort {
  t6* t6a t6b* t6c*
  t6* t6a* t6b t6c*
  t6 t6a* t6b t6c*
  t6 t6a* t6b* t6c
}]

do_adp_test 2.4.3 {
  DELETE FROM t6 WHERE (d=0 AND (a=100 OR b=150)) OR (d=1 AND (b=50 OR c=603));
} [lsort {
  t6 t6a t6b* t6c*
  t6 t6a* t6b t6c*
  t6 t6a* t6b t6c*
  t6 t6a* t6b* t6c
}]

#-------------------------------------------------------------------------
# Test that a record that consists of the bytes:
#
#   0x01 0x00
#
# is interpreted by OP_Column as a vector of NULL values (assuming the 

  CREATE TABLE t19 AS SELECT * FROM sqlite_master;
  SELECT name FROM t19 ORDER BY name;
} {{} savepoint t10 t11 t12 t13 t16 t2 t3 t3\"xyz t4\"abc t7 t8 t9 tablet8 test1 weird}

# At one point the DROP TABLE in the following was causing problems on an
# experimental branch.
#
reset_db
do_execsql_test table-20.0 {
  CREATE TABLE t20(a PRIMARY KEY, b, c);
  CREATE TRIGGER tr6 AFTER INSERT ON t20 BEGIN SELECT 1+1+1; END;
  ANALYZE;
  DROP TABLE t20;
  SELECT name FROM sqlite_master
} {sqlite_stat1 sqlite_stat4}