wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2005 August 13 |
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 | # This file implements regression tests for SQLite library. The |
||
12 | # focus of this file is testing the LIKE and GLOB operators and |
||
13 | # in particular the optimizations that occur to help those operators |
||
14 | # run faster. |
||
15 | # |
||
16 | # $Id: like.test,v 1.13 2009/06/07 23:45:11 drh Exp $ |
||
17 | |||
18 | set testdir [file dirname $argv0] |
||
19 | source $testdir/tester.tcl |
||
20 | |||
21 | # Create some sample data to work with. |
||
22 | # |
||
23 | do_test like-1.0 { |
||
24 | execsql { |
||
25 | CREATE TABLE t1(x TEXT); |
||
26 | } |
||
27 | foreach str { |
||
28 | a |
||
29 | ab |
||
30 | abc |
||
31 | abcd |
||
32 | |||
33 | acd |
||
34 | abd |
||
35 | bc |
||
36 | bcd |
||
37 | |||
38 | xyz |
||
39 | ABC |
||
40 | CDE |
||
41 | {ABC abc xyz} |
||
42 | } { |
||
43 | db eval {INSERT INTO t1 VALUES(:str)} |
||
44 | } |
||
45 | execsql { |
||
46 | SELECT count(*) FROM t1; |
||
47 | } |
||
48 | } {12} |
||
49 | |||
50 | # Test that both case sensitive and insensitive version of LIKE work. |
||
51 | # |
||
52 | do_test like-1.1 { |
||
53 | execsql { |
||
54 | SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; |
||
55 | } |
||
56 | } {ABC abc} |
||
57 | do_test like-1.2 { |
||
58 | execsql { |
||
59 | SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1; |
||
60 | } |
||
61 | } {abc} |
||
62 | do_test like-1.3 { |
||
63 | execsql { |
||
64 | SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1; |
||
65 | } |
||
66 | } {ABC abc} |
||
67 | do_test like-1.4 { |
||
68 | execsql { |
||
69 | SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1; |
||
70 | } |
||
71 | } {ABC abc} |
||
72 | do_test like-1.5.1 { |
||
73 | execsql { |
||
74 | PRAGMA case_sensitive_like=on; |
||
75 | SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; |
||
76 | } |
||
77 | } {abc} |
||
78 | do_test like-1.5.2 { |
||
79 | execsql { |
||
80 | PRAGMA case_sensitive_like; -- no argument; does not change setting |
||
81 | SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; |
||
82 | } |
||
83 | } {abc} |
||
84 | do_test like-1.6 { |
||
85 | execsql { |
||
86 | SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1; |
||
87 | } |
||
88 | } {abc} |
||
89 | do_test like-1.7 { |
||
90 | execsql { |
||
91 | SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1; |
||
92 | } |
||
93 | } {ABC} |
||
94 | do_test like-1.8 { |
||
95 | execsql { |
||
96 | SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1; |
||
97 | } |
||
98 | } {} |
||
99 | do_test like-1.9 { |
||
100 | execsql { |
||
101 | PRAGMA case_sensitive_like=off; |
||
102 | SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; |
||
103 | } |
||
104 | } {ABC abc} |
||
105 | do_test like-1.10 { |
||
106 | execsql { |
||
107 | PRAGMA case_sensitive_like; -- No argument, does not change setting. |
||
108 | SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; |
||
109 | } |
||
110 | } {ABC abc} |
||
111 | |||
112 | # Tests of the REGEXP operator |
||
113 | # |
||
114 | do_test like-2.1 { |
||
115 | proc test_regexp {a b} { |
||
116 | return [regexp $a $b] |
||
117 | } |
||
118 | db function regexp -argcount 2 test_regexp |
||
119 | execsql { |
||
120 | SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1; |
||
121 | } |
||
122 | } {{ABC abc xyz} abc abcd} |
||
123 | do_test like-2.2 { |
||
124 | execsql { |
||
125 | SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1; |
||
126 | } |
||
127 | } {abc abcd} |
||
128 | |||
129 | # Tests of the MATCH operator |
||
130 | # |
||
131 | do_test like-2.3 { |
||
132 | proc test_match {a b} { |
||
133 | return [string match $a $b] |
||
134 | } |
||
135 | db function match -argcount 2 test_match |
||
136 | execsql { |
||
137 | SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1; |
||
138 | } |
||
139 | } {{ABC abc xyz} abc abcd} |
||
140 | do_test like-2.4 { |
||
141 | execsql { |
||
142 | SELECT x FROM t1 WHERE x MATCH 'abc*' ORDER BY 1; |
||
143 | } |
||
144 | } {abc abcd} |
||
145 | |||
146 | # For the remaining tests, we need to have the like optimizations |
||
147 | # enabled. |
||
148 | # |
||
149 | ifcapable !like_opt { |
||
150 | finish_test |
||
151 | return |
||
152 | } |
||
153 | |||
154 | # This procedure executes the SQL. Then it appends to the result the |
||
155 | # "sort" or "nosort" keyword (as in the cksort procedure above) then |
||
156 | # it appends the ::sqlite_query_plan variable. |
||
157 | # |
||
158 | proc queryplan {sql} { |
||
159 | set ::sqlite_sort_count 0 |
||
160 | set data [execsql $sql] |
||
161 | if {$::sqlite_sort_count} {set x sort} {set x nosort} |
||
162 | lappend data $x |
||
163 | return [concat $data $::sqlite_query_plan] |
||
164 | } |
||
165 | |||
166 | # Perform tests on the like optimization. |
||
167 | # |
||
168 | # With no index on t1.x and with case sensitivity turned off, no optimization |
||
169 | # is performed. |
||
170 | # |
||
171 | do_test like-3.1 { |
||
172 | set sqlite_like_count 0 |
||
173 | queryplan { |
||
174 | SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; |
||
175 | } |
||
176 | } {ABC {ABC abc xyz} abc abcd sort t1 {}} |
||
177 | do_test like-3.2 { |
||
178 | set sqlite_like_count |
||
179 | } {12} |
||
180 | |||
181 | # With an index on t1.x and case sensitivity on, optimize completely. |
||
182 | # |
||
183 | do_test like-3.3 { |
||
184 | set sqlite_like_count 0 |
||
185 | execsql { |
||
186 | PRAGMA case_sensitive_like=on; |
||
187 | CREATE INDEX i1 ON t1(x); |
||
188 | } |
||
189 | queryplan { |
||
190 | SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; |
||
191 | } |
||
192 | } {abc abcd nosort {} i1} |
||
193 | do_test like-3.4 { |
||
194 | set sqlite_like_count |
||
195 | } 0 |
||
196 | |||
197 | # The LIKE optimization still works when the RHS is a string with no |
||
198 | # wildcard. Ticket [e090183531fc2747] |
||
199 | # |
||
200 | do_test like-3.4.2 { |
||
201 | queryplan { |
||
202 | SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1; |
||
203 | } |
||
204 | } {a nosort {} i1} |
||
205 | do_test like-3.4.3 { |
||
206 | queryplan { |
||
207 | SELECT x FROM t1 WHERE x LIKE 'ab' ORDER BY 1; |
||
208 | } |
||
209 | } {ab nosort {} i1} |
||
210 | do_test like-3.4.4 { |
||
211 | queryplan { |
||
212 | SELECT x FROM t1 WHERE x LIKE 'abcd' ORDER BY 1; |
||
213 | } |
||
214 | } {abcd nosort {} i1} |
||
215 | do_test like-3.4.5 { |
||
216 | queryplan { |
||
217 | SELECT x FROM t1 WHERE x LIKE 'abcde' ORDER BY 1; |
||
218 | } |
||
219 | } {nosort {} i1} |
||
220 | |||
221 | |||
222 | # Partial optimization when the pattern does not end in '%' |
||
223 | # |
||
224 | do_test like-3.5 { |
||
225 | set sqlite_like_count 0 |
||
226 | queryplan { |
||
227 | SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1; |
||
228 | } |
||
229 | } {abc nosort {} i1} |
||
230 | do_test like-3.6 { |
||
231 | set sqlite_like_count |
||
232 | } 6 |
||
233 | do_test like-3.7 { |
||
234 | set sqlite_like_count 0 |
||
235 | queryplan { |
||
236 | SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1; |
||
237 | } |
||
238 | } {abcd abd nosort {} i1} |
||
239 | do_test like-3.8 { |
||
240 | set sqlite_like_count |
||
241 | } 4 |
||
242 | do_test like-3.9 { |
||
243 | set sqlite_like_count 0 |
||
244 | queryplan { |
||
245 | SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1; |
||
246 | } |
||
247 | } {abc abcd nosort {} i1} |
||
248 | do_test like-3.10 { |
||
249 | set sqlite_like_count |
||
250 | } 6 |
||
251 | |||
252 | # No optimization when the pattern begins with a wildcard. |
||
253 | # Note that the index is still used but only for sorting. |
||
254 | # |
||
255 | do_test like-3.11 { |
||
256 | set sqlite_like_count 0 |
||
257 | queryplan { |
||
258 | SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1; |
||
259 | } |
||
260 | } {abcd bcd nosort {} i1} |
||
261 | do_test like-3.12 { |
||
262 | set sqlite_like_count |
||
263 | } 12 |
||
264 | |||
265 | # No optimization for case insensitive LIKE |
||
266 | # |
||
267 | do_test like-3.13 { |
||
268 | set sqlite_like_count 0 |
||
269 | queryplan { |
||
270 | PRAGMA case_sensitive_like=off; |
||
271 | SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; |
||
272 | } |
||
273 | } {ABC {ABC abc xyz} abc abcd nosort {} i1} |
||
274 | do_test like-3.14 { |
||
275 | set sqlite_like_count |
||
276 | } 12 |
||
277 | |||
278 | # No optimization without an index. |
||
279 | # |
||
280 | do_test like-3.15 { |
||
281 | set sqlite_like_count 0 |
||
282 | queryplan { |
||
283 | PRAGMA case_sensitive_like=on; |
||
284 | DROP INDEX i1; |
||
285 | SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; |
||
286 | } |
||
287 | } {abc abcd sort t1 {}} |
||
288 | do_test like-3.16 { |
||
289 | set sqlite_like_count |
||
290 | } 12 |
||
291 | |||
292 | # No GLOB optimization without an index. |
||
293 | # |
||
294 | do_test like-3.17 { |
||
295 | set sqlite_like_count 0 |
||
296 | queryplan { |
||
297 | SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; |
||
298 | } |
||
299 | } {abc abcd sort t1 {}} |
||
300 | do_test like-3.18 { |
||
301 | set sqlite_like_count |
||
302 | } 12 |
||
303 | |||
304 | # GLOB is optimized regardless of the case_sensitive_like setting. |
||
305 | # |
||
306 | do_test like-3.19 { |
||
307 | set sqlite_like_count 0 |
||
308 | queryplan { |
||
309 | CREATE INDEX i1 ON t1(x); |
||
310 | SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; |
||
311 | } |
||
312 | } {abc abcd nosort {} i1} |
||
313 | do_test like-3.20 { |
||
314 | set sqlite_like_count |
||
315 | } 0 |
||
316 | do_test like-3.21 { |
||
317 | set sqlite_like_count 0 |
||
318 | queryplan { |
||
319 | PRAGMA case_sensitive_like=on; |
||
320 | SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; |
||
321 | } |
||
322 | } {abc abcd nosort {} i1} |
||
323 | do_test like-3.22 { |
||
324 | set sqlite_like_count |
||
325 | } 0 |
||
326 | do_test like-3.23 { |
||
327 | set sqlite_like_count 0 |
||
328 | queryplan { |
||
329 | PRAGMA case_sensitive_like=off; |
||
330 | SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1; |
||
331 | } |
||
332 | } {abd acd nosort {} i1} |
||
333 | do_test like-3.24 { |
||
334 | set sqlite_like_count |
||
335 | } 6 |
||
336 | |||
337 | # GLOB optimization when there is no wildcard. Ticket [e090183531fc2747] |
||
338 | # |
||
339 | do_test like-3.25 { |
||
340 | queryplan { |
||
341 | SELECT x FROM t1 WHERE x GLOB 'a' ORDER BY 1; |
||
342 | } |
||
343 | } {a nosort {} i1} |
||
344 | do_test like-3.26 { |
||
345 | queryplan { |
||
346 | SELECT x FROM t1 WHERE x GLOB 'abcd' ORDER BY 1; |
||
347 | } |
||
348 | } {abcd nosort {} i1} |
||
349 | do_test like-3.27 { |
||
350 | queryplan { |
||
351 | SELECT x FROM t1 WHERE x GLOB 'abcde' ORDER BY 1; |
||
352 | } |
||
353 | } {nosort {} i1} |
||
354 | |||
355 | |||
356 | |||
357 | # No optimization if the LHS of the LIKE is not a column name or |
||
358 | # if the RHS is not a string. |
||
359 | # |
||
360 | do_test like-4.1 { |
||
361 | execsql {PRAGMA case_sensitive_like=on} |
||
362 | set sqlite_like_count 0 |
||
363 | queryplan { |
||
364 | SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1 |
||
365 | } |
||
366 | } {abc abcd nosort {} i1} |
||
367 | do_test like-4.2 { |
||
368 | set sqlite_like_count |
||
369 | } 0 |
||
370 | do_test like-4.3 { |
||
371 | set sqlite_like_count 0 |
||
372 | queryplan { |
||
373 | SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1 |
||
374 | } |
||
375 | } {abc abcd nosort {} i1} |
||
376 | do_test like-4.4 { |
||
377 | set sqlite_like_count |
||
378 | } 12 |
||
379 | do_test like-4.5 { |
||
380 | set sqlite_like_count 0 |
||
381 | queryplan { |
||
382 | SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1 |
||
383 | } |
||
384 | } {abc abcd nosort {} i1} |
||
385 | do_test like-4.6 { |
||
386 | set sqlite_like_count |
||
387 | } 12 |
||
388 | |||
389 | # Collating sequences on the index disable the LIKE optimization. |
||
390 | # Or if the NOCASE collating sequence is used, the LIKE optimization |
||
391 | # is enabled when case_sensitive_like is OFF. |
||
392 | # |
||
393 | do_test like-5.1 { |
||
394 | execsql {PRAGMA case_sensitive_like=off} |
||
395 | set sqlite_like_count 0 |
||
396 | queryplan { |
||
397 | SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1 |
||
398 | } |
||
399 | } {ABC {ABC abc xyz} abc abcd nosort {} i1} |
||
400 | do_test like-5.2 { |
||
401 | set sqlite_like_count |
||
402 | } 12 |
||
403 | do_test like-5.3 { |
||
404 | execsql { |
||
405 | CREATE TABLE t2(x TEXT COLLATE NOCASE); |
||
406 | INSERT INTO t2 SELECT * FROM t1; |
||
407 | CREATE INDEX i2 ON t2(x COLLATE NOCASE); |
||
408 | } |
||
409 | set sqlite_like_count 0 |
||
410 | queryplan { |
||
411 | SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1 |
||
412 | } |
||
413 | } {abc ABC {ABC abc xyz} abcd nosort {} i2} |
||
414 | do_test like-5.4 { |
||
415 | set sqlite_like_count |
||
416 | } 0 |
||
417 | do_test like-5.5 { |
||
418 | execsql { |
||
419 | PRAGMA case_sensitive_like=on; |
||
420 | } |
||
421 | set sqlite_like_count 0 |
||
422 | queryplan { |
||
423 | SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1 |
||
424 | } |
||
425 | } {abc abcd nosort {} i2} |
||
426 | do_test like-5.6 { |
||
427 | set sqlite_like_count |
||
428 | } 12 |
||
429 | do_test like-5.7 { |
||
430 | execsql { |
||
431 | PRAGMA case_sensitive_like=off; |
||
432 | } |
||
433 | set sqlite_like_count 0 |
||
434 | queryplan { |
||
435 | SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1 |
||
436 | } |
||
437 | } {abc abcd nosort {} i2} |
||
438 | do_test like-5.8 { |
||
439 | set sqlite_like_count |
||
440 | } 12 |
||
441 | do_test like-5.11 { |
||
442 | execsql {PRAGMA case_sensitive_like=off} |
||
443 | set sqlite_like_count 0 |
||
444 | queryplan { |
||
445 | SELECT x FROM t1 WHERE x LIKE 'ABC%' ORDER BY 1 |
||
446 | } |
||
447 | } {ABC {ABC abc xyz} abc abcd nosort {} i1} |
||
448 | do_test like-5.12 { |
||
449 | set sqlite_like_count |
||
450 | } 12 |
||
451 | do_test like-5.13 { |
||
452 | set sqlite_like_count 0 |
||
453 | queryplan { |
||
454 | SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1 |
||
455 | } |
||
456 | } {abc ABC {ABC abc xyz} abcd nosort {} i2} |
||
457 | do_test like-5.14 { |
||
458 | set sqlite_like_count |
||
459 | } 0 |
||
460 | do_test like-5.15 { |
||
461 | execsql { |
||
462 | PRAGMA case_sensitive_like=on; |
||
463 | } |
||
464 | set sqlite_like_count 0 |
||
465 | queryplan { |
||
466 | SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1 |
||
467 | } |
||
468 | } {ABC {ABC abc xyz} nosort {} i2} |
||
469 | do_test like-5.16 { |
||
470 | set sqlite_like_count |
||
471 | } 12 |
||
472 | do_test like-5.17 { |
||
473 | execsql { |
||
474 | PRAGMA case_sensitive_like=off; |
||
475 | } |
||
476 | set sqlite_like_count 0 |
||
477 | queryplan { |
||
478 | SELECT x FROM t2 WHERE x GLOB 'ABC*' ORDER BY 1 |
||
479 | } |
||
480 | } {ABC {ABC abc xyz} nosort {} i2} |
||
481 | do_test like-5.18 { |
||
482 | set sqlite_like_count |
||
483 | } 12 |
||
484 | |||
485 | # Boundary case. The prefix for a LIKE comparison is rounded up |
||
486 | # when constructing the comparison. Example: "ab" becomes "ac". |
||
487 | # In other words, the last character is increased by one. |
||
488 | # |
||
489 | # Make sure this happens correctly when the last character is a |
||
490 | # "z" and we are doing case-insensitive comparisons. |
||
491 | # |
||
492 | # Ticket #2959 |
||
493 | # |
||
494 | do_test like-5.21 { |
||
495 | execsql { |
||
496 | PRAGMA case_sensitive_like=off; |
||
497 | INSERT INTO t2 VALUES('ZZ-upper-upper'); |
||
498 | INSERT INTO t2 VALUES('zZ-lower-upper'); |
||
499 | INSERT INTO t2 VALUES('Zz-upper-lower'); |
||
500 | INSERT INTO t2 VALUES('zz-lower-lower'); |
||
501 | } |
||
502 | queryplan { |
||
503 | SELECT x FROM t2 WHERE x LIKE 'zz%'; |
||
504 | } |
||
505 | } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} |
||
506 | do_test like-5.22 { |
||
507 | queryplan { |
||
508 | SELECT x FROM t2 WHERE x LIKE 'zZ%'; |
||
509 | } |
||
510 | } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} |
||
511 | do_test like-5.23 { |
||
512 | queryplan { |
||
513 | SELECT x FROM t2 WHERE x LIKE 'Zz%'; |
||
514 | } |
||
515 | } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} |
||
516 | do_test like-5.24 { |
||
517 | queryplan { |
||
518 | SELECT x FROM t2 WHERE x LIKE 'ZZ%'; |
||
519 | } |
||
520 | } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} |
||
521 | do_test like-5.25 { |
||
522 | queryplan { |
||
523 | PRAGMA case_sensitive_like=on; |
||
524 | CREATE TABLE t3(x TEXT); |
||
525 | CREATE INDEX i3 ON t3(x); |
||
526 | INSERT INTO t3 VALUES('ZZ-upper-upper'); |
||
527 | INSERT INTO t3 VALUES('zZ-lower-upper'); |
||
528 | INSERT INTO t3 VALUES('Zz-upper-lower'); |
||
529 | INSERT INTO t3 VALUES('zz-lower-lower'); |
||
530 | SELECT x FROM t3 WHERE x LIKE 'zz%'; |
||
531 | } |
||
532 | } {zz-lower-lower nosort {} i3} |
||
533 | do_test like-5.26 { |
||
534 | queryplan { |
||
535 | SELECT x FROM t3 WHERE x LIKE 'zZ%'; |
||
536 | } |
||
537 | } {zZ-lower-upper nosort {} i3} |
||
538 | do_test like-5.27 { |
||
539 | queryplan { |
||
540 | SELECT x FROM t3 WHERE x LIKE 'Zz%'; |
||
541 | } |
||
542 | } {Zz-upper-lower nosort {} i3} |
||
543 | do_test like-5.28 { |
||
544 | queryplan { |
||
545 | SELECT x FROM t3 WHERE x LIKE 'ZZ%'; |
||
546 | } |
||
547 | } {ZZ-upper-upper nosort {} i3} |
||
548 | |||
549 | |||
550 | # ticket #2407 |
||
551 | # |
||
552 | # Make sure the LIKE prefix optimization does not strip off leading |
||
553 | # characters of the like pattern that happen to be quote characters. |
||
554 | # |
||
555 | do_test like-6.1 { |
||
556 | foreach x { 'abc 'bcd 'def 'ax } { |
||
557 | set x2 '[string map {' ''} $x]' |
||
558 | db eval "INSERT INTO t2 VALUES($x2)" |
||
559 | } |
||
560 | execsql { |
||
561 | SELECT * FROM t2 WHERE x LIKE '''a%' |
||
562 | } |
||
563 | } {'abc 'ax} |
||
564 | |||
565 | do_test like-7.1 { |
||
566 | execsql { |
||
567 | SELECT rowid, * FROM t1 WHERE rowid GLOB '1*' ORDER BY rowid; |
||
568 | } |
||
569 | } {1 a 10 ABC 11 CDE 12 {ABC abc xyz}} |
||
570 | |||
571 | # ticket #3345. |
||
572 | # |
||
573 | # Overloading the LIKE function with -1 for the number of arguments |
||
574 | # will overload both the 2-argument and the 3-argument LIKE. |
||
575 | # |
||
576 | do_test like-8.1 { |
||
577 | db eval { |
||
578 | CREATE TABLE t8(x); |
||
579 | INSERT INTO t8 VALUES('abcdef'); |
||
580 | INSERT INTO t8 VALUES('ghijkl'); |
||
581 | INSERT INTO t8 VALUES('mnopqr'); |
||
582 | SELECT 1, x FROM t8 WHERE x LIKE '%h%'; |
||
583 | SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; |
||
584 | } |
||
585 | } {1 ghijkl 2 ghijkl} |
||
586 | do_test like-8.2 { |
||
587 | proc newlike {args} {return 1} ;# Alternative LIKE is always return TRUE |
||
588 | db function like newlike ;# Uses -1 for nArg in sqlite3_create_function |
||
589 | db cache flush |
||
590 | db eval { |
||
591 | SELECT 1, x FROM t8 WHERE x LIKE '%h%'; |
||
592 | SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; |
||
593 | } |
||
594 | } {1 ghijkl 2 ghijkl} |
||
595 | do_test like-8.3 { |
||
596 | db function like -argcount 2 newlike |
||
597 | db eval { |
||
598 | SELECT 1, x FROM t8 WHERE x LIKE '%h%'; |
||
599 | SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; |
||
600 | } |
||
601 | } {1 abcdef 1 ghijkl 1 mnopqr 2 ghijkl} |
||
602 | do_test like-8.4 { |
||
603 | db function like -argcount 3 newlike |
||
604 | db eval { |
||
605 | SELECT 1, x FROM t8 WHERE x LIKE '%h%'; |
||
606 | SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; |
||
607 | } |
||
608 | } {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr} |
||
609 | |||
610 | |||
611 | ifcapable like_opt&&!icu { |
||
612 | # Evaluate SQL. Return the result set followed by the |
||
613 | # and the number of full-scan steps. |
||
614 | # |
||
615 | db close |
||
616 | sqlite3 db test.db |
||
617 | proc count_steps {sql} { |
||
618 | set r [db eval $sql] |
||
619 | lappend r scan [db status step] sort [db status sort] |
||
620 | } |
||
621 | do_test like-9.1 { |
||
622 | count_steps { |
||
623 | SELECT x FROM t2 WHERE x LIKE 'x%' |
||
624 | } |
||
625 | } {xyz scan 0 sort 0} |
||
626 | do_test like-9.2 { |
||
627 | count_steps { |
||
628 | SELECT x FROM t2 WHERE x LIKE '_y%' |
||
629 | } |
||
630 | } {xyz scan 19 sort 0} |
||
631 | do_test like-9.3.1 { |
||
632 | set res [sqlite3_exec_hex db { |
||
633 | SELECT x FROM t2 WHERE x LIKE '%78%25' |
||
634 | }] |
||
635 | } {0 {x xyz}} |
||
636 | ifcapable explain { |
||
637 | do_test like-9.3.2 { |
||
638 | set res [sqlite3_exec_hex db { |
||
639 | EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%78%25' |
||
640 | }] |
||
641 | regexp {INDEX i2} $res |
||
642 | } {1} |
||
643 | } |
||
644 | do_test like-9.4.1 { |
||
645 | sqlite3_exec_hex db {INSERT INTO t2 VALUES('%ffhello')} |
||
646 | set res [sqlite3_exec_hex db { |
||
647 | SELECT substr(x,2) AS x FROM t2 WHERE +x LIKE '%ff%25' |
||
648 | }] |
||
649 | } {0 {x hello}} |
||
650 | do_test like-9.4.2 { |
||
651 | set res [sqlite3_exec_hex db { |
||
652 | SELECT substr(x,2) AS x FROM t2 WHERE x LIKE '%ff%25' |
||
653 | }] |
||
654 | } {0 {x hello}} |
||
655 | ifcapable explain { |
||
656 | do_test like-9.4.3 { |
||
657 | set res [sqlite3_exec_hex db { |
||
658 | EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25' |
||
659 | }] |
||
660 | regexp {INDEX i2} $res |
||
661 | } {0} |
||
662 | } |
||
663 | do_test like-9.5.1 { |
||
664 | set res [sqlite3_exec_hex db { |
||
665 | SELECT x FROM t2 WHERE x LIKE '%fe%25' |
||
666 | }] |
||
667 | } {0 {}} |
||
668 | ifcapable explain { |
||
669 | do_test like-9.5.2 { |
||
670 | set res [sqlite3_exec_hex db { |
||
671 | EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25' |
||
672 | }] |
||
673 | regexp {INDEX i2} $res |
||
674 | } {1} |
||
675 | } |
||
676 | |||
677 | # Do an SQL statement. Append the search count to the end of the result. |
||
678 | # |
||
679 | proc count sql { |
||
680 | set ::sqlite_search_count 0 |
||
681 | set ::sqlite_like_count 0 |
||
682 | return [concat [execsql $sql] scan $::sqlite_search_count \ |
||
683 | like $::sqlite_like_count] |
||
684 | } |
||
685 | |||
686 | # The LIKE and GLOB optimizations do not work on columns with |
||
687 | # affinity other than TEXT. |
||
688 | # Ticket #3901 |
||
689 | # |
||
690 | do_test like-10.1 { |
||
691 | db close |
||
692 | sqlite3 db test.db |
||
693 | execsql { |
||
694 | CREATE TABLE t10( |
||
695 | a INTEGER PRIMARY KEY, |
||
696 | b INTEGER COLLATE nocase UNIQUE, |
||
697 | c NUMBER COLLATE nocase UNIQUE, |
||
698 | d BLOB COLLATE nocase UNIQUE, |
||
699 | e COLLATE nocase UNIQUE, |
||
700 | f TEXT COLLATE nocase UNIQUE |
||
701 | ); |
||
702 | INSERT INTO t10 VALUES(1,1,1,1,1,1); |
||
703 | INSERT INTO t10 VALUES(12,12,12,12,12,12); |
||
704 | INSERT INTO t10 VALUES(123,123,123,123,123,123); |
||
705 | INSERT INTO t10 VALUES(234,234,234,234,234,234); |
||
706 | INSERT INTO t10 VALUES(345,345,345,345,345,345); |
||
707 | INSERT INTO t10 VALUES(45,45,45,45,45,45); |
||
708 | } |
||
709 | count { |
||
710 | SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY +a; |
||
711 | } |
||
712 | } {12 123 scan 5 like 6} |
||
713 | do_test like-10.2 { |
||
714 | count { |
||
715 | SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY +a; |
||
716 | } |
||
717 | } {12 123 scan 5 like 6} |
||
718 | do_test like-10.3 { |
||
719 | count { |
||
720 | SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY +a; |
||
721 | } |
||
722 | } {12 123 scan 5 like 6} |
||
723 | do_test like-10.4 { |
||
724 | count { |
||
725 | SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a; |
||
726 | } |
||
727 | } {12 123 scan 5 like 6} |
||
728 | do_test like-10.5 { |
||
729 | count { |
||
730 | SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a; |
||
731 | } |
||
732 | } {12 123 scan 3 like 0} |
||
733 | do_test like-10.6 { |
||
734 | count { |
||
735 | SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a; |
||
736 | } |
||
737 | } {12 123 scan 5 like 6} |
||
738 | do_test like-10.10 { |
||
739 | execsql { |
||
740 | CREATE TABLE t10b( |
||
741 | a INTEGER PRIMARY KEY, |
||
742 | b INTEGER UNIQUE, |
||
743 | c NUMBER UNIQUE, |
||
744 | d BLOB UNIQUE, |
||
745 | e UNIQUE, |
||
746 | f TEXT UNIQUE |
||
747 | ); |
||
748 | INSERT INTO t10b SELECT * FROM t10; |
||
749 | } |
||
750 | count { |
||
751 | SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY +a; |
||
752 | } |
||
753 | } {12 123 scan 5 like 6} |
||
754 | do_test like-10.11 { |
||
755 | count { |
||
756 | SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY +a; |
||
757 | } |
||
758 | } {12 123 scan 5 like 6} |
||
759 | do_test like-10.12 { |
||
760 | count { |
||
761 | SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY +a; |
||
762 | } |
||
763 | } {12 123 scan 5 like 6} |
||
764 | do_test like-10.13 { |
||
765 | count { |
||
766 | SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a; |
||
767 | } |
||
768 | } {12 123 scan 5 like 6} |
||
769 | do_test like-10.14 { |
||
770 | count { |
||
771 | SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a; |
||
772 | } |
||
773 | } {12 123 scan 3 like 0} |
||
774 | do_test like-10.15 { |
||
775 | count { |
||
776 | SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a; |
||
777 | } |
||
778 | } {12 123 scan 5 like 6} |
||
779 | } |
||
780 | |||
781 | # LIKE and GLOB where the default collating sequence is not appropriate |
||
782 | # but an index with the appropriate collating sequence exists. |
||
783 | # |
||
784 | do_test like-11.0 { |
||
785 | execsql { |
||
786 | CREATE TABLE t11( |
||
787 | a INTEGER PRIMARY KEY, |
||
788 | b TEXT COLLATE nocase, |
||
789 | c TEXT COLLATE binary |
||
790 | ); |
||
791 | INSERT INTO t11 VALUES(1, 'a','a'); |
||
792 | INSERT INTO t11 VALUES(2, 'ab','ab'); |
||
793 | INSERT INTO t11 VALUES(3, 'abc','abc'); |
||
794 | INSERT INTO t11 VALUES(4, 'abcd','abcd'); |
||
795 | INSERT INTO t11 VALUES(5, 'A','A'); |
||
796 | INSERT INTO t11 VALUES(6, 'AB','AB'); |
||
797 | INSERT INTO t11 VALUES(7, 'ABC','ABC'); |
||
798 | INSERT INTO t11 VALUES(8, 'ABCD','ABCD'); |
||
799 | INSERT INTO t11 VALUES(9, 'x','x'); |
||
800 | INSERT INTO t11 VALUES(10, 'yz','yz'); |
||
801 | INSERT INTO t11 VALUES(11, 'X','X'); |
||
802 | INSERT INTO t11 VALUES(12, 'YZ','YZ'); |
||
803 | SELECT count(*) FROM t11; |
||
804 | } |
||
805 | } {12} |
||
806 | do_test like-11.1 { |
||
807 | queryplan { |
||
808 | PRAGMA case_sensitive_like=OFF; |
||
809 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; |
||
810 | } |
||
811 | } {abc abcd ABC ABCD nosort t11 *} |
||
812 | do_test like-11.2 { |
||
813 | queryplan { |
||
814 | PRAGMA case_sensitive_like=ON; |
||
815 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; |
||
816 | } |
||
817 | } {abc abcd nosort t11 *} |
||
818 | do_test like-11.3 { |
||
819 | queryplan { |
||
820 | PRAGMA case_sensitive_like=OFF; |
||
821 | CREATE INDEX t11b ON t11(b); |
||
822 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; |
||
823 | } |
||
824 | } {abc abcd ABC ABCD sort {} t11b} |
||
825 | do_test like-11.4 { |
||
826 | queryplan { |
||
827 | PRAGMA case_sensitive_like=ON; |
||
828 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a; |
||
829 | } |
||
830 | } {abc abcd nosort t11 *} |
||
831 | do_test like-11.5 { |
||
832 | queryplan { |
||
833 | PRAGMA case_sensitive_like=OFF; |
||
834 | DROP INDEX t11b; |
||
835 | CREATE INDEX t11bnc ON t11(b COLLATE nocase); |
||
836 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; |
||
837 | } |
||
838 | } {abc abcd ABC ABCD sort {} t11bnc} |
||
839 | do_test like-11.6 { |
||
840 | queryplan { |
||
841 | CREATE INDEX t11bb ON t11(b COLLATE binary); |
||
842 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; |
||
843 | } |
||
844 | } {abc abcd ABC ABCD sort {} t11bnc} |
||
845 | do_test like-11.7 { |
||
846 | queryplan { |
||
847 | PRAGMA case_sensitive_like=ON; |
||
848 | SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a; |
||
849 | } |
||
850 | } {abc abcd sort {} t11bb} |
||
851 | do_test like-11.8 { |
||
852 | queryplan { |
||
853 | PRAGMA case_sensitive_like=OFF; |
||
854 | SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a; |
||
855 | } |
||
856 | } {abc abcd sort {} t11bb} |
||
857 | do_test like-11.9 { |
||
858 | queryplan { |
||
859 | CREATE INDEX t11cnc ON t11(c COLLATE nocase); |
||
860 | CREATE INDEX t11cb ON t11(c COLLATE binary); |
||
861 | SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a; |
||
862 | } |
||
863 | } {abc abcd ABC ABCD sort {} t11cnc} |
||
864 | do_test like-11.10 { |
||
865 | queryplan { |
||
866 | SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a; |
||
867 | } |
||
868 | } {abc abcd sort {} t11cb} |
||
869 | |||
870 | |||
871 | finish_test |