# 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