1: # 2003 December 17
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: misc3.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: ifcapable {integrityck} {
22: # Ticket #529. Make sure an ABORT does not damage the in-memory cache
23: # that will be used by subsequent statements in the same transaction.
24: #
25: do_test misc3-1.1 {
26: execsql {
27: CREATE TABLE t1(a UNIQUE,b);
28: INSERT INTO t1
29: VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
30: UPDATE t1 SET b=b||b;
31: UPDATE t1 SET b=b||b;
32: UPDATE t1 SET b=b||b;
33: UPDATE t1 SET b=b||b;
34: UPDATE t1 SET b=b||b;
35: INSERT INTO t1 VALUES(2,'x');
36: UPDATE t1 SET b=substr(b,1,500);
37: BEGIN;
38: }
39: catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';}
40: execsql {
41: CREATE TABLE t2(x,y);
42: COMMIT;
43: PRAGMA integrity_check;
44: }
45: } ok
46: }
47: ifcapable {integrityck} {
48: do_test misc3-1.2 {
49: execsql {
50: DROP TABLE t1;
51: DROP TABLE t2;
52: }
53: ifcapable {vacuum} {execsql VACUUM}
54: execsql {
55: CREATE TABLE t1(a UNIQUE,b);
56: INSERT INTO t1
57: VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
58: INSERT INTO t1 SELECT a+1, b||b FROM t1;
59: INSERT INTO t1 SELECT a+2, b||b FROM t1;
60: INSERT INTO t1 SELECT a+4, b FROM t1;
61: INSERT INTO t1 SELECT a+8, b FROM t1;
62: INSERT INTO t1 SELECT a+16, b FROM t1;
63: INSERT INTO t1 SELECT a+32, b FROM t1;
64: INSERT INTO t1 SELECT a+64, b FROM t1;
65: BEGIN;
66: }
67: catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';}
68: execsql {
69: INSERT INTO t1 VALUES(200,'hello out there');
70: COMMIT;
71: PRAGMA integrity_check;
72: }
73: } ok
74: }
75:
76: # Tests of the sqliteAtoF() function in util.c
77: #
78: do_test misc3-2.1 {
79: execsql {SELECT 2e-25*0.5e25}
80: } 1.0
81: do_test misc3-2.2 {
82: execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025}
83: } 1.0
84: do_test misc3-2.3 {
85: execsql {SELECT 000000000002e-0000000025*0.5e25}
86: } 1.0
87: do_test misc3-2.4 {
88: execsql {SELECT 2e-25*0.5e250}
89: } 1e+225
90: do_test misc3-2.5 {
91: execsql {SELECT 2.0e-250*0.5e25}
92: } 1e-225
93: do_test misc3-2.6 {
94: execsql {SELECT '-2.0e-127' * '-0.5e27'}
95: } 1e-100
96: do_test misc3-2.7 {
97: execsql {SELECT '+2.0e-127' * '-0.5e27'}
98: } -1e-100
99: do_test misc3-2.8 {
100: execsql {SELECT 2.0e-27 * '+0.5e+127'}
101: } 1e+100
102: do_test misc3-2.9 {
103: execsql {SELECT 2.0e-27 * '+0.000005e+132'}
104: } 1e+100
105:
106: # Ticket #522. Make sure integer overflow is handled properly in
107: # indices.
108: #
109: integrity_check misc3-3.1
110: do_test misc3-3.2 {
111: execsql {
112: CREATE TABLE t2(a INT UNIQUE);
113: }
114: } {}
115: integrity_check misc3-3.2.1
116: do_test misc3-3.3 {
117: execsql {
118: INSERT INTO t2 VALUES(2147483648);
119: }
120: } {}
121: integrity_check misc3-3.3.1
122: do_test misc3-3.4 {
123: execsql {
124: INSERT INTO t2 VALUES(-2147483649);
125: }
126: } {}
127: integrity_check misc3-3.4.1
128: do_test misc3-3.5 {
129: execsql {
130: INSERT INTO t2 VALUES(+2147483649);
131: }
132: } {}
133: integrity_check misc3-3.5.1
134: do_test misc3-3.6 {
135: execsql {
136: INSERT INTO t2 VALUES(+2147483647);
137: INSERT INTO t2 VALUES(-2147483648);
138: INSERT INTO t2 VALUES(-2147483647);
139: INSERT INTO t2 VALUES(2147483646);
140: SELECT * FROM t2 ORDER BY a;
141: }
142: } {-2147483649 -2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
143: do_test misc3-3.7 {
144: execsql {
145: SELECT * FROM t2 WHERE a>=-2147483648 ORDER BY a;
146: }
147: } {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
148: do_test misc3-3.8 {
149: execsql {
150: SELECT * FROM t2 WHERE a>-2147483648 ORDER BY a;
151: }
152: } {-2147483647 2147483646 2147483647 2147483648 2147483649}
153: do_test misc3-3.9 {
154: execsql {
155: SELECT * FROM t2 WHERE a>-2147483649 ORDER BY a;
156: }
157: } {-2147483648 -2147483647 2147483646 2147483647 2147483648 2147483649}
158: do_test misc3-3.10 {
159: execsql {
160: SELECT * FROM t2 WHERE a>=0 AND a<2147483649 ORDER BY a DESC;
161: }
162: } {2147483648 2147483647 2147483646}
163: do_test misc3-3.11 {
164: execsql {
165: SELECT * FROM t2 WHERE a>=0 AND a<=2147483648 ORDER BY a DESC;
166: }
167: } {2147483648 2147483647 2147483646}
168: do_test misc3-3.12 {
169: execsql {
170: SELECT * FROM t2 WHERE a>=0 AND a<2147483648 ORDER BY a DESC;
171: }
172: } {2147483647 2147483646}
173: do_test misc3-3.13 {
174: execsql {
175: SELECT * FROM t2 WHERE a>=0 AND a<=2147483647 ORDER BY a DESC;
176: }
177: } {2147483647 2147483646}
178: do_test misc3-3.14 {
179: execsql {
180: SELECT * FROM t2 WHERE a>=0 AND a<2147483647 ORDER BY a DESC;
181: }
182: } {2147483646}
183:
184: # Ticket #565. A stack overflow is occurring when the subquery to the
185: # right of an IN operator contains many NULLs
186: #
187: do_test misc3-4.1 {
188: execsql {
189: CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
190: INSERT INTO t3(b) VALUES('abc');
191: INSERT INTO t3(b) VALUES('xyz');
192: INSERT INTO t3(b) VALUES(NULL);
193: INSERT INTO t3(b) VALUES(NULL);
194: INSERT INTO t3(b) SELECT b||'d' FROM t3;
195: INSERT INTO t3(b) SELECT b||'e' FROM t3;
196: INSERT INTO t3(b) SELECT b||'f' FROM t3;
197: INSERT INTO t3(b) SELECT b||'g' FROM t3;
198: INSERT INTO t3(b) SELECT b||'h' FROM t3;
199: SELECT count(a), count(b) FROM t3;
200: }
201: } {128 64}
202: ifcapable subquery {
203: do_test misc3-4.2 {
204: execsql {
205: SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);
206: }
207: } {64}
208: do_test misc3-4.3 {
209: execsql {
210: SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
211: }
212: } {64}
213: }
214:
215: # Ticket #601: Putting a left join inside "SELECT * FROM (<join-here>)"
216: # gives different results that if the outer "SELECT * FROM ..." is omitted.
217: #
218: ifcapable subquery {
219: do_test misc3-5.1 {
220: execsql {
221: CREATE TABLE x1 (b, c);
222: INSERT INTO x1 VALUES('dog',3);
223: INSERT INTO x1 VALUES('cat',1);
224: INSERT INTO x1 VALUES('dog',4);
225: CREATE TABLE x2 (c, e);
226: INSERT INTO x2 VALUES(1,'one');
227: INSERT INTO x2 VALUES(2,'two');
228: INSERT INTO x2 VALUES(3,'three');
229: INSERT INTO x2 VALUES(4,'four');
230: SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
231: (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
232: USING(c);
233: }
234: } {1 one cat 2 two {} 3 three {} 4 four dog}
235: do_test misc3-5.2 {
236: execsql {
237: SELECT * FROM (
238: SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
239: (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
240: USING(c)
241: );
242: }
243: } {1 one cat 2 two {} 3 three {} 4 four dog}
244: }
245:
246: ifcapable {explain} {
247: # Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working.
248: #
249: do_test misc3-6.1 {
250: execsql {EXPLAIN BEGIN}
251: catchsql {BEGIN}
252: } {0 {}}
253: do_test misc3-6.2 {
254: execsql {EXPLAIN COMMIT}
255: catchsql {COMMIT}
256: } {0 {}}
257: do_test misc3-6.3 {
258: execsql {BEGIN; EXPLAIN ROLLBACK}
259: catchsql {ROLLBACK}
260: } {0 {}}
261:
262: # Do some additional EXPLAIN operations to exercise the displayP4 logic.
263: do_test misc3-6.10 {
264: set x [execsql {
265: CREATE TABLE ex1(
266: a INTEGER DEFAULT 54321,
267: b TEXT DEFAULT "hello",
268: c REAL DEFAULT 3.1415926
269: );
270: CREATE UNIQUE INDEX ex1i1 ON ex1(a);
271: EXPLAIN REINDEX;
272: }]
273: ifcapable mergesort {
274: regexp { SorterCompare \d+ \d+ \d+ } $x
275: } else {
276: regexp { IsUnique \d+ \d+ \d+ \d+ } $x
277: }
278: } {1}
279: if {[regexp {16} [db one {PRAGMA encoding}]]} {
280: do_test misc3-6.11-utf16 {
281: set x [execsql {
282: EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC
283: }]
284: set y [regexp { 123456789012 } $x]
285: lappend y [regexp { 4.5678 } $x]
286: lappend y [regexp {,-BINARY} $x]
287: } {1 1 1}
288: } else {
289: do_test misc3-6.11-utf8 {
290: set x [execsql {
291: EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC
292: }]
293: set y [regexp { 123456789012 } $x]
294: lappend y [regexp { 4.5678 } $x]
295: lappend y [regexp { hello } $x]
296: lappend y [regexp {,-BINARY} $x]
297: } {1 1 1 1}
298: }
299: }
300:
301: ifcapable {trigger} {
302: # Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside
303: # of a trigger.
304: #
305: do_test misc3-7.1 {
306: execsql {
307: BEGIN;
308: CREATE TABLE y1(a);
309: CREATE TABLE y2(b);
310: CREATE TABLE y3(c);
311: CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN
312: INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1;
313: END;
314: INSERT INTO y1 VALUES(1);
315: INSERT INTO y1 VALUES(2);
316: INSERT INTO y1 SELECT a+2 FROM y1;
317: INSERT INTO y1 SELECT a+4 FROM y1;
318: INSERT INTO y1 SELECT a+8 FROM y1;
319: INSERT INTO y1 SELECT a+16 FROM y1;
320: INSERT INTO y2 SELECT a FROM y1;
321: COMMIT;
322: SELECT count(*) FROM y1;
323: }
324: } 32
325: do_test misc3-7.2 {
326: execsql {
327: DELETE FROM y1;
328: SELECT count(*) FROM y1;
329: }
330: } 0
331: do_test misc3-7.3 {
332: execsql {
333: SELECT count(*) FROM y3;
334: }
335: } 32
336: } ;# endif trigger
337:
338: # Ticket #668: VDBE stack overflow occurs when the left-hand side
339: # of an IN expression is NULL and the result is used as an integer, not
340: # as a jump.
341: #
342: ifcapable subquery {
343: do_test misc-8.1 {
344: execsql {
345: SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3
346: }
347: } {2}
348: do_test misc-8.2 {
349: execsql {
350: SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2
351: }
352: } {2}
353: }
354:
355: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to misc3.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