| blob | 57fc931f7c3d945d6e459cbb6bd50ff6a5ff3b6a |
1 # 2007 May 8
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 #
12 # This file contains tests to verify that the limits defined in
13 # sqlite source file limits.h are enforced.
14 #
15 # $Id: sqllimits1.test,v 1.33 2009/06/25 01:47:12 drh Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # Verify that the default per-connection limits are the same as
21 # the compile-time hard limits.
22 #
23 sqlite3 db2 :memory:
24 do_test sqllimits1-1.1 {
25 sqlite3_limit db SQLITE_LIMIT_LENGTH -1
26 } $SQLITE_MAX_LENGTH
27 do_test sqllimits1-1.2 {
28 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
29 } $SQLITE_MAX_SQL_LENGTH
30 do_test sqllimits1-1.3 {
31 sqlite3_limit db SQLITE_LIMIT_COLUMN -1
32 } $SQLITE_MAX_COLUMN
33 do_test sqllimits1-1.4 {
34 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
35 } $SQLITE_MAX_EXPR_DEPTH
36 do_test sqllimits1-1.5 {
37 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
38 } $SQLITE_MAX_COMPOUND_SELECT
39 do_test sqllimits1-1.6 {
40 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
41 } $SQLITE_MAX_VDBE_OP
42 do_test sqllimits1-1.7 {
43 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
44 } $SQLITE_MAX_FUNCTION_ARG
45 do_test sqllimits1-1.8 {
46 sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
47 } $SQLITE_MAX_ATTACHED
48 do_test sqllimits1-1.9 {
49 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
50 } $SQLITE_MAX_LIKE_PATTERN_LENGTH
51 do_test sqllimits1-1.10 {
52 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
53 } $SQLITE_MAX_VARIABLE_NUMBER
54 do_test sqllimits1-1.11 {
55 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH -1
56 } $SQLITE_MAX_TRIGGER_DEPTH
57 do_test sqllimits1-1.12 {
58 sqlite3_limit db SQLITE_LIMIT_WORKER_THREADS 99999
59 sqlite3_limit db SQLITE_LIMIT_WORKER_THREADS -1
60 } $SQLITE_MAX_WORKER_THREADS
62 # Limit parameters out of range.
63 #
64 do_test sqllimits1-1.20 {
65 sqlite3_limit db SQLITE_LIMIT_TOOSMALL 123
66 } {-1}
67 do_test sqllimits1-1.21 {
68 sqlite3_limit db SQLITE_LIMIT_TOOSMALL 123
69 } {-1}
70 do_test sqllimits1-1.22 {
71 sqlite3_limit db SQLITE_LIMIT_TOOBIG 123
72 } {-1}
73 do_test sqllimits1-1.23 {
74 sqlite3_limit db SQLITE_LIMIT_TOOBIG 123
75 } {-1}
78 # Decrease all limits by half. Verify that the new limits take.
79 #
80 if {$SQLITE_MAX_LENGTH>=2} {
81 do_test sqllimits1-2.1.1 {
82 sqlite3_limit db SQLITE_LIMIT_LENGTH \
83 [expr {$::SQLITE_MAX_LENGTH/2}]
84 } $SQLITE_MAX_LENGTH
85 do_test sqllimits1-2.1.2 {
86 sqlite3_limit db SQLITE_LIMIT_LENGTH -1
87 } [expr {$SQLITE_MAX_LENGTH/2}]
88 }
89 if {$SQLITE_MAX_SQL_LENGTH>=2} {
90 do_test sqllimits1-2.2.1 {
91 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH \
92 [expr {$::SQLITE_MAX_SQL_LENGTH/2}]
93 } $SQLITE_MAX_SQL_LENGTH
94 do_test sqllimits1-2.2.2 {
95 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
96 } [expr {$SQLITE_MAX_SQL_LENGTH/2}]
97 }
98 if {$SQLITE_MAX_COLUMN>=2} {
99 do_test sqllimits1-2.3.1 {
100 sqlite3_limit db SQLITE_LIMIT_COLUMN \
101 [expr {$::SQLITE_MAX_COLUMN/2}]
102 } $SQLITE_MAX_COLUMN
103 do_test sqllimits1-2.3.2 {
104 sqlite3_limit db SQLITE_LIMIT_COLUMN -1
105 } [expr {$SQLITE_MAX_COLUMN/2}]
106 }
107 if {$SQLITE_MAX_EXPR_DEPTH>=2} {
108 do_test sqllimits1-2.4.1 {
109 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH \
110 [expr {$::SQLITE_MAX_EXPR_DEPTH/2}]
111 } $SQLITE_MAX_EXPR_DEPTH
112 do_test sqllimits1-2.4.2 {
113 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
114 } [expr {$SQLITE_MAX_EXPR_DEPTH/2}]
115 }
116 if {$SQLITE_MAX_COMPOUND_SELECT>=2} {
117 do_test sqllimits1-2.5.1 {
118 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT \
119 [expr {$::SQLITE_MAX_COMPOUND_SELECT/2}]
120 } $SQLITE_MAX_COMPOUND_SELECT
121 do_test sqllimits1-2.5.2 {
122 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
123 } [expr {$SQLITE_MAX_COMPOUND_SELECT/2}]
124 }
125 if {$SQLITE_MAX_VDBE_OP>=2} {
126 do_test sqllimits1-2.6.1 {
127 sqlite3_limit db SQLITE_LIMIT_VDBE_OP \
128 [expr {$::SQLITE_MAX_VDBE_OP/2}]
129 } $SQLITE_MAX_VDBE_OP
130 do_test sqllimits1-2.6.2 {
131 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
132 } [expr {$SQLITE_MAX_VDBE_OP/2}]
133 }
134 if {$SQLITE_MAX_FUNCTION_ARG>=2} {
135 do_test sqllimits1-2.7.1 {
136 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG \
137 [expr {$::SQLITE_MAX_FUNCTION_ARG/2}]
138 } $SQLITE_MAX_FUNCTION_ARG
139 do_test sqllimits1-2.7.2 {
140 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
141 } [expr {$SQLITE_MAX_FUNCTION_ARG/2}]
142 }
143 if {$SQLITE_MAX_ATTACHED>=2} {
144 do_test sqllimits1-2.8.1 {
145 sqlite3_limit db SQLITE_LIMIT_ATTACHED \
146 [expr {$::SQLITE_MAX_ATTACHED/2}]
147 } $SQLITE_MAX_ATTACHED
148 do_test sqllimits1-2.8.2 {
149 sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
150 } [expr {$SQLITE_MAX_ATTACHED/2}]
151 }
152 if {$SQLITE_MAX_LIKE_PATTERN_LENGTH>=2} {
153 do_test sqllimits1-2.9.1 {
154 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH \
155 [expr {$::SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]
156 } $SQLITE_MAX_LIKE_PATTERN_LENGTH
157 do_test sqllimits1-2.9.2 {
158 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
159 } [expr {$SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]
160 }
161 if {$SQLITE_MAX_VARIABLE_NUMBER>=2} {
162 do_test sqllimits1-2.10.1 {
163 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER \
164 [expr {$::SQLITE_MAX_VARIABLE_NUMBER/2}]
165 } $SQLITE_MAX_VARIABLE_NUMBER
166 do_test sqllimits1-2.10.2 {
167 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
168 } [expr {$SQLITE_MAX_VARIABLE_NUMBER/2}]
169 }
171 # In a separate database connection, verify that the limits are unchanged.
172 #
173 do_test sqllimits1-3.1 {
174 sqlite3_limit db2 SQLITE_LIMIT_LENGTH -1
175 } $SQLITE_MAX_LENGTH
176 do_test sqllimits1-3.2 {
177 sqlite3_limit db2 SQLITE_LIMIT_SQL_LENGTH -1
178 } $SQLITE_MAX_SQL_LENGTH
179 do_test sqllimits1-3.3 {
180 sqlite3_limit db2 SQLITE_LIMIT_COLUMN -1
181 } $SQLITE_MAX_COLUMN
182 do_test sqllimits1-3.4 {
183 sqlite3_limit db2 SQLITE_LIMIT_EXPR_DEPTH -1
184 } $SQLITE_MAX_EXPR_DEPTH
185 do_test sqllimits1-3.5 {
186 sqlite3_limit db2 SQLITE_LIMIT_COMPOUND_SELECT -1
187 } $SQLITE_MAX_COMPOUND_SELECT
188 do_test sqllimits1-3.6 {
189 sqlite3_limit db2 SQLITE_LIMIT_VDBE_OP -1
190 } $SQLITE_MAX_VDBE_OP
191 do_test sqllimits1-3.7 {
192 sqlite3_limit db2 SQLITE_LIMIT_FUNCTION_ARG -1
193 } $SQLITE_MAX_FUNCTION_ARG
194 do_test sqllimits1-3.8 {
195 sqlite3_limit db2 SQLITE_LIMIT_ATTACHED -1
196 } $SQLITE_MAX_ATTACHED
197 do_test sqllimits1-3.9 {
198 sqlite3_limit db2 SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
199 } $SQLITE_MAX_LIKE_PATTERN_LENGTH
200 do_test sqllimits1-3.10 {
201 sqlite3_limit db2 SQLITE_LIMIT_VARIABLE_NUMBER -1
202 } $SQLITE_MAX_VARIABLE_NUMBER
203 db2 close
205 # Attempt to set all limits to the maximum 32-bit integer. Verify
206 # that the limit does not exceed the compile-time upper bound.
207 #
208 do_test sqllimits1-4.1.1 {
209 sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff
210 sqlite3_limit db SQLITE_LIMIT_LENGTH -1
211 } $SQLITE_MAX_LENGTH
212 do_test sqllimits1-4.2.1 {
213 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff
214 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
215 } $SQLITE_MAX_SQL_LENGTH
216 do_test sqllimits1-4.3.1 {
217 sqlite3_limit db SQLITE_LIMIT_COLUMN 0x7fffffff
218 sqlite3_limit db SQLITE_LIMIT_COLUMN -1
219 } $SQLITE_MAX_COLUMN
220 do_test sqllimits1-4.4.1 {
221 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH 0x7fffffff
222 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
223 } $SQLITE_MAX_EXPR_DEPTH
224 do_test sqllimits1-4.5.1 {
225 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0x7fffffff
226 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
227 } $SQLITE_MAX_COMPOUND_SELECT
228 do_test sqllimits1-4.6.1 {
229 sqlite3_limit db SQLITE_LIMIT_VDBE_OP 0x7fffffff
230 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
231 } $SQLITE_MAX_VDBE_OP
232 do_test sqllimits1-4.7.1 {
233 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG 0x7fffffff
234 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
235 } $SQLITE_MAX_FUNCTION_ARG
236 do_test sqllimits1-4.8.1 {
237 sqlite3_limit db SQLITE_LIMIT_ATTACHED 0x7fffffff
238 sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
239 } $SQLITE_MAX_ATTACHED
240 do_test sqllimits1-4.9.1 {
241 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH 0x7fffffff
242 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
243 } $SQLITE_MAX_LIKE_PATTERN_LENGTH
244 do_test sqllimits1-4.10.1 {
245 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER 0x7fffffff
246 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
247 } $SQLITE_MAX_VARIABLE_NUMBER
249 #--------------------------------------------------------------------
250 # Test cases sqllimits1-5.* test that the SQLITE_MAX_LENGTH limit
251 # is enforced.
252 #
253 db close
254 sqlite3 db test.db
255 set LARGESIZE 99999
256 set SQLITE_LIMIT_LENGTH 100000
257 sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH
259 do_test sqllimits1-5.1.1 {
260 catchsql { SELECT randomblob(2147483647) }
261 } {1 {string or blob too big}}
262 do_test sqllimits1-5.1.2 {
263 catchsql { SELECT zeroblob(2147483647) }
264 } {1 {string or blob too big}}
266 do_test sqllimits1-5.2 {
267 catchsql { SELECT LENGTH(randomblob($::LARGESIZE)) }
268 } [list 0 $LARGESIZE]
270 do_test sqllimits1-5.3 {
271 catchsql { SELECT quote(randomblob($::LARGESIZE)) }
272 } {1 {string or blob too big}}
274 do_test sqllimits1-5.4 {
275 catchsql { SELECT LENGTH(zeroblob($::LARGESIZE)) }
276 } [list 0 $LARGESIZE]
278 do_test sqllimits1-5.5 {
279 catchsql { SELECT quote(zeroblob($::LARGESIZE)) }
280 } {1 {string or blob too big}}
282 do_test sqllimits1-5.6 {
283 catchsql { SELECT zeroblob(-1) }
284 } {0 {{}}}
286 do_test sqllimits1-5.9 {
287 set ::str [string repeat A 65537]
288 set ::rep [string repeat B 65537]
289 catchsql { SELECT replace($::str, 'A', $::rep) }
290 } {1 {string or blob too big}}
292 do_test sqllimits1-5.10 {
293 set ::str [string repeat %J 2100]
294 catchsql { SELECT strftime($::str, '2003-10-31') }
295 } {1 {string or blob too big}}
297 do_test sqllimits1-5.11 {
298 set ::str1 [string repeat A [expr {$SQLITE_LIMIT_LENGTH - 10}]]
299 set ::str2 [string repeat B [expr {$SQLITE_LIMIT_LENGTH - 10}]]
300 catchsql { SELECT $::str1 || $::str2 }
301 } {1 {string or blob too big}}
303 do_test sqllimits1-5.12 {
304 set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]
305 catchsql { SELECT quote($::str1) }
306 } {1 {string or blob too big}}
308 do_test sqllimits1-5.13 {
309 set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]
310 catchsql { SELECT hex($::str1) }
311 } {1 {string or blob too big}}
313 do_test sqllimits1-5.14.1 {
314 set ::STMT [sqlite3_prepare db "SELECT ?" -1 TAIL]
315 sqlite3_bind_zeroblob $::STMT 1 [expr {$SQLITE_LIMIT_LENGTH + 1}]
316 } {}
317 do_test sqllimits1-5.14.2 {
318 sqlite3_step $::STMT
319 } {SQLITE_ERROR}
320 do_test sqllimits1-5.14.3 {
321 sqlite3_reset $::STMT
322 } {SQLITE_TOOBIG}
323 do_test sqllimits1-5.14.4 {
324 set np1 [expr {$SQLITE_LIMIT_LENGTH + 1}]
325 set ::str1 [string repeat A $np1]
326 catch {sqlite3_bind_text $::STMT 1 $::str1 -1} res
327 set res
328 } {SQLITE_TOOBIG}
329 ifcapable utf16 {
330 do_test sqllimits1-5.14.5 {
331 catch {sqlite3_bind_text16 $::STMT 1 $::str1 -1} res
332 set res
333 } {SQLITE_TOOBIG}
334 }
335 do_test sqllimits1-5.14.6 {
336 catch {sqlite3_bind_text $::STMT 1 $::str1 $np1} res
337 set res
338 } {SQLITE_TOOBIG}
339 ifcapable utf16 {
340 do_test sqllimits1-5.14.7 {
341 catch {sqlite3_bind_text16 $::STMT 1 $::str1 $np1} res
342 set res
343 } {SQLITE_TOOBIG}
344 }
345 do_test sqllimits1-5.14.8 {
346 set n [expr {$np1-1}]
347 catch {sqlite3_bind_text $::STMT 1 $::str1 $n} res
348 set res
349 } {}
350 do_test sqllimits1-5.14.9 {
351 catch {sqlite3_bind_text16 $::STMT 1 $::str1 $n} res
352 set res
353 } {}
354 sqlite3_finalize $::STMT
356 do_test sqllimits1-5.15 {
357 execsql {
358 CREATE TABLE t4(x);
359 INSERT INTO t4 VALUES(1);
360 INSERT INTO t4 VALUES(2);
361 INSERT INTO t4 SELECT 2+x FROM t4;
362 }
363 catchsql {
364 SELECT group_concat(hex(randomblob(20000))) FROM t4;
365 }
366 } {1 {string or blob too big}}
367 db eval {DROP TABLE t4}
369 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff
370 set strvalue [string repeat A $::SQLITE_LIMIT_LENGTH]
371 do_test sqllimits1-5.16 {
372 catchsql "SELECT '$strvalue'"
373 } [list 0 $strvalue]
374 do_test sqllimits1-5.17.1 {
375 catchsql "SELECT 'A$strvalue'"
376 } [list 1 {string or blob too big}]
377 do_test sqllimits1-5.17.2 {
378 sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff
379 catchsql {SELECT 'A' || $::strvalue}
380 } [list 0 A$strvalue]
381 do_test sqllimits1-5.17.3 {
382 sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH
383 catchsql {SELECT 'A' || $::strvalue}
384 } [list 1 {string or blob too big}]
385 set blobvalue [string repeat 41 $::SQLITE_LIMIT_LENGTH]
386 do_test sqllimits1-5.18 {
387 catchsql "SELECT x'$blobvalue'"
388 } [list 0 $strvalue]
389 do_test sqllimits1-5.19 {
390 catchsql "SELECT '41$blobvalue'"
391 } [list 1 {string or blob too big}]
392 unset blobvalue
394 ifcapable datetime {
395 set strvalue [string repeat D [expr {$SQLITE_LIMIT_LENGTH-12}]]
396 do_test sqllimits1-5.20 {
397 catchsql {SELECT strftime('%Y ' || $::strvalue, '2008-01-02')}
398 } [list 0 [list "2008 $strvalue"]]
399 do_test sqllimits1-5.21 {
400 catchsql {SELECT strftime('%Y-%m-%d ' || $::strvalue, '2008-01-02')}
401 } {1 {string or blob too big}}
402 }
403 unset strvalue
405 #--------------------------------------------------------------------
406 # Test cases sqllimits1-6.* test that the SQLITE_MAX_SQL_LENGTH limit
407 # is enforced.
408 #
409 do_test sqllimits1-6.1 {
410 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000
411 set sql "SELECT 1 WHERE 1==1"
412 set tail " /* A comment to take up space in order to make the string\
413 longer without increasing the expression depth */\
414 AND 1 == 1"
415 set N [expr {(50000 / [string length $tail])+1}]
416 append sql [string repeat $tail $N]
417 catchsql $sql
418 } {1 {string or blob too big}}
419 do_test sqllimits1-6.3 {
420 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000
421 set sql "SELECT 1 WHERE 1==1"
422 set tail " /* A comment to take up space in order to make the string\
423 longer without increasing the expression depth */\
424 AND 1 == 1"
425 set N [expr {(50000 / [string length $tail])+1}]
426 append sql [string repeat $tail $N]
427 set nbytes [string length $sql]
428 append sql { AND 0}
429 set rc [catch {sqlite3_prepare db $sql $nbytes TAIL} STMT]
430 lappend rc $STMT
431 } {1 {(18) statement too long}}
432 do_test sqllimits1-6.4 {
433 sqlite3_errmsg db
434 } {statement too long}
436 #--------------------------------------------------------------------
437 # Test cases sqllimits1-7.* test that the limit set using the
438 # max_page_count pragma.
439 #
440 do_test sqllimits1-7.1 {
441 execsql {
442 PRAGMA max_page_count = 1000;
443 }
444 } {1000}
445 do_test sqllimits1-7.2 {
446 execsql { CREATE TABLE trig (a INTEGER, b INTEGER); }
448 # Set up a tree of triggers to fire when a row is inserted
449 # into table "trig".
450 #
451 # INSERT -> insert_b -> update_b -> insert_a -> update_a (chain 1)
452 # -> update_a -> insert_a -> update_b (chain 2)
453 # -> insert_a -> update_b -> insert_b -> update_a (chain 3)
454 # -> update_a -> insert_b -> update_b (chain 4)
455 #
456 # Table starts with N rows.
457 #
458 # Chain 1: insert_b (update N rows)
459 # -> update_b (insert 1 rows)
460 # -> insert_a (update N rows)
461 # -> update_a (insert 1 rows)
462 #
463 # chains 2, 3 and 4 are similar. Each inserts more than N^2 rows, where
464 # N is the number of rows at the conclusion of the previous chain.
465 #
466 # Therefore, a single insert adds (N^16 plus some) rows to the database.
467 # A really long loop...
468 #
469 execsql {
470 CREATE TRIGGER update_b BEFORE UPDATE ON trig
471 FOR EACH ROW BEGIN
472 INSERT INTO trig VALUES (65, 'update_b');
473 END;
475 CREATE TRIGGER update_a AFTER UPDATE ON trig
476 FOR EACH ROW BEGIN
477 INSERT INTO trig VALUES (65, 'update_a');
478 END;
480 CREATE TRIGGER insert_b BEFORE INSERT ON trig
481 FOR EACH ROW BEGIN
482 UPDATE trig SET a = 1;
483 END;
485 CREATE TRIGGER insert_a AFTER INSERT ON trig
486 FOR EACH ROW BEGIN
487 UPDATE trig SET a = 1;
488 END;
489 }
490 } {}
492 do_test sqllimits1-7.3 {
493 execsql {
494 INSERT INTO trig VALUES (1,1);
495 }
496 } {}
498 do_test sqllimits1-7.4 {
499 execsql {
500 SELECT COUNT(*) FROM trig;
501 }
502 } {7}
504 # This tries to insert so many rows it fills up the database (limited
505 # to 1MB, so not that noteworthy an achievement).
506 #
507 do_test sqllimits1-7.5 {
508 catchsql {
509 INSERT INTO trig VALUES (1,10);
510 }
511 } {1 {database or disk is full}}
513 do_test sqllimits1-7.6 {
514 catchsql {
515 SELECT COUNT(*) FROM trig;
516 }
517 } {0 7}
519 # Now check the response of the library to opening a file larger than
520 # the current max_page_count value. The response is to change the
521 # internal max_page_count value to match the actual size of the file.
522 if {[db eval {PRAGMA auto_vacuum}]} {
523 set fsize 1700
524 } else {
525 set fsize 1691
526 }
527 do_test sqllimits1-7.7.1 {
528 execsql {
529 PRAGMA max_page_count = 1000000;
530 CREATE TABLE abc(a, b, c);
531 INSERT INTO abc VALUES(1, 2, 3);
532 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
533 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
534 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
535 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
536 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
537 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
538 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
539 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
540 INSERT INTO abc SELECT a, b, c FROM abc;
541 INSERT INTO abc SELECT b, a, c FROM abc;
542 INSERT INTO abc SELECT c, b, a FROM abc;
543 }
544 expr [file size test.db] / 1024
545 } $fsize
546 do_test sqllimits1-7.7.2 {
547 db close
548 sqlite3 db test.db
549 execsql {
550 PRAGMA max_page_count = 1000;
551 }
552 execsql {
553 SELECT count(*) FROM sqlite_master;
554 }
555 } {6}
556 do_test sqllimits1-7.7.3 {
557 execsql {
558 PRAGMA max_page_count;
559 }
560 } $fsize
561 do_test sqllimits1-7.7.4 {
562 execsql {
563 DROP TABLE abc;
564 }
565 } {}
567 #--------------------------------------------------------------------
568 # Test cases sqllimits1-8.* test the SQLITE_MAX_COLUMN limit.
569 #
570 set SQLITE_LIMIT_COLUMN 200
571 sqlite3_limit db SQLITE_LIMIT_COLUMN $SQLITE_LIMIT_COLUMN
572 do_test sqllimits1-8.1 {
573 # Columns in a table.
574 set cols [list]
575 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
576 lappend cols "c$i"
577 }
578 catchsql "CREATE TABLE t([join $cols ,])"
579 } {1 {too many columns on t}}
581 do_test sqllimits1-8.2 {
582 # Columns in the result-set of a SELECT.
583 set cols [list]
584 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
585 lappend cols "sql AS sql$i"
586 }
587 catchsql "SELECT [join $cols ,] FROM sqlite_master"
588 } {1 {too many columns in result set}}
590 do_test sqllimits1-8.3 {
591 # Columns in the result-set of a sub-SELECT.
592 set cols [list]
593 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
594 lappend cols "sql AS sql$i"
595 }
596 catchsql "SELECT sql4 FROM (SELECT [join $cols ,] FROM sqlite_master)"
597 } {1 {too many columns in result set}}
599 do_test sqllimits1-8.4 {
600 # Columns in an index.
601 set cols [list]
602 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
603 lappend cols c
604 }
605 set sql1 "CREATE TABLE t1(c);"
606 set sql2 "CREATE INDEX i1 ON t1([join $cols ,]);"
607 catchsql "$sql1 ; $sql2"
608 } {1 {too many columns in index}}
610 do_test sqllimits1-8.5 {
611 # Columns in a GROUP BY clause.
612 catchsql "SELECT * FROM t1 GROUP BY [join $cols ,]"
613 } {1 {too many terms in GROUP BY clause}}
615 do_test sqllimits1-8.6 {
616 # Columns in an ORDER BY clause.
617 catchsql "SELECT * FROM t1 ORDER BY [join $cols ,]"
618 } {1 {too many terms in ORDER BY clause}}
620 do_test sqllimits1-8.7 {
621 # Assignments in an UPDATE statement.
622 set cols [list]
623 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
624 lappend cols "c = 1"
625 }
626 catchsql "UPDATE t1 SET [join $cols ,];"
627 } {1 {too many columns in set list}}
629 do_test sqllimits1-8.8 {
630 # Columns in a view definition:
631 set cols [list]
632 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
633 lappend cols "c$i"
634 }
635 catchsql "CREATE VIEW v1 AS SELECT [join $cols ,] FROM t1;"
636 } {1 {too many columns in result set}}
638 do_test sqllimits1-8.9 {
639 # Columns in a view definition (testing * expansion):
640 set cols [list]
641 for {set i 0} {$i < $SQLITE_LIMIT_COLUMN} {incr i} {
642 lappend cols "c$i"
643 }
644 catchsql "CREATE TABLE t2([join $cols ,])"
645 catchsql "CREATE VIEW v1 AS SELECT *, c1 AS o FROM t2;"
646 } {1 {too many columns in result set}}
647 do_test sqllimits1-8.10 {
648 # ORDER BY columns
649 set cols [list]
650 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
651 lappend cols c
652 }
653 set sql "SELECT c FROM t1 ORDER BY [join $cols ,]"
654 catchsql $sql
655 } {1 {too many terms in ORDER BY clause}}
656 do_test sqllimits1-8.11 {
657 # ORDER BY columns
658 set cols [list]
659 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
660 lappend cols [expr {$i%3 + 1}]
661 }
662 set sql "SELECT c, c+1, c+2 FROM t1 UNION SELECT c-1, c-2, c-3 FROM t1"
663 append sql " ORDER BY [join $cols ,]"
664 catchsql $sql
665 } {1 {too many terms in ORDER BY clause}}
668 #--------------------------------------------------------------------
669 # These tests - sqllimits1-9.* - test that the SQLITE_LIMIT_EXPR_DEPTH
670 # limit is enforced. The limit refers to the number of terms in
671 # the expression.
672 #
673 if {$SQLITE_MAX_EXPR_DEPTH==0} {
674 puts -nonewline stderr "WARNING: Compile with -DSQLITE_MAX_EXPR_DEPTH to run "
675 puts stderr "tests sqllimits1-9.X"
676 } else {
677 do_test sqllimits1-9.1 {
678 set max $::SQLITE_MAX_EXPR_DEPTH
679 set expr "(1 [string repeat {AND 1 } $max])"
680 catchsql [subst {
681 SELECT $expr
682 }]
683 } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
685 # Attempting to beat the expression depth limit using nested SELECT
686 # queries causes a parser stack overflow.
687 do_test sqllimits1-9.2 {
688 set max $::SQLITE_MAX_EXPR_DEPTH
689 set expr "SELECT 1"
690 for {set i 0} {$i <= $max} {incr i} {
691 set expr "SELECT ($expr)"
692 }
693 catchsql [subst { $expr }]
694 } "1 {parser stack overflow}"
696 if 0 {
697 do_test sqllimits1-9.3 {
698 execsql {
699 PRAGMA max_page_count = 1000000; -- 1 GB
700 CREATE TABLE v0(a);
701 INSERT INTO v0 VALUES(1);
702 }
703 db transaction {
704 for {set i 1} {$i < 200} {incr i} {
705 set expr "(a [string repeat {AND 1 } 50]) AS a"
706 execsql [subst {
707 CREATE VIEW v${i} AS SELECT $expr FROM v[expr {$i-1}]
708 }]
709 }
710 }
711 } {}
713 do_test sqllimits1-9.4 {
714 catchsql {
715 SELECT a FROM v199
716 }
717 } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
718 }
719 }
721 #--------------------------------------------------------------------
722 # Test cases sqllimits1-10.* test that the SQLITE_MAX_VDBE_OP
723 # limit works as expected. The limit refers to the number of opcodes
724 # in a single VDBE program.
725 #
726 # TODO
728 #--------------------------------------------------------------------
729 # Test the SQLITE_LIMIT_FUNCTION_ARG limit works. Test case names
730 # match the pattern "sqllimits1-11.*".
731 #
732 for {set max 5} {$max<=$SQLITE_MAX_FUNCTION_ARG} {incr max} {
733 do_test sqllimits1-11.$max.1 {
734 set vals [list]
735 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG $::max
736 for {set i 0} {$i < $::max} {incr i} {
737 lappend vals $i
738 }
739 catchsql "SELECT max([join $vals ,])"
740 } "0 [expr {$::max - 1}]"
741 do_test sqllimits1-11.$max.2 {
742 set vals [list]
743 for {set i 0} {$i <= $::max} {incr i} {
744 lappend vals $i
745 }
746 catchsql "SELECT max([join $vals ,])"
747 } {1 {too many arguments on function max}}
749 # Test that it is SQLite, and not the implementation of the
750 # user function that is throwing the error.
751 proc myfunc {args} {error "I don't like to be called!"}
752 do_test sqllimits1-11.$max.2 {
753 db function myfunc myfunc
754 set vals [list]
755 for {set i 0} {$i <= $::max} {incr i} {
756 lappend vals $i
757 }
758 catchsql "SELECT myfunc([join $vals ,])"
759 } {1 {too many arguments on function myfunc}}
760 }
762 #--------------------------------------------------------------------
763 # Test cases sqllimits1-12.*: Test the SQLITE_MAX_ATTACHED limit.
764 #
765 ifcapable attach {
766 do_test sqllimits1-12.1 {
767 set max $::SQLITE_MAX_ATTACHED
768 for {set i 0} {$i < ($max)} {incr i} {
769 forcedelete test${i}.db test${i}.db-journal
770 }
771 for {set i 0} {$i < ($max)} {incr i} {
772 execsql "ATTACH 'test${i}.db' AS aux${i}"
773 }
774 catchsql "ATTACH 'test${i}.db' AS aux${i}"
775 } "1 {too many attached databases - max $::SQLITE_MAX_ATTACHED}"
776 do_test sqllimits1-12.2 {
777 set max $::SQLITE_MAX_ATTACHED
778 for {set i 0} {$i < ($max)} {incr i} {
779 execsql "DETACH aux${i}"
780 }
781 } {}
782 }
784 #--------------------------------------------------------------------
785 # Test cases sqllimits1-13.*: Check that the SQLITE_MAX_VARIABLE_NUMBER
786 # limit works.
787 #
788 do_test sqllimits1-13.1 {
789 set max $::SQLITE_MAX_VARIABLE_NUMBER
790 catchsql "SELECT ?[expr {$max+1}] FROM t1"
791 } "1 {variable number must be between ?1 and ?$::SQLITE_MAX_VARIABLE_NUMBER}"
792 do_test sqllimits1-13.2 {
793 set max $::SQLITE_MAX_VARIABLE_NUMBER
794 set vals [list]
795 for {set i 0} {$i < ($max+3)} {incr i} {
796 lappend vals ?
797 }
798 catchsql "SELECT [join $vals ,] FROM t1"
799 } "1 {too many SQL variables}"
802 #--------------------------------------------------------------------
803 # Test cases sqllimits1-15.* verify that the
804 # SQLITE_MAX_LIKE_PATTERN_LENGTH limit is enforced. This limit only
805 # applies to the built-in LIKE operator, supplying an external
806 # implementation by overriding the like() scalar function bypasses
807 # this limitation.
808 #
809 # These tests check that the limit is not incorrectly applied to
810 # the left-hand-side of the LIKE operator (the string being tested
811 # against the pattern).
812 #
813 set SQLITE_LIMIT_LIKE_PATTERN 1000
814 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH $SQLITE_LIMIT_LIKE_PATTERN
815 do_test sqllimits1-15.1 {
816 set max $::SQLITE_LIMIT_LIKE_PATTERN
817 set ::pattern [string repeat "A%" [expr $max/2]]
818 set ::string [string repeat "A" [expr {$max*2}]]
819 execsql {
820 SELECT $::string LIKE $::pattern;
821 }
822 } {1}
823 do_test sqllimits1-15.2 {
824 set max $::SQLITE_LIMIT_LIKE_PATTERN
825 set ::pattern [string repeat "A%" [expr {($max/2) + 1}]]
826 set ::string [string repeat "A" [expr {$max*2}]]
827 catchsql {
828 SELECT $::string LIKE $::pattern;
829 }
830 } {1 {LIKE or GLOB pattern too complex}}
832 #--------------------------------------------------------------------
833 # This test case doesn't really belong with the other limits tests.
834 # It is in this file because it is taxing to run, like the limits tests.
835 #
836 do_test sqllimits1-16.1 {
837 set ::N [expr int(([expr pow(2,32)]/50) + 1)]
838 expr (($::N*50) & 0xffffffff)<55
839 } {1}
840 do_test sqllimits1-16.2 {
841 set ::format "[string repeat A 60][string repeat "%J" $::N]"
842 catchsql {
843 SELECT strftime($::format, 1);
844 }
845 } {1 {string or blob too big}}
848 foreach {key value} [array get saved] {
849 catch {set $key $value}
850 }
851 finish_test