000001 # 2007 May 8
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # This file contains tests to verify that the limits defined in
000013 # sqlite source file limits.h are enforced.
000014 #
000015 # $Id: sqllimits1.test,v 1.33 2009/06/25 01:47:12 drh Exp $
000016
000017 set testdir [file dirname $argv0]
000018 source $testdir/tester.tcl
000019
000020 # Verify that the default per-connection limits are the same as
000021 # the compile-time hard limits.
000022 #
000023 sqlite3 db2 :memory:
000024 do_test sqllimits1-1.1 {
000025 sqlite3_limit db SQLITE_LIMIT_LENGTH -1
000026 } $SQLITE_MAX_LENGTH
000027 do_test sqllimits1-1.2 {
000028 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
000029 } $SQLITE_MAX_SQL_LENGTH
000030 do_test sqllimits1-1.3 {
000031 sqlite3_limit db SQLITE_LIMIT_COLUMN -1
000032 } $SQLITE_MAX_COLUMN
000033 do_test sqllimits1-1.4 {
000034 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
000035 } $SQLITE_MAX_EXPR_DEPTH
000036 do_test sqllimits1-1.5 {
000037 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
000038 } $SQLITE_MAX_COMPOUND_SELECT
000039 do_test sqllimits1-1.6 {
000040 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
000041 } $SQLITE_MAX_VDBE_OP
000042 do_test sqllimits1-1.7 {
000043 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
000044 } $SQLITE_MAX_FUNCTION_ARG
000045 do_test sqllimits1-1.8 {
000046 sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
000047 } $SQLITE_MAX_ATTACHED
000048 do_test sqllimits1-1.9 {
000049 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
000050 } $SQLITE_MAX_LIKE_PATTERN_LENGTH
000051 do_test sqllimits1-1.10 {
000052 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
000053 } $SQLITE_MAX_VARIABLE_NUMBER
000054 do_test sqllimits1-1.11 {
000055 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH -1
000056 } $SQLITE_MAX_TRIGGER_DEPTH
000057 do_test sqllimits1-1.12 {
000058 sqlite3_limit db SQLITE_LIMIT_WORKER_THREADS 99999
000059 sqlite3_limit db SQLITE_LIMIT_WORKER_THREADS -1
000060 } $SQLITE_MAX_WORKER_THREADS
000061
000062 # Limit parameters out of range.
000063 #
000064 do_test sqllimits1-1.20 {
000065 sqlite3_limit db SQLITE_LIMIT_TOOSMALL 123
000066 } {-1}
000067 do_test sqllimits1-1.21 {
000068 sqlite3_limit db SQLITE_LIMIT_TOOSMALL 123
000069 } {-1}
000070 do_test sqllimits1-1.22 {
000071 sqlite3_limit db SQLITE_LIMIT_TOOBIG 123
000072 } {-1}
000073 do_test sqllimits1-1.23 {
000074 sqlite3_limit db SQLITE_LIMIT_TOOBIG 123
000075 } {-1}
000076
000077
000078 # Decrease all limits by half. Verify that the new limits take.
000079 #
000080 if {$SQLITE_MAX_LENGTH>=2} {
000081 do_test sqllimits1-2.1.1 {
000082 sqlite3_limit db SQLITE_LIMIT_LENGTH \
000083 [expr {$::SQLITE_MAX_LENGTH/2}]
000084 } $SQLITE_MAX_LENGTH
000085 do_test sqllimits1-2.1.2 {
000086 sqlite3_limit db SQLITE_LIMIT_LENGTH -1
000087 } [expr {$SQLITE_MAX_LENGTH/2}]
000088 }
000089 if {$SQLITE_MAX_SQL_LENGTH>=2} {
000090 do_test sqllimits1-2.2.1 {
000091 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH \
000092 [expr {$::SQLITE_MAX_SQL_LENGTH/2}]
000093 } $SQLITE_MAX_SQL_LENGTH
000094 do_test sqllimits1-2.2.2 {
000095 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
000096 } [expr {$SQLITE_MAX_SQL_LENGTH/2}]
000097 }
000098 if {$SQLITE_MAX_COLUMN>=2} {
000099 do_test sqllimits1-2.3.1 {
000100 sqlite3_limit db SQLITE_LIMIT_COLUMN \
000101 [expr {$::SQLITE_MAX_COLUMN/2}]
000102 } $SQLITE_MAX_COLUMN
000103 do_test sqllimits1-2.3.2 {
000104 sqlite3_limit db SQLITE_LIMIT_COLUMN -1
000105 } [expr {$SQLITE_MAX_COLUMN/2}]
000106 }
000107 if {$SQLITE_MAX_EXPR_DEPTH>=2} {
000108 do_test sqllimits1-2.4.1 {
000109 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH \
000110 [expr {$::SQLITE_MAX_EXPR_DEPTH/2}]
000111 } $SQLITE_MAX_EXPR_DEPTH
000112 do_test sqllimits1-2.4.2 {
000113 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
000114 } [expr {$SQLITE_MAX_EXPR_DEPTH/2}]
000115 }
000116 if {$SQLITE_MAX_COMPOUND_SELECT>=2} {
000117 do_test sqllimits1-2.5.1 {
000118 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT \
000119 [expr {$::SQLITE_MAX_COMPOUND_SELECT/2}]
000120 } $SQLITE_MAX_COMPOUND_SELECT
000121 do_test sqllimits1-2.5.2 {
000122 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
000123 } [expr {$SQLITE_MAX_COMPOUND_SELECT/2}]
000124 }
000125 if {$SQLITE_MAX_VDBE_OP>=2} {
000126 do_test sqllimits1-2.6.1 {
000127 sqlite3_limit db SQLITE_LIMIT_VDBE_OP \
000128 [expr {$::SQLITE_MAX_VDBE_OP/2}]
000129 } $SQLITE_MAX_VDBE_OP
000130 do_test sqllimits1-2.6.2 {
000131 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
000132 } [expr {$SQLITE_MAX_VDBE_OP/2}]
000133 }
000134 if {$SQLITE_MAX_FUNCTION_ARG>=2} {
000135 do_test sqllimits1-2.7.1 {
000136 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG \
000137 [expr {$::SQLITE_MAX_FUNCTION_ARG/2}]
000138 } $SQLITE_MAX_FUNCTION_ARG
000139 do_test sqllimits1-2.7.2 {
000140 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
000141 } [expr {$SQLITE_MAX_FUNCTION_ARG/2}]
000142 }
000143 if {$SQLITE_MAX_ATTACHED>=2} {
000144 do_test sqllimits1-2.8.1 {
000145 sqlite3_limit db SQLITE_LIMIT_ATTACHED \
000146 [expr {$::SQLITE_MAX_ATTACHED/2}]
000147 } $SQLITE_MAX_ATTACHED
000148 do_test sqllimits1-2.8.2 {
000149 sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
000150 } [expr {$SQLITE_MAX_ATTACHED/2}]
000151 }
000152 if {$SQLITE_MAX_LIKE_PATTERN_LENGTH>=2} {
000153 do_test sqllimits1-2.9.1 {
000154 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH \
000155 [expr {$::SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]
000156 } $SQLITE_MAX_LIKE_PATTERN_LENGTH
000157 do_test sqllimits1-2.9.2 {
000158 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
000159 } [expr {$SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]
000160 }
000161 if {$SQLITE_MAX_VARIABLE_NUMBER>=2} {
000162 do_test sqllimits1-2.10.1 {
000163 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER \
000164 [expr {$::SQLITE_MAX_VARIABLE_NUMBER/2}]
000165 } $SQLITE_MAX_VARIABLE_NUMBER
000166 do_test sqllimits1-2.10.2 {
000167 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
000168 } [expr {$SQLITE_MAX_VARIABLE_NUMBER/2}]
000169 }
000170
000171 # In a separate database connection, verify that the limits are unchanged.
000172 #
000173 do_test sqllimits1-3.1 {
000174 sqlite3_limit db2 SQLITE_LIMIT_LENGTH -1
000175 } $SQLITE_MAX_LENGTH
000176 do_test sqllimits1-3.2 {
000177 sqlite3_limit db2 SQLITE_LIMIT_SQL_LENGTH -1
000178 } $SQLITE_MAX_SQL_LENGTH
000179 do_test sqllimits1-3.3 {
000180 sqlite3_limit db2 SQLITE_LIMIT_COLUMN -1
000181 } $SQLITE_MAX_COLUMN
000182 do_test sqllimits1-3.4 {
000183 sqlite3_limit db2 SQLITE_LIMIT_EXPR_DEPTH -1
000184 } $SQLITE_MAX_EXPR_DEPTH
000185 do_test sqllimits1-3.5 {
000186 sqlite3_limit db2 SQLITE_LIMIT_COMPOUND_SELECT -1
000187 } $SQLITE_MAX_COMPOUND_SELECT
000188 do_test sqllimits1-3.6 {
000189 sqlite3_limit db2 SQLITE_LIMIT_VDBE_OP -1
000190 } $SQLITE_MAX_VDBE_OP
000191 do_test sqllimits1-3.7 {
000192 sqlite3_limit db2 SQLITE_LIMIT_FUNCTION_ARG -1
000193 } $SQLITE_MAX_FUNCTION_ARG
000194 do_test sqllimits1-3.8 {
000195 sqlite3_limit db2 SQLITE_LIMIT_ATTACHED -1
000196 } $SQLITE_MAX_ATTACHED
000197 do_test sqllimits1-3.9 {
000198 sqlite3_limit db2 SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
000199 } $SQLITE_MAX_LIKE_PATTERN_LENGTH
000200 do_test sqllimits1-3.10 {
000201 sqlite3_limit db2 SQLITE_LIMIT_VARIABLE_NUMBER -1
000202 } $SQLITE_MAX_VARIABLE_NUMBER
000203 db2 close
000204
000205 # Attempt to set all limits to the maximum 32-bit integer. Verify
000206 # that the limit does not exceed the compile-time upper bound.
000207 #
000208 do_test sqllimits1-4.1.1 {
000209 sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff
000210 sqlite3_limit db SQLITE_LIMIT_LENGTH -1
000211 } $SQLITE_MAX_LENGTH
000212 do_test sqllimits1-4.2.1 {
000213 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff
000214 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
000215 } $SQLITE_MAX_SQL_LENGTH
000216 do_test sqllimits1-4.3.1 {
000217 sqlite3_limit db SQLITE_LIMIT_COLUMN 0x7fffffff
000218 sqlite3_limit db SQLITE_LIMIT_COLUMN -1
000219 } $SQLITE_MAX_COLUMN
000220 do_test sqllimits1-4.4.1 {
000221 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH 0x7fffffff
000222 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
000223 } $SQLITE_MAX_EXPR_DEPTH
000224 do_test sqllimits1-4.5.1 {
000225 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0x7fffffff
000226 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
000227 } $SQLITE_MAX_COMPOUND_SELECT
000228 do_test sqllimits1-4.6.1 {
000229 sqlite3_limit db SQLITE_LIMIT_VDBE_OP 0x7fffffff
000230 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
000231 } $SQLITE_MAX_VDBE_OP
000232 do_test sqllimits1-4.7.1 {
000233 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG 0x7fffffff
000234 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
000235 } $SQLITE_MAX_FUNCTION_ARG
000236 do_test sqllimits1-4.8.1 {
000237 sqlite3_limit db SQLITE_LIMIT_ATTACHED 0x7fffffff
000238 sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
000239 } $SQLITE_MAX_ATTACHED
000240 do_test sqllimits1-4.9.1 {
000241 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH 0x7fffffff
000242 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
000243 } $SQLITE_MAX_LIKE_PATTERN_LENGTH
000244 do_test sqllimits1-4.10.1 {
000245 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER 0x7fffffff
000246 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
000247 } $SQLITE_MAX_VARIABLE_NUMBER
000248
000249 #--------------------------------------------------------------------
000250 # Test cases sqllimits1-5.* test that the SQLITE_MAX_LENGTH limit
000251 # is enforced.
000252 #
000253 # EVIDENCE-OF: R-61987-00541 SQLITE_LIMIT_LENGTH The maximum size of any
000254 # string or BLOB or table row, in bytes.
000255 #
000256 db close
000257 sqlite3 db test.db
000258 set LARGESIZE 99999
000259 set SQLITE_LIMIT_LENGTH 100000
000260 sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH
000261
000262 do_test sqllimits1-5.1.1 {
000263 catchsql { SELECT randomblob(2147483647) }
000264 } {1 {string or blob too big}}
000265 do_test sqllimits1-5.1.2 {
000266 catchsql { SELECT zeroblob(2147483647) }
000267 } {1 {string or blob too big}}
000268
000269 do_test sqllimits1-5.2 {
000270 catchsql { SELECT LENGTH(randomblob($::LARGESIZE)) }
000271 } [list 0 $LARGESIZE]
000272
000273 do_test sqllimits1-5.3 {
000274 catchsql { SELECT quote(randomblob($::LARGESIZE)) }
000275 } {1 {string or blob too big}}
000276
000277 do_test sqllimits1-5.4 {
000278 catchsql { SELECT LENGTH(zeroblob($::LARGESIZE)) }
000279 } [list 0 $LARGESIZE]
000280
000281 do_test sqllimits1-5.5 {
000282 catchsql { SELECT quote(zeroblob($::LARGESIZE)) }
000283 } {1 {string or blob too big}}
000284
000285 do_test sqllimits1-5.6 {
000286 catchsql { SELECT zeroblob(-1) }
000287 } {0 {{}}}
000288
000289 do_test sqllimits1-5.9 {
000290 set ::str [string repeat A 65537]
000291 set ::rep [string repeat B 65537]
000292 catchsql { SELECT replace($::str, 'A', $::rep) }
000293 } {1 {string or blob too big}}
000294
000295 do_test sqllimits1-5.10 {
000296 set ::str [string repeat %J 2100]
000297 catchsql { SELECT strftime($::str, '2003-10-31') }
000298 } {1 {string or blob too big}}
000299
000300 do_test sqllimits1-5.11 {
000301 set ::str1 [string repeat A [expr {$SQLITE_LIMIT_LENGTH - 10}]]
000302 set ::str2 [string repeat B [expr {$SQLITE_LIMIT_LENGTH - 10}]]
000303 catchsql { SELECT $::str1 || $::str2 }
000304 } {1 {string or blob too big}}
000305
000306 do_test sqllimits1-5.12 {
000307 set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]
000308 catchsql { SELECT quote($::str1) }
000309 } {1 {string or blob too big}}
000310
000311 do_test sqllimits1-5.13 {
000312 set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]
000313 catchsql { SELECT hex($::str1) }
000314 } {1 {string or blob too big}}
000315
000316 do_test sqllimits1-5.14.1 {
000317 set ::STMT [sqlite3_prepare db "SELECT ?" -1 TAIL]
000318 sqlite3_bind_zeroblob $::STMT 1 [expr {$SQLITE_LIMIT_LENGTH + 1}]
000319 } {}
000320 do_test sqllimits1-5.14.2 {
000321 sqlite3_step $::STMT
000322 } {SQLITE_ERROR}
000323 do_test sqllimits1-5.14.3 {
000324 sqlite3_reset $::STMT
000325 } {SQLITE_TOOBIG}
000326 do_test sqllimits1-5.14.4 {
000327 set np1 [expr {$SQLITE_LIMIT_LENGTH + 1}]
000328 set ::str1 [string repeat A $np1]
000329 catch {sqlite3_bind_text $::STMT 1 $::str1 -1} res
000330 set res
000331 } {SQLITE_TOOBIG}
000332 ifcapable utf16 {
000333 do_test sqllimits1-5.14.5 {
000334 catch {sqlite3_bind_text16 $::STMT 1 $::str1 -1} res
000335 set res
000336 } {SQLITE_TOOBIG}
000337 }
000338 do_test sqllimits1-5.14.6 {
000339 catch {sqlite3_bind_text $::STMT 1 $::str1 $np1} res
000340 set res
000341 } {SQLITE_TOOBIG}
000342 ifcapable utf16 {
000343 do_test sqllimits1-5.14.7 {
000344 catch {sqlite3_bind_text16 $::STMT 1 $::str1 $np1} res
000345 set res
000346 } {SQLITE_TOOBIG}
000347 }
000348 do_test sqllimits1-5.14.8 {
000349 set n [expr {$np1-1}]
000350 catch {sqlite3_bind_text $::STMT 1 $::str1 $n} res
000351 set res
000352 } {}
000353 do_test sqllimits1-5.14.9 {
000354 catch {sqlite3_bind_text16 $::STMT 1 $::str1 $n} res
000355 set res
000356 } {}
000357 sqlite3_finalize $::STMT
000358
000359 do_test sqllimits1-5.15 {
000360 execsql {
000361 CREATE TABLE t4(x);
000362 INSERT INTO t4 VALUES(1);
000363 INSERT INTO t4 VALUES(2);
000364 INSERT INTO t4 SELECT 2+x FROM t4;
000365 }
000366 catchsql {
000367 SELECT group_concat(hex(randomblob(20000))) FROM t4;
000368 }
000369 } {1 {string or blob too big}}
000370 db eval {DROP TABLE t4}
000371
000372 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff
000373 set strvalue [string repeat A $::SQLITE_LIMIT_LENGTH]
000374 do_test sqllimits1-5.16 {
000375 catchsql "SELECT '$strvalue'"
000376 } [list 0 $strvalue]
000377 do_test sqllimits1-5.17.1 {
000378 catchsql "SELECT 'A$strvalue'"
000379 } [list 1 {string or blob too big}]
000380 do_test sqllimits1-5.17.2 {
000381 sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff
000382 catchsql {SELECT 'A' || $::strvalue}
000383 } [list 0 A$strvalue]
000384 do_test sqllimits1-5.17.3 {
000385 sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH
000386 catchsql {SELECT 'A' || $::strvalue}
000387 } [list 1 {string or blob too big}]
000388 set blobvalue [string repeat 41 $::SQLITE_LIMIT_LENGTH]
000389 do_test sqllimits1-5.18 {
000390 catchsql "SELECT x'$blobvalue'"
000391 } [list 0 $strvalue]
000392 do_test sqllimits1-5.19 {
000393 catchsql "SELECT '41$blobvalue'"
000394 } [list 1 {string or blob too big}]
000395 unset blobvalue
000396
000397 ifcapable datetime {
000398 set strvalue [string repeat D [expr {$SQLITE_LIMIT_LENGTH-12}]]
000399 do_test sqllimits1-5.20 {
000400 catchsql {SELECT strftime('%Y ' || $::strvalue, '2008-01-02')}
000401 } [list 0 [list "2008 $strvalue"]]
000402 do_test sqllimits1-5.21 {
000403 catchsql {SELECT strftime('%Y-%m-%d ' || $::strvalue, '2008-01-02')}
000404 } {1 {string or blob too big}}
000405 }
000406 unset strvalue
000407
000408 #--------------------------------------------------------------------
000409 # Test cases sqllimits1-6.* test that the SQLITE_MAX_SQL_LENGTH limit
000410 # is enforced.
000411 #
000412 # EVIDENCE-OF: R-09808-17554 SQLITE_LIMIT_SQL_LENGTH The maximum length
000413 # of an SQL statement, in bytes.
000414 #
000415 do_test sqllimits1-6.1 {
000416 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000
000417 set sql "SELECT 1 WHERE 1==1"
000418 set tail " /* A comment to take up space in order to make the string\
000419 longer without increasing the expression depth */\
000420 AND 1 == 1"
000421 set N [expr {(50000 / [string length $tail])+1}]
000422 append sql [string repeat $tail $N]
000423 catchsql $sql
000424 } {1 {string or blob too big}}
000425 do_test sqllimits1-6.3 {
000426 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000
000427 set sql "SELECT 1 WHERE 1==1"
000428 set tail " /* A comment to take up space in order to make the string\
000429 longer without increasing the expression depth */\
000430 AND 1 == 1"
000431 set N [expr {(50000 / [string length $tail])+1}]
000432 append sql [string repeat $tail $N]
000433 set nbytes [string length $sql]
000434 append sql { AND 0}
000435 set rc [catch {sqlite3_prepare db $sql $nbytes TAIL} STMT]
000436 lappend rc $STMT
000437 } {1 {(18) statement too long}}
000438 do_test sqllimits1-6.4 {
000439 sqlite3_errmsg db
000440 } {statement too long}
000441
000442 #--------------------------------------------------------------------
000443 # Test cases sqllimits1-7.* test that the limit set using the
000444 # max_page_count pragma.
000445 #
000446 do_test sqllimits1-7.1 {
000447 execsql {
000448 PRAGMA max_page_count = 1000;
000449 }
000450 } {1000}
000451 do_test sqllimits1-7.2 {
000452 execsql { CREATE TABLE trig (a INTEGER, b INTEGER); }
000453
000454 # Set up a tree of triggers to fire when a row is inserted
000455 # into table "trig".
000456 #
000457 # INSERT -> insert_b -> update_b -> insert_a -> update_a (chain 1)
000458 # -> update_a -> insert_a -> update_b (chain 2)
000459 # -> insert_a -> update_b -> insert_b -> update_a (chain 3)
000460 # -> update_a -> insert_b -> update_b (chain 4)
000461 #
000462 # Table starts with N rows.
000463 #
000464 # Chain 1: insert_b (update N rows)
000465 # -> update_b (insert 1 rows)
000466 # -> insert_a (update N rows)
000467 # -> update_a (insert 1 rows)
000468 #
000469 # chains 2, 3 and 4 are similar. Each inserts more than N^2 rows, where
000470 # N is the number of rows at the conclusion of the previous chain.
000471 #
000472 # Therefore, a single insert adds (N^16 plus some) rows to the database.
000473 # A really long loop...
000474 #
000475 execsql {
000476 CREATE TRIGGER update_b BEFORE UPDATE ON trig
000477 FOR EACH ROW BEGIN
000478 INSERT INTO trig VALUES (65, 'update_b');
000479 END;
000480
000481 CREATE TRIGGER update_a AFTER UPDATE ON trig
000482 FOR EACH ROW BEGIN
000483 INSERT INTO trig VALUES (65, 'update_a');
000484 END;
000485
000486 CREATE TRIGGER insert_b BEFORE INSERT ON trig
000487 FOR EACH ROW BEGIN
000488 UPDATE trig SET a = 1;
000489 END;
000490
000491 CREATE TRIGGER insert_a AFTER INSERT ON trig
000492 FOR EACH ROW BEGIN
000493 UPDATE trig SET a = 1;
000494 END;
000495 }
000496 } {}
000497
000498 do_test sqllimits1-7.3 {
000499 execsql {
000500 INSERT INTO trig VALUES (1,1);
000501 }
000502 } {}
000503
000504 do_test sqllimits1-7.4 {
000505 execsql {
000506 SELECT COUNT(*) FROM trig;
000507 }
000508 } {7}
000509
000510 # This tries to insert so many rows it fills up the database (limited
000511 # to 1MB, so not that noteworthy an achievement).
000512 #
000513 do_test sqllimits1-7.5 {
000514 catchsql {
000515 INSERT INTO trig VALUES (1,10);
000516 }
000517 } {1 {database or disk is full}}
000518
000519 do_test sqllimits1-7.6 {
000520 catchsql {
000521 SELECT COUNT(*) FROM trig;
000522 }
000523 } {0 7}
000524
000525 # Now check the response of the library to opening a file larger than
000526 # the current max_page_count value. The response is to change the
000527 # internal max_page_count value to match the actual size of the file.
000528 if {[db eval {PRAGMA auto_vacuum}]} {
000529 set fsize 1700
000530 } else {
000531 set fsize 1691
000532 }
000533 do_test sqllimits1-7.7.1 {
000534 execsql {
000535 PRAGMA max_page_count = 1000000;
000536 CREATE TABLE abc(a, b, c);
000537 INSERT INTO abc VALUES(1, 2, 3);
000538 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
000539 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
000540 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
000541 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
000542 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
000543 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
000544 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
000545 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
000546 INSERT INTO abc SELECT a, b, c FROM abc;
000547 INSERT INTO abc SELECT b, a, c FROM abc;
000548 INSERT INTO abc SELECT c, b, a FROM abc;
000549 }
000550 expr [file size test.db] / 1024
000551 } $fsize
000552 do_test sqllimits1-7.7.2 {
000553 db close
000554 sqlite3 db test.db
000555 execsql {
000556 PRAGMA max_page_count = 1000;
000557 }
000558 execsql {
000559 SELECT count(*) FROM sqlite_master;
000560 }
000561 } {6}
000562 do_test sqllimits1-7.7.3 {
000563 execsql {
000564 PRAGMA max_page_count;
000565 }
000566 } $fsize
000567 do_test sqllimits1-7.7.4 {
000568 execsql {
000569 DROP TABLE abc;
000570 }
000571 } {}
000572
000573 #--------------------------------------------------------------------
000574 # Test cases sqllimits1-8.* test the SQLITE_MAX_COLUMN limit.
000575 #
000576 # EVIDENCE-OF: R-43996-29471 SQLITE_LIMIT_COLUMN The maximum number of
000577 # columns in a table definition or in the result set of a SELECT or the
000578 # maximum number of columns in an index or in an ORDER BY or GROUP BY
000579 # clause.
000580 #
000581 set SQLITE_LIMIT_COLUMN 200
000582 sqlite3_limit db SQLITE_LIMIT_COLUMN $SQLITE_LIMIT_COLUMN
000583 do_test sqllimits1-8.1 {
000584 # Columns in a table.
000585 set cols [list]
000586 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
000587 lappend cols "c$i"
000588 }
000589 catchsql "CREATE TABLE t([join $cols ,])"
000590 } {1 {too many columns on t}}
000591
000592 do_test sqllimits1-8.2 {
000593 # Columns in the result-set of a SELECT.
000594 set cols [list]
000595 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
000596 lappend cols "sql AS sql$i"
000597 }
000598 catchsql "SELECT [join $cols ,] FROM sqlite_master"
000599 } {1 {too many columns in result set}}
000600
000601 do_test sqllimits1-8.3 {
000602 # Columns in the result-set of a sub-SELECT.
000603 set cols [list]
000604 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
000605 lappend cols "sql AS sql$i"
000606 }
000607 catchsql "SELECT sql4 FROM (SELECT [join $cols ,] FROM sqlite_master)"
000608 } {1 {too many columns in result set}}
000609
000610 do_test sqllimits1-8.4 {
000611 # Columns in an index.
000612 set cols [list]
000613 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
000614 lappend cols c
000615 }
000616 set sql1 "CREATE TABLE t1(c);"
000617 set sql2 "CREATE INDEX i1 ON t1([join $cols ,]);"
000618 catchsql "$sql1 ; $sql2"
000619 } {1 {too many columns in index}}
000620
000621 do_test sqllimits1-8.5 {
000622 # Columns in a GROUP BY clause.
000623 catchsql "SELECT * FROM t1 GROUP BY [join $cols ,]"
000624 } {1 {too many terms in GROUP BY clause}}
000625
000626 do_test sqllimits1-8.6 {
000627 # Columns in an ORDER BY clause.
000628 catchsql "SELECT * FROM t1 ORDER BY [join $cols ,]"
000629 } {1 {too many terms in ORDER BY clause}}
000630
000631 do_test sqllimits1-8.7 {
000632 # Assignments in an UPDATE statement.
000633 set cols [list]
000634 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
000635 lappend cols "c = 1"
000636 }
000637 catchsql "UPDATE t1 SET [join $cols ,];"
000638 } {1 {too many columns in set list}}
000639
000640 do_test sqllimits1-8.8 {
000641 # Columns in a view definition:
000642 set cols [list]
000643 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
000644 lappend cols "c$i"
000645 }
000646 execsql "CREATE VIEW v1 AS SELECT [join $cols ,] FROM t1;"
000647 catchsql {SELECT * FROM v1}
000648 } {1 {too many columns in result set}}
000649
000650 do_test sqllimits1-8.9 {
000651 # Columns in a view definition (testing * expansion):
000652 set cols [list]
000653 for {set i 0} {$i < $SQLITE_LIMIT_COLUMN} {incr i} {
000654 lappend cols "c$i"
000655 }
000656 execsql {DROP VIEW IF EXISTS v1}
000657 catchsql "CREATE TABLE t2([join $cols ,])"
000658 catchsql "CREATE VIEW v1 AS SELECT *, c1 AS o FROM t2;"
000659 catchsql "SELECT * FROM v1"
000660 } {1 {too many columns in result set}}
000661
000662 do_test sqllimits1-8.10 {
000663 # ORDER BY columns
000664 set cols [list]
000665 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
000666 lappend cols c
000667 }
000668 set sql "SELECT c FROM t1 ORDER BY [join $cols ,]"
000669 catchsql $sql
000670 } {1 {too many terms in ORDER BY clause}}
000671 do_test sqllimits1-8.11 {
000672 # ORDER BY columns
000673 set cols [list]
000674 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
000675 lappend cols [expr {$i%3 + 1}]
000676 }
000677 set sql "SELECT c, c+1, c+2 FROM t1 UNION SELECT c-1, c-2, c-3 FROM t1"
000678 append sql " ORDER BY [join $cols ,]"
000679 catchsql $sql
000680 } {1 {too many terms in ORDER BY clause}}
000681
000682
000683 #--------------------------------------------------------------------
000684 # These tests - sqllimits1-9.* - test that the SQLITE_LIMIT_EXPR_DEPTH
000685 # limit is enforced. The limit refers to the number of terms in
000686 # the expression.
000687 #
000688 # EVIDENCE-OF: R-12723-08526 SQLITE_LIMIT_EXPR_DEPTH The maximum depth
000689 # of the parse tree on any expression.
000690 #
000691 if {$SQLITE_MAX_EXPR_DEPTH==0} {
000692 puts -nonewline stderr "WARNING: Compile with -DSQLITE_MAX_EXPR_DEPTH to run "
000693 puts stderr "tests sqllimits1-9.X"
000694 } else {
000695 do_test sqllimits1-9.1 {
000696 set max $::SQLITE_MAX_EXPR_DEPTH
000697 set expr "(1 [string repeat {AND 1 } $max])"
000698 catchsql [subst {
000699 SELECT $expr
000700 }]
000701 } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
000702
000703 # Attempting to beat the expression depth limit using nested SELECT
000704 # queries causes a parser stack overflow.
000705 do_test sqllimits1-9.2 {
000706 set max $::SQLITE_MAX_EXPR_DEPTH
000707 set expr "SELECT 1"
000708 for {set i 0} {$i <= $max} {incr i} {
000709 set expr "SELECT ($expr)"
000710 }
000711 catchsql [subst { $expr }]
000712 } "1 {parser stack overflow}"
000713
000714 if 0 {
000715 do_test sqllimits1-9.3 {
000716 execsql {
000717 PRAGMA max_page_count = 1000000; -- 1 GB
000718 CREATE TABLE v0(a);
000719 INSERT INTO v0 VALUES(1);
000720 }
000721 db transaction {
000722 for {set i 1} {$i < 200} {incr i} {
000723 set expr "(a [string repeat {AND 1 } 50]) AS a"
000724 execsql [subst {
000725 CREATE VIEW v${i} AS SELECT $expr FROM v[expr {$i-1}]
000726 }]
000727 }
000728 }
000729 } {}
000730
000731 do_test sqllimits1-9.4 {
000732 catchsql {
000733 SELECT a FROM v199
000734 }
000735 } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
000736 }
000737 }
000738
000739 #--------------------------------------------------------------------
000740 # Test cases sqllimits1-10.* test that the SQLITE_MAX_VDBE_OP
000741 # limit works as expected. The limit refers to the number of opcodes
000742 # in a single VDBE program.
000743 #
000744 # TODO
000745
000746 #--------------------------------------------------------------------
000747 # Test the SQLITE_LIMIT_FUNCTION_ARG limit works. Test case names
000748 # match the pattern "sqllimits1-11.*".
000749 #
000750 # EVIDENCE-OF: R-59001-45278 SQLITE_LIMIT_FUNCTION_ARG The maximum
000751 # number of arguments on a function.
000752 #
000753 for {set max 5} {$max<=$SQLITE_MAX_FUNCTION_ARG} {incr max} {
000754 do_test sqllimits1-11.$max.1 {
000755 set vals [list]
000756 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG $::max
000757 for {set i 0} {$i < $::max} {incr i} {
000758 lappend vals $i
000759 }
000760 catchsql "SELECT max([join $vals ,])"
000761 } "0 [expr {$::max - 1}]"
000762 do_test sqllimits1-11.$max.2 {
000763 set vals [list]
000764 for {set i 0} {$i <= $::max} {incr i} {
000765 lappend vals $i
000766 }
000767 catchsql "SELECT max([join $vals ,])"
000768 } {1 {too many arguments on function max}}
000769
000770 # Test that it is SQLite, and not the implementation of the
000771 # user function that is throwing the error.
000772 proc myfunc {args} {error "I don't like to be called!"}
000773 do_test sqllimits1-11.$max.2 {
000774 db function myfunc myfunc
000775 set vals [list]
000776 for {set i 0} {$i <= $::max} {incr i} {
000777 lappend vals $i
000778 }
000779 catchsql "SELECT myfunc([join $vals ,])"
000780 } {1 {too many arguments on function myfunc}}
000781 }
000782
000783 #--------------------------------------------------------------------
000784 # Test cases sqllimits1-12.*: Test the SQLITE_MAX_ATTACHED limit.
000785 #
000786 # EVIDENCE-OF: R-41778-26203 SQLITE_LIMIT_ATTACHED The maximum number of
000787 # attached databases.
000788 #
000789 ifcapable attach {
000790 do_test sqllimits1-12.1 {
000791 set max $::SQLITE_MAX_ATTACHED
000792 for {set i 0} {$i < ($max)} {incr i} {
000793 forcedelete test${i}.db test${i}.db-journal
000794 }
000795 for {set i 0} {$i < ($max)} {incr i} {
000796 execsql "ATTACH 'test${i}.db' AS aux${i}"
000797 }
000798 catchsql "ATTACH 'test${i}.db' AS aux${i}"
000799 } "1 {too many attached databases - max $::SQLITE_MAX_ATTACHED}"
000800 do_test sqllimits1-12.2 {
000801 set max $::SQLITE_MAX_ATTACHED
000802 for {set i 0} {$i < ($max)} {incr i} {
000803 execsql "DETACH aux${i}"
000804 }
000805 } {}
000806 }
000807
000808 #--------------------------------------------------------------------
000809 # Test cases sqllimits1-13.*: Check that the SQLITE_MAX_VARIABLE_NUMBER
000810 # limit works.
000811 #
000812 # EVIDENCE-OF: R-42363-29104 SQLITE_LIMIT_VARIABLE_NUMBER The maximum
000813 # index number of any parameter in an SQL statement.
000814 #
000815 do_test sqllimits1-13.1 {
000816 set max $::SQLITE_MAX_VARIABLE_NUMBER
000817 catchsql "SELECT ?[expr {$max+1}] FROM t1"
000818 } "1 {variable number must be between ?1 and ?$::SQLITE_MAX_VARIABLE_NUMBER}"
000819 do_test sqllimits1-13.2 {
000820 set max $::SQLITE_MAX_VARIABLE_NUMBER
000821 set vals [list]
000822 for {set i 0} {$i < ($max+3)} {incr i} {
000823 lappend vals ?
000824 }
000825 catchsql "SELECT [join $vals ,] FROM t1"
000826 } "1 {too many SQL variables}"
000827
000828
000829 #--------------------------------------------------------------------
000830 # Test cases sqllimits1-15.* verify that the
000831 # SQLITE_MAX_LIKE_PATTERN_LENGTH limit is enforced. This limit only
000832 # applies to the built-in LIKE operator, supplying an external
000833 # implementation by overriding the like() scalar function bypasses
000834 # this limitation.
000835 #
000836 # EVIDENCE-OF: R-12940-37052 SQLITE_LIMIT_LIKE_PATTERN_LENGTH The
000837 # maximum length of the pattern argument to the LIKE or GLOB operators.
000838 #
000839 # These tests check that the limit is not incorrectly applied to
000840 # the left-hand-side of the LIKE operator (the string being tested
000841 # against the pattern).
000842 #
000843 set SQLITE_LIMIT_LIKE_PATTERN 1000
000844 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH $SQLITE_LIMIT_LIKE_PATTERN
000845 do_test sqllimits1-15.1 {
000846 set max $::SQLITE_LIMIT_LIKE_PATTERN
000847 set ::pattern [string repeat "A%" [expr $max/2]]
000848 set ::string [string repeat "A" [expr {$max*2}]]
000849 execsql {
000850 SELECT $::string LIKE $::pattern;
000851 }
000852 } {1}
000853 do_test sqllimits1-15.2 {
000854 set max $::SQLITE_LIMIT_LIKE_PATTERN
000855 set ::pattern [string repeat "A%" [expr {($max/2) + 1}]]
000856 set ::string [string repeat "A" [expr {$max*2}]]
000857 catchsql {
000858 SELECT $::string LIKE $::pattern;
000859 }
000860 } {1 {LIKE or GLOB pattern too complex}}
000861
000862 #--------------------------------------------------------------------
000863 # This test case doesn't really belong with the other limits tests.
000864 # It is in this file because it is taxing to run, like the limits tests.
000865 #
000866 do_test sqllimits1-16.1 {
000867 set ::N [expr int(([expr pow(2,32)]/50) + 1)]
000868 expr (($::N*50) & 0xffffffff)<55
000869 } {1}
000870 do_test sqllimits1-16.2 {
000871 set ::format "[string repeat A 60][string repeat "%J" $::N]"
000872 catchsql {
000873 SELECT strftime($::format, 1);
000874 }
000875 } {1 {string or blob too big}}
000876
000877 do_catchsql_test sqllimits1.17.0 {
000878 SELECT *,*,*,*,*,*,*,* FROM (
000879 SELECT *,*,*,*,*,*,*,* FROM (
000880 SELECT *,*,*,*,*,*,*,* FROM (
000881 SELECT *,*,*,*,*,*,*,* FROM (
000882 SELECT *,*,*,*,*,*,*,* FROM (
000883 SELECT 1,2,3,4,5,6,7,8,9,10
000884 )
000885 ))))
000886 } "1 {too many columns in result set}"
000887
000888
000889 foreach {key value} [array get saved] {
000890 catch {set $key $value}
000891 }
000892 finish_test