1# 2001 September 15 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# This file implements regression tests for SQLite library. The 12# focus of this file is testing the CREATE TABLE statement. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18# Create a basic table and verify it is added to sqlite_master 19# 20do_test table-1.1 { 21 execsql { 22 CREATE TABLE test1 ( 23 one varchar(10), 24 two text 25 ) 26 } 27 execsql { 28 SELECT sql FROM sqlite_master WHERE type!='meta' 29 } 30} {{CREATE TABLE test1 ( 31 one varchar(10), 32 two text 33 )}} 34 35 36# Verify the other fields of the sqlite_master file. 37# 38do_test table-1.3 { 39 execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'} 40} {test1 test1 table} 41 42# Close and reopen the database. Verify that everything is 43# still the same. 44# 45do_test table-1.4 { 46 db close 47 sqlite3 db test.db 48 execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'} 49} {test1 test1 table} 50 51# Drop the database and make sure it disappears. 52# 53do_test table-1.5 { 54 execsql {DROP TABLE test1} 55 execsql {SELECT * FROM sqlite_master WHERE type!='meta'} 56} {} 57 58# Close and reopen the database. Verify that the table is 59# still gone. 60# 61do_test table-1.6 { 62 db close 63 sqlite3 db test.db 64 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 65} {} 66 67# Repeat the above steps, but this time quote the table name. 68# 69do_test table-1.10 { 70 execsql {CREATE TABLE "create" (f1 int)} 71 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 72} {create} 73do_test table-1.11 { 74 execsql {DROP TABLE "create"} 75 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} 76} {} 77do_test table-1.12 { 78 execsql {CREATE TABLE test1("f1 ho" int)} 79 execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'} 80} {test1} 81do_test table-1.13 { 82 execsql {DROP TABLE "TEST1"} 83 execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'} 84} {} 85 86 87 88# Verify that we cannot make two tables with the same name 89# 90do_test table-2.1 { 91 execsql {CREATE TABLE TEST2(one text)} 92 catchsql {CREATE TABLE test2(two text default 'hi')} 93} {1 {table test2 already exists}} 94do_test table-2.1.1 { 95 catchsql {CREATE TABLE "test2" (two)} 96} {1 {table "test2" already exists}} 97do_test table-2.1b { 98 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] 99 lappend v $msg 100} {1 {object name reserved for internal use: sqlite_master}} 101do_test table-2.1c { 102 db close 103 sqlite3 db test.db 104 set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg] 105 lappend v $msg 106} {1 {object name reserved for internal use: sqlite_master}} 107do_test table-2.1d { 108 catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)} 109} {0 {}} 110do_test table-2.1e { 111 catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)} 112} {0 {}} 113do_test table-2.1f { 114 execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'} 115} {} 116 117# Verify that we cannot make a table with the same name as an index 118# 119do_test table-2.2a { 120 execsql {CREATE TABLE test2(one text)} 121 execsql {CREATE INDEX test3 ON test2(one)} 122 catchsql {CREATE TABLE test3(two text)} 123} {1 {there is already an index named test3}} 124do_test table-2.2b { 125 db close 126 sqlite3 db test.db 127 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] 128 lappend v $msg 129} {1 {there is already an index named test3}} 130do_test table-2.2c { 131 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 132} {test2 test3} 133do_test table-2.2d { 134 execsql {DROP INDEX test3} 135 set v [catch {execsql {CREATE TABLE test3(two text)}} msg] 136 lappend v $msg 137} {0 {}} 138do_test table-2.2e { 139 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 140} {test2 test3} 141do_test table-2.2f { 142 execsql {DROP TABLE test2; DROP TABLE test3} 143 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 144} {} 145 146# Create a table with many field names 147# 148set big_table \ 149{CREATE TABLE big( 150 f1 varchar(20), 151 f2 char(10), 152 f3 varchar(30) primary key, 153 f4 text, 154 f5 text, 155 f6 text, 156 f7 text, 157 f8 text, 158 f9 text, 159 f10 text, 160 f11 text, 161 f12 text, 162 f13 text, 163 f14 text, 164 f15 text, 165 f16 text, 166 f17 text, 167 f18 text, 168 f19 text, 169 f20 text 170)} 171do_test table-3.1 { 172 execsql $big_table 173 execsql {SELECT sql FROM sqlite_master WHERE type=='table'} 174} \{$big_table\} 175do_test table-3.2 { 176 set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg] 177 lappend v $msg 178} {1 {table BIG already exists}} 179do_test table-3.3 { 180 set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg] 181 lappend v $msg 182} {1 {table biG already exists}} 183do_test table-3.4 { 184 set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg] 185 lappend v $msg 186} {1 {table bIg already exists}} 187do_test table-3.5 { 188 db close 189 sqlite3 db test.db 190 set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg] 191 lappend v $msg 192} {1 {table Big already exists}} 193do_test table-3.6 { 194 execsql {DROP TABLE big} 195 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 196} {} 197 198# Try creating large numbers of tables 199# 200set r {} 201for {set i 1} {$i<=100} {incr i} { 202 lappend r [format test%03d $i] 203} 204do_test table-4.1 { 205 for {set i 1} {$i<=100} {incr i} { 206 set sql "CREATE TABLE [format test%03d $i] (" 207 for {set k 1} {$k<$i} {incr k} { 208 append sql "field$k text," 209 } 210 append sql "last_field text)" 211 execsql $sql 212 } 213 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 214} $r 215do_test table-4.1b { 216 db close 217 sqlite3 db test.db 218 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 219} $r 220 221# Drop the even numbered tables 222# 223set r {} 224for {set i 1} {$i<=100} {incr i 2} { 225 lappend r [format test%03d $i] 226} 227do_test table-4.2 { 228 for {set i 2} {$i<=100} {incr i 2} { 229 # if {$i==38} {execsql {pragma vdbe_trace=on}} 230 set sql "DROP TABLE [format TEST%03d $i]" 231 execsql $sql 232 } 233 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 234} $r 235#exit 236 237# Drop the odd number tables 238# 239do_test table-4.3 { 240 for {set i 1} {$i<=100} {incr i 2} { 241 set sql "DROP TABLE [format test%03d $i]" 242 execsql $sql 243 } 244 execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name} 245} {} 246 247# Try to drop a table that does not exist 248# 249do_test table-5.1.1 { 250 catchsql {DROP TABLE test009} 251} {1 {no such table: test009}} 252do_test table-5.1.2 { 253 catchsql {DROP TABLE IF EXISTS test009} 254} {0 {}} 255 256# Try to drop sqlite_master 257# 258do_test table-5.2 { 259 catchsql {DROP TABLE IF EXISTS sqlite_master} 260} {1 {table sqlite_master may not be dropped}} 261 262# Dropping sqlite_statN tables is OK. 263# 264do_test table-5.2.1 { 265 db eval { 266 ANALYZE; 267 DROP TABLE IF EXISTS sqlite_stat1; 268 DROP TABLE IF EXISTS sqlite_stat2; 269 DROP TABLE IF EXISTS sqlite_stat3; 270 DROP TABLE IF EXISTS sqlite_stat4; 271 SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_stat*'; 272 } 273} {} 274 275do_test table-5.2.2 { 276 db close 277 forcedelete test.db 278 sqlite3 db test.db 279 sqlite3_db_config db DEFENSIVE 0 280 db eval { 281 CREATE TABLE t0(a,b); 282 CREATE INDEX t ON t0(a); 283 PRAGMA writable_schema=ON; 284 UPDATE sqlite_master SET sql='CREATE TABLE a.b(a UNIQUE'; 285 BEGIN; 286 CREATE TABLE t1(x); 287 ROLLBACK; 288 DROP TABLE IF EXISTS t99; 289 } 290} {} 291db close 292forcedelete test.db 293sqlite3 db test.db 294 295# Make sure an EXPLAIN does not really create a new table 296# 297do_test table-5.3 { 298 ifcapable {explain} { 299 execsql {EXPLAIN CREATE TABLE test1(f1 int)} 300 } 301 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 302} {} 303 304# Make sure an EXPLAIN does not really drop an existing table 305# 306do_test table-5.4 { 307 execsql {CREATE TABLE test1(f1 int)} 308 ifcapable {explain} { 309 execsql {EXPLAIN DROP TABLE test1} 310 } 311 execsql {SELECT name FROM sqlite_master WHERE type!='meta'} 312} {test1} 313 314# Create a table with a goofy name 315# 316#do_test table-6.1 { 317# execsql {CREATE TABLE 'Spaces In This Name!'(x int)} 318# execsql {INSERT INTO 'spaces in this name!' VALUES(1)} 319# set list [glob -nocomplain testdb/spaces*.tbl] 320#} {testdb/spaces+in+this+name+.tbl} 321 322# Try using keywords as table names or column names. 323# 324do_test table-7.1 { 325 set v [catch {execsql { 326 CREATE TABLE weird( 327 desc text, 328 asc text, 329 key int, 330 [14_vac] boolean, 331 fuzzy_dog_12 varchar(10), 332 begin blob, 333 end clob 334 ) 335 }} msg] 336 lappend v $msg 337} {0 {}} 338do_test table-7.2 { 339 execsql { 340 INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all'); 341 SELECT * FROM weird; 342 } 343} {a b 9 0 xyz hi y'all} 344do_test table-7.3 { 345 execsql2 { 346 SELECT * FROM weird; 347 } 348} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} 349do_test table-7.3 { 350 execsql { 351 CREATE TABLE savepoint(release); 352 INSERT INTO savepoint(release) VALUES(10); 353 UPDATE savepoint SET release = 5; 354 SELECT release FROM savepoint; 355 } 356} {5} 357 358# Try out the CREATE TABLE AS syntax 359# 360do_test table-8.1 { 361 execsql2 { 362 CREATE TABLE t2 AS SELECT * FROM weird; 363 SELECT * FROM t2; 364 } 365} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} 366do_test table-8.1.1 { 367 execsql { 368 SELECT sql FROM sqlite_master WHERE name='t2'; 369 } 370} {{CREATE TABLE t2( 371 "desc" TEXT, 372 "asc" TEXT, 373 "key" INT, 374 "14_vac" NUM, 375 fuzzy_dog_12 TEXT, 376 "begin", 377 "end" TEXT 378)}} 379do_test table-8.2 { 380 execsql { 381 CREATE TABLE "t3""xyz"(a,b,c); 382 INSERT INTO [t3"xyz] VALUES(1,2,3); 383 SELECT * FROM [t3"xyz]; 384 } 385} {1 2 3} 386do_test table-8.3 { 387 execsql2 { 388 CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz]; 389 SELECT * FROM [t4"abc]; 390 } 391} {cnt 1 max(b+c) 5} 392 393# Update for v3: The declaration type of anything except a column is now a 394# NULL pointer, so the created table has no column types. (Changed result 395# from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}). 396do_test table-8.3.1 { 397 execsql { 398 SELECT sql FROM sqlite_master WHERE name='t4"abc' 399 } 400} {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}} 401 402ifcapable tempdb { 403 do_test table-8.4 { 404 execsql2 { 405 CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz]; 406 SELECT * FROM t5; 407 } 408 } {y'all 1} 409} 410 411do_test table-8.5 { 412 db close 413 sqlite3 db test.db 414 execsql2 { 415 SELECT * FROM [t4"abc]; 416 } 417} {cnt 1 max(b+c) 5} 418do_test table-8.6 { 419 execsql2 { 420 SELECT * FROM t2; 421 } 422} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all} 423do_test table-8.7 { 424 catchsql { 425 SELECT * FROM t5; 426 } 427} {1 {no such table: t5}} 428do_test table-8.8 { 429 catchsql { 430 CREATE TABLE t5 AS SELECT * FROM no_such_table; 431 } 432} {1 {no such table: no_such_table}} 433 434do_test table-8.9 { 435 execsql { 436 CREATE TABLE t10("col.1" [char.3]); 437 CREATE TABLE t11 AS SELECT * FROM t10; 438 SELECT sql FROM sqlite_master WHERE name = 't11'; 439 } 440} {{CREATE TABLE t11("col.1" TEXT)}} 441do_test table-8.10 { 442 execsql { 443 CREATE TABLE t12( 444 a INTEGER, 445 b VARCHAR(10), 446 c VARCHAR(1,10), 447 d VARCHAR(+1,-10), 448 e VARCHAR (+1,-10), 449 f "VARCHAR (+1,-10, 5)", 450 g BIG INTEGER 451 ); 452 CREATE TABLE t13 AS SELECT * FROM t12; 453 SELECT sql FROM sqlite_master WHERE name = 't13'; 454 } 455} {{CREATE TABLE t13( 456 a INT, 457 b TEXT, 458 c TEXT, 459 d TEXT, 460 e TEXT, 461 f TEXT, 462 g INT 463)}} 464 465# Make sure we cannot have duplicate column names within a table. 466# 467do_test table-9.1 { 468 catchsql { 469 CREATE TABLE t6(a,b,a); 470 } 471} {1 {duplicate column name: a}} 472do_test table-9.2 { 473 catchsql { 474 CREATE TABLE t6(a varchar(100), b blob, a integer); 475 } 476} {1 {duplicate column name: a}} 477 478# Check the foreign key syntax. 479# 480ifcapable {foreignkey} { 481do_test table-10.1 { 482 catchsql { 483 CREATE TABLE t6(a REFERENCES t4(a) NOT NULL); 484 INSERT INTO t6 VALUES(NULL); 485 } 486} {1 {NOT NULL constraint failed: t6.a}} 487do_test table-10.2 { 488 catchsql { 489 DROP TABLE t6; 490 CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL); 491 } 492} {0 {}} 493do_test table-10.3 { 494 catchsql { 495 DROP TABLE t6; 496 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL); 497 } 498} {0 {}} 499do_test table-10.4 { 500 catchsql { 501 DROP TABLE t6; 502 CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1); 503 } 504} {0 {}} 505do_test table-10.5 { 506 catchsql { 507 DROP TABLE t6; 508 CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE); 509 } 510} {0 {}} 511do_test table-10.6 { 512 catchsql { 513 DROP TABLE t6; 514 CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED); 515 } 516} {0 {}} 517do_test table-10.7 { 518 catchsql { 519 DROP TABLE t6; 520 CREATE TABLE t6(a, 521 FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED 522 ); 523 } 524} {0 {}} 525do_test table-10.8 { 526 catchsql { 527 DROP TABLE t6; 528 CREATE TABLE t6(a,b,c, 529 FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL 530 ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED 531 ); 532 } 533} {0 {}} 534do_test table-10.9 { 535 catchsql { 536 DROP TABLE t6; 537 CREATE TABLE t6(a,b,c, 538 FOREIGN KEY (b,c) REFERENCES t4(x) 539 ); 540 } 541} {1 {number of columns in foreign key does not match the number of columns in the referenced table}} 542do_test table-10.10 { 543 catchsql {DROP TABLE t6} 544 catchsql { 545 CREATE TABLE t6(a,b,c, 546 FOREIGN KEY (b,c) REFERENCES t4(x,y,z) 547 ); 548 } 549} {1 {number of columns in foreign key does not match the number of columns in the referenced table}} 550do_test table-10.11 { 551 catchsql {DROP TABLE t6} 552 catchsql { 553 CREATE TABLE t6(a,b, c REFERENCES t4(x,y)); 554 } 555} {1 {foreign key on c should reference only one column of table t4}} 556do_test table-10.12 { 557 catchsql {DROP TABLE t6} 558 catchsql { 559 CREATE TABLE t6(a,b,c, 560 FOREIGN KEY (b,x) REFERENCES t4(x,y) 561 ); 562 } 563} {1 {unknown column "x" in foreign key definition}} 564do_test table-10.13 { 565 catchsql {DROP TABLE t6} 566 catchsql { 567 CREATE TABLE t6(a,b,c, 568 FOREIGN KEY (x,b) REFERENCES t4(x,y) 569 ); 570 } 571} {1 {unknown column "x" in foreign key definition}} 572} ;# endif foreignkey 573 574# Test for the "typeof" function. More tests for the 575# typeof() function are found in bind.test and types.test. 576# 577do_test table-11.1 { 578 execsql { 579 CREATE TABLE t7( 580 a integer primary key, 581 b number(5,10), 582 c character varying (8), 583 d VARCHAR(9), 584 e clob, 585 f BLOB, 586 g Text, 587 h 588 ); 589 INSERT INTO t7(a) VALUES(1); 590 SELECT typeof(a), typeof(b), typeof(c), typeof(d), 591 typeof(e), typeof(f), typeof(g), typeof(h) 592 FROM t7 LIMIT 1; 593 } 594} {integer null null null null null null null} 595do_test table-11.2 { 596 execsql { 597 SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d) 598 FROM t7 LIMIT 1; 599 } 600} {null null null null} 601 602# Test that when creating a table using CREATE TABLE AS, column types are 603# assigned correctly for (SELECT ...) and 'x AS y' expressions. 604do_test table-12.1 { 605 ifcapable subquery { 606 execsql { 607 CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7; 608 } 609 } else { 610 execsql { 611 CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7; 612 } 613 } 614} {} 615do_test table-12.2 { 616 execsql { 617 SELECT sql FROM sqlite_master WHERE tbl_name = 't8' 618 } 619} {{CREATE TABLE t8(b NUM,h,i INT,j)}} 620 621#-------------------------------------------------------------------- 622# Test cases table-13.* 623# 624# Test the ability to have default values of CURRENT_TIME, CURRENT_DATE 625# and CURRENT_TIMESTAMP. 626# 627do_test table-13.1 { 628 execsql { 629 CREATE TABLE tablet8( 630 a integer primary key, 631 tm text DEFAULT CURRENT_TIME, 632 dt text DEFAULT CURRENT_DATE, 633 dttm text DEFAULT CURRENT_TIMESTAMP 634 ); 635 SELECT * FROM tablet8; 636 } 637} {} 638set i 0 639unset -nocomplain date time seconds 640foreach {date time seconds} { 641 1976-07-04 12:00:00 205329600 642 1994-04-16 14:00:00 766504800 643 2000-01-01 00:00:00 946684800 644 2003-12-31 12:34:56 1072874096 645} { 646 incr i 647 set sqlite_current_time $seconds 648 do_test table-13.2.$i { 649 execsql " 650 INSERT INTO tablet8(a) VALUES($i); 651 SELECT tm, dt, dttm FROM tablet8 WHERE a=$i; 652 " 653 } [list $time $date [list $date $time]] 654} 655set sqlite_current_time 0 656 657#-------------------------------------------------------------------- 658# Test cases table-14.* 659# 660# Test that a table cannot be created or dropped while other virtual 661# machines are active. This is required because otherwise when in 662# auto-vacuum mode the btree-layer may need to move the root-pages of 663# a table for which there is an open cursor. 664# 665# 2007-05-02: A open btree cursor no longer blocks CREATE TABLE. 666# But DROP TABLE is still prohibited because we do not want to 667# delete a table out from under a running query. 668# 669 670# db eval { 671# pragma vdbe_trace = 0; 672# } 673# Try to create a table from within a callback: 674unset -nocomplain result 675do_test table-14.1 { 676 set rc [ 677 catch { 678 db eval {SELECT * FROM tablet8 LIMIT 1} {} { 679 db eval {CREATE TABLE t9(a, b, c)} 680 } 681 } msg 682 ] 683 set result [list $rc $msg] 684} {0 {}} 685 686# Try to drop a table from within a callback: 687do_test table-14.2 { 688 set rc [ 689 catch { 690 db eval {SELECT * FROM tablet8 LIMIT 1} {} { 691 db eval {DROP TABLE t9;} 692 } 693 } msg 694 ] 695 set result [list $rc $msg] 696} {1 {database table is locked}} 697 698ifcapable attach { 699 # Now attach a database and ensure that a table can be created in the 700 # attached database whilst in a callback from a query on the main database. 701 do_test table-14.3 { 702 forcedelete test2.db 703 forcedelete test2.db-journal 704 execsql { 705 ATTACH 'test2.db' as aux; 706 } 707 db eval {SELECT * FROM tablet8 LIMIT 1} {} { 708 db eval {CREATE TABLE aux.t1(a, b, c)} 709 } 710 } {} 711 712 # On the other hand, it should be impossible to drop a table when any VMs 713 # are active. This is because VerifyCookie instructions may have already 714 # been executed, and btree root-pages may not move after this (which a 715 # delete table might do). 716 do_test table-14.4 { 717 set rc [ 718 catch { 719 db eval {SELECT * FROM tablet8 LIMIT 1} {} { 720 db eval {DROP TABLE aux.t1;} 721 } 722 } msg 723 ] 724 set result [list $rc $msg] 725 } {1 {database table is locked}} 726} 727 728# Create and drop 2000 tables. This is to check that the balance_shallow() 729# routine works correctly on the sqlite_master table. At one point it 730# contained a bug that would prevent the right-child pointer of the 731# child page from being copied to the root page. 732# 733do_test table-15.1 { 734 execsql {BEGIN} 735 for {set i 0} {$i<2000} {incr i} { 736 execsql "CREATE TABLE tbl$i (a, b, c)" 737 } 738 execsql {COMMIT} 739} {} 740do_test table-15.2 { 741 execsql {BEGIN} 742 for {set i 0} {$i<2000} {incr i} { 743 execsql "DROP TABLE tbl$i" 744 } 745 execsql {COMMIT} 746} {} 747 748# Ticket 3a88d85f36704eebe134f7f48aebf00cd6438c1a (2014-08-05) 749# The following SQL script segfaults while running the INSERT statement: 750# 751# CREATE TABLE t1(x DEFAULT(max(1))); 752# INSERT INTO t1(rowid) VALUES(1); 753# 754# The problem appears to be the use of an aggregate function as part of 755# the default value for a column. This problem has been in the code since 756# at least 2006-01-01 and probably before that. This problem was detected 757# and reported on the [email protected] mailing list by Zsbán Ambrus. 758# 759do_execsql_test table-16.1 { 760 CREATE TABLE t16(x DEFAULT(max(1))); 761 INSERT INTO t16(x) VALUES(123); 762 SELECT rowid, x FROM t16; 763} {1 123} 764do_catchsql_test table-16.2 { 765 INSERT INTO t16(rowid) VALUES(4); 766} {1 {unknown function: max()}} 767do_execsql_test table-16.3 { 768 DROP TABLE t16; 769 CREATE TABLE t16(x DEFAULT(abs(1))); 770 INSERT INTO t16(rowid) VALUES(4); 771 SELECT rowid, x FROM t16; 772} {4 1} 773do_catchsql_test table-16.4 { 774 DROP TABLE t16; 775 CREATE TABLE t16(x DEFAULT(avg(1))); 776 INSERT INTO t16(rowid) VALUES(123); 777 SELECT rowid, x FROM t16; 778} {1 {unknown function: avg()}} 779do_catchsql_test table-16.5 { 780 DROP TABLE t16; 781 CREATE TABLE t16(x DEFAULT(count())); 782 INSERT INTO t16(rowid) VALUES(123); 783 SELECT rowid, x FROM t16; 784} {1 {unknown function: count()}} 785do_catchsql_test table-16.6 { 786 DROP TABLE t16; 787 CREATE TABLE t16(x DEFAULT(group_concat('x',','))); 788 INSERT INTO t16(rowid) VALUES(123); 789 SELECT rowid, x FROM t16; 790} {1 {unknown function: group_concat()}} 791do_catchsql_test table-16.7 { 792 INSERT INTO t16 DEFAULT VALUES; 793} {1 {unknown function: group_concat()}} 794 795# Ticket [https://www.sqlite.org/src/info/094d39a4c95ee4abbc417f04214617675ba15c63] 796# describes a assertion fault that occurs on a CREATE TABLE .. AS SELECT statement. 797# the following test verifies that the problem has been fixed. 798# 799do_execsql_test table-17.1 { 800 DROP TABLE IF EXISTS t1; 801 CREATE TABLE t1(a TEXT); 802 INSERT INTO t1(a) VALUES(1),(2); 803 DROP TABLE IF EXISTS t2; 804 CREATE TABLE t2(x TEXT, y TEXT); 805 INSERT INTO t2(x,y) VALUES(3,4); 806 DROP TABLE IF EXISTS t3; 807 CREATE TABLE t3 AS 808 SELECT a AS p, coalesce(y,a) AS q FROM t1 LEFT JOIN t2 ON a=x; 809 SELECT p, q, '|' FROM t3 ORDER BY p; 810} {1 1 | 2 2 |} 811 812# 2015-06-16 813# Ticket [https://www.sqlite.org/src/tktview/873cae2b6e25b1991ce5e9b782f9cd0409b96063] 814# Make sure a CREATE TABLE AS statement correctly rolls back partial changes to the 815# sqlite_master table when the SELECT on the right-hand side aborts. 816# 817do_catchsql_test table-18.1 { 818 DROP TABLE IF EXISTS t1; 819 BEGIN; 820 CREATE TABLE t1 AS SELECT zeroblob(2e20); 821} {1 {string or blob too big}} 822do_execsql_test table-18.2 { 823 COMMIT; 824 PRAGMA integrity_check; 825} {ok} 826 827# 2015-09-09 828# Ticket [https://www.sqlite.org/src/info/acd12990885d9276] 829# "CREATE TABLE ... AS SELECT ... FROM sqlite_master" fails because the row 830# in the sqlite_master table for the next table is initially populated 831# with a NULL instead of a record created by OP_Record. 832# 833do_execsql_test table-19.1 { 834 CREATE TABLE t19 AS SELECT * FROM sqlite_master; 835 SELECT name FROM t19 ORDER BY name; 836} {{} savepoint t10 t11 t12 t13 t16 t2 t3 t3\"xyz t4\"abc t7 t8 t9 tablet8 test1 weird} 837 838 839 840finish_test 841