3
# The author disclaims copyright to this source code. In place of
4
# a legal notice, here is a blessing:
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.
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
16
# $Id: like.test,v 1.5 2006/06/14 08:48:26 danielk1977 Exp $
18
set testdir [file dirname $argv0]
19
source $testdir/tester.tcl
21
# Create some sample data to work with.
25
CREATE TABLE t1(x TEXT);
43
db eval {INSERT INTO t1 VALUES(:str)}
46
SELECT count(*) FROM t1;
50
# Test that both case sensitive and insensitive version of LIKE work.
54
SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
59
SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
64
SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
69
SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
74
PRAGMA case_sensitive_like=on;
75
SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
80
SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
85
SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
90
SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
95
PRAGMA case_sensitive_like=off;
96
SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
100
# Tests of the REGEXP operator
103
proc test_regexp {a b} {
104
return [regexp $a $b]
106
db function regexp test_regexp
108
SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;
110
} {{ABC abc xyz} abc abcd}
113
SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1;
117
# Tests of the MATCH operator
120
proc test_match {a b} {
121
return [string match $a $b]
123
db function match test_match
125
SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1;
127
} {{ABC abc xyz} abc abcd}
130
SELECT x FROM t1 WHERE x MATCH 'abc*' ORDER BY 1;
134
# For the remaining tests, we need to have the like optimizations
137
ifcapable !like_opt {
142
# This procedure executes the SQL. Then it appends to the result the
143
# "sort" or "nosort" keyword (as in the cksort procedure above) then
144
# it appends the ::sqlite_query_plan variable.
146
proc queryplan {sql} {
147
set ::sqlite_sort_count 0
148
set data [execsql $sql]
149
if {$::sqlite_sort_count} {set x sort} {set x nosort}
151
return [concat $data $::sqlite_query_plan]
154
# Perform tests on the like optimization.
156
# With no index on t1.x and with case sensitivity turned off, no optimization
160
set sqlite_like_count 0
162
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
164
} {ABC {ABC abc xyz} abc abcd sort t1 {}}
166
set sqlite_like_count
169
# With an index on t1.x and case sensitivity on, optimize completely.
172
set sqlite_like_count 0
174
PRAGMA case_sensitive_like=on;
175
CREATE INDEX i1 ON t1(x);
178
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
180
} {abc abcd nosort {} i1}
182
set sqlite_like_count
185
# Partial optimization when the pattern does not end in '%'
188
set sqlite_like_count 0
190
SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;
194
set sqlite_like_count
197
set sqlite_like_count 0
199
SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1;
201
} {abcd abd nosort {} i1}
203
set sqlite_like_count
206
set sqlite_like_count 0
208
SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1;
210
} {abc abcd nosort {} i1}
212
set sqlite_like_count
215
# No optimization when the pattern begins with a wildcard.
216
# Note that the index is still used but only for sorting.
219
set sqlite_like_count 0
221
SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1;
223
} {abcd bcd nosort {} i1}
225
set sqlite_like_count
228
# No optimization for case insensitive LIKE
231
set sqlite_like_count 0
233
PRAGMA case_sensitive_like=off;
234
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
236
} {ABC {ABC abc xyz} abc abcd nosort {} i1}
238
set sqlite_like_count
241
# No optimization without an index.
244
set sqlite_like_count 0
246
PRAGMA case_sensitive_like=on;
248
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
250
} {abc abcd sort t1 {}}
252
set sqlite_like_count
255
# No GLOB optimization without an index.
258
set sqlite_like_count 0
260
SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
262
} {abc abcd sort t1 {}}
264
set sqlite_like_count
267
# GLOB is optimized regardless of the case_sensitive_like setting.
270
set sqlite_like_count 0
272
CREATE INDEX i1 ON t1(x);
273
SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
275
} {abc abcd nosort {} i1}
277
set sqlite_like_count
280
set sqlite_like_count 0
282
PRAGMA case_sensitive_like=on;
283
SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
285
} {abc abcd nosort {} i1}
287
set sqlite_like_count
290
set sqlite_like_count 0
292
PRAGMA case_sensitive_like=off;
293
SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
295
} {abd acd nosort {} i1}
297
set sqlite_like_count
300
# No optimization if the LHS of the LIKE is not a column name or
301
# if the RHS is not a string.
304
execsql {PRAGMA case_sensitive_like=on}
305
set sqlite_like_count 0
307
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1
309
} {abc abcd nosort {} i1}
311
set sqlite_like_count
314
set sqlite_like_count 0
316
SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1
318
} {abc abcd nosort {} i1}
320
set sqlite_like_count
323
set sqlite_like_count 0
325
SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1
327
} {abc abcd nosort {} i1}
329
set sqlite_like_count
332
# Collating sequences on the index disable the LIKE optimization.
333
# Or if the NOCASE collating sequence is used, the LIKE optimization
334
# is enabled when case_sensitive_like is OFF.
337
execsql {PRAGMA case_sensitive_like=off}
338
set sqlite_like_count 0
340
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1
342
} {ABC {ABC abc xyz} abc abcd nosort {} i1}
344
set sqlite_like_count
348
CREATE TABLE t2(x COLLATE NOCASE);
349
INSERT INTO t2 SELECT * FROM t1;
350
CREATE INDEX i2 ON t2(x COLLATE NOCASE);
352
set sqlite_like_count 0
354
SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
356
} {abc ABC {ABC abc xyz} abcd nosort {} i2}
358
set sqlite_like_count
362
PRAGMA case_sensitive_like=on;
364
set sqlite_like_count 0
366
SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
368
} {abc abcd nosort {} i2}
370
set sqlite_like_count
374
PRAGMA case_sensitive_like=off;
376
set sqlite_like_count 0
378
SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1
380
} {abc abcd nosort {} i2}
382
set sqlite_like_count
added
removed
test/like.test
