Import SQLite 3.10.2.

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.
 #
 #***********************************************************************
@@ skipping 38 matching lines @@
 #
 
 
 #----------------------------------------------------------------------
 # Test cases vtab1.1.*
 #
 
 # We cannot create a virtual table if the module has not been registered.
 #
 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 {
@@ skipping 317 matching lines @@
   set echo_module ""
   execsql {
     SELECT * FROM t1;
   }
 } {1 2 3 4 5 6}
 do_test vtab1-3.7 {
   execsql {
     SELECT rowid, * FROM t1;
   }
 } {1 1 2 3 2 4 5 6}
-do_test vtab1-3.8 {
+do_test vtab1-3.8.1 {
   execsql {
     SELECT a AS d, b AS e, c AS f FROM t1;
   }
 } {1 2 3 4 5 6}
 
 # Execute some SELECT statements with WHERE clauses on the t1 table.
 # Then check the echo_module variable (written to by the module methods
 # in test8.c) to make sure the xBestIndex() and xFilter() methods were
 # called correctly.
 #
-do_test vtab1-3.8 {
+do_test vtab1-3.8.2 {
   set echo_module ""
   execsql {
     SELECT * FROM t1;
   }
   set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
-        xFilter    {SELECT rowid, * FROM 'treal'} ]
+} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
+        xFilter    {SELECT rowid, a, b, c FROM 'treal'} ]
 do_test vtab1-3.9 {
   set echo_module ""
   execsql {
     SELECT * FROM t1 WHERE b = 5;
   }
 } {4 5 6}
 do_test vtab1-3.10 {
   set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b = ?}   \
-        xFilter    {SELECT rowid, * FROM 'treal' WHERE b = ?} 5 ]
+} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b = ?}   \
+        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b = ?} 5 ]
 do_test vtab1-3.10 {
   set echo_module ""
   execsql {
     SELECT * FROM t1 WHERE b >= 5 AND b <= 10;
   }
 } {4 5 6}
 do_test vtab1-3.11 {
   set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
-        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 5 10 ]
+} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
+        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
+        5 10 ]
 do_test vtab1-3.12 {
   set echo_module ""
   execsql {
     SELECT * FROM t1 WHERE b BETWEEN 2 AND 10;
   }
 } {1 2 3 4 5 6}
 do_test vtab1-3.13 {
   set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?}      \
-        xFilter    {SELECT rowid, * FROM 'treal' WHERE b >= ? AND b <= ?} 2 10 ]
+} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
+        xFilter    {SELECT rowid, a, b, c FROM 'treal' WHERE b >= ? AND b <= ?}\
+        2 10 ]
 
 # Add a function for the MATCH operator. Everything always matches!
 #proc test_match {lhs rhs} {
 #  lappend ::echo_module MATCH $lhs $rhs
 #  return 1
 #}
 #db function match test_match
 
 set echo_module ""
 do_test vtab1-3.12 {
   set echo_module ""
   catchsql {
     SELECT * FROM t1 WHERE a MATCH 'string';
   }
 } {1 {unable to use function MATCH in the requested context}}
 do_test vtab1-3.13 {
   set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
-        xFilter    {SELECT rowid, * FROM 'treal'}]
+} [list xBestIndex {SELECT rowid, a, b, c FROM 'treal'} \
+        xFilter    {SELECT rowid, a, b, c FROM 'treal'}]
 ifcapable subquery {
 # The echo module uses a subquery internally to implement the MATCH operator.
 do_test vtab1-3.14 {
   set echo_module ""
   execsql {
     SELECT * FROM t1 WHERE b MATCH 'string';
   }
 } {}
 do_test vtab1-3.15 {
   set echo_module
 } [list xBestIndex \
-        {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')}  \
+        {SELECT rowid, a, b, c FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
         xFilter \
-        {SELECT rowid, * FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
+        {SELECT rowid, a, b, c FROM 'treal' WHERE b LIKE (SELECT '%'||?||'%')} \
         string ]
 }; #ifcapable subquery
 
 #----------------------------------------------------------------------
 # Test case vtab1-3 test table scans and the echo module's 
 # xBestIndex/xFilter handling of ORDER BY clauses.
 
 # This procedure executes the SQL.  Then it checks to see if the OP_Sort
 # opcode was executed.  If an OP_Sort did occur, then "sort" is appended
 # to the result.  If no OP_Sort happened, then "nosort" is appended.
@@ skipping 10 matching lines @@
 }
 
 do_test vtab1-4.1 {
   set echo_module ""
   cksort {
     SELECT b FROM t1 ORDER BY b;
   }
 } {2 5 nosort}
 do_test vtab1-4.2 {
   set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \
-        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ]
+} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b ASC} \
+        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b ASC} ]
 do_test vtab1-4.3 {
   set echo_module ""
   cksort {
     SELECT b FROM t1 ORDER BY b DESC;
   }
 } {5 2 nosort}
 do_test vtab1-4.4 {
   set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \
-        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ]
+} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b DESC} \
+        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal' ORDER BY b DESC} ]
 do_test vtab1-4.3 {
   set echo_module ""
   cksort {
     SELECT b FROM t1 ORDER BY b||'';
   }
 } {2 5 sort}
 do_test vtab1-4.4 {
   set echo_module
-} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
-        xFilter    {SELECT rowid, * FROM 'treal'} ]
+} [list xBestIndex {SELECT rowid, NULL, b, NULL FROM 'treal'} \
+        xFilter    {SELECT rowid, NULL, b, NULL FROM 'treal'} ]
 
 execsql {
   DROP TABLE t1;
   DROP TABLE treal;
 }
 
 #----------------------------------------------------------------------
 # Test cases vtab1-5 test SELECT queries that include joins on virtual 
 # tables.
 
