AryaWu/sqlite

1# 2016 May 292#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 12set testdir [file dirname $argv0]13source $testdir/tester.tcl14set testprefix bestindex315 16ifcapable !vtab {17  finish_test18  return19}20 21#-------------------------------------------------------------------------22# Virtual table callback for a virtual table named $tbl.23#24# The table created is:25#26#      "CREATE TABLE t1 (a, b, c)"27#28# This virtual table supports both LIKE and = operators on all columns.29#  30proc vtab_cmd {bOmit method args} {31  switch -- $method {32    xConnect {33      return "CREATE TABLE t1(a, b, c)"34    }35 36    xBestIndex {37      set hdl [lindex $args 0]38      set clist [$hdl constraints]39      set orderby [$hdl orderby]40      set mask [$hdl mask]41 42      set ret [list]43      set use use44      if {$bOmit} {set use omit}45 46      for {set i 0} {$i < [llength $clist]} {incr i} {47        array unset C48        array set C [lindex $clist $i]49        if {$C(usable) && ($C(op)=="like" || $C(op)=="eq")} {50          lappend ret $use $i51          lappend ret idxstr 52          lappend ret "[lindex {a b c} $C(column)] [string toupper $C(op)] ?"53          break54        }55      }56 57      if {$ret==""} {58        lappend ret cost 1000000 rows 100000059      } else {60        lappend ret cost 100 rows 1061      }62      return $ret63    }64 65    xFilter {66      foreach {idxnum idxstr param} $args {}67      set where ""68      if {$bOmit && $idxstr != ""} {69        set where " WHERE [string map [list ? '$param' EQ =] $idxstr]"70      }71      return [list sql "SELECT rowid, * FROM ttt$where"]72    }73  }74  return ""75}76 77register_tcl_module db78 79do_execsql_test 1.0 {80  CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0");81}82 83do_eqp_test 1.1 {84  SELECT * FROM t1 WHERE a LIKE 'abc';85} {SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?}86 87do_eqp_test 1.2 {88  SELECT * FROM t1 WHERE a = 'abc';89} {SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?}90 91do_eqp_test 1.3 {92  SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';93} {94  QUERY PLAN95  `--MULTI-INDEX OR96     |--INDEX 197     |  `--SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?98     `--INDEX 299        `--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ?100}101 102do_eqp_test 1.4 {103  SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';104} {105  QUERY PLAN106  `--MULTI-INDEX OR107     |--INDEX 1108     |  `--SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?109     `--INDEX 2110        `--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ?111}112 113do_execsql_test 1.5 {114  CREATE TABLE ttt(a, b, c);115 116  INSERT INTO ttt VALUES(1, 'two',   'three');117  INSERT INTO ttt VALUES(2, 'one',   'two');118  INSERT INTO ttt VALUES(3, 'three', 'one');119  INSERT INTO ttt VALUES(4, 'y',     'one');120  INSERT INTO ttt VALUES(5, 'x',     'two');121  INSERT INTO ttt VALUES(6, 'y',     'three');122}123 124foreach omit {0 1} {125  do_execsql_test 1.6.$omit.0 "126    DROP TABLE t1;127    CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $omit');128  "129  do_execsql_test 1.6.$omit.1 { 130    SELECT rowid FROM t1 WHERE c LIKE 'o%'131  } {3 4}132 133  do_execsql_test 1.6.$omit.2 { 134    SELECT rowid FROM t1 WHERE c LIKE 'o%' OR b='y'135  } {3 4 6}136 137  do_execsql_test 1.6.$omit.3 { 138    SELECT rowid FROM t1 WHERE c = 'three' OR c LIKE 'o%'139  } {1 6 3 4}140}141 142#-------------------------------------------------------------------------143# Test the same pattern works with ordinary tables.144#145# This test does not work if the ICU extension is enabled. ICU overrides146# LIKE - and this optimization only works with the built-in LIKE function.147#148ifcapable !icu {149  do_execsql_test 2.1 {150    CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT);151    CREATE INDEX t2x ON t2(x COLLATE nocase);152    CREATE INDEX t2y ON t2(y);153  }154 155  do_eqp_test 2.2 {156    SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'157  } [string map {"\n  " \n} {158    QUERY PLAN159    `--MULTI-INDEX OR160       |--INDEX 1161       |  `--SEARCH t2 USING INDEX t2x (x>? AND x<?)162       `--INDEX 2163          `--SEARCH t2 USING INDEX t2y (y=?)164  }]165}166 167#-------------------------------------------------------------------------168# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE 169# statement is currently ignored.170#171proc vvv_command {method args} {172  switch -- $method {173    xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" }174  }175}176proc yyy_command {method args} {177  switch -- $method {178    xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" }179  }180}181 182do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') }183do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') }184 185finish_test186