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.1.1.1 2012/02/21 17:04:16 misho Exp $
17:
18: set testdir [file dirname $argv0]
19: source $testdir/tester.tcl
20:
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 }
25:
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
45:
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
71:
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: }
80:
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}}
88:
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}
125:
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: }
143:
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}
157:
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}
261:
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: }
362:
363: db close
364: forcedelete test.db
365: sqlite3 db test.db
366: catchsql { pragma recursive_triggers = off }
367:
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}}
375:
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;
389:
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;
394:
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;
405:
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;
416:
417: SELECT count(*) FROM x;
418: }
419: } [expr 5*5*5*5]
420: }
421:
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: }
440:
441: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to misc2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test