MDEV-8960: Can't refer the same column twice in one ALTER TABLEbb-10.0-MDEV-8960

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.

3 files changed, 136 insertions, 3 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index cac4c477b5a..5f5e813a302 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -2112,3 +2112,55 @@ t1	CREATE TABLE `t1` (
   `b` int(11) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
 DROP TABLE t1;
+#
+# 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 3670e871bb0..e252b606860 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1767,3 +1767,45 @@ SHOW CREATE TABLE t1;
 ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+--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 2eff8fd5e2f..3ba3ec6847e 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -7503,9 +7503,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
@@ -7571,6 +7587,29 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
         find_it.after(def);			// Put column after this
       }
     }
+    /*
+      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)
   {