AryaWu/sqlite
0
1# 2007 May 82#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11#12# This file contains tests to verify that the limits defined in13# sqlite source file limits.h are enforced.14#15# $Id: sqllimits1.test,v 1.33 2009/06/25 01:47:12 drh Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix sqllimits120 21# Verify that the default per-connection limits are the same as22# the compile-time hard limits.23#24sqlite3 db2 :memory:25do_test sqllimits1-1.1 {26 sqlite3_limit db SQLITE_LIMIT_LENGTH -127} $SQLITE_MAX_LENGTH28do_test sqllimits1-1.2 {29 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -130} $SQLITE_MAX_SQL_LENGTH31do_test sqllimits1-1.3 {32 sqlite3_limit db SQLITE_LIMIT_COLUMN -133} $SQLITE_MAX_COLUMN34do_test sqllimits1-1.4 {35 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -136} $SQLITE_MAX_EXPR_DEPTH37do_test sqllimits1-1.5 {38 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -139} $SQLITE_MAX_COMPOUND_SELECT40do_test sqllimits1-1.6 {41 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -142} $SQLITE_MAX_VDBE_OP43do_test sqllimits1-1.7 {44 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -145} $SQLITE_MAX_FUNCTION_ARG46do_test sqllimits1-1.8 {47 sqlite3_limit db SQLITE_LIMIT_ATTACHED -148} $SQLITE_MAX_ATTACHED49do_test sqllimits1-1.9 {50 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -151} $SQLITE_MAX_LIKE_PATTERN_LENGTH52do_test sqllimits1-1.10 {53 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -154} $SQLITE_MAX_VARIABLE_NUMBER55do_test sqllimits1-1.11 {56 sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH -157} $SQLITE_MAX_TRIGGER_DEPTH58do_test sqllimits1-1.12 {59 sqlite3_limit db SQLITE_LIMIT_WORKER_THREADS 9999960 sqlite3_limit db SQLITE_LIMIT_WORKER_THREADS -161} $SQLITE_MAX_WORKER_THREADS62 63# Limit parameters out of range.64#65do_test sqllimits1-1.20 {66 sqlite3_limit db SQLITE_LIMIT_TOOSMALL 12367} {-1}68do_test sqllimits1-1.21 {69 sqlite3_limit db SQLITE_LIMIT_TOOSMALL 12370} {-1}71do_test sqllimits1-1.22 {72 sqlite3_limit db SQLITE_LIMIT_TOOBIG 12373} {-1}74do_test sqllimits1-1.23 {75 sqlite3_limit db SQLITE_LIMIT_TOOBIG 12376} {-1}77 78# Minimum value for SQLITE_LIMIT_LENGTH is 3079#80do_test sqllimits1-1.30 {81 set prior [sqlite3_limit db SQLITE_LIMIT_LENGTH 1]82 sqlite3_limit db SQLITE_LIMIT_LENGTH $prior83} 3084 85 86# Decrease all limits by half. Verify that the new limits take.87#88if {$SQLITE_MAX_LENGTH>=2} {89 do_test sqllimits1-2.1.1 {90 sqlite3_limit db SQLITE_LIMIT_LENGTH \91 [expr {$::SQLITE_MAX_LENGTH/2}]92 } $SQLITE_MAX_LENGTH93 do_test sqllimits1-2.1.2 {94 sqlite3_limit db SQLITE_LIMIT_LENGTH -195 } [expr {$SQLITE_MAX_LENGTH/2}]96}97if {$SQLITE_MAX_SQL_LENGTH>=2} {98 do_test sqllimits1-2.2.1 {99 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH \100 [expr {$::SQLITE_MAX_SQL_LENGTH/2}]101 } $SQLITE_MAX_SQL_LENGTH102 do_test sqllimits1-2.2.2 {103 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1104 } [expr {$SQLITE_MAX_SQL_LENGTH/2}]105}106if {$SQLITE_MAX_COLUMN>=2} {107 do_test sqllimits1-2.3.1 {108 sqlite3_limit db SQLITE_LIMIT_COLUMN \109 [expr {$::SQLITE_MAX_COLUMN/2}]110 } $SQLITE_MAX_COLUMN111 do_test sqllimits1-2.3.2 {112 sqlite3_limit db SQLITE_LIMIT_COLUMN -1113 } [expr {$SQLITE_MAX_COLUMN/2}]114}115if {$SQLITE_MAX_EXPR_DEPTH>=2} {116 do_test sqllimits1-2.4.1 {117 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH \118 [expr {$::SQLITE_MAX_EXPR_DEPTH/2}]119 } $SQLITE_MAX_EXPR_DEPTH120 do_test sqllimits1-2.4.2 {121 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1122 } [expr {$SQLITE_MAX_EXPR_DEPTH/2}]123}124if {$SQLITE_MAX_COMPOUND_SELECT>=2} {125 do_test sqllimits1-2.5.1 {126 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT \127 [expr {$::SQLITE_MAX_COMPOUND_SELECT/2}]128 } $SQLITE_MAX_COMPOUND_SELECT129 do_test sqllimits1-2.5.2 {130 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1131 } [expr {$SQLITE_MAX_COMPOUND_SELECT/2}]132}133if {$SQLITE_MAX_VDBE_OP>=2} {134 do_test sqllimits1-2.6.1 {135 sqlite3_limit db SQLITE_LIMIT_VDBE_OP \136 [expr {$::SQLITE_MAX_VDBE_OP/2}]137 } $SQLITE_MAX_VDBE_OP138 do_test sqllimits1-2.6.2 {139 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1140 } [expr {$SQLITE_MAX_VDBE_OP/2}]141}142if {$SQLITE_MAX_FUNCTION_ARG>=2} {143 do_test sqllimits1-2.7.1 {144 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG \145 [expr {$::SQLITE_MAX_FUNCTION_ARG/2}]146 } $SQLITE_MAX_FUNCTION_ARG147 do_test sqllimits1-2.7.2 {148 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1149 } [expr {$SQLITE_MAX_FUNCTION_ARG/2}]150}151if {$SQLITE_MAX_ATTACHED>=2} {152 do_test sqllimits1-2.8.1 {153 sqlite3_limit db SQLITE_LIMIT_ATTACHED \154 [expr {$::SQLITE_MAX_ATTACHED/2}]155 } $SQLITE_MAX_ATTACHED156 do_test sqllimits1-2.8.2 {157 sqlite3_limit db SQLITE_LIMIT_ATTACHED -1158 } [expr {$SQLITE_MAX_ATTACHED/2}]159}160if {$SQLITE_MAX_LIKE_PATTERN_LENGTH>=2} {161 do_test sqllimits1-2.9.1 {162 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH \163 [expr {$::SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]164 } $SQLITE_MAX_LIKE_PATTERN_LENGTH165 do_test sqllimits1-2.9.2 {166 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1167 } [expr {$SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]168}169if {$SQLITE_MAX_VARIABLE_NUMBER>=2} {170 do_test sqllimits1-2.10.1 {171 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER \172 [expr {$::SQLITE_MAX_VARIABLE_NUMBER/2}]173 } $SQLITE_MAX_VARIABLE_NUMBER174 do_test sqllimits1-2.10.2 {175 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1176 } [expr {$SQLITE_MAX_VARIABLE_NUMBER/2}]177}178 179# In a separate database connection, verify that the limits are unchanged.180#181do_test sqllimits1-3.1 {182 sqlite3_limit db2 SQLITE_LIMIT_LENGTH -1183} $SQLITE_MAX_LENGTH184do_test sqllimits1-3.2 {185 sqlite3_limit db2 SQLITE_LIMIT_SQL_LENGTH -1186} $SQLITE_MAX_SQL_LENGTH187do_test sqllimits1-3.3 {188 sqlite3_limit db2 SQLITE_LIMIT_COLUMN -1189} $SQLITE_MAX_COLUMN190do_test sqllimits1-3.4 {191 sqlite3_limit db2 SQLITE_LIMIT_EXPR_DEPTH -1192} $SQLITE_MAX_EXPR_DEPTH193do_test sqllimits1-3.5 {194 sqlite3_limit db2 SQLITE_LIMIT_COMPOUND_SELECT -1195} $SQLITE_MAX_COMPOUND_SELECT196do_test sqllimits1-3.6 {197 sqlite3_limit db2 SQLITE_LIMIT_VDBE_OP -1198} $SQLITE_MAX_VDBE_OP199do_test sqllimits1-3.7 {200 sqlite3_limit db2 SQLITE_LIMIT_FUNCTION_ARG -1201} $SQLITE_MAX_FUNCTION_ARG202do_test sqllimits1-3.8 {203 sqlite3_limit db2 SQLITE_LIMIT_ATTACHED -1204} $SQLITE_MAX_ATTACHED205do_test sqllimits1-3.9 {206 sqlite3_limit db2 SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1207} $SQLITE_MAX_LIKE_PATTERN_LENGTH208do_test sqllimits1-3.10 {209 sqlite3_limit db2 SQLITE_LIMIT_VARIABLE_NUMBER -1210} $SQLITE_MAX_VARIABLE_NUMBER211db2 close212 213# Attempt to set all limits to the maximum 32-bit integer. Verify214# that the limit does not exceed the compile-time upper bound.215#216do_test sqllimits1-4.1.1 {217 sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff218 sqlite3_limit db SQLITE_LIMIT_LENGTH -1219} $SQLITE_MAX_LENGTH220do_test sqllimits1-4.2.1 {221 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff222 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1223} $SQLITE_MAX_SQL_LENGTH224do_test sqllimits1-4.3.1 {225 sqlite3_limit db SQLITE_LIMIT_COLUMN 0x7fffffff226 sqlite3_limit db SQLITE_LIMIT_COLUMN -1227} $SQLITE_MAX_COLUMN228do_test sqllimits1-4.4.1 {229 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH 0x7fffffff230 sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1231} $SQLITE_MAX_EXPR_DEPTH232do_test sqllimits1-4.5.1 {233 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0x7fffffff234 sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1235} $SQLITE_MAX_COMPOUND_SELECT236do_test sqllimits1-4.6.1 {237 sqlite3_limit db SQLITE_LIMIT_VDBE_OP 0x7fffffff238 sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1239} $SQLITE_MAX_VDBE_OP240do_test sqllimits1-4.7.1 {241 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG 0x7fffffff242 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1243} $SQLITE_MAX_FUNCTION_ARG244do_test sqllimits1-4.8.1 {245 sqlite3_limit db SQLITE_LIMIT_ATTACHED 0x7fffffff246 sqlite3_limit db SQLITE_LIMIT_ATTACHED -1247} $SQLITE_MAX_ATTACHED248do_test sqllimits1-4.9.1 {249 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH 0x7fffffff250 sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1251} $SQLITE_MAX_LIKE_PATTERN_LENGTH252do_test sqllimits1-4.10.1 {253 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER 0x7fffffff254 sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1255} $SQLITE_MAX_VARIABLE_NUMBER256 257#--------------------------------------------------------------------258# Test cases sqllimits1-5.* test that the SQLITE_MAX_LENGTH limit259# is enforced.260#261# EVIDENCE-OF: R-61987-00541 SQLITE_LIMIT_LENGTH The maximum size of any262# string or BLOB or table row, in bytes.263#264db close265sqlite3 db test.db266set LARGESIZE 99999267set SQLITE_LIMIT_LENGTH 100000268sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH269 270do_test sqllimits1-5.1.1 {271 catchsql { SELECT randomblob(2147483647) }272} {1 {string or blob too big}}273do_test sqllimits1-5.1.2 {274 catchsql { SELECT zeroblob(2147483647) }275} {1 {string or blob too big}}276 277do_test sqllimits1-5.2 {278 catchsql { SELECT LENGTH(randomblob($::LARGESIZE)) }279} [list 0 $LARGESIZE]280 281do_test sqllimits1-5.3 {282 catchsql { SELECT quote(randomblob($::LARGESIZE)) }283} {1 {string or blob too big}}284 285do_test sqllimits1-5.4 {286 catchsql { SELECT LENGTH(zeroblob($::LARGESIZE)) }287} [list 0 $LARGESIZE]288 289do_test sqllimits1-5.5 {290 catchsql { SELECT quote(zeroblob($::LARGESIZE)) }291} {1 {string or blob too big}}292 293do_test sqllimits1-5.6 {294 catchsql { SELECT zeroblob(-1) }295} {0 {{}}}296 297do_test sqllimits1-5.9 {298 set ::str [string repeat A 65537]299 set ::rep [string repeat B 65537]300 catchsql { SELECT replace($::str, 'A', $::rep) }301} {1 {string or blob too big}}302 303do_test sqllimits1-5.10 {304 # Prior to 3.37.0 strftime() allocated a large static buffer into305 # which to format its output. Using that strategy, 2100 repeats was306 # enough to exceed 100KiB and provoke the error. As of 3.37.0 strftime()307 # uses the StrAccum functions, so it requires 12100 to fail.308 #309 # set ::str [string repeat %J 2100]310 set ::str [string repeat %J 12100]311 catchsql { SELECT length(strftime($::str, '2003-10-31')) }312} {1 {string or blob too big}}313 314do_test sqllimits1-5.11 {315 set ::str1 [string repeat A [expr {$SQLITE_LIMIT_LENGTH - 10}]]316 set ::str2 [string repeat B [expr {$SQLITE_LIMIT_LENGTH - 10}]]317 catchsql { SELECT $::str1 || $::str2 }318} {1 {string or blob too big}}319 320do_test sqllimits1-5.12 {321 set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]322 catchsql { SELECT quote($::str1) }323} {1 {string or blob too big}}324 325do_test sqllimits1-5.13 {326 set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]327 catchsql { SELECT hex($::str1) }328} {1 {string or blob too big}}329 330do_test sqllimits1-5.14.1 {331 set ::STMT [sqlite3_prepare db "SELECT ?" -1 TAIL]332 sqlite3_bind_zeroblob $::STMT 1 [expr {$SQLITE_LIMIT_LENGTH + 1}]333} {}334do_test sqllimits1-5.14.2 {335 sqlite3_step $::STMT 336} {SQLITE_ERROR}337do_test sqllimits1-5.14.3 {338 sqlite3_reset $::STMT 339} {SQLITE_TOOBIG}340do_test sqllimits1-5.14.4 {341 set np1 [expr {$SQLITE_LIMIT_LENGTH + 1}]342 set ::str1 [string repeat A $np1]343 catch {sqlite3_bind_text $::STMT 1 $::str1 -1} res344 set res345} {SQLITE_TOOBIG}346ifcapable utf16 {347 do_test sqllimits1-5.14.5 {348 catch {sqlite3_bind_text16 $::STMT 1 $::str1 -1} res349 set res350 } {SQLITE_TOOBIG}351}352do_test sqllimits1-5.14.6 {353 catch {sqlite3_bind_text $::STMT 1 $::str1 $np1} res354 set res355} {SQLITE_TOOBIG}356ifcapable utf16 {357 do_test sqllimits1-5.14.7 {358 catch {sqlite3_bind_text16 $::STMT 1 $::str1 [expr $np1+1]} res359 set res360 } {SQLITE_TOOBIG}361}362do_test sqllimits1-5.14.8 {363 set n [expr {$np1-1}]364 catch {sqlite3_bind_text $::STMT 1 $::str1 $n} res365 set res366} {}367do_test sqllimits1-5.14.9 {368 catch {sqlite3_bind_text16 $::STMT 1 $::str1 $n} res369 set res370} {}371sqlite3_finalize $::STMT 372 373do_test sqllimits1-5.15 {374 execsql {375 CREATE TABLE t4(x);376 INSERT INTO t4 VALUES(1);377 INSERT INTO t4 VALUES(2);378 INSERT INTO t4 SELECT 2+x FROM t4;379 }380 catchsql {381 SELECT group_concat(hex(randomblob(20000))) FROM t4;382 }383} {1 {string or blob too big}}384db eval {DROP TABLE t4}385 386sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff387set strvalue [string repeat A $::SQLITE_LIMIT_LENGTH]388do_test sqllimits1-5.16 {389 catchsql "SELECT '$strvalue' AS x"390} [list 0 $strvalue]391do_test sqllimits1-5.17.1 {392 catchsql "SELECT 'A$strvalue'"393} [list 1 {string or blob too big}]394do_test sqllimits1-5.17.2 {395 sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff396 catchsql {SELECT 'A' || $::strvalue}397} [list 0 A$strvalue]398do_test sqllimits1-5.17.3 {399 sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH400 catchsql {SELECT 'A' || $::strvalue}401} [list 1 {string or blob too big}]402set blobvalue [string repeat 41 $::SQLITE_LIMIT_LENGTH]403do_test sqllimits1-5.18 {404 catchsql "SELECT x'$blobvalue' AS x"405} [list 0 $strvalue]406do_test sqllimits1-5.19 {407 catchsql "SELECT '41$blobvalue'"408} [list 1 {string or blob too big}]409unset blobvalue410 411ifcapable datetime {412 set strvalue [string repeat D [expr {$SQLITE_LIMIT_LENGTH-11}]]413 do_test sqllimits1-5.20 {414 catchsql {SELECT strftime('%Y ' || $::strvalue, '2008-01-02')}415 } [list 0 [list "2008 $strvalue"]]416 do_test sqllimits1-5.21 {417 catchsql {SELECT strftime('%Y-%m-%d ' || $::strvalue, '2008-01-02')}418 } {1 {string or blob too big}}419}420unset strvalue421 422#--------------------------------------------------------------------423# Test cases sqllimits1-6.* test that the SQLITE_MAX_SQL_LENGTH limit424# is enforced.425#426# EVIDENCE-OF: R-09808-17554 SQLITE_LIMIT_SQL_LENGTH The maximum length427# of an SQL statement, in bytes.428#429do_test sqllimits1-6.1 {430 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000431 set sql "SELECT 1 WHERE 1==1"432 set tail " /* A comment to take up space in order to make the string\433 longer without increasing the expression depth */\434 AND 1 == 1"435 set N [expr {(50000 / [string length $tail])+1}]436 append sql [string repeat $tail $N]437 catchsql $sql438} {1 {string or blob too big}}439do_test sqllimits1-6.3 {440 sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000441 set sql "SELECT 1 WHERE 1==1"442 set tail " /* A comment to take up space in order to make the string\443 longer without increasing the expression depth */\444 AND 1 == 1"445 set N [expr {(50000 / [string length $tail])+1}]446 append sql [string repeat $tail $N]447 set nbytes [string length $sql]448 append sql { AND 0}449 set rc [catch {sqlite3_prepare db $sql $nbytes TAIL} STMT]450 lappend rc $STMT451} {1 {(18) statement too long}}452do_test sqllimits1-6.4 {453 sqlite3_errmsg db454} {statement too long}455 456#--------------------------------------------------------------------457# Test cases sqllimits1-7.* test that the limit set using the458# max_page_count pragma.459#460do_test sqllimits1-7.1 {461 execsql {462 PRAGMA max_page_count = 1000;463 }464} {1000}465do_test sqllimits1-7.2 {466 execsql { CREATE TABLE trig (a INTEGER, b INTEGER); }467 468 # Set up a tree of triggers to fire when a row is inserted469 # into table "trig".470 #471 # INSERT -> insert_b -> update_b -> insert_a -> update_a (chain 1)472 # -> update_a -> insert_a -> update_b (chain 2)473 # -> insert_a -> update_b -> insert_b -> update_a (chain 3)474 # -> update_a -> insert_b -> update_b (chain 4)475 #476 # Table starts with N rows.477 #478 # Chain 1: insert_b (update N rows)479 # -> update_b (insert 1 rows)480 # -> insert_a (update N rows)481 # -> update_a (insert 1 rows)482 #483 # chains 2, 3 and 4 are similar. Each inserts more than N^2 rows, where484 # N is the number of rows at the conclusion of the previous chain.485 #486 # Therefore, a single insert adds (N^16 plus some) rows to the database.487 # A really long loop...488 # 489 execsql {490 CREATE TRIGGER update_b BEFORE UPDATE ON trig491 FOR EACH ROW BEGIN492 INSERT INTO trig VALUES (65, 'update_b');493 END;494 495 CREATE TRIGGER update_a AFTER UPDATE ON trig496 FOR EACH ROW BEGIN497 INSERT INTO trig VALUES (65, 'update_a');498 END;499 500 CREATE TRIGGER insert_b BEFORE INSERT ON trig501 FOR EACH ROW BEGIN502 UPDATE trig SET a = 1;503 END;504 505 CREATE TRIGGER insert_a AFTER INSERT ON trig506 FOR EACH ROW BEGIN507 UPDATE trig SET a = 1;508 END;509 }510} {}511 512do_test sqllimits1-7.3 {513 execsql {514 INSERT INTO trig VALUES (1,1); 515 }516} {}517 518do_test sqllimits1-7.4 {519 execsql {520 SELECT COUNT(*) FROM trig;521 }522} {7}523 524# This tries to insert so many rows it fills up the database (limited525# to 1MB, so not that noteworthy an achievement).526#527do_test sqllimits1-7.5 {528 catchsql {529 INSERT INTO trig VALUES (1,10);530 }531} {1 {database or disk is full}}532 533do_test sqllimits1-7.6 {534 catchsql {535 SELECT COUNT(*) FROM trig;536 }537} {0 7}538 539# Now check the response of the library to opening a file larger than540# the current max_page_count value. The response is to change the541# internal max_page_count value to match the actual size of the file.542if {[db eval {PRAGMA auto_vacuum}]} {543 set fsize 1700544} else {545 set fsize 1691546}547do_test sqllimits1-7.7.1 {548 execsql {549 PRAGMA max_page_count = 1000000;550 CREATE TABLE abc(a, b, c);551 INSERT INTO abc VALUES(1, 2, 3);552 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;553 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;554 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;555 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;556 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;557 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;558 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;559 INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;560 INSERT INTO abc SELECT a, b, c FROM abc;561 INSERT INTO abc SELECT b, a, c FROM abc;562 INSERT INTO abc SELECT c, b, a FROM abc;563 }564 expr [file size test.db] / 1024565} $fsize566do_test sqllimits1-7.7.2 {567 db close568 sqlite3 db test.db569 execsql {570 PRAGMA max_page_count = 1000;571 }572 execsql {573 SELECT count(*) FROM sqlite_master;574 }575} {6}576do_test sqllimits1-7.7.3 {577 execsql {578 PRAGMA max_page_count;579 }580} $fsize581do_test sqllimits1-7.7.4 {582 execsql {583 DROP TABLE abc;584 }585} {}586 587#--------------------------------------------------------------------588# Test cases sqllimits1-8.* test the SQLITE_MAX_COLUMN limit.589#590# EVIDENCE-OF: R-43996-29471 SQLITE_LIMIT_COLUMN The maximum number of591# columns in a table definition or in the result set of a SELECT or the592# maximum number of columns in an index or in an ORDER BY or GROUP BY593# clause.594#595set SQLITE_LIMIT_COLUMN 200596sqlite3_limit db SQLITE_LIMIT_COLUMN $SQLITE_LIMIT_COLUMN597do_test sqllimits1-8.1 {598 # Columns in a table.599 set cols [list]600 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {601 lappend cols "c$i"602 }603 catchsql "CREATE TABLE t([join $cols ,])" 604} {1 {too many columns on t}}605 606do_test sqllimits1-8.2 {607 # Columns in the result-set of a SELECT.608 set cols [list]609 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {610 lappend cols "sql AS sql$i"611 }612 catchsql "SELECT [join $cols ,] FROM sqlite_master"613} {1 {too many columns in result set}}614 615do_test sqllimits1-8.3 {616 # Columns in the result-set of a sub-SELECT.617 set cols [list]618 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {619 lappend cols "sql AS sql$i"620 }621 catchsql "SELECT sql4 FROM (SELECT [join $cols ,] FROM sqlite_master)"622} {1 {too many columns in result set}}623 624do_test sqllimits1-8.4 {625 # Columns in an index.626 set cols [list]627 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {628 lappend cols c629 }630 set sql1 "CREATE TABLE t1(c);"631 set sql2 "CREATE INDEX i1 ON t1([join $cols ,]);"632 catchsql "$sql1 ; $sql2"633} {1 {too many columns in index}}634 635do_test sqllimits1-8.5 {636 # Columns in a GROUP BY clause.637 catchsql "SELECT * FROM t1 GROUP BY [join $cols ,]"638} {1 {too many terms in GROUP BY clause}}639 640do_test sqllimits1-8.6 {641 # Columns in an ORDER BY clause.642 catchsql "SELECT * FROM t1 ORDER BY [join $cols ,]"643} {1 {too many terms in ORDER BY clause}}644 645do_test sqllimits1-8.7 {646 # Assignments in an UPDATE statement.647 set cols [list]648 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {649 lappend cols "c = 1"650 }651 catchsql "UPDATE t1 SET [join $cols ,];"652} {1 {too many columns in set list}}653 654do_test sqllimits1-8.8 {655 # Columns in a view definition:656 set cols [list]657 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {658 lappend cols "c$i"659 }660 execsql "CREATE VIEW v1 AS SELECT [join $cols ,] FROM t1;"661 catchsql {SELECT * FROM v1}662} {1 {too many columns in result set}}663 664do_test sqllimits1-8.9 {665 # Columns in a view definition (testing * expansion):666 set cols [list]667 for {set i 0} {$i < $SQLITE_LIMIT_COLUMN} {incr i} {668 lappend cols "c$i"669 }670 execsql {DROP VIEW IF EXISTS v1}671 catchsql "CREATE TABLE t2([join $cols ,])"672 catchsql "CREATE VIEW v1 AS SELECT *, c1 AS o FROM t2;"673 catchsql "SELECT * FROM v1"674} {1 {too many columns in result set}}675 676do_test sqllimits1-8.10 {677 # ORDER BY columns678 set cols [list]679 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {680 lappend cols c681 }682 set sql "SELECT c FROM t1 ORDER BY [join $cols ,]"683 catchsql $sql684} {1 {too many terms in ORDER BY clause}}685do_test sqllimits1-8.11 {686 # ORDER BY columns687 set cols [list]688 for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {689 lappend cols [expr {$i%3 + 1}]690 }691 set sql "SELECT c, c+1, c+2 FROM t1 UNION SELECT c-1, c-2, c-3 FROM t1"692 append sql " ORDER BY [join $cols ,]"693 catchsql $sql694} {1 {too many terms in ORDER BY clause}}695 696 697#--------------------------------------------------------------------698# These tests - sqllimits1-9.* - test that the SQLITE_LIMIT_EXPR_DEPTH699# limit is enforced. The limit refers to the number of terms in 700# the expression.701#702# EVIDENCE-OF: R-12723-08526 SQLITE_LIMIT_EXPR_DEPTH The maximum depth703# of the parse tree on any expression.704#705if {$SQLITE_MAX_EXPR_DEPTH==0} {706 puts -nonewline stderr "WARNING: Compile with -DSQLITE_MAX_EXPR_DEPTH to run "707 puts stderr "tests sqllimits1-9.X"708} else {709 do_test sqllimits1-9.1 {710 set max $::SQLITE_MAX_EXPR_DEPTH711 set expr "(1 [string repeat {AND 1 } $max])"712 catchsql [subst {713 SELECT $expr714 }]715 } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"716 717if 0 { 718 # Attempting to beat the expression depth limit using nested SELECT719 # queries causes a parser stack overflow. 720 do_test sqllimits1-9.2 {721 set max $::SQLITE_MAX_EXPR_DEPTH722 set expr "SELECT 1"723 for {set i 0} {$i <= $max} {incr i} {724 set expr "SELECT ($expr)"725 }726 catchsql [subst { $expr }]727 } "1 {parser stack overflow}"728 729 do_test sqllimits1-9.3 {730 execsql {731 PRAGMA max_page_count = 1000000; -- 1 GB732 CREATE TABLE v0(a);733 INSERT INTO v0 VALUES(1);734 }735 db transaction {736 for {set i 1} {$i < 200} {incr i} {737 set expr "(a [string repeat {AND 1 } 50]) AS a"738 execsql [subst {739 CREATE VIEW v${i} AS SELECT $expr FROM v[expr {$i-1}]740 }]741 }742 }743 } {}744 745 do_test sqllimits1-9.4 {746 catchsql {747 SELECT a FROM v199748 }749 } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"750}751}752 753#--------------------------------------------------------------------754# Test cases sqllimits1-10.* test that the SQLITE_MAX_VDBE_OP755# limit works as expected. The limit refers to the number of opcodes756# in a single VDBE program.757#758# TODO759 760#--------------------------------------------------------------------761# Test the SQLITE_LIMIT_FUNCTION_ARG limit works. Test case names762# match the pattern "sqllimits1-11.*".763#764# EVIDENCE-OF: R-59001-45278 SQLITE_LIMIT_FUNCTION_ARG The maximum765# number of arguments on a function.766#767for {set max 5} {$max<=$SQLITE_MAX_FUNCTION_ARG} {incr max} {768 do_test sqllimits1-11.$max.1 {769 set vals [list]770 sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG $::max771 for {set i 0} {$i < $::max} {incr i} {772 lappend vals $i773 }774 catchsql "SELECT max([join $vals ,])"775 } "0 [expr {$::max - 1}]"776 do_test sqllimits1-11.$max.2 {777 set vals [list]778 for {set i 0} {$i <= $::max} {incr i} {779 lappend vals $i780 }781 catchsql "SELECT max([join $vals ,])"782 } {1 {too many arguments on function max}}783 784 # Test that it is SQLite, and not the implementation of the785 # user function that is throwing the error.786 proc myfunc {args} {error "I don't like to be called!"}787 do_test sqllimits1-11.$max.2 {788 db function myfunc myfunc789 set vals [list]790 for {set i 0} {$i <= $::max} {incr i} {791 lappend vals $i792 }793 catchsql "SELECT myfunc([join $vals ,])"794 } {1 {too many arguments on function myfunc}}795}796 797#--------------------------------------------------------------------798# Test cases sqllimits1-12.*: Test the SQLITE_MAX_ATTACHED limit.799#800# EVIDENCE-OF: R-41778-26203 SQLITE_LIMIT_ATTACHED The maximum number of801# attached databases.802#803ifcapable attach {804 do_test sqllimits1-12.1 {805 set max $::SQLITE_MAX_ATTACHED806 for {set i 0} {$i < ($max)} {incr i} {807 forcedelete test${i}.db test${i}.db-journal808 }809 for {set i 0} {$i < ($max)} {incr i} {810 execsql "ATTACH 'test${i}.db' AS aux${i}"811 }812 catchsql "ATTACH 'test${i}.db' AS aux${i}"813 } "1 {too many attached databases - max $::SQLITE_MAX_ATTACHED}"814 do_test sqllimits1-12.2 {815 set max $::SQLITE_MAX_ATTACHED816 for {set i 0} {$i < ($max)} {incr i} {817 execsql "DETACH aux${i}"818 }819 } {}820}821 822#--------------------------------------------------------------------823# Test cases sqllimits1-13.*: Check that the SQLITE_MAX_VARIABLE_NUMBER 824# limit works.825#826# EVIDENCE-OF: R-42363-29104 SQLITE_LIMIT_VARIABLE_NUMBER The maximum827# index number of any parameter in an SQL statement.828#829do_test sqllimits1-13.1 {830 set max $::SQLITE_MAX_VARIABLE_NUMBER831 catchsql "SELECT ?[expr {$max+1}] FROM t1"832} "1 {variable number must be between ?1 and ?$::SQLITE_MAX_VARIABLE_NUMBER}"833do_test sqllimits1-13.2 {834 set max $::SQLITE_MAX_VARIABLE_NUMBER835 set vals [list]836 for {set i 0} {$i < ($max+3)} {incr i} {837 lappend vals ?838 }839 catchsql "SELECT [join $vals ,] FROM t1"840} "1 {too many SQL variables}"841 842 843#--------------------------------------------------------------------844# Test cases sqllimits1-15.* verify that the 845# SQLITE_MAX_LIKE_PATTERN_LENGTH limit is enforced. This limit only846# applies to the built-in LIKE operator, supplying an external 847# implementation by overriding the like() scalar function bypasses848# this limitation.849#850# EVIDENCE-OF: R-12940-37052 SQLITE_LIMIT_LIKE_PATTERN_LENGTH The851# maximum length of the pattern argument to the LIKE or GLOB operators.852#853# These tests check that the limit is not incorrectly applied to854# the left-hand-side of the LIKE operator (the string being tested855# against the pattern).856#857set SQLITE_LIMIT_LIKE_PATTERN 1000858sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH $SQLITE_LIMIT_LIKE_PATTERN859do_test sqllimits1-15.1 {860 set max $::SQLITE_LIMIT_LIKE_PATTERN861 set ::pattern [string repeat "A%" [expr $max/2]]862 set ::string [string repeat "A" [expr {$max*2}]]863 execsql {864 SELECT $::string LIKE $::pattern;865 }866} {1}867do_test sqllimits1-15.2 {868 set max $::SQLITE_LIMIT_LIKE_PATTERN869 set ::pattern [string repeat "A%" [expr {($max/2) + 1}]]870 set ::string [string repeat "A" [expr {$max*2}]]871 catchsql {872 SELECT $::string LIKE $::pattern;873 }874} {1 {LIKE or GLOB pattern too complex}}875 876#--------------------------------------------------------------------877# This test case doesn't really belong with the other limits tests.878# It is in this file because it is taxing to run, like the limits tests.879#880# Update for 3.37.0: strftime() used to allocate a large static buffer881# into which it would write its result. With that implementation, the882# following would trigger an SQLITE_TOOBIG error. But strftime() now883# uses the StrAccum functions, causing this test to fail.884#885#do_test sqllimits1-16.1 {886# set ::N [expr int(([expr pow(2,32)]/50) + 1)]887# expr (($::N*50) & 0xffffffff)<55888#} {1}889#do_test sqllimits1-16.2 {890# set ::format "[string repeat A 60][string repeat "%J" $::N]"891# catchsql {892# SELECT strftime($::format, 1);893# }894#} {1 {string or blob too big}}895 896do_catchsql_test sqllimits1.17.0 {897 SELECT *,*,*,*,*,*,*,* FROM (898 SELECT *,*,*,*,*,*,*,* FROM (899 SELECT *,*,*,*,*,*,*,* FROM (900 SELECT *,*,*,*,*,*,*,* FROM (901 SELECT *,*,*,*,*,*,*,* FROM (902 SELECT 1,2,3,4,5,6,7,8,9,10903 )904 ))))905} "1 {too many columns in result set}"906 907 908foreach {key value} [array get saved] {909 catch {set $key $value}910}911 912#-------------------------------------------------------------------------913# At one point the following caused an assert() to fail.914#915sqlite3_limit db SQLITE_LIMIT_LENGTH 10000916set nm [string repeat x 10000]917do_catchsql_test sqllimits1-17.1 "918 CREATE TABLE $nm (x PRIMARY KEY)919" {1 {string or blob too big}}920 921#-------------------------------------------------------------------------922#923sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 10924do_catchsql_test sqllimits1-18.1 {925 CREATE TABLE b1(x);926 INSERT INTO b1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11);927} {0 {}}928 929do_catchsql_test sqllimits1-18.2 {930 INSERT INTO b1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)931 UNION VALUES(11);932} {0 {}}933 934#-------------------------------------------------------------------------935#936reset_db937ifcapable utf16 {938 do_execsql_test 19.0 {939 PRAGMA encoding = 'utf16';940 }941 set bigstr [string repeat abcdefghij 5000]942 set bigstr16 [encoding convertto unicode $bigstr]943 944 do_test 19.1 {945 string length $bigstr16946 } {100000}947 948 do_test 19.2 {949 set ::stmt [sqlite3_prepare db "SELECT length( ? )" -1 TAIL]950 sqlite3_bind_text16 $::stmt 1 $bigstr16 100000951 sqlite3_step $::stmt952 set val [sqlite3_column_int $::stmt 0]953 sqlite3_finalize $::stmt954 set val955 } {50000}956 957 sqlite3_limit db SQLITE_LIMIT_LENGTH 100000958 959 do_test 19.3 {960 set ::stmt [sqlite3_prepare db "SELECT length( ? )" -1 TAIL]961 sqlite3_bind_text16 $::stmt 1 $bigstr16 100000962 sqlite3_step $::stmt963 set val [sqlite3_column_int $::stmt 0]964 sqlite3_finalize $::stmt965 set val966 } {50000}967 968 sqlite3_limit db SQLITE_LIMIT_LENGTH 99999969 970 do_test 19.4 {971 set ::stmt [sqlite3_prepare db "SELECT length( ? )" -1 TAIL]972 list [catch { sqlite3_bind_text16 $::stmt 1 $bigstr16 100000 } msg] $msg973 } {1 SQLITE_TOOBIG}974 sqlite3_finalize $::stmt975 976 sqlite3_limit db SQLITE_LIMIT_LENGTH 100000977 978 do_test 19.5 {979 set ::stmt [sqlite3_prepare db "SELECT length( ? )" -1 TAIL]980 list [catch { sqlite3_bind_text16 $::stmt 1 $bigstr16 100002 } msg] $msg981 } {1 SQLITE_TOOBIG}982 sqlite3_finalize $::stmt983}984 985finish_test986