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