sl@0: # 2004 Jun 27
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.
sl@0: #
sl@0: # This file implements tests for miscellanous features that were
sl@0: # left out of other test files.
sl@0: #
sl@0: # $Id: misc4.test,v 1.23 2007/12/08 18:01:31 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # Prepare a statement that will create a temporary table.  Then do
sl@0: # a rollback.  Then try to execute the prepared statement.
sl@0: #
sl@0: do_test misc4-1.1 {
sl@0:   set DB [sqlite3_connection_pointer db]
sl@0:   execsql {
sl@0:     CREATE TABLE t1(x);
sl@0:     INSERT INTO t1 VALUES(1);
sl@0:   }
sl@0: } {}
sl@0: 
sl@0: ifcapable tempdb {
sl@0:   do_test misc4-1.2 {
sl@0:     set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
sl@0:     set stmt [sqlite3_prepare $DB $sql -1 TAIL]
sl@0:     execsql {
sl@0:       BEGIN;
sl@0:       CREATE TABLE t3(a,b,c);
sl@0:       INSERT INTO t1 SELECT * FROM t1;
sl@0:       ROLLBACK;
sl@0:     }
sl@0:   } {}
sl@0: 
sl@0:   # Because the previous transaction included a DDL statement and
sl@0:   # was rolled back, statement $stmt was marked as expired. Executing it
sl@0:   # now returns SQLITE_SCHEMA.
sl@0:   do_test misc4-1.2.1 {
sl@0:     list [sqlite3_step $stmt] [sqlite3_finalize $stmt]
sl@0:   } {SQLITE_ERROR SQLITE_SCHEMA}
sl@0:   do_test misc4-1.2.2 {
sl@0:     set stmt [sqlite3_prepare $DB $sql -1 TAIL]
sl@0:     set TAIL
sl@0:   } {}
sl@0: 
sl@0:   do_test misc4-1.3 {
sl@0:     sqlite3_step $stmt
sl@0:   } SQLITE_DONE
sl@0:   do_test misc4-1.4 {
sl@0:     execsql {
sl@0:       SELECT * FROM temp.t2;
sl@0:     }
sl@0:   } {1}
sl@0:   
sl@0:   # Drop the temporary table, then rerun the prepared  statement to
sl@0:   # recreate it again.  This recreates ticket #807.
sl@0:   #
sl@0:   do_test misc4-1.5 {
sl@0:     execsql {DROP TABLE t2}
sl@0:     sqlite3_reset $stmt
sl@0:     sqlite3_step $stmt
sl@0:   } {SQLITE_ERROR}
sl@0:   do_test misc4-1.6 {
sl@0:     sqlite3_finalize $stmt
sl@0:   } {SQLITE_SCHEMA}
sl@0: }
sl@0: 
sl@0: # Prepare but do not execute various CREATE statements.  Then before
sl@0: # those statements are executed, try to use the tables, indices, views,
sl@0: # are triggers that were created.
sl@0: #
sl@0: do_test misc4-2.1 {
sl@0:   set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]
sl@0:   catchsql {
sl@0:     INSERT INTO t3 VALUES(1);
sl@0:   }
sl@0: } {1 {no such table: t3}}
sl@0: do_test misc4-2.2 {
sl@0:   sqlite3_step $stmt
sl@0: } SQLITE_DONE
sl@0: do_test misc4-2.3 {
sl@0:   sqlite3_finalize $stmt
sl@0: } SQLITE_OK
sl@0: do_test misc4-2.4 {
sl@0:   catchsql {
sl@0:     INSERT INTO t3 VALUES(1);
sl@0:   }
sl@0: } {0 {}}
sl@0: 
sl@0: # Ticket #966
sl@0: #
sl@0: do_test misc4-3.1 {
sl@0:   execsql { 
sl@0:     CREATE TABLE Table1(ID integer primary key, Value TEXT);
sl@0:     INSERT INTO Table1 VALUES(1, 'x');
sl@0:     CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
sl@0:     INSERT INTO Table2 VALUES(1, 'z');
sl@0:     INSERT INTO Table2 VALUES (1, 'a');
sl@0:   }
sl@0:   catchsql { 
sl@0:     SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2 ORDER BY 1, 2;
sl@0:   }
sl@0: } {1 {aggregate functions are not allowed in the GROUP BY clause}}
sl@0: ifcapable compound {
sl@0:   do_test misc4-3.2 {
sl@0:     execsql {
sl@0:       SELECT ID, Value FROM Table1
sl@0:          UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
sl@0:       ORDER BY 1, 2;
sl@0:     }
sl@0:   } {1 x 1 z}
sl@0:   do_test misc4-3.3 {
sl@0:     catchsql { 
sl@0:       SELECT ID, Value FROM Table1
sl@0:          UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
sl@0:       ORDER BY 1, 2;
sl@0:     }
sl@0:   } {1 {aggregate functions are not allowed in the GROUP BY clause}}
sl@0:   do_test misc4-3.4 {
sl@0:     catchsql { 
sl@0:       SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
sl@0:          UNION SELECT ID, Value FROM Table1
sl@0:       ORDER BY 1, 2;
sl@0:     }
sl@0:   } {1 {aggregate functions are not allowed in the GROUP BY clause}}
sl@0: } ;# ifcapable compound
sl@0: 
sl@0: # Ticket #1047.  Make sure column types are preserved in subqueries.
sl@0: #
sl@0: ifcapable subquery {
sl@0:   do_test misc4-4.1 {
sl@0:     execsql {
sl@0:       create table a(key varchar, data varchar);
sl@0:       create table b(key varchar, period integer);
sl@0:       insert into a values('01','data01');
sl@0:       insert into a values('+1','data+1');
sl@0:       
sl@0:       insert into b values ('01',1);
sl@0:       insert into b values ('01',2);
sl@0:       insert into b values ('+1',3);
sl@0:       insert into b values ('+1',4);
sl@0:       
sl@0:       select a.*, x.*
sl@0:         from a, (select key,sum(period) from b group by key) as x
sl@0:         where a.key=x.key;
sl@0:     }
sl@0:   } {01 data01 01 3 +1 data+1 +1 7}
sl@0: 
sl@0:   # This test case tests the same property as misc4-4.1, but it is
sl@0:   # a bit smaller which makes it easier to work with while debugging.
sl@0:   do_test misc4-4.2 {
sl@0:     execsql {
sl@0:       CREATE TABLE ab(a TEXT, b TEXT);
sl@0:       INSERT INTO ab VALUES('01', '1');
sl@0:     }
sl@0:     execsql {
sl@0:       select * from ab, (select b from ab) as x where x.b = ab.a;
sl@0:     }
sl@0:   } {}
sl@0: }
sl@0: 
sl@0: 
sl@0: # Ticket #1036.  When creating tables from a SELECT on a view, use the
sl@0: # short names of columns.
sl@0: #
sl@0: ifcapable view {
sl@0:   do_test misc4-5.1 {
sl@0:     execsql {
sl@0:       create table t4(a,b);
sl@0:       create table t5(a,c);
sl@0:       insert into t4 values (1,2);
sl@0:       insert into t5 values (1,3);
sl@0:       create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
sl@0:       create table problem as select * from myview; 
sl@0:     }
sl@0:     execsql2 {
sl@0:       select * FROM problem;
sl@0:     }
sl@0:   } {a 1}
sl@0:   do_test misc4-5.2 {
sl@0:     execsql2 {
sl@0:       create table t6 as select * from t4, t5;
sl@0:       select * from t6;
sl@0:     }
sl@0:   } {a 1 b 2 a:1 1 c 3}
sl@0: }
sl@0: 
sl@0: # Ticket #1086
sl@0: do_test misc4-6.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE abc(a);
sl@0:     INSERT INTO abc VALUES(1);
sl@0:     CREATE TABLE def(d, e, f, PRIMARY KEY(d, e));
sl@0:   }
sl@0: } {}
sl@0: do_test misc4-6.2 {
sl@0:   execsql {
sl@0:     SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);
sl@0:   }
sl@0: } {1}
sl@0: 
sl@0: finish_test