1*1da57d55SToomas Soome#
27c478bd9Sstevel@tonic-gate# 2001 September 15
37c478bd9Sstevel@tonic-gate#
47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code.  In place of
57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing:
67c478bd9Sstevel@tonic-gate#
77c478bd9Sstevel@tonic-gate#    May you do good and not evil.
87c478bd9Sstevel@tonic-gate#    May you find forgiveness for yourself and forgive others.
97c478bd9Sstevel@tonic-gate#    May you share freely, never taking more than you give.
107c478bd9Sstevel@tonic-gate#
117c478bd9Sstevel@tonic-gate#***********************************************************************
127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library.  The
137c478bd9Sstevel@tonic-gate# focus of this file is testing the CREATE TABLE statement.
147c478bd9Sstevel@tonic-gate#
157c478bd9Sstevel@tonic-gate# $Id: table.test,v 1.22 2003/01/29 18:46:54 drh Exp $
167c478bd9Sstevel@tonic-gate
177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0]
187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl
197c478bd9Sstevel@tonic-gate
207c478bd9Sstevel@tonic-gate# Create a basic table and verify it is added to sqlite_master
217c478bd9Sstevel@tonic-gate#
227c478bd9Sstevel@tonic-gatedo_test table-1.1 {
237c478bd9Sstevel@tonic-gate  execsql {
247c478bd9Sstevel@tonic-gate    CREATE TABLE test1 (
257c478bd9Sstevel@tonic-gate      one varchar(10),
267c478bd9Sstevel@tonic-gate      two text
277c478bd9Sstevel@tonic-gate    )
287c478bd9Sstevel@tonic-gate  }
297c478bd9Sstevel@tonic-gate  execsql {
307c478bd9Sstevel@tonic-gate    SELECT sql FROM sqlite_master WHERE type!='meta'
317c478bd9Sstevel@tonic-gate  }
327c478bd9Sstevel@tonic-gate} {{CREATE TABLE test1 (
337c478bd9Sstevel@tonic-gate      one varchar(10),
347c478bd9Sstevel@tonic-gate      two text
357c478bd9Sstevel@tonic-gate    )}}
367c478bd9Sstevel@tonic-gate
377c478bd9Sstevel@tonic-gate
387c478bd9Sstevel@tonic-gate# Verify the other fields of the sqlite_master file.
397c478bd9Sstevel@tonic-gate#
407c478bd9Sstevel@tonic-gatedo_test table-1.3 {
417c478bd9Sstevel@tonic-gate  execsql {SELECT name, tbl_name, type FROM sqlite_master WHERE type!='meta'}
427c478bd9Sstevel@tonic-gate} {test1 test1 table}
437c478bd9Sstevel@tonic-gate
447c478bd9Sstevel@tonic-gate# Close and reopen the database.  Verify that everything is
457c478bd9Sstevel@tonic-gate# still the same.
467c478bd9Sstevel@tonic-gate#
477c478bd9Sstevel@tonic-gatedo_test table-1.4 {
487c478bd9Sstevel@tonic-gate  db close
497c478bd9Sstevel@tonic-gate  sqlite db test.db
507c478bd9Sstevel@tonic-gate  execsql {SELECT name, tbl_name, type from sqlite_master WHERE type!='meta'}
517c478bd9Sstevel@tonic-gate} {test1 test1 table}
527c478bd9Sstevel@tonic-gate
537c478bd9Sstevel@tonic-gate# Drop the database and make sure it disappears.
547c478bd9Sstevel@tonic-gate#
557c478bd9Sstevel@tonic-gatedo_test table-1.5 {
567c478bd9Sstevel@tonic-gate  execsql {DROP TABLE test1}
577c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM sqlite_master WHERE type!='meta'}
587c478bd9Sstevel@tonic-gate} {}
597c478bd9Sstevel@tonic-gate
607c478bd9Sstevel@tonic-gate# Close and reopen the database.  Verify that the table is
617c478bd9Sstevel@tonic-gate# still gone.
627c478bd9Sstevel@tonic-gate#
637c478bd9Sstevel@tonic-gatedo_test table-1.6 {
647c478bd9Sstevel@tonic-gate  db close
657c478bd9Sstevel@tonic-gate  sqlite db test.db
667c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
677c478bd9Sstevel@tonic-gate} {}
687c478bd9Sstevel@tonic-gate
697c478bd9Sstevel@tonic-gate# Repeat the above steps, but this time quote the table name.
707c478bd9Sstevel@tonic-gate#
717c478bd9Sstevel@tonic-gatedo_test table-1.10 {
727c478bd9Sstevel@tonic-gate  execsql {CREATE TABLE "create" (f1 int)}
737c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
747c478bd9Sstevel@tonic-gate} {create}
757c478bd9Sstevel@tonic-gatedo_test table-1.11 {
767c478bd9Sstevel@tonic-gate  execsql {DROP TABLE "create"}
777c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
787c478bd9Sstevel@tonic-gate} {}
797c478bd9Sstevel@tonic-gatedo_test table-1.12 {
807c478bd9Sstevel@tonic-gate  execsql {CREATE TABLE test1("f1 ho" int)}
817c478bd9Sstevel@tonic-gate  execsql {SELECT name as "X" FROM sqlite_master WHERE type!='meta'}
827c478bd9Sstevel@tonic-gate} {test1}
837c478bd9Sstevel@tonic-gatedo_test table-1.13 {
847c478bd9Sstevel@tonic-gate  execsql {DROP TABLE "TEST1"}
857c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM "sqlite_master" WHERE type!='meta'}
867c478bd9Sstevel@tonic-gate} {}
877c478bd9Sstevel@tonic-gate
887c478bd9Sstevel@tonic-gate
897c478bd9Sstevel@tonic-gate
907c478bd9Sstevel@tonic-gate# Verify that we cannot make two tables with the same name
917c478bd9Sstevel@tonic-gate#
927c478bd9Sstevel@tonic-gatedo_test table-2.1 {
937c478bd9Sstevel@tonic-gate  execsql {CREATE TABLE TEST2(one text)}
947c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE test2(two text)}} msg]
957c478bd9Sstevel@tonic-gate  lappend v $msg
967c478bd9Sstevel@tonic-gate} {1 {table test2 already exists}}
977c478bd9Sstevel@tonic-gatedo_test table-2.1b {
987c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
997c478bd9Sstevel@tonic-gate  lappend v $msg
1007c478bd9Sstevel@tonic-gate} {1 {table sqlite_master already exists}}
1017c478bd9Sstevel@tonic-gatedo_test table-2.1c {
1027c478bd9Sstevel@tonic-gate  db close
1037c478bd9Sstevel@tonic-gate  sqlite db test.db
1047c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE sqlite_master(two text)}} msg]
1057c478bd9Sstevel@tonic-gate  lappend v $msg
1067c478bd9Sstevel@tonic-gate} {1 {table sqlite_master already exists}}
1077c478bd9Sstevel@tonic-gatedo_test table-2.1d {
1087c478bd9Sstevel@tonic-gate  execsql {DROP TABLE test2; SELECT name FROM sqlite_master WHERE type!='meta'}
1097c478bd9Sstevel@tonic-gate} {}
1107c478bd9Sstevel@tonic-gate
1117c478bd9Sstevel@tonic-gate# Verify that we cannot make a table with the same name as an index
1127c478bd9Sstevel@tonic-gate#
1137c478bd9Sstevel@tonic-gatedo_test table-2.2a {
1147c478bd9Sstevel@tonic-gate  execsql {CREATE TABLE test2(one text); CREATE INDEX test3 ON test2(one)}
1157c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
1167c478bd9Sstevel@tonic-gate  lappend v $msg
1177c478bd9Sstevel@tonic-gate} {1 {there is already an index named test3}}
1187c478bd9Sstevel@tonic-gatedo_test table-2.2b {
1197c478bd9Sstevel@tonic-gate  db close
1207c478bd9Sstevel@tonic-gate  sqlite db test.db
1217c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
1227c478bd9Sstevel@tonic-gate  lappend v $msg
1237c478bd9Sstevel@tonic-gate} {1 {there is already an index named test3}}
1247c478bd9Sstevel@tonic-gatedo_test table-2.2c {
1257c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1267c478bd9Sstevel@tonic-gate} {test2 test3}
1277c478bd9Sstevel@tonic-gatedo_test table-2.2d {
1287c478bd9Sstevel@tonic-gate  execsql {DROP INDEX test3}
1297c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE test3(two text)}} msg]
1307c478bd9Sstevel@tonic-gate  lappend v $msg
1317c478bd9Sstevel@tonic-gate} {0 {}}
1327c478bd9Sstevel@tonic-gatedo_test table-2.2e {
1337c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1347c478bd9Sstevel@tonic-gate} {test2 test3}
1357c478bd9Sstevel@tonic-gatedo_test table-2.2f {
1367c478bd9Sstevel@tonic-gate  execsql {DROP TABLE test2; DROP TABLE test3}
1377c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
1387c478bd9Sstevel@tonic-gate} {}
1397c478bd9Sstevel@tonic-gate
1407c478bd9Sstevel@tonic-gate# Create a table with many field names
1417c478bd9Sstevel@tonic-gate#
1427c478bd9Sstevel@tonic-gateset big_table \
1437c478bd9Sstevel@tonic-gate{CREATE TABLE big(
1447c478bd9Sstevel@tonic-gate  f1 varchar(20),
1457c478bd9Sstevel@tonic-gate  f2 char(10),
1467c478bd9Sstevel@tonic-gate  f3 varchar(30) primary key,
1477c478bd9Sstevel@tonic-gate  f4 text,
1487c478bd9Sstevel@tonic-gate  f5 text,
1497c478bd9Sstevel@tonic-gate  f6 text,
1507c478bd9Sstevel@tonic-gate  f7 text,
1517c478bd9Sstevel@tonic-gate  f8 text,
1527c478bd9Sstevel@tonic-gate  f9 text,
1537c478bd9Sstevel@tonic-gate  f10 text,
1547c478bd9Sstevel@tonic-gate  f11 text,
1557c478bd9Sstevel@tonic-gate  f12 text,
1567c478bd9Sstevel@tonic-gate  f13 text,
1577c478bd9Sstevel@tonic-gate  f14 text,
1587c478bd9Sstevel@tonic-gate  f15 text,
1597c478bd9Sstevel@tonic-gate  f16 text,
1607c478bd9Sstevel@tonic-gate  f17 text,
1617c478bd9Sstevel@tonic-gate  f18 text,
1627c478bd9Sstevel@tonic-gate  f19 text,
1637c478bd9Sstevel@tonic-gate  f20 text
1647c478bd9Sstevel@tonic-gate)}
1657c478bd9Sstevel@tonic-gatedo_test table-3.1 {
1667c478bd9Sstevel@tonic-gate  execsql $big_table
1677c478bd9Sstevel@tonic-gate  execsql {SELECT sql FROM sqlite_master WHERE type=='table'}
1687c478bd9Sstevel@tonic-gate} \{$big_table\}
1697c478bd9Sstevel@tonic-gatedo_test table-3.2 {
1707c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE BIG(xyz foo)}} msg]
1717c478bd9Sstevel@tonic-gate  lappend v $msg
1727c478bd9Sstevel@tonic-gate} {1 {table BIG already exists}}
1737c478bd9Sstevel@tonic-gatedo_test table-3.3 {
1747c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE biG(xyz foo)}} msg]
1757c478bd9Sstevel@tonic-gate  lappend v $msg
1767c478bd9Sstevel@tonic-gate} {1 {table biG already exists}}
1777c478bd9Sstevel@tonic-gatedo_test table-3.4 {
1787c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE bIg(xyz foo)}} msg]
1797c478bd9Sstevel@tonic-gate  lappend v $msg
1807c478bd9Sstevel@tonic-gate} {1 {table bIg already exists}}
1817c478bd9Sstevel@tonic-gatedo_test table-3.5 {
1827c478bd9Sstevel@tonic-gate  db close
1837c478bd9Sstevel@tonic-gate  sqlite db test.db
1847c478bd9Sstevel@tonic-gate  set v [catch {execsql {CREATE TABLE Big(xyz foo)}} msg]
1857c478bd9Sstevel@tonic-gate  lappend v $msg
1867c478bd9Sstevel@tonic-gate} {1 {table Big already exists}}
1877c478bd9Sstevel@tonic-gatedo_test table-3.6 {
1887c478bd9Sstevel@tonic-gate  execsql {DROP TABLE big}
1897c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
1907c478bd9Sstevel@tonic-gate} {}
1917c478bd9Sstevel@tonic-gate
1927c478bd9Sstevel@tonic-gate# Try creating large numbers of tables
1937c478bd9Sstevel@tonic-gate#
1947c478bd9Sstevel@tonic-gateset r {}
1957c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=100} {incr i} {
1967c478bd9Sstevel@tonic-gate  lappend r [format test%03d $i]
1977c478bd9Sstevel@tonic-gate}
1987c478bd9Sstevel@tonic-gatedo_test table-4.1 {
1997c478bd9Sstevel@tonic-gate  for {set i 1} {$i<=100} {incr i} {
2007c478bd9Sstevel@tonic-gate    set sql "CREATE TABLE [format test%03d $i] ("
2017c478bd9Sstevel@tonic-gate    for {set k 1} {$k<$i} {incr k} {
2027c478bd9Sstevel@tonic-gate      append sql "field$k text,"
2037c478bd9Sstevel@tonic-gate    }
2047c478bd9Sstevel@tonic-gate    append sql "last_field text)"
2057c478bd9Sstevel@tonic-gate    execsql $sql
2067c478bd9Sstevel@tonic-gate  }
2077c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2087c478bd9Sstevel@tonic-gate} $r
2097c478bd9Sstevel@tonic-gatedo_test table-4.1b {
2107c478bd9Sstevel@tonic-gate  db close
2117c478bd9Sstevel@tonic-gate  sqlite db test.db
2127c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2137c478bd9Sstevel@tonic-gate} $r
2147c478bd9Sstevel@tonic-gate
2157c478bd9Sstevel@tonic-gate# Drop the even numbered tables
2167c478bd9Sstevel@tonic-gate#
2177c478bd9Sstevel@tonic-gateset r {}
2187c478bd9Sstevel@tonic-gatefor {set i 1} {$i<=100} {incr i 2} {
2197c478bd9Sstevel@tonic-gate  lappend r [format test%03d $i]
2207c478bd9Sstevel@tonic-gate}
2217c478bd9Sstevel@tonic-gatedo_test table-4.2 {
2227c478bd9Sstevel@tonic-gate  for {set i 2} {$i<=100} {incr i 2} {
2237c478bd9Sstevel@tonic-gate    # if {$i==38} {execsql {pragma vdbe_trace=on}}
2247c478bd9Sstevel@tonic-gate    set sql "DROP TABLE [format TEST%03d $i]"
2257c478bd9Sstevel@tonic-gate    execsql $sql
2267c478bd9Sstevel@tonic-gate  }
2277c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2287c478bd9Sstevel@tonic-gate} $r
2297c478bd9Sstevel@tonic-gate#exit
2307c478bd9Sstevel@tonic-gate
2317c478bd9Sstevel@tonic-gate# Drop the odd number tables
2327c478bd9Sstevel@tonic-gate#
2337c478bd9Sstevel@tonic-gatedo_test table-4.3 {
2347c478bd9Sstevel@tonic-gate  for {set i 1} {$i<=100} {incr i 2} {
2357c478bd9Sstevel@tonic-gate    set sql "DROP TABLE [format test%03d $i]"
2367c478bd9Sstevel@tonic-gate    execsql $sql
2377c478bd9Sstevel@tonic-gate  }
2387c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
2397c478bd9Sstevel@tonic-gate} {}
2407c478bd9Sstevel@tonic-gate
2417c478bd9Sstevel@tonic-gate# Try to drop a table that does not exist
2427c478bd9Sstevel@tonic-gate#
2437c478bd9Sstevel@tonic-gatedo_test table-5.1 {
2447c478bd9Sstevel@tonic-gate  set v [catch {execsql {DROP TABLE test009}} msg]
2457c478bd9Sstevel@tonic-gate  lappend v $msg
2467c478bd9Sstevel@tonic-gate} {1 {no such table: test009}}
2477c478bd9Sstevel@tonic-gate
2487c478bd9Sstevel@tonic-gate# Try to drop sqlite_master
2497c478bd9Sstevel@tonic-gate#
2507c478bd9Sstevel@tonic-gatedo_test table-5.2 {
2517c478bd9Sstevel@tonic-gate  set v [catch {execsql {DROP TABLE sqlite_master}} msg]
2527c478bd9Sstevel@tonic-gate  lappend v $msg
2537c478bd9Sstevel@tonic-gate} {1 {table sqlite_master may not be dropped}}
2547c478bd9Sstevel@tonic-gate
2557c478bd9Sstevel@tonic-gate# Make sure an EXPLAIN does not really create a new table
2567c478bd9Sstevel@tonic-gate#
2577c478bd9Sstevel@tonic-gatedo_test table-5.3 {
2587c478bd9Sstevel@tonic-gate  execsql {EXPLAIN CREATE TABLE test1(f1 int)}
2597c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2607c478bd9Sstevel@tonic-gate} {}
2617c478bd9Sstevel@tonic-gate
2627c478bd9Sstevel@tonic-gate# Make sure an EXPLAIN does not really drop an existing table
2637c478bd9Sstevel@tonic-gate#
2647c478bd9Sstevel@tonic-gatedo_test table-5.4 {
2657c478bd9Sstevel@tonic-gate  execsql {CREATE TABLE test1(f1 int)}
2667c478bd9Sstevel@tonic-gate  execsql {EXPLAIN DROP TABLE test1}
2677c478bd9Sstevel@tonic-gate  execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
2687c478bd9Sstevel@tonic-gate} {test1}
2697c478bd9Sstevel@tonic-gate
2707c478bd9Sstevel@tonic-gate# Create a table with a goofy name
2717c478bd9Sstevel@tonic-gate#
2727c478bd9Sstevel@tonic-gate#do_test table-6.1 {
2737c478bd9Sstevel@tonic-gate#  execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
2747c478bd9Sstevel@tonic-gate#  execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
2757c478bd9Sstevel@tonic-gate#  set list [glob -nocomplain testdb/spaces*.tbl]
2767c478bd9Sstevel@tonic-gate#} {testdb/spaces+in+this+name+.tbl}
2777c478bd9Sstevel@tonic-gate
2787c478bd9Sstevel@tonic-gate# Try using keywords as table names or column names.
279*1da57d55SToomas Soome#
2807c478bd9Sstevel@tonic-gatedo_test table-7.1 {
2817c478bd9Sstevel@tonic-gate  set v [catch {execsql {
2827c478bd9Sstevel@tonic-gate    CREATE TABLE weird(
2837c478bd9Sstevel@tonic-gate      desc text,
2847c478bd9Sstevel@tonic-gate      asc text,
2857c478bd9Sstevel@tonic-gate      explain int,
2867c478bd9Sstevel@tonic-gate      [14_vac] boolean,
2877c478bd9Sstevel@tonic-gate      fuzzy_dog_12 varchar(10),
2887c478bd9Sstevel@tonic-gate      begin blob,
2897c478bd9Sstevel@tonic-gate      end clob
2907c478bd9Sstevel@tonic-gate    )
2917c478bd9Sstevel@tonic-gate  }} msg]
2927c478bd9Sstevel@tonic-gate  lappend v $msg
2937c478bd9Sstevel@tonic-gate} {0 {}}
2947c478bd9Sstevel@tonic-gatedo_test table-7.2 {
2957c478bd9Sstevel@tonic-gate  execsql {
2967c478bd9Sstevel@tonic-gate    INSERT INTO weird VALUES('a','b',9,0,'xyz','hi','y''all');
2977c478bd9Sstevel@tonic-gate    SELECT * FROM weird;
2987c478bd9Sstevel@tonic-gate  }
2997c478bd9Sstevel@tonic-gate} {a b 9 0 xyz hi y'all}
3007c478bd9Sstevel@tonic-gatedo_test table-7.3 {
3017c478bd9Sstevel@tonic-gate  execsql2 {
3027c478bd9Sstevel@tonic-gate    SELECT * FROM weird;
3037c478bd9Sstevel@tonic-gate  }
3047c478bd9Sstevel@tonic-gate} {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
3057c478bd9Sstevel@tonic-gate
3067c478bd9Sstevel@tonic-gate# Try out the CREATE TABLE AS syntax
3077c478bd9Sstevel@tonic-gate#
3087c478bd9Sstevel@tonic-gatedo_test table-8.1 {
3097c478bd9Sstevel@tonic-gate  execsql2 {
3107c478bd9Sstevel@tonic-gate    CREATE TABLE t2 AS SELECT * FROM weird;
3117c478bd9Sstevel@tonic-gate    SELECT * FROM t2;
3127c478bd9Sstevel@tonic-gate  }
3137c478bd9Sstevel@tonic-gate} {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
3147c478bd9Sstevel@tonic-gatedo_test table-8.1.1 {
3157c478bd9Sstevel@tonic-gate  execsql {
3167c478bd9Sstevel@tonic-gate    SELECT sql FROM sqlite_master WHERE name='t2';
3177c478bd9Sstevel@tonic-gate  }
3187c478bd9Sstevel@tonic-gate} {{CREATE TABLE t2(
3197c478bd9Sstevel@tonic-gate  'desc',
3207c478bd9Sstevel@tonic-gate  'asc',
3217c478bd9Sstevel@tonic-gate  'explain',
3227c478bd9Sstevel@tonic-gate  '14_vac',
3237c478bd9Sstevel@tonic-gate  fuzzy_dog_12,
3247c478bd9Sstevel@tonic-gate  'begin',
3257c478bd9Sstevel@tonic-gate  'end'
3267c478bd9Sstevel@tonic-gate)}}
3277c478bd9Sstevel@tonic-gatedo_test table-8.2 {
3287c478bd9Sstevel@tonic-gate  execsql {
3297c478bd9Sstevel@tonic-gate    CREATE TABLE 't3''xyz'(a,b,c);
3307c478bd9Sstevel@tonic-gate    INSERT INTO [t3'xyz] VALUES(1,2,3);
3317c478bd9Sstevel@tonic-gate    SELECT * FROM [t3'xyz];
3327c478bd9Sstevel@tonic-gate  }
3337c478bd9Sstevel@tonic-gate} {1 2 3}
3347c478bd9Sstevel@tonic-gatedo_test table-8.3 {
3357c478bd9Sstevel@tonic-gate  execsql2 {
3367c478bd9Sstevel@tonic-gate    CREATE TABLE [t4'abc] AS SELECT count(*) as cnt, max(b+c) FROM [t3'xyz];
3377c478bd9Sstevel@tonic-gate    SELECT * FROM [t4'abc];
3387c478bd9Sstevel@tonic-gate  }
3397c478bd9Sstevel@tonic-gate} {cnt 1 max(b+c) 5}
3407c478bd9Sstevel@tonic-gatedo_test table-8.3.1 {
3417c478bd9Sstevel@tonic-gate  execsql {
3427c478bd9Sstevel@tonic-gate    SELECT sql FROM sqlite_master WHERE name='t4''abc'
3437c478bd9Sstevel@tonic-gate  }
3447c478bd9Sstevel@tonic-gate} {{CREATE TABLE 't4''abc'(cnt,'max(b+c)')}}
3457c478bd9Sstevel@tonic-gatedo_test table-8.4 {
3467c478bd9Sstevel@tonic-gate  execsql2 {
3477c478bd9Sstevel@tonic-gate    CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3'xyz];
3487c478bd9Sstevel@tonic-gate    SELECT * FROM t5;
3497c478bd9Sstevel@tonic-gate  }
3507c478bd9Sstevel@tonic-gate} {y'all 1}
3517c478bd9Sstevel@tonic-gatedo_test table-8.5 {
3527c478bd9Sstevel@tonic-gate  db close
3537c478bd9Sstevel@tonic-gate  sqlite db test.db
3547c478bd9Sstevel@tonic-gate  execsql2 {
3557c478bd9Sstevel@tonic-gate    SELECT * FROM [t4'abc];
3567c478bd9Sstevel@tonic-gate  }
3577c478bd9Sstevel@tonic-gate} {cnt 1 max(b+c) 5}
3587c478bd9Sstevel@tonic-gatedo_test table-8.6 {
3597c478bd9Sstevel@tonic-gate  execsql2 {
3607c478bd9Sstevel@tonic-gate    SELECT * FROM t2;
3617c478bd9Sstevel@tonic-gate  }
3627c478bd9Sstevel@tonic-gate} {desc a asc b explain 9 14_vac 0 fuzzy_dog_12 xyz begin hi end y'all}
3637c478bd9Sstevel@tonic-gatedo_test table-8.7 {
3647c478bd9Sstevel@tonic-gate  catchsql {
3657c478bd9Sstevel@tonic-gate    SELECT * FROM t5;
3667c478bd9Sstevel@tonic-gate  }
3677c478bd9Sstevel@tonic-gate} {1 {no such table: t5}}
3687c478bd9Sstevel@tonic-gatedo_test table-8.8 {
3697c478bd9Sstevel@tonic-gate  catchsql {
3707c478bd9Sstevel@tonic-gate    CREATE TABLE t5 AS SELECT * FROM no_such_table;
3717c478bd9Sstevel@tonic-gate  }
3727c478bd9Sstevel@tonic-gate} {1 {no such table: no_such_table}}
3737c478bd9Sstevel@tonic-gate
3747c478bd9Sstevel@tonic-gate# Make sure we cannot have duplicate column names within a table.
3757c478bd9Sstevel@tonic-gate#
3767c478bd9Sstevel@tonic-gatedo_test table-9.1 {
3777c478bd9Sstevel@tonic-gate  catchsql {
3787c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a,b,a);
3797c478bd9Sstevel@tonic-gate  }
3807c478bd9Sstevel@tonic-gate} {1 {duplicate column name: a}}
3817c478bd9Sstevel@tonic-gate
3827c478bd9Sstevel@tonic-gate# Check the foreign key syntax.
3837c478bd9Sstevel@tonic-gate#
3847c478bd9Sstevel@tonic-gatedo_test table-10.1 {
3857c478bd9Sstevel@tonic-gate  catchsql {
3867c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a REFERENCES t4(a) NOT NULL);
3877c478bd9Sstevel@tonic-gate    INSERT INTO t6 VALUES(NULL);
3887c478bd9Sstevel@tonic-gate  }
3897c478bd9Sstevel@tonic-gate} {1 {t6.a may not be NULL}}
3907c478bd9Sstevel@tonic-gatedo_test table-10.2 {
3917c478bd9Sstevel@tonic-gate  catchsql {
3927c478bd9Sstevel@tonic-gate    DROP TABLE t6;
3937c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a REFERENCES t4(a) MATCH PARTIAL);
3947c478bd9Sstevel@tonic-gate  }
3957c478bd9Sstevel@tonic-gate} {0 {}}
3967c478bd9Sstevel@tonic-gatedo_test table-10.3 {
3977c478bd9Sstevel@tonic-gate  catchsql {
3987c478bd9Sstevel@tonic-gate    DROP TABLE t6;
3997c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON DELETE SET NULL NOT NULL);
4007c478bd9Sstevel@tonic-gate  }
4017c478bd9Sstevel@tonic-gate} {0 {}}
4027c478bd9Sstevel@tonic-gatedo_test table-10.4 {
4037c478bd9Sstevel@tonic-gate  catchsql {
4047c478bd9Sstevel@tonic-gate    DROP TABLE t6;
4057c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a REFERENCES t4 MATCH FULL ON UPDATE SET DEFAULT DEFAULT 1);
4067c478bd9Sstevel@tonic-gate  }
4077c478bd9Sstevel@tonic-gate} {0 {}}
4087c478bd9Sstevel@tonic-gatedo_test table-10.5 {
4097c478bd9Sstevel@tonic-gate  catchsql {
4107c478bd9Sstevel@tonic-gate    DROP TABLE t6;
4117c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a NOT NULL NOT DEFERRABLE INITIALLY IMMEDIATE);
4127c478bd9Sstevel@tonic-gate  }
4137c478bd9Sstevel@tonic-gate} {0 {}}
4147c478bd9Sstevel@tonic-gatedo_test table-10.6 {
4157c478bd9Sstevel@tonic-gate  catchsql {
4167c478bd9Sstevel@tonic-gate    DROP TABLE t6;
4177c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a NOT NULL DEFERRABLE INITIALLY DEFERRED);
4187c478bd9Sstevel@tonic-gate  }
4197c478bd9Sstevel@tonic-gate} {0 {}}
4207c478bd9Sstevel@tonic-gatedo_test table-10.7 {
4217c478bd9Sstevel@tonic-gate  catchsql {
4227c478bd9Sstevel@tonic-gate    DROP TABLE t6;
4237c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a,
4247c478bd9Sstevel@tonic-gate      FOREIGN KEY (a) REFERENCES t4(b) DEFERRABLE INITIALLY DEFERRED
4257c478bd9Sstevel@tonic-gate    );
4267c478bd9Sstevel@tonic-gate  }
4277c478bd9Sstevel@tonic-gate} {0 {}}
4287c478bd9Sstevel@tonic-gatedo_test table-10.8 {
4297c478bd9Sstevel@tonic-gate  catchsql {
4307c478bd9Sstevel@tonic-gate    DROP TABLE t6;
4317c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a,b,c,
4327c478bd9Sstevel@tonic-gate      FOREIGN KEY (b,c) REFERENCES t4(x,y) MATCH PARTIAL
4337c478bd9Sstevel@tonic-gate        ON UPDATE SET NULL ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
4347c478bd9Sstevel@tonic-gate    );
4357c478bd9Sstevel@tonic-gate  }
4367c478bd9Sstevel@tonic-gate} {0 {}}
4377c478bd9Sstevel@tonic-gatedo_test table-10.9 {
4387c478bd9Sstevel@tonic-gate  catchsql {
4397c478bd9Sstevel@tonic-gate    DROP TABLE t6;
4407c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a,b,c,
4417c478bd9Sstevel@tonic-gate      FOREIGN KEY (b,c) REFERENCES t4(x)
4427c478bd9Sstevel@tonic-gate    );
4437c478bd9Sstevel@tonic-gate  }
4447c478bd9Sstevel@tonic-gate} {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
4457c478bd9Sstevel@tonic-gatedo_test table-10.10 {
4467c478bd9Sstevel@tonic-gate  catchsql {DROP TABLE t6}
4477c478bd9Sstevel@tonic-gate  catchsql {
4487c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a,b,c,
4497c478bd9Sstevel@tonic-gate      FOREIGN KEY (b,c) REFERENCES t4(x,y,z)
4507c478bd9Sstevel@tonic-gate    );
4517c478bd9Sstevel@tonic-gate  }
4527c478bd9Sstevel@tonic-gate} {1 {number of columns in foreign key does not match the number of columns in the referenced table}}
4537c478bd9Sstevel@tonic-gatedo_test table-10.11 {
4547c478bd9Sstevel@tonic-gate  catchsql {DROP TABLE t6}
4557c478bd9Sstevel@tonic-gate  catchsql {
4567c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a,b, c REFERENCES t4(x,y));
4577c478bd9Sstevel@tonic-gate  }
4587c478bd9Sstevel@tonic-gate} {1 {foreign key on c should reference only one column of table t4}}
4597c478bd9Sstevel@tonic-gatedo_test table-10.12 {
4607c478bd9Sstevel@tonic-gate  catchsql {DROP TABLE t6}
4617c478bd9Sstevel@tonic-gate  catchsql {
4627c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a,b,c,
4637c478bd9Sstevel@tonic-gate      FOREIGN KEY (b,x) REFERENCES t4(x,y)
4647c478bd9Sstevel@tonic-gate    );
4657c478bd9Sstevel@tonic-gate  }
4667c478bd9Sstevel@tonic-gate} {1 {unknown column "x" in foreign key definition}}
4677c478bd9Sstevel@tonic-gatedo_test table-10.13 {
4687c478bd9Sstevel@tonic-gate  catchsql {DROP TABLE t6}
4697c478bd9Sstevel@tonic-gate  catchsql {
4707c478bd9Sstevel@tonic-gate    CREATE TABLE t6(a,b,c,
4717c478bd9Sstevel@tonic-gate      FOREIGN KEY (x,b) REFERENCES t4(x,y)
4727c478bd9Sstevel@tonic-gate    );
4737c478bd9Sstevel@tonic-gate  }
4747c478bd9Sstevel@tonic-gate} {1 {unknown column "x" in foreign key definition}}
4757c478bd9Sstevel@tonic-gate
4767c478bd9Sstevel@tonic-gate
4777c478bd9Sstevel@tonic-gate# Test for the "typeof" function.
4787c478bd9Sstevel@tonic-gate#
4797c478bd9Sstevel@tonic-gatedo_test table-11.1 {
4807c478bd9Sstevel@tonic-gate  execsql {
4817c478bd9Sstevel@tonic-gate    CREATE TABLE t7(
4827c478bd9Sstevel@tonic-gate       a integer primary key,
4837c478bd9Sstevel@tonic-gate       b number(5,10),
4847c478bd9Sstevel@tonic-gate       c character varying (8),
4857c478bd9Sstevel@tonic-gate       d VARCHAR(9),
4867c478bd9Sstevel@tonic-gate       e clob,
4877c478bd9Sstevel@tonic-gate       f BLOB,
4887c478bd9Sstevel@tonic-gate       g Text,
4897c478bd9Sstevel@tonic-gate       h
4907c478bd9Sstevel@tonic-gate    );
4917c478bd9Sstevel@tonic-gate    INSERT INTO t7(a) VALUES(1);
4927c478bd9Sstevel@tonic-gate    SELECT typeof(a), typeof(b), typeof(c), typeof(d),
4937c478bd9Sstevel@tonic-gate           typeof(e), typeof(f), typeof(g), typeof(h)
4947c478bd9Sstevel@tonic-gate    FROM t7 LIMIT 1;
4957c478bd9Sstevel@tonic-gate  }
4967c478bd9Sstevel@tonic-gate} {numeric numeric text text text text text numeric}
4977c478bd9Sstevel@tonic-gatedo_test table-11.2 {
4987c478bd9Sstevel@tonic-gate  execsql {
4997c478bd9Sstevel@tonic-gate    SELECT typeof(a+b), typeof(a||b), typeof(c+d), typeof(c||d)
5007c478bd9Sstevel@tonic-gate    FROM t7 LIMIT 1;
5017c478bd9Sstevel@tonic-gate  }
5027c478bd9Sstevel@tonic-gate} {numeric text numeric text}
5037c478bd9Sstevel@tonic-gate
5047c478bd9Sstevel@tonic-gatefinish_test
505