1 files changed, 78 insertions, 0 deletions
diff --git a/test/unique2.test b/test/unique2.test
new file mode 100644
index 0000000..6a320d4
--- /dev/null
+++ b/test/unique2.test
@@ -0,0 +1,78 @@
+# 2014-07-30
+#
+# 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 regression tests for SQLite library.  The
+# focus of this file is testing the CREATE UNIQUE INDEX statement
+# to verify that ticket 9a6daf340df99ba93c53bcf8fa83d9f28040d2a8
+# has been fixed:
+#
+#  drh added on 2014-07-30 12:33:04:
+#
+#  The CREATE UNIQUE INDEX on the third line below does not fail even
+#  though the x column values are not all unique.
+#
+#     CREATE TABLE t1(x NOT NULL);
+#     INSERT INTO t1 VALUES(1),(2),(2),(3);
+#     CREATE UNIQUE INDEX t1x ON t1(x);
+#
+# If the index is created before the INSERT, then uniqueness is enforced
+# at the point of the INSERT. Note that the NOT NULL on the indexed column
+# seems to be required in order to exhibit this bug.
+#
+# "PRAGMA integrity_check" does not detect the resulting malformed database.
+# That might be considered a separate issue.
+#
+# Bisecting shows that this problem was introduced by the addition of
+#  WITHOUT ROWID support in version 3.8.2, specifically in check-in
+# [c80e229dd9c1230] on 2013-11-07. This problem was reported on the mailing
+# list by Pavel Pimenov.  and primary keys, and the UNIQUE constraint
+# on table columns
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+foreach {id sql} {
+   1 {CREATE TABLE t1(x TEXT PRIMARY KEY, y NOT NULL) WITHOUT ROWID}
+   2 {CREATE TABLE t1(x TEXT PRIMARY KEY, y NOT NULL)}
+   3 {CREATE TABLE t1(x TEXT PRIMARY KEY, y) WITHOUT ROWID}
+   4 {CREATE TABLE t1(x TEXT PRIMARY KEY, y)}
+} {
+  do_test $id.1 {
+    db eval {DROP TABLE IF EXISTS t1}
+    db eval $sql
+    db eval {INSERT INTO t1(x,y) VALUES(1,1),(2,2),(3,2),(4,3)}
+  } {}
+  do_test $id.2 {
+    catchsql {CREATE UNIQUE INDEX t1y ON t1(y)}
+  } {1 {UNIQUE constraint failed: t1.y}}
+}
+
+foreach {id sql} {
+   5 {CREATE TABLE t1(w,x,y NOT NULL,z NOT NULL,PRIMARY KEY(w,x)) WITHOUT ROWID}
+   6 {CREATE TABLE t1(w,x,y NOT NULL,z NOT NULL,PRIMARY KEY(w,x))}
+   7 {CREATE TABLE t1(w,x,y NOT NULL,z,PRIMARY KEY(w,x)) WITHOUT ROWID}
+   8 {CREATE TABLE t1(w,x,y NOT NULL,z,PRIMARY KEY(w,x))}
+   9 {CREATE TABLE t1(w,x,y,z NOT NULL,PRIMARY KEY(w,x)) WITHOUT ROWID}
+  10 {CREATE TABLE t1(w,x,y,z NOT NULL,PRIMARY KEY(w,x))}
+  11 {CREATE TABLE t1(w,x,y,z,PRIMARY KEY(w,x)) WITHOUT ROWID}
+  12 {CREATE TABLE t1(w,x,y,z,PRIMARY KEY(w,x))}
+} {
+  do_test $id.1 {
+    db eval {DROP TABLE IF EXISTS t1}
+    db eval $sql
+    db eval {INSERT INTO t1(w,x,y,z) VALUES(1,2,3,4),(2,3,3,4)}
+  } {}
+  do_test $id.2 {
+    catchsql {CREATE UNIQUE INDEX t1yz ON t1(y,z)}
+  } {1 {UNIQUE constraint failed: t1.y, t1.z}}
+}
+
+finish_test