Debian Codegraph @

test/bestindex2.test

raw
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