wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2003 June 21 |
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: misc2.test,v 1.28 2007/09/12 17:01:45 danielk1977 Exp $ |
||
17 | |||
18 | set testdir [file dirname $argv0] |
||
19 | source $testdir/tester.tcl |
||
20 | |||
21 | # The tests in this file were written before SQLite supported recursive |
||
22 | # trigger invocation, and some tests depend on that to pass. So disable |
||
23 | # recursive triggers for this file. |
||
24 | catchsql { pragma recursive_triggers = off } |
||
25 | |||
26 | ifcapable {trigger} { |
||
27 | # Test for ticket #360 |
||
28 | # |
||
29 | do_test misc2-1.1 { |
||
30 | catchsql { |
||
31 | CREATE TABLE FOO(bar integer); |
||
32 | CREATE TRIGGER foo_insert BEFORE INSERT ON foo BEGIN |
||
33 | SELECT CASE WHEN (NOT new.bar BETWEEN 0 AND 20) |
||
34 | THEN raise(rollback, 'aiieee') END; |
||
35 | END; |
||
36 | INSERT INTO foo(bar) VALUES (1); |
||
37 | } |
||
38 | } {0 {}} |
||
39 | do_test misc2-1.2 { |
||
40 | catchsql { |
||
41 | INSERT INTO foo(bar) VALUES (111); |
||
42 | } |
||
43 | } {1 aiieee} |
||
44 | } ;# endif trigger |
||
45 | |||
46 | # Make sure ROWID works on a view and a subquery. Ticket #364 |
||
47 | # |
||
48 | do_test misc2-2.1 { |
||
49 | execsql { |
||
50 | CREATE TABLE t1(a,b,c); |
||
51 | INSERT INTO t1 VALUES(1,2,3); |
||
52 | CREATE TABLE t2(a,b,c); |
||
53 | INSERT INTO t2 VALUES(7,8,9); |
||
54 | } |
||
55 | } {} |
||
56 | ifcapable subquery { |
||
57 | do_test misc2-2.2 { |
||
58 | execsql { |
||
59 | SELECT rowid, * FROM (SELECT * FROM t1, t2); |
||
60 | } |
||
61 | } {{} 1 2 3 7 8 9} |
||
62 | } |
||
63 | ifcapable view { |
||
64 | do_test misc2-2.3 { |
||
65 | execsql { |
||
66 | CREATE VIEW v1 AS SELECT * FROM t1, t2; |
||
67 | SELECT rowid, * FROM v1; |
||
68 | } |
||
69 | } {{} 1 2 3 7 8 9} |
||
70 | } ;# ifcapable view |
||
71 | |||
72 | # Ticket #2002 and #1952. |
||
73 | ifcapable subquery { |
||
74 | do_test misc2-2.4 { |
||
75 | execsql2 { |
||
76 | SELECT * FROM (SELECT a, b AS 'a', c AS 'a', 4 AS 'a' FROM t1) |
||
77 | } |
||
78 | } {a 1 a:1 2 a:2 3 a:3 4} |
||
79 | } |
||
80 | |||
81 | # Check name binding precedence. Ticket #387 |
||
82 | # |
||
83 | do_test misc2-3.1 { |
||
84 | catchsql { |
||
85 | SELECT t1.b+t2.b AS a, t1.a, t2.a FROM t1, t2 WHERE a==10 |
||
86 | } |
||
87 | } {1 {ambiguous column name: a}} |
||
88 | |||
89 | # Make sure 32-bit integer overflow is handled properly in queries. |
||
90 | # ticket #408 |
||
91 | # |
||
92 | do_test misc2-4.1 { |
||
93 | execsql { |
||
94 | INSERT INTO t1 VALUES(4000000000,'a','b'); |
||
95 | SELECT a FROM t1 WHERE a>1; |
||
96 | } |
||
97 | } {4000000000} |
||
98 | do_test misc2-4.2 { |
||
99 | execsql { |
||
100 | INSERT INTO t1 VALUES(2147483648,'b2','c2'); |
||
101 | INSERT INTO t1 VALUES(2147483647,'b3','c3'); |
||
102 | SELECT a FROM t1 WHERE a>2147483647; |
||
103 | } |
||
104 | } {4000000000 2147483648} |
||
105 | do_test misc2-4.3 { |
||
106 | execsql { |
||
107 | SELECT a FROM t1 WHERE a<2147483648; |
||
108 | } |
||
109 | } {1 2147483647} |
||
110 | do_test misc2-4.4 { |
||
111 | execsql { |
||
112 | SELECT a FROM t1 WHERE a<=2147483648; |
||
113 | } |
||
114 | } {1 2147483648 2147483647} |
||
115 | do_test misc2-4.5 { |
||
116 | execsql { |
||
117 | SELECT a FROM t1 WHERE a<10000000000; |
||
118 | } |
||
119 | } {1 4000000000 2147483648 2147483647} |
||
120 | do_test misc2-4.6 { |
||
121 | execsql { |
||
122 | SELECT a FROM t1 WHERE a<1000000000000 ORDER BY 1; |
||
123 | } |
||
124 | } {1 2147483647 2147483648 4000000000} |
||
125 | |||
126 | # There were some issues with expanding a SrcList object using a call |
||
127 | # to sqliteSrcListAppend() if the SrcList had previously been duplicated |
||
128 | # using a call to sqliteSrcListDup(). Ticket #416. The following test |
||
129 | # makes sure the problem has been fixed. |
||
130 | # |
||
131 | ifcapable view { |
||
132 | do_test misc2-5.1 { |
||
133 | execsql { |
||
134 | CREATE TABLE x(a,b); |
||
135 | CREATE VIEW y AS |
||
136 | SELECT x1.b AS p, x2.b AS q FROM x AS x1, x AS x2 WHERE x1.a=x2.a; |
||
137 | CREATE VIEW z AS |
||
138 | SELECT y1.p, y2.p FROM y AS y1, y AS y2 WHERE y1.q=y2.q; |
||
139 | SELECT * from z; |
||
140 | } |
||
141 | } {} |
||
142 | } |
||
143 | |||
144 | # Make sure we can open a database with an empty filename. What this |
||
145 | # does is store the database in a temporary file that is deleted when |
||
146 | # the database is closed. Ticket #432. |
||
147 | # |
||
148 | do_test misc2-6.1 { |
||
149 | db close |
||
150 | sqlite3 db {} |
||
151 | execsql { |
||
152 | CREATE TABLE t1(a,b); |
||
153 | INSERT INTO t1 VALUES(1,2); |
||
154 | SELECT * FROM t1; |
||
155 | } |
||
156 | } {1 2} |
||
157 | |||
158 | # Make sure we get an error message (not a segfault) on an attempt to |
||
159 | # update a table from within the callback of a select on that same |
||
160 | # table. |
||
161 | # |
||
162 | # 2006-08-16: This has changed. It is now permitted to update |
||
163 | # the table being SELECTed from within the callback of the query. |
||
164 | # |
||
165 | ifcapable tclvar { |
||
166 | do_test misc2-7.1 { |
||
167 | db close |
||
168 | file delete -force test.db |
||
169 | sqlite3 db test.db |
||
170 | execsql { |
||
171 | CREATE TABLE t1(x); |
||
172 | INSERT INTO t1 VALUES(1); |
||
173 | INSERT INTO t1 VALUES(2); |
||
174 | INSERT INTO t1 VALUES(3); |
||
175 | SELECT * FROM t1; |
||
176 | } |
||
177 | } {1 2 3} |
||
178 | do_test misc2-7.2 { |
||
179 | set rc [catch { |
||
180 | db eval {SELECT rowid FROM t1} {} { |
||
181 | db eval "DELETE FROM t1 WHERE rowid=$rowid" |
||
182 | } |
||
183 | } msg] |
||
184 | lappend rc $msg |
||
185 | } {0 {}} |
||
186 | do_test misc2-7.3 { |
||
187 | execsql {SELECT * FROM t1} |
||
188 | } {} |
||
189 | do_test misc2-7.4 { |
||
190 | execsql { |
||
191 | DELETE FROM t1; |
||
192 | INSERT INTO t1 VALUES(1); |
||
193 | INSERT INTO t1 VALUES(2); |
||
194 | INSERT INTO t1 VALUES(3); |
||
195 | INSERT INTO t1 VALUES(4); |
||
196 | } |
||
197 | db eval {SELECT rowid, x FROM t1} { |
||
198 | if {$x & 1} { |
||
199 | db eval {DELETE FROM t1 WHERE rowid=$rowid} |
||
200 | } |
||
201 | } |
||
202 | execsql {SELECT * FROM t1} |
||
203 | } {2 4} |
||
204 | do_test misc2-7.5 { |
||
205 | execsql { |
||
206 | DELETE FROM t1; |
||
207 | INSERT INTO t1 VALUES(1); |
||
208 | INSERT INTO t1 VALUES(2); |
||
209 | INSERT INTO t1 VALUES(3); |
||
210 | INSERT INTO t1 VALUES(4); |
||
211 | } |
||
212 | db eval {SELECT rowid, x FROM t1} { |
||
213 | if {$x & 1} { |
||
214 | db eval {DELETE FROM t1 WHERE rowid=$rowid+1} |
||
215 | } |
||
216 | } |
||
217 | execsql {SELECT * FROM t1} |
||
218 | } {1 3} |
||
219 | do_test misc2-7.6 { |
||
220 | execsql { |
||
221 | DELETE FROM t1; |
||
222 | INSERT INTO t1 VALUES(1); |
||
223 | INSERT INTO t1 VALUES(2); |
||
224 | INSERT INTO t1 VALUES(3); |
||
225 | INSERT INTO t1 VALUES(4); |
||
226 | } |
||
227 | db eval {SELECT rowid, x FROM t1} { |
||
228 | if {$x & 1} { |
||
229 | db eval {DELETE FROM t1} |
||
230 | } |
||
231 | } |
||
232 | execsql {SELECT * FROM t1} |
||
233 | } {} |
||
234 | do_test misc2-7.7 { |
||
235 | execsql { |
||
236 | DELETE FROM t1; |
||
237 | INSERT INTO t1 VALUES(1); |
||
238 | INSERT INTO t1 VALUES(2); |
||
239 | INSERT INTO t1 VALUES(3); |
||
240 | INSERT INTO t1 VALUES(4); |
||
241 | } |
||
242 | db eval {SELECT rowid, x FROM t1} { |
||
243 | if {$x & 1} { |
||
244 | db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid} |
||
245 | } |
||
246 | } |
||
247 | execsql {SELECT * FROM t1} |
||
248 | } {101 2 103 4} |
||
249 | do_test misc2-7.8 { |
||
250 | execsql { |
||
251 | DELETE FROM t1; |
||
252 | INSERT INTO t1 VALUES(1); |
||
253 | } |
||
254 | db eval {SELECT rowid, x FROM t1} { |
||
255 | if {$x<10} { |
||
256 | db eval {INSERT INTO t1 VALUES($x+1)} |
||
257 | } |
||
258 | } |
||
259 | execsql {SELECT * FROM t1} |
||
260 | } {1 2 3 4 5 6 7 8 9 10} |
||
261 | |||
262 | # Repeat the tests 7.1 through 7.8 about but this time do the SELECTs |
||
263 | # in reverse order so that we exercise the sqlite3BtreePrev() routine |
||
264 | # instead of sqlite3BtreeNext() |
||
265 | # |
||
266 | do_test misc2-7.11 { |
||
267 | db close |
||
268 | file delete -force test.db |
||
269 | sqlite3 db test.db |
||
270 | execsql { |
||
271 | CREATE TABLE t1(x); |
||
272 | INSERT INTO t1 VALUES(1); |
||
273 | INSERT INTO t1 VALUES(2); |
||
274 | INSERT INTO t1 VALUES(3); |
||
275 | SELECT * FROM t1; |
||
276 | } |
||
277 | } {1 2 3} |
||
278 | do_test misc2-7.12 { |
||
279 | set rc [catch { |
||
280 | db eval {SELECT rowid FROM t1 ORDER BY rowid DESC} {} { |
||
281 | db eval "DELETE FROM t1 WHERE rowid=$rowid" |
||
282 | } |
||
283 | } msg] |
||
284 | lappend rc $msg |
||
285 | } {0 {}} |
||
286 | do_test misc2-7.13 { |
||
287 | execsql {SELECT * FROM t1} |
||
288 | } {} |
||
289 | do_test misc2-7.14 { |
||
290 | execsql { |
||
291 | DELETE FROM t1; |
||
292 | INSERT INTO t1 VALUES(1); |
||
293 | INSERT INTO t1 VALUES(2); |
||
294 | INSERT INTO t1 VALUES(3); |
||
295 | INSERT INTO t1 VALUES(4); |
||
296 | } |
||
297 | db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} { |
||
298 | if {$x & 1} { |
||
299 | db eval {DELETE FROM t1 WHERE rowid=$rowid} |
||
300 | } |
||
301 | } |
||
302 | execsql {SELECT * FROM t1} |
||
303 | } {2 4} |
||
304 | do_test misc2-7.15 { |
||
305 | execsql { |
||
306 | DELETE FROM t1; |
||
307 | INSERT INTO t1 VALUES(1); |
||
308 | INSERT INTO t1 VALUES(2); |
||
309 | INSERT INTO t1 VALUES(3); |
||
310 | INSERT INTO t1 VALUES(4); |
||
311 | } |
||
312 | db eval {SELECT rowid, x FROM t1} { |
||
313 | if {$x & 1} { |
||
314 | db eval {DELETE FROM t1 WHERE rowid=$rowid+1} |
||
315 | } |
||
316 | } |
||
317 | execsql {SELECT * FROM t1} |
||
318 | } {1 3} |
||
319 | do_test misc2-7.16 { |
||
320 | execsql { |
||
321 | DELETE FROM t1; |
||
322 | INSERT INTO t1 VALUES(1); |
||
323 | INSERT INTO t1 VALUES(2); |
||
324 | INSERT INTO t1 VALUES(3); |
||
325 | INSERT INTO t1 VALUES(4); |
||
326 | } |
||
327 | db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} { |
||
328 | if {$x & 1} { |
||
329 | db eval {DELETE FROM t1} |
||
330 | } |
||
331 | } |
||
332 | execsql {SELECT * FROM t1} |
||
333 | } {} |
||
334 | do_test misc2-7.17 { |
||
335 | execsql { |
||
336 | DELETE FROM t1; |
||
337 | INSERT INTO t1 VALUES(1); |
||
338 | INSERT INTO t1 VALUES(2); |
||
339 | INSERT INTO t1 VALUES(3); |
||
340 | INSERT INTO t1 VALUES(4); |
||
341 | } |
||
342 | db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} { |
||
343 | if {$x & 1} { |
||
344 | db eval {UPDATE t1 SET x=x+100 WHERE rowid=$rowid} |
||
345 | } |
||
346 | } |
||
347 | execsql {SELECT * FROM t1} |
||
348 | } {101 2 103 4} |
||
349 | do_test misc2-7.18 { |
||
350 | execsql { |
||
351 | DELETE FROM t1; |
||
352 | INSERT INTO t1(rowid,x) VALUES(10,10); |
||
353 | } |
||
354 | db eval {SELECT rowid, x FROM t1 ORDER BY rowid DESC} { |
||
355 | if {$x>1} { |
||
356 | db eval {INSERT INTO t1(rowid,x) VALUES($x-1,$x-1)} |
||
357 | } |
||
358 | } |
||
359 | execsql {SELECT * FROM t1} |
||
360 | } {1 2 3 4 5 6 7 8 9 10} |
||
361 | } |
||
362 | |||
363 | db close |
||
364 | file delete -force test.db |
||
365 | sqlite3 db test.db |
||
366 | catchsql { pragma recursive_triggers = off } |
||
367 | |||
368 | # Ticket #453. If the SQL ended with "-", the tokenizer was calling that |
||
369 | # an incomplete token, which caused problem. The solution was to just call |
||
370 | # it a minus sign. |
||
371 | # |
||
372 | do_test misc2-8.1 { |
||
373 | catchsql {-} |
||
374 | } {1 {near "-": syntax error}} |
||
375 | |||
376 | # Ticket #513. Make sure the VDBE stack does not grow on a 3-way join. |
||
377 | # |
||
378 | ifcapable tempdb { |
||
379 | do_test misc2-9.1 { |
||
380 | execsql { |
||
381 | BEGIN; |
||
382 | CREATE TABLE counts(n INTEGER PRIMARY KEY); |
||
383 | INSERT INTO counts VALUES(0); |
||
384 | INSERT INTO counts VALUES(1); |
||
385 | INSERT INTO counts SELECT n+2 FROM counts; |
||
386 | INSERT INTO counts SELECT n+4 FROM counts; |
||
387 | INSERT INTO counts SELECT n+8 FROM counts; |
||
388 | COMMIT; |
||
389 | |||
390 | CREATE TEMP TABLE x AS |
||
391 | SELECT dim1.n, dim2.n, dim3.n |
||
392 | FROM counts AS dim1, counts AS dim2, counts AS dim3 |
||
393 | WHERE dim1.n<10 AND dim2.n<10 AND dim3.n<10; |
||
394 | |||
395 | SELECT count(*) FROM x; |
||
396 | } |
||
397 | } {1000} |
||
398 | do_test misc2-9.2 { |
||
399 | execsql { |
||
400 | DROP TABLE x; |
||
401 | CREATE TEMP TABLE x AS |
||
402 | SELECT dim1.n, dim2.n, dim3.n |
||
403 | FROM counts AS dim1, counts AS dim2, counts AS dim3 |
||
404 | WHERE dim1.n>=6 AND dim2.n>=6 AND dim3.n>=6; |
||
405 | |||
406 | SELECT count(*) FROM x; |
||
407 | } |
||
408 | } {1000} |
||
409 | do_test misc2-9.3 { |
||
410 | execsql { |
||
411 | DROP TABLE x; |
||
412 | CREATE TEMP TABLE x AS |
||
413 | SELECT dim1.n, dim2.n, dim3.n, dim4.n |
||
414 | FROM counts AS dim1, counts AS dim2, counts AS dim3, counts AS dim4 |
||
415 | WHERE dim1.n<5 AND dim2.n<5 AND dim3.n<5 AND dim4.n<5; |
||
416 | |||
417 | SELECT count(*) FROM x; |
||
418 | } |
||
419 | } [expr 5*5*5*5] |
||
420 | } |
||
421 | |||
422 | # Ticket #1229. Sometimes when a "NEW.X" appears in a SELECT without |
||
423 | # a FROM clause deep within a trigger, the code generator is unable to |
||
424 | # trace the NEW.X back to an original table and thus figure out its |
||
425 | # declared datatype. |
||
426 | # |
||
427 | # The SQL code below was causing a segfault. |
||
428 | # |
||
429 | ifcapable subquery&&trigger { |
||
430 | do_test misc2-10.1 { |
||
431 | execsql { |
||
432 | CREATE TABLE t1229(x); |
||
433 | CREATE TRIGGER r1229 BEFORE INSERT ON t1229 BEGIN |
||
434 | INSERT INTO t1229 SELECT y FROM (SELECT new.x y); |
||
435 | END; |
||
436 | INSERT INTO t1229 VALUES(1); |
||
437 | } |
||
438 | } {} |
||
439 | } |
||
440 | |||
441 | finish_test |