Debian Codegraph @

test/bestindex1.test

raw
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