Debian Codegraph @

test/bestindex3.test

raw
1 # 2016 May 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 set testdir [file dirname $argv0]
13 source $testdir/tester.tcl
14 set testprefix bestindex3
15
16 ifcapable !vtab {
17 finish_test
18 return
19 }
20
21 #-------------------------------------------------------------------------
22 # Virtual table callback for a virtual table named $tbl.
23 #
24 # The table created is:
25 #
26 # "CREATE TABLE t1 (a, b, c)"
27 #
28 # This virtual table supports both LIKE and = operators on all columns.
29 #
30 proc vtab_cmd {bOmit method args} {
31 switch -- $method {
32 xConnect {
33 return "CREATE TABLE t1(a, b, c)"
34 }
35
36 xBestIndex {
37 set hdl [lindex $args 0]
38 set clist [$hdl constraints]
39 set orderby [$hdl orderby]
40 set mask [$hdl mask]
41
42 set ret [list]
43 set use use
44 if {$bOmit} {set use omit}
45
46 for {set i 0} {$i < [llength $clist]} {incr i} {
47 array unset C
48 array set C [lindex $clist $i]
49 if {$C(usable) && ($C(op)=="like" || $C(op)=="eq")} {
50 lappend ret $use $i
51 lappend ret idxstr
52 lappend ret "[lindex {a b c} $C(column)] [string toupper $C(op)] ?"
53 break
54 }
55 }
56
57 if {$ret==""} {
58 lappend ret cost 1000000 rows 1000000
59 } else {
60 lappend ret cost 100 rows 10
61 }
62 return $ret
63 }
64
65 xFilter {
66 foreach {idxnum idxstr param} $args {}
67 set where ""
68 if {$bOmit && $idxstr != ""} {
69 set where " WHERE [string map [list ? '$param' EQ =] $idxstr]"
70 }
71 return [list sql "SELECT rowid, * FROM ttt$where"]
72 }
73 }
74 return ""
75 }
76
77 register_tcl_module db
78
79 do_execsql_test 1.0 {
80 CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0");
81 }
82
83 do_eqp_test 1.1 {
84 SELECT * FROM t1 WHERE a LIKE 'abc';
85 } {SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
86
87 do_eqp_test 1.2 {
88 SELECT * FROM t1 WHERE a = 'abc';
89 } {SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?}
90
91 do_eqp_test 1.3 {
92 SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
93 } {
94 QUERY PLAN
95 `--MULTI-INDEX OR
96 |--INDEX 1
97 | `--SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?
98 `--INDEX 2
99 `--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ?
100 }
101
102 do_eqp_test 1.4 {
103 SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
104 } {
105 QUERY PLAN
106 `--MULTI-INDEX OR
107 |--INDEX 1
108 | `--SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?
109 `--INDEX 2
110 `--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ?
111 }
112
113 do_execsql_test 1.5 {
114 CREATE TABLE ttt(a, b, c);
115
116 INSERT INTO ttt VALUES(1, 'two', 'three');
117 INSERT INTO ttt VALUES(2, 'one', 'two');
118 INSERT INTO ttt VALUES(3, 'three', 'one');
119 INSERT INTO ttt VALUES(4, 'y', 'one');
120 INSERT INTO ttt VALUES(5, 'x', 'two');
121 INSERT INTO ttt VALUES(6, 'y', 'three');
122 }
123
124 foreach omit {0 1} {
125 do_execsql_test 1.6.$omit.0 "
126 DROP TABLE t1;
127 CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $omit');
128 "
129 do_execsql_test 1.6.$omit.1 {
130 SELECT rowid FROM t1 WHERE c LIKE 'o%'
131 } {3 4}
132
133 do_execsql_test 1.6.$omit.2 {
134 SELECT rowid FROM t1 WHERE c LIKE 'o%' OR b='y'
135 } {3 4 6}
136
137 do_execsql_test 1.6.$omit.3 {
138 SELECT rowid FROM t1 WHERE c = 'three' OR c LIKE 'o%'
139 } {1 6 3 4}
140 }
141
142 #-------------------------------------------------------------------------
143 # Test the same pattern works with ordinary tables.
144 #
145 # This test does not work if the ICU extension is enabled. ICU overrides
146 # LIKE - and this optimization only works with the built-in LIKE function.
147 #
148 ifcapable !icu {
149 do_execsql_test 2.1 {
150 CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT);
151 CREATE INDEX t2x ON t2(x COLLATE nocase);
152 CREATE INDEX t2y ON t2(y);
153 }
154
155 do_eqp_test 2.2 {
156 SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
157 } [string map {"\n " \n} {
158 QUERY PLAN
159 `--MULTI-INDEX OR
160 |--INDEX 1
161 | `--SEARCH t2 USING INDEX t2x (x>? AND x<?)
162 `--INDEX 2
163 `--SEARCH t2 USING INDEX t2y (y=?)
164 }]
165 }
166
167 #-------------------------------------------------------------------------
168 # Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE
169 # statement is currently ignored.
170 #
171 proc vvv_command {method args} {
172 switch -- $method {
173 xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" }
174 }
175 }
176 proc yyy_command {method args} {
177 switch -- $method {
178 xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" }
179 }
180 }
181
182 do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') }
183 do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') }
184
185 finish_test