gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/bestindex2.test (about) 1 # 2016 March 3 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 set testdir [file dirname $argv0] 13 source $testdir/tester.tcl 14 set testprefix bestindex2 15 16 ifcapable !vtab { 17 finish_test 18 return 19 } 20 21 #------------------------------------------------------------------------- 22 # Virtual table callback for table named $tbl, with the columns specified 23 # by list argument $cols. e.g. if the function is invoked as: 24 # 25 # vtab_cmd t1 {a b c} ... 26 # 27 # The table created is: 28 # 29 # "CREATE TABLE t1 (a, b, c)" 30 # 31 # The tables xBestIndex method behaves as if all possible combinations of 32 # "=" constraints (but no others) may be optimized. The cost of a full table 33 # scan is: 34 # 35 # "WHERE 1" "cost 1000000 rows 1000000" 36 # 37 # If one or more "=" constraints are in use, the cost and estimated number 38 # of rows returned are both is (11 - nCons)*1000, where nCons is the number 39 # of constraints used. e.g. 40 # 41 # "WHERE a=? AND b=?" -> "cost 900 rows 900" 42 # "WHERE c=? AND b<?" -> "cost 1000 rows 1000" 43 # 44 proc vtab_cmd {tbl cols method args} { 45 switch -- $method { 46 xConnect { 47 return "CREATE TABLE $tbl ([join $cols ,])" 48 } 49 xBestIndex { 50 set hdl [lindex $args 0] 51 set clist [$hdl constraints] 52 set orderby [$hdl orderby] 53 set mask [$hdl mask] 54 55 set cons [list] 56 set used [list] 57 58 for {set i 0} {$i < [llength $clist]} {incr i} { 59 array unset C 60 array set C [lindex $clist $i] 61 if {$C(op)=="eq" && $C(usable) && [lsearch $cons $C(column)]<0} { 62 lappend used use $i 63 lappend cons $C(column) 64 } 65 } 66 67 set nCons [llength $cons] 68 if {$nCons==0} { 69 return "cost 1000000 rows 1000000" 70 } else { 71 set cost [expr (11-$nCons) * 1000] 72 set ret [concat $used "cost $cost rows $cost"] 73 74 set txt [list] 75 foreach c $cons { lappend txt "[lindex $cols $c]=?" } 76 lappend ret idxstr "indexed([join $txt { AND }])" 77 78 return $ret 79 } 80 } 81 } 82 return "" 83 } 84 85 register_tcl_module db 86 87 do_execsql_test 1.0 { 88 CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd t1 {a b}"); 89 CREATE VIRTUAL TABLE t2 USING tcl("vtab_cmd t2 {c d}"); 90 CREATE VIRTUAL TABLE t3 USING tcl("vtab_cmd t3 {e f}"); 91 } 92 93 do_eqp_test 1.1 { 94 SELECT * FROM t1 WHERE a='abc' 95 } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=?)} 96 97 do_eqp_test 1.2 { 98 SELECT * FROM t1 WHERE a='abc' AND b='def' 99 } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)} 100 101 do_eqp_test 1.3 { 102 SELECT * FROM t1 WHERE a='abc' AND a='def' 103 } {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=?)} 104 105 do_eqp_test 1.4 { 106 SELECT * FROM t1,t2 WHERE c=a 107 } { 108 QUERY PLAN 109 |--SCAN t1 VIRTUAL TABLE INDEX 0: 110 `--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?) 111 } 112 113 do_eqp_test 1.5 { 114 SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d 115 } { 116 QUERY PLAN 117 |--SCAN t1 VIRTUAL TABLE INDEX 0: 118 |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?) 119 `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?) 120 } 121 122 do_eqp_test 1.6 { 123 SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d 124 } { 125 QUERY PLAN 126 |--SCAN t1 VIRTUAL TABLE INDEX 0: 127 |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?) 128 `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?) 129 } 130 131 do_execsql_test 1.7.1 { 132 CREATE TABLE x1(a, b); 133 } 134 do_eqp_test 1.7.2 { 135 SELECT * FROM x1 CROSS JOIN t1, t2, t3 136 WHERE t1.a = t2.c AND t1.b = t3.e 137 } { 138 QUERY PLAN 139 |--SCAN x1 140 |--SCAN t1 VIRTUAL TABLE INDEX 0: 141 |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?) 142 `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?) 143 } 144 145 finish_test