Import SQLite 3.7.6.3.

third_party/sqlite/src/test/e_createtable.test

+# 2010 September 25
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# This file implements tests to verify that the "testable statements" in 
+# the lang_createtable.html document are correct.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+set ::testprefix e_createtable
+
+# Test organization:
+#
+#   e_createtable-0.*: Test that the syntax diagrams are correct.
+#
+#   e_createtable-1.*: Test statements related to table and database names, 
+#       the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
+#
+#   e_createtable-2.*: Test "CREATE TABLE AS" statements.
+#
+
+proc do_createtable_tests {nm args} {
+  uplevel do_select_tests [list e_createtable-$nm] $args
+}
+
+
+#-------------------------------------------------------------------------
+# This command returns a serialized tcl array mapping from the name of
+# each attached database to a list of tables in that database. For example,
+# if the database schema is created with:
+#
+#   CREATE TABLE t1(x);
+#   CREATE TEMP TABLE t2(x);
+#   CREATE TEMP TABLE t3(x);
+#
+# Then this command returns "main t1 temp {t2 t3}".
+#
+proc table_list {} {
+  set res [list]
+  db eval { pragma database_list } a {
+    set dbname $a(name)
+    set master $a(name).sqlite_master
+    if {$dbname == "temp"} { set master sqlite_temp_master }
+    lappend res $dbname [
+      db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
+    ]
+  }
+  set res
+}
+
+
+# EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name
+#
+do_createtable_tests 0.1.1 -repair {
+  drop_all_tables
+} {
+  1   "CREATE TABLE t1(c1 one)"                        {}
+  2   "CREATE TABLE t1(c1 one two)"                    {}
+  3   "CREATE TABLE t1(c1 one two three)"              {}
+  4   "CREATE TABLE t1(c1 one two three four)"         {}
+  5   "CREATE TABLE t1(c1 one two three four(14))"     {}
+  6   "CREATE TABLE t1(c1 one two three four(14, 22))" {}
+  7   "CREATE TABLE t1(c1 var(+14, -22.3))"            {}
+  8   "CREATE TABLE t1(c1 var(1.0e10))"                {}
+}
+do_createtable_tests 0.1.2 -error {
+  near "%s": syntax error
+} {
+  1   "CREATE TABLE t1(c1 one(number))"                {number}
+}
+
+
+# EVIDENCE-OF: R-18762-12428 -- syntax diagram column-constraint
+#
+#   Note: Not shown in the syntax diagram is the "NULL" constraint. This
+#         is the opposite of "NOT NULL" - it implies that the column may
+#         take a NULL value. This is the default anyway, so this type of
+#         constraint is rarely used.
+#
+do_createtable_tests 0.2.1 -repair {
+  drop_all_tables 
+  execsql { CREATE TABLE t2(x PRIMARY KEY) }
+} {
+  1.1   "CREATE TABLE t1(c1 text PRIMARY KEY)"                         {}
+  1.2   "CREATE TABLE t1(c1 text PRIMARY KEY ASC)"                     {}
+  1.3   "CREATE TABLE t1(c1 text PRIMARY KEY DESC)"                    {}
+  1.4   "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)"    {}
+
+  2.1   "CREATE TABLE t1(c1 text NOT NULL)"                            {}
+  2.2   "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)"              {}
+  2.3   "CREATE TABLE t1(c1 text NULL)"                                {}
+  2.4   "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)"                  {}
+
+  3.1   "CREATE TABLE t1(c1 text UNIQUE)"                              {}
+  3.2   "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)"                {}
+
+  4.1   "CREATE TABLE t1(c1 text CHECK(c1!=0))"                        {}
+  4.2   "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))"         {}
+
+  5.1   "CREATE TABLE t1(c1 text DEFAULT 1)"                           {}
+  5.2   "CREATE TABLE t1(c1 text DEFAULT -1)"                          {}
+  5.3   "CREATE TABLE t1(c1 text DEFAULT +1)"                          {}
+  5.4   "CREATE TABLE t1(c1 text DEFAULT -45.8e22)"                    {}
+  5.5   "CREATE TABLE t1(c1 text DEFAULT (1+1))"                       {}
+  5.6   "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))"    {}
+
+  6.1   "CREATE TABLE t1(c1 text COLLATE nocase)"        {}
+  6.2   "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)"     {}
+
+  7.1   "CREATE TABLE t1(c1 REFERENCES t2)"                            {}
+  7.2   "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)"             {}
+
+  8.1   {
+    CREATE TABLE t1(c1 
+      PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1
+    );
+  } {}
+  8.2   {
+    CREATE TABLE t1(c1 
+      REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY 
+    );
+  } {}
+}
+
+# EVIDENCE-OF: R-17905-31923 -- syntax diagram table-constraint
+#
+do_createtable_tests 0.3.1 -repair {
+  drop_all_tables 
+  execsql { CREATE TABLE t2(x PRIMARY KEY) }
+} {
+  1.1   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))"                         {}
+  1.2   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))"                     {}
+  1.3   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)"  {}
+
+  2.1   "CREATE TABLE t1(c1, c2, UNIQUE(c1))"                              {}
+  2.2   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))"                          {}
+  2.3   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)"       {}
+
+  3.1   "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))"                     {}
+
+  4.1   "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)"           {}
+}
+
+# EVIDENCE-OF: R-18765-31171 -- syntax diagram column-def
+#
+do_createtable_tests 0.4.1 -repair {
+  drop_all_tables 
+} {
+  1     {CREATE TABLE t1(
+           col1,
+           col2 TEXT,
+           col3 INTEGER UNIQUE,
+           col4 VARCHAR(10, 10) PRIMARY KEY,
+           "name with spaces" REFERENCES t1
+         );
+        } {}
+}
+
+# EVIDENCE-OF: R-59573-11075 -- syntax diagram create-table-stmt
+#
+do_createtable_tests 0.5.1 -repair {
+  drop_all_tables 
+  execsql { CREATE TABLE t2(a, b, c) }
+} {
+  1     "CREATE TABLE t1(a, b, c)"                                    {}
+  2     "CREATE TEMP TABLE t1(a, b, c)"                               {}
+  3     "CREATE TEMPORARY TABLE t1(a, b, c)"                          {}
+  4     "CREATE TABLE IF NOT EXISTS t1(a, b, c)"                      {}
+  5     "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)"                 {}
+  6     "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)"            {}
+
+  7     "CREATE TABLE main.t1(a, b, c)"                               {}
+  8     "CREATE TEMP TABLE temp.t1(a, b, c)"                          {}
+  9     "CREATE TEMPORARY TABLE temp.t1(a, b, c)"                     {}
+  10    "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)"                 {}
+  11    "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)"            {}
+  12    "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)"       {}
+
+  13    "CREATE TABLE t1 AS SELECT * FROM t2"                         {}
+  14    "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2"              {}
+  15    "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2"  {}
+}
+
+# EVIDENCE-OF: R-32138-02228 -- syntax diagram foreign-key-clause
+#
+#   1:         Explicit parent-key columns.
+#   2:         Implicit child-key columns.
+#
+#   1:         MATCH FULL
+#   2:         MATCH PARTIAL
+#   3:         MATCH SIMPLE
+#   4:         MATCH STICK
+#   5:         
+#
+#   1:         ON DELETE SET NULL
+#   2:         ON DELETE SET DEFAULT
+#   3:         ON DELETE CASCADE
+#   4:         ON DELETE RESTRICT
+#   5:         ON DELETE NO ACTION
+#   6:
+#
+#   1:         ON UPDATE SET NULL
+#   2:         ON UPDATE SET DEFAULT
+#   3:         ON UPDATE CASCADE
+#   4:         ON UPDATE RESTRICT
+#   5:         ON UPDATE NO ACTION
+#   6:
+#
+#   1:         NOT DEFERRABLE INITIALLY DEFERRED
+#   2:         NOT DEFERRABLE INITIALLY IMMEDIATE
+#   3:         NOT DEFERRABLE
+#   4:         DEFERRABLE INITIALLY DEFERRED
+#   5:         DEFERRABLE INITIALLY IMMEDIATE
+#   6:         DEFERRABLE
+#   7:         
+#
+do_createtable_tests 0.6.1 -repair {
+  drop_all_tables 
+  execsql { CREATE TABLE t2(x PRIMARY KEY, y) }
+  execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }
+} {
+  11146 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH FULL 
+    ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE
+  )} {}
+  11412 { CREATE TABLE t1(a 
+    REFERENCES t2(x) 
+    ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL 
+    NOT DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  12135 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH PARTIAL 
+    ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  12427 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH PARTIAL 
+    ON DELETE RESTRICT ON UPDATE SET DEFAULT 
+  )} {}
+  12446 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH PARTIAL 
+    ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE
+  )} {}
+  12522 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH PARTIAL 
+    ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  13133 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH SIMPLE 
+    ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE
+  )} {}
+  13216 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH SIMPLE 
+    ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE
+  )} {}
+  13263 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH SIMPLE 
+    ON DELETE SET DEFAULT  NOT DEFERRABLE
+  )} {}
+  13421 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH SIMPLE 
+    ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED
+  )} {}
+  13432 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH SIMPLE 
+    ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  13523 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH SIMPLE 
+    ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE
+  )} {}
+  14336 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH STICK 
+    ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE
+  )} {}
+  14611 { CREATE TABLE t1(a 
+    REFERENCES t2(x) MATCH STICK 
+    ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED
+  )} {}
+  15155 { CREATE TABLE t1(a 
+    REFERENCES t2(x)
+    ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  15453 { CREATE TABLE t1(a 
+    REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE
+  )} {}
+  15661 { CREATE TABLE t1(a 
+    REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED
+  )} {}
+  21115 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH FULL 
+    ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  21123 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH FULL 
+    ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE
+  )} {}
+  21217 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL 
+  )} {}
+  21362 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH FULL 
+    ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  22143 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH PARTIAL 
+    ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE
+  )} {}
+  22156 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH PARTIAL 
+    ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE
+  )} {}
+  22327 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT 
+  )} {}
+  22663 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE
+  )} {}
+  23236 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH SIMPLE 
+    ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE
+  )} {}
+  24155 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH STICK 
+    ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  24522 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH STICK 
+    ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  24625 { CREATE TABLE t1(a 
+    REFERENCES t2 MATCH STICK 
+    ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE
+  )} {}
+  25454 { CREATE TABLE t1(a 
+    REFERENCES t2 
+    ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED
+  )} {}
+}
+
+#-------------------------------------------------------------------------
+# Test cases e_createtable-1.* - test statements related to table and
+# database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS
+# clause.
+#
+drop_all_tables
+forcedelete test.db2 test.db3
+
+do_execsql_test e_createtable-1.0 {
+  ATTACH 'test.db2' AS auxa;
+  ATTACH 'test.db3' AS auxb;
+} {}
+
+# EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are
+# reserved for internal use. It is an error to attempt to create a table
+# with a name that starts with "sqlite_".
+#
+do_createtable_tests 1.1.1 -error {
+  object name reserved for internal use: %s
+} {
+  1    "CREATE TABLE sqlite_abc(a, b, c)"        sqlite_abc
+  2    "CREATE TABLE temp.sqlite_helloworld(x)"  sqlite_helloworld
+  3    {CREATE TABLE auxa."sqlite__"(x, y)}      sqlite__
+  4    {CREATE TABLE auxb."sqlite_"(z)}          sqlite_
+  5    {CREATE TABLE "SQLITE_TBL"(z)}            SQLITE_TBL
+}
+do_createtable_tests 1.1.2 {
+  1    "CREATE TABLE sqlit_abc(a, b, c)"         {}
+  2    "CREATE TABLE temp.sqlitehelloworld(x)"   {}
+  3    {CREATE TABLE auxa."sqlite"(x, y)}        {}
+  4    {CREATE TABLE auxb."sqlite-"(z)}          {}
+  5    {CREATE TABLE "SQLITE-TBL"(z)}            {}
+}
+
+
+# EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it
+# must be either "main", "temp", or the name of an attached database.
+#
+# EVIDENCE-OF: R-39822-07822 In this case the new table is created in
+# the named database.
+#
+#   Test cases 1.2.* test the first of the two requirements above. The
+#   second is verified by cases 1.3.*.
+#
+do_createtable_tests 1.2.1 -error {
+  unknown database %s
+} {
+  1    "CREATE TABLE george.t1(a, b)"            george
+  2    "CREATE TABLE _.t1(a, b)"                 _
+}
+do_createtable_tests 1.2.2 {
+  1    "CREATE TABLE main.abc(a, b, c)"          {}
+  2    "CREATE TABLE temp.helloworld(x)"         {}
+  3    {CREATE TABLE auxa."t 1"(x, y)}           {}
+  4    {CREATE TABLE auxb.xyz(z)}                {}
+}
+drop_all_tables
+do_createtable_tests 1.3 -tclquery {
+  unset -nocomplain X
+  array set X [table_list]
+  list $X(main) $X(temp) $X(auxa) $X(auxb)
+} {
+  1    "CREATE TABLE main.abc(a, b, c)"  {abc {} {} {}}
+  2    "CREATE TABLE main.t1(a, b, c)"   {{abc t1} {} {} {}}
+  3    "CREATE TABLE temp.tmp(a, b, c)"  {{abc t1} tmp {} {}}
+  4    "CREATE TABLE auxb.tbl(x, y)"     {{abc t1} tmp {} tbl}
+  5    "CREATE TABLE auxb.t1(k, v)"      {{abc t1} tmp {} {t1 tbl}}
+  6    "CREATE TABLE auxa.next(c, d)"    {{abc t1} tmp next {t1 tbl}}
+}
+
+# EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs
+# between the "CREATE" and "TABLE" then the new table is created in the
+# temp database.
+#
+drop_all_tables
+do_createtable_tests 1.4 -tclquery {
+  unset -nocomplain X
+  array set X [table_list]
+  list $X(main) $X(temp) $X(auxa) $X(auxb)
+} {
+  1    "CREATE TEMP TABLE t1(a, b)"      {{} t1 {} {}}
+  2    "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}}
+}
+
+# EVIDENCE-OF: R-49439-47561 It is an error to specify both a
+# <database-name> and the TEMP or TEMPORARY keyword, unless the
+# <database-name> is "temp".
+#
+drop_all_tables
+do_createtable_tests 1.5.1 -error {
+  temporary table name must be unqualified
+} {
+  1    "CREATE TEMP TABLE main.t1(a, b)"        {}
+  2    "CREATE TEMPORARY TABLE auxa.t2(a, b)"   {}
+  3    "CREATE TEMP TABLE auxb.t3(a, b)"        {}
+  4    "CREATE TEMPORARY TABLE main.xxx(x)"     {}
+}
+drop_all_tables
+do_createtable_tests 1.5.2 -tclquery {
+  unset -nocomplain X
+  array set X [table_list]
+  list $X(main) $X(temp) $X(auxa) $X(auxb)
+} {
+  1    "CREATE TEMP TABLE temp.t1(a, b)"        {{} t1 {} {}}
+  2    "CREATE TEMPORARY TABLE temp.t2(a, b)"   {{} {t1 t2} {} {}}
+  3    "CREATE TEMP TABLE TEMP.t3(a, b)"        {{} {t1 t2 t3} {} {}}
+  4    "CREATE TEMPORARY TABLE TEMP.xxx(x)"     {{} {t1 t2 t3 xxx} {} {}}
+}
+
+# EVIDENCE-OF: R-00917-09393 If no database name is specified and the
+# TEMP keyword is not present then the table is created in the main
+# database.
+#
+drop_all_tables
+do_createtable_tests 1.6 -tclquery {
+  unset -nocomplain X
+  array set X [table_list]
+  list $X(main) $X(temp) $X(auxa) $X(auxb)
+} {
+  1    "CREATE TABLE t1(a, b)"   {t1 {} {} {}}
+  2    "CREATE TABLE t2(a, b)"   {{t1 t2} {} {} {}}
+  3    "CREATE TABLE t3(a, b)"   {{t1 t2 t3} {} {} {}}
+  4    "CREATE TABLE xxx(x)"     {{t1 t2 t3 xxx} {} {} {}}
+}
+
+drop_all_tables
+do_execsql_test e_createtable-1.7.0 {
+  CREATE TABLE t1(x, y);
+  CREATE INDEX i1 ON t1(x);
+  CREATE VIEW  v1 AS SELECT * FROM t1;
+
+  CREATE TABLE auxa.tbl1(x, y);
+  CREATE INDEX auxa.idx1 ON tbl1(x);
+  CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
+} {}
+
+# EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create
+# a new table in a database that already contains a table, index or view
+# of the same name.
+#
+#   Test cases 1.7.1.* verify that creating a table in a database with a
+#   table/index/view of the same name does fail. 1.7.2.* tests that creating
+#   a table with the same name as a table/index/view in a different database
+#   is Ok.
+#
+do_createtable_tests 1.7.1 -error { %s } {
+  1    "CREATE TABLE t1(a, b)"   {{table t1 already exists}}
+  2    "CREATE TABLE i1(a, b)"   {{there is already an index named i1}}
+  3    "CREATE TABLE v1(a, b)"   {{table v1 already exists}}
+  4    "CREATE TABLE auxa.tbl1(a, b)"   {{table tbl1 already exists}}
+  5    "CREATE TABLE auxa.idx1(a, b)"   {{there is already an index named idx1}}
+  6    "CREATE TABLE auxa.view1(a, b)"  {{table view1 already exists}}
+}
+do_createtable_tests 1.7.2 {
+  1    "CREATE TABLE auxa.t1(a, b)"   {}
+  2    "CREATE TABLE auxa.i1(a, b)"   {}
+  3    "CREATE TABLE auxa.v1(a, b)"   {}
+  4    "CREATE TABLE tbl1(a, b)"      {}
+  5    "CREATE TABLE idx1(a, b)"      {}
+  6    "CREATE TABLE view1(a, b)"     {}
+}
+
+# EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is
+# specified as part of the CREATE TABLE statement and a table or view of
+# the same name already exists, the CREATE TABLE command simply has no
+# effect (and no error message is returned).
+#
+drop_all_tables
+do_execsql_test e_createtable-1.8.0 {
+  CREATE TABLE t1(x, y);
+  CREATE INDEX i1 ON t1(x);
+  CREATE VIEW  v1 AS SELECT * FROM t1;
+  CREATE TABLE auxa.tbl1(x, y);
+  CREATE INDEX auxa.idx1 ON tbl1(x);
+  CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
+} {}
+do_createtable_tests 1.8 {
+  1    "CREATE TABLE IF NOT EXISTS t1(a, b)"          {}
+  2    "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)"   {}
+  3    "CREATE TABLE IF NOT EXISTS v1(a, b)"          {}
+  4    "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)"  {}
+}
+
+# EVIDENCE-OF: R-16465-40078 An error is still returned if the table
+# cannot be created because of an existing index, even if the "IF NOT
+# EXISTS" clause is specified.
+#
+do_createtable_tests 1.9 -error { %s } {
+  1    "CREATE TABLE IF NOT EXISTS i1(a, b)"   
+       {{there is already an index named i1}}
+  2    "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)"   
+       {{there is already an index named idx1}}
+}
+
+# EVIDENCE-OF: R-05513-33819 It is not an error to create a table that
+# has the same name as an existing trigger.
+#
+drop_all_tables
+do_execsql_test e_createtable-1.10.0 {
+  CREATE TABLE t1(x, y);
+  CREATE TABLE auxb.t2(x, y);
+
+  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
+    SELECT 1;
+  END;
+  CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN
+    SELECT 1;
+  END;
+} {}
+do_createtable_tests 1.10 {
+  1    "CREATE TABLE tr1(a, b)"          {}
+  2    "CREATE TABLE tr2(a, b)"          {}
+  3    "CREATE TABLE auxb.tr1(a, b)"     {}
+  4    "CREATE TABLE auxb.tr2(a, b)"     {}
+}
+
+# EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE
+# statement.
+#
+drop_all_tables
+do_execsql_test e_createtable-1.11.0 {
+  CREATE TABLE t1(a, b);
+  CREATE TABLE t2(a, b);
+  CREATE TABLE auxa.t3(a, b);
+  CREATE TABLE auxa.t4(a, b);
+} {}
+
+do_execsql_test e_createtable-1.11.1.1 {
+  SELECT * FROM t1;
+  SELECT * FROM t2;
+  SELECT * FROM t3;
+  SELECT * FROM t4;
+} {}
+do_execsql_test  e_createtable-1.11.1.2 { DROP TABLE t1 } {}
+do_catchsql_test e_createtable-1.11.1.3 { 
+  SELECT * FROM t1 
+} {1 {no such table: t1}}
+do_execsql_test  e_createtable-1.11.1.4 { DROP TABLE t3 } {}
+do_catchsql_test e_createtable-1.11.1.5 { 
+  SELECT * FROM t3 
+} {1 {no such table: t3}}
+
+do_execsql_test e_createtable-1.11.2.1 {
+  SELECT name FROM sqlite_master;
+  SELECT name FROM auxa.sqlite_master;
+} {t2 t4}
+do_execsql_test  e_createtable-1.11.2.2 { DROP TABLE t2 } {}
+do_execsql_test  e_createtable-1.11.2.3 { DROP TABLE t4 } {}
+do_execsql_test e_createtable-1.11.2.4 {
+  SELECT name FROM sqlite_master;
+  SELECT name FROM auxa.sqlite_master;
+} {}
+
+#-------------------------------------------------------------------------
+# Test cases e_createtable-2.* - test statements related to the CREATE
+# TABLE AS ... SELECT statement.
+#
+
+# Three Tcl commands:
+#
+#   select_column_names SQL
+#     The argument must be a SELECT statement. Return a list of the names
+#     of the columns of the result-set that would be returned by executing
+#     the SELECT.
+#
+#   table_column_names TBL
+#     The argument must be a table name. Return a list of column names, from
+#     left to right, for the table.
+#
+#   table_column_decltypes TBL
+#     The argument must be a table name. Return a list of column declared
+#     types, from left to right, for the table.
+#
+proc sci {select cmd} {
+  set res [list]
+  set STMT [sqlite3_prepare_v2 db $select -1 dummy]
+  for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
+    lappend res [$cmd $STMT $i]
+  }
+  sqlite3_finalize $STMT
+  set res
+}
+proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
+proc select_column_names    {sql} { sci $sql sqlite3_column_name }
+proc table_column_names     {tbl} { tci $tbl sqlite3_column_name }
+proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }
+
+# Create a database schema. This schema is used by tests 2.1.* through 2.3.*.
+#
+drop_all_tables
+do_execsql_test e_createtable-2.0 {
+  CREATE TABLE t1(a, b, c);
+  CREATE TABLE t2(d, e, f);
+  CREATE TABLE t3(g BIGINT, h VARCHAR(10));
+  CREATE TABLE t4(i BLOB, j ANYOLDATA);
+  CREATE TABLE t5(k FLOAT, l INTEGER);
+  CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));
+  CREATE TABLE t7(x INTEGER PRIMARY KEY);
+  CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');
+  CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);
+} {}
+
+# EVIDENCE-OF: R-64828-59568 The table has the same number of columns as
+# the rows returned by the SELECT statement. The name of each column is
+# the same as the name of the corresponding column in the result set of
+# the SELECT statement.
+#
+do_createtable_tests 2.1 -tclquery {
+  table_column_names x1
+} -repair {
+  catchsql { DROP TABLE x1 }
+} {
+  1    "CREATE TABLE x1 AS SELECT * FROM t1"                     {a b c}
+  2    "CREATE TABLE x1 AS SELECT c, b, a FROM t1"               {c b a}
+  3    "CREATE TABLE x1 AS SELECT * FROM t1, t2"                 {a b c d e f}
+  4    "CREATE TABLE x1 AS SELECT count(*) FROM t1"              {count(*)}
+  5    "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}
+}
+
+# EVIDENCE-OF: R-37111-22855 The declared type of each column is
+# determined by the expression affinity of the corresponding expression
+# in the result set of the SELECT statement, as follows: Expression
+# Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"
+# REAL "REAL" NONE "" (empty string)
+#
+do_createtable_tests 2.2 -tclquery {
+  table_column_decltypes x1
+} -repair {
+  catchsql { DROP TABLE x1 }
+} {
+  1    "CREATE TABLE x1 AS SELECT a FROM t1"     {""}
+  2    "CREATE TABLE x1 AS SELECT * FROM t3"     {INT TEXT}
+  3    "CREATE TABLE x1 AS SELECT * FROM t4"     {"" NUM}
+  4    "CREATE TABLE x1 AS SELECT * FROM t5"     {REAL INT}
+}
+
+# EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has
+# no PRIMARY KEY and no constraints of any kind. The default value of
+# each column is NULL. The default collation sequence for each column of
+# the new table is BINARY.
+#
+#   The following tests create tables based on SELECT statements that read
+#   from tables that have primary keys, constraints and explicit default 
+#   collation sequences. None of this is transfered to the definition of
+#   the new table as stored in the sqlite_master table.
+#
+#   Tests 2.3.2.* show that the default value of each column is NULL.
+#
+do_createtable_tests 2.3.1 -query {
+  SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1
+} {
+  1    "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}
+  2    "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}
+  3    "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}
+  4    "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}
+}
+do_execsql_test e_createtable-2.3.2.1 {
+  INSERT INTO x1 DEFAULT VALUES;
+  INSERT INTO x2 DEFAULT VALUES;
+  INSERT INTO x3 DEFAULT VALUES;
+  INSERT INTO x4 DEFAULT VALUES;
+} {}
+db nullvalue null
+do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}
+do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}
+do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}
+do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}
+db nullvalue {}
+
+drop_all_tables
+do_execsql_test e_createtable-2.4.0 {
+  CREATE TABLE t1(x, y);
+  INSERT INTO t1 VALUES('i',   'one');
+  INSERT INTO t1 VALUES('ii',  'two');
+  INSERT INTO t1 VALUES('iii', 'three');
+} {}
+
+# EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are
+# initially populated with the rows of data returned by the SELECT
+# statement.
+#
+# EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending
+# rowid values, starting with 1, in the order that they are returned by
+# the SELECT statement.
+#
+#   Each test case below is specified as the name of a table to create
+#   using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in
+#   creating it. The table is created. 
+#
+#   Test cases 2.4.*.1 check that after it has been created, the data in the
+#   table is the same as the data returned by the SELECT statement executed as
+#   a standalone command, verifying the first testable statement above.
+#
+#   Test cases 2.4.*.2 check that the rowids were allocated contiguously
+#   as required by the second testable statement above. That the rowids
+#   from the contiguous block were allocated to rows in the order rows are
+#   returned by the SELECT statement is verified by 2.4.*.1.
+#
+# EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement
+# creates and populates a database table based on the results of a
+# SELECT statement.
+#
+#   The above is also considered to be tested by the following. It is
+#   clear that tables are being created and populated by the command in
+#   question.
+#
+foreach {tn tbl select} {
+  1   x1   "SELECT * FROM t1"
+  2   x2   "SELECT * FROM t1 ORDER BY x DESC"
+  3   x3   "SELECT * FROM t1 ORDER BY x ASC"
+} {
+  # Create the table using a "CREATE TABLE ... AS SELECT ..." command.
+  execsql [subst {CREATE TABLE $tbl AS $select}]
+
+  # Check that the rows inserted into the table, sorted in ascending rowid
+  # order, match those returned by executing the SELECT statement as a
+  # standalone command.
+  do_execsql_test e_createtable-2.4.$tn.1 [subst {
+    SELECT * FROM $tbl ORDER BY rowid;
+  }] [execsql $select]
+
+  # Check that the rowids in the new table are a contiguous block starting
+  # with rowid 1. Note that this will fail if SELECT statement $select 
+  # returns 0 rows (as max(rowid) will be NULL).
+  do_execsql_test e_createtable-2.4.$tn.2 [subst {
+    SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl
+  }] {1 1}
+}
+
+#--------------------------------------------------------------------------
+# Test cases for column defintions in CREATE TABLE statements that do not
+# use a SELECT statement. Not including data constraints. In other words,
+# tests for the specification of:
+#
+#   * declared types,
+#   * default values, and
+#   * default collation sequences.
+#
+
+# EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not
+# restrict the type of data that may be inserted into a column based on
+# the columns declared type.
+#
+#   Test this by creating a few tables with varied declared types, then
+#   inserting various different types of values into them.
+#
+drop_all_tables
+do_execsql_test e_createtable-3.1.0 {
+  CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);
+  CREATE TABLE t2(a DATETIME, b STRING, c REAL);
+  CREATE TABLE t3(o, t);
+} {}
+
+# value type -> declared column type
+# ----------------------------------
+# integer    -> VARCHAR(10)
+# string     -> INTEGER
+# blob       -> DOUBLE
+#
+do_execsql_test e_createtable-3.1.1 {
+  INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');
+  SELECT * FROM t1;
+} {14 {quite a lengthy string} UVU}
+
+# string     -> DATETIME
+# integer    -> STRING
+# time       -> REAL
+#
+do_execsql_test e_createtable-3.1.2 {
+  INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');
+  SELECT * FROM t2;
+} {{not a datetime} 13 12:41:59}
+
+# EVIDENCE-OF: R-10565-09557 The declared type of a column is used to
+# determine the affinity of the column only.
+#
+#     Affinities are tested in more detail elsewhere (see document
+#     datatype3.html). Here, just test that affinity transformations
+#     consistent with the expected affinity of each column (based on
+#     the declared type) appear to take place.
+#
+# Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL
+# Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL
+# Affinities of t3 (test cases 3.2.3.*): NONE, NONE
+#
+do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {}
+
+do_createtable_tests 3.2.1 -query {
+  SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1;
+} {
+  1   "INSERT INTO t1 VALUES(15,   '22.0', '14')"   {'15' 22 14.0}
+  2   "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)"     {'22.0' 22 22.0}
+}
+do_createtable_tests 3.2.2 -query {
+  SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1;
+} {
+  1   "INSERT INTO t2 VALUES(15,   '22.0', '14')"   {15   22  14.0}
+  2   "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)"     {22   22  22.0}
+}
+do_createtable_tests 3.2.3 -query {
+  SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1;
+} {
+  1   "INSERT INTO t3 VALUES('15', '22.0')"         {'15' '22.0'}
+  2   "INSERT INTO t3 VALUES(15, 22.0)"             {15 22.0}
+}
+
+# EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause
+# attached to a column definition, then the default value of the column
+# is NULL.
+#
+#     None of the columns in table t1 have an explicit DEFAULT clause.
+#     So testing that the default value of all columns in table t1 is
+#     NULL serves to verify the above.
+#     
+do_createtable_tests 3.2.3 -query {
+  SELECT quote(x), quote(y), quote(z) FROM t1
+} -repair {
+  execsql { DELETE FROM t1 }
+} {
+  1   "INSERT INTO t1(x, y) VALUES('abc', 'xyz')"   {'abc' 'xyz' NULL}
+  2   "INSERT INTO t1(x, z) VALUES('abc', 'xyz')"   {'abc' NULL 'xyz'}
+  3   "INSERT INTO t1 DEFAULT VALUES"               {NULL NULL NULL}
+}
+
+# EVIDENCE-OF: R-62940-43005 An explicit DEFAULT clause may specify that
+# the default value is NULL, a string constant, a blob constant, a
+# signed-number, or any constant expression enclosed in parentheses. An
+# explicit default value may also be one of the special case-independent
+# keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
+#
+do_execsql_test e_createtable-3.3.1 {
+  CREATE TABLE t4(
+    a DEFAULT NULL,
+    b DEFAULT 'string constant',
+    c DEFAULT X'424C4F42',
+    d DEFAULT 1,
+    e DEFAULT -1,
+    f DEFAULT 3.14,
+    g DEFAULT -3.14,
+    h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),
+    i DEFAULT CURRENT_TIME,
+    j DEFAULT CURRENT_DATE,
+    k DEFAULT CURRENT_TIMESTAMP
+  );
+} {}
+
+# EVIDENCE-OF: R-10288-43169 For the purposes of the DEFAULT clause, an
+# expression is considered constant provided that it does not contain
+# any sub-queries or string constants enclosed in double quotes.
+#
+do_createtable_tests 3.4.1 -error {
+  default value of column [x] is not constant
+} {
+  1   {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))}  {}
+  2   {CREATE TABLE t5(x DEFAULT ( "abc" ))}  {}
+  3   {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))}  {}
+  4   {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))}  {}
+}
+do_createtable_tests 3.4.2 -repair {
+  catchsql { DROP TABLE t5 }
+} {
+  1   {CREATE TABLE t5(x DEFAULT ( 'abc' ))}  {}
+  2   {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))}  {}
+}
+
+# EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table
+# by an INSERT statement that does not provide explicit values for all
+# table columns the values stored in the new row are determined by their
+# default values
+#
+#     Verify this with some assert statements for which all, some and no
+#     columns lack explicit values.
+#
+set sqlite_current_time 1000000000
+do_createtable_tests 3.5 -query {
+  SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), 
+         quote(g), quote(h), quote(i), quote(j), quote(k)
+  FROM t4 ORDER BY rowid DESC LIMIT 1;
+} {
+  1 "INSERT INTO t4 DEFAULT VALUES" {
+    NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 
+    'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
+  }
+
+  2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {
+    1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
+  }
+
+  3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {
+    NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1
+  }
+
+  4 "INSERT INTO t4(a,b,c,d,e,f,g,h,i,j,k) VALUES(1,2,3,4,5,6,7,8,9,10,11)" {
+    1 2 3 4 5 6 7 8 9 10 11
+  }
+}
+
+# EVIDENCE-OF: R-12572-62501 If the default value of the column is a
+# constant NULL, text, blob or signed-number value, then that value is
+# used directly in the new row.
+#
+do_execsql_test e_createtable-3.6.1 {
+  CREATE TABLE t5(
+    a DEFAULT NULL,  
+    b DEFAULT 'text value',  
+    c DEFAULT X'424C4F42',
+    d DEFAULT -45678.6,
+    e DEFAULT 394507
+  );
+} {}
+do_execsql_test e_createtable-3.6.2 {
+  INSERT INTO t5 DEFAULT VALUES;
+  SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
+} {NULL {'text value'} X'424C4F42' -45678.6 394507}
+
+# EVIDENCE-OF: R-60616-50251 If the default value of a column is an
+# expression in parentheses, then the expression is evaluated once for
+# each row inserted and the results used in the new row.
+#
+#   Test case 3.6.4 demonstrates that the expression is evaluated 
+#   separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
+#   command.
+#
+set ::nextint 0
+proc nextint {} { incr ::nextint }
+db func nextint nextint
+
+do_execsql_test e_createtable-3.7.1 {
+  CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));
+} {}
+do_execsql_test e_createtable-3.7.2 {
+  INSERT INTO t6 DEFAULT VALUES;
+  SELECT quote(a), quote(b) FROM t6;
+} {1 2}
+do_execsql_test e_createtable-3.7.3 {
+  INSERT INTO t6(a) VALUES('X');
+  SELECT quote(a), quote(b) FROM t6;
+} {1 2 'X' 3}
+do_execsql_test e_createtable-3.7.4 {
+  INSERT INTO t6(a) SELECT a FROM t6;
+  SELECT quote(a), quote(b) FROM t6;
+} {1 2 'X' 3 1 4 'X' 5}
+
+# EVIDENCE-OF: R-18683-56219 If the default value of a column is
+# CURRENT_TIME, CURRENT_DATE or CURRENT_DATETIME, then the value used in
+# the new row is a text representation of the current UTC date and/or
+# time.
+#
+#     This is difficult to test literally without knowing what time the 
+#     user will run the tests. Instead, we test that the three cases
+#     above set the value to the current date and/or time according to
+#     the xCurrentTime() method of the VFS. Which is usually the same
+#     as UTC. In this case, however, we instrument it to always return
+#     a time equivalent to "2001-09-09 01:46:40 UTC".
+#
+set sqlite_current_time 1000000000
+do_execsql_test e_createtable-3.8.1 {
+  CREATE TABLE t7(
+    a DEFAULT CURRENT_TIME, 
+    b DEFAULT CURRENT_DATE, 
+    c DEFAULT CURRENT_TIMESTAMP
+  );
+} {}
+do_execsql_test e_createtable-3.8.2 {
+  INSERT INTO t7 DEFAULT VALUES;
+  SELECT quote(a), quote(b), quote(c) FROM t7;
+} {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}}
+
+
+# EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value
+# is "HH:MM:SS".
+#
+# EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".
+#
+# EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is
+# "YYYY-MM-DD HH:MM:SS".
+#
+#     The three above are demonstrated by tests 1, 2 and 3 below. 
+#     Respectively.
+#
+do_createtable_tests 3.8.3 -query {
+  SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1;
+} {
+  1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y}
+  2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x}
+  3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}}
+}
+
+# EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a
+# collating sequence to use as the default collation sequence for the
+# column.
+#
+# EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the
+# default collation sequence is BINARY.
+#
+do_execsql_test e_createtable-3-9.1 {
+  CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);
+  INSERT INTO t8 VALUES('abc',   'abc',   'abc',   'abc');
+  INSERT INTO t8 VALUES('abc  ', 'abc  ', 'abc  ', 'abc  ');
+  INSERT INTO t8 VALUES('ABC  ', 'ABC  ', 'ABC  ', 'ABC  ');
+  INSERT INTO t8 VALUES('ABC',   'ABC',   'ABC',   'ABC');
+} {}
+do_createtable_tests 3.9 {
+  2    "SELECT a FROM t8 ORDER BY a, rowid"    {abc ABC {abc  } {ABC  }}
+  3    "SELECT b FROM t8 ORDER BY b, rowid"    {{ABC  } ABC abc {abc  }}
+  4    "SELECT c FROM t8 ORDER BY c, rowid"    {ABC {ABC  } abc {abc  }}
+  5    "SELECT d FROM t8 ORDER BY d, rowid"    {ABC {ABC  } abc {abc  }}
+}
+
+# EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited
+# by the SQLITE_MAX_COLUMN compile-time parameter.
+#
+proc columns {n} {
+  set res [list]
+  for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }
+  join $res ", "
+}
+do_execsql_test e_createtable-3.10.1 [subst {
+  CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);
+}] {}
+do_catchsql_test e_createtable-3.10.2 [subst {
+  CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);
+}] {1 {too many columns on t10}}
+
+# EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at
+# runtime using the sqlite3_limit() C/C++ interface.
+#
+#   A 30,000 byte blob consumes 30,003 bytes of record space. A record 
+#   that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests
+#   3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered
+#   at runtime, are based on this calculation.
+#
+sqlite3_limit db SQLITE_LIMIT_COLUMN 500
+do_execsql_test e_createtable-3.11.1 [subst {
+  CREATE TABLE t10([columns 500]);
+}] {}
+do_catchsql_test e_createtable-3.11.2 [subst {
+  CREATE TABLE t11([columns 501]);
+}] {1 {too many columns on t11}}
+
+# Check that it is not possible to raise the column limit above its 
+# default compile time value.
+#
+sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]
+do_catchsql_test e_createtable-3.11.3 [subst {
+  CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);
+}] {1 {too many columns on t11}}
+
+sqlite3_limit db SQLITE_LIMIT_LENGTH 90010
+do_execsql_test e_createtable-3.11.4 {
+  CREATE TABLE t12(a, b, c);
+  INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
+} {}
+do_catchsql_test e_createtable-3.11.5 {
+  INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
+} {1 {string or blob too big}}
+
+#-------------------------------------------------------------------------
+# Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT 
+# NULL and CHECK constraints).
+#
+
+# EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one
+# PRIMARY KEY.
+# 
+# EVIDENCE-OF: R-18080-47271 If there is more than one PRIMARY KEY
+# clause in a single CREATE TABLE statement, it is an error.
+#
+#     To test the two above, show that zero primary keys is Ok, one primary
+#     key is Ok, and two or more primary keys is an error.
+#
+drop_all_tables
+do_createtable_tests 4.1.1 {
+  1    "CREATE TABLE t1(a, b, c)"                                        {}
+  2    "CREATE TABLE t2(a PRIMARY KEY, b, c)"                            {}
+  3    "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))"                        {}
+  4    "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))"                    {}
+}
+do_createtable_tests 4.1.2 -error {
+  table "t5" has more than one primary key
+} {
+  1    "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)"                {}
+  2    "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))"            {}
+  3    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)"        {}
+  4    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}
+  5    "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))"            {}
+  6    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))"    {}
+}
+
+proc table_pk {tbl} { 
+  set pk [list]
+  db eval "pragma table_info($tbl)" a {
+    if {$a(pk)} { lappend pk $a(name) }
+  }
+  set pk
+}
+
+# EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a
+# column definition, then the primary key for the table consists of that
+# single column.
+#
+#     The above is tested by 4.2.1.*
+#
+# EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as
+# a table-constraint, then the primary key of the table consists of the
+# list of columns specified as part of the PRIMARY KEY clause.
+#
+#     The above is tested by 4.2.2.*
+#
+do_createtable_tests 4.2 -repair {
+  catchsql { DROP TABLE t5 }
+} -tclquery {
+  table_pk t5
+} {
+  1.1    "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)"       {b}
+  1.2    "CREATE TABLE t5(a PRIMARY KEY, b, c)"               {a}
+
+  2.1    "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))"           {a}
+  2.2    "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))"       {a b c}
+  2.3    "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)"       {b}
+}
+
+# EVIDENCE-OF: R-33986-09410 Each row in a table with a primary key must
+# feature a unique combination of values in its primary key columns.
+#
+# EVIDENCE-OF: R-39102-06737 If an INSERT or UPDATE statement attempts
+# to modify the table content so that two or more rows feature identical
+# primary key values, it is a constraint violation.
+#
+drop_all_tables
+do_execsql_test 4.3.0 {
+  CREATE TABLE t1(x PRIMARY KEY, y);
+  INSERT INTO t1 VALUES(0,          'zero');
+  INSERT INTO t1 VALUES(45.5,       'one');
+  INSERT INTO t1 VALUES('brambles', 'two');
+  INSERT INTO t1 VALUES(X'ABCDEF',  'three');
+
+  CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
+  INSERT INTO t2 VALUES(0,          'zero');
+  INSERT INTO t2 VALUES(45.5,       'one');
+  INSERT INTO t2 VALUES('brambles', 'two');
+  INSERT INTO t2 VALUES(X'ABCDEF',  'three');
+} {}
+
+do_createtable_tests 4.3.1 -error { %s not unique } {
+  1    "INSERT INTO t1 VALUES(0, 0)"                 {"column x is"}
+  2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {"column x is"}
+  3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {"column x is"}
+  4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {"column x is"}
+  5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {"column x is"}
+
+  6    "INSERT INTO t2 VALUES(0, 'zero')"            {"columns x, y are"}
+  7    "INSERT INTO t2 VALUES(45.5, 'one')"          {"columns x, y are"}
+  8    "INSERT INTO t2 VALUES(0.0, 'zero')"          {"columns x, y are"}
+  9    "INSERT INTO t2 VALUES('brambles', 'two')"    {"columns x, y are"}
+  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'three')"   {"columns x, y are"}
+}
+do_createtable_tests 4.3.2 {
+  1    "INSERT INTO t1 VALUES(-1, 0)"                {}
+  2    "INSERT INTO t1 VALUES(45.2, 'abc')"          {}
+  3    "INSERT INTO t1 VALUES(0.01, 'abc')"          {}
+  4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
+  5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}
+
+  6    "INSERT INTO t2 VALUES(0, 0)"                 {}
+  7    "INSERT INTO t2 VALUES(45.5, 'abc')"          {}
+  8    "INSERT INTO t2 VALUES(0.0, 'abc')"           {}
+  9    "INSERT INTO t2 VALUES('brambles', 'abc')"    {}
+  10   "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')"     {}
+}
+do_createtable_tests 4.3.3 -error { %s not unique } {
+  1    "UPDATE t1 SET x=0           WHERE y='two'"    {"column x is"}
+  2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {"column x is"}
+  3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {"column x is"}
+  4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {"column x is"}
+  5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {"column x is"}
+
+  6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {"columns x, y are"}
+  7    "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"  
+       {"columns x, y are"}
+  8    "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
+  9    "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" 
+       {"columns x, y are"}
+  10   "UPDATE t2 SET x=0.0, y='zero'        WHERE y='three'"  
+       {"columns x, y are"}
+}
+
+
+# EVIDENCE-OF: R-52572-02078 For the purposes of determining the
+# uniqueness of primary key values, NULL values are considered distinct
+# from all other values, including other NULLs.
+#
+do_createtable_tests 4.4 {
+  1    "INSERT INTO t1 VALUES(NULL, 0)"              {}
+  2    "INSERT INTO t1 VALUES(NULL, 0)"              {}
+  3    "INSERT INTO t1 VALUES(NULL, 0)"              {}
+
+  4    "INSERT INTO t2 VALUES(NULL, 'zero')"         {}
+  5    "INSERT INTO t2 VALUES(NULL, 'one')"          {}
+  6    "INSERT INTO t2 VALUES(NULL, 'two')"          {}
+  7    "INSERT INTO t2 VALUES(NULL, 'three')"        {}
+
+  8    "INSERT INTO t2 VALUES(0, NULL)"              {}
+  9    "INSERT INTO t2 VALUES(45.5, NULL)"           {}
+  10   "INSERT INTO t2 VALUES(0.0, NULL)"            {}
+  11   "INSERT INTO t2 VALUES('brambles', NULL)"     {}
+  12   "INSERT INTO t2 VALUES(X'ABCDEF', NULL)"      {}
+
+  13   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
+  14   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
+}
+
+# EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY
+# SQLite allows NULL values in a PRIMARY KEY column.
+#
+#     If the column is an integer primary key, attempting to insert a NULL
+#     into the column triggers the auto-increment behaviour. Attempting
+#     to use UPDATE to set an ipk column to a NULL value is an error.
+#
+do_createtable_tests 4.5.1 {
+  1    "SELECT count(*) FROM t1 WHERE x IS NULL"                   3
+  2    "SELECT count(*) FROM t2 WHERE x IS NULL"                   6
+  3    "SELECT count(*) FROM t2 WHERE y IS NULL"                   7
+  4    "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL"     2
+}
+do_execsql_test 4.5.2 {
+  CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
+  INSERT INTO t3 VALUES(1, NULL, 2);
+  INSERT INTO t3 VALUES('x', NULL, 'y');
+  SELECT u FROM t3;
+} {1 2}
+do_catchsql_test 4.5.3 {
+  INSERT INTO t3 VALUES(2, 5, 3);
+  UPDATE t3 SET u = NULL WHERE s = 2;
+} {1 {datatype mismatch}}
+
+# EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
+# KEY constraint, except that a single table may have any number of
+# UNIQUE constraints.
+#
+drop_all_tables
+do_createtable_tests 4.6 {
+  1    "CREATE TABLE t1(a UNIQUE, b UNIQUE)"                       {}
+  2    "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))"             {}
+  3    "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {}
+  4    "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))"                 {}
+}
+
+# EVIDENCE-OF: R-55240-58877 For each UNIQUE constraint on the table,
+# each row must feature a unique combination of values in the columns
+# identified by the UNIQUE constraint.
+#
+# EVIDENCE-OF: R-47733-51480 If an INSERT or UPDATE statement attempts
+# to modify the table content so that two or more rows feature identical
+# values in a set of columns that are subject to a UNIQUE constraint, it
+# is a constraint violation.
+#
+do_execsql_test 4.7.0 {
+  INSERT INTO t1 VALUES(1, 2);
+  INSERT INTO t1 VALUES(4.3, 5.5);
+  INSERT INTO t1 VALUES('reveal', 'variableness');
+  INSERT INTO t1 VALUES(X'123456', X'654321');
+
+  INSERT INTO t4 VALUES('xyx', 1, 1);
+  INSERT INTO t4 VALUES('xyx', 2, 1);
+  INSERT INTO t4 VALUES('uvw', 1, 1);
+}
+do_createtable_tests 4.7.1 -error { %s not unique } {
+  1    "INSERT INTO t1 VALUES(1, 'one')"             {{column a is}}
+  2    "INSERT INTO t1 VALUES(4.3, 'two')"           {{column a is}}
+  3    "INSERT INTO t1 VALUES('reveal', 'three')"    {{column a is}}
+  4    "INSERT INTO t1 VALUES(X'123456', 'four')"    {{column a is}}
+
+  5    "UPDATE t1 SET a = 1 WHERE rowid=2"           {{column a is}}
+  6    "UPDATE t1 SET a = 4.3 WHERE rowid=3"         {{column a is}}
+  7    "UPDATE t1 SET a = 'reveal' WHERE rowid=4"    {{column a is}}
+  8    "UPDATE t1 SET a = X'123456' WHERE rowid=1"   {{column a is}}
+
+  9    "INSERT INTO t4 VALUES('xyx', 1, 1)"          {{columns a, b, c are}}
+  10   "INSERT INTO t4 VALUES('xyx', 2, 1)"          {{columns a, b, c are}}
+  11   "INSERT INTO t4 VALUES('uvw', 1, 1)"          {{columns a, b, c are}}
+
+  12   "UPDATE t4 SET a='xyx' WHERE rowid=3"         {{columns a, b, c are}}
+  13   "UPDATE t4 SET b=1 WHERE rowid=2"             {{columns a, b, c are}}
+  14   "UPDATE t4 SET a=0, b=0, c=0"                 {{columns a, b, c are}}
+}
+
+# EVIDENCE-OF: R-21289-11559 As with PRIMARY KEY constraints, for the
+# purposes of UNIQUE constraints NULL values are considered distinct
+# from all other values (including other NULLs).
+#
+do_createtable_tests 4.8 {
+  1    "INSERT INTO t1 VALUES(NULL, NULL)"           {}
+  2    "INSERT INTO t1 VALUES(NULL, NULL)"           {}
+  3    "UPDATE t1 SET a = NULL"                      {}
+  4    "UPDATE t1 SET b = NULL"                      {}
+
+  5    "INSERT INTO t4 VALUES(NULL, NULL, NULL)"     {}
+  6    "INSERT INTO t4 VALUES(NULL, NULL, NULL)"     {}
+  7    "UPDATE t4 SET a = NULL"                      {}
+  8    "UPDATE t4 SET b = NULL"                      {}
+  9    "UPDATE t4 SET c = NULL"                      {}
+}
+
+# EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both
+# UNIQUE and PRIMARY KEY constraints are implemented by creating an
+# index in the database (in the same way as a "CREATE UNIQUE INDEX"
+# statement would).
+do_createtable_tests 4.9 -repair drop_all_tables -query {
+  SELECT count(*) FROM sqlite_master WHERE type='index'
+} {
+  1    "CREATE TABLE t1(a TEXT PRIMARY KEY, b)"              1
+  2    "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)"           0
+  3    "CREATE TABLE t1(a TEXT UNIQUE, b)"                   1
+  4    "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)"       2
+  5    "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))"  2
+}
+
+# EVIDENCE-OF: R-02252-33116 Such an index is used like any other index
+# in the database to optimize queries.
+#
+do_execsql_test 4.10.0 {
+  CREATE TABLE t1(a, b PRIMARY KEY);
+  CREATE TABLE t2(a, b, c, UNIQUE(b, c));
+}
+do_createtable_tests 4.10 {
+  1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 
+       {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}
+
+  2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
+       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}
+
+  3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
+       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}}
+}
+
+# EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
+# column definition or specified as a table constraint. In practice it
+# makes no difference.
+#
+#   All the tests that deal with CHECK constraints below (4.11.* and 
+#   4.12.*) are run once for a table with the check constraint attached
+#   to a column definition, and once with a table where the check 
+#   condition is specified as a table constraint.
+#
+# EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the
+# table or an existing row is updated, the expression associated with
+# each CHECK constraint is evaluated and cast to a NUMERIC value in the
+# same way as a CAST expression. If the result is zero (integer value 0
+# or real value 0.0), then a constraint violation has occurred.
+#
+drop_all_tables
+do_execsql_test 4.11 {
+  CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));
+  CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));
+  INSERT INTO x1 VALUES('x', 'xx');
+  INSERT INTO x1 VALUES('y', 'yy');
+  INSERT INTO t1 SELECT * FROM x1;
+
+  CREATE TABLE x2(a CHECK( a||b ), b);
+  CREATE TABLE t2(a, b, CHECK( a||b ));
+  INSERT INTO x2 VALUES(1, 'xx');
+  INSERT INTO x2 VALUES(1, 'yy');
+  INSERT INTO t2 SELECT * FROM x2;
+}
+
+do_createtable_tests 4.11 -error {constraint failed} {
+  1a    "INSERT INTO x1 VALUES('one', 0)"       {}
+  1b    "INSERT INTO t1 VALUES('one', -4.0)"    {}
+
+  2a    "INSERT INTO x2 VALUES('abc', 1)"       {}
+  2b    "INSERT INTO t2 VALUES('abc', 1)"       {}
+
+  3a    "INSERT INTO x2 VALUES(0, 'abc')"       {}
+  3b    "INSERT INTO t2 VALUES(0, 'abc')"       {}
+
+  4a    "UPDATE t1 SET b=-1 WHERE rowid=1"      {}
+  4b    "UPDATE x1 SET b=-1 WHERE rowid=1"      {}
+
+  4a    "UPDATE x2 SET a='' WHERE rowid=1"      {}
+  4b    "UPDATE t2 SET a='' WHERE rowid=1"      {}
+}
+
+# EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,
+# or any other non-zero value, it is not a constraint violation.
+#
+do_createtable_tests 4.12 {
+  1a    "INSERT INTO x1 VALUES('one', NULL)"    {}
+  1b    "INSERT INTO t1 VALUES('one', NULL)"    {}
+
+  2a    "INSERT INTO x1 VALUES('one', 2)"    {}
+  2b    "INSERT INTO t1 VALUES('one', 2)"    {}
+
+  3a    "INSERT INTO x2 VALUES(1, 'abc')"       {}
+  3b    "INSERT INTO t2 VALUES(1, 'abc')"       {}
+}
+
+# EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached
+# to a column definition, not specified as a table constraint.
+#
+drop_all_tables
+do_createtable_tests 4.13.1 {
+  1     "CREATE TABLE t1(a NOT NULL, b)"                               {}
+  2     "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)"                   {}
+  3     "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)"   {}
+}
+do_createtable_tests 4.13.2 -error {
+  near "NOT": syntax error
+} {
+  1     "CREATE TABLE t4(a, b, NOT NULL(a))"                   {}
+  2     "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))"       {}
+  3     "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))"   {}
+}
+
+# EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the
+# associated column may not contain a NULL value. Attempting to set the
+# column value to NULL when inserting a new row or updating an existing
+# one causes a constraint violation.
+#
+#     These tests use the tables created by 4.13.
+#
+do_execsql_test 4.14.0 {
+  INSERT INTO t1 VALUES('x', 'y');
+  INSERT INTO t1 VALUES('z', NULL);
+
+  INSERT INTO t2 VALUES('x', 'y');
+  INSERT INTO t2 VALUES('z', NULL);
+
+  INSERT INTO t3 VALUES('x', 'y', 'z');
+  INSERT INTO t3 VALUES(1, 2, 3);
+}
+do_createtable_tests 4.14 -error {
+  %s may not be NULL
+} {
+  1    "INSERT INTO t1 VALUES(NULL, 'a')"         {t1.a}
+  2    "INSERT INTO t2 VALUES(NULL, 'b')"         {t2.a}
+  3    "INSERT INTO t3 VALUES('c', 'd', NULL)"    {t3.c}
+  4    "INSERT INTO t3 VALUES('e', NULL, 'f')"    {t3.b}
+  5    "INSERT INTO t3 VALUES(NULL, 'g', 'h')"    {t3.a}
+}
+
+# EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL
+# constraints may be explicitly assigned a default conflict resolution
+# algorithm by including a conflict-clause in their definitions.
+#
+#     Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE
+#
+#     Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL
+#     and UNIQUE constraints, respectively.
+#
+drop_all_tables
+do_execsql_test 4.15.0 {
+  CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b);
+  CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b);
+  CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b);
+  CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b);
+  CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b);
+  CREATE TABLE t1_xx(a PRIMARY KEY, b);
+
+  INSERT INTO t1_ab VALUES(1, 'one');
+  INSERT INTO t1_ab VALUES(2, 'two');
+  INSERT INTO t1_ro SELECT * FROM t1_ab;
+  INSERT INTO t1_ig SELECT * FROM t1_ab;
+  INSERT INTO t1_fa SELECT * FROM t1_ab;
+  INSERT INTO t1_re SELECT * FROM t1_ab;
+  INSERT INTO t1_xx SELECT * FROM t1_ab;
+
+  CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT);
+  CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK);
+  CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE);
+  CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL);
+  CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE);
+  CREATE TABLE t2_xx(a, b NOT NULL);
+
+  INSERT INTO t2_ab VALUES(1, 'one');
+  INSERT INTO t2_ab VALUES(2, 'two');
+  INSERT INTO t2_ro SELECT * FROM t2_ab;
+  INSERT INTO t2_ig SELECT * FROM t2_ab;
+  INSERT INTO t2_fa SELECT * FROM t2_ab;
+  INSERT INTO t2_re SELECT * FROM t2_ab;
+  INSERT INTO t2_xx SELECT * FROM t2_ab;
+
+  CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT);
+  CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK);
+  CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE);
+  CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL);
+  CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE);
+  CREATE TABLE t3_xx(a, b, UNIQUE(a, b));
+
+  INSERT INTO t3_ab VALUES(1, 'one');
+  INSERT INTO t3_ab VALUES(2, 'two');
+  INSERT INTO t3_ro SELECT * FROM t3_ab;
+  INSERT INTO t3_ig SELECT * FROM t3_ab;
+  INSERT INTO t3_fa SELECT * FROM t3_ab;
+  INSERT INTO t3_re SELECT * FROM t3_ab;
+  INSERT INTO t3_xx SELECT * FROM t3_ab;
+}
+
+foreach {tn tbl res ac data} {
+  1   t1_ab    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
+  2   t1_ro    {1 {column a is not unique}} 1 {1 one 2 two}
+  3   t1_fa    {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string}
+  4   t1_ig    {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
+  5   t1_re    {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
+  6   t1_xx    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
+} {
+  catchsql COMMIT
+  do_execsql_test  4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
+
+  do_catchsql_test 4.15.$tn.2 " 
+    INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
+  " $res
+
+  do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac
+  do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
+}
+foreach {tn tbl res ac data} {
+  1   t2_ab    {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three}
+  2   t2_ro    {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two}
+  3   t2_fa    {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx}
+  4   t2_ig    {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
+  5   t2_re    {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three}
+  6   t2_xx    {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three}
+} {
+  catchsql COMMIT
+  do_execsql_test  4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
+
+  do_catchsql_test 4.16.$tn.2 " 
+    INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
+  " $res
+
+  do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac
+  do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
+}
+foreach {tn tbl res ac data} {
+  1   t3_ab    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
+  2   t3_ro    {1 {columns a, b are not unique}} 1 {1 one 2 two}
+  3   t3_fa    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three}
+  4   t3_ig    {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
+  5   t3_re    {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
+  6   t3_xx    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
+} {
+  catchsql COMMIT
+  do_execsql_test  4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
+
+  do_catchsql_test 4.17.$tn.2 " 
+    INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
+  " $res
+
+  do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
+  do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data
+}
+catchsql COMMIT
+
+# EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
+# include a conflict-clause or it is a CHECK constraint, the default
+# conflict resolution algorithm is ABORT.
+#
+#     The first half of the above is tested along with explicit ON 
+#     CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx
+#     and t3_xx). The following just tests that the default conflict
+#     handling for CHECK constraints is ABORT.
+#
+do_execsql_test 4.18.1 {
+  CREATE TABLE t4(a, b CHECK (b!=10));
+  INSERT INTO t4 VALUES(1, 2);
+  INSERT INTO t4 VALUES(3, 4);
+}
+do_execsql_test  4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
+do_catchsql_test 4.18.3 { 
+  INSERT INTO t4 SELECT a+4, b+4 FROM t4
+} {1 {constraint failed}}
+do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0
+do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}
+
+# EVIDENCE-OF: R-19114-56113 Different constraints within the same table
+# may have different default conflict resolution algorithms.
+#
+do_execsql_test 4.19.0 {
+  CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
+}
+do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
+do_execsql_test  4.19.2 { SELECT * FROM t5 } {}
+do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
+  {1 {t5.b may not be NULL}}
+do_execsql_test  4.19.4 { SELECT * FROM t5 } {}
+
+#------------------------------------------------------------------------
+# Tests for INTEGER PRIMARY KEY and rowid related statements.
+#
+
+# EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
+# of the special case-independent names "rowid", "oid", or "_rowid_" in
+# place of a column name.
+#
+drop_all_tables
+do_execsql_test 5.1.0 {
+  CREATE TABLE t1(x, y);
+  INSERT INTO t1 VALUES('one', 'first');
+  INSERT INTO t1 VALUES('two', 'second');
+  INSERT INTO t1 VALUES('three', 'third');
+}
+do_createtable_tests 5.1 {
+  1   "SELECT rowid FROM t1"        {1 2 3}
+  2   "SELECT oid FROM t1"          {1 2 3}
+  3   "SELECT _rowid_ FROM t1"      {1 2 3}
+  4   "SELECT ROWID FROM t1"        {1 2 3}
+  5   "SELECT OID FROM t1"          {1 2 3}
+  6   "SELECT _ROWID_ FROM t1"      {1 2 3}
+  7   "SELECT RoWiD FROM t1"        {1 2 3}
+  8   "SELECT OiD FROM t1"          {1 2 3}
+  9   "SELECT _RoWiD_ FROM t1"      {1 2 3}
+}
+
+# EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
+# named "rowid", "oid" or "_rowid_", then that name always refers the
+# explicitly declared column and cannot be used to retrieve the integer
+# rowid value.
+#
+do_execsql_test 5.2.0 {
+  CREATE TABLE t2(oid, b);
+  CREATE TABLE t3(a, _rowid_);
+  CREATE TABLE t4(a, b, rowid);
+
+  INSERT INTO t2 VALUES('one', 'two');
+  INSERT INTO t2 VALUES('three', 'four');
+
+  INSERT INTO t3 VALUES('five', 'six');
+  INSERT INTO t3 VALUES('seven', 'eight');
+
+  INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
+  INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
+}
+do_createtable_tests 5.2 {
+  1   "SELECT oid, rowid, _rowid_ FROM t2"   {one 1 1      three 2 2}
+  2   "SELECT oid, rowid, _rowid_ FROM t3"   {1 1 six      2 2 eight} 
+  3   "SELECT oid, rowid, _rowid_ FROM t4"   {1 eleven 1   2 fourteen 2}
+}
+
+
+# Argument $tbl is the name of a table in the database. Argument $col is
+# the name of one of the tables columns. Return 1 if $col is an alias for
+# the rowid, or 0 otherwise.
+#
+proc is_integer_primary_key {tbl col} {
+  lindex [db eval [subst {
+    DELETE FROM $tbl;
+    INSERT INTO $tbl ($col) VALUES(0);
+    SELECT (rowid==$col) FROM $tbl;
+    DELETE FROM $tbl;
+  }]] 0
+}
+
+# EVIDENCE-OF: R-53738-31673 With one exception, if a table has a
+# primary key that consists of a single column, and the declared type of
+# that column is "INTEGER" in any mixture of upper and lower case, then
+# the column becomes an alias for the rowid.
+#
+# EVIDENCE-OF: R-45951-08347 if the declaration of a column with
+# declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does
+# not become an alias for the rowid and is not classified as an integer
+# primary key.
+#
+do_createtable_tests 5.3 -tclquery { 
+  is_integer_primary_key t5 pk
+} -repair {
+  catchsql { DROP TABLE t5 }
+} {
+  1   "CREATE TABLE t5(pk integer primary key)"                         1
+  2   "CREATE TABLE t5(pk integer, primary key(pk))"                    1
+  3   "CREATE TABLE t5(pk integer, v integer, primary key(pk))"         1
+  4   "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))"      0
+  5   "CREATE TABLE t5(pk int, v integer, primary key(pk, v))"          0
+  6   "CREATE TABLE t5(pk int, v integer, primary key(pk))"             0
+  7   "CREATE TABLE t5(pk int primary key, v integer)"                  0
+  8   "CREATE TABLE t5(pk inTEger primary key)"                         1
+  9   "CREATE TABLE t5(pk inteGEr, primary key(pk))"                    1
+  10  "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))"         1
+}
+
+# EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or
+# "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
+# key column to behave as an ordinary table column with integer affinity
+# and a unique index, not as an alias for the rowid.
+#
+do_execsql_test 5.4.1 {
+  CREATE TABLE t6(pk INT primary key);
+  CREATE TABLE t7(pk BIGINT primary key);
+  CREATE TABLE t8(pk SHORT INTEGER primary key);
+  CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
+} 
+do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0
+do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0
+do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0
+do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0
+
+do_execsql_test 5.4.3 {
+  INSERT INTO t6 VALUES('2.0');
+  INSERT INTO t7 VALUES('2.0');
+  INSERT INTO t8 VALUES('2.0');
+  INSERT INTO t9 VALUES('2.0');
+  SELECT typeof(pk), pk FROM t6;
+  SELECT typeof(pk), pk FROM t7;
+  SELECT typeof(pk), pk FROM t8;
+  SELECT typeof(pk), pk FROM t9;
+} {integer 2 integer 2 integer 2 integer 2}
+
+do_catchsql_test 5.4.4.1 { 
+  INSERT INTO t6 VALUES(2) 
+} {1 {column pk is not unique}}
+do_catchsql_test 5.4.4.2 { 
+  INSERT INTO t7 VALUES(2) 
+} {1 {column pk is not unique}}
+do_catchsql_test 5.4.4.3 { 
+  INSERT INTO t8 VALUES(2) 
+} {1 {column pk is not unique}}
+do_catchsql_test 5.4.4.4 { 
+  INSERT INTO t9 VALUES(2) 
+} {1 {column pk is not unique}}
+
+# EVIDENCE-OF: R-56094-57830 the following three table declarations all
+# cause the column "x" to be an alias for the rowid (an integer primary
+# key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
+# t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
+# z, PRIMARY KEY(x DESC));
+#
+# EVIDENCE-OF: R-20149-25884 the following declaration does not result
+# in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY
+# KEY DESC, y, z);
+#
+do_createtable_tests 5 -tclquery { 
+  is_integer_primary_key t x
+} -repair {
+  catchsql { DROP TABLE t }
+} {
+  5.1    "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)"      1
+  5.2    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))"  1
+  5.3    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1
+  6.1    "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)"     0
+}
+
+# EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
+# UPDATE statement in the same way as any other column value can, either
+# using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
+# using an alias created by an integer primary key.
+#
+do_execsql_test 5.7.0 {
+  CREATE TABLE t10(a, b);
+  INSERT INTO t10 VALUES('ten', 10);
+
+  CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
+  INSERT INTO t11 VALUES('ten', 10);
+}
+do_createtable_tests 5.7.1 -query { 
+  SELECT rowid, _rowid_, oid FROM t10;
+} {
+  1    "UPDATE t10 SET rowid = 5"   {5 5 5}
+  2    "UPDATE t10 SET _rowid_ = 6" {6 6 6}
+  3    "UPDATE t10 SET oid = 7"     {7 7 7}
+}
+do_createtable_tests 5.7.2 -query { 
+  SELECT rowid, _rowid_, oid, b FROM t11;
+} {
+  1    "UPDATE t11 SET rowid = 5"   {5 5 5 5}
+  2    "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}
+  3    "UPDATE t11 SET oid = 7"     {7 7 7 7}
+  4    "UPDATE t11 SET b = 8"       {8 8 8 8}
+}
+
+# EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide
+# a value to use as the rowid for each row inserted.
+#
+do_createtable_tests 5.8.1 -query { 
+  SELECT rowid, _rowid_, oid FROM t10;
+} -repair { 
+  execsql { DELETE FROM t10 } 
+} {
+  1    "INSERT INTO t10(oid) VALUES(15)"           {15 15 15}
+  2    "INSERT INTO t10(rowid) VALUES(16)"         {16 16 16}
+  3    "INSERT INTO t10(_rowid_) VALUES(17)"       {17 17 17}
+  4    "INSERT INTO t10(a, b, oid) VALUES(1,2,3)"  {3 3 3}
+}
+do_createtable_tests 5.8.2 -query { 
+  SELECT rowid, _rowid_, oid, b FROM t11;
+} -repair { 
+  execsql { DELETE FROM t11 } 
+} {
+  1    "INSERT INTO t11(oid) VALUES(15)"           {15 15 15 15}
+  2    "INSERT INTO t11(rowid) VALUES(16)"         {16 16 16 16}
+  3    "INSERT INTO t11(_rowid_) VALUES(17)"       {17 17 17 17}
+  4    "INSERT INTO t11(a, b) VALUES(1,2)"         {2 2 2 2}
+}
+
+# EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer
+# primary key or rowid column must contain integer values. Integer
+# primary key or rowid columns are not able to hold floating point
+# values, strings, BLOBs, or NULLs.
+#
+#     This is considered by the tests for the following 3 statements,
+#     which show that:
+#
+#       1. Attempts to UPDATE a rowid column to a non-integer value fail,
+#       2. Attempts to INSERT a real, string or blob value into a rowid 
+#          column fail, and
+#       3. Attempting to INSERT a NULL value into a rowid column causes the
+#          system to automatically select an integer value to use.
+#
+
+
+# EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an
+# integer primary key or rowid column to a NULL or blob value, or to a
+# string or real value that cannot be losslessly converted to an
+# integer, a "datatype mismatch" error occurs and the statement is
+# aborted.
+#
+drop_all_tables
+do_execsql_test 5.9.0 {
+  CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
+  INSERT INTO t12 VALUES(5, 'five');
+}
+do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {
+  1   "UPDATE t12 SET x = 4"       {integer 4}
+  2   "UPDATE t12 SET x = 10.0"    {integer 10}
+  3   "UPDATE t12 SET x = '12.0'"  {integer 12}
+  4   "UPDATE t12 SET x = '-15.0'" {integer -15}
+}
+do_createtable_tests 5.9.2 -error {
+  datatype mismatch
+} {
+  1   "UPDATE t12 SET x = 4.1"         {}
+  2   "UPDATE t12 SET x = 'hello'"     {}
+  3   "UPDATE t12 SET x = NULL"        {}
+  4   "UPDATE t12 SET x = X'ABCD'"     {}
+  5   "UPDATE t12 SET x = X'3900'"     {}
+  6   "UPDATE t12 SET x = X'39'"       {}
+}
+
+# EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a
+# blob value, or a string or real value that cannot be losslessly
+# converted to an integer into an integer primary key or rowid column, a
+# "datatype mismatch" error occurs and the statement is aborted.
+#
+do_execsql_test 5.10.0 { DELETE FROM t12 }
+do_createtable_tests 5.10.1 -error { 
+  datatype mismatch
+} {
+  1   "INSERT INTO t12(x) VALUES(4.1)"     {}
+  2   "INSERT INTO t12(x) VALUES('hello')" {}
+  3   "INSERT INTO t12(x) VALUES(X'ABCD')" {}
+  4   "INSERT INTO t12(x) VALUES(X'3900')" {}
+  5   "INSERT INTO t12(x) VALUES(X'39')"   {}
+}
+do_createtable_tests 5.10.2 -query { 
+  SELECT typeof(x), x FROM t12 
+} -repair {
+  execsql { DELETE FROM t12 }
+} {
+  1   "INSERT INTO t12(x) VALUES(4)"       {integer 4}
+  2   "INSERT INTO t12(x) VALUES(10.0)"    {integer 10}
+  3   "INSERT INTO t12(x) VALUES('12.0')"  {integer 12}
+  4   "INSERT INTO t12(x) VALUES('4e3')"   {integer 4000}
+  5   "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}
+}
+
+# EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a
+# NULL value into a rowid or integer primary key column, the system
+# chooses an integer value to use as the rowid automatically.
+#
+do_execsql_test 5.11.0 { DELETE FROM t12 }
+do_createtable_tests 5.11 -query { 
+  SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)
+} {
+  1   "INSERT INTO t12 DEFAULT VALUES"                {integer 1}
+  2   "INSERT INTO t12(y)   VALUES(5)"                {integer 2}
+  3   "INSERT INTO t12(x,y) VALUES(NULL, 10)"         {integer 3}
+  4   "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12" 
+      {integer 4 integer 5 integer 6}
+  5   "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"
+      {integer 7 integer 8 integer 9}
+}
+
+finish_test