3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
13 # This file implements tests for miscellanous features that were
14 # left out of other test files.
16 # $Id: misc4.test,v 1.16 2005/03/29 03:11:00 danielk1977 Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
21 # Prepare a statement that will create a temporary table. Then do
22 # a rollback. Then try to execute the prepared statement.
26 set DB [sqlite3 db test.db]
29 INSERT INTO t1 VALUES(1);
35 set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
36 set stmt [sqlite3_prepare $DB $sql -1 TAIL]
39 CREATE TABLE t3(a,b,c);
40 INSERT INTO t1 SELECT * FROM t1;
49 SELECT * FROM temp.t2;
53 # Drop the temporary table, then rerun the prepared statement to
54 # recreate it again. This recreates ticket #807.
57 execsql {DROP TABLE t2}
62 sqlite3_finalize $stmt
66 # Prepare but do not execute various CREATE statements. Then before
67 # those statements are executed, try to use the tables, indices, views,
68 # are triggers that were created.
71 set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]
73 INSERT INTO t3 VALUES(1);
75 } {1 {no such table: t3}}
80 sqlite3_finalize $stmt
84 INSERT INTO t3 VALUES(1);
93 CREATE TABLE Table1(ID integer primary key, Value TEXT);
94 INSERT INTO Table1 VALUES(1, 'x');
95 CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
96 INSERT INTO Table2 VALUES(1, 'z');
97 INSERT INTO Table2 VALUES (1, 'a');
98 SELECT ID, Value FROM Table1
99 UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1,2
103 } ;# ifcapable compound
105 # Ticket #1047. Make sure column types are preserved in subqueries.
110 create table a(key varchar, data varchar);
111 create table b(key varchar, period integer);
112 insert into a values('01','data01');
113 insert into a values('+1','data+1');
115 insert into b values ('01',1);
116 insert into b values ('01',2);
117 insert into b values ('+1',3);
118 insert into b values ('+1',4);
121 from a, (select key,sum(period) from b group by key) as x
124 } {01 data01 01 3.0 +1 data+1 +1 7.0}
126 # This test case tests the same property as misc4-4.1, but it is
127 # a bit smaller which makes it easier to work with while debugging.
130 CREATE TABLE ab(a TEXT, b TEXT);
131 INSERT INTO ab VALUES('01', '1');
134 select * from ab, (select b from ab) as x where x.b = ab.a;
140 # Ticket #1036. When creating tables from a SELECT on a view, use the
141 # short names of columns.
146 create table t4(a,b);
147 create table t5(a,c);
148 insert into t4 values (1,2);
149 insert into t5 values (1,3);
150 create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
151 create table problem as select * from myview;
154 select * FROM problem;
159 create table t6 as select * from t4, t5;
162 } {a 1 b 2 a:1 1 c 3}
169 INSERT INTO abc VALUES(1);
170 CREATE TABLE def(d, e, f, PRIMARY KEY(d, e));
175 SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);