# 2007 May 8 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file contains tests to verify that the limits defined in # sqlite source file limits.h are enforced. # # $Id: sqllimits1.test,v 1.4 2007/05/09 08:24:44 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Test organization: # # sqllimits-1.*: SQLITE_MAX_LENGTH # sqllimits-2.*: SQLITE_MAX_SQL_LENGTH # sqllimits-3.*: SQLITE_MAX_PAGE_COUNT # sqllimits-4.*: SQLITE_MAX_COLUMN # # Todo: # # sqllimits-5.*: SQLITE_MAX_EXPR_LENGTH (sqlite todo) # sqllimits-6.*: SQLITE_MAX_VDBE_OP (sqlite todo) # sqllimits-7.*: SQLITE_MAX_FUNCTION_ARG # sqllimits-8.*: SQLITE_MAX_ATTACHED # sqllimits-9.*: SQLITE_MAX_VARIABLE_NUMBER # sqllimits-10.*: SQLITE_MAX_PAGE_SIZE # sqllimits-11.*: SQLITE_MAX_PAGE_COUNT # sqllimits-12.*: SQLITE_MAX_LIKE_PATTERN_LENGTH # #-------------------------------------------------------------------- # Test cases sqllimits-1.* test that the SQLITE_MAX_LENGTH limit # is enforced. # do_test sqllimits-1.1 { catchsql { SELECT randomblob(2147483647) } } {1 {string or blob too big}} # Large, but allowable, blob-size. # set ::LARGESIZE [expr $SQLITE_MAX_LENGTH - 1] do_test sqllimits-1.2 { catchsql { SELECT LENGTH(randomblob($::LARGESIZE)) } } "0 $::LARGESIZE" do_test sqllimits-1.3 { catchsql { SELECT quote(randomblob($::LARGESIZE)) } } {1 {string or blob too big}} do_test sqllimits-1.4 { set ::str [string repeat A 65537] set ::rep [string repeat B 65537] catchsql { SELECT replace($::str, 'A', $::rep) } } {1 {string or blob too big}} #-------------------------------------------------------------------- # Test cases sqllimits-2.* test that the SQLITE_MAX_SQL_LENGTH limit # is enforced. # do_test sqllimits-2.1 { set sql "SELECT 1 WHERE 1==1" set N [expr {$::SQLITE_MAX_SQL_LENGTH / [string length " AND 1==1"]}] append sql [string repeat " AND 1==1" $N] catchsql $sql } {1 {String or BLOB exceeded size limit}} #-------------------------------------------------------------------- # Test cases sqllimits-3.* test that the limit set using the # max_page_count pragma. # do_test sqllimits-3.1 { execsql { PRAGMA max_page_count = 1000; } } {1000} do_test sqllimits-3.2 { execsql { CREATE TABLE trig (a INTEGER, b INTEGER); } # Set up a tree of triggers to fire when a row is inserted # into table "trig". # # INSERT -> insert_b -> update_b -> insert_a -> update_a (chain 1) # -> update_a -> insert_a -> update_b (chain 2) # -> insert_a -> update_b -> insert_b -> update_a (chain 3) # -> update_a -> insert_b -> update_b (chain 4) # # Table starts with N rows. # # Chain 1: insert_b (update N rows) # -> update_b (insert 1 rows) # -> insert_a (update N rows) # -> update_a (insert 1 rows) # # chains 2, 3 and 4 are similar. Each inserts more than N^2 rows, where # N is the number of rows at the conclusion of the previous chain. # # Therefore, a single insert adds (N^16 plus some) rows to the database. # A really long loop... # execsql { CREATE TRIGGER update_b BEFORE UPDATE ON trig FOR EACH ROW BEGIN INSERT INTO trig VALUES (65, 'update_b'); END; CREATE TRIGGER update_a AFTER UPDATE ON trig FOR EACH ROW BEGIN INSERT INTO trig VALUES (65, 'update_a'); END; CREATE TRIGGER insert_b BEFORE INSERT ON trig FOR EACH ROW BEGIN UPDATE trig SET a = 1; END; CREATE TRIGGER insert_a AFTER INSERT ON trig FOR EACH ROW BEGIN UPDATE trig SET a = 1; END; } } {} do_test sqllimits1-3.3 { execsql { INSERT INTO trig VALUES (1,1); } } {} do_test sqllimits1-3.4 { execsql { SELECT COUNT(*) FROM trig; } } {7} # This tries to insert so many rows it fills up the database (limited # to 1MB, so not that noteworthy an achievement). # do_test sqllimits1-3.5 { catchsql { INSERT INTO trig VALUES (1,10); } } {1 {database or disk is full}} do_test sqllimits1-3.6 { catchsql { SELECT COUNT(*) FROM trig; } } {0 7} #-------------------------------------------------------------------- # Test cases sqllimits1-4.* test the SQLITE_MAX_COLUMN limit. # do_test sqllimits-1.4.1 { # Columns in a table. set cols [list] for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} { lappend cols "c$i" } catchsql "CREATE TABLE t([join $cols ,])" } {1 {too many columns on t}} do_test sqllimits-1.4.2 { # Columns in the result-set of a SELECT. set cols [list] for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} { lappend cols "sql AS sql$i" } catchsql "SELECT [join $cols ,] FROM sqlite_master" } {1 {too many columns in result set}} do_test sqllimits-1.4.3 { # Columns in the result-set of a sub-SELECT. set cols [list] for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} { lappend cols "sql AS sql$i" } catchsql "SELECT sql4 FROM (SELECT [join $cols ,] FROM sqlite_master)" } {1 {too many columns in result set}} do_test sqllimits-1.4.4 { # Columns in an index. set cols [list] for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} { lappend cols c } set sql1 "CREATE TABLE t1(c);" set sql2 "CREATE INDEX i1 ON t1([join $cols ,]);" catchsql "$sql1 ; $sql2" } {1 {too many columns in index}} do_test sqllimits-1.4.5 { # Columns in a GROUP BY clause. catchsql "SELECT * FROM t1 GROUP BY [join $cols ,]" } {1 {too many terms in GROUP BY clause}} do_test sqllimits-1.4.6 { # Columns in an ORDER BY clause. catchsql "SELECT * FROM t1 ORDER BY [join $cols ,]" } {1 {too many terms in ORDER BY clause}} do_test sqllimits-1.4.7 { # Assignments in an UPDATE statement. set cols [list] for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} { lappend cols "c = 1" } catchsql "UPDATE t1 SET [join $cols ,];" } {1 {too many columns in set list}} do_test sqllimits-1.4.8 { # Columns in a view definition: set cols [list] for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} { lappend cols "c$i" } catchsql "CREATE VIEW v1 AS SELECT [join $cols ,] FROM t1;" } {1 {too many columns in result set}} do_test sqllimits-1.4.9 { # Columns in a view definition (testing * expansion): set cols [list] for {set i 0} {$i < $SQLITE_MAX_COLUMN} {incr i} { lappend cols "c$i" } catchsql "CREATE TABLE t2([join $cols ,])" catchsql "CREATE VIEW v1 AS SELECT *, c1 AS o FROM t2;" } {1 {too many columns in result set}} #-------------------------------------------------------------------- # These tests - sqllimits-5.* - test that the SQLITE_MAX_EXPR_LENGTH # limit is enforced. The limit refers to the number of terms in # the expression. # #-------------------------------------------------------------------- # Test cases sqllimits-12.* verify that the # SQLITE_MAX_LIKE_PATTERN_LENGTH limit is enforced. This limit only # applies to the built-in LIKE operator, supplying an external # implementation by overriding the like() scalar function bypasses # this limitation. # # These tests check that the limit is not incorrectly applied to # the left-hand-side of the LIKE operator (the string being tested # against the pattern). # do_test sqllimits-1.12.1 { set max $::SQLITE_MAX_LIKE_PATTERN_LENGTH set ::pattern [string repeat "A%" [expr $max/2]] set ::string [string repeat "A" [expr {$max*2}]] execsql { SELECT $::string LIKE $::pattern; } } {1} do_test sqllimits-1.12.2 { set max $::SQLITE_MAX_LIKE_PATTERN_LENGTH set ::pattern [string repeat "A%" [expr {($max/2) + 1}]] set ::string [string repeat "A" [expr {$max*2}]] catchsql { SELECT $::string LIKE $::pattern; } } {1 {LIKE or GLOB pattern too complex}} finish_test