SQLite

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.352 2008/12/30 12:00:12 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)

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{
    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 );
  assert( op!=TK_LT || c==WO_LT );
  assert( op!=TK_LE || c==WO_LE );
  assert( op!=TK_GT || c==WO_GT );
  assert( op!=TK_GE || c==WO_GE );
  return c;

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# is testing of where.c. More specifically, the focus is the optimization
# of WHERE clauses that feature the OR operator.
#
# $Id: where8.test,v 1.2 2008/12/30 12:00:12 danielk1977 Exp $

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

# Test organization:
#
#   where8-1.*: Tests to demonstrate simple cases work with a single table

proc execsql_status {sql {db db}} {
  set result [uplevel $db eval [list $sql]]
  concat $result [db status step] [db status sort]
}

proc execsql_status2 {sql {db db}} {
  set ::sqlite_search_count 0

  set result [uplevel [list execsql_status $sql $db]]
  concat $result $::sqlite_search_count
}

do_test where8-1.1 {
  execsql {
    CREATE TABLE t1(a, b, c);

} {I II III V 0 0 14}

do_test where8-1.12.2 {
  execsql_status2 { 
    SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5
  }
} {I II III V 9 0 9}

do_test where8-1.13 {
  execsql_status2 {
    SELECT c FROM t1
    WHERE a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6
    ORDER BY rowid
  }
} {II III IV V VI 0 0 15}
do_test where8-1.14 {
  execsql_status2 {
    SELECT c FROM t1
    WHERE 
      a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR
      b = 'seven' OR a = 8 OR b = 'nine' OR a = 10
    ORDER BY rowid
  }
} {II III IV V VI VII VIII IX X 0 0 26}

do_test where8-1.15 {
  execsql_status2 {
    SELECT c FROM t1 WHERE 
      a BETWEEN 2 AND 4 OR b = 'nine'
    ORDER BY rowid
  }
} {II III IV IX 0 0 10}



#--------------------------------------------------------------------------
# Tests where8-2.*: Virtual tables
# 

if 0 {

    FROM t1, t2 
    WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen')
    ORDER BY t1.rowid
  }
} {2 2 2 4 3 3 3 4 0 0}

do_test where8-3.9 {
  # The "OR c = 'IX'" term forces a linear scan.
  execsql_status {
    SELECT a, d 
    FROM t1, t2 
    WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen')
    ORDER BY t1.rowid
  }
} {2 2 2 4 3 3 3 4 9 4 9 9 9 0}

do_test where8-3.10 {
  execsql_status {
    SELECT d FROM t2 WHERE e IS NULL OR e = 'four'
  }
} {1 2 3 5 10 0 0}

do_test where8-3.11 {
  execsql_status {
    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a
  }
} {1 1 2 2 3 3 4 2 4 4 0 0}
do_test where8-3.12 {
  execsql_status {
    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a
  }
} {1 1 2 2 3 3 4 2 4 4 0 0}
do_test where8-3.13 {
  execsql_status {
    SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5
  }
} {1 1 2 2 3 3 4 2 4 4 9 0}

do_test where8-3.14 {
  execsql_status {
    SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5
  }
} {IV V 9 0}

do_test where8-3.15 {
  execsql_status {
    SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (
      SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d
    )
  }
} {I I I I I I I I I I II II II II II II II II II II III III III III III 99 0}

#-----------------------------------------------------------------------
# The following tests - where8-4.* - verify that adding or removing 
# indexes does not change the results returned by various queries.
#
do_test where8-4.1 {
  execsql {
    CREATE TABLE t3(a INTEGER, b REAL, c TEXT);
    CREATE TABLE t4(f INTEGER, g REAL, h TEXT);
  }
} {}

finish_test

# 2008 December 23
#
# 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
# is testing of where.c. More specifically, the focus is the optimization
# of WHERE clauses that feature the OR operator.
#
# $Id: where8m.test,v 1.1 2008/12/30 12:00:12 danielk1977 Exp $

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

source $testdir/malloc_common.tcl

do_malloc_test where8m-1 -sqlprep {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX i1 ON t1(a);
  CREATE INDEX i2 ON t1(b);
} -sqlbody {
  SELECT c FROM t1
  WHERE 
    a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR
    b = 'seven' OR a = 8 OR b = 'nine' OR a = 10
  ORDER BY rowid;

  SELECT c FROM t1 WHERE
    a = 1 OR a = 2 OR a = 3 OR a = 4 OR a = 5 OR a = 6;

  SELECT c FROM t1 WHERE
    a BETWEEN 1 AND 3  AND b < 5 AND b > 2 AND c = 4;
}

finish_test