test/table.test

   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
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 # Create a basic table and verify it is added to sqlite_master
  19 #
  20 do_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 #
  38 do_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 #
  45 do_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 #
  53 do_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 #
  61 do_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 #
  69 do_test table-1.10 {
  70   execsql {CREATE TABLE "create" (f1 int)}
  71   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
  72 } {create}
  73 do_test table-1.11 {
  74   execsql {DROP TABLE "create"}
  75   execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
  76 } {}
  77 do_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}
  81 do_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 #
  90 do_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}}
  94 do_test table-2.1.1 {
  95   catchsql {CREATE TABLE "test2" (two)}
  96 } {1 {table "test2" already exists}}
  97 do_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}}
 101 do_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}}
 107 do_test table-2.1d {
 108   catchsql {CREATE TABLE IF NOT EXISTS test2(x,y)}
 109 } {0 {}}
 110 do_test table-2.1e {
 111   catchsql {CREATE TABLE IF NOT EXISTS test2(x UNIQUE, y TEXT PRIMARY KEY)}
 112 } {0 {}}
 113 do_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 #
 119 do_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}}
 124 do_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}}
 130 do_test table-2.2c {
 131   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 132 } {test2 test3}
 133 do_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 {}}
 138 do_test table-2.2e {
 139   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 140 } {test2 test3}
 141 do_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 #
 148 set 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 )}
 171 do_test table-3.1 {
 172   execsql $big_table
 173   execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
 174 } \{$big_table\}
 175 do_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}}
 179 do_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}}
 183 do_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}}
 187 do_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}}
 193 do_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 #
 200 set r {}
 201 for {set i 1} {$i<=100} {incr i} {
 202   lappend r [format test%03d $i]
 203 }
 204 do_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
 215 do_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 #
 223 set r {}
 224 for {set i 1} {$i<=100} {incr i 2} {
 225   lappend r [format test%03d $i]
 226 }
 227 do_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 #
 239 do_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 #
 249 do_test table-5.1.1 {
 250   catchsql {DROP TABLE test009}
 251 } {1 {no such table: test009}}
 252 do_test table-5.1.2 {
 253   catchsql {DROP TABLE IF EXISTS test009}
 254 } {0 {}}
 255
 256 # Try to drop sqlite_master
 257 #
 258 do_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 #
 264 do_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
 275 do_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 } {}
 291 db close
 292 forcedelete test.db
 293 sqlite3 db test.db
 294
 295 # Make sure an EXPLAIN does not really create a new table
 296 #
 297 do_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 #
 306 do_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 #
 324 do_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 {}}
 338 do_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}
 344 do_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}
 349 do_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 #
 360 do_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}
 366 do_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 )}}
 379 do_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}
 386 do_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)}}).
 396 do_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
 402 ifcapable 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
 411 do_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}
 418 do_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}
 423 do_test table-8.7 {
 424   catchsql {
 425     SELECT * FROM t5;
 426   }
 427 } {1 {no such table: t5}}
 428 do_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
 434 do_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)}}
 441 do_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 #
 467 do_test table-9.1 {
 468   catchsql {
 469     CREATE TABLE t6(a,b,a);
 470   }
 471 } {1 {duplicate column name: a}}
 472 do_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 #
 480 ifcapable {foreignkey} {
 481 do_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}}
 487 do_test table-10.2 {
 488   catchsql {
 489     DROP TABLE t6;
 490     CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
 491   }
 492 } {0 {}}
 493 do_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 {}}
 499 do_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 {}}
 505 do_test table-10.5 {
 506   catchsql {
 507     DROP TABLE t6;
 508     CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
 509   }
 510 } {0 {}}
 511 do_test table-10.6 {
 512   catchsql {
 513     DROP TABLE t6;
 514     CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
 515   }
 516 } {0 {}}
 517 do_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 {}}
 525 do_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 {}}
 534 do_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}}
 542 do_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}}
 550 do_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}}
 556 do_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}}
 564 do_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 #
 577 do_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}
 595 do_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.
 604 do_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 } {}
 615 do_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 #
 627 do_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 } {}
 638 set i 0
 639 unset -nocomplain date time seconds
 640 foreach {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 }
 655 set 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:
 674 unset -nocomplain result
 675 do_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:
 687 do_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
 698 ifcapable 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 #
 733 do_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 } {}
 740 do_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 sqlite-users@sqlite.org mailing list by Zsbán Ambrus.
 758 #
 759 do_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}
 764 do_catchsql_test table-16.2 {
 765   INSERT INTO t16(rowid) VALUES(4);
 766 } {1 {unknown function: max()}}
 767 do_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}
 773 do_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()}}
 779 do_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()}}
 785 do_catchsql_test table-16.6 {
 786   DROP TABLE t16;
 787   CREATE TABLE t16(x DEFAULT(string_agg('x',',')));
 788   INSERT INTO t16(rowid) VALUES(123);
 789   SELECT rowid, x FROM t16;
 790 } {1 {unknown function: string_agg()}}
 791 do_catchsql_test table-16.7 {
 792   INSERT INTO t16 DEFAULT VALUES;
 793 } {1 {unknown function: string_agg()}}
 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 #
 799 do_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 #
 817 do_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}}
 822 do_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 #
 833 do_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
 840 finish_test