Import SQLite 3.8.7.4.

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.
 #
 #***********************************************************************
@@ skipping 40 matching lines @@
     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.
+# syntax diagram column-constraint
 #
 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)"    {}
 
@@ skipping 26 matching lines @@
       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
+# -- 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
+# -- 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
+# -- 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:         
 #
@@ skipping 659 matching lines @@
 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
+# EVIDENCE-OF: R-07343-35026 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.
+# signed-number, or any constant expression enclosed in parentheses. A
+# 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.
+# EVIDENCE-OF: R-18415-27776 For the purposes of the DEFAULT clause, an
+# expression is considered constant if it does contains no sub-queries,
+# column or table references, bound parameters, or string literals
+# enclosed in double-quotes instead of single-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) ))}  {}
+  5   {CREATE TABLE t5(x DEFAULT ( x!=?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
@@ skipping 66 matching lines @@
 } {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
+# EVIDENCE-OF: R-15363-55230 If the default value of a column is
+# CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, 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
@@ skipping 99 matching lines @@
 } {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.
+# EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY
+# KEY clause appears in a CREATE TABLE statement.
 #
 #     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))"    {}
 }
 
+# EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary
+# tables but is required for WITHOUT ROWID tables.
+#
+do_catchsql_test 4.1.3 {
+  CREATE TABLE t6(a, b); --ok
+} {0 {}}
+do_catchsql_test 4.1.4 {
+  CREATE TABLE t7(a, b) WITHOUT ROWID; --Error, no PRIMARY KEY
+} {1 {PRIMARY KEY missing on table t7}}
+
+
 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
@@ skipping 13 matching lines @@
   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-59124-61339 Each row in a table with a primary key must
+# have 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.
+# EVIDENCE-OF: R-06471-16287 If an INSERT or UPDATE statement attempts
+# to modify the table content so that two or more rows have identical
+# primary key values, that 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 } {
+do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t1.x} {
   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"}
-
+}
+do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t2.x, t2.y} {
   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 } {
+do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t1.x} {
   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"}
-
+}
+do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t2.x, t2.y} {
   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"}
 }
 
@@ skipping 15 matching lines @@
   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.
+# EVIDENCE-OF: R-35113-43214 Unless the column is an INTEGER PRIMARY KEY
+# or the table is a WITHOUT ROWID table or the column is declared NOT
+# NULL, 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
+#     into the column triggers the auto-increment behavior. 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}}
+do_catchsql_test 4.5.4 {
+  CREATE TABLE t4(s, u INT PRIMARY KEY, v) WITHOUT ROWID;
+  INSERT INTO t4 VALUES(1, NULL, 2);
+} {1 {NOT NULL constraint failed: t4.u}}
+do_catchsql_test 4.5.5 {
+  CREATE TABLE t5(s, u INT PRIMARY KEY NOT NULL, v);
+  INSERT INTO t5 VALUES(1, NULL, 2);
+} {1 {NOT NULL constraint failed: t5.u}}
 
 # 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
+# EVIDENCE-OF: R-30981-64168 For each UNIQUE constraint on the table,
+# each row must contain 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.
+# EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must
+# have a unique combination of values in its primary key columns.
 #
 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}}
+do_createtable_tests 4.7.1 -error {UNIQUE constraint failed: %s} {
+  1    "INSERT INTO t1 VALUES(1, 'one')"             {{t1.a}}
+  2    "INSERT INTO t1 VALUES(4.3, 'two')"           {{t1.a}}
+  3    "INSERT INTO t1 VALUES('reveal', 'three')"    {{t1.a}}
+  4    "INSERT INTO t1 VALUES(X'123456', 'four')"    {{t1.a}}
 
-  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}}
+  5    "UPDATE t1 SET a = 1 WHERE rowid=2"           {{t1.a}}
+  6    "UPDATE t1 SET a = 4.3 WHERE rowid=3"         {{t1.a}}
+  7    "UPDATE t1 SET a = 'reveal' WHERE rowid=4"    {{t1.a}}
+  8    "UPDATE t1 SET a = X'123456' WHERE rowid=1"   {{t1.a}}
 
-  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}}
+  9    "INSERT INTO t4 VALUES('xyx', 1, 1)"          {{t4.a, t4.b, t4.c}}
+  10   "INSERT INTO t4 VALUES('xyx', 2, 1)"          {{t4.a, t4.b, t4.c}}
+  11   "INSERT INTO t4 VALUES('uvw', 1, 1)"          {{t4.a, t4.b, t4.c}}
 
-  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}}
+  12   "UPDATE t4 SET a='xyx' WHERE rowid=3"         {{t4.a, t4.b, t4.c}}
+  13   "UPDATE t4 SET b=1 WHERE rowid=2"             {{t4.a, t4.b, t4.c}}
+  14   "UPDATE t4 SET a=0, b=0, c=0"                 {{t4.a, t4.b, t4.c}}
 }
 
-# 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).
+# EVIDENCE-OF: R-00404-17670 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).
+# EVIDENCE-OF: R-55820-29984 In most cases, UNIQUE and PRIMARY KEY
+# constraints are implemented by creating a unique index in the
+# database.
 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
