MDEV-16102 Wrong ER_DUP_ENTRY upon ADD UNIQUE KEY on versioned table

* ignore CHECK constraint for historical rows;
* FOREIGN KEY test case.

TODO:
MDEV-16301 IB: use real table name for error messages on ALTER

Closes tempesta-tech/mariadb#491
Closes #748

7 files changed, 67 insertions, 14 deletions

diff --git a/mysql-test/main/partition_alter.result b/mysql-test/main/partition_alter.result
index 448c26c7919..ca6359f94de 100644
--- a/mysql-test/main/partition_alter.result
+++ b/mysql-test/main/partition_alter.result
@@ -59,7 +59,7 @@ partition p2 values less than ('2020-10-19'));
 insert t1 values (0, '2000-01-02', 0);
 insert t1 values (1, '2020-01-02', 10);
 alter table t1 add check (b in (0, 1));
-ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`#sql-temporary`
+ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1`
 alter table t1 add check (b in (0, 10));
 show create table t1;
 Table	Create Table
@@ -84,7 +84,7 @@ partition p2 values less than ('2020-10-19'));
 insert t1 values (0, '2000-01-02', 0);
 insert t1 values (1, '2020-01-02', 10);
 alter table t1 add check (b in (0, 1));
-ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`#sql-temporary`
+ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1`
 alter table t1 add check (b in (0, 10));
 show create table t1;
 Table	Create Table
diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result
index fafcf3c30b0..8db23ef6d39 100644
--- a/mysql-test/suite/versioning/r/alter.result
+++ b/mysql-test/suite/versioning/r/alter.result
@@ -19,6 +19,10 @@ t	CREATE TABLE `t` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 alter table t add column y int;
 ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER.
+alter table t add primary key (a);
+ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER.
+alter table t add unique key (a);
+ERROR HY000: Not allowed for system-versioned `test`.`t`. Change @@system_versioning_alter_history to proceed with ALTER.
 alter table t engine innodb;
 ERROR HY000: Not allowed for system-versioned `test`.`t`. Change to/from native system versioning engine is not supported.
 alter table t drop system versioning;
@@ -528,5 +532,15 @@ use test;
 create or replace table t1 (i int, j int as (i), s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning;
 alter table t1 modify s timestamp(6) as row start;
 ERROR HY000: Can not change system versioning field `s`
+# ignore CHECK for historical rows
+create or replace table t (a int) with system versioning;
+insert into t values (0), (1);
+delete from t where a = 0;
+alter table t add check (a > 1);
+ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t`
+alter table t add check (a > 0);
+insert into t values (0);
+ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t`
+insert into t values (2);
 drop database test;
 create database test;
diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test
index d570b6f4259..05bd6019a32 100644
--- a/mysql-test/suite/versioning/t/alter.test
+++ b/mysql-test/suite/versioning/t/alter.test
@@ -12,6 +12,10 @@ show create table t;
 
 --error ER_VERS_ALTER_NOT_ALLOWED
 alter table t add column y int;
+--error ER_VERS_ALTER_NOT_ALLOWED
+alter table t add primary key (a);
+--error ER_VERS_ALTER_NOT_ALLOWED
+alter table t add unique key (a);
 --error ER_VERS_ALTER_ENGINE_PROHIBITED
 alter table t engine innodb;
 
@@ -454,5 +458,16 @@ create or replace table t1 (i int, j int as (i), s timestamp(6) as row start, e
 --error ER_VERS_ALTER_SYSTEM_FIELD
 alter table t1 modify s timestamp(6) as row start;
 
+--echo # ignore CHECK for historical rows
+create or replace table t (a int) with system versioning;
+insert into t values (0), (1);
+delete from t where a = 0;
+--error ER_CONSTRAINT_FAILED
+alter table t add check (a > 1);
+alter table t add check (a > 0);
+--error ER_CONSTRAINT_FAILED
+insert into t values (0);
+insert into t values (2);
+
 drop database test;
 create database test;
diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc
index bf78bd12192..a96b0a5a32b 100644
--- a/sql/sql_alter.cc
+++ b/sql/sql_alter.cc
@@ -1,5 +1,5 @@
 /* Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
-   Copyright (c) 2016, MariaDB Corporation
+   Copyright (c) 2016, 2018, MariaDB Corporation
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -209,6 +209,35 @@ bool Alter_info::supports_lock(THD *thd, enum_alter_inplace_result result,
   return false;
 }
 
+bool Alter_info::vers_prohibited(THD *thd) const
+{
+  if (thd->slave_thread ||
+      thd->variables.vers_alter_history != VERS_ALTER_HISTORY_ERROR)
+  {
+    return false;
+  }
+  if (flags & (
+    ALTER_PARSER_ADD_COLUMN |
+    ALTER_PARSER_DROP_COLUMN |
+    ALTER_CHANGE_COLUMN |
+    ALTER_COLUMN_ORDER))
+  {
+    return true;
+  }
+  if (flags & ALTER_ADD_INDEX)
+  {
+    List_iterator_fast<Key> key_it(const_cast<List<Key> &>(key_list));
+    Key *key;
+    while ((key= key_it++))
+    {
+      if (key->type == Key::PRIMARY || key->type == Key::UNIQUE)
+        return true;
+    }
+  }
+  return false;
+}
+
+
 Alter_table_ctx::Alter_table_ctx()
   : datetime_field(NULL), error_if_not_empty(false),
     tables_opened(0),
diff --git a/sql/sql_alter.h b/sql/sql_alter.h
index 268dbc43abd..108b98afdd7 100644
--- a/sql/sql_alter.h
+++ b/sql/sql_alter.h
@@ -32,14 +32,7 @@ public:
 
   enum enum_enable_or_disable { LEAVE_AS_IS, ENABLE, DISABLE };
 
-  bool data_modifying() const
-  {
-    return flags & (
-      ALTER_PARSER_ADD_COLUMN |
-      ALTER_PARSER_DROP_COLUMN |
-      ALTER_CHANGE_COLUMN |
-      ALTER_COLUMN_ORDER);
-  }
+  bool vers_prohibited(THD *thd) const;
 
   /**
      The different values of the ALGORITHM clause.
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 0473561fd9e..74a6e04b928 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -9130,8 +9130,7 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db,
         DBUG_RETURN(true);
       }
     }
-    if (alter_info->data_modifying() && !thd->slave_thread &&
-        thd->variables.vers_alter_history == VERS_ALTER_HISTORY_ERROR)
+    if (alter_info->vers_prohibited(thd))
     {
       my_error(ER_VERS_ALTER_NOT_ALLOWED, MYF(0),
                table_list->db.str, table_list->table_name.str);
diff --git a/sql/table.cc b/sql/table.cc
index 58e0c0a9189..843e87c4e02 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -5231,6 +5231,8 @@ int TABLE::verify_constraints(bool ignore_failure)
   if (check_constraints &&
       !(in_use->variables.option_bits & OPTION_NO_CHECK_CONSTRAINT_CHECKS))
   {
+    if (versioned() && !vers_end_field()->is_max())
+      return VIEW_CHECK_OK;
     for (Virtual_column_info **chk= check_constraints ; *chk ; chk++)
     {
       /*
@@ -5242,7 +5244,7 @@ int TABLE::verify_constraints(bool ignore_failure)
       {
         my_error(ER_CONSTRAINT_FAILED,
                  MYF(ignore_failure ? ME_JUST_WARNING : 0), (*chk)->name.str,
-                 s->db.str, s->table_name.str);
+                 s->db.str, s->error_table_name());
         return ignore_failure ? VIEW_CHECK_SKIP : VIEW_CHECK_ERROR;
       }
     }
@@ -7780,6 +7782,7 @@ void TABLE::vers_update_fields()
   }
 
   vers_end_field()->set_max();
+  bitmap_set_bit(read_set, vers_end_field()->field_index);
 }