[Commits] f90be8dab06: MDEV-25078: ALTER INDEX is inconsistent with ADD/DROP/RENAME index

psergey sergey at mariadb.com
Fri May 14 15:42:05 EEST 2021


revision-id: f90be8dab063dd730a3a8fa3c2ac51ee8549a87e (mariadb-10.6.0-43-gf90be8dab06)
parent(s): c67d69abb9b6d05a1c837dc92e1faad770741f55
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-05-14 15:42:05 +0300
message:

MDEV-25078: ALTER INDEX is inconsistent with ADD/DROP/RENAME index

Support IF EXISTS in the command that alter index visibility:

  ALTER TABLE ALTER (KEY|INDEX) [IF EXISTS] index_name [NOT] IGNORED

---
 mysql-test/main/ignored_index.result | 49 ++++++++++++++++++++++++++++++++++++
 mysql-test/main/ignored_index.test   | 20 +++++++++++++++
 sql/sql_class.h                      |  6 +++--
 sql/sql_table.cc                     | 28 ++++++++++++++++++++-
 sql/sql_yacc.yy                      |  4 +--
 5 files changed, 102 insertions(+), 5 deletions(-)

diff --git a/mysql-test/main/ignored_index.result b/mysql-test/main/ignored_index.result
index 733e44a3afa..84263dddd4d 100644
--- a/mysql-test/main/ignored_index.result
+++ b/mysql-test/main/ignored_index.result
@@ -479,3 +479,52 @@ t1	CREATE TABLE `t1` (
   KEY `a` (`a`) IGNORED
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
+#
+#  MDEV-25078, part #2: allow IF EXISTS
+#
+create table t1 (a int, b int, c int, key(a), key(b), key(c));
+alter table t1 alter key if exists no_such_key ignored;
+Warnings:
+Note	1176	Key 'no_such_key' doesn't exist in table 't1'
+alter table t1 alter key if exists a ignored;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  KEY `a` (`a`) IGNORED,
+  KEY `b` (`b`),
+  KEY `c` (`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t1
+alter key if exists no_such_key ignored,
+alter key if exists c ignored ;
+Warnings:
+Note	1176	Key 'no_such_key' doesn't exist in table 't1'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  KEY `a` (`a`) IGNORED,
+  KEY `b` (`b`),
+  KEY `c` (`c`) IGNORED
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t1 
+alter key if exists no_such_key not ignored, 
+alter key if exists c not ignored ;
+Warnings:
+Note	1176	Key 'no_such_key' doesn't exist in table 't1'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  KEY `a` (`a`) IGNORED,
+  KEY `b` (`b`),
+  KEY `c` (`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
diff --git a/mysql-test/main/ignored_index.test b/mysql-test/main/ignored_index.test
index a1084f3eb9c..a3d46fe6046 100644
--- a/mysql-test/main/ignored_index.test
+++ b/mysql-test/main/ignored_index.test
@@ -442,3 +442,23 @@ CREATE TABLE t1 (a INT, KEY (a));
 ALTER TABLE t1 ALTER KEY a IGNORED;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+--echo #
+--echo #  MDEV-25078, part #2: allow IF EXISTS
+--echo #
+
+create table t1 (a int, b int, c int, key(a), key(b), key(c));
+alter table t1 alter key if exists no_such_key ignored;
+alter table t1 alter key if exists a ignored;
+show create table t1;
+alter table t1
+  alter key if exists no_such_key ignored,
+  alter key if exists c ignored ;
+show create table t1;
+alter table t1 
+  alter key if exists no_such_key not ignored, 
+  alter key if exists c not ignored ;
+show create table t1;
+drop table t1;
+
+
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 031fff71ec8..09faeb10ab4 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -386,13 +386,14 @@ class Alter_rename_key : public Sql_alloc
 class Alter_index_ignorability: public Sql_alloc
 {
 public:
-  Alter_index_ignorability(const char *name, bool is_ignored) :
-    m_name(name), m_is_ignored(is_ignored)
+  Alter_index_ignorability(const char *name, bool is_ignored, bool if_exists) :
+    m_name(name), m_is_ignored(is_ignored), m_if_exists(if_exists)
   {
     assert(name != NULL);
   }
 
   const char *name() const { return m_name; }
+  bool if_exists() const { return m_if_exists; }
 
   /* The ignorability after the operation is performed. */
   bool is_ignored() const { return m_is_ignored; }
@@ -402,6 +403,7 @@ class Alter_index_ignorability: public Sql_alloc
 private:
   const char *m_name;
   bool m_is_ignored;
+  bool m_if_exists;
 };
 
 
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 209c799dc59..e8fde33191f 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -6695,7 +6695,33 @@ handle_if_exists_options(THD *thd, TABLE *table, Alter_info *alter_info,
       rename_key_it.remove();
     }
   }
-
+  /* Handle ALTER KEY IF EXISTS. */
+  {
+    List_iterator<Alter_index_ignorability> ignor_it(alter_info->alter_index_ignorability_list);
+    Alter_index_ignorability *aii;
+    while ((aii= ignor_it++))
+    {
+      if (!aii->if_exists())
+        continue;
+      bool exists= false;
+      for (uint n_key= 0; n_key < table->s->keys; n_key++)
+      {
+        if (my_strcasecmp(system_charset_info, aii->name(),
+                          table->key_info[n_key].name.str) == 0)
+        {
+          exists= true;
+          break;
+        }
+      }
+      if (exists)
+        continue;
+      push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+                          ER_KEY_DOES_NOT_EXISTS,
+                          ER_THD(thd, ER_KEY_DOES_NOT_EXISTS),
+                          aii->name(), table->s->table_name.str);
+      ignor_it.remove();
+    }
+  }
   /* ALTER TABLE ADD KEY IF NOT EXISTS */
   /* ALTER TABLE ADD FOREIGN KEY IF NOT EXISTS */
   {
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 92769dc01f1..c585ff4403c 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -7824,11 +7824,11 @@ alter_list_item:
             if (unlikely(Lex->add_alter_list($4, $7, $3)))
               MYSQL_YYABORT;
           }
-        | ALTER key_or_index ident ignorability
+        | ALTER key_or_index opt_if_exists_table_element ident ignorability
           {
             LEX *lex= Lex;
             Alter_index_ignorability *ac= new (thd->mem_root)
-                                        Alter_index_ignorability($3.str, $4);
+                                        Alter_index_ignorability($4.str, $5, $3);
             if (ac == NULL)
               MYSQL_YYABORT;
             lex->alter_info.alter_index_ignorability_list.push_back(ac);


More information about the commits mailing list