test/unique2.test

# 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}}
}

do_catchsql_test 13.1 {
  CREATE TABLE err1(a,b,c,UNIQUE(rowid));
} {1 {no such column: rowid}}
do_catchsql_test 13.2 {
  CREATE TABLE err1(a,b,c,PRIMARY KEY(rowid));
} {1 {no such column: rowid}}


finish_test