1# 2016-03-01 2# 3# The author disclaims copyright to this source code. In place of 4# 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.tcl 16set testprefix bestindex1 17 18register_tcl_module db 19 20proc vtab_command {method args} { 21 switch -- $method { 22 xConnect { 23 return "CREATE TABLE t1(a, b, c)" 24 } 25 26 xBestIndex { 27 set clist [lindex $args 0] 28 if {[llength $clist]!=1} { error "unexpected constraint list" } 29 catch { array unset C } 30 array set C [lindex $clist 0] 31 if {$C(usable)} { 32 return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!" 33 } else { 34 return "cost 1000000 rows 0 idxnum 0 idxstr scan..." 35 } 36 } 37 38 } 39 40 return {} 41} 42 43do_execsql_test 1.0 { 44 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command); 45} {} 46 47do_eqp_test 1.1 { 48 SELECT * FROM x1 WHERE a = 'abc' 49} { 50 0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} 51} 52 53do_eqp_test 1.2 { 54 SELECT * FROM x1 WHERE a IN ('abc', 'def'); 55} { 56 0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} 57 0 0 0 {EXECUTE LIST SUBQUERY 1} 58} 59 60#------------------------------------------------------------------------- 61# 62reset_db 63register_tcl_module db 64 65# Parameter $mode may be one of: 66# 67# "omit" - Implement filtering. Set the omit flag. 68# "use" - Implement filtering. Use the constraint, but do not set omit. 69# "use2" - Do not implement filtering. Use the constraint anyway. 70# 71# 72proc t1_vtab {mode method args} { 73 switch -- $method { 74 xConnect { 75 return "CREATE TABLE t1(a, b)" 76 } 77 78 xBestIndex { 79 set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'} 80 set SQL_SCAN {SELECT * FROM t1x} 81 82 set clist [lindex $args 0] 83 set idx 0 84 for {set idx 0} {$idx < [llength $clist]} {incr idx} { 85 array unset C 86 array set C [lindex $clist $idx] 87 if {$C(column)==0 && $C(op)=="eq" && $C(usable)} { 88 switch -- $mode { 89 "omit" { 90 return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER] 91 } 92 "use" { 93 return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER] 94 } 95 "use2" { 96 return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN] 97 } 98 default { 99 error "Bad mode - $mode" 100 } 101 } 102 } 103 } 104 105 return [list idxstr {SELECT * FROM t1x}] 106 } 107 108 xFilter { 109 set map [list %1% [lindex $args 2 0]] 110 set sql [string map $map [lindex $args 1]] 111 return [list sql $sql] 112 } 113 } 114 115 return {} 116} 117 118do_execsql_test 2.1 { 119 CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b); 120 INSERT INTO t1x VALUES(1, 'one', 1); 121 INSERT INTO t1x VALUES(2, 'two', 2); 122 INSERT INTO t1x VALUES(3, 'three', 3); 123 INSERT INTO t1x VALUES(4, 'four', 4); 124} 125 126foreach {tn mode} { 127 1 use 2 omit 3 use2 128} { 129 do_execsql_test 2.2.$mode.1 " 130 DROP TABLE IF EXISTS t1; 131 CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode); 132 " 133 134 do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4} 135 do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4} 136 do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2} 137 138 do_execsql_test 2.2.$mode.5 { 139 SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid 140 } {1 4} 141 142 set plan(use) {0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}} 143 set plan(use2) {0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}} 144 set plan(omit) { 145 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} 146 0 0 0 {EXECUTE LIST SUBQUERY 1} 147 } 148 149 do_eqp_test 2.2.$mode.6 { 150 SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid 151 } [concat $plan($mode) {0 0 0 {USE TEMP B-TREE FOR ORDER BY}}] 152} 153 154finish_test 155 156 157