@@ skipping 28 matching lines @@
   }
 } [list \
   1 red green 1 spades clubs     \
   1 red green 2 hearts diamonds  \
   2 blue black 1 spades clubs    \
   2 blue black 2 hearts diamonds \
 ]
 do_test vtab1-5-3 {
   filter $echo_module
 } [list \
-  xFilter {SELECT rowid, * FROM 't1'} \
-  xFilter {SELECT rowid, * FROM 't2'} \
-  xFilter {SELECT rowid, * FROM 't2'} \
+  xFilter {SELECT rowid, a, b, c FROM 't1'} \
+  xFilter {SELECT rowid, d, e, f FROM 't2'} \
+  xFilter {SELECT rowid, d, e, f FROM 't2'} \
 ]
 do_test vtab1-5-4 {
   set echo_module ""
   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-5 {
   filter $echo_module
 } [list \
-  xFilter {SELECT rowid, * FROM 't1'} \
-  xFilter {SELECT rowid, * FROM 't2'} \
-  xFilter {SELECT rowid, * FROM 't2'} \
+  xFilter {SELECT rowid, a, b, c FROM 't1'} \
+  xFilter {SELECT rowid, d, e, f FROM 't2'} \
+  xFilter {SELECT rowid, d, e, f FROM 't2'} \
 ]
 do_test vtab1-5-6 {
   execsql {
     CREATE INDEX i1 ON t2(d);
   }
 
   db close
   sqlite3 db test.db
   register_echo_module [sqlite3_connection_pointer db]
 
   set ::echo_module ""
   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 't2' WHERE d = ?} \
+  xFilter {SELECT rowid, a, b, c FROM 't1'}             \
+  xFilter {SELECT rowid, d, e, f FROM 't2' WHERE d = ?} \
+  xFilter {SELECT rowid, d, e, f FROM 't2' WHERE d = ?} \
 ]
 
 execsql {
   DROP TABLE t1;
   DROP TABLE t2;
   DROP TABLE et1;
   DROP TABLE et2;
 }
 
 #----------------------------------------------------------------------
