diff -r 000000000000 -r bde4ae8d615e os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/like.test --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/like.test Fri Jun 15 03:10:57 2012 +0200 @@ -0,0 +1,554 @@ +# 2005 August 13 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the LIKE and GLOB operators and +# in particular the optimizations that occur to help those operators +# run faster. +# +# $Id: like.test,v 1.10 2008/09/09 12:31:34 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# Create some sample data to work with. +# +do_test like-1.0 { + execsql { + CREATE TABLE t1(x TEXT); + } + foreach str { + a + ab + abc + abcd + + acd + abd + bc + bcd + + xyz + ABC + CDE + {ABC abc xyz} + } { + db eval {INSERT INTO t1 VALUES(:str)} + } + execsql { + SELECT count(*) FROM t1; + } +} {12} + +# Test that both case sensitive and insensitive version of LIKE work. +# +do_test like-1.1 { + execsql { + SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; + } +} {ABC abc} +do_test like-1.2 { + execsql { + SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1; + } +} {abc} +do_test like-1.3 { + execsql { + SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1; + } +} {ABC abc} +do_test like-1.4 { + execsql { + SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1; + } +} {ABC abc} +do_test like-1.5 { + execsql { + PRAGMA case_sensitive_like=on; + SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; + } +} {abc} +do_test like-1.6 { + execsql { + SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1; + } +} {abc} +do_test like-1.7 { + execsql { + SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1; + } +} {ABC} +do_test like-1.8 { + execsql { + SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1; + } +} {} +do_test like-1.9 { + execsql { + PRAGMA case_sensitive_like=off; + SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; + } +} {ABC abc} + +# Tests of the REGEXP operator +# +do_test like-2.1 { + proc test_regexp {a b} { + return [regexp $a $b] + } + db function regexp test_regexp + execsql { + SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1; + } +} {{ABC abc xyz} abc abcd} +do_test like-2.2 { + execsql { + SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1; + } +} {abc abcd} + +# Tests of the MATCH operator +# +do_test like-2.3 { + proc test_match {a b} { + return [string match $a $b] + } + db function match -argcount 2 test_match + execsql { + SELECT x FROM t1 WHERE x MATCH '*abc*' ORDER BY 1; + } +} {{ABC abc xyz} abc abcd} +do_test like-2.4 { + execsql { + SELECT x FROM t1 WHERE x MATCH 'abc*' ORDER BY 1; + } +} {abc abcd} + +# For the remaining tests, we need to have the like optimizations +# enabled. +# +ifcapable !like_opt { + finish_test + return +} + +# This procedure executes the SQL. Then it appends to the result the +# "sort" or "nosort" keyword (as in the cksort procedure above) then +# it appends the ::sqlite_query_plan variable. +# +proc queryplan {sql} { + set ::sqlite_sort_count 0 + set data [execsql $sql] + if {$::sqlite_sort_count} {set x sort} {set x nosort} + lappend data $x + return [concat $data $::sqlite_query_plan] +} + +# Perform tests on the like optimization. +# +# With no index on t1.x and with case sensitivity turned off, no optimization +# is performed. +# +do_test like-3.1 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; + } +} {ABC {ABC abc xyz} abc abcd sort t1 {}} +do_test like-3.2 { + set sqlite_like_count +} {12} + +# With an index on t1.x and case sensitivity on, optimize completely. +# +do_test like-3.3 { + set sqlite_like_count 0 + execsql { + PRAGMA case_sensitive_like=on; + CREATE INDEX i1 ON t1(x); + } + queryplan { + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; + } +} {abc abcd nosort {} i1} +do_test like-3.4 { + set sqlite_like_count +} 0 + +# Partial optimization when the pattern does not end in '%' +# +do_test like-3.5 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1; + } +} {abc nosort {} i1} +do_test like-3.6 { + set sqlite_like_count +} 6 +do_test like-3.7 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1; + } +} {abcd abd nosort {} i1} +do_test like-3.8 { + set sqlite_like_count +} 4 +do_test like-3.9 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1; + } +} {abc abcd nosort {} i1} +do_test like-3.10 { + set sqlite_like_count +} 6 + +# No optimization when the pattern begins with a wildcard. +# Note that the index is still used but only for sorting. +# +do_test like-3.11 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1; + } +} {abcd bcd nosort {} i1} +do_test like-3.12 { + set sqlite_like_count +} 12 + +# No optimization for case insensitive LIKE +# +do_test like-3.13 { + set sqlite_like_count 0 + queryplan { + PRAGMA case_sensitive_like=off; + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; + } +} {ABC {ABC abc xyz} abc abcd nosort {} i1} +do_test like-3.14 { + set sqlite_like_count +} 12 + +# No optimization without an index. +# +do_test like-3.15 { + set sqlite_like_count 0 + queryplan { + PRAGMA case_sensitive_like=on; + DROP INDEX i1; + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; + } +} {abc abcd sort t1 {}} +do_test like-3.16 { + set sqlite_like_count +} 12 + +# No GLOB optimization without an index. +# +do_test like-3.17 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; + } +} {abc abcd sort t1 {}} +do_test like-3.18 { + set sqlite_like_count +} 12 + +# GLOB is optimized regardless of the case_sensitive_like setting. +# +do_test like-3.19 { + set sqlite_like_count 0 + queryplan { + CREATE INDEX i1 ON t1(x); + SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; + } +} {abc abcd nosort {} i1} +do_test like-3.20 { + set sqlite_like_count +} 0 +do_test like-3.21 { + set sqlite_like_count 0 + queryplan { + PRAGMA case_sensitive_like=on; + SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; + } +} {abc abcd nosort {} i1} +do_test like-3.22 { + set sqlite_like_count +} 0 +do_test like-3.23 { + set sqlite_like_count 0 + queryplan { + PRAGMA case_sensitive_like=off; + SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1; + } +} {abd acd nosort {} i1} +do_test like-3.24 { + set sqlite_like_count +} 6 + +# No optimization if the LHS of the LIKE is not a column name or +# if the RHS is not a string. +# +do_test like-4.1 { + execsql {PRAGMA case_sensitive_like=on} + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1 + } +} {abc abcd nosort {} i1} +do_test like-4.2 { + set sqlite_like_count +} 0 +do_test like-4.3 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE +x LIKE 'abc%' ORDER BY 1 + } +} {abc abcd nosort {} i1} +do_test like-4.4 { + set sqlite_like_count +} 12 +do_test like-4.5 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE ('ab' || 'c%') ORDER BY 1 + } +} {abc abcd nosort {} i1} +do_test like-4.6 { + set sqlite_like_count +} 12 + +# Collating sequences on the index disable the LIKE optimization. +# Or if the NOCASE collating sequence is used, the LIKE optimization +# is enabled when case_sensitive_like is OFF. +# +do_test like-5.1 { + execsql {PRAGMA case_sensitive_like=off} + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1 + } +} {ABC {ABC abc xyz} abc abcd nosort {} i1} +do_test like-5.2 { + set sqlite_like_count +} 12 +do_test like-5.3 { + execsql { + CREATE TABLE t2(x COLLATE NOCASE); + INSERT INTO t2 SELECT * FROM t1; + CREATE INDEX i2 ON t2(x COLLATE NOCASE); + } + set sqlite_like_count 0 + queryplan { + SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1 + } +} {abc ABC {ABC abc xyz} abcd nosort {} i2} +do_test like-5.4 { + set sqlite_like_count +} 0 +do_test like-5.5 { + execsql { + PRAGMA case_sensitive_like=on; + } + set sqlite_like_count 0 + queryplan { + SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1 + } +} {abc abcd nosort {} i2} +do_test like-5.6 { + set sqlite_like_count +} 12 +do_test like-5.7 { + execsql { + PRAGMA case_sensitive_like=off; + } + set sqlite_like_count 0 + queryplan { + SELECT x FROM t2 WHERE x GLOB 'abc*' ORDER BY 1 + } +} {abc abcd nosort {} i2} +do_test like-5.8 { + set sqlite_like_count +} 12 +do_test like-5.11 { + execsql {PRAGMA case_sensitive_like=off} + set sqlite_like_count 0 + queryplan { + SELECT x FROM t1 WHERE x LIKE 'ABC%' ORDER BY 1 + } +} {ABC {ABC abc xyz} abc abcd nosort {} i1} +do_test like-5.12 { + set sqlite_like_count +} 12 +do_test like-5.13 { + set sqlite_like_count 0 + queryplan { + SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1 + } +} {abc ABC {ABC abc xyz} abcd nosort {} i2} +do_test like-5.14 { + set sqlite_like_count +} 0 +do_test like-5.15 { + execsql { + PRAGMA case_sensitive_like=on; + } + set sqlite_like_count 0 + queryplan { + SELECT x FROM t2 WHERE x LIKE 'ABC%' ORDER BY 1 + } +} {ABC {ABC abc xyz} nosort {} i2} +do_test like-5.16 { + set sqlite_like_count +} 12 +do_test like-5.17 { + execsql { + PRAGMA case_sensitive_like=off; + } + set sqlite_like_count 0 + queryplan { + SELECT x FROM t2 WHERE x GLOB 'ABC*' ORDER BY 1 + } +} {ABC {ABC abc xyz} nosort {} i2} +do_test like-5.18 { + set sqlite_like_count +} 12 + +# Boundary case. The prefix for a LIKE comparison is rounded up +# when constructing the comparison. Example: "ab" becomes "ac". +# In other words, the last character is increased by one. +# +# Make sure this happens correctly when the last character is a +# "z" and we are doing case-insensitive comparisons. +# +# Ticket #2959 +# +do_test like-5.21 { + execsql { + PRAGMA case_sensitive_like=off; + INSERT INTO t2 VALUES('ZZ-upper-upper'); + INSERT INTO t2 VALUES('zZ-lower-upper'); + INSERT INTO t2 VALUES('Zz-upper-lower'); + INSERT INTO t2 VALUES('zz-lower-lower'); + } + queryplan { + SELECT x FROM t2 WHERE x LIKE 'zz%'; + } +} {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} +do_test like-5.22 { + queryplan { + SELECT x FROM t2 WHERE x LIKE 'zZ%'; + } +} {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} +do_test like-5.23 { + queryplan { + SELECT x FROM t2 WHERE x LIKE 'Zz%'; + } +} {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} +do_test like-5.24 { + queryplan { + SELECT x FROM t2 WHERE x LIKE 'ZZ%'; + } +} {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} +do_test like-5.25 { + queryplan { + PRAGMA case_sensitive_like=on; + CREATE TABLE t3(x); + CREATE INDEX i3 ON t3(x); + INSERT INTO t3 VALUES('ZZ-upper-upper'); + INSERT INTO t3 VALUES('zZ-lower-upper'); + INSERT INTO t3 VALUES('Zz-upper-lower'); + INSERT INTO t3 VALUES('zz-lower-lower'); + SELECT x FROM t3 WHERE x LIKE 'zz%'; + } +} {zz-lower-lower nosort {} i3} +do_test like-5.26 { + queryplan { + SELECT x FROM t3 WHERE x LIKE 'zZ%'; + } +} {zZ-lower-upper nosort {} i3} +do_test like-5.27 { + queryplan { + SELECT x FROM t3 WHERE x LIKE 'Zz%'; + } +} {Zz-upper-lower nosort {} i3} +do_test like-5.28 { + queryplan { + SELECT x FROM t3 WHERE x LIKE 'ZZ%'; + } +} {ZZ-upper-upper nosort {} i3} + + +# ticket #2407 +# +# Make sure the LIKE prefix optimization does not strip off leading +# characters of the like pattern that happen to be quote characters. +# +do_test like-6.1 { + foreach x { 'abc 'bcd 'def 'ax } { + set x2 '[string map {' ''} $x]' + db eval "INSERT INTO t2 VALUES($x2)" + } + execsql { + SELECT * FROM t2 WHERE x LIKE '''a%' + } +} {'abc 'ax} + +do_test like-7.1 { + execsql { + SELECT * FROM t1 WHERE rowid GLOB '1*'; + } +} {a} + +# ticket #3345. +# +# Overloading the LIKE function with -1 for the number of arguments +# will overload both the 2-argument and the 3-argument LIKE. +# +do_test like-8.1 { + db eval { + CREATE TABLE t8(x); + INSERT INTO t8 VALUES('abcdef'); + INSERT INTO t8 VALUES('ghijkl'); + INSERT INTO t8 VALUES('mnopqr'); + SELECT 1, x FROM t8 WHERE x LIKE '%h%'; + SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; + } +} {1 ghijkl 2 ghijkl} +do_test like-8.2 { + proc newlike {args} {return 1} ;# Alternative LIKE is always return TRUE + db function like newlike ;# Uses -1 for nArg in sqlite3_create_function + db cache flush + db eval { + SELECT 1, x FROM t8 WHERE x LIKE '%h%'; + SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; + } +} {1 ghijkl 2 ghijkl} +do_test like-8.3 { + db function like -argcount 2 newlike + db eval { + SELECT 1, x FROM t8 WHERE x LIKE '%h%'; + SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; + } +} {1 abcdef 1 ghijkl 1 mnopqr 2 ghijkl} +do_test like-8.4 { + db function like -argcount 3 newlike + db eval { + SELECT 1, x FROM t8 WHERE x LIKE '%h%'; + SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; + } +} {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr} + + +finish_test