# 2007 May 8

#

# Portions Copyright (c) 2007-2008 Nokia Corporation and/or its subsidiaries. All rights reserved.

#

# 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.31 2008/07/15 00:27:35 drh Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Verify that the default per-connection limits are the same as

# the compile-time hard limits.

#

sqlite3 db2 :memory:

do_test sqllimits1-1.1 {

  sqlite3_limit db SQLITE_LIMIT_LENGTH -1

} $SQLITE_MAX_LENGTH

do_test sqllimits1-1.2 {

  sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1

} $SQLITE_MAX_SQL_LENGTH

do_test sqllimits1-1.3 {

  sqlite3_limit db SQLITE_LIMIT_COLUMN -1

} $SQLITE_MAX_COLUMN

do_test sqllimits1-1.4 {

  sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1

} $SQLITE_MAX_EXPR_DEPTH

do_test sqllimits1-1.5 {

  sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1

} $SQLITE_MAX_COMPOUND_SELECT

do_test sqllimits1-1.6 {

  sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1

} $SQLITE_MAX_VDBE_OP

do_test sqllimits1-1.7 {

  sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1

} $SQLITE_MAX_FUNCTION_ARG

do_test sqllimits1-1.8 {

  sqlite3_limit db SQLITE_LIMIT_ATTACHED -1

} $SQLITE_MAX_ATTACHED

do_test sqllimits1-1.9 {

  sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1

} $SQLITE_MAX_LIKE_PATTERN_LENGTH

do_test sqllimits1-1.10 {

  sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1

} $SQLITE_MAX_VARIABLE_NUMBER

# Limit parameters out of range.

#

do_test sqllimits1-1.20 {

  sqlite3_limit db SQLITE_LIMIT_TOOSMALL 123

} {-1}

do_test sqllimits1-1.21 {

  sqlite3_limit db SQLITE_LIMIT_TOOSMALL 123

} {-1}

do_test sqllimits1-1.22 {

  sqlite3_limit db SQLITE_LIMIT_TOOBIG 123

} {-1}

do_test sqllimits1-1.23 {

  sqlite3_limit db SQLITE_LIMIT_TOOBIG 123

} {-1}

# Decrease all limits by half.  Verify that the new limits take.

#

if {$SQLITE_MAX_LENGTH>=2} {

  do_test sqllimits1-2.1.1 {

    sqlite3_limit db SQLITE_LIMIT_LENGTH \

                    [expr {$::SQLITE_MAX_LENGTH/2}]

  } $SQLITE_MAX_LENGTH

  do_test sqllimits1-2.1.2 {

    sqlite3_limit db SQLITE_LIMIT_LENGTH -1

  } [expr {$SQLITE_MAX_LENGTH/2}]

}

if {$SQLITE_MAX_SQL_LENGTH>=2} {

  do_test sqllimits1-2.2.1 {

    sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH \

                    [expr {$::SQLITE_MAX_SQL_LENGTH/2}]

  } $SQLITE_MAX_SQL_LENGTH

  do_test sqllimits1-2.2.2 {

    sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1

  } [expr {$SQLITE_MAX_SQL_LENGTH/2}]

}

if {$SQLITE_MAX_COLUMN>=2} {

  do_test sqllimits1-2.3.1 {

    sqlite3_limit db SQLITE_LIMIT_COLUMN \

                    [expr {$::SQLITE_MAX_COLUMN/2}]

  } $SQLITE_MAX_COLUMN

  do_test sqllimits1-2.3.2 {

    sqlite3_limit db SQLITE_LIMIT_COLUMN -1

  } [expr {$SQLITE_MAX_COLUMN/2}]

}

if {$SQLITE_MAX_EXPR_DEPTH>=2} {

  do_test sqllimits1-2.4.1 {

    sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH \

                    [expr {$::SQLITE_MAX_EXPR_DEPTH/2}]

  } $SQLITE_MAX_EXPR_DEPTH

  do_test sqllimits1-2.4.2 {

    sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1

  } [expr {$SQLITE_MAX_EXPR_DEPTH/2}]

}

if {$SQLITE_MAX_COMPOUND_SELECT>=2} {

  do_test sqllimits1-2.5.1 {

    sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT \

                    [expr {$::SQLITE_MAX_COMPOUND_SELECT/2}]

  } $SQLITE_MAX_COMPOUND_SELECT

  do_test sqllimits1-2.5.2 {

    sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1

  } [expr {$SQLITE_MAX_COMPOUND_SELECT/2}]

}