+# Obsolete: 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)}}
+       {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)}}
 
   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)}}
+       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1}}
 
   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)}}
+       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)}}
 }
 
 # 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.
@@ skipping 12 matching lines @@
   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)"    {}
+do_createtable_tests 4.11 -error {CHECK constraint failed: %s} {
+  1a    "INSERT INTO x1 VALUES('one', 0)"       {x1}
+  1b    "INSERT INTO t1 VALUES('one', -4.0)"    {t1}
 
-  2a    "INSERT INTO x2 VALUES('abc', 1)"       {}
-  2b    "INSERT INTO t2 VALUES('abc', 1)"       {}
+  2a    "INSERT INTO x2 VALUES('abc', 1)"       {x2}
+  2b    "INSERT INTO t2 VALUES('abc', 1)"       {t2}
 
-  3a    "INSERT INTO x2 VALUES(0, 'abc')"       {}
-  3b    "INSERT INTO t2 VALUES(0, 'abc')"       {}
+  3a    "INSERT INTO x2 VALUES(0, 'abc')"       {x2}
+  3b    "INSERT INTO t2 VALUES(0, 'abc')"       {t2}
 
-  4a    "UPDATE t1 SET b=-1 WHERE rowid=1"      {}
-  4b    "UPDATE x1 SET b=-1 WHERE rowid=1"      {}
+  4a    "UPDATE t1 SET b=-1 WHERE rowid=1"      {t1}
+  4b    "UPDATE x1 SET b=-1 WHERE rowid=1"      {x1}
 
-  4a    "UPDATE x2 SET a='' WHERE rowid=1"      {}
-  4b    "UPDATE t2 SET a='' WHERE rowid=1"      {}
+  4a    "UPDATE x2 SET a='' WHERE rowid=1"      {x2}
+  4b    "UPDATE t2 SET a='' WHERE rowid=1"      {t2}
 }
 
 # 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)"    {}
@@ skipping 30 matching lines @@
 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
-} {
+do_createtable_tests 4.14 -error {NOT NULL constraint failed: %s} {
   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.
@@ skipping 45 matching lines @@
   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}
+  1   t1_ab    {1 {UNIQUE constraint failed: t1_ab.a}} 0 {1 one 2 two 3 three}
+  2   t1_ro    {1 {UNIQUE constraint failed: t1_ro.a}} 1 {1 one 2 two}
+  3   t1_fa    {1 {UNIQUE constraint failed: t1_fa.a}} 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}
+  6   t1_xx    {1 {UNIQUE constraint failed: t1_xx.a}} 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}
+  1   t2_ab    {1 {NOT NULL constraint failed: t2_ab.b}} 0 {1 one 2 two 3 three}
+  2   t2_ro    {1 {NOT NULL constraint failed: t2_ro.b}} 1 {1 one 2 two}
+  3   t2_fa    {1 {NOT NULL constraint failed: t2_fa.b}} 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}
+  5   t2_re    {1 {NOT NULL constraint failed: t2_re.b}} 0 {1 one 2 two 3 three}
+  6   t2_xx    {1 {NOT NULL constraint failed: t2_xx.b}} 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}
+  1   t3_ab    {1 {UNIQUE constraint failed: t3_ab.a, t3_ab.b}}
+               0 {1 one 2 two 3 three}
+  2   t3_ro    {1 {UNIQUE constraint failed: t3_ro.a, t3_ro.b}}
+               1 {1 one 2 two}
+  3   t3_fa    {1 {UNIQUE constraint failed: t3_fa.a, t3_fa.b}}
+               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}
+  6   t3_xx    {1 {UNIQUE constraint failed: t3_xx.a, t3_xx.b}}
+               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
+  do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $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}}
+} {1 {CHECK constraint failed: t4}}
 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}}
+  {1 {NOT NULL constraint failed: t5.b}}
 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.
 #
+# EVIDENCE-OF: R-06726-07466 A column name can be any of the names
+# defined in the CREATE TABLE statement or one of the following special
+# identifiers: "ROWID", "OID", or "_ROWID_".
+#
 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.
 #
+# EVIDENCE-OF: R-44615-33286 The special identifiers only refer to the
+# row key if the CREATE TABLE statement does not define a real column
+# with the same name.
+#
 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');
@@ skipping 14 matching lines @@
 #
 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-47901-33947 With one exception noted below, if a rowid
+# 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 }
@@ skipping 32 matching lines @@
   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}}
+} {1 {UNIQUE constraint failed: t6.pk}}
 do_catchsql_test 5.4.4.2 { 
   INSERT INTO t7 VALUES(2) 
-} {1 {column pk is not unique}}
+} {1 {UNIQUE constraint failed: t7.pk}}
 do_catchsql_test 5.4.4.3 { 
   INSERT INTO t8 VALUES(2) 
-} {1 {column pk is not unique}}
+} {1 {UNIQUE constraint failed: t8.pk}}
 do_catchsql_test 5.4.4.4 { 
   INSERT INTO t9 VALUES(2) 
-} {1 {column pk is not unique}}
+} {1 {UNIQUE constraint failed: t9.pk}}
 
 # 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);
@@ skipping 143 matching lines @@
   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