# 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