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