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);