Import SQLite 3.8.7.4.

third_party/sqlite/src/test/vtab1.test

 # 2006 June 10
 #
 # 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 creating and dropping virtual tables.
 #
 # $Id: vtab1.test,v 1.57 2008/08/01 17:51:47 danielk1977 Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
+set testprefix vtab1
 
 ifcapable !vtab||!schema_pragmas {
   finish_test
   return
 }
 
 #----------------------------------------------------------------------
 # Organization of tests in this file:
 #
 # vtab1-1.*: Error conditions and other issues surrounding creation/connection
 #            of a virtual module.
 # vtab1-2.*: Test sqlite3_declare_vtab() and the xConnect/xDisconnect methods.
 # vtab1-3.*: Table scans and WHERE clauses.
 # vtab1-4.*: Table scans and ORDER BY clauses.
 # vtab1-5.*: Test queries that include joins. This brings the
 #            sqlite3_index_info.estimatedCost variable into play.
 # vtab1-6.*: Test UPDATE/INSERT/DELETE on vtables.
 # vtab1-7.*: Test sqlite3_last_insert_rowid().
 #
 # This file uses the "echo" module (see src/test8.c). Refer to comments
 # in that file for the special behaviour of the Tcl $echo_module variable.
 #
 # TODO: 
 #   * How to test the sqlite3_index_constraint_usage.omit field?
 #   * vtab1-5.*
 #
 # 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.
+#
 
 
 #----------------------------------------------------------------------
 # Test cases vtab1.1.*
 #
 
 # 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;
   }
   catchsql {
     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
   }
 } {}
 
 # Register the module
 register_echo_module [sqlite3_connection_pointer db]
 
 # Once a module has been registered, virtual tables using that module
 # may be created. However if a module xCreate() fails to call
 # sqlite3_declare_vtab() an error will be raised and the table not created.
 #
 # 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
   }
 } {}
 
 # The "echo" module xCreate method returns an error and does not create
 # 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
   }
 } {}
 
 # Ticket #2156.  Using the sqlite3_prepare_v2() API, make sure that
 # a CREATE VIRTUAL TABLE statement can be used multiple times.
 #
 do_test vtab1-1.2152.1 {
@@ skipping 13 matching lines @@
 } SQLITE_DONE
 do_test vtab-1.2152.4 {
   sqlite3_finalize $STMT
   db eval {DROP TABLE t2152a; DROP TABLE t2152b}
 } {}
 
 # Test to make sure nothing goes wrong and no memory is leaked if we 
 # 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;
     SELECT name FROM sqlite_master ORDER BY 1
   }
 } {}
 
 do_test vtab1-1.10 {
   execsql {
     CREATE TABLE treal(a, b, c);
@@ skipping 430 matching lines @@
   execsql {
     SELECT * FROM et1, et2 WHERE et2.d = 2;
   }
 } [list \
   1 red green 2 hearts diamonds  \
   2 blue black 2 hearts diamonds \
 ]
 do_test vtab1-5-7 {
   filter $::echo_module
 } [list \
+  xFilter {SELECT rowid, * FROM 't1'}             \
   xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
-  xFilter {SELECT rowid, * FROM 't1'}             \
+  xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
 ]
 
 execsql {
   DROP TABLE t1;
   DROP TABLE t2;
   DROP TABLE et1;
   DROP TABLE et2;
 }
 
 #----------------------------------------------------------------------
@@ skipping 113 matching lines @@
   }
   db changes
 } {2}
 do_test vtab1-6-8.4 {
   execsql {
     SELECT * FROM techo ORDER BY a;
   }
 } {}
 execsql {PRAGMA count_changes=OFF}
 
