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