Debian Codegraph @

test/bestindexC.test

raw
1 # 2024-04-26
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
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix bestindexC
17
18 ifcapable !vtab {
19 finish_test
20 return
21 }
22
23 register_tcl_module db
24
25 proc vtab_command {lVal method args} {
26 switch -- $method {
27 xConnect {
28 return "CREATE TABLE t1(a)"
29 }
30
31 xBestIndex {
32 set hdl [lindex $args 0]
33 set clist [$hdl constraints]
34 set orderby [$hdl orderby]
35
36 set idxstr [list]
37 set res [list]
38
39 set idx 0
40 foreach c $clist {
41 array set a $c
42 if {$a(usable)==0} continue
43 if {$a(op)=="limit" && ![info exists ::do_not_use_limit]} {
44 lappend idxstr limit
45 lappend res omit $idx
46 }
47 if {$a(op)=="offset" && ![info exists ::do_not_use_offset]} {
48 lappend idxstr offset
49 lappend res omit $idx
50 }
51 incr idx
52 }
53
54 return "cost 1000000 rows 1000000 idxnum 0 idxstr {$idxstr} $res"
55 }
56
57 xFilter {
58 set idxstr [lindex $args 1]
59 set LIMIT ""
60 foreach a $idxstr b [lindex $args 2] {
61 set x($a) $b
62 }
63
64 if {![info exists x(limit)]} { set x(limit) -1 }
65 if {![info exists x(offset)]} { set x(offset) -1 }
66 set LIMIT " LIMIT $x(limit) OFFSET $x(offset)"
67
68 set idx 1
69 foreach v $lVal {
70 lappend lRow "($idx, '$v')"
71 incr idx
72 }
73
74 return [list sql "
75 SELECT * FROM ( VALUES [join $lRow ,]) $LIMIT
76 "]
77 }
78 }
79
80 return {}
81 }
82
83 do_execsql_test 1.0 {
84 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
85 CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "A B C D E F a b");
86 } {}
87
88 do_execsql_test 1.1 {
89 CREATE TEMP TABLE t_unionall AS
90 SELECT * FROM x1 UNION ALL SELECT * FROM x2;
91
92 CREATE TEMP TABLE t_intersect AS
93 SELECT * FROM x1 INTERSECT SELECT * FROM x2;
94
95 CREATE TEMP TABLE t_union AS
96 SELECT * FROM x1 UNION SELECT * FROM x2;
97
98 CREATE TEMP TABLE t_except AS
99 SELECT * FROM x1 EXCEPT SELECT * FROM x2;
100 }
101
102 foreach {tn limit} {
103 1 "LIMIT 8"
104 2 "LIMIT 4"
105 3 "LIMIT 4 OFFSET 2"
106 4 "LIMIT 8 OFFSET 4"
107 } {
108
109 foreach {op tbl} {
110 "UNION ALL" t_unionall
111 "UNION" t_union
112 "INTERSECT" t_intersect
113 "EXCEPT" t_except
114 } {
115
116 set expect [execsql "SELECT * FROM $tbl $limit"]
117 do_execsql_test 1.2.$tbl.$tn "SELECT * FROM (
118 SELECT * FROM x1 $op SELECT * FROM x2
119 ) $limit" $expect
120
121 }
122
123 }
124
125 #-------------------------------------------------------------------------
126 reset_db
127 register_tcl_module db
128
129 do_execsql_test 2.0 {
130 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command "a b c d e f");
131 CREATE VIRTUAL TABLE x2 USING tcl(vtab_command "a b e f");
132 } {}
133
134 do_execsql_test 2.1 {
135 SELECT * FROM x1
136 EXCEPT
137 SELECT * FROM x2
138 LIMIT 3
139 } {c d}
140
141 #-------------------------------------------------------------------------
142 reset_db
143 register_tcl_module db
144 do_execsql_test 3.0 {
145 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "1 2 3 4 5 6 7 8 9 10");
146 } {}
147
148 do_execsql_test 3.1 {
149 SELECT * FROM y1 WHERE a = COALESCE('8', a) LIMIT 3
150 } {8}
151
152 do_execsql_test 3.2 {
153 SELECT * FROM y1 WHERE a = '2' LIMIT 3
154 } {2}
155
156 load_static_extension db series
157 do_execsql_test 3.3 {
158 SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3
159 } {2 4}
160
161 do_execsql_test 3.4 {
162 SELECT value FROM generate_series(1,10) WHERE value>2 LIMIT 4 OFFSET 1;
163 } {4 5 6 7}
164
165 set ::do_not_use_limit 1
166 do_execsql_test 3.5 {
167 SELECT * FROM y1 LIMIT 5 OFFSET 3
168 } {4 5 6 7 8}
169 unset ::do_not_use_limit
170 set ::do_not_use_offset 1
171 do_execsql_test 3.6 {
172 SELECT * FROM y1 LIMIT 5 OFFSET 3
173 } {4 5 6 7 8}
174 unset ::do_not_use_offset
175
176 #-------------------------------------------------------------------------
177 reset_db
178 proc vtab_command {lVal method args} {
179 switch -- $method {
180 xConnect { error "not happy!" }
181 }
182
183 return {}
184 }
185
186 register_tcl_module db
187 do_catchsql_test 4.0 {
188 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command 1);
189 } {1 {not happy!}}
190 do_test 4.1 {
191 sqlite3_errcode db
192 } SQLITE_ERROR
193
194 proc vtab_command {lVal method args} {
195 switch -- $method {
196 xConnect {
197 return $lVal
198 }
199 }
200 return {}
201 }
202
203 do_catchsql_test 4.2 {
204 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "PRAGMA page_size=1024");
205 } {1 {declare_vtab: syntax error}}
206 do_catchsql_test 4.3 {
207 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "CREATE TABLE x1(");
208 } {1 {declare_vtab: incomplete input}}
209 do_catchsql_test 4.4 {
210 CREATE VIRTUAL TABLE y1 USING tcl(vtab_command "CREATE TABLE x1(insert)");
211 } {1 {declare_vtab: near "insert": syntax error}}
212
213 #-------------------------------------------------------------------------
214 reset_db
215 register_tcl_module db
216
217 proc quote {str} {
218 return "'[string map {' ''} $str]'"
219 }
220
221 proc vtab_command {lVal method args} {
222 switch -- $method {
223 xConnect {
224 return "CREATE TABLE t1(a, b, c, d)"
225 }
226
227 xBestIndex {
228 set hdl [lindex $args 0]
229 set clist [$hdl constraints]
230
231 set res [list]
232 set idx 0
233 set idxnum 0
234
235 set cols(0) a
236 set cols(1) b
237 set cols(2) c
238
239 set lCons [list]
240
241 foreach c $clist {
242 array set a $c
243 if {$a(usable)==0} continue
244
245 if {($a(op)=="eq" || $a(op)=="is") && [info exists cols($a(column))]} {
246 lappend res omit $idx
247 set coll [$hdl collation $idx]
248 lappend lCons "$cols($a(column)) = %[llength $lCons]% COLLATE $coll"
249
250 set idxnum [expr {$idx + (1 << $a(column))}]
251 catch { unset cols($a(column)) }
252 }
253
254 incr idx
255 }
256
257 if {[llength [array names cols]]>0} {
258 set missing [list]
259 for {set i 0} {$i < 3} {incr i} {
260 catch { lappend missing $cols($i) }
261 }
262 set msg "missing required constraints: [join $missing ,]"
263 return [list constraint $msg]
264 }
265
266 set idxstr [join $lCons " AND "]
267 return "cost 1000 rows 1000 idxnum $idxnum $res idxstr {$idxstr}"
268 }
269
270 xFilter {
271 foreach {idxnum idxstr lArg} $args {}
272 set i 0
273 set where $idxstr
274 foreach a $lArg {
275 set where [string map [list %$i% [quote $a]] $where]
276 incr i
277 }
278 # puts $where
279 return [list sql "SELECT rowid, * FROM $lVal WHERE $where"]
280 }
281 }
282
283 return {}
284 }
285
286 do_execsql_test 5.1 {
287 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1);
288 CREATE TABLE t1(a, b, c, d);
289 }
290
291 foreach {tn where ok} {
292 0 "WHERE a=? AND b=? AND c=? AND c=?" 1
293 1 "WHERE a=? AND b=? AND c=?" 1
294 2 "WHERE a=? AND b=? AND (c=? OR c=?)" 1
295 3 "WHERE a=? AND b=? AND (c=? OR c=? OR c=?)" 1
296 4 "WHERE a=? AND b=? AND (c IS ? OR c IS ?)" 1
297 5 "WHERE a=? AND ((b=? AND c=?) OR (c=? AND b=?))" 1
298 6 "WHERE a=? AND ((b=? AND c=?) OR (c=?))" 0
299 } {
300 do_test 5.2.$tn {
301 catch { execsql "SELECT * FROM x1 $::where" } msg
302 # if {$tn==0 || $tn==2 || $tn==3} { puts "MSG: $msg" }
303 } [expr !$ok]
304 }
305
306 do_execsql_test 5.3 {
307 SELECT * FROM x1 WHERE (a, b, c) = (?, ?, ?);
308 }
309
310 do_execsql_test 5.4 {
311 INSERT INTO t1(rowid, a, b, c, d) VALUES(1, 'x', 'y', 'z', 'one');
312 INSERT INTO t1(rowid, a, b, c, d) VALUES(2, 'X', 'Y', 'Z', 'two');
313 SELECT * FROM x1 WHERE (a, b, c) = ('X', 'Y', 'Z');
314 } {X Y Z two}
315 do_execsql_test 5.5 {
316 SELECT * FROM x1 WHERE a='x' AND b='y' AND c='z';
317 } {x y z one}
318 do_execsql_test 5.6 {
319 SELECT * FROM x1
320 WHERE a='x' COLLATE nocase AND b='y' COLLATE nocase AND c='z'COLLATE nocase;
321 } {x y z one X Y Z two}
322
323 do_execsql_test 5.7 {
324 DELETE FROM t1;
325
326 INSERT INTO t1(rowid, a, b, c, d) VALUES(0, 'x', 'y', 'z', 'zero');
327 INSERT INTO t1(rowid, a, b, c, d) VALUES(1, 'x', 'y', 'Z', 'one');
328 INSERT INTO t1(rowid, a, b, c, d) VALUES(2, 'x', 'Y', 'z', 'two');
329 INSERT INTO t1(rowid, a, b, c, d) VALUES(3, 'x', 'Y', 'Z', 'three');
330 INSERT INTO t1(rowid, a, b, c, d) VALUES(4, 'X', 'y', 'z', 'four');
331 INSERT INTO t1(rowid, a, b, c, d) VALUES(5, 'X', 'y', 'Z', 'five');
332 INSERT INTO t1(rowid, a, b, c, d) VALUES(6, 'X', 'Y', 'z', 'six');
333 INSERT INTO t1(rowid, a, b, c, d) VALUES(7, 'X', 'Y', 'z', 'seven');
334 }
335
336
337 if {1} {
338 # Since 2026-05-18, SQLite does not attempt the OR-optimization on
339 # conjuncts that contain COLLATE operators, due to
340 # bug report 2026-05-17T11:46:40Z.
341 do_catchsql_test 5.8 {
342 SELECT d FROM x1
343 WHERE a='x' AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase))
344 } {1 {no query solution}}
345
346 do_catchsql_test 5.9 {
347 SELECT d FROM x1
348 WHERE a='x' COLLATE nocase
349 AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase))
350 } {1 {no query solution}}
351 } else {
352 # This case would work if SQLite knew how to deal with conjuncts that
353 # contain COLLATE operators.
354 do_execsql_test 5.8 {
355 SELECT d FROM x1
356 WHERE a='x' AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase))
357 } {
358 zero two three
359 }
360
361 do_execsql_test 5.9 {
362 SELECT d FROM x1
363 WHERE a='x' COLLATE nocase
364 AND ((b='y' AND c='z') OR (b='Y' AND c='z' COLLATE nocase))
365 } {
366 zero four two
367 three six seven
368 }
369 }
370
371 #--------------------------------------------------------------------------
372
373 reset_db
374 register_tcl_module db
375
376 proc quote {str} {
377 return "'[string map {' ''} $str]'"
378 }
379
380 proc vtab_command {lVal method args} {
381 switch -- $method {
382 xConnect {
383 return "CREATE TABLE t1(a, b, c, d)"
384 }
385
386 xBestIndex {
387 set hdl [lindex $args 0]
388 set clist [$hdl constraints]
389
390 set idx 0
391 set idxnum 0
392
393 foreach c $clist {
394 array set a $c
395 if {$a(usable)==0} continue
396
397 if {$a(op)=="limit"} {
398 set idxnum [$hdl rhs_value $idx 555]
399 }
400
401 incr idx
402 }
403
404 return "cost 1000 rows 1000 idxnum $idxnum"
405
406 }
407
408 xFilter {
409 foreach {idxnum idxstr lArg} $args {}
410 return [list sql "SELECT 0, $idxnum, $idxnum, $idxnum, $idxnum"]
411 }
412 }
413
414 return {}
415 }
416
417 do_execsql_test 6.0 {
418 CREATE TABLE t1(x, y);
419 INSERT INTO t1 VALUES(2, 2);
420 CREATE VIRTUAL TABLE x1 USING tcl(vtab_command t1);
421 }
422
423 do_execsql_test 6.1 { SELECT * FROM x1 LIMIT 50 } {50 50 50 50}
424
425 do_execsql_test 6.2 { SELECT * FROM x1 WHERE b=c LIMIT 5 } {0 0 0 0}
426
427 do_execsql_test 6.3 {
428 SELECT (SELECT a FROM x1 WHERE t1.x=t1.y LIMIT 10) FROM t1
429 } {0}
430
431 do_execsql_test 6.4 {
432 SELECT (SELECT a FROM x1 WHERE x1.a=1) FROM t1
433 } {1}
434
435 do_execsql_test 6.5 {
436 SELECT (SELECT a FROM x1 WHERE x1.a=1 LIMIT 1) FROM t1
437 } {1}
438
439 do_execsql_test 6.6 {
440 SELECT (SELECT a FROM x1 WHERE x1.a=555 LIMIT 2) FROM t1
441 } {555}
442
443 finish_test