1*1da57d55SToomas Soome# 2c5c4113dSnw141292# 2001 September 23 3c5c4113dSnw141292# 4c5c4113dSnw141292# The author disclaims copyright to this source code. In place of 5c5c4113dSnw141292# a legal notice, here is a blessing: 6c5c4113dSnw141292# 7c5c4113dSnw141292# May you do good and not evil. 8c5c4113dSnw141292# May you find forgiveness for yourself and forgive others. 9c5c4113dSnw141292# May you share freely, never taking more than you give. 10c5c4113dSnw141292# 11c5c4113dSnw141292#*********************************************************************** 12c5c4113dSnw141292# This file implements regression tests for SQLite library. The 13c5c4113dSnw141292# focus of this file is stressing the library by putting large amounts 14c5c4113dSnw141292# of data in a single row of a table. 15c5c4113dSnw141292# 16c5c4113dSnw141292# $Id: bigrow.test,v 1.4 2001/11/24 00:31:47 drh Exp $ 17c5c4113dSnw141292 18c5c4113dSnw141292set testdir [file dirname $argv0] 19c5c4113dSnw141292source $testdir/tester.tcl 20c5c4113dSnw141292 21c5c4113dSnw141292# Make a big string that we can use for test data 22c5c4113dSnw141292# 23c5c4113dSnw141292do_test bigrow-1.0 { 24c5c4113dSnw141292 set ::bigstr {} 25c5c4113dSnw141292 for {set i 1} {$i<=9999} {incr i} { 26c5c4113dSnw141292 set sep [string index "abcdefghijklmnopqrstuvwxyz" [expr {$i%26}]] 27c5c4113dSnw141292 append ::bigstr "$sep [format %04d $i] " 28c5c4113dSnw141292 } 29c5c4113dSnw141292 string length $::bigstr 30c5c4113dSnw141292} {69993} 31c5c4113dSnw141292 32c5c4113dSnw141292# Make a table into which we can insert some but records. 33c5c4113dSnw141292# 34c5c4113dSnw141292do_test bigrow-1.1 { 35c5c4113dSnw141292 execsql { 36c5c4113dSnw141292 CREATE TABLE t1(a text, b text, c text); 37c5c4113dSnw141292 SELECT name FROM sqlite_master 38c5c4113dSnw141292 WHERE type='table' OR type='index' 39c5c4113dSnw141292 ORDER BY name 40c5c4113dSnw141292 } 41c5c4113dSnw141292} {t1} 42c5c4113dSnw141292 43c5c4113dSnw141292do_test bigrow-1.2 { 44c5c4113dSnw141292 set ::big1 [string range $::bigstr 0 65519] 45c5c4113dSnw141292 set sql "INSERT INTO t1 VALUES('abc'," 46c5c4113dSnw141292 append sql "'$::big1', 'xyz');" 47c5c4113dSnw141292 execsql $sql 48c5c4113dSnw141292 execsql {SELECT a, c FROM t1} 49c5c4113dSnw141292} {abc xyz} 50c5c4113dSnw141292do_test bigrow-1.3 { 51c5c4113dSnw141292 execsql {SELECT b FROM t1} 52c5c4113dSnw141292} [list $::big1] 53c5c4113dSnw141292do_test bigrow-1.4 { 54c5c4113dSnw141292 set ::big2 [string range $::bigstr 0 65520] 55c5c4113dSnw141292 set sql "INSERT INTO t1 VALUES('abc2'," 56c5c4113dSnw141292 append sql "'$::big2', 'xyz2');" 57c5c4113dSnw141292 set r [catch {execsql $sql} msg] 58c5c4113dSnw141292 lappend r $msg 59c5c4113dSnw141292} {0 {}} 60c5c4113dSnw141292do_test bigrow-1.4.1 { 61c5c4113dSnw141292 execsql {SELECT b FROM t1 ORDER BY c} 62c5c4113dSnw141292} [list $::big1 $::big2] 63c5c4113dSnw141292do_test bigrow-1.4.2 { 64c5c4113dSnw141292 execsql {SELECT c FROM t1 ORDER BY c} 65c5c4113dSnw141292} {xyz xyz2} 66c5c4113dSnw141292do_test bigrow-1.4.3 { 67c5c4113dSnw141292 execsql {DELETE FROM t1 WHERE a='abc2'} 68c5c4113dSnw141292 execsql {SELECT c FROM t1} 69c5c4113dSnw141292} {xyz} 70c5c4113dSnw141292 71c5c4113dSnw141292do_test bigrow-1.5 { 72c5c4113dSnw141292 execsql { 73c5c4113dSnw141292 UPDATE t1 SET a=b, b=a; 74c5c4113dSnw141292 SELECT b,c FROM t1 75c5c4113dSnw141292 } 76c5c4113dSnw141292} {abc xyz} 77c5c4113dSnw141292do_test bigrow-1.6 { 78c5c4113dSnw141292 execsql { 79c5c4113dSnw141292 SELECT * FROM t1 80c5c4113dSnw141292 } 81c5c4113dSnw141292} [list $::big1 abc xyz] 82c5c4113dSnw141292do_test bigrow-1.7 { 83c5c4113dSnw141292 execsql { 84c5c4113dSnw141292 INSERT INTO t1 VALUES('1','2','3'); 85c5c4113dSnw141292 INSERT INTO t1 VALUES('A','B','C'); 86c5c4113dSnw141292 SELECT b FROM t1 WHERE a=='1'; 87c5c4113dSnw141292 } 88c5c4113dSnw141292} {2} 89c5c4113dSnw141292do_test bigrow-1.8 { 90c5c4113dSnw141292 execsql "SELECT b FROM t1 WHERE a=='$::big1'" 91c5c4113dSnw141292} {abc} 92c5c4113dSnw141292do_test bigrow-1.9 { 93c5c4113dSnw141292 execsql "SELECT b FROM t1 WHERE a!='$::big1' ORDER BY a" 94c5c4113dSnw141292} {2 B} 95c5c4113dSnw141292 96c5c4113dSnw141292# Try doing some indexing on big columns 97c5c4113dSnw141292# 98c5c4113dSnw141292do_test bigrow-2.1 { 99c5c4113dSnw141292 execsql { 100c5c4113dSnw141292 CREATE INDEX i1 ON t1(a) 101c5c4113dSnw141292 } 102c5c4113dSnw141292 execsql "SELECT b FROM t1 WHERE a=='$::big1'" 103c5c4113dSnw141292} {abc} 104c5c4113dSnw141292do_test bigrow-2.2 { 105c5c4113dSnw141292 execsql { 106c5c4113dSnw141292 UPDATE t1 SET a=b, b=a 107c5c4113dSnw141292 } 108c5c4113dSnw141292 execsql "SELECT b FROM t1 WHERE a=='abc'" 109c5c4113dSnw141292} [list $::big1] 110c5c4113dSnw141292do_test bigrow-2.3 { 111c5c4113dSnw141292 execsql { 112c5c4113dSnw141292 UPDATE t1 SET a=b, b=a 113c5c4113dSnw141292 } 114c5c4113dSnw141292 execsql "SELECT b FROM t1 WHERE a=='$::big1'" 115c5c4113dSnw141292} {abc} 116c5c4113dSnw141292catch {unset ::bigstr} 117c5c4113dSnw141292catch {unset ::big1} 118c5c4113dSnw141292catch {unset ::big2} 119c5c4113dSnw141292 120c5c4113dSnw141292# Mosts of the tests above were created back when rows were limited in 121c5c4113dSnw141292# size to 64K. Now rows can be much bigger. Test that logic. Also 122c5c4113dSnw141292# make sure things work correctly at the transition boundries between 123c5c4113dSnw141292# row sizes of 256 to 257 bytes and from 65536 to 65537 bytes. 124c5c4113dSnw141292# 125c5c4113dSnw141292# We begin by testing the 256..257 transition. 126c5c4113dSnw141292# 127c5c4113dSnw141292do_test bigrow-3.1 { 128c5c4113dSnw141292 execsql { 129c5c4113dSnw141292 DELETE FROM t1; 130c5c4113dSnw141292 INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi'); 131c5c4113dSnw141292 } 132c5c4113dSnw141292 execsql {SELECT a,length(b),c FROM t1} 133c5c4113dSnw141292} {one 30 hi} 134c5c4113dSnw141292do_test bigrow-3.2 { 135c5c4113dSnw141292 execsql { 136c5c4113dSnw141292 UPDATE t1 SET b=b||b; 137c5c4113dSnw141292 UPDATE t1 SET b=b||b; 138c5c4113dSnw141292 UPDATE t1 SET b=b||b; 139c5c4113dSnw141292 } 140c5c4113dSnw141292 execsql {SELECT a,length(b),c FROM t1} 141c5c4113dSnw141292} {one 240 hi} 142c5c4113dSnw141292for {set i 1} {$i<10} {incr i} { 143c5c4113dSnw141292 do_test bigrow-3.3.$i { 144c5c4113dSnw141292 execsql "UPDATE t1 SET b=b||'$i'" 145c5c4113dSnw141292 execsql {SELECT a,length(b),c FROM t1} 146c5c4113dSnw141292 } "one [expr {240+$i}] hi" 147c5c4113dSnw141292} 148c5c4113dSnw141292 149c5c4113dSnw141292# Now test the 65536..65537 row-size transition. 150c5c4113dSnw141292# 151c5c4113dSnw141292do_test bigrow-4.1 { 152c5c4113dSnw141292 execsql { 153c5c4113dSnw141292 DELETE FROM t1; 154c5c4113dSnw141292 INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi'); 155c5c4113dSnw141292 } 156c5c4113dSnw141292 execsql {SELECT a,length(b),c FROM t1} 157c5c4113dSnw141292} {one 30 hi} 158c5c4113dSnw141292do_test bigrow-4.2 { 159c5c4113dSnw141292 execsql { 160c5c4113dSnw141292 UPDATE t1 SET b=b||b; 161c5c4113dSnw141292 UPDATE t1 SET b=b||b; 162c5c4113dSnw141292 UPDATE t1 SET b=b||b; 163c5c4113dSnw141292 UPDATE t1 SET b=b||b; 164c5c4113dSnw141292 UPDATE t1 SET b=b||b; 165c5c4113dSnw141292 UPDATE t1 SET b=b||b; 166c5c4113dSnw141292 UPDATE t1 SET b=b||b; 167c5c4113dSnw141292 UPDATE t1 SET b=b||b; 168c5c4113dSnw141292 UPDATE t1 SET b=b||b; 169c5c4113dSnw141292 UPDATE t1 SET b=b||b; 170c5c4113dSnw141292 UPDATE t1 SET b=b||b; 171c5c4113dSnw141292 UPDATE t1 SET b=b||b; 172c5c4113dSnw141292 } 173c5c4113dSnw141292 execsql {SELECT a,length(b),c FROM t1} 174c5c4113dSnw141292} {one 122880 hi} 175c5c4113dSnw141292do_test bigrow-4.3 { 176c5c4113dSnw141292 execsql { 177c5c4113dSnw141292 UPDATE t1 SET b=substr(b,1,65515) 178c5c4113dSnw141292 } 179c5c4113dSnw141292 execsql {SELECT a,length(b),c FROM t1} 180c5c4113dSnw141292} {one 65515 hi} 181c5c4113dSnw141292for {set i 1} {$i<10} {incr i} { 182c5c4113dSnw141292 do_test bigrow-4.4.$i { 183c5c4113dSnw141292 execsql "UPDATE t1 SET b=b||'$i'" 184c5c4113dSnw141292 execsql {SELECT a,length(b),c FROM t1} 185c5c4113dSnw141292 } "one [expr {65515+$i}] hi" 186c5c4113dSnw141292} 187c5c4113dSnw141292 188c5c4113dSnw141292# Check to make sure the library recovers safely if a row contains 189c5c4113dSnw141292# too much data. 190c5c4113dSnw141292# 191c5c4113dSnw141292do_test bigrow-5.1 { 192c5c4113dSnw141292 execsql { 193c5c4113dSnw141292 DELETE FROM t1; 194c5c4113dSnw141292 INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi'); 195c5c4113dSnw141292 } 196c5c4113dSnw141292 execsql {SELECT a,length(b),c FROM t1} 197c5c4113dSnw141292} {one 30 hi} 198c5c4113dSnw141292set i 1 199c5c4113dSnw141292for {set sz 60} {$sz<1048560} {incr sz $sz} { 200c5c4113dSnw141292 do_test bigrow-5.2.$i { 201c5c4113dSnw141292 execsql { 202c5c4113dSnw141292 UPDATE t1 SET b=b||b; 203c5c4113dSnw141292 SELECT a,length(b),c FROM t1; 204c5c4113dSnw141292 } 205c5c4113dSnw141292 } "one $sz hi" 206c5c4113dSnw141292 incr i 207c5c4113dSnw141292} 208c5c4113dSnw141292do_test bigrow-5.3 { 209c5c4113dSnw141292 set r [catch {execsql {UPDATE t1 SET b=b||b}} msg] 210c5c4113dSnw141292 lappend r $msg 211c5c4113dSnw141292} {1 {too much data for one table row}} 212c5c4113dSnw141292do_test bigrow-5.4 { 213c5c4113dSnw141292 execsql {DROP TABLE t1} 214c5c4113dSnw141292} {} 215c5c4113dSnw141292 216c5c4113dSnw141292finish_test 217