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