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
183
184
185
|
# 2016 May 29
#
# 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.
#
#***********************************************************************
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix bestindex3
ifcapable !vtab {
finish_test
return
}
#-------------------------------------------------------------------------
# Virtual table callback for a virtual table named $tbl.
#
# The table created is:
#
# "CREATE TABLE t1 (a, b, c)"
#
# This virtual table supports both LIKE and = operators on all columns.
#
proc vtab_cmd {bOmit method args} {
switch -- $method {
xConnect {
return "CREATE TABLE t1(a, b, c)"
}
xBestIndex {
set hdl [lindex $args 0]
set clist [$hdl constraints]
set orderby [$hdl orderby]
set mask [$hdl mask]
set ret [list]
set use use
if {$bOmit} {set use omit}
for {set i 0} {$i < [llength $clist]} {incr i} {
array unset C
array set C [lindex $clist $i]
if {$C(usable) && ($C(op)=="like" || $C(op)=="eq")} {
lappend ret $use $i
lappend ret idxstr
lappend ret "[lindex {a b c} $C(column)] [string toupper $C(op)] ?"
break
}
}
if {$ret==""} {
lappend ret cost 1000000 rows 1000000
} else {
lappend ret cost 100 rows 10
}
return $ret
}
xFilter {
foreach {idxnum idxstr param} $args {}
set where ""
if {$bOmit && $idxstr != ""} {
set where " WHERE [string map [list ? '$param' EQ =] $idxstr]"
}
return [list sql "SELECT rowid, * FROM ttt$where"]
}
}
return ""
}
register_tcl_module db
do_execsql_test 1.0 {
CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0");
}
do_eqp_test 1.1 {
SELECT * FROM t1 WHERE a LIKE 'abc';
} {SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
do_eqp_test 1.2 {
SELECT * FROM t1 WHERE a = 'abc';
} {SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?}
do_eqp_test 1.3 {
SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
} {
QUERY PLAN
`--MULTI-INDEX OR
|--INDEX 1
| `--SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?
`--INDEX 2
`--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ?
}
do_eqp_test 1.4 {
SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
} {
QUERY PLAN
`--MULTI-INDEX OR
|--INDEX 1
| `--SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?
`--INDEX 2
`--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ?
}
do_execsql_test 1.5 {
CREATE TABLE ttt(a, b, c);
INSERT INTO ttt VALUES(1, 'two', 'three');
INSERT INTO ttt VALUES(2, 'one', 'two');
INSERT INTO ttt VALUES(3, 'three', 'one');
INSERT INTO ttt VALUES(4, 'y', 'one');
INSERT INTO ttt VALUES(5, 'x', 'two');
INSERT INTO ttt VALUES(6, 'y', 'three');
}
foreach omit {0 1} {
do_execsql_test 1.6.$omit.0 "
DROP TABLE t1;
CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $omit');
"
do_execsql_test 1.6.$omit.1 {
SELECT rowid FROM t1 WHERE c LIKE 'o%'
} {3 4}
do_execsql_test 1.6.$omit.2 {
SELECT rowid FROM t1 WHERE c LIKE 'o%' OR b='y'
} {3 4 6}
do_execsql_test 1.6.$omit.3 {
SELECT rowid FROM t1 WHERE c = 'three' OR c LIKE 'o%'
} {1 6 3 4}
}
#-------------------------------------------------------------------------
# Test the same pattern works with ordinary tables.
#
# This test does not work if the ICU extension is enabled. ICU overrides
# LIKE - and this optimization only works with the built-in LIKE function.
#
ifcapable !icu {
do_execsql_test 2.1 {
CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT);
CREATE INDEX t2x ON t2(x COLLATE nocase);
CREATE INDEX t2y ON t2(y);
}
do_eqp_test 2.2 {
SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
} [string map {"\n " \n} {
QUERY PLAN
`--MULTI-INDEX OR
|--INDEX 1
| `--SEARCH t2 USING INDEX t2x (x>? AND x<?)
`--INDEX 2
`--SEARCH t2 USING INDEX t2y (y=?)
}]
}
#-------------------------------------------------------------------------
# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE
# statement is currently ignored.
#
proc vvv_command {method args} {
switch -- $method {
xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" }
}
}
proc yyy_command {method args} {
switch -- $method {
xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" }
}
}
do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') }
do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') }
finish_test
|
