| blob | a91da5feb920a7baaf1f2eaca404371999125da9 |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2003 December 17
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library.
15 #
16 # This file implements tests for miscellanous features that were
17 # left out of other test files.
18 #
19 # $Id: misc3.test,v 1.10 2004/03/17 23:32:08 drh Exp $
21 set testdir [file dirname $argv0]
22 source $testdir/tester.tcl
24 # Ticket #529. Make sure an ABORT does not damage the in-memory cache
25 # that will be used by subsequent statements in the same transaction.
26 #
27 do_test misc3-1.1 {
28 execsql {
29 CREATE TABLE t1(a UNIQUE,b);
30 INSERT INTO t1
31 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
32 UPDATE t1 SET b=b||b;
33 UPDATE t1 SET b=b||b;
34 UPDATE t1 SET b=b||b;
35 UPDATE t1 SET b=b||b;
36 UPDATE t1 SET b=b||b;
37 INSERT INTO t1 VALUES(2,'x');
38 UPDATE t1 SET b=substr(b,1,500);
39 BEGIN;
40 }
41 catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';}
42 execsql {
43 CREATE TABLE t2(x,y);
44 COMMIT;
45 PRAGMA integrity_check;
46 }
47 } ok
48 do_test misc3-1.2 {
49 execsql {
50 DROP TABLE t1;
51 DROP TABLE t2;
52 VACUUM;
53 CREATE TABLE t1(a UNIQUE,b);
54 INSERT INTO t1
55 VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
56 INSERT INTO t1 SELECT a+1, b||b FROM t1;
57 INSERT INTO t1 SELECT a+2, b||b FROM t1;
58 INSERT INTO t1 SELECT a+4, b FROM t1;
59 INSERT INTO t1 SELECT a+8, b FROM t1;
60 INSERT INTO t1 SELECT a+16, b FROM t1;
61 INSERT INTO t1 SELECT a+32, b FROM t1;
62 INSERT INTO t1 SELECT a+64, b FROM t1;
64 BEGIN;
65 }
66 catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';}
67 execsql {
68 INSERT INTO t1 VALUES(200,'hello out there');
69 COMMIT;
70 PRAGMA integrity_check;
71 }
72 } ok
74 # Tests of the sqliteAtoF() function in util.c
75 #
76 do_test misc3-2.1 {
77 execsql {SELECT 2e-25*0.5e25}
78 } 1
79 do_test misc3-2.2 {
80 execsql {SELECT 2.0e-25*000000.500000000000000000000000000000e+00025}
81 } 1
82 do_test misc3-2.3 {
83 execsql {SELECT 000000000002e-0000000025*0.5e25}
84 } 1
85 do_test misc3-2.4 {
86 execsql {SELECT 2e-25*0.5e250}
87 } 1e+225
88 do_test misc3-2.5 {
89 execsql {SELECT 2.0e-250*0.5e25}
90 } 1e-225
91 do_test misc3-2.6 {
92 execsql {SELECT '-2.0e-127' * '-0.5e27'}
93 } 1e-100
94 do_test misc3-2.7 {
95 execsql {SELECT '+2.0e-127' * '-0.5e27'}
96 } -1e-100
97 do_test misc3-2.8 {
98 execsql {SELECT 2.0e-27 * '+0.5e+127'}
99 } 1e+100
100 do_test misc3-2.9 {
101 execsql {SELECT 2.0e-27 * '+0.000005e+132'}
102 } 1e+100
104 # Ticket #522. Make sure integer overflow is handled properly in
105 # indices.
106 #
107 do_test misc3-3.1 {
108 execsql {PRAGMA integrity_check}
109 } ok
110 do_test misc3-3.2 {
111 execsql {
112 CREATE TABLE t2(a INT UNIQUE);
113 PRAGMA integrity_check;
114 }
115 } ok
116 do_test misc3-3.3 {
117 execsql {
118 INSERT INTO t2 VALUES(2147483648);
119 PRAGMA integrity_check;
120 }
121 } ok
122 do_test misc3-3.4 {
123 execsql {
124 INSERT INTO t2 VALUES(-2147483649);
125 PRAGMA integrity_check;
126 }
127 } ok
128 do_test misc3-3.5 {
129 execsql {
130 INSERT INTO t2 VALUES(+2147483649);
131 PRAGMA integrity_check;
132 }
133 } ok
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 do_test misc3-4.2 {
203 execsql {
204 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);
205 }
206 } {64}
207 do_test misc3-4.3 {
208 execsql {
209 SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
210 }
211 } {64}
213 # Ticket #601: Putting a left join inside "SELECT * FROM (<join-here>)"
214 # gives different results that if the outer "SELECT * FROM ..." is omitted.
215 #
216 do_test misc3-5.1 {
217 execsql {
218 CREATE TABLE x1 (b, c);
219 INSERT INTO x1 VALUES('dog',3);
220 INSERT INTO x1 VALUES('cat',1);
221 INSERT INTO x1 VALUES('dog',4);
222 CREATE TABLE x2 (c, e);
223 INSERT INTO x2 VALUES(1,'one');
224 INSERT INTO x2 VALUES(2,'two');
225 INSERT INTO x2 VALUES(3,'three');
226 INSERT INTO x2 VALUES(4,'four');
227 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
228 (SELECT b, max(c) AS c FROM x1 GROUP BY b)
229 USING(c);
230 }
231 } {1 one cat 2 two {} 3 three {} 4 four dog}
232 do_test misc4-5.2 {
233 execsql {
234 SELECT * FROM (
235 SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
236 (SELECT b, max(c) AS c FROM x1 GROUP BY b)
237 USING(c)
238 );
239 }
240 } {1 one cat 2 two {} 3 three {} 4 four dog}
242 # Ticket #626: make sure EXPLAIN prevents BEGIN and COMMIT from working.
243 #
244 do_test misc3-6.1 {
245 execsql {EXPLAIN BEGIN}
246 catchsql {BEGIN}
247 } {0 {}}
248 do_test misc3-6.2 {
249 execsql {EXPLAIN COMMIT}
250 catchsql {COMMIT}
251 } {0 {}}
252 do_test misc3-6.3 {
253 execsql {BEGIN; EXPLAIN ROLLBACK}
254 catchsql {ROLLBACK}
255 } {0 {}}
257 # Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside
258 # of a trigger.
259 #
260 do_test misc3-7.1 {
261 execsql {
262 BEGIN;
263 CREATE TABLE y1(a);
264 CREATE TABLE y2(b);
265 CREATE TABLE y3(c);
266 CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN
267 INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1;
268 END;
269 INSERT INTO y1 VALUES(1);
270 INSERT INTO y1 VALUES(2);
271 INSERT INTO y1 SELECT a+2 FROM y1;
272 INSERT INTO y1 SELECT a+4 FROM y1;
273 INSERT INTO y1 SELECT a+8 FROM y1;
274 INSERT INTO y1 SELECT a+16 FROM y1;
275 INSERT INTO y2 SELECT a FROM y1;
276 COMMIT;
277 SELECT count(*) FROM y1;
278 }
279 } 32
280 do_test misc3-7.2 {
281 execsql {
282 DELETE FROM y1;
283 SELECT count(*) FROM y1;
284 }
285 } 0
286 do_test misc3-7.3 {
287 execsql {
288 SELECT count(*) FROM y3;
289 }
290 } 32
292 # Ticket #668: VDBE stack overflow occurs when the left-hand side
293 # of an IN expression is NULL and the result is used as an integer, not
294 # as a jump.
295 #
296 do_test misc-8.1 {
297 execsql {
298 SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3
299 }
300 } {2}
301 do_test misc-8.2 {
302 execsql {
303 SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2
304 }
305 } {2}
307 finish_test