MDEV-17599 ALTER TABLE DROP CONSTRAINT does not work for foreign keys.

The list of table constraints doesn't include foreign keys and uniques.
So we replace DROP CONSTRAINT with DROP [FOREIGN] KEY in this case.

2 files changed, 65 insertions, 2 deletions

diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index f27b61092d2..1227f144348 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -8979,6 +8979,64 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
 
   THD_STAGE_INFO(thd, stage_setup);
 
+  if (alter_info->flags & Alter_info::ALTER_DROP_CHECK_CONSTRAINT)
+  {
+    /*
+      ALTER TABLE DROP CONSTRAINT
+      should be replaced with ... DROP [FOREIGN] KEY
+      if the constraint is the FOREIGN KEY or UNIQUE one.
+    */
+
+    List_iterator<Alter_drop> drop_it(alter_info->drop_list);
+    Alter_drop *drop;
+    List <FOREIGN_KEY_INFO> fk_child_key_list;
+    table->file->get_foreign_key_list(thd, &fk_child_key_list);
+
+    alter_info->flags&= ~Alter_info::ALTER_DROP_CHECK_CONSTRAINT;
+
+    while ((drop= drop_it++))
+    {
+      if (drop->type == Alter_drop::CHECK_CONSTRAINT)
+      {
+        {
+          /* Test if there is a FOREIGN KEY with this name. */
+          FOREIGN_KEY_INFO *f_key;
+          List_iterator<FOREIGN_KEY_INFO> fk_key_it(fk_child_key_list);
+
+          while ((f_key= fk_key_it++))
+          {
+            if (my_strcasecmp(system_charset_info, f_key->foreign_id->str,
+                  drop->name) == 0)
+            {
+              drop->type= Alter_drop::FOREIGN_KEY;
+              alter_info->flags|= Alter_info::DROP_FOREIGN_KEY;
+              goto do_continue;
+            }
+          }
+        }
+
+        {
+          /* Test if there is an UNIQUE with this name. */
+          uint n_key;
+
+          for (n_key=0; n_key < table->s->keys; n_key++)
+          {
+            if ((table->key_info[n_key].flags & HA_NOSAME) &&
+                my_strcasecmp(system_charset_info,
+                  drop->name, table->key_info[n_key].name) == 0)
+            {
+              drop->type= Alter_drop::KEY;
+              alter_info->flags|= Alter_info::ALTER_DROP_INDEX;
+              goto do_continue;
+            }
+          }
+        }
+      }
+      alter_info->flags|= Alter_info::ALTER_DROP_CHECK_CONSTRAINT;
+do_continue:;
+    }
+  }
+
   handle_if_exists_options(thd, table, alter_info);
 
   /*
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 480c2e9bacb..14d084e7022 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1741,7 +1741,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
         IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
         NCHAR_STRING opt_component key_cache_name
         sp_opt_label BIN_NUM label_ident TEXT_STRING_filesystem ident_or_empty
-        opt_constraint constraint opt_ident ident_table_alias
+        opt_constraint opt_constraint_no_id constraint opt_ident ident_table_alias
 
 %type <lex_str_ptr>
         opt_table_alias
@@ -6123,6 +6123,11 @@ check_constraint:
           }
         ;
 
+opt_constraint_no_id:
+          /* Empty */  {}
+        | CONSTRAINT   {}
+        ;
+
 opt_constraint:
           /* empty */ { $$= null_lex_str; }
         | constraint { $$= $1; }
@@ -7653,7 +7658,7 @@ alter_list_item:
             lex->alter_info.drop_list.push_back(ad, thd->mem_root);
             lex->alter_info.flags|= Alter_info::DROP_FOREIGN_KEY;
           }
-        | DROP PRIMARY_SYM KEY_SYM
+        | DROP opt_constraint_no_id PRIMARY_SYM KEY_SYM
           {
             LEX *lex=Lex;
             Alter_drop *ad= (new (thd->mem_root)