# 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