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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
|
# 2024-04-26
#
# 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 bestindexC
ifcapable !vtab {
finish_test
return
}
register_tcl_module db
proc vtab_command {lVal method args} {
switch -- $method {
xConnect {
return "CREATE TABLE t1(a)"
}
xBestIndex {
set hdl [lindex $args 0]
set clist [$hdl constraints]
set orderby [$hdl orderby]
set idxstr [list]
set res [list]
set idx 0
foreach c $clist {
array set a $c
if {$a(usable)==0} continue
if {$a(op)=="limit" && ![info exists ::do_not_use_limit]} {
lappend idxstr limit
lappend res omit $idx
}
if {$a(op)=="offset" && ![info exists ::do_not_use_offset]} {
lappend idxstr offset
lappend res omit $idx
}
incr idx
}
return "cost 1000000 rows 1000000 idxnum 0 idxstr {$idxstr} $res"
}
xFilter {
set idxstr [lindex $args 1]
set LIMIT ""
foreach a $idxstr b [lindex $args 2] {
set x($a) $b
}
if {![info exists x(limit)]} { set x(limit) -1 }
if {![info exists x(offset)]} { set x(offset) -1 }
set LIMIT " LIMIT $x(limit) OFFSET $x(offset)"
set idx 1
foreach v $lVal {
lappend lRow "($idx, '$v')"
incr idx
}
return [list sql "
SELECT * FROM ( VALUES [join $lRow ,]) $LIMIT
"]
}
}
return {}
}
do_execsql_test 1.0 {
CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "A B C D E F a b");
} {}
do_execsql_test 1.1 {
CREATE TEMP TABLE t_unionall AS
SELECT * FROM x1 UNION ALL SELECT * FROM x2;
CREATE TEMP TABLE t_intersect AS
SELECT * FROM x1 INTERSECT SELECT * FROM x2;
CREATE TEMP TABLE t_union AS
SELECT * FROM x1 UNION SELECT * FROM x2;
CREATE TEMP TABLE t_except AS
SELECT * FROM x1 EXCEPT SELECT * FROM x2;
}
foreach {tn limit} {
1 "LIMIT 8"
2 "LIMIT 4"
3 "LIMIT 4 OFFSET 2"
4 "LIMIT 8 OFFSET 4"
} {
foreach {op tbl} {
"UNION ALL" t_unionall
"UNION" t_union
"INTERSECT" t_intersect
"EXCEPT" t_except
} {
set expect [execsql "SELECT * FROM $tbl $limit"]
do_execsql_test 1.2.$tbl.$tn "SELECT * FROM (
SELECT * FROM x1 $op SELECT * FROM x2
) $limit" $expect
}
}
#-------------------------------------------------------------------------
reset_db
register_tcl_module db
do_execsql_test 2.0 {
CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "a b e f");
} {}
do_execsql_test 2.1 {
SELECT * FROM x1
EXCEPT
SELECT * FROM x2
LIMIT 3
} {c d}
#-------------------------------------------------------------------------
reset_db
register_tcl_module db
do_execsql_test 3.0 {
CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "1 2 3 4 5 6 7 8 9 10");
} {}
do_execsql_test 3.1 {
SELECT * FROM y1 WHERE a = COALESCE('8', a) LIMIT 3
} {8}
do_execsql_test 3.2 {
SELECT * FROM y1 WHERE a = '2' LIMIT 3
} {2}
load_static_extension db series
do_execsql_test 3.3 {
SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3
} {2 4}
do_execsql_test 3.4 {
SELECT value FROM generate_series(1,10) WHERE value>2 LIMIT 4 OFFSET 1;
} {4 5 6 7}
set ::do_not_use_limit 1
do_execsql_test 3.5 {
SELECT * FROM y1 LIMIT 5 OFFSET 3
} {4 5 6 7 8}
unset ::do_not_use_limit
set ::do_not_use_offset 1
do_execsql_test 3.6 {
SELECT * FROM y1 LIMIT 5 OFFSET 3
} {4 5 6 7 8}
unset ::do_not_use_offset
#-------------------------------------------------------------------------
reset_db
proc vtab_command {lVal method args} {
switch -- $method {
xConnect { error "not happy!" }
}
return {}
}
register_tcl_module db
do_catchsql_test 4.0 {
CREATE VIRTUAL TABLE y1 USING tcl(vtab_command 1);
} {1 {not happy!}}
do_test 4.1 {
sqlite3_errcode db
} SQLITE_ERROR
proc vtab_command {lVal method args} {
switch -- $method {
xConnect {
return $lVal
}
}
return {}
}
do_catchsql_test 4.2 {
CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "PRAGMA page_size=1024");
} {1 {declare_vtab: syntax error}}
do_catchsql_test 4.3 {
CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "CREATE TABLE x1(");
} {1 {declare_vtab: incomplete input}}
do_catchsql_test 4.4 {
CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "CREATE TABLE x1(insert)");
} {1 {declare_vtab: near "insert": syntax error}}
finish_test
|
