MDEV-25327 Unexpected ER_DUP_ENTRY upon dropping PK column from system-versioned table

When dropped all user key-parts we also drop key-parts of implicit system fields.

3 files changed, 49 insertions, 2 deletions

diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result
index 3666a5ddcb1..5092adc2b11 100644
--- a/mysql-test/suite/versioning/r/alter.result
+++ b/mysql-test/suite/versioning/r/alter.result
@@ -742,3 +742,20 @@ create or replace table t1 (x int);
 alter table t1 add column y timestamp(6) as row start;
 ERROR HY000: Table `t1` is not system-versioned
 drop table t1;
+#
+# MDEV-25327 Unexpected ER_DUP_ENTRY upon dropping PK column from system-versioned table
+#
+create table t1 (pk int, a int, primary key (pk), key (a))
+with system versioning;
+insert into t1 values (1, 1), (2, 2);
+delete from t1;
+set system_versioning_alter_history= keep;
+alter table t1 drop pk;
+drop table t1;
+create table t1 (pk int, a int, primary key (pk), key (a))
+with system versioning;
+insert into t1 values (1, 2), (2, 8), (3, 4), (4, 4), (5, 0);
+delete from t1;
+set system_versioning_alter_history= keep;
+alter ignore table t1 drop pk;
+drop table t1;
diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test
index 91bddb5aaeb..3ce87817e06 100644
--- a/mysql-test/suite/versioning/t/alter.test
+++ b/mysql-test/suite/versioning/t/alter.test
@@ -630,3 +630,25 @@ create or replace table t1 (x int);
 alter table t1 add column y timestamp(6) as row start;
 # cleanup
 drop table t1;
+
+
+--echo #
+--echo # MDEV-25327 Unexpected ER_DUP_ENTRY upon dropping PK column from system-versioned table
+--echo #
+create table t1 (pk int, a int, primary key (pk), key (a))
+with system versioning;
+insert into t1 values (1, 1), (2, 2);
+delete from t1;
+set system_versioning_alter_history= keep;
+alter table t1 drop pk;
+# cleanup
+drop table t1;
+
+create table t1 (pk int, a int, primary key (pk), key (a))
+with system versioning;
+insert into t1 values (1, 2), (2, 8), (3, 4), (4, 4), (5, 0);
+delete from t1;
+set system_versioning_alter_history= keep;
+alter ignore table t1 drop pk;
+# cleanup
+drop table t1;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 01812e039be..64336ed02af 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -8451,7 +8451,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       key_parts.push_back(new (thd->mem_root) Key_part_spec(&cfield->field_name,
                                                             key_part_length, true),
                           thd->mem_root);
-      if (cfield->invisible < INVISIBLE_SYSTEM)
+      if (!(cfield->invisible == INVISIBLE_SYSTEM && cfield->vers_sys_field()))
         user_keyparts= true;
     }
     if (table->s->tmp_table == NO_TMP_TABLE)
@@ -8463,6 +8463,14 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
         (void) delete_statistics_for_index(thd, table, key_info, TRUE);
     }
 
+    if (!user_keyparts && key_parts.elements)
+    {
+      /*
+        If we dropped all user key-parts we also drop implicit system fields.
+      */
+      key_parts.empty();
+    }
+
     if (key_parts.elements)
     {
       KEY_CREATE_INFO key_create_info;
@@ -8496,7 +8504,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
           key_type= Key::PRIMARY;
         else
           key_type= Key::UNIQUE;
-        if (dropped_key_part && user_keyparts)
+        if (dropped_key_part)
         {
           my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), dropped_key_part);
           goto err;