Adding upstream version 3.40.1.upstream/3.40.1upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 233 insertions, 0 deletions

diff --git a/test/misc4.test b/test/misc4.test
new file mode 100644
index 0000000..3d39188
--- /dev/null
+++ b/test/misc4.test
@@ -0,0 +1,233 @@
+# 2004 Jun 27
+#
+# 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.
+#
+# This file implements tests for miscellanous features that were
+# left out of other test files.
+#
+# $Id: misc4.test,v 1.23 2007/12/08 18:01:31 drh Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Prepare a statement that will create a temporary table.  Then do
+# a rollback.  Then try to execute the prepared statement.
+#
+do_test misc4-1.1 {
+  set DB [sqlite3_connection_pointer db]
+  execsql {
+    CREATE TABLE t1(x);
+    INSERT INTO t1 VALUES(1);
+  }
+} {}
+
+ifcapable tempdb {
+  do_test misc4-1.2 {
+    set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
+    set stmt [sqlite3_prepare $DB $sql -1 TAIL]
+    execsql {
+      BEGIN;
+      CREATE TABLE t3(a,b,c);
+      INSERT INTO t1 SELECT * FROM t1;
+      ROLLBACK;
+    }
+  } {}
+
+  # Because the previous transaction included a DDL statement and
+  # was rolled back, statement $stmt was marked as expired. Executing it
+  # now returns SQLITE_SCHEMA.
+  do_test misc4-1.2.1 {
+    list [sqlite3_step $stmt] [sqlite3_finalize $stmt]
+  } {SQLITE_ERROR SQLITE_SCHEMA}
+  do_test misc4-1.2.2 {
+    set stmt [sqlite3_prepare $DB $sql -1 TAIL]
+    set TAIL
+  } {}
+
+  do_test misc4-1.3 {
+    sqlite3_step $stmt
+  } SQLITE_DONE
+  do_test misc4-1.4 {
+    execsql {
+      SELECT * FROM temp.t2;
+    }
+  } {1}
+  
+  # Drop the temporary table, then rerun the prepared  statement to
+  # recreate it again.  This recreates ticket #807.
+  #
+  do_test misc4-1.5 {
+    execsql {DROP TABLE t2}
+    sqlite3_reset $stmt
+    sqlite3_step $stmt
+  } {SQLITE_ERROR}
+  do_test misc4-1.6 {
+    sqlite3_finalize $stmt
+  } {SQLITE_SCHEMA}
+}
+
+# Prepare but do not execute various CREATE statements.  Then before
+# those statements are executed, try to use the tables, indices, views,
+# are triggers that were created.
+#
+do_test misc4-2.1 {
+  set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]
+  catchsql {
+    INSERT INTO t3 VALUES(1);
+  }
+} {1 {no such table: t3}}
+do_test misc4-2.2 {
+  sqlite3_step $stmt
+} SQLITE_DONE
+do_test misc4-2.3 {
+  sqlite3_finalize $stmt
+} SQLITE_OK
+do_test misc4-2.4 {
+  catchsql {
+    INSERT INTO t3 VALUES(1);
+  }
+} {0 {}}
+
+# Ticket #966
+#
+do_test misc4-3.1 {
+  execsql { 
+    CREATE TABLE Table1(ID integer primary key, Value TEXT);
+    INSERT INTO Table1 VALUES(1, 'x');
+    CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
+    INSERT INTO Table2 VALUES(1, 'z');
+    INSERT INTO Table2 VALUES (1, 'a');
+  }
+  catchsql { 
+    SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2 ORDER BY 1, 2;
+  }
+} {1 {aggregate functions are not allowed in the GROUP BY clause}}
+ifcapable compound {
+  do_test misc4-3.2 {
+    execsql {
+      SELECT ID, Value FROM Table1
+         UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
+      ORDER BY 1, 2;
+    }
+  } {1 x 1 z}
+  do_test misc4-3.3 {
+    catchsql { 
+      SELECT ID, Value FROM Table1
+         UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
+      ORDER BY 1, 2;
+    }
+  } {1 {aggregate functions are not allowed in the GROUP BY clause}}
+  do_test misc4-3.4 {
+    catchsql { 
+      SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
+         UNION SELECT ID, Value FROM Table1
+      ORDER BY 1, 2;
+    }
+  } {1 {aggregate functions are not allowed in the GROUP BY clause}}
+} ;# ifcapable compound
+
+# Ticket #1047.  Make sure column types are preserved in subqueries.
+#
+ifcapable subquery {
+  do_test misc4-4.1 {
+    execsql {
+      create table a(key varchar, data varchar);
+      create table b(key varchar, period integer);
+      insert into a values('01','data01');
+      insert into a values('+1','data+1');
+      
+      insert into b values ('01',1);
+      insert into b values ('01',2);
+      insert into b values ('+1',3);
+      insert into b values ('+1',4);
+      
+      select a.*, x.*
+        from a, (select key,sum(period) from b group by key) as x
+        where a.key=x.key order by 1 desc;
+    }
+  } {01 data01 01 3 +1 data+1 +1 7}
+
+  # This test case tests the same property as misc4-4.1, but it is
+  # a bit smaller which makes it easier to work with while debugging.
+  do_test misc4-4.2 {
+    execsql {
+      CREATE TABLE ab(a TEXT, b TEXT);
+      INSERT INTO ab VALUES('01', '1');
+    }
+    execsql {
+      select * from ab, (select b from ab) as x where x.b = ab.a;
+    }
+  } {}
+}
+
+
+# Ticket #1036.  When creating tables from a SELECT on a view, use the
+# short names of columns.
+#
+ifcapable view {
+  do_test misc4-5.1 {
+    execsql {
+      create table t4(a,b);
+      create table t5(a,c);
+      insert into t4 values (1,2);
+      insert into t5 values (1,3);
+      create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
+      create table problem as select * from myview; 
+    }
+    execsql2 {
+      select * FROM problem;
+    }
+  } {a 1}
+  do_test misc4-5.2 {
+    execsql2 {
+      create table t6 as select * from t4, t5;
+      select * from t6;
+    }
+  } {a 1 b 2 a:1 1 c 3}
+}
+
+# Ticket #1086
+do_test misc4-6.1 {
+  execsql {
+    CREATE TABLE abc(a);
+    INSERT INTO abc VALUES(1);
+    CREATE TABLE def(d, e, f, PRIMARY KEY(d, e));
+  }
+} {}
+do_test misc4-6.2 {
+  execsql {
+    SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);
+  }
+} {1}
+
+# 2015-05-15.  Error message formatting problem.
+#
+db close
+sqlite3 db :memory:
+sqlite3_db_config db DEFENSIVE 0
+do_catchsql_test misc4-7.1 {
+  CREATE TABLE t7(x);
+  PRAGMA writable_schema=ON;
+  UPDATE sqlite_master SET sql='CREATE TABLE [M%s%s%s%s%s%s%s%s%s%s%s%s%s';
+  VACUUM;
+} {1 {unrecognized token: "[M%s%s%s%s%s%s%s%s%s%s%s%s%s"}}
+
+# 2015-05-18.  Use of ephermeral Mem content after the cursor that holds
+# the canonical content has moved on.
+#
+do_execsql_test misc4-7.2 {
+  CREATE TABLE t0(a,b);
+  INSERT INTO t0 VALUES(1,0),(2,0);
+  UPDATE t0 SET b=9 WHERE a AND (SELECT a FROM t0 WHERE a);
+  SELECT * FROM t0 ORDER BY +a;
+} {1 9 2 9}
+
+finish_test