| blob | bb544c5fbb57891289967080c805341e2a157d55 |
1 # 2003 June 21
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: misc2.test,v 1.28 2007/09/12 17:01:45 danielk1977 Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
21 # The tests in this file were written before SQLite supported recursive
22 # trigger invocation, and some tests depend on that to pass. So disable
23 # recursive triggers for this file.
24 catchsql { pragma recursive_triggers = off }
26 ifcapable {trigger} {
27 # Test for ticket #360
28 #
29 do_test misc2-1.1 {
30 catchsql {
31 CREATE TABLE FOO(bar integer);
32 CREATE TRIGGER foo_insert BEFORE INSERT ON foo BEGIN
33 SELECT CASE WHEN (NOT new.bar BETWEEN 0 AND 20)
34 THEN raise(rollback, 'aiieee') END;
35 END;
36 INSERT INTO foo(bar) VALUES (1);
37 }
38 } {0 {}}
39 do_test misc2-1.2 {
40 catchsql {
41 INSERT INTO foo(bar) VALUES (111);
42 }
43 } {1 aiieee}
44 } ;# endif trigger
46 # Make sure ROWID works on a view and a subquery. Ticket #364
47 #
48 do_test misc2-2.1 {
49 execsql {
50 CREATE TABLE t1(a,b,c);
51 INSERT INTO t1 VALUES(1,2,3);
52 CREATE TABLE t2(a,b,c);
53 INSERT INTO t2 VALUES(7,8,9);
54 }
55 } {}
56 ifcapable subquery {
57 do_test misc2-2.2 {
58 execsql {
59 SELECT rowid, * FROM (SELECT * FROM t1, t2);
60 }
61 } {{} 1 2 3 7 8 9}
62 }
63 ifcapable view {
64 do_test misc2-2.3 {
65 execsql {
66 CREATE VIEW v1 AS SELECT * FROM t1, t2;
67 SELECT rowid, * FROM v1;
68 }
69 } {{} 1 2 3 7 8 9}
70 } ;# ifcapable view
72 # Ticket #2002 and #1952.
73 ifcapable subquery {
74 do_test misc2-2.4 {
75 execsql2 {
76 SELECT * FROM (SELECT a, b AS 'a', c AS 'a', 4 AS 'a' FROM t1)
77 }
78 } {a 1 a:1 2 a:2 3 a:3 4}
79 }
81 # Check name binding precedence. Ticket #387
82 #
83 do_test misc2-3.1 {
84 catchsql {
85 SELECT t1.b+t2.b AS a, t1.a, t2.a FROM t1, t2 WHERE a==10
86 }
87 } {1 {ambiguous column name: a}}
89 # Make sure 32-bit integer overflow is handled properly in queries.
90 # ticket #408
91 #
92 do_test misc2-4.1 {
93 execsql {
94 INSERT INTO t1 VALUES(4000000000,'a','b');
95 SELECT a FROM t1 WHERE a>1;
96 }
97 } {4000000000}
98 do_test misc2-4.2 {
99 execsql {
100 INSERT INTO t1 VALUES(2147483648,'b2','c2');
101 INSERT INTO t1 VALUES(2147483647,'b3','c3');
102 SELECT a FROM t1 WHERE a>2147483647;
103 }
104 } {4000000000 2147483648}
105 do_test misc2-4.3 {
106 execsql {
107 SELECT a FROM t1 WHERE a<2147483648;
108 }
109 } {1 2147483647}
110 do_test misc2-4.4 {
111 execsql {
112 SELECT a FROM t1 WHERE a<=2147483648;
113 }
114 } {1 2147483648 2147483647}
115 do_test misc2-4.5 {
116 execsql {
117 SELECT a FROM t1 WHERE a<10000000000;
118 }
119 } {1 4000000000 2147483648 2147483647}
120 do_test misc2-4.6 {
121 execsql {
122 SELECT a FROM t1 WHERE a<1000000000000 ORDER BY 1;
123 }
124 } {1 2147483647 2147483648 4000000000}
126 # There were some issues with expanding a SrcList object using a call
127 # to sqliteSrcListAppend() if the SrcList had previously been duplicated
128 # using a call to sqliteSrcListDup(). Ticket #416. The following test
129 # makes sure the problem has been fixed.
130 #
131 ifcapable view {
132 do_test misc2-5.1 {
133 execsql {
134 CREATE TABLE x(a,b);
135 CREATE VIEW y AS
136 SELECT x1.b AS p, x2.b AS q FROM x AS x1, x AS x2 WHERE x1.a=x2.a;
137 CREATE VIEW z AS
138 SELECT y1.p, y2.p FROM y AS y1, y AS y2 WHERE y1.q=y2.q;
139 SELECT * from z;
140 }
141 } {}
142 }
144 # Make sure we can open a database with an empty filename. What this
145 # does is store the database in a temporary file that is deleted when
146 # the database is closed. Ticket #432.
147 #
148 do_test misc2-6.1 {
149 db close
150 sqlite3 db {}
151 execsql {
152 CREATE TABLE t1(a,b);
153 INSERT INTO t1 VALUES(1,2);
154 SELECT * FROM t1;
155 }
156 } {1 2}
158 # Make sure we get an error message (not a segfault) on an attempt to
159 # update a table from within the callback of a select on that same
160 # table.
161 #
162 # 2006-08-16: This has changed. It is now permitted to update
163 # the table being SELECTed from within the callback of the query.
164 #
165 ifcapable tclvar {
166 do_test misc2-7.1 {
167 db close
168 forcedelete test.db
169 sqlite3 db test.db
170 execsql {
171 CREATE TABLE t1(x);
172 INSERT INTO t1 VALUES(1);
173 INSERT INTO t1 VALUES(2);
174 INSERT INTO t1 VALUES(3);
175 SELECT * FROM t1;
176 }
177 } {1 2 3}
178 do_test misc2-7.2 {
179 set rc [catch {
180 db eval {SELECT rowid FROM t1} {} {
181 db eval "DELETE FROM t1 WHERE rowid=$rowid"
182 }
183 } msg]
184 lappend rc $msg
185 } {0 {}}
186 do_test misc2-7.3 {
187 execsql {SELECT * FROM t1}
188 } {}
189 do_test misc2-7.4 {
190 execsql {
191 DELETE FROM t1;
192 INSERT INTO t1 VALUES(1);
193 INSERT INTO t1 VALUES(2);
194 INSERT INTO t1 VALUES(3);
195 INSERT INTO t1 VALUES(4);
196 }
197 db eval {SELECT rowid, x FROM t1} {
198 if {$x & 1} {
199 db eval {DELETE FROM t1 WHERE rowid=$rowid}
200 }
201 }
202 execsql {SELECT * FROM t1}
203 } {2 4}
204 do_test misc2-7.5 {
205 execsql {
206 DELETE FROM t1;
207 INSERT INTO t1 VALUES(1);
208 INSERT INTO t1 VALUES(2);
209 INSERT INTO t1 VALUES(3);
210 INSERT INTO t1 VALUES(4);
211 }
212 db eval {SELECT rowid, x FROM t1} {
213 if {$x & 1} {
214 db eval {DELETE FROM t1 WHERE rowid=$rowid+1}
215 }
216 }
217 execsql {SELECT * FROM t1}
218 } {1 3}
219 do_test misc2-7.6 {
220 execsql {
221 DELETE FROM t1;
222 INSERT INTO t1 VALUES(1);
223 INSERT INTO t1 VALUES(2);
224 INSERT INTO t1 VALUES(3);
225 INSERT INTO t1 VALUES(4);
226 }
227 db eval {SELECT rowid, x FROM t1} {
228 if {$x & 1} {
229 db eval {DELETE FROM t1}
230 }
231 }
232 execsql {SELECT * FROM t1}
233 } {}
234 do_test misc2-7.7 {
235 execsql {
236 DELETE FROM t1;
237 INSERT INTO t1 VALUES(1);
238 INSERT INTO t1 VALUES(2);
239 INSERT INTO t1 VALUES(3);
240 INSERT INTO t1 VALUES(4);
241 }
242 db eval {SELECT rowid, x FROM t1} {
243 if {$x & 1} {
244 db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid}
245 }
246 }
247 execsql {SELECT * FROM t1}
248 } {101 2 103 4}
249 do_test misc2-7.8 {
250 execsql {
251 DELETE FROM t1;
252 INSERT INTO t1 VALUES(1);
253 }
254 db eval {SELECT rowid, x FROM t1} {
255 if {$x<10} {
256 db eval {INSERT INTO t1 VALUES($x+1)}
257 }
258 }
259 execsql {SELECT * FROM t1}
260 } {1 2 3 4 5 6 7 8 9 10}
262 # Repeat the tests 7.1 through 7.8 about but this time do the SELECTs
263 # in reverse order so that we exercise the sqlite3BtreePrev() routine
264 # instead of sqlite3BtreeNext()
265 #
266 do_test misc2-7.11 {
267 db close
268 forcedelete test.db
269 sqlite3 db test.db
270 execsql {
271 CREATE TABLE t1(x);
272 INSERT INTO t1 VALUES(1);
273 INSERT INTO t1 VALUES(2);
274 INSERT INTO t1 VALUES(3);
275 SELECT * FROM t1;
276 }
277 } {1 2 3}
278 do_test misc2-7.12 {
279 set rc [catch {
280 db eval {SELECT rowid FROM t1 ORDER BY rowid DESC} {} {
281 db eval "DELETE FROM t1 WHERE rowid=$rowid"
282 }
283 } msg]
284 lappend rc $msg
285 } {0 {}}
286 do_test misc2-7.13 {
287 execsql {SELECT * FROM t1}
288 } {}
289 do_test misc2-7.14 {
290 execsql {
291 DELETE FROM t1;
292 INSERT INTO t1 VALUES(1);
293 INSERT INTO t1 VALUES(2);
294 INSERT INTO t1 VALUES(3);
295 INSERT INTO t1 VALUES(4);
296 }
297 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
298 if {$x & 1} {
299 db eval {DELETE FROM t1 WHERE rowid=$rowid}
300 }
301 }
302 execsql {SELECT * FROM t1}
303 } {2 4}
304 do_test misc2-7.15 {
305 execsql {
306 DELETE FROM t1;
307 INSERT INTO t1 VALUES(1);
308 INSERT INTO t1 VALUES(2);
309 INSERT INTO t1 VALUES(3);
310 INSERT INTO t1 VALUES(4);
311 }
312 db eval {SELECT rowid, x FROM t1} {
313 if {$x & 1} {
314 db eval {DELETE FROM t1 WHERE rowid=$rowid+1}
315 }
316 }
317 execsql {SELECT * FROM t1}
318 } {1 3}
319 do_test misc2-7.16 {
320 execsql {
321 DELETE FROM t1;
322 INSERT INTO t1 VALUES(1);
323 INSERT INTO t1 VALUES(2);
324 INSERT INTO t1 VALUES(3);
325 INSERT INTO t1 VALUES(4);
326 }
327 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
328 if {$x & 1} {
329 db eval {DELETE FROM t1}
330 }
331 }
332 execsql {SELECT * FROM t1}
333 } {}
334 do_test misc2-7.17 {
335 execsql {
336 DELETE FROM t1;
337 INSERT INTO t1 VALUES(1);
338 INSERT INTO t1 VALUES(2);
339 INSERT INTO t1 VALUES(3);
340 INSERT INTO t1 VALUES(4);
341 }
342 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
343 if {$x & 1} {
344 db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid}
345 }
346 }
347 execsql {SELECT * FROM t1}
348 } {101 2 103 4}
349 do_test misc2-7.18 {
350 execsql {
351 DELETE FROM t1;
352 INSERT INTO t1(rowid,x) VALUES(10,10);
353 }
354 db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} {
355 if {$x>1} {
356 db eval {INSERT INTO t1(rowid,x) VALUES($x-1,$x-1)}
357 }
358 }
359 execsql {SELECT * FROM t1}
360 } {1 2 3 4 5 6 7 8 9 10}
361 }
363 db close
364 forcedelete test.db
365 sqlite3 db test.db
366 catchsql { pragma recursive_triggers = off }
368 # Ticket #453. If the SQL ended with "-", the tokenizer was calling that
369 # an incomplete token, which caused problem. The solution was to just call
370 # it a minus sign.
371 #
372 do_test misc2-8.1 {
373 catchsql {-}
374 } {1 {near "-": syntax error}}
376 # Ticket #513. Make sure the VDBE stack does not grow on a 3-way join.
377 #
378 ifcapable tempdb {
379 do_test misc2-9.1 {
380 execsql {
381 BEGIN;
382 CREATE TABLE counts(n INTEGER PRIMARY KEY);
383 INSERT INTO counts VALUES(0);
384 INSERT INTO counts VALUES(1);
385 INSERT INTO counts SELECT n+2 FROM counts;
386 INSERT INTO counts SELECT n+4 FROM counts;
387 INSERT INTO counts SELECT n+8 FROM counts;
388 COMMIT;
390 CREATE TEMP TABLE x AS
391 SELECT dim1.n, dim2.n, dim3.n
392 FROM counts AS dim1, counts AS dim2, counts AS dim3
393 WHERE dim1.n<10 AND dim2.n<10 AND dim3.n<10;
395 SELECT count(*) FROM x;
396 }
397 } {1000}
398 do_test misc2-9.2 {
399 execsql {
400 DROP TABLE x;
401 CREATE TEMP TABLE x AS
402 SELECT dim1.n, dim2.n, dim3.n
403 FROM counts AS dim1, counts AS dim2, counts AS dim3
404 WHERE dim1.n>=6 AND dim2.n>=6 AND dim3.n>=6;
406 SELECT count(*) FROM x;
407 }
408 } {1000}
409 do_test misc2-9.3 {
410 execsql {
411 DROP TABLE x;
412 CREATE TEMP TABLE x AS
413 SELECT dim1.n, dim2.n, dim3.n, dim4.n
414 FROM counts AS dim1, counts AS dim2, counts AS dim3, counts AS dim4
415 WHERE dim1.n<5 AND dim2.n<5 AND dim3.n<5 AND dim4.n<5;
417 SELECT count(*) FROM x;
418 }
419 } [expr 5*5*5*5]
420 }
422 # Ticket #1229. Sometimes when a "NEW.X" appears in a SELECT without
423 # a FROM clause deep within a trigger, the code generator is unable to
424 # trace the NEW.X back to an original table and thus figure out its
425 # declared datatype.
426 #
427 # The SQL code below was causing a segfault.
428 #
429 ifcapable subquery&&trigger {
430 do_test misc2-10.1 {
431 execsql {
432 CREATE TABLE t1229(x);
433 CREATE TRIGGER r1229 BEFORE INSERT ON t1229 BEGIN
434 INSERT INTO t1229 SELECT y FROM (SELECT new.x y);
435 END;
436 INSERT INTO t1229 VALUES(1);
437 }
438 } {}
439 }
441 finish_test