-file delete -force test2.db
-file delete -force test2.db-journal
+forcedelete test2.db
+forcedelete test2.db-journal
 sqlite3 db2 test2.db
 execsql {
   CREATE TABLE techo(a PRIMARY KEY, b, c);
 } db2
 proc check_echo_table {tn} {
   set ::data1 [execsql {SELECT rowid, * FROM techo}]
   set ::data2 [execsql {SELECT rowid, * FROM techo} db2]
   do_test $tn {
     string equal $::data1 $::data2
   } 1
@@ skipping 264 matching lines @@
     INSERT INTO b VALUES(2, 'C', 'D');
     INSERT INTO b VALUES(3, 'E', 'F');
     INSERT INTO c VALUES(3, 'G', 'H');
     CREATE VIRTUAL TABLE echo_c USING echo(c);
   }
 } {}
 
 # First test outside of a transaction.
 do_test vtab1.12-2 {
   catchsql { INSERT INTO echo_c SELECT * FROM b; }
-} {1 {echo-vtab-error: column a is not unique}}
+} {1 {echo-vtab-error: UNIQUE constraint failed: c.a}}
 do_test vtab1.12-2.1 {
   sqlite3_errmsg db
-} {echo-vtab-error: column a is not unique}
+} {echo-vtab-error: UNIQUE constraint failed: c.a}
 do_test vtab1.12-3 {
   execsql { SELECT * FROM c }
 } {3 G H}
 
 # Now the real test - wrapped in a transaction.
 do_test vtab1.12-4 {
   execsql  {BEGIN}
   catchsql { INSERT INTO echo_c SELECT * FROM b; }
-} {1 {echo-vtab-error: column a is not unique}}
+} {1 {echo-vtab-error: UNIQUE constraint failed: c.a}}
 do_test vtab1.12-5 {
   execsql { SELECT * FROM c }
 } {3 G H}
 do_test vtab1.12-6 {
   execsql { COMMIT }
   execsql { SELECT * FROM c }
 } {3 G H}
 
 # At one point (ticket #2759), a WHERE clause of the form "<column> IS NULL"
 # on a virtual table was causing an assert() to fail in the compiler.
@@ skipping 19 matching lines @@
     SELECT * FROM echo_c WHERE b IS NULL 
   }
 } {15 {} 16}
 do_test vtab1.13-3 {
   execsql { 
     SELECT * FROM echo_c WHERE b IS NULL AND a = 15;
   }
 } {15 {} 16}
 
 
-do_test vtab1-14.1 {
-  execsql { DELETE FROM c }
-  set echo_module ""
-  execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) }
-  set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}]
+do_test vtab1-14.001 {
+  execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (1,2,3)}
+} {1 3 G H 2 {} 15 16 3 15 {} 16}
+do_test vtab1-14.002 {
+  execsql {SELECT rowid, * FROM echo_c WHERE rowid IN (1,2,3)}
+} {1 3 G H 2 {} 15 16 3 15 {} 16}
+do_test vtab1-14.003 {
+  execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (0,1,5,2,'a',3,NULL)}
+} {1 3 G H 2 {} 15 16 3 15 {} 16}
+do_test vtab1-14.004 {
+  execsql {SELECT rowid, * FROM echo_c WHERE rowid IN (0,1,5,'a',2,3,NULL)}
+} {1 3 G H 2 {} 15 16 3 15 {} 16}
+do_test vtab1-14.005 {
+  execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,1,5,'a',2,3)}
+} {}
+do_test vtab1-14.006 {
+  execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,5,'a',2,3)}
+} {1 3 G H}
+do_test vtab1-14.007 {
+  execsql {SELECT rowid, * FROM echo_c WHERE +rowid NOT IN (0,5,'a',2,3,NULL)}
+} {}
+do_test vtab1-14.008 {
+  execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,5,'a',2,3,NULL)}
+} {}
+do_test vtab1-14.011 {
+  execsql {SELECT * FROM echo_c WHERE +a IN (1,3,8,'x',NULL,15,24)}
+} {3 G H 15 {} 16}
+do_test vtab1-14.012 {
+  execsql {SELECT * FROM echo_c WHERE a IN (1,3,8,'x',NULL,15,24)}
+} {3 G H 15 {} 16}
+do_test vtab1-14.013 {
+  execsql {SELECT * FROM echo_c WHERE a NOT IN (1,8,'x',15,24)}
+} {3 G H}
+do_test vtab1-14.014 {
+  execsql {SELECT * FROM echo_c WHERE a NOT IN (1,8,'x',NULL,15,24)}
+} {}
+do_test vtab1-14.015 {
+  execsql {SELECT * FROM echo_c WHERE +a NOT IN (1,8,'x',NULL,15,24)}
+} {}
+
+
+
+#do_test vtab1-14.1 {
+#  execsql { DELETE FROM c }
+#  set echo_module ""
+#  execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) }
+#  set echo_module
+#} {/.*xBestIndex {SELECT rowid, . FROM 'c' WHERE rowid = .} xFilter {SELECT rowid, . FROM 'c'} 1/}
 
 do_test vtab1-14.2 {
   set echo_module ""
   execsql { SELECT * FROM echo_c WHERE rowid = 1 }
   set echo_module
 } [list xBestIndex {SELECT rowid, * FROM 'c' WHERE rowid = ?} xFilter {SELECT rowid, * FROM 'c' WHERE rowid = ?} 1]
 
 do_test vtab1-14.3 {
   set echo_module ""
   execsql { SELECT * FROM echo_c WHERE a = 1 }
   set echo_module
 } [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1]
 
