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