From 421716391b8bafe9af853b1ee3f83d521b69db6e Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sat, 14 Oct 2017 15:03:43 +0200 Subject: MDEV-13912 Can't refer the same column twice in one ALTER TABLE backport ce6c0e584e3 MDEV-8960: Can't refer the same column twice in one ALTER TABLE Problem was that if column was created in alter table when it was refered again it was not tried to find from list of current columns. mysql_prepare_alter_table: There is two cases (1) If alter table adds a new column and then later alter changes the field definition, there was no check from list of new columns, instead an incorrect error was given. (2) If alter table adds a new column and then later alter changes the default, there was no check from list of new columns, instead an incorrect error was given. --- mysql-test/r/alter_table.result | 52 +++++++++++++++++++++++++++++++++++++++++ mysql-test/t/alter_table.test | 41 ++++++++++++++++++++++++++++++++ sql/sql_table.cc | 45 ++++++++++++++++++++++++++++++++--- 3 files changed, 135 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 624cc7afa98..8e61031f8ac 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1340,3 +1340,55 @@ rename table t2 to t1; execute stmt1; deallocate prepare stmt1; drop table t2; +# +# MDEV-8960 Can't refer the same column twice in one ALTER TABLE +# +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` SET DEFAULT 2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL DEFAULT '2' +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` int(11) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; +CREATE TABLE t1 ( +`a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT, +MODIFY COLUMN `consultant_id` BIGINT; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `consultant_id` bigint(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +DROP TABLE t1; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index eade7ba721e..ee9616e233d 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1231,3 +1231,44 @@ execute stmt1; deallocate prepare stmt1; drop table t2; +--echo # +--echo # MDEV-8960 Can't refer the same column twice in one ALTER TABLE +--echo # + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` DROP DEFAULT; + +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, +ALTER COLUMN `consultant_id` SET DEFAULT 2; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + `a` int(11) DEFAULT NULL +) DEFAULT CHARSET=utf8; + +ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, +ALTER COLUMN `consultant_id` DROP DEFAULT, +MODIFY COLUMN `consultant_id` BIGINT; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index e6490876352..19093d9b2ca 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -5739,9 +5739,25 @@ mysql_prepare_alter_table(THD *thd, TABLE *table, { if (def->change && ! def->field) { - my_error(ER_BAD_FIELD_ERROR, MYF(0), def->change, - table->s->table_name.str); - goto err; + /* + Check if there is modify for newly added field. + */ + Create_field *find; + find_it.rewind(); + while((find=find_it++)) + { + if (!my_strcasecmp(system_charset_info,find->field_name, def->field_name)) + break; + } + + if (find && !find->field) + find_it.remove(); + else + { + my_error(ER_BAD_FIELD_ERROR, MYF(0), def->change, + table->s->table_name.str); + goto err; + } } /* Check that the DATE/DATETIME not null field we are going to add is @@ -5793,6 +5809,29 @@ mysql_prepare_alter_table(THD *thd, TABLE *table, */ alter_info->change_level= ALTER_TABLE_DATA_CHANGED; } + /* + Check if there is alter for newly added field. + */ + alter_it.rewind(); + Alter_column *alter; + while ((alter=alter_it++)) + { + if (!my_strcasecmp(system_charset_info,def->field_name, alter->name)) + break; + } + if (alter) + { + if (def->sql_type == MYSQL_TYPE_BLOB) + { + my_error(ER_BLOB_CANT_HAVE_DEFAULT, MYF(0), def->change); + goto err; + } + if ((def->def=alter->def)) // Use new default + def->flags&= ~NO_DEFAULT_VALUE_FLAG; + else + def->flags|= NO_DEFAULT_VALUE_FLAG; + alter_it.remove(); + } } if (alter_info->alter_list.elements) { -- cgit v1.2.1