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