| 1 |
# 2016 November 11 |
| 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 |
# Test the virtual table interface. In particular the xBestIndex |
| 12 |
# method. |
| 13 |
# |
| 14 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
set testprefix bestindex4 |
| 18 |
|
| 19 |
ifcapable !vtab { |
| 20 |
finish_test |
| 21 |
return |
| 22 |
} |
| 23 |
|
| 24 |
#------------------------------------------------------------------------- |
| 25 |
# Virtual table callback for a virtual table named $tbl. |
| 26 |
# |
| 27 |
# The table created is: |
| 28 |
# |
| 29 |
# "CREATE TABLE t1 (id, host, class)" |
| 30 |
# |
| 31 |
# The virtual table supports == operators on a subset of its columns. The |
| 32 |
# exact subset depends on the value of bitmask paramater $param. |
| 33 |
# |
| 34 |
# 0x01 - == on "id" supported |
| 35 |
# 0x02 - == on "host" supported |
| 36 |
# 0x04 - == on "class" supported |
| 37 |
# |
| 38 |
# $param also supports the following bits: |
| 39 |
# |
| 40 |
# 0x08 - ignore the "usable" flag (malfunction) |
| 41 |
# |
| 42 |
# |
| 43 |
# |
| 44 |
proc vtab_cmd {param method args} { |
| 45 |
switch -- $method { |
| 46 |
xConnect { |
| 47 |
return "CREATE TABLE t1(id TEXT, host TEXT, class TEXT)" |
| 48 |
} |
| 49 |
|
| 50 |
xBestIndex { |
| 51 |
set hdl [lindex $args 0] |
| 52 |
set clist [$hdl constraints] |
| 53 |
set orderby [$hdl orderby] |
| 54 |
set mask [$hdl mask] |
| 55 |
|
| 56 |
set ret [list] |
| 57 |
|
| 58 |
set use use |
| 59 |
|
| 60 |
|
| 61 |
for {set i 0} {$i < [llength $clist]} {incr i} { |
| 62 |
array unset C |
| 63 |
array set C [lindex $clist $i] |
| 64 |
if { ($C(usable) || ($param & 0x08)) |
| 65 |
&& $C(op)=="eq" && ($param & 1<<$C(column)) |
| 66 |
} { |
| 67 |
lappend ret $use $i |
| 68 |
break |
| 69 |
} |
| 70 |
} |
| 71 |
|
| 72 |
set score 1000000 |
| 73 |
if {$ret!=""} { |
| 74 |
set score [expr $score / [llength $ret]] |
| 75 |
} |
| 76 |
lappend ret cost $score rows $score |
| 77 |
|
| 78 |
return $ret |
| 79 |
} |
| 80 |
|
| 81 |
xFilter { |
| 82 |
} |
| 83 |
} |
| 84 |
return "" |
| 85 |
} |
| 86 |
|
| 87 |
register_tcl_module db |
| 88 |
|
| 89 |
for {set param1 0} {$param1<16} {incr param1} { |
| 90 |
for {set param2 0} {$param2<16} {incr param2} { |
| 91 |
reset_db |
| 92 |
register_tcl_module db |
| 93 |
do_execsql_test 1.$param1.$param2.1 " |
| 94 |
CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $param1'); |
| 95 |
CREATE VIRTUAL TABLE t2 USING tcl('vtab_cmd $param2'); |
| 96 |
" |
| 97 |
|
| 98 |
foreach {tn sql} { |
| 99 |
2 "select t1.id as ID from t1, t2 where t1.id=t2.host and t2.class='xx'" |
| 100 |
3 { |
| 101 |
select t1.id as ID from t1, t2 where t2.class ='xx' and t2.id = t1.host |
| 102 |
} |
| 103 |
4 { |
| 104 |
select t1.id as ID from t1, t2 where t1.host = t2.id and t2. class ='xx' |
| 105 |
} |
| 106 |
} { |
| 107 |
|
| 108 |
if {($param1 & 0x08)==0 && ($param2 & 0x08)==0} { |
| 109 |
|
| 110 |
do_execsql_test 1.$param1.$param2.$tn.a $sql {} |
| 111 |
|
| 112 |
} else { |
| 113 |
do_test 1.$param1.$param2.$tn.b { |
| 114 |
catchsql $sql |
| 115 |
set {} {} |
| 116 |
} {} |
| 117 |
} |
| 118 |
} |
| 119 |
|
| 120 |
} |
| 121 |
} |
| 122 |
|
| 123 |
#------------------------------------------------------------------------- |
| 124 |
# Test that a parameter passed to a table-valued function cannot be |
| 125 |
# used to drive an index. i.e. that in the following: |
| 126 |
# |
| 127 |
# SELECT * FROM tbl, vtab(tbl.x); |
| 128 |
# |
| 129 |
# The implicit constraint "tbl.x = vtab.hidden" is not optimized using |
| 130 |
# an index on tbl.x. |
| 131 |
# |
| 132 |
reset_db |
| 133 |
register_tcl_module db |
| 134 |
proc vtab_command {method args} { |
| 135 |
switch -- $method { |
| 136 |
xConnect { |
| 137 |
return "CREATE TABLE t1(a, b, c, d HIDDEN)" |
| 138 |
} |
| 139 |
|
| 140 |
xBestIndex { |
| 141 |
set hdl [lindex $args 0] |
| 142 |
set clist [$hdl constraints] |
| 143 |
set orderby [$hdl orderby] |
| 144 |
set mask [$hdl mask] |
| 145 |
|
| 146 |
if {[llength $clist]!=1} { error "unexpected constraint list" } |
| 147 |
catch { array unset C } |
| 148 |
array set C [lindex $clist 0] |
| 149 |
if {$C(usable)} { |
| 150 |
return [list omit 0 idxnum 555 rows 10 cost 100] |
| 151 |
} |
| 152 |
return [list cost 100000000] |
| 153 |
} |
| 154 |
|
| 155 |
} |
| 156 |
|
| 157 |
return {} |
| 158 |
} |
| 159 |
|
| 160 |
do_execsql_test 2.0 { |
| 161 |
CREATE VIRTUAL TABLE x1 USING tcl(vtab_command); |
| 162 |
CREATE TABLE t1 (x INT PRIMARY KEY); |
| 163 |
} {} |
| 164 |
|
| 165 |
do_eqp_test 2.1 { |
| 166 |
SELECT * FROM t1, x1 WHERE x1.d=t1.x; |
| 167 |
} { |
| 168 |
QUERY PLAN |
| 169 |
|--SCAN x1 VIRTUAL TABLE INDEX 0: |
| 170 |
`--SEARCH t1 USING COVERING INDEX sqlite_autoindex_t1_1 (x=?) |
| 171 |
} |
| 172 |
|
| 173 |
do_eqp_test 2.2 { |
| 174 |
SELECT * FROM t1, x1(t1.x) |
| 175 |
} { |
| 176 |
QUERY PLAN |
| 177 |
|--SCAN t1 |
| 178 |
`--SCAN x1 VIRTUAL TABLE INDEX 555: |
| 179 |
} |
| 180 |
|
| 181 |
|
| 182 |
finish_test |