AryaWu/sqlite
0
1# 2016-03-012#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# 12#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix bestindex117 18ifcapable !vtab {19 finish_test20 return21}22 23register_tcl_module db24 25proc vtab_command {method args} {26 switch -- $method {27 xConnect {28 return "CREATE TABLE t1(a, b, c)"29 }30 31 xBestIndex {32 set hdl [lindex $args 0]33 set clist [$hdl constraints]34 set orderby [$hdl orderby]35 36 if {[llength $clist]!=1} { error "unexpected constraint list" }37 catch { array unset C }38 array set C [lindex $clist 0]39 if {$C(usable)} {40 return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!"41 } else {42 return "cost 1000000 rows 0 idxnum 0 idxstr scan..."43 }44 }45 46 }47 48 return {}49}50 51do_execsql_test 1.0 {52 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);53} {}54 55do_eqp_test 1.1 {56 SELECT * FROM x1 WHERE a = 'abc'57} {SCAN x1 VIRTUAL TABLE INDEX 555:eq!}58 59do_eqp_test 1.2 {60 SELECT * FROM x1 WHERE a IN ('abc', 'def');61} {SCAN x1 VIRTUAL TABLE INDEX 555:eq!}62 63#-------------------------------------------------------------------------64#65reset_db66register_tcl_module db67 68# Parameter $mode may be one of:69#70# "omit" - Implement filtering. Set the omit flag.71# "use" - Implement filtering. Use the constraint, but do not set omit.72# "use2" - Do not implement filtering. Use the constraint anyway.73#74# 75proc t1_vtab {mode method args} {76 switch -- $method {77 xConnect {78 return "CREATE TABLE t1(a, b)"79 }80 81 xBestIndex {82 set hdl [lindex $args 0]83 set clist [$hdl constraints]84 set orderby [$hdl orderby]85 86 set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'}87 set SQL_SCAN {SELECT * FROM t1x}88 89 set idx 090 for {set idx 0} {$idx < [llength $clist]} {incr idx} {91 array unset C92 array set C [lindex $clist $idx]93 if {$C(column)==0 && $C(op)=="eq" && $C(usable)} {94 switch -- $mode {95 "omit" {96 return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER]97 }98 "use" {99 return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER]100 }101 "use2" {102 return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN]103 }104 default {105 error "Bad mode - $mode"106 }107 }108 }109 }110 111 return [list idxstr {SELECT * FROM t1x}]112 }113 114 xFilter {115 set map [list %1% [lindex $args 2 0]]116 set sql [string map $map [lindex $args 1]]117 return [list sql $sql]118 }119 }120 121 return {}122}123 124do_execsql_test 2.1 {125 CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b);126 INSERT INTO t1x VALUES(1, 'one', 1);127 INSERT INTO t1x VALUES(2, 'two', 2);128 INSERT INTO t1x VALUES(3, 'three', 3);129 INSERT INTO t1x VALUES(4, 'four', 4);130}131 132foreach {tn mode} {133 1 use 2 omit 3 use2134} {135 do_execsql_test 2.2.$mode.1 "136 DROP TABLE IF EXISTS t1;137 CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode);138 "139 140 do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4}141 do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4}142 do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2} 143 144 do_execsql_test 2.2.$mode.5 {145 SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid146 } {1 4} 147 148 set plan(use) {149 QUERY PLAN150 |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'151 `--USE TEMP B-TREE FOR ORDER BY152 }153 set plan(omit) {154 QUERY PLAN155 |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'156 `--USE TEMP B-TREE FOR ORDER BY157 }158 set plan(use2) {159 QUERY PLAN160 |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x161 `--USE TEMP B-TREE FOR ORDER BY162 }163 164 do_eqp_test 2.2.$mode.6 { 165 SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid166 } [string map {"\n " "\n"} $plan($mode)]167}168 169# 2016-04-09.170# Demonstrate a register overwrite problem when using two virtual171# tables where the outer loop uses the IN operator.172#173set G(collist) [list PrimaryKey flagA columnA]174set G(cols) [join $G(collist) ,]175set G(nulls) "NULL"176 177proc vtab_command {method args} {178 global G179 180 switch -- $method {181 xConnect {182 return "CREATE TABLE t1($G(cols))"183 }184 185 xBestIndex {186 set hdl [lindex $args 0]187 set clist [$hdl constraints]188 set orderby [$hdl orderby]189 190 #puts $clist191 set W [list]192 set U [list]193 194 set i 0195 for {set idx 0} {$idx < [llength $clist]} {incr idx} {196 array set c [lindex $clist $idx]197 if {$c(op)=="eq" && $c(usable)} {198 lappend W "[lindex $G(collist) $c(column)] = %$i%"199 lappend U use $idx200 incr i201 }202 }203 204 if {$W==""} {205 set sql "SELECT rowid, * FROM t1"206 } else {207 set sql "SELECT rowid, * FROM t1 WHERE [join $W { AND }]"208 }209 210 return [concat [list idxstr $sql] $U]211 }212 213 xFilter {214 foreach {idxnum idxstr vals} $args {}215 216 set map [list]217 for {set i 0} {$i < [llength $vals]} {incr i} {218 lappend map "%$i%" 219 set v [lindex $vals $i]220 if {[string is integer $v]} { 221 lappend map $v 222 } else {223 lappend map "'$v'"224 }225 }226 set sql [string map $map $idxstr]227 228 #puts "SQL: $sql"229 return [list sql $sql]230 }231 }232 233 return {}234}235 236db close237forcedelete test.db238sqlite3 db test.db239register_tcl_module db240 241do_execsql_test 3.1 "242 CREATE TABLE t1($G(cols));243 INSERT INTO t1 VALUES(1, 0, 'ValueA');244 INSERT INTO t1 VALUES(2, 0, 'ValueA');245 INSERT INTO t1 VALUES(3, 0, 'ValueB');246 INSERT INTO t1 VALUES(4, 0, 'ValueB');247"248 249do_execsql_test 3.2 {250 CREATE VIRTUAL TABLE VirtualTableA USING tcl(vtab_command);251 CREATE VIRTUAL TABLE VirtualTableB USING tcl(vtab_command);252}253 254do_execsql_test 3.3 { SELECT primarykey FROM VirtualTableA } {1 2 3 4}255 256do_execsql_test 3.4 {257 SELECT * FROM 258 VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey259 WHERE a.ColumnA IN ('ValueA', 'ValueB') AND a.FlagA=0260} {261 1 0 ValueA 1 0 ValueA262 2 0 ValueA 2 0 ValueA263 3 0 ValueB 3 0 ValueB264 4 0 ValueB 4 0 ValueB265}266 267do_execsql_test 3.5 {268 SELECT * FROM 269 VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey270 WHERE a.FlagA=0 AND a.ColumnA IN ('ValueA', 'ValueB') 271} {272 1 0 ValueA 1 0 ValueA273 2 0 ValueA 2 0 ValueA274 3 0 ValueB 3 0 ValueB275 4 0 ValueB 4 0 ValueB276}277 278#-------------------------------------------------------------------------279# If there is an IN(..) condition in the WHERE clause of a query on a280# virtual table, the xBestIndex method is first invoked with the IN(...)281# represented by a "usable" SQLITE_INDEX_CONSTRAINT_EQ constraint. If282# the virtual table elects to use the IN(...) constraint, then the 283# xBestIndex method is invoked again, this time with the IN(...) marked284# as "not usable". Depending on the relative costs of the two plans as285# defined by the virtual table implementation, and the cardinality of the286# IN(...) operator, SQLite chooses the most efficient plan. 287#288# At one point the second invocation of xBestIndex() was only being made289# for join queries. The following tests check that this problem has been290# fixed.291#292proc vtab_command {method args} {293 switch -- $method {294 xConnect {295 return "CREATE TABLE t1(a, b, c, d)"296 }297 298 xBestIndex {299 set hdl [lindex $args 0]300 set clist [$hdl constraints]301 set orderby [$hdl orderby]302 303 lappend ::bestindex_calls $clist304 set ret "cost 1000000 idxnum 555"305 for {set i 0} {$i < [llength $clist]} {incr i} {306 array set C [lindex $clist $i]307 if {$C(usable)} { 308 lappend ret use $i 309 }310 }311 return $ret312 }313 }314 return {}315}316 317do_execsql_test 4.0 {318 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);319} {}320 321do_test 4.1 {322 set ::bestindex_calls [list]323 execsql {324 SELECT * FROM x1 WHERE a=? AND b BETWEEN ? AND ? AND c IN (1, 2, 3, 4);325 }326 set ::bestindex_calls327} [list \328 [list {op eq column 0 usable 1} \329 {op eq column 2 usable 1} \330 {op ge column 1 usable 1} \331 {op le column 1 usable 1} \332 ] \333 [list {op eq column 0 usable 1} \334 {op eq column 2 usable 0} \335 {op ge column 1 usable 1} \336 {op le column 1 usable 1}337 ]338]339 340do_catchsql_test 5.0 {341 SELECT * FROM tcl('abc');342} {1 {wrong number of arguments}}343 344finish_test345