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

3 files changed, 31 insertions, 2 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;