if {$SQLITE_MAX_VDBE_OP>=2} {

  do_test sqllimits1-2.6.1 {

    sqlite3_limit db SQLITE_LIMIT_VDBE_OP \

                    [expr {$::SQLITE_MAX_VDBE_OP/2}]

  } $SQLITE_MAX_VDBE_OP

  do_test sqllimits1-2.6.2 {

    sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1

  } [expr {$SQLITE_MAX_VDBE_OP/2}]

}

if {$SQLITE_MAX_FUNCTION_ARG>=2} {

  do_test sqllimits1-2.7.1 {

    sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG \

                    [expr {$::SQLITE_MAX_FUNCTION_ARG/2}]

  } $SQLITE_MAX_FUNCTION_ARG

  do_test sqllimits1-2.7.2 {

    sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1

  } [expr {$SQLITE_MAX_FUNCTION_ARG/2}]

}

if {$SQLITE_MAX_ATTACHED>=2} {

  do_test sqllimits1-2.8.1 {

    sqlite3_limit db SQLITE_LIMIT_ATTACHED \

                    [expr {$::SQLITE_MAX_ATTACHED/2}]

  } $SQLITE_MAX_ATTACHED

  do_test sqllimits1-2.8.2 {

    sqlite3_limit db SQLITE_LIMIT_ATTACHED -1

  } [expr {$SQLITE_MAX_ATTACHED/2}]

}

if {$SQLITE_MAX_LIKE_PATTERN_LENGTH>=2} {

  do_test sqllimits1-2.9.1 {

    sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH \

                    [expr {$::SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]

  } $SQLITE_MAX_LIKE_PATTERN_LENGTH

  do_test sqllimits1-2.9.2 {

    sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1

  } [expr {$SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]

}

if {$SQLITE_MAX_VARIABLE_NUMBER>=2} {

  do_test sqllimits1-2.10.1 {

    sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER \

                    [expr {$::SQLITE_MAX_VARIABLE_NUMBER/2}]

  } $SQLITE_MAX_VARIABLE_NUMBER

  do_test sqllimits1-2.10.2 {

    sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1

  } [expr {$SQLITE_MAX_VARIABLE_NUMBER/2}]

}

# In a separate database connection, verify that the limits are unchanged.

#

do_test sqllimits1-3.1 {

  sqlite3_limit db2 SQLITE_LIMIT_LENGTH -1

} $SQLITE_MAX_LENGTH

do_test sqllimits1-3.2 {

  sqlite3_limit db2 SQLITE_LIMIT_SQL_LENGTH -1

} $SQLITE_MAX_SQL_LENGTH

do_test sqllimits1-3.3 {

  sqlite3_limit db2 SQLITE_LIMIT_COLUMN -1

} $SQLITE_MAX_COLUMN

do_test sqllimits1-3.4 {

  sqlite3_limit db2 SQLITE_LIMIT_EXPR_DEPTH -1

} $SQLITE_MAX_EXPR_DEPTH

do_test sqllimits1-3.5 {

  sqlite3_limit db2 SQLITE_LIMIT_COMPOUND_SELECT -1

} $SQLITE_MAX_COMPOUND_SELECT

do_test sqllimits1-3.6 {

  sqlite3_limit db2 SQLITE_LIMIT_VDBE_OP -1

} $SQLITE_MAX_VDBE_OP

do_test sqllimits1-3.7 {

  sqlite3_limit db2 SQLITE_LIMIT_FUNCTION_ARG -1

} $SQLITE_MAX_FUNCTION_ARG

do_test sqllimits1-3.8 {

  sqlite3_limit db2 SQLITE_LIMIT_ATTACHED -1

} $SQLITE_MAX_ATTACHED

do_test sqllimits1-3.9 {

  sqlite3_limit db2 SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1

} $SQLITE_MAX_LIKE_PATTERN_LENGTH

do_test sqllimits1-3.10 {

  sqlite3_limit db2 SQLITE_LIMIT_VARIABLE_NUMBER -1

} $SQLITE_MAX_VARIABLE_NUMBER

db2 close

# Attempt to set all limits to the maximum 32-bit integer.  Verify

# that the limit does not exceed the compile-time upper bound.

#

do_test sqllimits1-4.1.1 {

  sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_LENGTH -1

} $SQLITE_MAX_LENGTH

do_test sqllimits1-4.2.1 {

  sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1

} $SQLITE_MAX_SQL_LENGTH

do_test sqllimits1-4.3.1 {

  sqlite3_limit db SQLITE_LIMIT_COLUMN 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_COLUMN -1

} $SQLITE_MAX_COLUMN

do_test sqllimits1-4.4.1 {

  sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1

} $SQLITE_MAX_EXPR_DEPTH

do_test sqllimits1-4.5.1 {

  sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1

} $SQLITE_MAX_COMPOUND_SELECT

do_test sqllimits1-4.6.1 {

  sqlite3_limit db SQLITE_LIMIT_VDBE_OP 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1

} $SQLITE_MAX_VDBE_OP

do_test sqllimits1-4.7.1 {

  sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1

} $SQLITE_MAX_FUNCTION_ARG

do_test sqllimits1-4.8.1 {

  sqlite3_limit db SQLITE_LIMIT_ATTACHED 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_ATTACHED -1

} $SQLITE_MAX_ATTACHED

do_test sqllimits1-4.9.1 {

  sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1

} $SQLITE_MAX_LIKE_PATTERN_LENGTH

do_test sqllimits1-4.10.1 {

  sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER 0x7fffffff

  sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1

} $SQLITE_MAX_VARIABLE_NUMBER

#--------------------------------------------------------------------

# Test cases sqllimits1-5.* test that the SQLITE_MAX_LENGTH limit

# is enforced.

#

db close

sqlite3 db test.db

set LARGESIZE 99999

set SQLITE_LIMIT_LENGTH 100000

sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH

do_test sqllimits1-5.1.1 {

  catchsql { SELECT randomblob(2147483647) }

} {1 {string or blob too big}}

do_test sqllimits1-5.1.2 {

  catchsql { SELECT zeroblob(2147483647) }

} {1 {string or blob too big}}

do_test sqllimits1-5.2 {

  catchsql { SELECT LENGTH(randomblob($::LARGESIZE)) }

} [list 0 $LARGESIZE]

do_test sqllimits1-5.3 {

  catchsql { SELECT quote(randomblob($::LARGESIZE)) }

} {1 {string or blob too big}}

do_test sqllimits1-5.4 {

  catchsql { SELECT LENGTH(zeroblob($::LARGESIZE)) }

} [list 0 $LARGESIZE]

do_test sqllimits1-5.5 {

  catchsql { SELECT quote(zeroblob($::LARGESIZE)) }

} {1 {string or blob too big}}

do_test sqllimits1-5.6 {

  catchsql { SELECT zeroblob(-1) }

} {0 {{}}}

do_test sqllimits1-5.9 {

  set ::str [string repeat A 65537]

  set ::rep [string repeat B 65537]

  catchsql { SELECT replace($::str, 'A', $::rep) }

} {1 {string or blob too big}}

do_test sqllimits1-5.10 {

  set ::str [string repeat %J 2100]

  catchsql { SELECT strftime($::str, '2003-10-31') }

} {1 {string or blob too big}}

do_test sqllimits1-5.11 {

  set ::str1 [string repeat A [expr {$SQLITE_LIMIT_LENGTH - 10}]]

  set ::str2 [string repeat B [expr {$SQLITE_LIMIT_LENGTH - 10}]]

  catchsql { SELECT $::str1 || $::str2 }

} {1 {string or blob too big}}

do_test sqllimits1-5.12 {

  set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]

  catchsql { SELECT quote($::str1) }

} {1 {string or blob too big}}

do_test sqllimits1-5.13 {

  set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]

  catchsql { SELECT hex($::str1) }

} {1 {string or blob too big}}

