Debian Codegraph @

test/sqllimits1.test

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