SQLite

  # Test that an OTA database containing no input tables is handled
  # correctly.
  reset_db
  forcedelete ota.db
  do_test $tn3.7 {
    list [catch { run_ota test.db ota.db } msg] $msg
  } {0 SQLITE_DONE}
  
  # Test that OTA can update indexes containing NULL values.
  #
  reset_db
  forcedelete ota.db
  do_execsql_test $tn3.8.1 {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    CREATE INDEX i1 ON t1(b, c);
    INSERT INTO t1 VALUES(1, 1, NULL);
    INSERT INTO t1 VALUES(2, NULL, 2);
    INSERT INTO t1 VALUES(3, NULL, NULL);

    ATTACH 'ota.db' AS ota;
    CREATE TABLE ota.data_t1(a, b, c, ota_control);
    INSERT INTO data_t1 VALUES(1, NULL, NULL, 1);
    INSERT INTO data_t1 VALUES(3, NULL, NULL, 1);
  } {}

  do_test $tn3.8.2 {
    list [catch { run_ota test.db ota.db } msg] $msg
  } {0 SQLITE_DONE}

  do_execsql_test $tn3.8.3 {
    SELECT * FROM t1
  } {2 {} 2}
  do_execsql_test $tn3.8.4 { PRAGMA integrity_check } {ok}

  catch { db close }
  eval $destroy_vfs
}


finish_test

** "=", "<", ">", "<=", ">=", "IN", and "IS NULL"
*/
static int allowedOp(int op){
  assert( TK_GT>TK_EQ && TK_GT<TK_GE );
  assert( TK_LT>TK_EQ && TK_LT<TK_GE );
  assert( TK_LE>TK_EQ && TK_LE<TK_GE );
  assert( TK_GE==TK_EQ+4 );
  return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL || op==TK_IS;
}

/*
** Commute a comparison operator.  Expressions of the form "X op Y"
** are converted into "Y op X".
**
** If left/right precedence rules come into play when determining the

static u16 operatorMask(int op){
  u16 c;
  assert( allowedOp(op) );
  if( op==TK_IN ){
    c = WO_IN;
  }else if( op==TK_ISNULL ){
    c = WO_ISNULL;
  }else if( op==TK_IS ){
    c = WO_IS;
  }else{
    assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff );
    c = (u16)(WO_EQ<<(op-TK_EQ));
  }
  assert( op!=TK_ISNULL || c==WO_ISNULL );
  assert( op!=TK_IN || c==WO_IN );
  assert( op!=TK_EQ || c==WO_EQ );

  int iTarget         /* Attempt to leave results in this register */
){
  Expr *pX = pTerm->pExpr;
  Vdbe *v = pParse->pVdbe;
  int iReg;                  /* Register holding results */

  assert( iTarget>0 );
  if( pX->op==TK_EQ || pX->op==TK_IS ){
    iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);
  }else if( pX->op==TK_ISNULL ){
    iReg = iTarget;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE_OMIT_SUBQUERY
  }else{
    int eType;

        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
      }
    }
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_IN );
    if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
      Expr *pRight = pTerm->pExpr->pRight;
      if( sqlite3ExprCanBeNull(pRight) && pTerm->eOperator!=WO_IS ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
        VdbeCoverage(v);
      }
      if( zAff ){
        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_NONE ){
          zAff[j] = SQLITE_AFF_NONE;
        }

    pLoop->aLTerm[0] = pTerm;
    pLoop->nLTerm = 1;
    pLoop->u.btree.nEq = 1;
    /* TUNING: Cost of a rowid lookup is 10 */
    pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  }else{
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      u32 mask = WO_EQ;
      assert( pLoop->aLTermSpace==pLoop->aLTerm );
      if( !IsUniqueIndex(pIdx)
       || pIdx->pPartIdxWhere!=0 
       || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
      ) continue;
      if( HasRowid(pTab)==0 && IsPrimaryKeyIndex(pIdx) ) mask |= WO_IS;
      for(j=0; j<pIdx->nKeyCol; j++){
        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, mask, pIdx);
        if( pTerm==0 ) break;
        pLoop->aLTerm[j] = pTerm;
      }
      if( j!=pIdx->nKeyCol ) continue;
      pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
      if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
        pLoop->wsFlags |= WHERE_IDX_ONLY;

#define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
#define WO_MATCH  0x040
#define WO_ISNULL 0x080
#define WO_OR     0x100       /* Two or more OR-connected terms */
#define WO_AND    0x200       /* Two or more AND-connected terms */
#define WO_EQUIV  0x400       /* Of the form A==B, both columns */
#define WO_NOOP   0x800       /* This term does not restrict search space */
#define WO_IS     0x1000      /* The IS operator */

#define WO_ALL    0x1fff      /* Mask of all possible WO_* values */
#define WO_SINGLE 0x0ff       /* Mask of all non-compound WO_* values */

/*
** These are definitions of bits in the WhereLoop.wsFlags field.
** The particular combination of bits in each WhereLoop help to
** determine the algorithm that WhereLoop represents.
*/

# 2015-03-19
#
# 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
set ::testprefix whereK

do_execsql_test 1.0 {
  CREATE TABLE t1(v, w TEXT, x, PRIMARY KEY(w, x)) WITHOUT ROWID;

  INSERT INTO t1 VALUES('a', 1, 1);
  INSERT INTO t1 VALUES('B', 2, 2);
  INSERT INTO t1 VALUES('C', 3, 3);
  INSERT INTO t1 VALUES('d', 4, 4);

  CREATE TABLE t2(v, w TEXT, x, PRIMARY KEY(w, x));

  CREATE TABLE t3(a, b, c);
  CREATE UNIQUE INDEX t3a ON t3(a);
}

do_eqp_test 1.1.1 {
  SELECT v FROM t1 WHERE w IS ? AND x IS ?
} {/SEARCH TABLE t1.*(w=. AND x=.)/}

do_eqp_test 1.1.2 {
  SELECT v FROM t2 WHERE w IS ? AND x IS ?
} {/SCAN TABLE t2/}

do_execsql_test 1.2.1 { SELECT v FROM t1 WHERE w IS 1   AND x IS 1 }   {a}
do_execsql_test 1.2.2 { SELECT v FROM t1 WHERE w IS '2' AND x IS 2 }   {B}
do_execsql_test 1.2.3 { SELECT v FROM t1 WHERE w IS 3   AND x IS '3' } {}
do_execsql_test 1.2.4 { SELECT v FROM t1 WHERE w IS '4' AND x IS '4' } {}
  

# IS constraints may only be used if all fields of the index are 
# constrained by either "IS" or "=".
#
do_eqp_test 1.3.1 {
    SELECT v FROM t1 WHERE w IS ?
} {/SCAN TABLE t1/}
do_eqp_test 1.3.2 {
    SELECT v FROM t1 WHERE w IS ? AND x > ?
} {/SCAN TABLE t1/}

do_execsql_test 1.4.1 { SELECT v FROM t1 WHERE w IS 3   AND x = 3 } {C}
do_execsql_test 1.4.2 { SELECT v FROM t1 WHERE w = '4' AND x IS 4 } {d}

do_eqp_test 1.5.1 { SELECT b FROM t3 WHERE a IS ? } {/SCAN TABLE/}
do_eqp_test 1.5.2 { SELECT b FROM t3 WHERE a = ? } {/SEARCH TABLE/}

finish_test