Move bundled copy of sqlite one level deeper to better separate it...

third_party/sqlite/test/misc4.test

-# 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;
-    }
-  } {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}
-
-finish_test