Debian Codegraph @

test/like.test

raw
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 set testprefix like
21
22 # Create some sample data to work with.
23 #
24 do_test like-1.0 {
25 execsql {
26 CREATE TABLE t1(x TEXT);
27 }
28 foreach str {
29 a
30 ab
31 abc
32 abcd
33
34 acd
35 abd
36 bc
37 bcd
38
39 xyz
40 ABC
41 CDE
42 {ABC abc xyz}
43 } {
44 db eval {INSERT INTO t1 VALUES(:str)}
45 }
46 execsql {
47 SELECT count(*) FROM t1;
48 }
49 } {12}
50
51 # Test that both case sensitive and insensitive version of LIKE work.
52 #
53 do_test like-1.1 {
54 execsql {
55 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
56 }
57 } {ABC abc}
58 do_test like-1.2 {
59 execsql {
60 SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
61 }
62 } {abc}
63 do_test like-1.3 {
64 execsql {
65 SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
66 }
67 } {ABC abc}
68 do_test like-1.4 {
69 execsql {
70 SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
71 }
72 } {ABC abc}
73 do_test like-1.5.1 {
74 # Use sqlite3_exec() to verify fix for ticket [25ee81271091] 2011-06-26
75 sqlite3_exec db {PRAGMA case_sensitive_like=on}
76 } {0 {}}
77 do_test like-1.5.2 {
78 execsql {
79 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
80 }
81 } {abc}
82 do_test like-1.5.3 {
83 execsql {
84 PRAGMA case_sensitive_like; -- no argument; does not change setting
85 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
86 }
87 } {abc}
88 do_test like-1.6 {
89 execsql {
90 SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
91 }
92 } {abc}
93 do_test like-1.7 {
94 execsql {
95 SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
96 }
97 } {ABC}
98 do_test like-1.8 {
99 execsql {
100 SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
101 }
102 } {}
103 do_test like-1.9 {
104 execsql {
105 PRAGMA case_sensitive_like=off;
106 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
107 }
108 } {ABC abc}
109 do_test like-1.10 {
110 execsql {
111 PRAGMA case_sensitive_like; -- No argument, does not change setting.
112 SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
113 }
114 } {ABC abc}
115
116 # Tests of the REGEXP operator
117 #
118 do_test like-2.1 {
119 proc test_regexp {a b} {
120 return [regexp $a $b]
121 }
122 db function regexp -argcount 2 test_regexp
123 execsql {
124 SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;
125 }
126 } {{ABC abc xyz} abc abcd}
127 do_test like-2.2 {
128 execsql {
129 SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1;
130 }
131 } {abc abcd}
132
133 # Tests of the MATCH operator
134 #
135 do_test like-2.3 {
136 proc test_match {a b} {
137 return [string match $a $b]
138 }
139 db function match -argcount 2 test_match
140 execsql {
141 SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1;
142 }
143 } {{ABC abc xyz} abc abcd}
144 do_test like-2.4 {
145 execsql {
146 SELECT x FROM t1 WHERE x MATCH 'abc*' ORDER BY 1;
147 }
148 } {abc abcd}
149
150 # For the remaining tests, we need to have the like optimizations
151 # enabled.
152 #
153 ifcapable !like_opt {
154 finish_test
155 return
156 }
157
158 # This procedure executes the SQL. Then it appends to the result the
159 # "sort" or "nosort" keyword (as in the cksort procedure above) then
160 # it appends the names of the table and index used.
161 #
162 proc queryplan {sql} {
163 set ::sqlite_sort_count 0
164 db cache flush
165 set data [execsql $sql]
166 if {$::sqlite_sort_count} {set x sort} {set x nosort}
167 lappend data $x
168 set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
169 # puts eqp=$eqp
170 foreach {a b c x} $eqp {
171 if {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\y} \
172 $x all ss as tab idx]} {
173 lappend data {} $idx
174 } elseif {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
175 $x all ss as tab idx]} {
176 lappend data $tab $idx
177 } elseif {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+)\y} $x all ss as tab]} {
178 lappend data $tab *
179 }
180 }
181 return $data
182 }
183
184 # Perform tests on the like optimization.
185 #
186 # With no index on t1.x and with case sensitivity turned off, no optimization
187 # is performed.
188 #
189 do_test like-3.1 {
190 set sqlite_like_count 0
191 queryplan {
192 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
193 }
194 } {ABC {ABC abc xyz} abc abcd sort t1 *}
195 do_test like-3.2 {
196 set sqlite_like_count
197 } {12}
198
199 # With an index on t1.x and case sensitivity on, optimize completely.
200 #
201 do_test like-3.3.100 {
202 set sqlite_like_count 0
203 execsql {
204 PRAGMA case_sensitive_like=on;
205 CREATE INDEX i1 ON t1(x);
206 }
207 queryplan {
208 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
209 }
210 } {abc abcd nosort {} i1}
211 do_test like-3.3.100.cnt {
212 set sqlite_like_count
213 } 0
214
215 # The like optimization works even when the pattern is a bound parameter
216 #
217 # Exception: It does not work if sqlite3_prepare() is used instead of
218 # sqlite3_prepare_v2(), as in that case the statement cannot be reprepared
219 # after the parameter is bound.
220 #
221 unset -nocomplain ::likepat
222 set ::likepat abc%
223 if {[permutation]!="prepare"} {
224 do_test like-3.3.102 {
225 set sqlite_like_count 0
226 queryplan {
227 SELECT x FROM t1 WHERE x LIKE $::likepat ORDER BY 1;
228 }
229 } {abc abcd nosort {} i1}
230 do_test like-3.3.103 {
231 set sqlite_like_count
232 } 0
233 }
234
235 # Except, the like optimization does not work for bound parameters if
236 # the query planner stability guarantee is active.
237 #
238 do_test like-3.3.104 {
239 set sqlite_like_count 0
240 sqlite3_db_config db QPSG 1
241 queryplan {
242 SELECT x FROM t1 WHERE x LIKE $::likepat ORDER BY 1;
243 }
244 } {abc abcd nosort {} i1}
245 do_test like-3.3.105 {
246 set sqlite_like_count
247 } 12
248
249 # The query planner stability guarantee does not disrupt explicit patterns
250 #
251 do_test like-3.3.105 {
252 set sqlite_like_count 0
253 queryplan {
254 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
255 }
256 } {abc abcd nosort {} i1}
257 do_test like-3.3.106 {
258 set sqlite_like_count
259 } 0
260 sqlite3_db_config db QPSG 0
261
262 # The LIKE optimization still works when the RHS is a string with no
263 # wildcard. Ticket [e090183531fc2747]
264 #
265 do_test like-3.4.2 {
266 queryplan {
267 SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1;
268 }
269 } {a nosort {} i1}
270 do_test like-3.4.3 {
271 queryplan {
272 SELECT x FROM t1 WHERE x LIKE 'ab' ORDER BY 1;
273 }
274 } {ab nosort {} i1}
275 do_test like-3.4.4 {
276 queryplan {
277 SELECT x FROM t1 WHERE x LIKE 'abcd' ORDER BY 1;
278 }
279 } {abcd nosort {} i1}
280 do_test like-3.4.5 {
281 queryplan {
282 SELECT x FROM t1 WHERE x LIKE 'abcde' ORDER BY 1;
283 }
284 } {nosort {} i1}
285
286
287 # Partial optimization when the pattern does not end in '%'
288 #
289 do_test like-3.5 {
290 set sqlite_like_count 0
291 queryplan {
292 SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;
293 }
294 } {abc nosort {} i1}
295 do_test like-3.6 {
296 set sqlite_like_count
297 } 6
298 do_test like-3.7 {
299 set sqlite_like_count 0
300 queryplan {
301 SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1;
302 }
303 } {abcd abd nosort {} i1}
304 do_test like-3.8 {
305 set sqlite_like_count
306 } 4
307 do_test like-3.9 {
308 set sqlite_like_count 0
309 queryplan {
310 SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1;
311 }
312 } {abc abcd nosort {} i1}
313 do_test like-3.10 {
314 set sqlite_like_count
315 } 6
316
317 # No optimization when the pattern begins with a wildcard.
318 # Note that the index is still used but only for sorting.
319 #
320 do_test like-3.11 {
321 set sqlite_like_count 0
322 queryplan {
323 SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1;
324 }
325 } {abcd bcd nosort {} i1}
326 do_test like-3.12 {
327 set sqlite_like_count
328 } 12
329
330 # No optimization for case insensitive LIKE
331 #
332 do_test like-3.13 {
333 set sqlite_like_count 0
334 db eval {PRAGMA case_sensitive_like=off;}
335 queryplan {
336 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
337 }
338 } {ABC {ABC abc xyz} abc abcd nosort {} i1}
339 do_test like-3.14 {
340 set sqlite_like_count
341 } 12
342
343 # No optimization without an index.
344 #
345 do_test like-3.15 {
346 set sqlite_like_count 0
347 db eval {
348 PRAGMA case_sensitive_like=on;
349 DROP INDEX i1;
350 }
351 queryplan {
352 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
353 }
354 } {abc abcd sort t1 *}
355 do_test like-3.16 {
356 set sqlite_like_count
357 } 12
358
359 # No GLOB optimization without an index.
360 #
361 do_test like-3.17 {
362 set sqlite_like_count 0
363 queryplan {
364 SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
365 }
366 } {abc abcd sort t1 *}
367 do_test like-3.18 {
368 set sqlite_like_count
369 } 12
370
371 # GLOB is optimized regardless of the case_sensitive_like setting.
372 #
373 do_test like-3.19 {
374 set sqlite_like_count 0
375 db eval {CREATE INDEX i1 ON t1(x);}
376 queryplan {
377 SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
378 }
379 } {abc abcd nosort {} i1}
380 do_test like-3.20 {
381 set sqlite_like_count
382 } 0
383 do_test like-3.21 {
384 set sqlite_like_count 0
385 db eval {PRAGMA case_sensitive_like=on;}
386 queryplan {
387 SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
388 }
389 } {abc abcd nosort {} i1}
390 do_test like-3.22 {
391 set sqlite_like_count
392 } 0
393 do_test like-3.23 {
394 set sqlite_like_count 0
395 db eval {PRAGMA case_sensitive_like=off;}
396 queryplan {
397 SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
398 }
399 } {abd acd nosort {} i1}
400 do_test like-3.24 {
401 set sqlite_like_count
402 } 6
403
404 # GLOB optimization when there is no wildcard. Ticket [e090183531fc2747]
405 #
406 do_test like-3.25 {
407 queryplan {
408 SELECT x FROM t1 WHERE x GLOB 'a' ORDER BY 1;
409 }
410 } {a nosort {} i1}
411 do_test like-3.26 {
412 queryplan {
413 SELECT x FROM t1 WHERE x GLOB 'abcd' ORDER BY 1;
414 }
415 } {abcd nosort {} i1}
416 do_test like-3.27 {
417 queryplan {
418 SELECT x FROM t1 WHERE x GLOB 'abcde' ORDER BY 1;
419 }
420 } {nosort {} i1}
421
422
423
424 # No optimization if the LHS of the LIKE is not a column name or
425 # if the RHS is not a string.
426 #
427 do_test like-4.1 {
428 execsql {PRAGMA case_sensitive_like=on}
429 set sqlite_like_count 0
430 queryplan {
431 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1
432 }
433 } {abc abcd nosort {} i1}
434 do_test like-4.2 {
435 set sqlite_like_count
436 } 0
437 do_test like-4.3 {
438 set sqlite_like_count 0
439 queryplan {
440 SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1
441 }
442 } {abc abcd nosort {} i1}
443 do_test like-4.4 {
444 set sqlite_like_count
445 } 12
446 do_test like-4.5 {
447 set sqlite_like_count 0
448 queryplan {
449 SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1
450 }
451 } {abc abcd nosort {} i1}
452 do_test like-4.6 {
453 set sqlite_like_count
454 } 12
455
456 # Collating sequences on the index disable the LIKE optimization.
457 # Or if the NOCASE collating sequence is used, the LIKE optimization
458 # is enabled when case_sensitive_like is OFF.
459 #
460 do_test like-5.1 {
461 execsql {PRAGMA case_sensitive_like=off}
462 set sqlite_like_count 0
463 queryplan {
464 SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1
465 }
466 } {ABC {ABC abc xyz} abc abcd nosort {} i1}
467 do_test like-5.2 {
468 set sqlite_like_count
469 } 12
470 do_test like-5.3 {
471 execsql {
472 CREATE TABLE t2(x TEXT COLLATE NOCASE);
473 INSERT INTO t2 SELECT * FROM t1 ORDER BY rowid;
474 CREATE INDEX i2 ON t2(x COLLATE NOCASE);
475 }
476 set sqlite_like_count 0
477 queryplan {
478 SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
479 }
480 } {abc ABC {ABC abc xyz} abcd nosort {} i2}
481 do_test like-5.4 {
482 set sqlite_like_count
483 } 0
484 do_test like-5.5 {
485 execsql {
486 PRAGMA case_sensitive_like=on;
487 }
488 set sqlite_like_count 0
489 queryplan {
490 SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
491 }
492 } {abc abcd nosort {} i2}
493 do_test like-5.6 {
494 set sqlite_like_count
495 } 12
496 do_test like-5.7 {
497 execsql {
498 PRAGMA case_sensitive_like=off;
499 }
500 set sqlite_like_count 0
501 queryplan {
502 SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1
503 }
504 } {abc abcd nosort {} i2}
505 do_test like-5.8 {
506 set sqlite_like_count
507 } 12
508 do_test like-5.11 {
509 execsql {PRAGMA case_sensitive_like=off}
510 set sqlite_like_count 0
511 queryplan {
512 SELECT x FROM t1 WHERE x LIKE 'ABC%' ORDER BY 1
513 }
514 } {ABC {ABC abc xyz} abc abcd nosort {} i1}
515 do_test like-5.12 {
516 set sqlite_like_count
517 } 12
518 do_test like-5.13 {
519 set sqlite_like_count 0
520 queryplan {
521 SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1
522 }
523 } {abc ABC {ABC abc xyz} abcd nosort {} i2}
524 do_test like-5.14 {
525 set sqlite_like_count
526 } 0
527 do_test like-5.15 {
528 execsql {
529 PRAGMA case_sensitive_like=on;
530 }
531 set sqlite_like_count 0
532 queryplan {
533 SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1
534 }
535 } {ABC {ABC abc xyz} nosort {} i2}
536 do_test like-5.16 {
537 set sqlite_like_count
538 } 12
539 do_test like-5.17 {
540 execsql {
541 PRAGMA case_sensitive_like=off;
542 }
543 set sqlite_like_count 0
544 queryplan {
545 SELECT x FROM t2 WHERE x GLOB 'ABC*' ORDER BY 1
546 }
547 } {ABC {ABC abc xyz} nosort {} i2}
548 do_test like-5.18 {
549 set sqlite_like_count
550 } 12
551
552 # Boundary case. The prefix for a LIKE comparison is rounded up
553 # when constructing the comparison. Example: "ab" becomes "ac".
554 # In other words, the last character is increased by one.
555 #
556 # Make sure this happens correctly when the last character is a
557 # "z" and we are doing case-insensitive comparisons.
558 #
559 # Ticket #2959
560 #
561 do_test like-5.21 {
562 execsql {
563 PRAGMA case_sensitive_like=off;
564 INSERT INTO t2 VALUES('ZZ-upper-upper');
565 INSERT INTO t2 VALUES('zZ-lower-upper');
566 INSERT INTO t2 VALUES('Zz-upper-lower');
567 INSERT INTO t2 VALUES('zz-lower-lower');
568 }
569 queryplan {
570 SELECT x FROM t2 WHERE x LIKE 'zz%';
571 }
572 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
573 do_test like-5.22 {
574 queryplan {
575 SELECT x FROM t2 WHERE x LIKE 'zZ%';
576 }
577 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
578 do_test like-5.23 {
579 queryplan {
580 SELECT x FROM t2 WHERE x LIKE 'Zz%';
581 }
582 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
583 do_test like-5.24 {
584 queryplan {
585 SELECT x FROM t2 WHERE x LIKE 'ZZ%';
586 }
587 } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
588 do_test like-5.25 {
589 db eval {
590 PRAGMA case_sensitive_like=on;
591 CREATE TABLE t3(x TEXT);
592 CREATE INDEX i3 ON t3(x);
593 INSERT INTO t3 VALUES('ZZ-upper-upper');
594 INSERT INTO t3 VALUES('zZ-lower-upper');
595 INSERT INTO t3 VALUES('Zz-upper-lower');
596 INSERT INTO t3 VALUES('zz-lower-lower');
597 }
598 queryplan {
599 SELECT x FROM t3 WHERE x LIKE 'zz%';
600 }
601 } {zz-lower-lower nosort {} i3}
602 do_test like-5.26 {
603 queryplan {
604 SELECT x FROM t3 WHERE x LIKE 'zZ%';
605 }
606 } {zZ-lower-upper nosort {} i3}
607 do_test like-5.27 {
608 queryplan {
609 SELECT x FROM t3 WHERE x LIKE 'Zz%';
610 }
611 } {Zz-upper-lower nosort {} i3}
612 do_test like-5.28 {
613 queryplan {
614 SELECT x FROM t3 WHERE x LIKE 'ZZ%';
615 }
616 } {ZZ-upper-upper nosort {} i3}
617
618
619 # ticket #2407
620 #
621 # Make sure the LIKE prefix optimization does not strip off leading
622 # characters of the like pattern that happen to be quote characters.
623 #
624 do_test like-6.1 {
625 foreach x { 'abc 'bcd 'def 'ax } {
626 set x2 '[string map {' ''} $x]'
627 db eval "INSERT INTO t2 VALUES($x2)"
628 }
629 execsql {
630 SELECT * FROM t2 WHERE x LIKE '''a%'
631 }
632 } {'abc 'ax}
633
634 do_test like-7.1 {
635 execsql {
636 SELECT rowid, * FROM t1 WHERE rowid GLOB '1*' ORDER BY rowid;
637 }
638 } {1 a 10 ABC 11 CDE 12 {ABC abc xyz}}
639
640 # ticket #3345.
641 #
642 # Overloading the LIKE function with -1 for the number of arguments
643 # will overload both the 2-argument and the 3-argument LIKE.
644 #
645 do_test like-8.1 {
646 db eval {
647 CREATE TABLE t8(x);
648 INSERT INTO t8 VALUES('abcdef');
649 INSERT INTO t8 VALUES('ghijkl');
650 INSERT INTO t8 VALUES('mnopqr');
651 SELECT 1, x FROM t8 WHERE x LIKE '%h%';
652 SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
653 }
654 } {1 ghijkl 2 ghijkl}
655 do_test like-8.2 {
656 proc newlike {args} {return 1} ;# Alternative LIKE is always return TRUE
657 db function like newlike ;# Uses -1 for nArg in sqlite3_create_function
658 db cache flush
659 db eval {
660 SELECT 1, x FROM t8 WHERE x LIKE '%h%';
661 SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
662 }
663 } {1 ghijkl 2 ghijkl}
664 do_test like-8.3 {
665 db function like -argcount 2 newlike
666 db eval {
667 SELECT 1, x FROM t8 WHERE x LIKE '%h%';
668 SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
669 }
670 } {1 abcdef 1 ghijkl 1 mnopqr 2 ghijkl}
671 do_test like-8.4 {
672 db function like -argcount 3 newlike
673 db eval {
674 SELECT 1, x FROM t8 WHERE x LIKE '%h%';
675 SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
676 }
677 } {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr}
678
679
680 ifcapable like_opt&&!icu {
681 # Evaluate SQL. Return the result set followed by the
682 # and the number of full-scan steps.
683 #
684 db close
685 sqlite3 db test.db
686 proc count_steps {sql} {
687 set r [db eval $sql]
688 lappend r scan [db status step] sort [db status sort]
689 }
690 do_test like-9.1 {
691 count_steps {
692 SELECT x FROM t2 WHERE x LIKE 'x%'
693 }
694 } {xyz scan 0 sort 0}
695 do_test like-9.2 {
696 count_steps {
697 SELECT x FROM t2 WHERE x LIKE '_y%'
698 }
699 } {xyz scan 19 sort 0}
700 do_test like-9.3.1 {
701 set res [sqlite3_exec_hex db {
702 SELECT x FROM t2 WHERE x LIKE '%78%25'
703 }]
704 } {0 {x xyz}}
705 ifcapable explain {
706 do_test like-9.3.2 {
707 set res [sqlite3_exec_hex db {
708 EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%78%25'
709 }]
710 regexp {INDEX i2} $res
711 } {1}
712 }
713 do_test like-9.4.1 {
714 sqlite3_exec_hex db {INSERT INTO t2 VALUES('%ffhello')}
715 set res [sqlite3_exec_hex db {
716 SELECT substr(x,2) AS x FROM t2 WHERE +x LIKE '%ff%25'
717 }]
718 } {0 {x hello}}
719 do_test like-9.4.2 {
720 set res [sqlite3_exec_hex db {
721 SELECT substr(x,2) AS x FROM t2 WHERE x LIKE '%ff%25'
722 }]
723 } {0 {x hello}}
724 ifcapable explain {
725 do_test like-9.4.3 {
726 set res [sqlite3_exec_hex db {
727 EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25'
728 }]
729 regexp {SCAN t2} $res
730 } {1}
731 }
732 do_test like-9.5.1 {
733 set res [sqlite3_exec_hex db {
734 SELECT 1 FROM t2 WHERE x LIKE '%fe%25'
735 }]
736 } {0 {1 1}}
737 ifcapable explain {
738 do_test like-9.5.2 {
739 set res [sqlite3_exec_hex db {
740 EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25'
741 }]
742 regexp {INDEX i2} $res
743 } {0}
744 }
745
746 # Do an SQL statement. Append the search count to the end of the result.
747 #
748 proc count sql {
749 set ::sqlite_search_count 0
750 set ::sqlite_like_count 0
751 return [concat [execsql $sql] scan $::sqlite_search_count \
752 like $::sqlite_like_count]
753 }
754
755 # The LIKE and GLOB optimizations do not work on columns with
756 # affinity other than TEXT.
757 # Ticket #3901
758 #
759 do_test like-10.1 {
760 db close
761 sqlite3 db test.db
762 execsql {
763 CREATE TABLE t10(
764 a INTEGER PRIMARY KEY,
765 b INTEGER COLLATE nocase UNIQUE,
766 c NUMBER COLLATE nocase UNIQUE,
767 d BLOB COLLATE nocase UNIQUE,
768 e COLLATE nocase UNIQUE,
769 f TEXT COLLATE nocase UNIQUE
770 );
771 INSERT INTO t10 VALUES(1,1,1,1,1,1);
772 INSERT INTO t10 VALUES(12,12,12,12,12,12);
773 INSERT INTO t10 VALUES(123,123,123,123,123,123);
774 INSERT INTO t10 VALUES(234,234,234,234,234,234);
775 INSERT INTO t10 VALUES(345,345,345,345,345,345);
776 INSERT INTO t10 VALUES(45,45,45,45,45,45);
777 }
778 count {
779 SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY +a;
780 }
781 } {12 123 scan 5 like 6}
782 do_test like-10.2 {
783 count {
784 SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY +a;
785 }
786 } {12 123 scan 5 like 6}
787 do_test like-10.3 {
788 count {
789 SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY +a;
790 }
791 } {12 123 scan 5 like 6}
792 do_test like-10.4 {
793 count {
794 SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a;
795 }
796 } {12 123 scan 5 like 6}
797 ifcapable like_match_blobs {
798 do_test like-10.5a {
799 count {
800 SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a;
801 }
802 } {12 123 scan 4 like 0}
803 } else {
804 do_test like-10.5b {
805 count {
806 SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a;
807 }
808 } {12 123 scan 3 like 0}
809 }
810 do_test like-10.6 {
811 count {
812 SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a;
813 }
814 } {12 123 scan 5 like 6}
815 do_test like-10.10 {
816 execsql {
817 CREATE TABLE t10b(
818 a INTEGER PRIMARY KEY,
819 b INTEGER UNIQUE,
820 c NUMBER UNIQUE,
821 d BLOB UNIQUE,
822 e UNIQUE,
823 f TEXT UNIQUE
824 );
825 INSERT INTO t10b SELECT * FROM t10;
826 }
827 count {
828 SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY +a;
829 }
830 } {12 123 scan 5 like 6}
831 do_test like-10.11 {
832 count {
833 SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY +a;
834 }
835 } {12 123 scan 5 like 6}
836 do_test like-10.12 {
837 count {
838 SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY +a;
839 }
840 } {12 123 scan 5 like 6}
841 do_test like-10.13 {
842 count {
843 SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a;
844 }
845 } {12 123 scan 5 like 6}
846 ifcapable like_match_blobs {
847 do_test like-10.14 {
848 count {
849 SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a;
850 }
851 } {12 123 scan 4 like 0}
852 } else {
853 do_test like-10.14 {
854 count {
855 SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a;
856 }
857 } {12 123 scan 3 like 0}
858 }
859 do_test like-10.15 {
860 count {
861 SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a;
862 }
863 } {12 123 scan 5 like 6}
864 }
865
866 # LIKE and GLOB where the default collating sequence is not appropriate
867 # but an index with the appropriate collating sequence exists.
868 #
869 do_test like-11.0 {
870 execsql {
871 CREATE TABLE t11(
872 a INTEGER PRIMARY KEY,
873 b TEXT COLLATE nocase,
874 c TEXT COLLATE binary
875 );
876 INSERT INTO t11 VALUES(1, 'a','a');
877 INSERT INTO t11 VALUES(2, 'ab','ab');
878 INSERT INTO t11 VALUES(3, 'abc','abc');
879 INSERT INTO t11 VALUES(4, 'abcd','abcd');
880 INSERT INTO t11 VALUES(5, 'A','A');
881 INSERT INTO t11 VALUES(6, 'AB','AB');
882 INSERT INTO t11 VALUES(7, 'ABC','ABC');
883 INSERT INTO t11 VALUES(8, 'ABCD','ABCD');
884 INSERT INTO t11 VALUES(9, 'x','x');
885 INSERT INTO t11 VALUES(10, 'yz','yz');
886 INSERT INTO t11 VALUES(11, 'X','X');
887 INSERT INTO t11 VALUES(12, 'YZ','YZ');
888 SELECT count(*) FROM t11;
889 }
890 } {12}
891 do_test like-11.1 {
892 db eval {PRAGMA case_sensitive_like=OFF;}
893 queryplan {
894 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
895 }
896 } {abc abcd ABC ABCD nosort t11 *}
897 do_test like-11.2 {
898 db eval {PRAGMA case_sensitive_like=ON;}
899 queryplan {
900 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
901 }
902 } {abc abcd nosort t11 *}
903 do_test like-11.3 {
904 db eval {
905 PRAGMA case_sensitive_like=OFF;
906 CREATE INDEX t11b ON t11(b);
907 }
908 queryplan {
909 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
910 }
911 } {abc abcd ABC ABCD sort {} t11b}
912 do_test like-11.4 {
913 db eval {PRAGMA case_sensitive_like=ON;}
914 queryplan {
915 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
916 }
917 } {abc abcd nosort t11 *}
918 do_test like-11.5 {
919 db eval {
920 PRAGMA case_sensitive_like=OFF;
921 DROP INDEX t11b;
922 CREATE INDEX t11bnc ON t11(b COLLATE nocase);
923 }
924 queryplan {
925 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
926 }
927 } {abc abcd ABC ABCD sort {} t11bnc}
928 do_test like-11.6 {
929 db eval {CREATE INDEX t11bb ON t11(b COLLATE binary);}
930 queryplan {
931 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
932 }
933 } {abc abcd ABC ABCD sort {} t11bnc}
934 do_test like-11.7 {
935 db eval {PRAGMA case_sensitive_like=ON;}
936 queryplan {
937 SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
938 }
939 } {abc abcd sort {} t11bb}
940 do_test like-11.8 {
941 db eval {PRAGMA case_sensitive_like=OFF;}
942 queryplan {
943 SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a;
944 }
945 } {abc abcd sort {} t11bb}
946 do_test like-11.9 {
947 db eval {
948 CREATE INDEX t11cnc ON t11(c COLLATE nocase);
949 CREATE INDEX t11cb ON t11(c COLLATE binary);
950 }
951 queryplan {
952 SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a;
953 }
954 } {abc abcd ABC ABCD sort {} t11cnc}
955 do_test like-11.10 {
956 queryplan {
957 SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a;
958 }
959 } {abc abcd sort {} t11cb}
960
961 # A COLLATE clause on the pattern does not change the result of a
962 # LIKE operator.
963 #
964 do_execsql_test like-12.1 {
965 CREATE TABLE t12nc(id INTEGER, x TEXT UNIQUE COLLATE nocase);
966 INSERT INTO t12nc VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF');
967 CREATE TABLE t12b(id INTEGER, x TEXT UNIQUE COLLATE binary);
968 INSERT INTO t12b VALUES(1,'abcde'),(2,'uvwxy'),(3,'ABCDEF');
969 SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;
970 } {1 3}
971 do_execsql_test like-12.2 {
972 SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;
973 } {1 3}
974 do_execsql_test like-12.3 {
975 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
976 } {1 3}
977 do_execsql_test like-12.4 {
978 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
979 } {1 3}
980 do_execsql_test like-12.5 {
981 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
982 } {1 3}
983 do_execsql_test like-12.6 {
984 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
985 } {1 3}
986
987 # Adding a COLLATE clause to the pattern of a LIKE operator does nothing
988 # to change the suitability of using an index to satisfy that LIKE
989 # operator.
990 #
991 do_execsql_test like-12.11 {
992 EXPLAIN QUERY PLAN
993 SELECT id FROM t12nc WHERE x LIKE 'abc%' ORDER BY +id;
994 } {/SEARCH/}
995 do_execsql_test like-12.12 {
996 EXPLAIN QUERY PLAN
997 SELECT id FROM t12b WHERE x LIKE 'abc%' ORDER BY +id;
998 } {/SCAN/}
999 do_execsql_test like-12.13 {
1000 EXPLAIN QUERY PLAN
1001 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
1002 } {/SEARCH/}
1003 do_execsql_test like-12.14 {
1004 EXPLAIN QUERY PLAN
1005 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE nocase ORDER BY +id;
1006 } {/SCAN/}
1007 do_execsql_test like-12.15 {
1008 EXPLAIN QUERY PLAN
1009 SELECT id FROM t12nc WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
1010 } {/SEARCH/}
1011 do_execsql_test like-12.16 {
1012 EXPLAIN QUERY PLAN
1013 SELECT id FROM t12b WHERE x LIKE 'abc%' COLLATE binary ORDER BY +id;
1014 } {/SCAN/}
1015
1016 # Ticket [https://sqlite.org/src/tktview/80369eddd5c94d49f7fbbcf5]
1017 # 2016-01-20
1018 #
1019 do_execsql_test like-13.1 {
1020 SELECT char(0x304d) LIKE char(0x306d);
1021 } {0}
1022 do_execsql_test like-13.2 {
1023 SELECT char(0x4d) LIKE char(0x306d);
1024 } {0}
1025 do_execsql_test like-13.3 {
1026 SELECT char(0x304d) LIKE char(0x6d);
1027 } {0}
1028 do_execsql_test like-13.4 {
1029 SELECT char(0x4d) LIKE char(0x6d);
1030 } {1}
1031
1032 # Performance testing for patterns with many wildcards. These LIKE and GLOB
1033 # patterns were quite slow with SQLite 3.15.2 and earlier.
1034 #
1035 do_test like-14.1 {
1036 set x [lindex [time {
1037 db one {SELECT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaz'GLOB'*a*a*a*a*a*a*a*a*y'}
1038 }] 0]
1039 set tlimit [expr {1000 * $::sqlite_options(configslower)}]
1040 puts -nonewline " ($x ms - want less than $tlimit) "
1041 expr {$x<$tlimit}
1042 } {1}
1043 ifcapable !icu {
1044 do_test like-14.2 {
1045 set x [lindex [time {
1046 db one {SELECT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaz'LIKE'%a%a%a%a%a%a%a%a%y'}
1047 }] 0]
1048 set tlimit [expr {1000 * $::sqlite_options(configslower)}]
1049 puts -nonewline " ($x ms - want less than $tlimit) "
1050 expr {$x<$tlimit}
1051 } {1}
1052 }
1053
1054 ifcapable !icu {
1055 # As of 2017-07-27 (3.21.0) the LIKE optimization works with ESCAPE as
1056 # long as the ESCAPE is a single-byte literal.
1057 #
1058 db close
1059 sqlite3 db :memory:
1060 do_execsql_test like-15.100 {
1061 CREATE TABLE t15(x TEXT COLLATE nocase, y, PRIMARY KEY(x));
1062 INSERT INTO t15(x,y) VALUES
1063 ('abcde',1), ('ab%de',2), ('a_cde',3),
1064 ('uvwxy',11),('uvwx%',12),('uvwx_',13),
1065 ('_bcde',21),('%bcde',22),
1066 ('abcd_',31),('abcd%',32),
1067 ('ab%xy',41);
1068 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';
1069 } {2}
1070 do_execsql_test like-15.101 {
1071 EXPLAIN QUERY PLAN
1072 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '/';
1073 } {/SEARCH/}
1074 do_execsql_test like-15.102 {
1075 EXPLAIN QUERY PLAN
1076 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '//';
1077 } {/SCAN/}
1078 do_execsql_test like-15.103 {
1079 EXPLAIN QUERY PLAN
1080 SELECT y FROM t15 WHERE x LIKE 'ab/%d%' ESCAPE '';
1081 } {/SCAN/}
1082 do_execsql_test like-15.110 {
1083 SELECT y FROM t15 WHERE x LIKE 'abcdx%%' ESCAPE 'x';
1084 } {32}
1085 do_execsql_test like-15.111 {
1086 SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y
1087 } {2 41}
1088 do_execsql_test like-15.112 {
1089 EXPLAIN QUERY PLAN
1090 SELECT y FROM t15 WHERE x LIKE 'abx%%' ESCAPE 'x' ORDER BY +y
1091 } {/SEARCH/}
1092 do_execsql_test like-15.120 {
1093 SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
1094 } {22}
1095 do_execsql_test like-15.121 {
1096 EXPLAIN QUERY PLAN
1097 SELECT y FROM t15 WHERE x LIKE '/%bc%' ESCAPE '/';
1098 } {/SEARCH/}
1099 }
1100
1101 #-------------------------------------------------------------------------
1102 # Tests for ticket [b1d8c79314].
1103 #
1104 reset_db
1105 do_execsql_test 16.0 {
1106 CREATE TABLE t1(a INTEGER COLLATE NOCASE);
1107 CREATE INDEX i1 ON t1(a);
1108 INSERT INTO t1 VALUES(' 1x');
1109 INSERT INTO t1 VALUES(' 1-');
1110 }
1111 do_execsql_test 16.1 {
1112 SELECT * FROM t1 WHERE a LIKE ' 1%';
1113 } {{ 1x} { 1-}}
1114 do_execsql_test 16.2 {
1115 SELECT * FROM t1 WHERE a LIKE ' 1-';
1116 } {{ 1-}}
1117
1118 # 2020-03-19
1119 # The ESCAPE clause on LIKE takes precedence over wildcards
1120 #
1121 do_execsql_test 17.0 {
1122 DROP TABLE IF EXISTS t1;
1123 CREATE TABLE t1(id INTEGER PRIMARY KEY, x TEXT);
1124 INSERT INTO t1 VALUES
1125 (1,'abcde'),
1126 (2,'abc_'),
1127 (3,'abc__'),
1128 (4,'abc%'),
1129 (5,'abc%%');
1130 SELECT id FROM t1 WHERE x LIKE 'abc%%' ESCAPE '%';
1131 } {4}
1132 do_execsql_test 17.1 {
1133 SELECT id FROM t1 WHERE x LIKE 'abc__' ESCAPE '_';
1134 } {2}
1135
1136 # 2021-02-15 ticket c0aeea67d58ae0fd
1137 #
1138 do_execsql_test 17.1 {
1139 SELECT 'x' LIKE '%' ESCAPE '_';
1140 } {1}
1141
1142
1143 # 2023-08-15 https://sqlite.org/forum/forumpost/925dc9f67804c540
1144 #
1145 reset_db
1146 sqlite3_db_config db DEFENSIVE 1
1147 db eval {PRAGMA trusted_schema=OFF}
1148 do_execsql_test 18.0 {
1149 CREATE TABLE t1(x INT, y TEXT);
1150 INSERT INTO t1 VALUES(1,'abc'),(2,'ABC'),(3,'Abc');
1151 CREATE VIEW t2 AS SELECT * FROM t1 WHERE y LIKE 'a%';
1152 SELECT * FROM t2;
1153 } {1 abc 2 ABC 3 Abc}
1154 do_execsql_test 18.1 {
1155 PRAGMA case_sensitive_like=OFF;
1156 SELECT * FROM t2;
1157 } {1 abc 2 ABC 3 Abc}
1158 do_execsql_test 18.2 {
1159 PRAGMA case_sensitive_like=ON;
1160 SELECT * FROM t2;
1161 } {1 abc}
1162
1163 finish_test