wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
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