AryaWu/sqlite

1# 2016 November 112#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# Test the virtual table interface. In particular the xBestIndex12# method.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix bestindex418 19ifcapable !vtab {20  finish_test21  return22}23 24#-------------------------------------------------------------------------25# Virtual table callback for a virtual table named $tbl.26#27# The table created is:28#29#      "CREATE TABLE t1 (id, host, class)"30#31# The virtual table supports == operators on a subset of its columns. The32# exact subset depends on the value of bitmask paramater $param.33#34#   0x01 - == on "id" supported35#   0x02 - == on "host" supported36#   0x04 - == on "class" supported37#38# $param also supports the following bits:39#40#   0x08 - ignore the "usable" flag (malfunction)41#42#43#  44proc vtab_cmd {param method args} {45  switch -- $method {46    xConnect {47      return "CREATE TABLE t1(id TEXT, host TEXT, class TEXT)"48    }49 50    xBestIndex {51      set hdl [lindex $args 0]52      set clist [$hdl constraints]53      set orderby [$hdl orderby]54      set mask [$hdl mask]55 56      set ret [list]57 58      set use use59 60 61      for {set i 0} {$i < [llength $clist]} {incr i} {62        array unset C63        array set C [lindex $clist $i]64        if { ($C(usable) || ($param & 0x08)) 65          && $C(op)=="eq" && ($param & 1<<$C(column))66        } {67          lappend ret $use $i68          break69        }70      }71 72      set score 100000073      if {$ret!=""} {74        set score [expr $score / [llength $ret]]75      }76      lappend ret cost $score rows $score77 78      return $ret79    }80 81    xFilter {82    }83  }84  return ""85}86 87register_tcl_module db88 89for {set param1 0} {$param1<16} {incr param1} {90  for {set param2 0} {$param2<16} {incr param2} {91    reset_db92    register_tcl_module db93    do_execsql_test 1.$param1.$param2.1 "94      CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $param1');95      CREATE VIRTUAL TABLE t2 USING tcl('vtab_cmd $param2');96    "97 98    foreach {tn sql} {99      2 "select t1.id as ID from t1, t2 where t1.id=t2.host and t2.class='xx'"100      3 {101        select t1.id as ID from t1, t2 where t2.class ='xx' and t2.id = t1.host102      }103      4 {104        select t1.id as ID from t1, t2 where t1.host = t2.id and t2. class ='xx'105      }106    } {107 108      if {($param1 & 0x08)==0 && ($param2 & 0x08)==0} {109 110        do_execsql_test 1.$param1.$param2.$tn.a $sql {}111 112      } else {113        do_test 1.$param1.$param2.$tn.b {114          catchsql $sql115            set {} {}116        } {}117      }118    }119 120  }121}122 123#-------------------------------------------------------------------------124# Test that a parameter passed to a table-valued function cannot be125# used to drive an index. i.e. that in the following:126#127#   SELECT * FROM tbl, vtab(tbl.x);128#129# The implicit constraint "tbl.x = vtab.hidden" is not optimized using130# an index on tbl.x.131#132reset_db133register_tcl_module db134proc vtab_command {method args} {135  switch -- $method {136    xConnect {137      return "CREATE TABLE t1(a, b, c, d HIDDEN)"138    }139 140    xBestIndex {141      set hdl [lindex $args 0]142      set clist [$hdl constraints]143      set orderby [$hdl orderby]144      set mask [$hdl mask]145      146      if {[llength $clist]!=1} { error "unexpected constraint list" }147      catch { array unset C }148      array set C [lindex $clist 0]149      if {$C(usable)} {150        return [list omit 0 idxnum 555 rows 10 cost 100]151      }152      return [list cost 100000000]153    }154 155  }156 157  return {}158}159 160do_execsql_test 2.0 {161  CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);162  CREATE TABLE t1 (x INT PRIMARY KEY);163} {}164 165do_eqp_test 2.1 {166  SELECT * FROM t1, x1 WHERE x1.d=t1.x;167} {168  QUERY PLAN169  |--SCAN x1 VIRTUAL TABLE INDEX 0:170  `--SEARCH t1 USING COVERING INDEX sqlite_autoindex_t1_1 (x=?)171}172 173do_eqp_test 2.2 {174  SELECT * FROM t1, x1(t1.x)175} {176  QUERY PLAN177  |--SCAN t1178  `--SCAN x1 VIRTUAL TABLE INDEX 555:179}180 181 182finish_test183