gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/bestindex1.test (about) 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 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set testprefix bestindex1 17 18 ifcapable !vtab { 19 finish_test 20 return 21 } 22 23 register_tcl_module db 24 25 proc 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 51 do_execsql_test 1.0 { 52 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command); 53 } {} 54 55 do_eqp_test 1.1 { 56 SELECT * FROM x1 WHERE a = 'abc' 57 } {SCAN x1 VIRTUAL TABLE INDEX 555:eq!} 58 59 do_eqp_test 1.2 { 60 SELECT * FROM x1 WHERE a IN ('abc', 'def'); 61 } {SCAN x1 VIRTUAL TABLE INDEX 555:eq!} 62 63 #------------------------------------------------------------------------- 64 # 65 reset_db 66 register_tcl_module db 67 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 # 75 proc 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 0 90 for {set idx 0} {$idx < [llength $clist]} {incr idx} { 91 array unset C 92 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 124 do_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 132 foreach {tn mode} { 133 1 use 2 omit 3 use2 134 } { 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 +rowid 146 } {1 4} 147 148 set plan(use) { 149 QUERY PLAN 150 |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%' 151 `--USE TEMP B-TREE FOR ORDER BY 152 } 153 set plan(omit) { 154 QUERY PLAN 155 |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%' 156 `--USE TEMP B-TREE FOR ORDER BY 157 } 158 set plan(use2) { 159 QUERY PLAN 160 |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x 161 `--USE TEMP B-TREE FOR ORDER BY 162 } 163 164 do_eqp_test 2.2.$mode.6 { 165 SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid 166 } [string map {"\n " "\n"} $plan($mode)] 167 } 168 169 # 2016-04-09. 170 # Demonstrate a register overwrite problem when using two virtual 171 # tables where the outer loop uses the IN operator. 172 # 173 set G(collist) [list PrimaryKey flagA columnA] 174 set G(cols) [join $G(collist) ,] 175 set G(nulls) "NULL" 176 177 proc vtab_command {method args} { 178 global G 179 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 $clist 191 set W [list] 192 set U [list] 193 194 set i 0 195 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 $idx 200 incr i 201 } 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 236 db close 237 forcedelete test.db 238 sqlite3 db test.db 239 register_tcl_module db 240 241 do_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 249 do_execsql_test 3.2 { 250 CREATE VIRTUAL TABLE VirtualTableA USING tcl(vtab_command); 251 CREATE VIRTUAL TABLE VirtualTableB USING tcl(vtab_command); 252 } 253 254 do_execsql_test 3.3 { SELECT primarykey FROM VirtualTableA } {1 2 3 4} 255 256 do_execsql_test 3.4 { 257 SELECT * FROM 258 VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey 259 WHERE a.ColumnA IN ('ValueA', 'ValueB') AND a.FlagA=0 260 } { 261 1 0 ValueA 1 0 ValueA 262 2 0 ValueA 2 0 ValueA 263 3 0 ValueB 3 0 ValueB 264 4 0 ValueB 4 0 ValueB 265 } 266 267 do_execsql_test 3.5 { 268 SELECT * FROM 269 VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey 270 WHERE a.FlagA=0 AND a.ColumnA IN ('ValueA', 'ValueB') 271 } { 272 1 0 ValueA 1 0 ValueA 273 2 0 ValueA 2 0 ValueA 274 3 0 ValueB 3 0 ValueB 275 4 0 ValueB 4 0 ValueB 276 } 277 278 #------------------------------------------------------------------------- 279 # If there is an IN(..) condition in the WHERE clause of a query on a 280 # virtual table, the xBestIndex method is first invoked with the IN(...) 281 # represented by a "usable" SQLITE_INDEX_CONSTRAINT_EQ constraint. If 282 # the virtual table elects to use the IN(...) constraint, then the 283 # xBestIndex method is invoked again, this time with the IN(...) marked 284 # as "not usable". Depending on the relative costs of the two plans as 285 # defined by the virtual table implementation, and the cardinality of the 286 # IN(...) operator, SQLite chooses the most efficient plan. 287 # 288 # At one point the second invocation of xBestIndex() was only being made 289 # for join queries. The following tests check that this problem has been 290 # fixed. 291 # 292 proc 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 $clist 304 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 $ret 312 } 313 } 314 return {} 315 } 316 317 do_execsql_test 4.0 { 318 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command); 319 } {} 320 321 do_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_calls 327 } [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 340 do_catchsql_test 5.0 { 341 SELECT * FROM tcl('abc'); 342 } {1 {wrong number of arguments}} 343 344 finish_test