MDEV-25803 innodb.alter_candidate_key fix

There is a case when implicit primary key may be changed when removing
NOT NULL from the part of unique key. In that case we update
modified_primary_key which is then used to not skip key sorting.

According to is_candidate_key() there is no other cases when primary
kay may be changed implicitly.

4 files changed, 24 insertions, 10 deletions

diff --git a/sql/handler.h b/sql/handler.h
index e3e8e9fc5d9..e56ac9fab6e 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -426,6 +426,7 @@ enum enum_alter_inplace_result {
 #define HA_CREATE_TMP_ALTER     8U
 #define HA_LEX_CREATE_SEQUENCE  16U
 #define HA_VERSIONED_TABLE      32U
+#define HA_SKIP_KEY_SORT        64U
 
 #define HA_MAX_REC_LENGTH	65535
 
diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc
index 94003e328cc..59498d71bb1 100644
--- a/sql/sql_alter.cc
+++ b/sql/sql_alter.cc
@@ -256,7 +256,7 @@ Alter_table_ctx::Alter_table_ctx()
     db(null_clex_str), table_name(null_clex_str), alias(null_clex_str),
     new_db(null_clex_str), new_name(null_clex_str), new_alias(null_clex_str),
     fk_error_if_delete_row(false), fk_error_id(NULL),
-    fk_error_table(NULL)
+    fk_error_table(NULL), modified_primary_key(false)
 #ifdef DBUG_ASSERT_EXISTS
     , tmp_table(false)
 #endif
@@ -276,7 +276,7 @@ Alter_table_ctx::Alter_table_ctx(THD *thd, TABLE_LIST *table_list,
     tables_opened(tables_opened_arg),
     new_db(*new_db_arg), new_name(*new_name_arg),
     fk_error_if_delete_row(false), fk_error_id(NULL),
-    fk_error_table(NULL)
+    fk_error_table(NULL), modified_primary_key(false)
 #ifdef DBUG_ASSERT_EXISTS
     , tmp_table(false)
 #endif
diff --git a/sql/sql_alter.h b/sql/sql_alter.h
index 71920b84792..d9749592a4f 100644
--- a/sql/sql_alter.h
+++ b/sql/sql_alter.h
@@ -324,6 +324,7 @@ public:
   const char   *fk_error_id;
   /** Name of table for the above error. */
   const char   *fk_error_table;
+  bool         modified_primary_key;
 
 private:
   char new_filename[FN_REFLEN + 1];
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index fb3ac735c5d..52948968c95 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4215,8 +4215,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
     MyISAM/Aria cannot add index inplace so we are safe to qsort key info in
     that case. And if we don't add index then we do not need qsort at all.
   */
-  if (!(create_info->options & HA_CREATE_TMP_ALTER) ||
-      alter_info->flags & ALTER_ADD_INDEX)
+  if (!(create_info->options & HA_SKIP_KEY_SORT))
   {
     /*
       Sort keys in optimized order.
@@ -8035,7 +8034,6 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
   uint used_fields, dropped_sys_vers_fields= 0;
   KEY *key_info=table->key_info;
   bool rc= TRUE;
-  bool modified_primary_key= FALSE;
   bool vers_system_invisible= false;
   Create_field *def;
   Field **f_ptr,*field;
@@ -8420,6 +8418,12 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
     if (key_info->flags & HA_INVISIBLE_KEY)
       continue;
     const char *key_name= key_info->name.str;
+    const bool primary_key= table->s->primary_key == i;
+    const bool explicit_pk= primary_key &&
+                            !my_strcasecmp(system_charset_info, key_name,
+                                           primary_key_name);
+    const bool implicit_pk= primary_key && !explicit_pk;
+
     Alter_drop *drop;
     drop_it.rewind();
     while ((drop=drop_it++))
@@ -8433,7 +8437,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       if (table->s->tmp_table == NO_TMP_TABLE)
       {
         (void) delete_statistics_for_index(thd, table, key_info, FALSE);
-        if (i == table->s->primary_key)
+        if (primary_key)
 	{
           KEY *tab_key_info= table->key_info;
 	  for (uint j=0; j < table->s->keys; j++, tab_key_info++)
@@ -8478,13 +8482,19 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       }
       if (!cfield)
       {
-        if (table->s->primary_key == i)
-          modified_primary_key= TRUE;
+        if (primary_key)
+          alter_ctx->modified_primary_key= true;
         delete_index_stat= TRUE;
         if (!(kfield->flags & VERS_SYSTEM_FIELD))
           dropped_key_part= key_part_name;
 	continue;				// Field is removed
       }
+
+      DBUG_ASSERT(!primary_key || kfield->flags & NOT_NULL_FLAG);
+      if (implicit_pk && !alter_ctx->modified_primary_key &&
+          !(cfield->flags & NOT_NULL_FLAG))
+        alter_ctx->modified_primary_key= true;
+
       key_part_length= key_part->length;
       if (cfield->field)			// Not new field
       {
@@ -8533,7 +8543,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
     {
       if (delete_index_stat) 
         (void) delete_statistics_for_index(thd, table, key_info, FALSE);
-      else if (modified_primary_key &&
+      else if (alter_ctx->modified_primary_key &&
                key_info->user_defined_key_parts != key_info->ext_key_parts)
         (void) delete_statistics_for_index(thd, table, key_info, TRUE);
     }
@@ -8575,7 +8585,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
         key_type= Key::SPATIAL;
       else if (key_info->flags & HA_NOSAME)
       {
-        if (! my_strcasecmp(system_charset_info, key_name, primary_key_name))
+        if (explicit_pk)
           key_type= Key::PRIMARY;
         else
           key_type= Key::UNIQUE;
@@ -9941,6 +9951,8 @@ do_continue:;
 
   tmp_disable_binlog(thd);
   create_info->options|=HA_CREATE_TMP_ALTER;
+  if (!(alter_info->flags & ALTER_ADD_INDEX) && !alter_ctx.modified_primary_key)
+    create_info->options|= HA_SKIP_KEY_SORT;
   create_info->alias= alter_ctx.table_name;
   error= create_table_impl(thd,
                            &alter_ctx.db, &alter_ctx.table_name,