-do_test vtab1-14.4 {
-  set echo_module ""
-  execsql { SELECT * FROM echo_c WHERE a IN (1, 2) }
-  set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}]
+#do_test vtab1-14.4 {
+#  set echo_module ""
+#  execsql { SELECT * FROM echo_c WHERE a IN (1, 2) }
+#  set echo_module
+#} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE a = .} xFilter {SELECT rowid, . FROM 'c' WHERE a = .} 1/}
 
 do_test vtab1-15.1 {
   execsql {
     CREATE TABLE t1(a, b, c);
     CREATE VIRTUAL TABLE echo_t1 USING echo(t1);
   }
 } {}
 do_test vtab1-15.2 {
   execsql {
     INSERT INTO echo_t1(rowid) VALUES(45);
@@ skipping 72 matching lines @@
 do_test vtab1-17.1 {
   execsql { 
     PRAGMA writable_schema = 1;
     INSERT INTO sqlite_master VALUES(
       'table', 't3', 't3', 0, 'INSERT INTO "%s%s" VALUES(1)'
     );
   }
   catchsql { CREATE VIRTUAL TABLE t4 USING echo(t3); }
 } {1 {vtable constructor failed: t4}}
 
+# This test verifies that ticket 48f29963 is fixed.
+#
+do_test vtab1-17.1 {
+  execsql { 
+    CREATE TABLE t5(a, b);
+    CREATE VIRTUAL TABLE e5 USING echo_v2(t5);
+    BEGIN;
+      INSERT INTO e5 VALUES(1, 2);
+      DROP TABLE e5;
+      SAVEPOINT one;
+      ROLLBACK TO one;
+    COMMIT;
+  }
+} {}
+
+do_test vtab1-17.2 {
+  execsql { DELETE FROM sqlite_master WHERE sql LIKE 'insert%' }
+} {}
+
+#-------------------------------------------------------------------------
+# 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 }
+
+#-------------------------------------------------------------------------
+# Test that an existing module may not be overridden.
+#
+do_test 19.1 {
+  sqlite3 db2 test.db
+  register_echo_module [sqlite3_connection_pointer db2]
+} SQLITE_OK
+do_test 19.2 {
+  register_echo_module [sqlite3_connection_pointer db2]
+} SQLITE_MISUSE
+do_test 19.3 {
+  db2 close
+} {}
+
+#-------------------------------------------------------------------------
+# Test that the bug fixed by [b0c1ba655d69] really is fixed.
+#
+do_execsql_test 20.1 {
+  CREATE TABLE t7 (a, b);
+  CREATE TABLE t8 (c, d);
+  CREATE INDEX i2 ON t7(a);
+  CREATE INDEX i3 ON t7(b);
+  CREATE INDEX i4 ON t8(c);
+  CREATE INDEX i5 ON t8(d);
+
+  CREATE VIRTUAL TABLE t7v USING echo(t7);
+  CREATE VIRTUAL TABLE t8v USING echo(t8);
+}
+
+do_test 20.2 {
+  for {set i 0} {$i < 1000} {incr i} {
+    db eval {INSERT INTO t7 VALUES($i, $i)}
+    db eval {INSERT INTO t8 VALUES($i, $i)}
+  }
+} {}
+
+do_execsql_test 20.3 {
+  SELECT a, b FROM (
+      SELECT a, b FROM t7 WHERE a=11 OR b=12
+      UNION ALL
+      SELECT c, d FROM t8 WHERE c=5 OR d=6
+  )
+  ORDER BY 1, 2;
+} {5 5 6 6 11 11 12 12}
+
+do_execsql_test 20.4 {
+  SELECT a, b FROM (
+      SELECT a, b FROM t7v WHERE a=11 OR b=12
+      UNION ALL
+      SELECT c, d FROM t8v WHERE c=5 OR d=6
+  )
+  ORDER BY 1, 2;
+} {5 5 6 6 11 11 12 12}
+
+#-------------------------------------------------------------------------
+#
+do_execsql_test 21.1 {
+  CREATE TABLE t9(a,b,c);
+  CREATE VIRTUAL TABLE t9v USING echo(t9);
+
+  INSERT INTO t9 VALUES(1,2,3);
+  INSERT INTO t9 VALUES(3,2,1);
+  INSERT INTO t9 VALUES(2,2,2);
+}
+
+do_execsql_test 21.2 {
+  SELECT * FROM t9v WHERE a<b;
+} {1 2 3}
+
+do_execsql_test 21.3 {
+  SELECT * FROM t9v WHERE a=b;
+} {2 2 2}
+
 finish_test