do_test sqllimits1-5.14.1 {

  set ::STMT [sqlite3_prepare db "SELECT ?" -1 TAIL]

  sqlite3_bind_zeroblob $::STMT 1 [expr {$SQLITE_LIMIT_LENGTH + 1}]

} {}

do_test sqllimits1-5.14.2 {

  sqlite3_step $::STMT 

} {SQLITE_ERROR}

do_test sqllimits1-5.14.3 {

  sqlite3_reset $::STMT 

} {SQLITE_TOOBIG}

do_test sqllimits1-5.14.4 {

  set np1 [expr {$SQLITE_LIMIT_LENGTH + 1}]

  set ::str1 [string repeat A $np1]

  catch {sqlite3_bind_text $::STMT 1 $::str1 -1} res

  set res

} {SQLITE_TOOBIG}

do_test sqllimits1-5.14.5 {

  catch {sqlite3_bind_text16 $::STMT 1 $::str1 -1} res

  set res

} {SQLITE_TOOBIG}

do_test sqllimits1-5.14.6 {

  catch {sqlite3_bind_text $::STMT 1 $::str1 $np1} res

  set res

} {SQLITE_TOOBIG}

do_test sqllimits1-5.14.7 {

  catch {sqlite3_bind_text16 $::STMT 1 $::str1 $np1} res

  set res

} {SQLITE_TOOBIG}

do_test sqllimits1-5.14.8 {

  set n [expr {$np1-1}]

  catch {sqlite3_bind_text $::STMT 1 $::str1 $n} res

  set res

} {}

