| blob | bc1f0ff91171dd626612ffeed8b29ea6020b584e |
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.20 2009/05/06 00:49:01 drh 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 {}}
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 {,-B} $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 {,-B} $x]
297 } {1 1 1 1}
298 }
299 }
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
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 }
355 finish_test