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.23 2007/12/08 18:01:31 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  set DB [sqlite3_connection_pointer db]
26  execsql {
27    CREATE TABLE t1(x);
28    INSERT INTO t1 VALUES(1);
29  }
30} {}
31
32ifcapable tempdb {
33  do_test misc4-1.2 {
34    set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
35    set stmt [sqlite3_prepare $DB $sql -1 TAIL]
36    execsql {
37      BEGIN;
38      CREATE TABLE t3(a,b,c);
39      INSERT INTO t1 SELECT * FROM t1;
40      ROLLBACK;
41    }
42  } {}
43
44  # Because the previous transaction included a DDL statement and
45  # was rolled back, statement $stmt was marked as expired. Executing it
46  # now returns SQLITE_SCHEMA.
47  do_test misc4-1.2.1 {
48    list [sqlite3_step $stmt] [sqlite3_finalize $stmt]
49  } {SQLITE_ERROR SQLITE_SCHEMA}
50  do_test misc4-1.2.2 {
51    set stmt [sqlite3_prepare $DB $sql -1 TAIL]
52    set TAIL
53  } {}
54
55  do_test misc4-1.3 {
56    sqlite3_step $stmt
57  } SQLITE_DONE
58  do_test misc4-1.4 {
59    execsql {
60      SELECT * FROM temp.t2;
61    }
62  } {1}
63
64  # Drop the temporary table, then rerun the prepared  statement to
65  # recreate it again.  This recreates ticket #807.
66  #
67  do_test misc4-1.5 {
68    execsql {DROP TABLE t2}
69    sqlite3_reset $stmt
70    sqlite3_step $stmt
71  } {SQLITE_ERROR}
72  do_test misc4-1.6 {
73    sqlite3_finalize $stmt
74  } {SQLITE_SCHEMA}
75}
76
77# Prepare but do not execute various CREATE statements.  Then before
78# those statements are executed, try to use the tables, indices, views,
79# are triggers that were created.
80#
81do_test misc4-2.1 {
82  set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]
83  catchsql {
84    INSERT INTO t3 VALUES(1);
85  }
86} {1 {no such table: t3}}
87do_test misc4-2.2 {
88  sqlite3_step $stmt
89} SQLITE_DONE
90do_test misc4-2.3 {
91  sqlite3_finalize $stmt
92} SQLITE_OK
93do_test misc4-2.4 {
94  catchsql {
95    INSERT INTO t3 VALUES(1);
96  }
97} {0 {}}
98
99# Ticket #966
100#
101do_test misc4-3.1 {
102  execsql {
103    CREATE TABLE Table1(ID integer primary key, Value TEXT);
104    INSERT INTO Table1 VALUES(1, 'x');
105    CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
106    INSERT INTO Table2 VALUES(1, 'z');
107    INSERT INTO Table2 VALUES (1, 'a');
108  }
109  catchsql {
110    SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2 ORDER BY 1, 2;
111  }
112} {1 {aggregate functions are not allowed in the GROUP BY clause}}
113ifcapable compound {
114  do_test misc4-3.2 {
115    execsql {
116      SELECT ID, Value FROM Table1
117         UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
118      ORDER BY 1, 2;
119    }
120  } {1 x 1 z}
121  do_test misc4-3.3 {
122    catchsql {
123      SELECT ID, Value FROM Table1
124         UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
125      ORDER BY 1, 2;
126    }
127  } {1 {aggregate functions are not allowed in the GROUP BY clause}}
128  do_test misc4-3.4 {
129    catchsql {
130      SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
131         UNION SELECT ID, Value FROM Table1
132      ORDER BY 1, 2;
133    }
134  } {1 {aggregate functions are not allowed in the GROUP BY clause}}
135} ;# ifcapable compound
136
137# Ticket #1047.  Make sure column types are preserved in subqueries.
138#
139ifcapable subquery {
140  do_test misc4-4.1 {
141    execsql {
142      create table a(key varchar, data varchar);
143      create table b(key varchar, period integer);
144      insert into a values('01','data01');
145      insert into a values('+1','data+1');
146
147      insert into b values ('01',1);
148      insert into b values ('01',2);
149      insert into b values ('+1',3);
150      insert into b values ('+1',4);
151
152      select a.*, x.*
153        from a, (select key,sum(period) from b group by key) as x
154        where a.key=x.key order by 1 desc;
155    }
156  } {01 data01 01 3 +1 data+1 +1 7}
157
158  # This test case tests the same property as misc4-4.1, but it is
159  # a bit smaller which makes it easier to work with while debugging.
160  do_test misc4-4.2 {
161    execsql {
162      CREATE TABLE ab(a TEXT, b TEXT);
163      INSERT INTO ab VALUES('01', '1');
164    }
165    execsql {
166      select * from ab, (select b from ab) as x where x.b = ab.a;
167    }
168  } {}
169}
170
171
172# Ticket #1036.  When creating tables from a SELECT on a view, use the
173# short names of columns.
174#
175ifcapable view {
176  do_test misc4-5.1 {
177    execsql {
178      create table t4(a,b);
179      create table t5(a,c);
180      insert into t4 values (1,2);
181      insert into t5 values (1,3);
182      create view myview as select t4.a a from t4 inner join t5 on t4.a=t5.a;
183      create table problem as select * from myview;
184    }
185    execsql2 {
186      select * FROM problem;
187    }
188  } {a 1}
189  do_test misc4-5.2 {
190    execsql2 {
191      create table t6 as select * from t4, t5;
192      select * from t6;
193    }
194  } {a 1 b 2 a:1 1 c 3}
195}
196
197# Ticket #1086
198do_test misc4-6.1 {
199  execsql {
200    CREATE TABLE abc(a);
201    INSERT INTO abc VALUES(1);
202    CREATE TABLE def(d, e, f, PRIMARY KEY(d, e));
203  }
204} {}
205do_test misc4-6.2 {
206  execsql {
207    SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);
208  }
209} {1}
210
211finish_test
212