| blob | cd76335a5b584e7ec48128b7450561a04993f25d |
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.16 2005/01/21 03:12:16 danielk1977 Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
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 }
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
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}
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 }
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 }
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 }
263 ifcapable {trigger} {
264 # Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside
265 # of a trigger.
266 #
267 do_test misc3-7.1 {
268 execsql {
269 BEGIN;
270 CREATE TABLE y1(a);
271 CREATE TABLE y2(b);
272 CREATE TABLE y3(c);
273 CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN
274 INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1;
275 END;
276 INSERT INTO y1 VALUES(1);
277 INSERT INTO y1 VALUES(2);
278 INSERT INTO y1 SELECT a+2 FROM y1;
279 INSERT INTO y1 SELECT a+4 FROM y1;
280 INSERT INTO y1 SELECT a+8 FROM y1;
281 INSERT INTO y1 SELECT a+16 FROM y1;
282 INSERT INTO y2 SELECT a FROM y1;
283 COMMIT;
284 SELECT count(*) FROM y1;
285 }
286 } 32
287 do_test misc3-7.2 {
288 execsql {
289 DELETE FROM y1;
290 SELECT count(*) FROM y1;
291 }
292 } 0
293 do_test misc3-7.3 {
294 execsql {
295 SELECT count(*) FROM y3;
296 }
297 } 32
298 } ;# endif trigger
300 # Ticket #668: VDBE stack overflow occurs when the left-hand side
301 # of an IN expression is NULL and the result is used as an integer, not
302 # as a jump.
303 #
304 ifcapable subquery {
305 do_test misc-8.1 {
306 execsql {
307 SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3
308 }
309 } {2}
310 do_test misc-8.2 {
311 execsql {
312 SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2
313 }
314 } {2}
315 }
317 finish_test