do_test sqllimits1-5.14.9 {

  catch {sqlite3_bind_text16 $::STMT 1 $::str1 $n} res

  set res

} {}

sqlite3_finalize $::STMT 

do_test sqllimits1-5.15 {

  execsql {

    CREATE TABLE t4(x);

    INSERT INTO t4 VALUES(1);

    INSERT INTO t4 VALUES(2);

    INSERT INTO t4 SELECT 2+x FROM t4;

  }

  catchsql {

    SELECT group_concat(hex(randomblob(20000))) FROM t4;

  }

} {1 {string or blob too big}}

db eval {DROP TABLE t4}

sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff

set strvalue [string repeat A $::SQLITE_LIMIT_LENGTH]

do_test sqllimits1-5.16 {

  catchsql "SELECT '$strvalue'"

} [list 0 $strvalue]

do_test sqllimits1-5.17.1 {

  catchsql "SELECT 'A$strvalue'"

} [list 1 {string or blob too big}]

do_test sqllimits1-5.17.2 {

  sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff

  catchsql {SELECT 'A' || $::strvalue}

} [list 0 A$strvalue]

do_test sqllimits1-5.17.3 {

  sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH

  catchsql {SELECT 'A' || $::strvalue}

} [list 1 {string or blob too big}]

set blobvalue [string repeat 41 $::SQLITE_LIMIT_LENGTH]

do_test sqllimits1-5.18 {

  catchsql "SELECT x'$blobvalue'"

} [list 0 $strvalue]

do_test sqllimits1-5.19 {

  catchsql "SELECT '41$blobvalue'"

} [list 1 {string or blob too big}]

unset blobvalue

ifcapable datetime {

  set strvalue [string repeat D [expr {$SQLITE_LIMIT_LENGTH-12}]]

  do_test sqllimits1-5.20 {

    catchsql {SELECT strftime('%Y ' || $::strvalue, '2008-01-02')}

  } [list 0 [list "2008 $strvalue"]]

  do_test sqllimits1-5.21 {

    catchsql {SELECT strftime('%Y-%m-%d ' || $::strvalue, '2008-01-02')}

  } {1 {string or blob too big}}

}

unset strvalue

#--------------------------------------------------------------------

# Test cases sqllimits1-6.* test that the SQLITE_MAX_SQL_LENGTH limit

# is enforced.

#

# Symbian OS: the next test fails because the max expression depth is limited to 250. 

if {$::tcl_platform(platform)!="symbian"} {

  do_test sqllimits1-6.1 {

    sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000

    set sql "SELECT 1 WHERE 1==1"

    set tail " /* A comment to take up space in order to make the string\

                  longer without increasing the expression depth */\

                  AND   1  ==  1"

    set N [expr {(50000 / [string length $tail])+1}]

    append sql [string repeat $tail $N]

    catchsql $sql

  } {1 {String or BLOB exceeded size limit}}

}

do_test sqllimits1-6.3 {

  sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000

  set sql "SELECT 1 WHERE 1==1"

  set tail " /* A comment to take up space in order to make the string\

                longer without increasing the expression depth */\

                AND   1  ==  1"

  set N [expr {(50000 / [string length $tail])+1}]

  append sql [string repeat $tail $N]

  set nbytes [string length $sql]

  append sql { AND 0}

  set rc [catch {sqlite3_prepare db $sql $nbytes TAIL} STMT]

  lappend rc $STMT

} {1 {(18) statement too long}}

do_test sqllimits1-6.4 {

  sqlite3_errmsg db

} {statement too long}

#--------------------------------------------------------------------

# Test cases sqllimits1-7.* test that the limit set using the

# max_page_count pragma.

#

do_test sqllimits1-7.1 {

  execsql {

    PRAGMA max_page_count = 1000;

  }

} {1000}

do_test sqllimits1-7.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-7.3 {

  execsql {

    INSERT INTO trig VALUES (1,1); 

  }

} {}

do_test sqllimits1-7.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-7.5 {

  catchsql {

    INSERT INTO trig VALUES (1,10);

  }

} {1 {database or disk is full}}

do_test sqllimits1-7.6 {

  catchsql {

    SELECT COUNT(*) FROM trig;

  }

} {0 7}

# Now check the response of the library to opening a file larger than

# the current max_page_count value. The response is to change the

# internal max_page_count value to match the actual size of the file.

if {[db eval {PRAGMA auto_vacuum}]} {

   set fsize 1700

} else {

   set fsize 1691

}

do_test sqllimits1-7.7.1 {

  execsql {

    PRAGMA max_page_count = 1000000;

    CREATE TABLE abc(a, b, c);

    INSERT INTO abc VALUES(1, 2, 3);

    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;

    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;

    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;