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