1 files changed, 278 insertions, 0 deletions
diff --git a/test/like3.test b/test/like3.test
new file mode 100644
index 0000000..a93e113
--- /dev/null
+++ b/test/like3.test
@@ -0,0 +1,278 @@
+# 2015-03-06
+#
+# 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 and that those optimizations work correctly when there
+# are both strings and blobs being tested.
+#
+# Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the following
+# SQL was not working correctly:
+#
+#     CREATE TABLE t1(x TEXT UNIQUE COLLATE nocase);
+#     INSERT INTO t1(x) VALUES(x'616263');
+#     SELECT 'query-1', x FROM t1 WHERE x LIKE 'a%';
+#     SELECT 'query-2', x FROM t1 WHERE +x LIKE 'a%';
+#
+# This script verifies that it works right now.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable !like_match_blobs {
+  finish_test
+  return
+}
+
+do_execsql_test like3-1.1 {
+  PRAGMA encoding=UTF8;
+  CREATE TABLE t1(a,b TEXT COLLATE nocase);
+  INSERT INTO t1(a,b)
+     VALUES(1,'abc'),
+           (2,'ABX'),
+           (3,'BCD'),
+           (4,x'616263'),
+           (5,x'414258'),
+           (6,x'424344');
+  CREATE INDEX t1ba ON t1(b,a);
+
+  SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a;
+} {1 abc 2 ABX 4 abc 5 ABX}
+do_execsql_test like3-1.2 {
+  SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a;
+} {1 abc 2 ABX 4 abc 5 ABX}
+
+do_execsql_test like3-2.0 {
+  CREATE TABLE t2(a, b TEXT);
+  INSERT INTO t2 SELECT a, b FROM t1;
+  CREATE INDEX t2ba ON t2(b,a);
+  SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a;
+} {1 abc 4 abc}
+do_execsql_test like3-2.1 {
+  SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a;
+} {1 abc 4 abc}
+do_execsql_test like3-2.2 {
+  SELECT a, b FROM t2 WHERE b>=x'6162' AND b GLOB 'ab*'
+} {4 abc}
+do_execsql_test like3-2.3 {
+  SELECT a, b FROM t2 WHERE +b>=x'6162' AND +b GLOB 'ab*'
+} {4 abc}
+do_execsql_test like3-2.4 {
+  SELECT a, b FROM t2 WHERE b GLOB 'ab*' AND b>=x'6162'
+} {4 abc}
+do_execsql_test like3-2.5 {
+  SELECT a, b FROM t2 WHERE +b GLOB 'ab*' AND +b>=x'6162'
+} {4 abc}
+
+do_execsql_test like3-3.0 {
+  CREATE TABLE t3(x TEXT PRIMARY KEY COLLATE nocase);
+  INSERT INTO t3(x) VALUES('aaa'),('abc'),('abd'),('abe'),('acz');
+  INSERT INTO t3(x) SELECT CAST(x AS blob) FROM t3;
+  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x;
+} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
+do_execsql_test like3-3.1 {
+  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x DESC;
+} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
+do_execsql_test like3-3.1ck {
+  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x DESC;
+} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
+do_execsql_test like3-3.2 {
+  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY x ASC;
+} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
+do_execsql_test like3-3.2ck {
+  SELECT quote(x) FROM t3 WHERE x LIKE 'ab%' ORDER BY +x ASC;
+} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
+
+do_execsql_test like3-4.0 {
+  CREATE TABLE t4(x TEXT COLLATE nocase);
+  CREATE INDEX t4x ON t4(x DESC);
+  INSERT INTO t4(x) SELECT x FROM t3;
+  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x;
+} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
+do_execsql_test like3-4.1 {
+  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x DESC;
+} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
+do_execsql_test like3-4.1ck {
+  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x DESC;
+} {X'616265' X'616264' X'616263' 'abe' 'abd' 'abc'}
+do_execsql_test like3-4.2 {
+  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY x ASC;
+} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
+do_execsql_test like3-4.2ck {
+  SELECT quote(x) FROM t4 WHERE x LIKE 'ab%' ORDER BY +x ASC;
+} {'abc' 'abd' 'abe' X'616263' X'616264' X'616265'}
+
+# 2018-09-10 ticket https://www.sqlite.org/src/tktview/c94369cae9b561b1f996
+# The like optimization fails for a column with numeric affinity if
+# the pattern '/%' or begins with the escape character.
+#
+do_execsql_test like3-5.100 {
+  CREATE TABLE t5a(x INT UNIQUE COLLATE nocase);
+  INSERT INTO t5a(x) VALUES('/abc'),(123),(-234);
+  SELECT x FROM t5a WHERE x LIKE '/%';
+} {/abc}
+do_eqp_test like3-5.101 {
+  SELECT x FROM t5a WHERE x LIKE '/%';
+} {
+  QUERY PLAN
+  `--SCAN t5a
+}
+do_execsql_test like3-5.110 {
+  SELECT x FROM t5a WHERE x LIKE '/a%';
+} {/abc}
+ifcapable !icu {
+do_eqp_test like3-5.111 {
+  SELECT x FROM t5a WHERE x LIKE '/a%';
+} {
+  QUERY PLAN
+  `--SEARCH t5a USING COVERING INDEX sqlite_autoindex_t5a_1 (x>? AND x<?)
+}
+}
+do_execsql_test like3-5.120 {
+  SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
+} {123}
+do_eqp_test like3-5.121 {
+  SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
+} {
+  QUERY PLAN
+  `--SCAN t5a
+}
+do_execsql_test like3-5.122 {
+  SELECT x FROM t5a WHERE x LIKE '^-2%' ESCAPE '^';
+} {-234}
+do_eqp_test like3-5.123 {
+  SELECT x FROM t5a WHERE x LIKE '^12%' ESCAPE '^';
+} {
+  QUERY PLAN
+  `--SCAN t5a
+}
+
+do_execsql_test like3-5.200 {
+  CREATE TABLE t5b(x INT UNIQUE COLLATE binary);
+  INSERT INTO t5b(x) VALUES('/abc'),(123),(-234);
+  SELECT x FROM t5b WHERE x GLOB '/*';
+} {/abc}
+do_eqp_test like3-5.201 {
+  SELECT x FROM t5b WHERE x GLOB '/*';
+} {
+  QUERY PLAN
+  `--SCAN t5b
+}
+do_execsql_test like3-5.210 {
+  SELECT x FROM t5b WHERE x GLOB '/a*';
+} {/abc}
+do_eqp_test like3-5.211 {
+  SELECT x FROM t5b WHERE x GLOB '/a*';
+} {
+  QUERY PLAN
+  `--SEARCH t5b USING COVERING INDEX sqlite_autoindex_t5b_1 (x>? AND x<?)
+}
+
+# 2019-05-01
+# another case of the above reported on the mailing list by Manuel Rigger.
+#
+do_execsql_test like3-5.300 {
+  CREATE TABLE t5c (c0 REAL);
+  CREATE INDEX t5c_0 ON t5c(c0 COLLATE NOCASE);
+  INSERT INTO t5c(rowid, c0) VALUES (99,'+/');
+  SELECT * FROM t5c WHERE (c0 LIKE '+/');
+} {+/}
+
+# 2019-05-08
+# Yet another case for the above from Manuel Rigger.
+#
+do_execsql_test like3-5.400 {
+  DROP TABLE IF EXISTS t0;
+  CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
+  INSERT INTO t0(c0) VALUES ('./');
+  SELECT * FROM t0 WHERE t0.c0 LIKE './';
+} {./}
+
+# 2019-06-14
+# Ticket https://www.sqlite.org/src/info/ce8717f0885af975
+do_execsql_test like3-5.410 {
+  DROP TABLE IF EXISTS t0;
+  CREATE TABLE t0(c0 INT UNIQUE COLLATE NOCASE);
+  INSERT INTO t0(c0) VALUES ('.1%');
+  SELECT * FROM t0 WHERE t0.c0 LIKE '.1%';
+} {.1%}
+
+# 2019-09-03
+# Ticket https://www.sqlite.org/src/info/0f0428096f
+do_execsql_test like3-5.420 {
+  DROP TABLE IF EXISTS t0;
+  CREATE TABLE t0(c0 UNIQUE);
+  INSERT INTO t0(c0) VALUES(-1);
+  SELECT * FROM t0 WHERE t0.c0 GLOB '-*';
+} {-1}
+do_execsql_test like3-5.421 {
+  SELECT t0.c0 GLOB '-*' FROM t0;
+} {1}
+
+
+
+# 2019-02-27
+# Verify that the LIKE optimization works with an ESCAPE clause when
+# using PRAGMA case_sensitive_like=ON.
+#
+ifcapable !icu {
+do_execsql_test like3-6.100 {
+  DROP TABLE IF EXISTS t1;
+  CREATE TABLE t1(path TEXT COLLATE nocase PRIMARY KEY,a,b,c) WITHOUT ROWID;
+}
+do_eqp_test like3-6.110 {
+  SELECT * FROM t1 WHERE path LIKE 'a%';
+} {
+  QUERY PLAN
+  `--SEARCH t1 USING PRIMARY KEY (path>? AND path<?)
+}
+do_eqp_test like3-6.120 {
+  SELECT * FROM t1 WHERE path LIKE 'a%' ESCAPE 'x';
+} {
+  QUERY PLAN
+  `--SEARCH t1 USING PRIMARY KEY (path>? AND path<?)
+}
+do_execsql_test like3-6.200 {
+  DROP TABLE IF EXISTS t2;
+  CREATE TABLE t2(path TEXT,x,y,z);
+  CREATE INDEX t2path ON t2(path COLLATE nocase);
+  CREATE INDEX t2path2 ON t2(path);
+}
+do_eqp_test like3-6.210 {
+  SELECT * FROM t2 WHERE path LIKE 'a%';
+} {
+  QUERY PLAN
+  `--SEARCH t2 USING INDEX t2path (path>? AND path<?)
+}
+do_eqp_test like3-6.220 {
+  SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';
+} {
+  QUERY PLAN
+  `--SEARCH t2 USING INDEX t2path (path>? AND path<?)
+}
+db eval {PRAGMA case_sensitive_like=ON}
+do_eqp_test like3-6.230 {
+  SELECT * FROM t2 WHERE path LIKE 'a%';
+} {
+  QUERY PLAN
+  `--SEARCH t2 USING INDEX t2path2 (path>? AND path<?)
+}
+do_eqp_test like3-6.240 {
+  SELECT * FROM t2 WHERE path LIKE 'a%' ESCAPE '\';
+} {
+  QUERY PLAN
+  `--SEARCH t2 USING INDEX t2path2 (path>? AND path<?)
+}
+}
+
+finish_test