Debian Codegraph @

test/bestindex8.test

raw
1 # 2020-01-29
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 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix bestindex8
16
17 ifcapable !vtab {
18 finish_test
19 return
20 }
21
22 register_tcl_module db
23
24 proc vtab_command {src method args} {
25 switch -- $method {
26 xConnect {
27 return "CREATE TABLE xxx(a, b)"
28 }
29
30 xBestIndex {
31 set hdl [lindex $args 0]
32 set clist [$hdl constraints]
33 set orderby [$hdl orderby]
34 lappend ::lBestIndexDistinct [$hdl distinct]
35
36 #puts "ORDERBY: $orderby"
37 set iCons 0
38 set ret [list]
39 foreach cons $clist {
40 catch { array unset C }
41 array set C $cons
42 if {$C(usable)} {
43 lappend ret use $iCons
44 }
45 incr iCons
46 }
47 if {$orderby=="{column 0 desc 0} {column 1 desc 0}"
48 || $orderby=="{column 0 desc 0}"
49 } {
50 lappend ret orderby 1
51 lappend ret idxnum 1
52 set ::lOrderByConsumed 1
53 }
54 return $ret
55 }
56
57 xFilter {
58 set idxnum [lindex $args 0]
59 if {$idxnum} {
60 return [list sql "SELECT rowid, a, b FROM $src order by 2, 3"]
61 }
62 return [list sql "SELECT rowid, a, b FROM $src"]
63 }
64
65 }
66
67 return {}
68 }
69
70 do_execsql_test 1.0 {
71 CREATE TABLE t1(a, b);
72 CREATE INDEX i1 ON t1(a, b);
73 INSERT INTO t1 VALUES('a', 'b'), ('c', 'd');
74 INSERT INTO t1 VALUES('a', 'b'), ('c', 'd');
75 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);
76
77 CREATE TABLE t0(c0);
78 INSERT INTO t0(c0) VALUES (1), (0);
79 }
80
81 foreach {tn sql bDistinct idxinsert bConsumed res} {
82 1 "SELECT a, b FROM vt1" 0 0 0 {a b c d a b c d}
83 2 "SELECT DISTINCT a, b FROM vt1" 2 0 1 {a b c d}
84 3 "SELECT DISTINCT a FROM vt1" 2 0 1 {a c}
85 4 "SELECT DISTINCT b FROM vt1" 2 1 0 {b d}
86 5 "SELECT DISTINCT b FROM vt1 ORDER BY a" 3 1 1 {b d}
87 6 "SELECT DISTINCT t0.c0 FROM vt1, t0 ORDER BY vt1.a" 3 1 1 {1 0}
88 7 "SELECT DISTINCT a, b FROM vt1 ORDER BY a, b" 3 0 1 {a b c d}
89 8 "SELECT DISTINCT a, b FROM vt1 ORDER BY a" 3 1 1 {a b c d}
90 9 "SELECT DISTINCT a FROM vt1 ORDER BY a, b" 3 1 1 {a c}
91
92 10 "SELECT DISTINCT a, b FROM vt1 WHERE b='b'" 2 0 1 {a b}
93 11 "SELECT DISTINCT a, b FROM vt1 WHERE +b='b'" 2 0 1 {a b}
94 } {
95 set ::lBestIndexDistinct ""
96 set ::lOrderByConsumed 0
97 do_execsql_test 1.$tn.1 $sql $res
98 do_test 1.$tn.2 {
99 set ::lBestIndexDistinct
100 } $bDistinct
101 do_test 1.$tn.3 {
102 expr {[lsearch [execsql "explain $sql"] IdxInsert]>=0}
103 } $idxinsert
104 do_test 1.$tn.4 {
105 set ::lOrderByConsumed
106 } $bConsumed
107 }
108
109 #-------------------------------------------------------------------------
110 reset_db
111 register_tcl_module db
112
113 proc vtab_command {src method args} {
114 switch -- $method {
115 xConnect {
116 return "CREATE TABLE xxx(a, b)"
117 }
118
119 xBestIndex {
120 set hdl [lindex $args 0]
121 set ret [list]
122
123 set iCons 0
124 foreach cons [$hdl constraints] {
125 array set C $cons
126 if {($C(op)=="limit" || $C(op)=="offset") && $C(usable)} {
127 lappend ret use $iCons
128 }
129 incr iCons
130 }
131
132 return $ret
133 }
134
135 xFilter {
136 lappend ::lFilterArgs [lindex $args 2]
137 return [list sql "SELECT rowid, a, b FROM $src"]
138 }
139
140 }
141
142 return {}
143 }
144
145 do_execsql_test 2.0 {
146 CREATE TABLE t1(a, b);
147 CREATE INDEX i1 ON t1(a, b);
148 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);
149 }
150
151 do_test 2.1 {
152 set ::lFilterArgs [list]
153 execsql { SELECT * FROM vt1 LIMIT 10 }
154 set ::lFilterArgs
155 } {10}
156
157 do_test 2.2 {
158 set ::lFilterArgs [list]
159 execsql { SELECT * FROM vt1 LIMIT 5 OFFSET 50 }
160 set ::lFilterArgs
161 } {{50 5}}
162
163 do_test 2.3 {
164 set ::lFilterArgs [list]
165 execsql { SELECT * FROM vt1 ORDER BY a, b LIMIT 1 OFFSET 1 }
166 set ::lFilterArgs
167 } {{1 1}}
168
169 do_test 2.4 {
170 set ::lFilterArgs [list]
171 execsql { SELECT * FROM vt1 ORDER BY a, +b LIMIT 1 OFFSET 1 }
172 set ::lFilterArgs
173 } {{}}
174
175 #-------------------------------------------------------------------------
176 reset_db
177 register_tcl_module db
178
179 proc vtab_command {src method args} {
180 switch -- $method {
181 xConnect {
182 return "CREATE TABLE xxx(a, b)"
183 }
184
185 xBestIndex {
186 set hdl [lindex $args 0]
187 set lCons [$hdl constraints]
188
189 set ret [list]
190 for {set i 0} {$i < [llength $lCons]} {incr i} {
191 array set C [lindex $lCons $i]
192 if {$C(usable)} {
193 lappend ret use $i
194 $hdl in $i 1
195 }
196 }
197 return $ret
198 }
199
200 xFilter {
201 set lArg [lindex $args 2]
202 lappend ::lFilterArg {*}$lArg
203 return [list sql "SELECT rowid, a, b FROM $src"]
204 }
205
206 }
207
208 return {}
209 }
210
211 do_execsql_test 3.0 {
212 CREATE TABLE t1(a, b);
213 CREATE INDEX i1 ON t1(a, b);
214 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);
215 }
216
217 foreach {tn sql lfa} {
218 1 "SELECT * FROM vt1 WHERE b IN (10, 20, 30)" {{10 20 30}}
219 2 "SELECT * FROM vt1 WHERE b IN ('abc', 'def')" {{abc def}}
220 3 "SELECT * FROM vt1 WHERE a IS NULL AND b IN ('abc', 'def')" {{} {abc def}}
221 4 "SELECT * FROM vt1 WHERE a IN (1,2,3) AND b IN ('abc', 'def')"
222 {{1 2 3} {abc def}}
223
224 5 "SELECT * FROM vt1
225 WHERE a IN (SELECT 1 UNION SELECT 2) AND b IN ('abc', 'def')"
226 {{1 2} {abc def}}
227
228 6 "SELECT * FROM vt1
229 WHERE b IN ('abc', 'def') AND a IN (SELECT 1 UNION SELECT 2)"
230 {{abc def} {1 2}}
231 } {
232 do_test 3.$tn {
233 set ::lFilterArg [list]
234 execsql $sql
235 set ::lFilterArg
236 } $lfa
237 }
238
239 #explain_i { SELECT * FROM vt1 WHERE b IN (10, 20, 30) }
240
241 #-------------------------------------------------------------------------
242 reset_db
243 register_tcl_module db
244
245 proc vtab_command {src method args} {
246 switch -- $method {
247 xConnect {
248 return "CREATE TABLE xxx(a, b, c)"
249 }
250
251 xBestIndex {
252 set hdl [lindex $args 0]
253 set lCons [$hdl constraints]
254
255 set ret [list]
256 for {set i 0} {$i < [llength $lCons]} {incr i} {
257 lappend ::lBestIndexRhs [$hdl rhs_value $i -]
258 }
259 return $ret
260 }
261
262 xFilter {
263 return [list sql "SELECT rowid, a, b, c FROM $src"]
264 }
265
266 }
267
268 return {}
269 }
270
271 do_execsql_test 4.0 {
272 CREATE TABLE t1(a, b, c);
273 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);
274 }
275
276 foreach {tn sql lbir} {
277 1 "SELECT * FROM vt1 WHERE b = 10" {10}
278 2 "SELECT * FROM vt1 WHERE a = 'abc' AND b < 30" {abc 30}
279 3 "SELECT * FROM vt1 WHERE a = 'abc' AND b < 30+2" {abc -}
280 4 "SELECT * FROM vt1 WHERE a IN (1,2,3) AND b < 30+2" {- -}
281 5 "SELECT * FROM vt1 WHERE a IS 111 AND b < 30+2" {111 -}
282 } {
283 do_test 4.$tn {
284 set ::lBestIndexRhs [list]
285 execsql $sql
286 set ::lBestIndexRhs
287 } $lbir
288 }
289
290 #-------------------------------------------------------------------------
291 reset_db
292 db cache size 0
293 register_tcl_module db
294
295 set ::vtab_handle_in 1
296 proc vtab_command {src method args} {
297 switch -- $method {
298 xConnect {
299 return "CREATE TABLE xxx(a, b, c)"
300 }
301
302 xBestIndex {
303 set lCols [list a b c]
304
305 set hdl [lindex $args 0]
306 set lCons [$hdl constraints]
307 set lOrder [$hdl order]
308
309 set L ""
310 set O ""
311 set W [list]
312 set a 0
313 for {set i 0} {$i < [llength $lCons]} {incr i} {
314 array set C [lindex $lCons $i]
315 if {$C(usable)} {
316 if { $C(op)=="eq" } {
317 set bIn 0
318 if {$::vtab_handle_in} { set bIn [$hdl in $i 1] }
319 if {$bIn} {
320 lappend W "[lindex $lCols $C(column)] IN (%I$a%)"
321 } else {
322 lappend W "[lindex $lCols $C(column)] = %$a%"
323 }
324 lappend ret omit $i
325 }
326 if { $C(op)=="limit" } { set L " LIMIT %$a%" ; lappend ret use $i }
327 if { $C(op)=="offset" } { set O " OFFSET %$a%" ; lappend ret use $i }
328 incr a
329 }
330 }
331
332 set order ""
333 set selectlist "rowid, a, b, c"
334 if {[llength $lOrder]} {
335 array set sl [list]
336 set lO [list]
337 foreach s $lOrder {
338 array set C $s
339 set ad ""
340 if {$C(desc)} { set ad " DESC" }
341 lappend lO "[lindex $lCols $C(column)]$ad"
342 set sl($C(column)) 1
343 }
344 if {[$hdl distinct]==2} {
345 set selectlist "DISTINCT 0"
346 foreach i {0 1 2} {
347 if {[info exists sl($i)]} {
348 append selectlist ", [lindex $lCols $i]"
349 } else {
350 append selectlist ", 0"
351 }
352 }
353 } else {
354 set order " ORDER BY [join $lO ,]"
355 }
356 }
357
358 set where ""
359 if {[llength $W]} { set where " WHERE [join $W { AND }]" }
360 set sql "SELECT $selectlist FROM $src$where$order$L$O"
361
362 lappend ret idxStr $sql
363 return $ret
364 }
365
366 xFilter {
367 foreach {idxnum idxstr lArg} $args {}
368 set ii 0
369 set sql $idxstr
370 foreach a $lArg {
371 set sql [string map [list %$ii% $a] $sql]
372 set sql [string map [list %I$ii% [join $a ,]] $sql]
373 incr ii
374 }
375 lappend ::lFilterSql $sql
376
377 if {[regexp {OFFSET (.*)$} $sql -> off]} {
378 set real_sql "
379 WITH c(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<$off )
380 SELECT 0,0,0,0 FROM c
381 UNION ALL SELECT * FROM (
382 $sql
383 )
384 "
385 } else {
386 set real_sql $sql
387 }
388
389 return [list sql $real_sql]
390 }
391
392 }
393
394 return {}
395 }
396
397 do_execsql_test 5.0 {
398 CREATE TABLE t1(a, b, c);
399 CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1);
400 INSERT INTO t1 VALUES(1, 2, 3);
401 INSERT INTO t1 VALUES(2, 3, 4);
402 INSERT INTO t1 VALUES(3, 4, 5);
403 INSERT INTO t1 VALUES(1, 5, 6);
404 INSERT INTO t1 VALUES(2, 6, 7);
405 INSERT INTO t1 VALUES(3, 7, 8);
406 INSERT INTO t1 VALUES(1, 8, 9);
407 INSERT INTO t1 VALUES(2, 9, 0);
408 }
409
410 proc do_vtab_test {tn sql vtsql {res {}}} {
411 set ::lFilterSql [list]
412 uplevel [list do_execsql_test $tn.1 $sql $res]
413 uplevel [list do_test $tn.2 {set ::lFilterSql} [list {*}$vtsql]]
414 }
415
416 do_vtab_test 5.1.1 {
417 SELECT DISTINCT a FROM vt1
418 } {
419 {SELECT DISTINCT 0, a, 0, 0 FROM t1}
420 } {1 2 3}
421
422 do_vtab_test 5.1.2 {
423 SELECT DISTINCT a FROM vt1 ORDER BY a
424 } {
425 {SELECT rowid, a, b, c FROM t1 ORDER BY a}
426 } {1 2 3}
427
428 do_vtab_test 5.1.3 {
429 SELECT DISTINCT a FROM vt1 WHERE c IN (4,5,6,7,8)
430 } {
431 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c IN (4,5,6,7,8)}
432 } {2 3 1}
433
434 set ::vtab_handle_in 0
435 do_vtab_test 5.1.4 {
436 SELECT DISTINCT a FROM vt1 WHERE c IN (4,5,6,7,8)
437 } {
438 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 4}
439 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 5}
440 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 6}
441 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 7}
442 {SELECT DISTINCT 0, a, 0, 0 FROM t1 WHERE c = 8}
443 } {2 3 1}
444
445 set ::vtab_handle_in 1
446 do_vtab_test 5.1.5a {
447 SELECT a, b, c FROM vt1 WHERE c IN (4,5,6,7,8) LIMIT 2 OFFSET 2
448 } {
449 {SELECT rowid, a, b, c FROM t1 WHERE c IN (4,5,6,7,8) LIMIT 2 OFFSET 2}
450 } {1 5 6 2 6 7}
451
452 set ::vtab_handle_in 0
453 do_vtab_test 5.1.5b {
454 SELECT a, b, c FROM vt1 WHERE c IN (4,5,6,7,8) LIMIT 2 OFFSET 2
455 } {
456 {SELECT rowid, a, b, c FROM t1 WHERE c = 4}
457 {SELECT rowid, a, b, c FROM t1 WHERE c = 5}
458 {SELECT rowid, a, b, c FROM t1 WHERE c = 6}
459 {SELECT rowid, a, b, c FROM t1 WHERE c = 7}
460 } {1 5 6 2 6 7}
461 set ::vtab_handle_in 1
462
463 finish_test