SQLite

#
# EVIDENCE-OF: R-04532-11527 The LIKE operator is case sensitive by
# default for unicode characters that are beyond the ASCII range.
#
# EVIDENCE-OF: R-44381-11669 the expression
# 'a' LIKE 'A' is TRUE but
# 'æ' LIKE 'Æ' is FALSE.
#
#   The restriction to ASCII characters does not apply if the ICU
#   library is compiled in. When ICU is enabled SQLite does not act
#   as it does "by default".
#
do_execsql_test e_expr-14.5.1 { SELECT 'A' LIKE 'a'         } 1
ifcapable !icu {
  do_execsql_test e_expr-14.5.2 "SELECT '\u00c6' LIKE '\u00e6'" 0
}

# EVIDENCE-OF: R-56683-13731 If the optional ESCAPE clause is present,
# then the expression following the ESCAPE keyword must evaluate to a
# string consisting of a single character.
#
do_catchsql_test e_expr-14.6.1 { 
  SELECT 'A' LIKE 'a' ESCAPE '12' 

# EVIDENCE-OF: R-51359-17496 The infix LIKE operator is implemented by
# calling the application-defined SQL functions like(Y,X) or like(Y,X,Z).
#
proc likefunc {args} {
  eval lappend ::likeargs $args
  return 1
}
db func like -argcount 2 likefunc
db func like -argcount 3 likefunc
set ::likeargs [list]
do_execsql_test e_expr-15.1.1 { SELECT 'abc' LIKE 'def' } 1
do_test         e_expr-15.1.2 { set likeargs } {def abc}
set ::likeargs [list]
do_execsql_test e_expr-15.1.3 { SELECT 'abc' LIKE 'def' ESCAPE 'X' } 1
do_test         e_expr-15.1.4 { set likeargs } {def abc X}
db close

do_test         e_expr-17.3.4 { set globargs } {Y X}
sqlite3 db test.db

# EVIDENCE-OF: R-41650-20872 No regexp() user function is defined by
# default and so use of the REGEXP operator will normally result in an
# error message.
#
#   There is a regexp function if ICU is enabled though.
#
ifcapable !icu {
  do_catchsql_test e_expr-18.1.1 { 
    SELECT regexp('abc', 'def') 
  } {1 {no such function: regexp}}
  do_catchsql_test e_expr-18.1.2 { 
    SELECT 'abc' REGEXP 'def'
  } {1 {no such function: REGEXP}}
}

# EVIDENCE-OF: R-33693-50180 The REGEXP operator is a special syntax for
# the regexp() user function.
#
# EVIDENCE-OF: R-57289-13578 If a application-defined SQL function named
# "regexp" is added at run-time, that function will be called in order
# to implement the REGEXP operator.

# Tests of the REGEXP operator
#
do_test like-2.1 {
  proc test_regexp {a b} {
    return [regexp $a $b]
  }
  db function regexp -argcount 2 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;

  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}


ifcapable like_opt&&!icu {
  # Evaluate SQL.  Return the result set followed by the
  # and the number of full-scan steps.
  #
  db close
  sqlite3 db test.db
  proc count_steps {sql} {
    set r [db eval $sql]

    do_test like-9.5.2 {
      set res [sqlite3_exec_hex db {
         EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25'
      }]
      regexp {INDEX i2} $res
    } {1}
  }


  # Do an SQL statement.  Append the search count to the end of the result.
  #
  proc count sql {
    set ::sqlite_search_count 0
    set ::sqlite_like_count 0
    return [concat [execsql $sql] scan $::sqlite_search_count \
             like $::sqlite_like_count]
  }

  # The LIKE and GLOB optimizations do not work on columns with
  # affinity other than TEXT.
  # Ticket #3901
  #
  do_test like-10.1 {
    db close
    sqlite3 db test.db
    execsql {
      CREATE TABLE t10(
        a INTEGER PRIMARY KEY,
        b INTEGER COLLATE nocase UNIQUE,
        c NUMBER COLLATE nocase UNIQUE,
        d BLOB COLLATE nocase UNIQUE,
        e COLLATE nocase UNIQUE,
        f TEXT COLLATE nocase UNIQUE
      );
      INSERT INTO t10 VALUES(1,1,1,1,1,1);
      INSERT INTO t10 VALUES(12,12,12,12,12,12);
      INSERT INTO t10 VALUES(123,123,123,123,123,123);
      INSERT INTO t10 VALUES(234,234,234,234,234,234);
      INSERT INTO t10 VALUES(345,345,345,345,345,345);
      INSERT INTO t10 VALUES(45,45,45,45,45,45);
    }
    count {
      SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.2 {
    count {
      SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.3 {
    count {
      SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.4 {
    count {
      SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.5 {
    count {
      SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 3 like 0}
  do_test like-10.6 {
    count {
      SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.10 {
    execsql {
      CREATE TABLE t10b(
        a INTEGER PRIMARY KEY,
        b INTEGER UNIQUE,
        c NUMBER UNIQUE,
        d BLOB UNIQUE,
        e UNIQUE,
        f TEXT UNIQUE
      );
      INSERT INTO t10b SELECT * FROM t10;
    }
    count {
      SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.11 {
    count {
      SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.12 {
    count {
      SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.13 {
    count {
      SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
  do_test like-10.14 {
    count {
      SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 3 like 0}
  do_test like-10.15 {
    count {
      SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY a;
    }
  } {12 123 scan 5 like 6}
}

# LIKE and GLOB where the default collating sequence is not appropriate
# but an index with the appropriate collating sequence exists.
#
do_test like-11.0 {
  execsql {
    CREATE TABLE t11(