xref: /sqlite-3.40.0/test/misc4.test (revision 3d1d95e6) 
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.20 2005/09/08 10:37:01 drh 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} {}
32
33ifcapable tempdb {
34  do_test misc4-1.2 {
35    set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
36    set stmt [sqlite3_prepare $DB $sql -1 TAIL]
37    execsql {
38      BEGIN;
39      CREATE TABLE t3(a,b,c);
40      INSERT INTO t1 SELECT * FROM t1;
41      ROLLBACK;
42    }
43  } {}
44  do_test misc4-1.3 {
45    sqlite3_step $stmt
46  } SQLITE_DONE
47  do_test misc4-1.4 {
48    execsql {
49      SELECT * FROM temp.t2;
50    }
51  } {1}
52
53  # Drop the temporary table, then rerun the prepared  statement to
54  # recreate it again.  This recreates ticket #807.
55  #
56  do_test misc4-1.5 {
57    execsql {DROP TABLE t2}
58    sqlite3_reset $stmt
59    sqlite3_step $stmt
60  } {SQLITE_ERROR}
61  do_test misc4-1.6 {
62    sqlite3_finalize $stmt
63  } {SQLITE_SCHEMA}
64}
65
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.
69#
70do_test misc4-2.1 {
71  set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]
72  catchsql {
73    INSERT INTO t3 VALUES(1);
74  }
75} {1 {no such table: t3}}
76do_test misc4-2.2 {
77  sqlite3_step $stmt
78} SQLITE_DONE
79do_test misc4-2.3 {
80  sqlite3_finalize $stmt
81} SQLITE_OK
82do_test misc4-2.4 {
83  catchsql {
84    INSERT INTO t3 VALUES(1);
85  }
86} {0 {}}
87
88# Ticket #966
89#
90ifcapable compound {
91do_test misc4-3.1 {
92  execsql {
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
100    ORDER BY 1, 2;
101  }
102} {1 x 1 z}
103do_test misc4-3.2 {
104  catchsql {
105    SELECT ID, Value FROM Table1
106       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
107    ORDER BY 1, 2;
108  }
109} {1 {aggregate functions are not allowed in the GROUP BY clause}}
110} ;# ifcapable compound
111
112# Ticket #1047.  Make sure column types are preserved in subqueries.
113#
114ifcapable subquery {
115  do_test misc4-4.1 {
116    execsql {
117      create table a(key varchar, data varchar);
118      create table b(key varchar, period integer);
119      insert into a values('01','data01');
120      insert into a values('+1','data+1');
121
122      insert into b values ('01',1);
123      insert into b values ('01',2);
124      insert into b values ('+1',3);
125      insert into b values ('+1',4);
126
127      select a.*, x.*
128        from a, (select key,sum(period) from b group by key) as x
129        where a.key=x.key;
130    }
131  } {01 data01 01 3 +1 data+1 +1 7}
132
133  # This test case tests the same property as misc4-4.1, but it is
134  # a bit smaller which makes it easier to work with while debugging.
135  do_test misc4-4.2 {
136    execsql {
137      CREATE TABLE ab(a TEXT, b TEXT);
138      INSERT INTO ab VALUES('01', '1');
139    }
140    execsql {
141      select * from ab, (select b from ab) as x where x.b = ab.a;
142    }
143  } {}
144}
145
146
147# Ticket #1036.  When creating tables from a SELECT on a view, use the
148# short names of columns.
149#
150ifcapable view {
151  do_test misc4-5.1 {
152    execsql {
153      create table t4(a,b);
154      create table t5(a,c);
155      insert into t4 values (1,2);
156      insert into t5 values (1,3);
157      create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
158      create table problem as select * from myview;
159    }
160    execsql2 {
161      select * FROM problem;
162    }
163  } {a 1}
164  do_test misc4-5.2 {
165    execsql2 {
166      create table t6 as select * from t4, t5;
167      select * from t6;
168    }
169  } {a 1 b 2 a:1 1 c 3}
170}
171
172# Ticket #1086
173do_test misc4-6.1 {
174  execsql {
175    CREATE TABLE abc(a);
176    INSERT INTO abc VALUES(1);
177    CREATE TABLE def(d, e, f, PRIMARY KEY(d, e));
178  }
179} {}
180do_test misc4-6.2 {
181  execsql {
182    SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);
183  }
184} {1}
185
186finish_test
187