execsql {    CREATE TABLE "t3""xyz"(a,b,c);    INSERT INTO [t3"xyz] VALUES(1,2,3);    SELECT * FROM [t3"xyz];  }} {1 2 3}do_test table-8.3 {  execsql2 {    CREATE TABLE [t4"abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3"xyz];    SELECT * FROM [t4"abc];  }} {cnt 1 max(b+c) 5}# Update for v3: The declaration type of anything except a column is now a# NULL pointer, so the created table has no column types. (Changed result# from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).do_test table-8.3.1 {  execsql {    SELECT sql FROM sqlite_master WHERE name='t4"abc'  }} {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}ifcapable tempdb {  do_test table-8.4 {    execsql2 {      CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz];      SELECT * FROM t5;    }  } {y'all 1}}do_test table-8.5 {  db close  sqlite3 db test.db  execsql2 {    SELECT * FROM [t4"abc];  }} {cnt 1 max(b+c) 5}do_test table-8.6 {  execsql2 {    SELECT * FROM t2;  }} {desc a asc b key 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}do_test table-8.7 {  catchsql {    SELECT * FROM t5;  }} {1 {no such table: t5}}do_test table-8.8 {  catchsql {    CREATE TABLE t5 AS SELECT * FROM no_such_table;  }} {1 {no such table: no_such_table}}# Make sure we cannot have duplicate column names within a table.#do_test table-9.1 {  catchsql {    CREATE TABLE t6(a,b,a);  }} {1 {duplicate column name: a}}do_test table-9.2 {  catchsql {    CREATE TABLE t6(a varchar(100), b blob, a integer);  }} {1 {duplicate column name: a}}# Check the foreign key syntax.#ifcapable {foreignkey} {do_test table-10.1 {  catchsql {    CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);    INSERT INTO t6 VALUES(NULL);  }} {1 {t6.a may not be NULL}}do_test table-10.2 {  catchsql {    DROP TABLE t6;    CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);  }} {0 {}}do_test table-10.3 {  catchsql {    DROP TABLE t6;    CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);  }} {0 {}}do_test table-10.4 {  catchsql {    DROP TABLE t6;    CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);  }} {0 {}}do_test table-10.5 {  catchsql {    DROP TABLE t6;    CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);  }} {0 {}}do_test table-10.6 {  catchsql {    DROP TABLE t6;    CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);  }} {0 {}}do_test table-10.7 {  catchsql {    DROP TABLE t6;    CREATE TABLE t6(a,      FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED    );  }} {0 {}}do_test table-10.8 {  catchsql {    DROP TABLE t6;    CREATE TABLE t6(a,b,c,      FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL        ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED    );  }} {0 {}}do_test table-10.9 {  catchsql {    DROP TABLE t6;    CREATE TABLE t6(a,b,c,      FOREIGN KEY (b,c) REFERENCES t4(x)    );  }} {1 {number of columns in foreign key does not match the number of columns in the referenced table}}do_test table-10.10 {  catchsql {DROP TABLE t6}  catchsql {    CREATE TABLE t6(a,b,c,      FOREIGN KEY (b,c) REFERENCES t4(x,y,z)    );  }} {1 {number of columns in foreign key does not match the number of columns in the referenced table}}do_test table-10.11 {  catchsql {DROP TABLE t6}  catchsql {    CREATE TABLE t6(a,b, c REFERENCES t4(x,y));  }} {1 {foreign key on c should reference only one column of table t4}}do_test table-10.12 {  catchsql {DROP TABLE t6}  catchsql {    CREATE TABLE t6(a,b,c,      FOREIGN KEY (b,x) REFERENCES t4(x,y)    );  }} {1 {unknown column "x" in foreign key definition}}do_test table-10.13 {  catchsql {DROP TABLE t6}  catchsql {    CREATE TABLE t6(a,b,c,      FOREIGN KEY (x,b) REFERENCES t4(x,y)    );  }} {1 {unknown column "x" in foreign key definition}}} ;# endif foreignkey# Test for the "typeof" function. More tests for the# typeof() function are found in bind.test and types.test.#do_test table-11.1 {  execsql {    CREATE TABLE t7(       a integer primary key,       b number(5,10),       c character varying (8),       d VARCHAR(9),       e clob,       f BLOB,       g Text,       h    );    INSERT INTO t7(a) VALUES(1);    SELECT typeof(a), typeof(b), typeof(c), typeof(d),           typeof(e), typeof(f), typeof(g), typeof(h)    FROM t7 LIMIT 1;  }} {integer null null null null null null null} do_test table-11.2 {  execsql {    SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)    FROM t7 LIMIT 1;  }} {null null null null}# Test that when creating a table using CREATE TABLE AS, column types are# assigned correctly for (SELECT ...) and 'x AS y' expressions.do_test table-12.1 {  ifcapable subquery {    execsql {      CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;    }  } else {    execsql {      CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7;    }  }} {}do_test table-12.2 {  execsql {    SELECT sql FROM sqlite_master WHERE tbl_name = 't8'  }} {{CREATE TABLE t8(b number(5,10),h,i integer,j BLOB)}}#--------------------------------------------------------------------# Test cases table-13.*## Test the ability to have default values of CURRENT_TIME, CURRENT_DATE# and CURRENT_TIMESTAMP.#do_test table-13.1 {  execsql {    CREATE TABLE tablet8(       a integer primary key,       tm text DEFAULT CURRENT_TIME,       dt text DEFAULT CURRENT_DATE,       dttm text DEFAULT CURRENT_TIMESTAMP    );    SELECT * FROM tablet8;  }} {}set i 0foreach {date time} {  1976-07-04 12:00:00  1994-04-16 14:00:00  2000-01-01 00:00:00  2003-12-31 12:34:56} {  incr i  set sqlite_current_time [clock scan "$date $time" -gmt 1]  # set sqlite_current_time [execsql "SELECT strftime('%s','$date $time')"]  do_test table-13.2.$i {    execsql "      INSERT INTO tablet8(a) VALUES($i);      SELECT tm, dt, dttm FROM tablet8 WHERE a=$i;    "  } [list $time $date [list $date $time]]}set sqlite_current_time 0#--------------------------------------------------------------------# Test cases table-14.*## Test that a table cannot be created or dropped while other virtual# machines are active. This is required because otherwise when in # auto-vacuum mode the btree-layer may need to move the root-pages of # a table for which there is an open cursor.## db eval {#   pragma vdbe_trace = 0;# }# Try to create a table from within a callback:unset -nocomplain resultdo_test table-14.1 {  set rc [    catch {      db eval {SELECT * FROM tablet8 LIMIT 1} {} {        db eval {CREATE TABLE t9(a, b, c)}      }    } msg  ]  set result [list $rc $msg]} {1 {database table is locked}}do_test table-14.2 {  execsql {    CREATE TABLE t9(a, b, c)  }} {}# Try to drop a table from within a callback:do_test table-14.3 {  set rc [    catch {      db eval {SELECT * FROM tablet8 LIMIT 1} {} {        db eval {DROP TABLE t9;}      }    } msg  ]   set result [list $rc $msg]} {1 {database table is locked}}# Now attach a database and ensure that a table can be created in the # attached database whilst in a callback from a query on the main database.do_test table-14.4 {  file delete -force test2.db  file delete -force test2.db-journal  execsql {    attach 'test2.db' as aux;  }  db eval {SELECT * FROM tablet8 LIMIT 1} {} {    db eval {CREATE TABLE aux.t1(a, b, c)}  }} {}# On the other hand, it should be impossible to drop a table when any VMs # are active. This is because VerifyCookie instructions may have already# been executed, and btree root-pages may not move after this (which a# delete table might do).do_test table-14.4 {  set rc [    catch {      db eval {SELECT * FROM tablet8 LIMIT 1} {} {        db eval {DROP TABLE aux.t1;}      }    } msg  ]   set result [list $rc $msg]} {1 {database table is locked}}# Create and drop 2000 tables. This is to check that the balance_shallow()# routine works correctly on the sqlite_master table. At one point it# contained a bug that would prevent the right-child pointer of the# child page from being copied to the root page.#do_test table-15.1 {  execsql {BEGIN}  for {set i 0} {$i<2000} {incr i} {    execsql "CREATE TABLE tbl$i (a, b, c)"  }  execsql {COMMIT}} {}do_test table-15.2 {  execsql {BEGIN}  for {set i 0} {$i<2000} {incr i} {    execsql "DROP TABLE tbl$i"  }  execsql {COMMIT}} {}finish_test