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   db eval {
 280     CREATE TABLE t0(a,b);
 281     CREATE INDEX t ON t0(a);
 282     PRAGMA writable_schema=ON;
 283     UPDATE sqlite_master SET sql='CREATE TABLE a.b(a UNIQUE';
 284     BEGIN;
 285     CREATE TABLE t1(x);
 286     ROLLBACK;
 287     DROP TABLE IF EXISTS t99;
 288   }
 289 } {}
 290 db close
 291 forcedelete test.db
 292 sqlite3 db test.db
 293
 294 # Make sure an EXPLAIN does not really create a new table
 295 #
 296 do_test table-5.3 {
 297   ifcapable {explain} {
 298     execsql {EXPLAIN CREATE TABLE test1(f1 int)}
 299   }
 300   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 301 } {}
 302
 303 # Make sure an EXPLAIN does not really drop an existing table
 304 #
 305 do_test table-5.4 {
 306   execsql {CREATE TABLE test1(f1 int)}
 307   ifcapable {explain} {
 308     execsql {EXPLAIN DROP TABLE test1}
 309   }
 310   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 311 } {test1}
 312
 313 # Create a table with a goofy name
 314 #
 315 #do_test table-6.1 {
 316 #  execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
 317 #  execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
 318 #  set list [glob -nocomplain testdb/spaces*.tbl]
 319 #} {testdb/spaces+in+this+name+.tbl}
 320
 321 # Try using keywords as table names or column names.
 322 #
 323 do_test table-7.1 {
 324   set v [catch {execsql {
 325     CREATE TABLE weird(
 326       desc text,
 327       asc text,
 328       key int,
 329       [14_vac] boolean,
 330       fuzzy_dog_12 varchar(10),
 331       begin blob,
 332       end clob
 333     )
 334   }} msg]
 335   lappend v $msg
 336 } {0 {}}
 337 do_test table-7.2 {
 338   execsql {
 339     INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
 340     SELECT * FROM weird;
 341   }
 342 } {a b 9 0 xyz hi y'all}
 343 do_test table-7.3 {
 344   execsql2 {
 345     SELECT * FROM weird;
 346   }
 347 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 348 do_test table-7.3 {
 349   execsql {
 350     CREATE TABLE savepoint(release);
 351     INSERT INTO savepoint(release) VALUES(10);
 352     UPDATE savepoint SET release = 5;
 353     SELECT release FROM savepoint;
 354   }
 355 } {5}
 356
 357 # Try out the CREATE TABLE AS syntax
 358 #
 359 do_test table-8.1 {
 360   execsql2 {
 361     CREATE TABLE t2 AS SELECT * FROM weird;
 362     SELECT * FROM t2;
 363   }
 364 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 365 do_test table-8.1.1 {
 366   execsql {
 367     SELECT sql FROM sqlite_master WHERE name='t2';
 368   }
 369 } {{CREATE TABLE t2(
 370   "desc" TEXT,
 371   "asc" TEXT,
 372   "key" INT,
 373   "14_vac" NUM,
 374   fuzzy_dog_12 TEXT,
 375   "begin",
 376   "end" TEXT
 377 )}}
 378 do_test table-8.2 {
 379   execsql {
 380     CREATE TABLE "t3""xyz"(a,b,c);
 381     INSERT INTO [t3"xyz] VALUES(1,2,3);
 382     SELECT * FROM [t3"xyz];
 383   }
 384 } {1 2 3}
 385 do_test table-8.3 {
 386   execsql2 {
 387     CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz];
 388     SELECT * FROM [t4"abc];
 389   }
 390 } {cnt 1 max(b+c) 5}
 391
 392 # Update for v3: The declaration type of anything except a column is now a
 393 # NULL pointer, so the created table has no column types. (Changed result
 394 # from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).
 395 do_test table-8.3.1 {
 396   execsql {
 397     SELECT sql FROM sqlite_master WHERE name='t4"abc'
 398   }
 399 } {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}
 400
 401 ifcapable tempdb {
 402   do_test table-8.4 {
 403     execsql2 {
 404       CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz];
 405       SELECT * FROM t5;
 406     }
 407   } {y'all 1}
 408 }
 409
 410 do_test table-8.5 {
 411   db close
 412   sqlite3 db test.db
 413   execsql2 {
 414     SELECT * FROM [t4"abc];
 415   }
 416 } {cnt 1 max(b+c) 5}
 417 do_test table-8.6 {
 418   execsql2 {
 419     SELECT * FROM t2;
 420   }
 421 } {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
 422 do_test table-8.7 {
 423   catchsql {
 424     SELECT * FROM t5;
 425   }
 426 } {1 {no such table: t5}}
 427 do_test table-8.8 {
 428   catchsql {
 429     CREATE TABLE t5 AS SELECT * FROM no_such_table;
 430   }
 431 } {1 {no such table: no_such_table}}
 432
 433 do_test table-8.9 {
 434   execsql {
 435     CREATE TABLE t10("col.1" [char.3]);
 436     CREATE TABLE t11 AS SELECT * FROM t10;
 437     SELECT sql FROM sqlite_master WHERE name = 't11';
 438   }
 439 } {{CREATE TABLE t11("col.1" TEXT)}}
 440 do_test table-8.10 {
 441   execsql {
 442     CREATE TABLE t12(
 443       a INTEGER,
 444       b VARCHAR(10),
 445       c VARCHAR(1,10),
 446       d VARCHAR(+1,-10),
 447       e VARCHAR (+1,-10),
 448       f "VARCHAR (+1,-10, 5)",
 449       g BIG INTEGER
 450     );
 451     CREATE TABLE t13 AS SELECT * FROM t12;
 452     SELECT sql FROM sqlite_master WHERE name = 't13';
 453   }
 454 } {{CREATE TABLE t13(
 455   a INT,
 456   b TEXT,
 457   c TEXT,
 458   d TEXT,
 459   e TEXT,
 460   f TEXT,
 461   g INT
 462 )}}
 463
 464 # Make sure we cannot have duplicate column names within a table.
 465 #
 466 do_test table-9.1 {
 467   catchsql {
 468     CREATE TABLE t6(a,b,a);
 469   }
 470 } {1 {duplicate column name: a}}
 471 do_test table-9.2 {
 472   catchsql {
 473     CREATE TABLE t6(a varchar(100), b blob, a integer);
 474   }
 475 } {1 {duplicate column name: a}}
 476
 477 # Check the foreign key syntax.
 478 #
 479 ifcapable {foreignkey} {
 480 do_test table-10.1 {
 481   catchsql {
 482     CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
 483     INSERT INTO t6 VALUES(NULL);
 484   }
 485 } {1 {NOT NULL constraint failed: t6.a}}
 486 do_test table-10.2 {
 487   catchsql {
 488     DROP TABLE t6;
 489     CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
 490   }
 491 } {0 {}}
 492 do_test table-10.3 {
 493   catchsql {
 494     DROP TABLE t6;
 495     CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);
 496   }
 497 } {0 {}}
 498 do_test table-10.4 {
 499   catchsql {
 500     DROP TABLE t6;
 501     CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);
 502   }
 503 } {0 {}}
 504 do_test table-10.5 {
 505   catchsql {
 506     DROP TABLE t6;
 507     CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
 508   }
 509 } {0 {}}
 510 do_test table-10.6 {
 511   catchsql {
 512     DROP TABLE t6;
 513     CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
 514   }
 515 } {0 {}}
 516 do_test table-10.7 {
 517   catchsql {
 518     DROP TABLE t6;
 519     CREATE TABLE t6(a,
 520       FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED
 521     );
 522   }
 523 } {0 {}}
 524 do_test table-10.8 {
 525   catchsql {
 526     DROP TABLE t6;
 527     CREATE TABLE t6(a,b,c,
 528       FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
 529         ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
 530     );
 531   }
 532 } {0 {}}
 533 do_test table-10.9 {
 534   catchsql {
 535     DROP TABLE t6;
 536     CREATE TABLE t6(a,b,c,
 537       FOREIGN KEY (b,c) REFERENCES t4(x)
 538     );
 539   }
 540 } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
 541 do_test table-10.10 {
 542   catchsql {DROP TABLE t6}
 543   catchsql {
 544     CREATE TABLE t6(a,b,c,
 545       FOREIGN KEY (b,c) REFERENCES t4(x,y,z)
 546     );
 547   }
 548 } {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
 549 do_test table-10.11 {
 550   catchsql {DROP TABLE t6}
 551   catchsql {
 552     CREATE TABLE t6(a,b, c REFERENCES t4(x,y));
 553   }
 554 } {1 {foreign key on c should reference only one column of table t4}}
 555 do_test table-10.12 {
 556   catchsql {DROP TABLE t6}
 557   catchsql {
 558     CREATE TABLE t6(a,b,c,
 559       FOREIGN KEY (b,x) REFERENCES t4(x,y)
 560     );
 561   }
 562 } {1 {unknown column "x" in foreign key definition}}
 563 do_test table-10.13 {
 564   catchsql {DROP TABLE t6}
 565   catchsql {
 566     CREATE TABLE t6(a,b,c,
 567       FOREIGN KEY (x,b) REFERENCES t4(x,y)
 568     );
 569   }
 570 } {1 {unknown column "x" in foreign key definition}}
 571 } ;# endif foreignkey
 572
 573 # Test for the "typeof" function. More tests for the
 574 # typeof() function are found in bind.test and types.test.
 575 #
 576 do_test table-11.1 {
 577   execsql {
 578     CREATE TABLE t7(
 579        a integer primary key,
 580        b number(5,10),
 581        c character varying (8),
 582        d VARCHAR(9),
 583        e clob,
 584        f BLOB,
 585        g Text,
 586        h
 587     );
 588     INSERT INTO t7(a) VALUES(1);
 589     SELECT typeof(a), typeof(b), typeof(c), typeof(d),
 590            typeof(e), typeof(f), typeof(g), typeof(h)
 591     FROM t7 LIMIT 1;
 592   }
 593 } {integer null null null null null null null}
 594 do_test table-11.2 {
 595   execsql {
 596     SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
 597     FROM t7 LIMIT 1;
 598   }
 599 } {null null null null}
 600
 601 # Test that when creating a table using CREATE TABLE AS, column types are
 602 # assigned correctly for (SELECT ...) and 'x AS y' expressions.
 603 do_test table-12.1 {
 604   ifcapable subquery {
 605     execsql {
 606       CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;
 607     }
 608   } else {
 609     execsql {
 610       CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7;
 611     }
 612   }
 613 } {}
 614 do_test table-12.2 {
 615   execsql {
 616     SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
 617   }
 618 } {{CREATE TABLE t8(b NUM,h,i INT,j)}}
 619
 620 #--------------------------------------------------------------------
 621 # Test cases table-13.*
 622 #
 623 # Test the ability to have default values of CURRENT_TIME, CURRENT_DATE
 624 # and CURRENT_TIMESTAMP.
 625 #
 626 do_test table-13.1 {
 627   execsql {
 628     CREATE TABLE tablet8(
 629        a integer primary key,
 630        tm text DEFAULT CURRENT_TIME,
 631        dt text DEFAULT CURRENT_DATE,
 632        dttm text DEFAULT CURRENT_TIMESTAMP
 633     );
 634     SELECT * FROM tablet8;
 635   }
 636 } {}
 637 set i 0
 638 unset -nocomplain date time seconds
 639 foreach {date time seconds} {
 640   1976-07-04 12:00:00 205329600
 641   1994-04-16 14:00:00 766504800
 642   2000-01-01 00:00:00 946684800
 643   2003-12-31 12:34:56 1072874096
 644 } {
 645   incr i
 646   set sqlite_current_time $seconds
 647   do_test table-13.2.$i {
 648     execsql "
 649       INSERT INTO tablet8(a) VALUES($i);
 650       SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;
 651     "
 652   } [list $time $date [list $date $time]]
 653 }
 654 set sqlite_current_time 0
 655
 656 #--------------------------------------------------------------------
 657 # Test cases table-14.*
 658 #
 659 # Test that a table cannot be created or dropped while other virtual
 660 # machines are active. This is required because otherwise when in
 661 # auto-vacuum mode the btree-layer may need to move the root-pages of
 662 # a table for which there is an open cursor.
 663 #
 664 # 2007-05-02:  A open btree cursor no longer blocks CREATE TABLE.
 665 # But DROP TABLE is still prohibited because we do not want to
 666 # delete a table out from under a running query.
 667 #
 668
 669 # db eval {
 670 #   pragma vdbe_trace = 0;
 671 # }
 672 # Try to create a table from within a callback:
 673 unset -nocomplain result
 674 do_test table-14.1 {
 675   set rc [
 676     catch {
 677       db eval {SELECT * FROM tablet8 LIMIT 1} {} {
 678         db eval {CREATE TABLE t9(a, b, c)}
 679       }
 680     } msg
 681   ]
 682   set result [list $rc $msg]
 683 } {0 {}}
 684
 685 # Try to drop a table from within a callback:
 686 do_test table-14.2 {
 687   set rc [
 688     catch {
 689       db eval {SELECT * FROM tablet8 LIMIT 1} {} {
 690         db eval {DROP TABLE t9;}
 691       }
 692     } msg
 693   ]
 694   set result [list $rc $msg]
 695 } {1 {database table is locked}}
 696
 697 ifcapable attach {
 698   # Now attach a database and ensure that a table can be created in the
 699   # attached database whilst in a callback from a query on the main database.
 700   do_test table-14.3 {
 701     forcedelete test2.db
 702     forcedelete test2.db-journal
 703     execsql {
 704       ATTACH 'test2.db' as aux;
 705     }
 706     db eval {SELECT * FROM tablet8 LIMIT 1} {} {
 707       db eval {CREATE TABLE aux.t1(a, b, c)}
 708     }
 709   } {}
 710
 711   # On the other hand, it should be impossible to drop a table when any VMs
 712   # are active. This is because VerifyCookie instructions may have already
 713   # been executed, and btree root-pages may not move after this (which a
 714   # delete table might do).
 715   do_test table-14.4 {
 716     set rc [
 717       catch {
 718         db eval {SELECT * FROM tablet8 LIMIT 1} {} {
 719           db eval {DROP TABLE aux.t1;}
 720         }
 721       } msg
 722     ]
 723     set result [list $rc $msg]
 724   } {1 {database table is locked}}
 725 }
 726
 727 # Create and drop 2000 tables. This is to check that the balance_shallow()
 728 # routine works correctly on the sqlite_master table. At one point it
 729 # contained a bug that would prevent the right-child pointer of the
 730 # child page from being copied to the root page.
 731 #
 732 do_test table-15.1 {
 733   execsql {BEGIN}
 734   for {set i 0} {$i<2000} {incr i} {
 735     execsql "CREATE TABLE tbl$i (a, b, c)"
 736   }
 737   execsql {COMMIT}
 738 } {}
 739 do_test table-15.2 {
 740   execsql {BEGIN}
 741   for {set i 0} {$i<2000} {incr i} {
 742     execsql "DROP TABLE tbl$i"
 743   }
 744   execsql {COMMIT}
 745 } {}
 746
 747 # Ticket 3a88d85f36704eebe134f7f48aebf00cd6438c1a (2014-08-05)
 748 # The following SQL script segfaults while running the INSERT statement:
 749 #
 750 #    CREATE TABLE t1(x DEFAULT(max(1)));
 751 #    INSERT INTO t1(rowid) VALUES(1);
 752 #
 753 # The problem appears to be the use of an aggregate function as part of
 754 # the default value for a column. This problem has been in the code since
 755 # at least 2006-01-01 and probably before that. This problem was detected
 756 # and reported on the sqlite-users@sqlite.org mailing list by Zsbán Ambrus.
 757 #
 758 do_execsql_test table-16.1 {
 759   CREATE TABLE t16(x DEFAULT(max(1)));
 760   INSERT INTO t16(x) VALUES(123);
 761   SELECT rowid, x FROM t16;
 762 } {1 123}
 763 do_catchsql_test table-16.2 {
 764   INSERT INTO t16(rowid) VALUES(4);
 765 } {1 {unknown function: max()}}
 766 do_execsql_test table-16.3 {
 767   DROP TABLE t16;
 768   CREATE TABLE t16(x DEFAULT(abs(1)));
 769   INSERT INTO t16(rowid) VALUES(4);
 770   SELECT rowid, x FROM t16;
 771 } {4 1}
 772 do_catchsql_test table-16.4 {
 773   DROP TABLE t16;
 774   CREATE TABLE t16(x DEFAULT(avg(1)));
 775   INSERT INTO t16(rowid) VALUES(123);
 776   SELECT rowid, x FROM t16;
 777 } {1 {unknown function: avg()}}
 778 do_catchsql_test table-16.5 {
 779   DROP TABLE t16;
 780   CREATE TABLE t16(x DEFAULT(count()));
 781   INSERT INTO t16(rowid) VALUES(123);
 782   SELECT rowid, x FROM t16;
 783 } {1 {unknown function: count()}}
 784 do_catchsql_test table-16.6 {
 785   DROP TABLE t16;
 786   CREATE TABLE t16(x DEFAULT(group_concat('x',',')));
 787   INSERT INTO t16(rowid) VALUES(123);
 788   SELECT rowid, x FROM t16;
 789 } {1 {unknown function: group_concat()}}
 790 do_catchsql_test table-16.7 {
 791   INSERT INTO t16 DEFAULT VALUES;
 792 } {1 {unknown function: group_concat()}}
 793
 794 # Ticket [https://www.sqlite.org/src/info/094d39a4c95ee4abbc417f04214617675ba15c63]
 795 # describes a assertion fault that occurs on a CREATE TABLE .. AS SELECT statement.
 796 # the following test verifies that the problem has been fixed.
 797 #
 798 do_execsql_test table-17.1 {
 799   DROP TABLE IF EXISTS t1;
 800   CREATE TABLE t1(a TEXT);
 801   INSERT INTO t1(a) VALUES(1),(2);
 802   DROP TABLE IF EXISTS t2;
 803   CREATE TABLE t2(x TEXT, y TEXT);
 804   INSERT INTO t2(x,y) VALUES(3,4);
 805   DROP TABLE IF EXISTS t3;
 806   CREATE TABLE t3 AS
 807     SELECT a AS p, coalesce(y,a) AS q FROM t1 LEFT JOIN t2 ON a=x;
 808   SELECT p, q, '|' FROM t3 ORDER BY p;
 809 } {1 1 | 2 2 |}
 810
 811 # 2015-06-16
 812 # Ticket [https://www.sqlite.org/src/tktview/873cae2b6e25b1991ce5e9b782f9cd0409b96063]
 813 # Make sure a CREATE TABLE AS statement correctly rolls back partial changes to the
 814 # sqlite_master table when the SELECT on the right-hand side aborts.
 815 #
 816 do_catchsql_test table-18.1 {
 817   DROP TABLE IF EXISTS t1;
 818   BEGIN;
 819   CREATE TABLE t1 AS SELECT zeroblob(2e20);
 820 } {1 {string or blob too big}}
 821 do_execsql_test table-18.2 {
 822   COMMIT;
 823   PRAGMA integrity_check;
 824 } {ok}
 825
 826 # 2015-09-09
 827 # Ticket [https://www.sqlite.org/src/info/acd12990885d9276]
 828 # "CREATE TABLE ... AS SELECT ... FROM sqlite_master" fails because the row
 829 # in the sqlite_master table for the next table is initially populated
 830 # with a NULL instead of a record created by OP_Record.
 831 #
 832 do_execsql_test table-19.1 {
 833   CREATE TABLE t19 AS SELECT * FROM sqlite_master;
 834   SELECT name FROM t19 ORDER BY name;
 835 } {{} savepoint t10 t11 t12 t13 t16 t2 t3 t3\"xyz t4\"abc t7 t8 t9 tablet8 test1 weird}
 836
 837
 838
 839 finish_test