wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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  
18 set testdir [file dirname $argv0]
19 source $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 #
24 do_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  
32 ifcapable 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 #
81 do_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}}
87 do_test misc4-2.2 {
88 sqlite3_step $stmt
89 } SQLITE_DONE
90 do_test misc4-2.3 {
91 sqlite3_finalize $stmt
92 } SQLITE_OK
93 do_test misc4-2.4 {
94 catchsql {
95 INSERT INTO t3 VALUES(1);
96 }
97 } {0 {}}
98  
99 # Ticket #966
100 #
101 do_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}}
113 ifcapable 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 #
139 ifcapable 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 #
175 ifcapable 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
198 do_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 } {}
205 do_test misc4-6.2 {
206 execsql {
207 SELECT a FROM abc LEFT JOIN def ON (abc.a=def.d);
208 }
209 } {1}
210  
211 finish_test