From 487e15dc239ccdb3344d1c99ce120e872bab4a74 Mon Sep 17 00:00:00 2001
From: Hans-Christoph Steiner <hans@eds.org>
Date: Thu, 20 Sep 2012 18:34:38 -0400
Subject: Imported Upstream version 2.0.6

---
 test/vtab1.test | 91 +++++++++++++++++++++++++++++++++++++++++++++++++++++----
 1 file changed, 86 insertions(+), 5 deletions(-)

(limited to 'test/vtab1.test')

diff --git a/test/vtab1.test b/test/vtab1.test
index 16f1b43..38aec09 100644
--- a/test/vtab1.test
+++ b/test/vtab1.test
@@ -15,6 +15,7 @@
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
+set testprefix vtab1
 
 ifcapable !vtab||!schema_pragmas {
   finish_test
@@ -43,6 +44,9 @@ ifcapable !vtab||!schema_pragmas {
 #
 # vtab1-14.*: Test 'IN' constraints - i.e. "SELECT * FROM t1 WHERE id IN(...)"
 #
+# vtab1-18.*: Check that the LIKE optimization is not applied when the lhs
+#             is a virtual table column.
+#
 
 
 #----------------------------------------------------------------------
@@ -51,7 +55,7 @@ ifcapable !vtab||!schema_pragmas {
 
 # We cannot create a virtual table if the module has not been registered.
 #
-do_test vtab1-1.1 {
+do_test vtab1-1.1.1 {
   explain {
     CREATE VIRTUAL TABLE t1 USING echo;
   }
@@ -59,6 +63,11 @@ do_test vtab1-1.1 {
     CREATE VIRTUAL TABLE t1 USING echo;
   }
 } {1 {no such module: echo}}
+do_test vtab1-1.1.2 {
+  catchsql {
+    CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo;
+  }
+} {1 {no such module: echo}}
 do_test vtab1-1.2 {
   execsql {
     SELECT name FROM sqlite_master ORDER BY 1
@@ -75,11 +84,16 @@ register_echo_module [sqlite3_connection_pointer db]
 # The "echo" module does not invoke sqlite3_declare_vtab() if it is
 # passed zero arguments.
 #
-do_test vtab1-1.3 {
+do_test vtab1-1.3.1 {
   catchsql {
     CREATE VIRTUAL TABLE t1 USING echo;
   }
 } {1 {vtable constructor did not declare schema: t1}}
+do_test vtab1-1.3.2 {
+  catchsql {
+    CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo;
+  }
+} {1 {vtable constructor did not declare schema: t1}}
 do_test vtab1-1.4 {
   execsql {
     SELECT name FROM sqlite_master ORDER BY 1
@@ -90,11 +104,16 @@ do_test vtab1-1.4 {
 # the virtual table if it is passed an argument that does not correspond
 # to an existing real table in the same database.
 #
-do_test vtab1-1.5 {
+do_test vtab1-1.5.1 {
   catchsql {
     CREATE VIRTUAL TABLE t1 USING echo(no_such_table);
   }
 } {1 {vtable constructor failed: t1}}
+do_test vtab1-1.5.2 {
+  catchsql {
+    CREATE VIRTUAL TABLE IF NOT EXISTS t1 USING echo(no_such_table);
+  }
+} {1 {vtable constructor failed: t1}}
 do_test vtab1-1.6 {
   execsql {
     SELECT name FROM sqlite_master ORDER BY 1
@@ -128,17 +147,27 @@ do_test vtab-1.2152.4 {
 # select an illegal table-name (i.e a reserved name or the name of a
 # table that already exists).
 #
-do_test vtab1-1.7 {
+do_test vtab1-1.7.1 {
   catchsql {
     CREATE VIRTUAL TABLE sqlite_master USING echo;
   }
 } {1 {object name reserved for internal use: sqlite_master}}
-do_test vtab1-1.8 {
+do_test vtab1-1.7.2 {
+  catchsql {
+    CREATE VIRTUAL TABLE IF NOT EXISTS sqlite_master USING echo;
+  }
+} {1 {object name reserved for internal use: sqlite_master}}
+do_test vtab1-1.8.1 {
   catchsql {
     CREATE TABLE treal(a, b, c);
     CREATE VIRTUAL TABLE treal USING echo(treal);
   }
 } {1 {table treal already exists}}
+do_test vtab1-1.8.2 {
+  catchsql {
+    CREATE VIRTUAL TABLE IF NOT EXISTS treal USING echo(treal);
+  }
+} {0 {}}
 do_test vtab1-1.9 {
   execsql {
     DROP TABLE treal;
@@ -1193,5 +1222,57 @@ do_test vtab1-17.1 {
   }
 } {}
 
+#-------------------------------------------------------------------------
+# The following tests - vtab1-18.* - test that the optimization of LIKE
+# constraints in where.c plays well with virtual tables.
+#
+#   18.1.*: Case-insensitive LIKE.
+#   18.2.*: Case-sensitive LIKE.
+#
 unset -nocomplain echo_module_begin_fail
+
+do_execsql_test 18.1.0 {
+  CREATE TABLE t6(a, b TEXT);
+  CREATE INDEX i6 ON t6(b, a);
+  INSERT INTO t6 VALUES(1, 'Peter');
+  INSERT INTO t6 VALUES(2, 'Andrew');
+  INSERT INTO t6 VALUES(3, 'James');
+  INSERT INTO t6 VALUES(4, 'John');
+  INSERT INTO t6 VALUES(5, 'Phillip');
+  INSERT INTO t6 VALUES(6, 'Bartholomew');
+  CREATE VIRTUAL TABLE e6 USING echo(t6);
+}
+
+foreach {tn sql res filter} {
+  1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5}
+    {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James}
+
+  1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4}
+    {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K}
+
+  1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
+    {xFilter {SELECT rowid, * FROM 't6'}}
+
+  1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
+    {xFilter {SELECT rowid, * FROM 't6'}}
+} {
+  set echo_module {}
+  do_execsql_test 18.$tn.1 $sql $res
+  do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
+}
+
+do_execsql_test 18.2.0 {  PRAGMA case_sensitive_like = ON }
+foreach {tn sql res filter} {
+  2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4}
+    {xFilter {SELECT rowid, * FROM 't6'}}
+
+  2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
+    {xFilter {SELECT rowid, * FROM 't6'}}
+} {
+  set echo_module {}
+  do_execsql_test 18.$tn.1 $sql $res
+  do_test         18.$tn.2 { lrange $::echo_module 2 end } $filter
+}
+do_execsql_test 18.2.x {  PRAGMA case_sensitive_like = OFF }
+
 finish_test
-- 
cgit v1.2.3