xref: /sqlite-3.40.0/test/misc4.test (revision b3bce662) 
1# 2004 Jun 27
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
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.
9#
10#***********************************************************************
11# This file implements regression tests for SQLite library.
12#
13# This file implements tests for miscellanous features that were
14# left out of other test files.
15#
16# $Id: misc4.test,v 1.14 2005/01/29 08:32:46 danielk1977 Exp $
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21# Prepare a statement that will create a temporary table.  Then do
22# a rollback.  Then try to execute the prepared statement.
23#
24do_test misc4-1.1 {
25  db close
26  set DB [sqlite3 db test.db]
27  execsql {
28    CREATE TABLE t1(x);
29    INSERT INTO t1 VALUES(1);
30  }
31} {}
32do_test misc4-1.2 {
33  set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
34  set stmt [sqlite3_prepare $DB $sql -1 TAIL]
35  execsql {
36    BEGIN;
37    CREATE TABLE t3(a,b,c);
38    INSERT INTO t1 SELECT * FROM t1;
39    ROLLBACK;
40  }
41} {}
42do_test misc4-1.3 {
43  sqlite3_step $stmt
44} SQLITE_DONE
45do_test misc4-1.4 {
46  execsql {
47    SELECT * FROM temp.t2;
48  }
49} {1}
50
51# Drop the temporary table, then rerun the prepared  statement to
52# recreate it again.  This recreates ticket #807.
53#
54do_test misc4-1.5 {
55  execsql {DROP TABLE t2}
56  sqlite3_reset $stmt
57  sqlite3_step $stmt
58} {SQLITE_ERROR}
59do_test misc4-1.6 {
60  sqlite3_finalize $stmt
61} {SQLITE_SCHEMA}
62
63# Prepare but do not execute various CREATE statements.  Then before
64# those statements are executed, try to use the tables, indices, views,
65# are triggers that were created.
66#
67do_test misc4-2.1 {
68  set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]
69  catchsql {
70    INSERT INTO t3 VALUES(1);
71  }
72} {1 {no such table: t3}}
73do_test misc4-2.2 {
74  sqlite3_step $stmt
75} SQLITE_DONE
76do_test misc4-2.3 {
77  sqlite3_finalize $stmt
78} SQLITE_OK
79do_test misc4-2.4 {
80  catchsql {
81    INSERT INTO t3 VALUES(1);
82  }
83} {0 {}}
84
85# Ticket #966
86#
87ifcapable compound {
88do_test misc4-3.1 {
89  execsql {
90    CREATE TABLE Table1(ID integer primary key, Value TEXT);
91    INSERT INTO Table1 VALUES(1, 'x');
92    CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
93    INSERT INTO Table2 VALUES(1, 'z');
94    INSERT INTO Table2 VALUES (1, 'a');
95    SELECT ID, Value FROM Table1
96       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1,2
97    ORDER BY 1, 2;
98  }
99} {{} {} 1 x 1 z}
100} ;# ifcapable compound
101
102# Ticket #1047.  Make sure column types are preserved in subqueries.
103#
104ifcapable subquery {
105  do_test misc4-4.1 {
106    execsql {
107      create table a(key varchar, data varchar);
108      create table b(key varchar, period integer);
109      insert into a values('01','data01');
110      insert into a values('+1','data+1');
111
112      insert into b values ('01',1);
113      insert into b values ('01',2);
114      insert into b values ('+1',3);
115      insert into b values ('+1',4);
116
117      select a.*, x.*
118        from a, (select key,sum(period) from b group by key) as x
119        where a.key=x.key;
120    }
121  } {01 data01 01 3.0 +1 data+1 +1 7.0}
122
123  # This test case tests the same property as misc4-4.1, but it is
124  # a bit smaller which makes it easier to work with while debugging.
125  do_test misc4-4.2 {
126    execsql {
127      CREATE TABLE ab(a TEXT, b TEXT);
128      INSERT INTO ab VALUES('01', '1');
129    }
130    execsql {
131      select * from ab, (select b from ab) as x where x.b = ab.a;
132    }
133  } {}
134}
135
136
137# Ticket #1036.  When creating tables from a SELECT on a view, use the
138# short names of columns.
139#
140ifcapable view {
141  do_test misc4-5.1 {
142    execsql {
143      create table t4(a,b);
144      create table t5(a,c);
145      insert into t4 values (1,2);
146      insert into t5 values (1,3);
147      create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
148      create table problem as select * from myview;
149    }
150    execsql2 {
151      select * FROM problem;
152    }
153  } {a 1}
154  do_test misc4-5.2 {
155    execsql2 {
156      create table t6 as select * from t4, t5;
157      select * from t6;
158    }
159  } {a 1 b 2 a:1 1 c 3}
160}
161
162finish_test
163