wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 $ |
||
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 | regexp { IsUnique \d+ \d+ \d+ \d+ } $x |
||
274 | } {1} |
||
275 | if {[regexp {16} [db one {PRAGMA encoding}]]} { |
||
276 | do_test misc3-6.11-utf16 { |
||
277 | set x [execsql { |
||
278 | EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC |
||
279 | }] |
||
280 | set y [regexp { 123456789012 } $x] |
||
281 | lappend y [regexp { 4.5678 } $x] |
||
282 | lappend y [regexp {,-BINARY} $x] |
||
283 | } {1 1 1} |
||
284 | } else { |
||
285 | do_test misc3-6.11-utf8 { |
||
286 | set x [execsql { |
||
287 | EXPLAIN SELECT a+123456789012, b*4.5678, c FROM ex1 ORDER BY +a, b DESC |
||
288 | }] |
||
289 | set y [regexp { 123456789012 } $x] |
||
290 | lappend y [regexp { 4.5678 } $x] |
||
291 | lappend y [regexp { hello } $x] |
||
292 | lappend y [regexp {,-BINARY} $x] |
||
293 | } {1 1 1 1} |
||
294 | } |
||
295 | } |
||
296 | |||
297 | ifcapable {trigger} { |
||
298 | # Ticket #640: vdbe stack overflow with a LIMIT clause on a SELECT inside |
||
299 | # of a trigger. |
||
300 | # |
||
301 | do_test misc3-7.1 { |
||
302 | execsql { |
||
303 | BEGIN; |
||
304 | CREATE TABLE y1(a); |
||
305 | CREATE TABLE y2(b); |
||
306 | CREATE TABLE y3(c); |
||
307 | CREATE TRIGGER r1 AFTER DELETE ON y1 FOR EACH ROW BEGIN |
||
308 | INSERT INTO y3(c) SELECT b FROM y2 ORDER BY b LIMIT 1; |
||
309 | END; |
||
310 | INSERT INTO y1 VALUES(1); |
||
311 | INSERT INTO y1 VALUES(2); |
||
312 | INSERT INTO y1 SELECT a+2 FROM y1; |
||
313 | INSERT INTO y1 SELECT a+4 FROM y1; |
||
314 | INSERT INTO y1 SELECT a+8 FROM y1; |
||
315 | INSERT INTO y1 SELECT a+16 FROM y1; |
||
316 | INSERT INTO y2 SELECT a FROM y1; |
||
317 | COMMIT; |
||
318 | SELECT count(*) FROM y1; |
||
319 | } |
||
320 | } 32 |
||
321 | do_test misc3-7.2 { |
||
322 | execsql { |
||
323 | DELETE FROM y1; |
||
324 | SELECT count(*) FROM y1; |
||
325 | } |
||
326 | } 0 |
||
327 | do_test misc3-7.3 { |
||
328 | execsql { |
||
329 | SELECT count(*) FROM y3; |
||
330 | } |
||
331 | } 32 |
||
332 | } ;# endif trigger |
||
333 | |||
334 | # Ticket #668: VDBE stack overflow occurs when the left-hand side |
||
335 | # of an IN expression is NULL and the result is used as an integer, not |
||
336 | # as a jump. |
||
337 | # |
||
338 | ifcapable subquery { |
||
339 | do_test misc-8.1 { |
||
340 | execsql { |
||
341 | SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3 |
||
342 | } |
||
343 | } {2} |
||
344 | do_test misc-8.2 { |
||
345 | execsql { |
||
346 | SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2 |
||
347 | } |
||
348 | } {2} |
||
349 | } |
||
350 | |||
351 | finish_test |