1 files changed, 226 insertions, 92 deletions

diff --git a/third_party/sqlite/src/test/like.test b/third_party/sqlite/src/test/like.test
index a2de686..bd9a6c3 100644
--- a/third_party/sqlite/src/test/like.test
+++ b/third_party/sqlite/src/test/like.test
@@ -115,7 +115,7 @@ do_test like-2.1 {
   proc test_regexp {a b} {
     return [regexp $a $b]
   }
-  db function regexp test_regexp
+  db function regexp -argcount 2 test_regexp
   execsql {
     SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1;
   }
@@ -194,6 +194,31 @@ do_test like-3.4 {
   set sqlite_like_count
 } 0
 
+# The LIKE optimization still works when the RHS is a string with no
+# wildcard.  Ticket [e090183531fc2747]
+#
+do_test like-3.4.2 {
+  queryplan {
+    SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1;
+  }
+} {a nosort {} i1}
+do_test like-3.4.3 {
+  queryplan {
+    SELECT x FROM t1 WHERE x LIKE 'ab' ORDER BY 1;
+  }
+} {ab nosort {} i1}
+do_test like-3.4.4 {
+  queryplan {
+    SELECT x FROM t1 WHERE x LIKE 'abcd' ORDER BY 1;
+  }
+} {abcd nosort {} i1}
+do_test like-3.4.5 {
+  queryplan {
+    SELECT x FROM t1 WHERE x LIKE 'abcde' ORDER BY 1;
+  }
+} {nosort {} i1}
+
+
 # Partial optimization when the pattern does not end in '%'
 #
 do_test like-3.5 {
@@ -309,6 +334,26 @@ do_test like-3.24 {
   set sqlite_like_count
 } 6
 
+# GLOB optimization when there is no wildcard.  Ticket [e090183531fc2747]
+#
+do_test like-3.25 {
+  queryplan {
+    SELECT x FROM t1 WHERE x GLOB 'a' ORDER BY 1;
+  }
+} {a nosort {} i1}
+do_test like-3.26 {
+  queryplan {
+    SELECT x FROM t1 WHERE x GLOB 'abcd' ORDER BY 1;
+  }
+} {abcd nosort {} i1}
+do_test like-3.27 {
+  queryplan {
+    SELECT x FROM t1 WHERE x GLOB 'abcde' ORDER BY 1;
+  }
+} {nosort {} i1}
+
+
+
 # No optimization if the LHS of the LIKE is not a column name or
 # if the RHS is not a string.
 #
@@ -563,7 +608,7 @@ do_test like-8.4 {
 } {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr}
 
 
-ifcapable like_opt {
+ifcapable like_opt&&!icu {
   # Evaluate SQL.  Return the result set followed by the
   # and the number of full-scan steps.
   #
@@ -628,110 +673,199 @@ ifcapable like_opt {
       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]
+  # 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}
 }
 
-# The LIKE and GLOB optimizations do not work on columns with
-# affinity other than TEXT.
-# Ticket #3901
+# LIKE and GLOB where the default collating sequence is not appropriate
+# but an index with the appropriate collating sequence exists.
 #
-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 {
+do_test like-11.0 {
   execsql {
-    CREATE TABLE t10b(
+    CREATE TABLE t11(
       a INTEGER PRIMARY KEY,
-      b INTEGER UNIQUE,
-      c NUMBER UNIQUE,
-      d BLOB UNIQUE,
-      e UNIQUE,
-      f TEXT UNIQUE
+      b TEXT COLLATE nocase,
+      c TEXT COLLATE binary
     );
-    INSERT INTO t10b SELECT * FROM t10;
+    INSERT INTO t11 VALUES(1, 'a','a');
+    INSERT INTO t11 VALUES(2, 'ab','ab');
+    INSERT INTO t11 VALUES(3, 'abc','abc');
+    INSERT INTO t11 VALUES(4, 'abcd','abcd');
+    INSERT INTO t11 VALUES(5, 'A','A');
+    INSERT INTO t11 VALUES(6, 'AB','AB');
+    INSERT INTO t11 VALUES(7, 'ABC','ABC');
+    INSERT INTO t11 VALUES(8, 'ABCD','ABCD');
+    INSERT INTO t11 VALUES(9, 'x','x');
+    INSERT INTO t11 VALUES(10, 'yz','yz');
+    INSERT INTO t11 VALUES(11, 'X','X');
+    INSERT INTO t11 VALUES(12, 'YZ','YZ');
+    SELECT count(*) FROM t11;
   }
-  count {
-    SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY a;
+} {12}
+do_test like-11.1 {
+  queryplan {
+    PRAGMA case_sensitive_like=OFF;
+    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
+  }
+} {abc abcd ABC ABCD nosort t11 *}
+do_test like-11.2 {
+  queryplan {
+    PRAGMA case_sensitive_like=ON;
+    SELECT b FROM t11 WHERE b LIKE 'abc%' 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;
+} {abc abcd nosort t11 *}
+do_test like-11.3 {
+  queryplan {
+    PRAGMA case_sensitive_like=OFF;
+    CREATE INDEX t11b ON t11(b);
+    SELECT b FROM t11 WHERE b LIKE 'abc%' 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;
+} {abc abcd ABC ABCD sort {} t11b}
+do_test like-11.4 {
+  queryplan {
+    PRAGMA case_sensitive_like=ON;
+    SELECT b FROM t11 WHERE b LIKE 'abc%' 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;
+} {abc abcd nosort t11 *}
+do_test like-11.5 {
+  queryplan {
+    PRAGMA case_sensitive_like=OFF;
+    DROP INDEX t11b;
+    CREATE INDEX t11bnc ON t11(b COLLATE nocase);
+    SELECT b FROM t11 WHERE b LIKE 'abc%' 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;
+} {abc abcd ABC ABCD sort {} t11bnc}
+do_test like-11.6 {
+  queryplan {
+    CREATE INDEX t11bb ON t11(b COLLATE binary);
+    SELECT b FROM t11 WHERE b LIKE 'abc%' 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;
+} {abc abcd ABC ABCD sort {} t11bnc}
+do_test like-11.7 {
+  queryplan {
+    PRAGMA case_sensitive_like=ON;
+    SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
+  }
+} {abc abcd sort {} t11bb}
+do_test like-11.8 {
+  queryplan {
+    PRAGMA case_sensitive_like=OFF;
+    SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a;
+  }
+} {abc abcd sort {} t11bb}
+do_test like-11.9 {
+  queryplan {
+    CREATE INDEX t11cnc ON t11(c COLLATE nocase);
+    CREATE INDEX t11cb ON t11(c COLLATE binary);
+    SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a;
+  }
+} {abc abcd ABC ABCD sort {} t11cnc}
+do_test like-11.10 {
+  queryplan {
+    SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a;
   }
-} {12 123 scan 5 like 6}
+} {abc abcd sort {} t11cb}
 
 
 finish_test