@@ skipping 329 matching lines @@
   }}
 } {0}
 
 do_test vtab1.10-5 {
   set echo_module ""
   execsql {
     SELECT * FROM e WHERE rowid||'' MATCH 'pattern';
   }
   set echo_module
 } [list \
-  xBestIndex {SELECT rowid, * FROM 'r'} \
-  xFilter {SELECT rowid, * FROM 'r'}    \
+  xBestIndex {SELECT rowid, a, b, c FROM 'r'} \
+  xFilter {SELECT rowid, a, b, c FROM 'r'}    \
 ]
 proc match_func {args} {return ""}
 do_test vtab1.10-6 {
   set echo_module ""
   db function match match_func
   execsql {
     SELECT * FROM e WHERE match('pattern', rowid, 'pattern2');
   }
   set echo_module
 } [list \
-  xBestIndex {SELECT rowid, * FROM 'r'} \
-  xFilter {SELECT rowid, * FROM 'r'}    \
+  xBestIndex {SELECT rowid, a, b, c FROM 'r'} \
+  xFilter {SELECT rowid, a, b, c FROM 'r'}    \
 ]
 
 
 # Testing the xFindFunction interface
 #
 catch {rename ::echo_glob_overload {}}
 do_test vtab1.11-1 {
   execsql {
     INSERT INTO r(a,b,c) VALUES(1,'?',99);
     INSERT INTO r(a,b,c) VALUES(2,3,99);
@@ skipping 81 matching lines @@
     INSERT INTO c VALUES(NULL, 15, 16);
     SELECT * FROM echo_c WHERE a IS NULL 
   }
 } {{} 15 16}
 do_test vtab1.13-3 {
   execsql { 
     INSERT INTO c VALUES(15, NULL, 16);
     SELECT * FROM echo_c WHERE b IS NULL 
   }
 } {15 {} 16}
-do_test vtab1.13-3 {
+do_test vtab1.13-4 {
+  unset -nocomplain null
+  execsql { 
+    SELECT * FROM echo_c WHERE b IS $null
+  }
+} {15 {} 16}
+do_test vtab1.13-5 {
   execsql { 
     SELECT * FROM echo_c WHERE b IS NULL AND a = 15;
   }
 } {15 {} 16}
+do_test vtab1.13-6 {
+  execsql { 
+    SELECT * FROM echo_c WHERE NULL IS b AND a IS 15;
+  }
+} {15 {} 16}
 
 
 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)}
@@ skipping 35 matching lines @@
 #  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]
+} [list xBestIndex {SELECT rowid, a, b, c FROM 'c' WHERE rowid = ?} \
+        xFilter {SELECT rowid, a, b, c 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]
+} [list xBestIndex {SELECT rowid, a, b, c FROM 'c' WHERE a = ?} \
+        xFilter {SELECT rowid, a, b, c 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
 #} {/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);
@@ skipping 120 matching lines @@
   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}
+    {xFilter {SELECT rowid, a, b 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}
+    {xFilter {SELECT rowid, a, b 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'}}
+    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} J%}
 
   1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4}
-    {xFilter {SELECT rowid, * FROM 't6'}}
+    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} j%}
 } {
   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'}}
+    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} J%}
 
   2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {}
-    {xFilter {SELECT rowid, * FROM 't6'}}
+    {xFilter {SELECT rowid, a, b FROM 't6' WHERE b like ?} j%}
 } {
   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.
 #
@@ skipping 60 matching lines @@
 }
 
 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}
 
