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