1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/like.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,554 @@
1.4 +# 2005 August 13
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the LIKE and GLOB operators and
1.16 +# in particular the optimizations that occur to help those operators
1.17 +# run faster.
1.18 +#
1.19 +# $Id: like.test,v 1.10 2008/09/09 12:31:34 drh Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +# Create some sample data to work with.
1.25 +#
1.26 +do_test like-1.0 {
1.27 + execsql {
1.28 + CREATE TABLE t1(x TEXT);
1.29 + }
1.30 + foreach str {
1.31 + a
1.32 + ab
1.33 + abc
1.34 + abcd
1.35 +
1.36 + acd
1.37 + abd
1.38 + bc
1.39 + bcd
1.40 +
1.41 + xyz
1.42 + ABC
1.43 + CDE
1.44 + {ABC abc xyz}
1.45 + } {
1.46 + db eval {INSERT INTO t1 VALUES(:str)}
1.47 + }
1.48 + execsql {
1.49 + SELECT count(*) FROM t1;
1.50 + }
1.51 +} {12}
1.52 +
1.53 +# Test that both case sensitive and insensitive version of LIKE work.
1.54 +#
1.55 +do_test like-1.1 {
1.56 + execsql {
1.57 + SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
1.58 + }
1.59 +} {ABC abc}
1.60 +do_test like-1.2 {
1.61 + execsql {
1.62 + SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
1.63 + }
1.64 +} {abc}
1.65 +do_test like-1.3 {
1.66 + execsql {
1.67 + SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
1.68 + }
1.69 +} {ABC abc}
1.70 +do_test like-1.4 {
1.71 + execsql {
1.72 + SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
1.73 + }
1.74 +} {ABC abc}
1.75 +do_test like-1.5 {
1.76 + execsql {
1.77 + PRAGMA case_sensitive_like=on;
1.78 + SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
1.79 + }
1.80 +} {abc}
1.81 +do_test like-1.6 {
1.82 + execsql {
1.83 + SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1;
1.84 + }
1.85 +} {abc}
1.86 +do_test like-1.7 {
1.87 + execsql {
1.88 + SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1;
1.89 + }
1.90 +} {ABC}
1.91 +do_test like-1.8 {
1.92 + execsql {
1.93 + SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1;
1.94 + }
1.95 +} {}
1.96 +do_test like-1.9 {
1.97 + execsql {
1.98 + PRAGMA case_sensitive_like=off;
1.99 + SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1;
1.100 + }
1.101 +} {ABC abc}
1.102 +
1.103 +# Tests of the REGEXP operator
1.104 +#
1.105 +do_test like-2.1 {
1.106 + proc test_regexp {a b} {
1.107 + return [regexp $a $b]
1.108 + }
1.109 + db function regexp test_regexp
1.110 + execsql {
1.111 + SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;
1.112 + }
1.113 +} {{ABC abc xyz} abc abcd}
1.114 +do_test like-2.2 {
1.115 + execsql {
1.116 + SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1;
1.117 + }
1.118 +} {abc abcd}
1.119 +
1.120 +# Tests of the MATCH operator
1.121 +#
1.122 +do_test like-2.3 {
1.123 + proc test_match {a b} {
1.124 + return [string match $a $b]
1.125 + }
1.126 + db function match -argcount 2 test_match
1.127 + execsql {
1.128 + SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1;
1.129 + }
1.130 +} {{ABC abc xyz} abc abcd}
1.131 +do_test like-2.4 {
1.132 + execsql {
1.133 + SELECT x FROM t1 WHERE x MATCH 'abc*' ORDER BY 1;
1.134 + }
1.135 +} {abc abcd}
1.136 +
1.137 +# For the remaining tests, we need to have the like optimizations
1.138 +# enabled.
1.139 +#
1.140 +ifcapable !like_opt {
1.141 + finish_test
1.142 + return
1.143 +}
1.144 +
1.145 +# This procedure executes the SQL. Then it appends to the result the
1.146 +# "sort" or "nosort" keyword (as in the cksort procedure above) then
1.147 +# it appends the ::sqlite_query_plan variable.
1.148 +#
1.149 +proc queryplan {sql} {
1.150 + set ::sqlite_sort_count 0
1.151 + set data [execsql $sql]
1.152 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.153 + lappend data $x
1.154 + return [concat $data $::sqlite_query_plan]
1.155 +}
1.156 +
1.157 +# Perform tests on the like optimization.
1.158 +#
1.159 +# With no index on t1.x and with case sensitivity turned off, no optimization
1.160 +# is performed.
1.161 +#
1.162 +do_test like-3.1 {
1.163 + set sqlite_like_count 0
1.164 + queryplan {
1.165 + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
1.166 + }
1.167 +} {ABC {ABC abc xyz} abc abcd sort t1 {}}
1.168 +do_test like-3.2 {
1.169 + set sqlite_like_count
1.170 +} {12}
1.171 +
1.172 +# With an index on t1.x and case sensitivity on, optimize completely.
1.173 +#
1.174 +do_test like-3.3 {
1.175 + set sqlite_like_count 0
1.176 + execsql {
1.177 + PRAGMA case_sensitive_like=on;
1.178 + CREATE INDEX i1 ON t1(x);
1.179 + }
1.180 + queryplan {
1.181 + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
1.182 + }
1.183 +} {abc abcd nosort {} i1}
1.184 +do_test like-3.4 {
1.185 + set sqlite_like_count
1.186 +} 0
1.187 +
1.188 +# Partial optimization when the pattern does not end in '%'
1.189 +#
1.190 +do_test like-3.5 {
1.191 + set sqlite_like_count 0
1.192 + queryplan {
1.193 + SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;
1.194 + }
1.195 +} {abc nosort {} i1}
1.196 +do_test like-3.6 {
1.197 + set sqlite_like_count
1.198 +} 6
1.199 +do_test like-3.7 {
1.200 + set sqlite_like_count 0
1.201 + queryplan {
1.202 + SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1;
1.203 + }
1.204 +} {abcd abd nosort {} i1}
1.205 +do_test like-3.8 {
1.206 + set sqlite_like_count
1.207 +} 4
1.208 +do_test like-3.9 {
1.209 + set sqlite_like_count 0
1.210 + queryplan {
1.211 + SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1;
1.212 + }
1.213 +} {abc abcd nosort {} i1}
1.214 +do_test like-3.10 {
1.215 + set sqlite_like_count
1.216 +} 6
1.217 +
1.218 +# No optimization when the pattern begins with a wildcard.
1.219 +# Note that the index is still used but only for sorting.
1.220 +#
1.221 +do_test like-3.11 {
1.222 + set sqlite_like_count 0
1.223 + queryplan {
1.224 + SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1;
1.225 + }
1.226 +} {abcd bcd nosort {} i1}
1.227 +do_test like-3.12 {
1.228 + set sqlite_like_count
1.229 +} 12
1.230 +
1.231 +# No optimization for case insensitive LIKE
1.232 +#
1.233 +do_test like-3.13 {
1.234 + set sqlite_like_count 0
1.235 + queryplan {
1.236 + PRAGMA case_sensitive_like=off;
1.237 + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
1.238 + }
1.239 +} {ABC {ABC abc xyz} abc abcd nosort {} i1}
1.240 +do_test like-3.14 {
1.241 + set sqlite_like_count
1.242 +} 12
1.243 +
1.244 +# No optimization without an index.
1.245 +#
1.246 +do_test like-3.15 {
1.247 + set sqlite_like_count 0
1.248 + queryplan {
1.249 + PRAGMA case_sensitive_like=on;
1.250 + DROP INDEX i1;
1.251 + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
1.252 + }
1.253 +} {abc abcd sort t1 {}}
1.254 +do_test like-3.16 {
1.255 + set sqlite_like_count
1.256 +} 12
1.257 +
1.258 +# No GLOB optimization without an index.
1.259 +#
1.260 +do_test like-3.17 {
1.261 + set sqlite_like_count 0
1.262 + queryplan {
1.263 + SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
1.264 + }
1.265 +} {abc abcd sort t1 {}}
1.266 +do_test like-3.18 {
1.267 + set sqlite_like_count
1.268 +} 12
1.269 +
1.270 +# GLOB is optimized regardless of the case_sensitive_like setting.
1.271 +#
1.272 +do_test like-3.19 {
1.273 + set sqlite_like_count 0
1.274 + queryplan {
1.275 + CREATE INDEX i1 ON t1(x);
1.276 + SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
1.277 + }
1.278 +} {abc abcd nosort {} i1}
1.279 +do_test like-3.20 {
1.280 + set sqlite_like_count
1.281 +} 0
1.282 +do_test like-3.21 {
1.283 + set sqlite_like_count 0
1.284 + queryplan {
1.285 + PRAGMA case_sensitive_like=on;
1.286 + SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
1.287 + }
1.288 +} {abc abcd nosort {} i1}
1.289 +do_test like-3.22 {
1.290 + set sqlite_like_count
1.291 +} 0
1.292 +do_test like-3.23 {
1.293 + set sqlite_like_count 0
1.294 + queryplan {
1.295 + PRAGMA case_sensitive_like=off;
1.296 + SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
1.297 + }
1.298 +} {abd acd nosort {} i1}
1.299 +do_test like-3.24 {
1.300 + set sqlite_like_count
1.301 +} 6
1.302 +
1.303 +# No optimization if the LHS of the LIKE is not a column name or
1.304 +# if the RHS is not a string.
1.305 +#
1.306 +do_test like-4.1 {
1.307 + execsql {PRAGMA case_sensitive_like=on}
1.308 + set sqlite_like_count 0
1.309 + queryplan {
1.310 + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1
1.311 + }
1.312 +} {abc abcd nosort {} i1}
1.313 +do_test like-4.2 {
1.314 + set sqlite_like_count
1.315 +} 0
1.316 +do_test like-4.3 {
1.317 + set sqlite_like_count 0
1.318 + queryplan {
1.319 + SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1
1.320 + }
1.321 +} {abc abcd nosort {} i1}
1.322 +do_test like-4.4 {
1.323 + set sqlite_like_count
1.324 +} 12
1.325 +do_test like-4.5 {
1.326 + set sqlite_like_count 0
1.327 + queryplan {
1.328 + SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1
1.329 + }
1.330 +} {abc abcd nosort {} i1}
1.331 +do_test like-4.6 {
1.332 + set sqlite_like_count
1.333 +} 12
1.334 +
1.335 +# Collating sequences on the index disable the LIKE optimization.
1.336 +# Or if the NOCASE collating sequence is used, the LIKE optimization
1.337 +# is enabled when case_sensitive_like is OFF.
1.338 +#
1.339 +do_test like-5.1 {
1.340 + execsql {PRAGMA case_sensitive_like=off}
1.341 + set sqlite_like_count 0
1.342 + queryplan {
1.343 + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1
1.344 + }
1.345 +} {ABC {ABC abc xyz} abc abcd nosort {} i1}
1.346 +do_test like-5.2 {
1.347 + set sqlite_like_count
1.348 +} 12
1.349 +do_test like-5.3 {
1.350 + execsql {
1.351 + CREATE TABLE t2(x COLLATE NOCASE);
1.352 + INSERT INTO t2 SELECT * FROM t1;
1.353 + CREATE INDEX i2 ON t2(x COLLATE NOCASE);
1.354 + }
1.355 + set sqlite_like_count 0
1.356 + queryplan {
1.357 + SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
1.358 + }
1.359 +} {abc ABC {ABC abc xyz} abcd nosort {} i2}
1.360 +do_test like-5.4 {
1.361 + set sqlite_like_count
1.362 +} 0
1.363 +do_test like-5.5 {
1.364 + execsql {
1.365 + PRAGMA case_sensitive_like=on;
1.366 + }
1.367 + set sqlite_like_count 0
1.368 + queryplan {
1.369 + SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
1.370 + }
1.371 +} {abc abcd nosort {} i2}
1.372 +do_test like-5.6 {
1.373 + set sqlite_like_count
1.374 +} 12
1.375 +do_test like-5.7 {
1.376 + execsql {
1.377 + PRAGMA case_sensitive_like=off;
1.378 + }
1.379 + set sqlite_like_count 0
1.380 + queryplan {
1.381 + SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1
1.382 + }
1.383 +} {abc abcd nosort {} i2}
1.384 +do_test like-5.8 {
1.385 + set sqlite_like_count
1.386 +} 12
1.387 +do_test like-5.11 {
1.388 + execsql {PRAGMA case_sensitive_like=off}
1.389 + set sqlite_like_count 0
1.390 + queryplan {
1.391 + SELECT x FROM t1 WHERE x LIKE 'ABC%' ORDER BY 1
1.392 + }
1.393 +} {ABC {ABC abc xyz} abc abcd nosort {} i1}
1.394 +do_test like-5.12 {
1.395 + set sqlite_like_count
1.396 +} 12
1.397 +do_test like-5.13 {
1.398 + set sqlite_like_count 0
1.399 + queryplan {
1.400 + SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1
1.401 + }
1.402 +} {abc ABC {ABC abc xyz} abcd nosort {} i2}
1.403 +do_test like-5.14 {
1.404 + set sqlite_like_count
1.405 +} 0
1.406 +do_test like-5.15 {
1.407 + execsql {
1.408 + PRAGMA case_sensitive_like=on;
1.409 + }
1.410 + set sqlite_like_count 0
1.411 + queryplan {
1.412 + SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1
1.413 + }
1.414 +} {ABC {ABC abc xyz} nosort {} i2}
1.415 +do_test like-5.16 {
1.416 + set sqlite_like_count
1.417 +} 12
1.418 +do_test like-5.17 {
1.419 + execsql {
1.420 + PRAGMA case_sensitive_like=off;
1.421 + }
1.422 + set sqlite_like_count 0
1.423 + queryplan {
1.424 + SELECT x FROM t2 WHERE x GLOB 'ABC*' ORDER BY 1
1.425 + }
1.426 +} {ABC {ABC abc xyz} nosort {} i2}
1.427 +do_test like-5.18 {
1.428 + set sqlite_like_count
1.429 +} 12
1.430 +
1.431 +# Boundary case. The prefix for a LIKE comparison is rounded up
1.432 +# when constructing the comparison. Example: "ab" becomes "ac".
1.433 +# In other words, the last character is increased by one.
1.434 +#
1.435 +# Make sure this happens correctly when the last character is a
1.436 +# "z" and we are doing case-insensitive comparisons.
1.437 +#
1.438 +# Ticket #2959
1.439 +#
1.440 +do_test like-5.21 {
1.441 + execsql {
1.442 + PRAGMA case_sensitive_like=off;
1.443 + INSERT INTO t2 VALUES('ZZ-upper-upper');
1.444 + INSERT INTO t2 VALUES('zZ-lower-upper');
1.445 + INSERT INTO t2 VALUES('Zz-upper-lower');
1.446 + INSERT INTO t2 VALUES('zz-lower-lower');
1.447 + }
1.448 + queryplan {
1.449 + SELECT x FROM t2 WHERE x LIKE 'zz%';
1.450 + }
1.451 +} {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
1.452 +do_test like-5.22 {
1.453 + queryplan {
1.454 + SELECT x FROM t2 WHERE x LIKE 'zZ%';
1.455 + }
1.456 +} {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
1.457 +do_test like-5.23 {
1.458 + queryplan {
1.459 + SELECT x FROM t2 WHERE x LIKE 'Zz%';
1.460 + }
1.461 +} {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
1.462 +do_test like-5.24 {
1.463 + queryplan {
1.464 + SELECT x FROM t2 WHERE x LIKE 'ZZ%';
1.465 + }
1.466 +} {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
1.467 +do_test like-5.25 {
1.468 + queryplan {
1.469 + PRAGMA case_sensitive_like=on;
1.470 + CREATE TABLE t3(x);
1.471 + CREATE INDEX i3 ON t3(x);
1.472 + INSERT INTO t3 VALUES('ZZ-upper-upper');
1.473 + INSERT INTO t3 VALUES('zZ-lower-upper');
1.474 + INSERT INTO t3 VALUES('Zz-upper-lower');
1.475 + INSERT INTO t3 VALUES('zz-lower-lower');
1.476 + SELECT x FROM t3 WHERE x LIKE 'zz%';
1.477 + }
1.478 +} {zz-lower-lower nosort {} i3}
1.479 +do_test like-5.26 {
1.480 + queryplan {
1.481 + SELECT x FROM t3 WHERE x LIKE 'zZ%';
1.482 + }
1.483 +} {zZ-lower-upper nosort {} i3}
1.484 +do_test like-5.27 {
1.485 + queryplan {
1.486 + SELECT x FROM t3 WHERE x LIKE 'Zz%';
1.487 + }
1.488 +} {Zz-upper-lower nosort {} i3}
1.489 +do_test like-5.28 {
1.490 + queryplan {
1.491 + SELECT x FROM t3 WHERE x LIKE 'ZZ%';
1.492 + }
1.493 +} {ZZ-upper-upper nosort {} i3}
1.494 +
1.495 +
1.496 +# ticket #2407
1.497 +#
1.498 +# Make sure the LIKE prefix optimization does not strip off leading
1.499 +# characters of the like pattern that happen to be quote characters.
1.500 +#
1.501 +do_test like-6.1 {
1.502 + foreach x { 'abc 'bcd 'def 'ax } {
1.503 + set x2 '[string map {' ''} $x]'
1.504 + db eval "INSERT INTO t2 VALUES($x2)"
1.505 + }
1.506 + execsql {
1.507 + SELECT * FROM t2 WHERE x LIKE '''a%'
1.508 + }
1.509 +} {'abc 'ax}
1.510 +
1.511 +do_test like-7.1 {
1.512 + execsql {
1.513 + SELECT * FROM t1 WHERE rowid GLOB '1*';
1.514 + }
1.515 +} {a}
1.516 +
1.517 +# ticket #3345.
1.518 +#
1.519 +# Overloading the LIKE function with -1 for the number of arguments
1.520 +# will overload both the 2-argument and the 3-argument LIKE.
1.521 +#
1.522 +do_test like-8.1 {
1.523 + db eval {
1.524 + CREATE TABLE t8(x);
1.525 + INSERT INTO t8 VALUES('abcdef');
1.526 + INSERT INTO t8 VALUES('ghijkl');
1.527 + INSERT INTO t8 VALUES('mnopqr');
1.528 + SELECT 1, x FROM t8 WHERE x LIKE '%h%';
1.529 + SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
1.530 + }
1.531 +} {1 ghijkl 2 ghijkl}
1.532 +do_test like-8.2 {
1.533 + proc newlike {args} {return 1} ;# Alternative LIKE is always return TRUE
1.534 + db function like newlike ;# Uses -1 for nArg in sqlite3_create_function
1.535 + db cache flush
1.536 + db eval {
1.537 + SELECT 1, x FROM t8 WHERE x LIKE '%h%';
1.538 + SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
1.539 + }
1.540 +} {1 ghijkl 2 ghijkl}
1.541 +do_test like-8.3 {
1.542 + db function like -argcount 2 newlike
1.543 + db eval {
1.544 + SELECT 1, x FROM t8 WHERE x LIKE '%h%';
1.545 + SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
1.546 + }
1.547 +} {1 abcdef 1 ghijkl 1 mnopqr 2 ghijkl}
1.548 +do_test like-8.4 {
1.549 + db function like -argcount 3 newlike
1.550 + db eval {
1.551 + SELECT 1, x FROM t8 WHERE x LIKE '%h%';
1.552 + SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x';
1.553 + }
1.554 +} {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr}
1.555 +
1.556 +
1.557 +finish_test