test/bestindex1.test

# 2016-03-01
#
# 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 bestindex1

register_tcl_module db

proc vtab_command {method args} {
  switch -- $method {
    xConnect {
      return "CREATE TABLE t1(a, b, c)"
    }

    xBestIndex {
      set clist [lindex $args 0]
      if {[llength $clist]!=1} { error "unexpected constraint list" }
      catch { array unset C }
      array set C [lindex $clist 0]
      if {$C(usable)} {
        return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!"
      } else {
        return "cost 1000000 rows 0 idxnum 0 idxstr scan..."
      }
    }

  }

  return {}
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
} {}

do_eqp_test 1.1 {
  SELECT * FROM x1 WHERE a = 'abc'
} {
  0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
}

do_eqp_test 1.2 {
  SELECT * FROM x1 WHERE a IN ('abc', 'def');
} {
  0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
  0 0 0 {EXECUTE LIST SUBQUERY 1}
}

#-------------------------------------------------------------------------
#
reset_db
register_tcl_module db

# Parameter $mode may be one of:
#
#   "omit" - Implement filtering. Set the omit flag.
#   "use"  - Implement filtering. Use the constraint, but do not set omit.
#   "use2" - Do not implement filtering. Use the constraint anyway.
#
#   
proc t1_vtab {mode method args} {
  switch -- $method {
    xConnect {
      return "CREATE TABLE t1(a, b)"
    }

    xBestIndex {
      set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'}
      set SQL_SCAN   {SELECT * FROM t1x}

      set clist [lindex $args 0]
      set idx 0
      for {set idx 0} {$idx < [llength $clist]} {incr idx} {
        array unset C
        array set C [lindex $clist $idx]
        if {$C(column)==0 && $C(op)=="eq" && $C(usable)} {
          switch -- $mode {
            "omit" {
              return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER]
            }
            "use" {
              return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER]
            }
            "use2" {
              return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN]
            }
            default {
              error "Bad mode - $mode"
            }
          }
        }
      }

      return [list idxstr {SELECT * FROM t1x}]
    }

    xFilter {
      set map [list %1% [lindex $args 2 0]]
      set sql [string map $map [lindex $args 1]]
      return [list sql $sql]
    }
  }

  return {}
}

do_execsql_test 2.1 {
  CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b);
  INSERT INTO t1x VALUES(1, 'one', 1);
  INSERT INTO t1x VALUES(2, 'two', 2);
  INSERT INTO t1x VALUES(3, 'three', 3);
  INSERT INTO t1x VALUES(4, 'four', 4);
}

foreach {tn mode} {
  1 use 2 omit 3 use2
} {
  do_execsql_test 2.2.$mode.1 "
    DROP TABLE IF EXISTS t1;
    CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode);
  "

  do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4}
  do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4}
  do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2} 

  do_execsql_test 2.2.$mode.5 {
    SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
  } {1 4} 

  set plan(use) {
    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
    0 0 0 {EXECUTE LIST SUBQUERY 1}
    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
  }
  set plan(omit) {
    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
    0 0 0 {EXECUTE LIST SUBQUERY 1}
    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
  }
  set plan(use2) {
    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}
    0 0 0 {EXECUTE LIST SUBQUERY 1}
    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
  }

  do_eqp_test 2.2.$mode.6 { 
    SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
  } $plan($mode)
}

finish_test