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