+#-------------------------------------------------------------------------
+# At one point executing a CREATE VIRTUAL TABLE statement that specified 
+# a database name but no virtual table arguments was causing an internal
+# buffer overread. Valgrind would report errors while running the following 
+# tests. Specifically:
+#
+#   CREATE VIRTUAL TABLE t1 USING fts4;          -- Ok - no db name.
+#   CREATE VIRTUAL TABLE main.t1 USING fts4(x);  -- Ok - has vtab arguments.
+#   CREATE VIRTUAL TABLE main.t1 USING fts4;     -- Had the problem. 
+#
+ifcapable fts3 {
+  forcedelete test.db2
+  set nm [string repeat abcdefghij 100]
+  do_execsql_test 22.1 {
+    ATTACH 'test.db2' AS $nm
+  }
+  
+  execsql "SELECT * FROM sqlite_master"
+  do_execsql_test 22.2 "CREATE VIRTUAL TABLE ${nm}.t1 USING fts4"
+  
+  do_test 22.3.1 {
+    set sql "CREATE VIRTUAL TABLE ${nm}.t2 USING fts4"
+    set stmt [sqlite3_prepare_v2 db $sql -1 dummy]
+    sqlite3_step $stmt
+  } {SQLITE_DONE}
+  
+  do_test 22.3.2 {
+    sqlite3_finalize $stmt
+  } {SQLITE_OK}
+  
+  do_test 22.4.1 {
+    set sql "CREATE VIRTUAL TABLE ${nm}.t3 USING fts4"
+    set n [string length $sql]
+    set stmt [sqlite3_prepare db "${sql}xyz" $n dummy]
+    sqlite3_step $stmt
+  } {SQLITE_DONE}
+  
+  do_test 22.4.2 {
+    sqlite3_finalize $stmt
+  } {SQLITE_OK}
+}
+
+
+#-------------------------------------------------------------------------
+# The following tests verify that a DROP TABLE command on a virtual
+# table does not cause other operations to crash.
+#
+#   23.1: Dropping a vtab while a SELECT is running on it.
+#
+#   23.2: Dropping a vtab while a SELECT that will, but has not yet,
+#         open a cursor on the vtab, is running. In this case the
+#         DROP TABLE succeeds and the SELECT hits an error.
+#   
+#   23.3: Dropping a vtab from within a user-defined-function callback
+#         in the middle of an "INSERT INTO vtab SELECT ..." statement.
+#
+reset_db
+load_static_extension db wholenumber
+load_static_extension db eval
+register_echo_module db
+
+do_test 23.1 {
+  execsql { CREATE VIRTUAL TABLE t1 USING wholenumber }
+  set res ""
+  db eval { SELECT value FROM t1 WHERE value<10 } {
+    if {$value == 5} {
+      set res [catchsql { DROP TABLE t1 }]
+    }
+  }
+  set res
+} {1 {database table is locked}}
+
+do_test 23.2 {
+  execsql { 
+    CREATE TABLE t2(value);
+    INSERT INTO t2 VALUES(1), (2), (3);
+  }
+
+  set res2 [list [catch {
+    db eval {
+      SELECT value FROM t2 UNION ALL 
+      SELECT value FROM t1 WHERE value<10
+    } {
+      if {$value == 2} { set res1 [catchsql { DROP TABLE t1 }] }
+    }
+  } msg] $msg]
+  list $res1 $res2
+} {{0 {}} {1 {database table is locked}}}
+
+do_test 23.3.1 {
+  execsql { CREATE VIRTUAL TABLE t1e USING echo(t2) }
+  execsql { INSERT INTO t1e SELECT 4 }
+  catchsql { INSERT INTO t1e SELECT eval('DROP TABLE t1e') }
+} {1 {database table is locked}}
+do_execsql_test 23.3.2 { SELECT * FROM t1e } {1 2 3 4}
+
+#-------------------------------------------------------------------------
+# At one point SQL like this:
+#
+#   SAVEPOINT xyz;       -- Opens SQL transaction
+#     INSERT INTO vtab   -- Write to virtual table
+#     ROLLBACK TO xyz;
+#   RELEASE xyz;
+#
+# was not invoking the xRollbackTo() callback for the ROLLBACK TO 
+# operation. Which meant that virtual tables like FTS3 would incorrectly 
+# commit the results of the INSERT as part of the "RELEASE xyz" command.
+#
+# The following tests check that this has been fixed.
+#
+ifcapable fts3 {
+  do_execsql_test 24.0 {
+    CREATE VIRTUAL TABLE t4 USING fts3();
+    SAVEPOINT a;
+    INSERT INTO t4 VALUES('a b c');
+    ROLLBACK TO a;
+    RELEASE a;
+    SELECT * FROM t4;
+  } {}
+
+  do_execsql_test 24.1 { SELECT * FROM t4 WHERE t4 MATCH 'b' } {}
+  do_execsql_test 24.2 { INSERT INTO t4(t4) VALUES('integrity-check') } {}
+
+  do_execsql_test 24.3 {
+    SAVEPOINT a;
+    CREATE VIRTUAL TABLE t5 USING fts3();
+    SAVEPOINT b;
+    ROLLBACK TO a;
+    SAVEPOINT c;
+    RELEASE a;
+  }
+}
+
 finish_test