Debian Codegraph @

test/like3.test

raw
1 # 2015-03-06
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 #
12 # This file implements regression tests for SQLite library. The
13 # focus of this file is testing the LIKE and GLOB operators and
14 # in particular the optimizations that occur to help those operators
15 # run faster and that those optimizations work correctly when there
16 # are both strings and blobs being tested.
17 #
18 # Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the following
19 # SQL was not working correctly:
20 #
21 # CREATE TABLE t1(x TEXT UNIQUE COLLATE nocase);
22 # INSERT INTO t1(x) VALUES(x'616263');
23 # SELECT 'query-1', x FROM t1 WHERE x LIKE 'a%';
24 # SELECT 'query-2', x FROM t1 WHERE +x LIKE 'a%';
25 #
26 # This script verifies that it works right now.
27 #
28
29 set testdir [file dirname $argv0]
30 source $testdir/tester.tcl
31
32 ifcapable !like_match_blobs {
33 finish_test
34 return
35 }
36
37 do_execsql_test like3-1.1 {
38 PRAGMA encoding=UTF8;
39 CREATE TABLE t1(a,b TEXT COLLATE nocase);
40 INSERT INTO t1(a,b)
41 VALUES(1,'abc'),
42 (2,'ABX'),
43 (3,'BCD'),
44 (4,x'616263'),
45 (5,x'414258'),
46 (6,x'424344');
47 CREATE INDEX t1ba ON t1(b,a);
48
49 SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a;
50 } {1 abc 2 ABX 4 abc 5 ABX}
51 do_execsql_test like3-1.2 {
52 SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a;
53 } {1 abc 2 ABX 4 abc 5 ABX}
54
55 do_execsql_test like3-2.0 {
56 CREATE TABLE t2(a, b TEXT);
57 INSERT INTO t2 SELECT a, b FROM t1;
58 CREATE INDEX t2ba ON t2(b,a);
59 SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a;
60 } {1 abc 4 abc}
61 do_execsql_test like3-2.1 {
62 SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a;
63 } {1 abc 4 abc}
64 do_execsql_test like3-2.2 {
65 SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*'
66 } {4 abc}
67 do_execsql_test like3-2.3 {
68 SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*'
69 } {4 abc}
70 do_execsql_test like3-2.4 {
71 SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162'
72 } {4 abc}
73 do_execsql_test like3-2.5 {
74 SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162'
75 } {4 abc}
76
77 do_execsql_test like3-3.0 {
78 CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase);
79 INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz');
80 INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3;
81 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x;
82 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
83 do_execsql_test like3-3.1 {
84 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC;
85 } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
86 do_execsql_test like3-3.1ck {
87 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC;
88 } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
89 do_execsql_test like3-3.2 {
90 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x ASC;
91 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
92 do_execsql_test like3-3.2ck {
93 SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x ASC;
94 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
95
96 do_execsql_test like3-4.0 {
97 CREATE TABLE t4(x TEXT COLLATE nocase);
98 CREATE INDEX t4x ON t4(x DESC);
99 INSERT INTO t4(x) SELECT x FROM t3;
100 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x;
101 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
102 do_execsql_test like3-4.1 {
103 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x DESC;
104 } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
105 do_execsql_test like3-4.1ck {
106 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x DESC;
107 } {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
108 do_execsql_test like3-4.2 {
109 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC;
110 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
111 do_execsql_test like3-4.2ck {
112 SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC;
113 } {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
114
115 # 2018-09-10 ticket https://sqlite.org/src/tktview/c94369cae9b561b1f996
116 # The like optimization fails for a column with numeric affinity if
117 # the pattern '/%' or begins with the escape character.
118 #
119 do_execsql_test like3-5.100 {
120 CREATE TABLE t5a(x INT UNIQUE COLLATE nocase);
121 INSERT INTO t5a(x) VALUES('/abc'),(123),(-234);
122 SELECT x FROM t5a WHERE x LIKE '/%';
123 } {/abc}
124 do_eqp_test like3-5.101 {
125 SELECT x FROM t5a WHERE x LIKE '/%';
126 } {
127 QUERY PLAN
128 `--SCAN t5a
129 }
130 do_execsql_test like3-5.110 {
131 SELECT x FROM t5a WHERE x LIKE '/a%';
132 } {/abc}
133 ifcapable !icu {
134 do_eqp_test like3-5.111 {
135 SELECT x FROM t5a WHERE x LIKE '/a%';
136 } {
137 QUERY PLAN
138 `--SEARCH t5a USING COVERING INDEX sqlite_autoindex_t5a_1 (x>? AND x<?)
139 }
140 }
141 do_execsql_test like3-5.120 {
142 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
143 } {123}
144 do_eqp_test like3-5.121 {
145 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
146 } {
147 QUERY PLAN
148 `--SCAN t5a
149 }
150 do_execsql_test like3-5.122 {
151 SELECT x FROM t5a WHERE x LIKE '^-2%' ESCAPE '^';
152 } {-234}
153 do_eqp_test like3-5.123 {
154 SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
155 } {
156 QUERY PLAN
157 `--SCAN t5a
158 }
159
160 do_execsql_test like3-5.200 {
161 CREATE TABLE t5b(x INT UNIQUE COLLATE binary);
162 INSERT INTO t5b(x) VALUES('/abc'),(123),(-234);
163 SELECT x FROM t5b WHERE x GLOB '/*';
164 } {/abc}
165 do_eqp_test like3-5.201 {
166 SELECT x FROM t5b WHERE x GLOB '/*';
167 } {
168 QUERY PLAN
169 `--SCAN t5b
170 }
171 do_execsql_test like3-5.210 {
172 SELECT x FROM t5b WHERE x GLOB '/a*';
173 } {/abc}
174 do_eqp_test like3-5.211 {
175 SELECT x FROM t5b WHERE x GLOB '/a*';
176 } {
177 QUERY PLAN
178 `--SEARCH t5b USING COVERING INDEX sqlite_autoindex_t5b_1 (x>? AND x<?)
179 }
180
181 # 2019-05-01
182 # another case of the above reported on the mailing list by Manuel Rigger.
183 #
184 do_execsql_test like3-5.300 {
185 CREATE TABLE t5c (c0 REAL);
186 CREATE INDEX t5c_0 ON t5c(c0 COLLATE NOCASE);
187 INSERT INTO t5c(rowid, c0) VALUES (99,'+/');
188 SELECT * FROM t5c WHERE (c0 LIKE '+/');
189 } {+/}
190
191 # 2019-05-08
192 # Yet another case for the above from Manuel Rigger.
193 #
194 do_execsql_test like3-5.400 {
195 DROP TABLE IF EXISTS t0;
196 CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
197 INSERT INTO t0(c0) VALUES ('./');
198 SELECT * FROM t0 WHERE t0.c0 LIKE './';
199 } {./}
200
201 # 2019-06-14
202 # Ticket https://sqlite.org/src/info/ce8717f0885af975
203 do_execsql_test like3-5.410 {
204 DROP TABLE IF EXISTS t0;
205 CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
206 INSERT INTO t0(c0) VALUES ('.1%');
207 SELECT * FROM t0 WHERE t0.c0 LIKE '.1%';
208 } {.1%}
209
210 # 2019-09-03
211 # Ticket https://sqlite.org/src/info/0f0428096f
212 do_execsql_test like3-5.420 {
213 DROP TABLE IF EXISTS t0;
214 CREATE TABLE t0(c0 UNIQUE);
215 INSERT INTO t0(c0) VALUES(-1);
216 SELECT * FROM t0 WHERE t0.c0 GLOB '-*';
217 } {-1}
218 do_execsql_test like3-5.421 {
219 SELECT t0.c0 GLOB '-*' FROM t0;
220 } {1}
221
222
223
224 # 2019-02-27
225 # Verify that the LIKE optimization works with an ESCAPE clause when
226 # using PRAGMA case_sensitive_like=ON.
227 #
228 ifcapable !icu {
229 do_execsql_test like3-6.100 {
230 DROP TABLE IF EXISTS t1;
231 CREATE TABLE t1(path TEXT COLLATE nocase PRIMARY KEY,a,b,c) WITHOUT ROWID;
232 }
233 do_eqp_test like3-6.110 {
234 SELECT * FROM t1 WHERE path LIKE 'a%';
235 } {
236 QUERY PLAN
237 `--SEARCH t1 USING PRIMARY KEY (path>? AND path<?)
238 }
239 do_eqp_test like3-6.120 {
240 SELECT * FROM t1 WHERE path LIKE 'a%' ESCAPE 'x';
241 } {
242 QUERY PLAN
243 `--SEARCH t1 USING PRIMARY KEY (path>? AND path<?)
244 }
245 do_execsql_test like3-6.200 {
246 DROP TABLE IF EXISTS t2;
247 CREATE TABLE t2(path TEXT,x,y,z);
248 CREATE INDEX t2path ON t2(path COLLATE nocase);
249 CREATE INDEX t2path2 ON t2(path);
250 }
251 do_eqp_test like3-6.210 {
252 SELECT * FROM t2 WHERE path LIKE 'a%';
253 } {
254 QUERY PLAN
255 `--SEARCH t2 USING INDEX t2path (path>? AND path<?)
256 }
257 do_eqp_test like3-6.220 {
258 SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';
259 } {
260 QUERY PLAN
261 `--SEARCH t2 USING INDEX t2path (path>? AND path<?)
262 }
263 db eval {PRAGMA case_sensitive_like=ON}
264 do_eqp_test like3-6.230 {
265 SELECT * FROM t2 WHERE path LIKE 'a%';
266 } {
267 QUERY PLAN
268 `--SEARCH t2 USING INDEX t2path2 (path>? AND path<?)
269 }
270 do_eqp_test like3-6.240 {
271 SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';
272 } {
273 QUERY PLAN
274 `--SEARCH t2 USING INDEX t2path2 (path>? AND path<?)
275 }
276 }
277
278 #-------------------------------------------------------------------------
279
280 ifcapable utf16 {
281 reset_db
282 do_execsql_test like3-7.0 {
283 PRAGMA encoding = 'UTF-16be';
284
285 CREATE TABLE Example(word TEXT NOT NULL);
286 CREATE INDEX Example_word on Example(word);
287
288 INSERT INTO Example VALUES(char(0x307F));
289 }
290
291 do_execsql_test like3-7.1 {
292 SELECT char(0x307F)=='み';
293 } {1}
294
295 do_execsql_test like3-7.1 {
296 SELECT * FROM Example WHERE word GLOB 'み*'
297 } {み}
298
299 do_execsql_test like3-7.2 {
300 SELECT * FROM Example WHERE word >= char(0x307F) AND word < char(0x3080);
301 } {み}
302 }
303
304 #-------------------------------------------------------------------------
305 reset_db
306
307 # See forum thread https://sqlite.org/forum/info/d7b90d92ffbfc61f
308 foreach enc {
309 UTF-8
310 UTF-16le
311 UTF-16be
312 } {
313 ifcapable icu {
314 if {$enc=="UTF-8"} {
315 # The invalid UTF8 used in these tests is incompatible with ICU
316 # https://sqlite.org/forum/forumpost/2ca8a09a7e
317 continue
318 }
319 }
320 foreach {tn expr} {
321 1 "CAST (X'FF' AS TEXT)"
322 2 "CAST (X'FFBF' AS TEXT)"
323 3 "CAST (X'FFBFBF' AS TEXT)"
324 4 "CAST (X'FFBFBFBF' AS TEXT)"
325
326 5 "'abc' || CAST (X'FF' AS TEXT)"
327 6 "'def' || CAST (X'FFBF' AS TEXT)"
328 7 "'ghi' || CAST (X'FFBFBF' AS TEXT)"
329 8 "'jkl' || CAST (X'FFBFBFBF' AS TEXT)"
330 } {
331 reset_db
332 execsql "PRAGMA encoding = '$enc'"
333 set tn utf[string range $enc 4 end].$tn
334 do_execsql_test like3-8.$tn.1 {
335 CREATE TABLE t1(x);
336 }
337
338 do_execsql_test like3-8.$tn.2 {
339 PRAGMA encoding
340 } $enc
341
342 do_execsql_test like3-8.$tn.3 "
343 INSERT INTO t1 VALUES( $expr )
344 "
345
346 do_execsql_test like3-8.$tn.4 {
347 SELECT typeof(x) FROM t1
348 } {text}
349
350 set x [db one {SELECT x || '%' FROM t1}]
351
352 do_execsql_test like3-8.$tn.5 {
353 SELECT rowid FROM t1 WHERE x LIKE $x
354 } 1
355
356 do_execsql_test like3-8.$tn.6 {
357 CREATE INDEX i1 ON t1(x);
358 }
359
360 do_execsql_test like3-8.$tn.7 {
361 SELECT rowid FROM t1 WHERE x LIKE $x
362 } 1
363 }
364 }
365
366 finish_test