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