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