MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax

The existing syntax for renaming a column uses "ALTER TABLE ...  CHANGE"
command. This requires full column specification to rename the column.
This patch adds new syntax "ALTER TABLE ...  RENAME COLUMN", which do not
expect users to provide full column specification.  It means that the new
syntax would pick in-place or copy algorithm in the same way as that of
existing "ALTER TABLE ... CHANGE" command. The existing syntax
"ALTER TABLE ... CHANGE" will continue to work.

Syntax changes
==============

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

Following is a new <alter_specification> added:

 | RENAME COLUMN <oldname> TO <newname>

Where <oldname> and <newname> are identifiers for old name and new
name of the column.

Related to: WL#10761

10 files changed, 656 insertions, 34 deletions

diff --git a/mysql-test/main/alter_table,aria.rdiff b/mysql-test/main/alter_table,aria.rdiff
new file mode 100644
index 00000000000..40bbf95494c
--- /dev/null
+++ b/mysql-test/main/alter_table,aria.rdiff
@@ -0,0 +1,22 @@
+--- ./mysql-test/main/alter_table.result	2020-02-27 19:35:41.279992329 +0300
++++ ./mysql-test/main/alter_table,aria.reject	2020-02-27 19:37:13.251994491 +0300
+@@ -2716,8 +2716,7 @@
+ t3	CREATE TABLE `t3` (
+   `a` int(11) DEFAULT NULL,
+   `b` int(11) DEFAULT NULL,
+-  KEY `b` (`b`),
+-  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`bb`)
++  KEY `b` (`b`)
+ ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ ALTER TABLE t1 RENAME COLUMN bb TO b;
+ SHOW CREATE TABLE t1;
+@@ -2733,8 +2732,7 @@
+ t3	CREATE TABLE `t3` (
+   `a` int(11) DEFAULT NULL,
+   `c` int(11) DEFAULT NULL,
+-  KEY `b` (`c`),
+-  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`b`)
++  KEY `b` (`c`)
+ ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ CREATE TABLE t4(a int);
+ ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE;
diff --git a/mysql-test/main/alter_table,heap.rdiff b/mysql-test/main/alter_table,heap.rdiff
new file mode 100644
index 00000000000..ad6fd194cc9
--- /dev/null
+++ b/mysql-test/main/alter_table,heap.rdiff
@@ -0,0 +1,59 @@
+--- ./mysql-test/main/alter_table.result	2020-02-27 19:35:41.279992329 +0300
++++ ./mysql-test/main/alter_table,heap.reject	2020-02-27 19:39:44.175998039 +0300
+@@ -2716,8 +2716,7 @@
+ t3	CREATE TABLE `t3` (
+   `a` int(11) DEFAULT NULL,
+   `b` int(11) DEFAULT NULL,
+-  KEY `b` (`b`),
+-  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`bb`)
++  KEY `b` (`b`)
+ ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ ALTER TABLE t1 RENAME COLUMN bb TO b;
+ SHOW CREATE TABLE t1;
+@@ -2733,8 +2732,7 @@
+ t3	CREATE TABLE `t3` (
+   `a` int(11) DEFAULT NULL,
+   `c` int(11) DEFAULT NULL,
+-  KEY `b` (`c`),
+-  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`b`)
++  KEY `b` (`c`)
+ ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ CREATE TABLE t4(a int);
+ ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE;
+@@ -2774,36 +2772,6 @@
+ ERROR 42S22: Unknown column 'd' in 'field list'
+ DROP TRIGGER trg1;
+ DROP PROCEDURE sp1;
+-CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a)));
+-INSERT INTO t_gen(a) VALUES(4);
+-SELECT * FROM t_gen;
+-a	b
+-4	2
+-SHOW CREATE TABLE t_gen;
+-Table	Create Table
+-t_gen	CREATE TABLE `t_gen` (
+-  `a` int(11) DEFAULT NULL,
+-  `b` double GENERATED ALWAYS AS (sqrt(`a`)) VIRTUAL
+-) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+-ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c));
+-SELECT * FROM t_gen;
+-c	b
+-4	2
+-SHOW CREATE TABLE t_gen;
+-Table	Create Table
+-t_gen	CREATE TABLE `t_gen` (
+-  `c` int(11) DEFAULT NULL,
+-  `b` double GENERATED ALWAYS AS (sqrt(`c`)) VIRTUAL
+-) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+-ALTER TABLE t_gen CHANGE COLUMN c x INT;
+-show create table t_gen;
+-Table	Create Table
+-t_gen	CREATE TABLE `t_gen` (
+-  `x` int(11) DEFAULT NULL,
+-  `b` double GENERATED ALWAYS AS (sqrt(`x`)) VIRTUAL
+-) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+-ALTER TABLE t_gen RENAME COLUMN x TO a;
+-DROP TABLE t_gen;
+ SHOW CREATE TABLE t1;
+ Table	Create Table
+ t1	CREATE TABLE `t1` (
diff --git a/mysql-test/main/alter_table.combinations b/mysql-test/main/alter_table.combinations
new file mode 100644
index 00000000000..824e0a3be04
--- /dev/null
+++ b/mysql-test/main/alter_table.combinations
@@ -0,0 +1,5 @@
+[innodb]
+
+[aria]
+
+[heap]
diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result
index 44973141c2d..8a2a9d21f35 100644
--- a/mysql-test/main/alter_table.result
+++ b/mysql-test/main/alter_table.result
@@ -2563,3 +2563,291 @@ drop view v1;
 #
 # End of 10.3 tests
 #
+#
+# MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax
+#
+SET @save_default_engine= @@DEFAULT_STORAGE_ENGINE;
+CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES(1,'abcd',1.234);
+CREATE TABLE t2(a INT, b VARCHAR(30), c FLOAT) ENGINE=MyIsam;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` float DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t2 VALUES(1,'abcd',1.234);
+ALTER TABLE t1 RENAME COLUMN a TO a;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 RENAME COLUMN a TO m;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `m` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+m	b	c
+1	abcd	1.234
+ALTER TABLE t1 RENAME COLUMN m TO x,
+RENAME COLUMN b TO y,
+RENAME COLUMN c TO z;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL,
+  `y` varchar(30) DEFAULT NULL,
+  `z` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+x	y	z
+1	abcd	1.234
+ALTER TABLE t2 RENAME COLUMN a TO d, RENAME COLUMN b TO e, RENAME COLUMN c to f;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `d` int(11) DEFAULT NULL,
+  `e` varchar(30) DEFAULT NULL,
+  `f` float DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t2;
+d	e	f
+1	abcd	1.234
+ALTER TABLE t1 CHANGE COLUMN x a INT, RENAME COLUMN y TO b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `z` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 CHANGE COLUMN z c DOUBLE, RENAME COLUMN b to b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(30) DEFAULT NULL,
+  `c` double DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 CHANGE COLUMN a b int, RENAME COLUMN b TO c, CHANGE COLUMN c d FLOAT;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(30) DEFAULT NULL,
+  `d` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 ADD COLUMN zz INT, RENAME COLUMN d TO f;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(30) DEFAULT NULL,
+  `f` float DEFAULT NULL,
+  `zz` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 DROP COLUMN zz, RENAME COLUMN c TO zz;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `zz` varchar(30) DEFAULT NULL,
+  `f` float DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 RENAME COLUMN zz to c, DROP COLUMN f;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(30) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 ADD COLUMN d INT DEFAULT 5, RENAME COLUMN c TO b, DROP COLUMN b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `b` varchar(30) DEFAULT NULL,
+  `d` int(11) DEFAULT 5
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 RENAME COLUMN b TO d, RENAME COLUMN d TO b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 ADD KEY(b);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5,
+  KEY `b` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 RENAME COLUMN b TO bb;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `bb` int(11) DEFAULT 5,
+  KEY `b` (`bb`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+d	bb
+abcd	5
+CREATE TABLE t3(a int, b int, KEY(b));
+ALTER TABLE t3 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(bb);
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  KEY `b` (`b`),
+  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`bb`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 RENAME COLUMN bb TO b;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5,
+  KEY `b` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t3 RENAME COLUMN b TO c;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `a` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  KEY `b` (`c`),
+  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+CREATE TABLE t4(a int);
+ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `aa` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t4 RENAME COLUMN aa TO a, ALGORITHM = COPY;
+SHOW CREATE TABLE t4;
+Table	Create Table
+t4	CREATE TABLE `t4` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+DROP TABLE t4;
+CREATE VIEW v1 AS SELECT d,e,f FROM t2;
+CREATE TRIGGER trg1 BEFORE UPDATE on t2 FOR EACH ROW SET NEW.d=OLD.d + 10;
+CREATE PROCEDURE sp1() INSERT INTO t2(d) VALUES(10);
+ALTER TABLE t2 RENAME COLUMN d TO g;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `g` int(11) DEFAULT NULL,
+  `e` varchar(30) DEFAULT NULL,
+  `f` float DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t2`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`f` AS `f` from `t2`	koi8r	koi8r_general_ci
+Warnings:
+Warning	1356	View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+SELECT * FROM v1;
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+UPDATE t2 SET f = f + 10;
+ERROR 42S22: Unknown column 'd' in 'OLD'
+CALL sp1();
+ERROR 42S22: Unknown column 'd' in 'field list'
+DROP TRIGGER trg1;
+DROP PROCEDURE sp1;
+CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a)));
+INSERT INTO t_gen(a) VALUES(4);
+SELECT * FROM t_gen;
+a	b
+4	2
+SHOW CREATE TABLE t_gen;
+Table	Create Table
+t_gen	CREATE TABLE `t_gen` (
+  `a` int(11) DEFAULT NULL,
+  `b` double GENERATED ALWAYS AS (sqrt(`a`)) VIRTUAL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c));
+SELECT * FROM t_gen;
+c	b
+4	2
+SHOW CREATE TABLE t_gen;
+Table	Create Table
+t_gen	CREATE TABLE `t_gen` (
+  `c` int(11) DEFAULT NULL,
+  `b` double GENERATED ALWAYS AS (sqrt(`c`)) VIRTUAL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t_gen CHANGE COLUMN c x INT;
+show create table t_gen;
+Table	Create Table
+t_gen	CREATE TABLE `t_gen` (
+  `x` int(11) DEFAULT NULL,
+  `b` double GENERATED ALWAYS AS (sqrt(`x`)) VIRTUAL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t_gen RENAME COLUMN x TO a;
+DROP TABLE t_gen;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5,
+  KEY `b` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+ALTER TABLE t1 RENAME COLUMN b z;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'z' at line 1
+ALTER TABLE t1 RENAME COLUMN FROM b TO z;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM b TO z' at line 1
+ALTER TABLE t1 RENAME COLUMN b TO 1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1
+ALTER TABLE t1 RENAME COLUMN b TO e, RENAME COLUMN c TO e;
+ERROR 42S22: Unknown column 'c' in 't1'
+ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN b TO z;
+ERROR 42S21: Duplicate column name 'z'
+ALTER TABLE t1 DROP COLUMN b, RENAME COLUMN b TO z;
+ERROR 42S22: Unknown column 'b' in 't1'
+ALTER TABLE t1 RENAME COLUMN b TO b, RENAME COLUMN b TO b;
+ERROR 42S22: Unknown column 'b' in 't1'
+ALTER TABLE t1 RENAME COLUMN b TO c3, DROP COLUMN c3;
+ERROR 42000: Can't DROP COLUMN `c3`; check that it exists
+ALTER TABLE t1 ADD COLUMN z INT, CHANGE COLUMN z y INT, DROP COLUMN y;
+ERROR 42S22: Unknown column 'z' in 't1'
+ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN z TO y, DROP COLUMN y;
+ERROR 42S22: Unknown column 'z' in 't1'
+ALTER TABLE t1 RENAME COLUMN b TO `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn`;
+ERROR 42000: Incorrect column name 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn'
+ALTER TABLE t1 CHANGE b `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn` int;
+ERROR 42000: Identifier name 'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn' is too long
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` varchar(30) DEFAULT NULL,
+  `b` int(11) DEFAULT 5,
+  KEY `b` (`b`)
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+d	b
+abcd	5
+DROP VIEW v1;
+DROP TABLE t3,t1,t2;
+SET DEFAULT_STORAGE_ENGINE= @save_default_engine;
+#
+# End of 10.5 tests
+#
diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test
index dc6983da38b..ccd2ee953b1 100644
--- a/mysql-test/main/alter_table.test
+++ b/mysql-test/main/alter_table.test
@@ -2076,3 +2076,203 @@ drop view v1;
 --echo #
 --echo # End of 10.3 tests
 --echo #
+
+--echo #
+--echo # MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax
+--echo #
+SET @save_default_engine= @@DEFAULT_STORAGE_ENGINE;
+--disable_query_log
+if ($MTR_COMBINATION_INNODB)
+{
+SET DEFAULT_STORAGE_ENGINE= INNODB;
+}
+if ($MTR_COMBINATION_ARIA)
+{
+SET DEFAULT_STORAGE_ENGINE= ARIA;
+}
+if ($MTR_COMBINATION_HEAP)
+{
+SET DEFAULT_STORAGE_ENGINE= MEMORY;
+}
+--enable_query_log
+let $default_engine= `select @@default_storage_engine`;
+
+CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT);
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES(1,'abcd',1.234);
+CREATE TABLE t2(a INT, b VARCHAR(30), c FLOAT) ENGINE=MyIsam;
+SHOW CREATE TABLE t2;
+INSERT INTO t2 VALUES(1,'abcd',1.234);
+
+# Rename one column
+ALTER TABLE t1 RENAME COLUMN a TO a;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 RENAME COLUMN a TO m;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+# Rename multiple column
+ALTER TABLE t1 RENAME COLUMN m TO x,
+               RENAME COLUMN b TO y,
+               RENAME COLUMN c TO z;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+# Rename multiple columns with MyIsam Engine
+ALTER TABLE t2 RENAME COLUMN a TO d, RENAME COLUMN b TO e, RENAME COLUMN c to f;
+SHOW CREATE TABLE t2;
+SELECT * FROM t2;
+
+# Mix different ALTER operations with RENAME COLUMN
+ALTER TABLE t1 CHANGE COLUMN x a INT, RENAME COLUMN y TO b;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 CHANGE COLUMN z c DOUBLE, RENAME COLUMN b to b;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 CHANGE COLUMN a b int, RENAME COLUMN b TO c, CHANGE COLUMN c d FLOAT;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 ADD COLUMN zz INT, RENAME COLUMN d TO f;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 DROP COLUMN zz, RENAME COLUMN c TO zz;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 RENAME COLUMN zz to c, DROP COLUMN f;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 ADD COLUMN d INT DEFAULT 5, RENAME COLUMN c TO b, DROP COLUMN b;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+
+#Cyclic Rename
+ALTER TABLE t1 RENAME COLUMN b TO d, RENAME COLUMN d TO b;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+
+# Rename with Indexes
+ALTER TABLE t1 ADD KEY(b);
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 RENAME COLUMN b TO bb;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+# Rename with Foreign keys.
+CREATE TABLE t3(a int, b int, KEY(b));
+ALTER TABLE t3 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(bb);
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t3;
+ALTER TABLE t1 RENAME COLUMN bb TO b;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+ALTER TABLE t3 RENAME COLUMN b TO c;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t3;
+
+# Different Algorithm
+CREATE TABLE t4(a int);
+ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t4;
+ALTER TABLE t4 RENAME COLUMN aa TO a, ALGORITHM = COPY;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t4;
+DROP TABLE t4;
+
+# View, Trigger and SP
+CREATE VIEW v1 AS SELECT d,e,f FROM t2;
+CREATE TRIGGER trg1 BEFORE UPDATE on t2 FOR EACH ROW SET NEW.d=OLD.d + 10;
+CREATE PROCEDURE sp1() INSERT INTO t2(d) VALUES(10);
+ALTER TABLE t2 RENAME COLUMN d TO g;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t2;
+SHOW CREATE VIEW v1;
+--error ER_VIEW_INVALID
+SELECT * FROM v1;
+--error ER_BAD_FIELD_ERROR
+UPDATE t2 SET f = f + 10;
+--error ER_BAD_FIELD_ERROR
+CALL sp1();
+DROP TRIGGER trg1;
+DROP PROCEDURE sp1;
+
+# Generated Columns
+if (!$MTR_COMBINATION_HEAP)
+{
+CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a)));
+INSERT INTO t_gen(a) VALUES(4);
+SELECT * FROM t_gen;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t_gen;
+ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c));
+SELECT * FROM t_gen;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t_gen;
+#--error ER_DEPENDENT_BY_GENERATED_COLUMN
+ALTER TABLE t_gen CHANGE COLUMN c x INT;
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+show create table t_gen;
+#--error ER_DEPENDENT_BY_GENERATED_COLUMN
+ALTER TABLE t_gen RENAME COLUMN x TO a;
+DROP TABLE t_gen;
+}
+
+
+#
+# Negative tests
+#
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+
+# Invalid Syntax
+--error ER_PARSE_ERROR
+ALTER TABLE t1 RENAME COLUMN b z;
+--error ER_PARSE_ERROR
+ALTER TABLE t1 RENAME COLUMN FROM b TO z;
+--error ER_PARSE_ERROR
+ALTER TABLE t1 RENAME COLUMN b TO 1;
+
+# Duplicate column name
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t1 RENAME COLUMN b TO e, RENAME COLUMN c TO e;
+--error ER_DUP_FIELDNAME
+ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN b TO z;
+
+# Multiple operation on same column
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t1 DROP COLUMN b, RENAME COLUMN b TO z;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t1 RENAME COLUMN b TO b, RENAME COLUMN b TO b;
+--error ER_CANT_DROP_FIELD_OR_KEY
+ALTER TABLE t1 RENAME COLUMN b TO c3, DROP COLUMN c3;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t1 ADD COLUMN z INT, CHANGE COLUMN z y INT, DROP COLUMN y;
+--error ER_BAD_FIELD_ERROR
+ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN z TO y, DROP COLUMN y;
+
+# Invalid column name while renaming
+--error ER_WRONG_COLUMN_NAME
+ALTER TABLE t1 RENAME COLUMN b TO `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn`;
+# This error is different compared to ALTER TABLE ... CHANGE command
+--error ER_TOO_LONG_IDENT
+ALTER TABLE t1 CHANGE b `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn` int;
+
+--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" ""
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+
+# Cleanup
+DROP VIEW v1;
+DROP TABLE t3,t1,t2;
+SET DEFAULT_STORAGE_ENGINE= @save_default_engine;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 13b2659789d..6a7c0cd4d94 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -333,17 +333,25 @@ public:
 
 class Alter_column :public Sql_alloc {
 public:
-  const char *name;
+  LEX_CSTRING name;
+  LEX_CSTRING new_name;
   Virtual_column_info *default_value;
   bool alter_if_exists;
-  Alter_column(const char *par_name, Virtual_column_info *expr, bool par_exists)
-    :name(par_name), default_value(expr), alter_if_exists(par_exists) {}
+  Alter_column(LEX_CSTRING par_name, Virtual_column_info *expr, bool par_exists)
+    :name(par_name), new_name{NULL, 0}, default_value(expr), alter_if_exists(par_exists) {}
+  Alter_column(LEX_CSTRING par_name, LEX_CSTRING _new_name)
+    :name(par_name), new_name(_new_name), default_value(NULL), alter_if_exists(false) {}
   /**
     Used to make a clone of this object for ALTER/CREATE TABLE
     @sa comment for Key_part_spec::clone
   */
   Alter_column *clone(MEM_ROOT *mem_root) const
     { return new (mem_root) Alter_column(*this); }
+  bool is_rename()
+  {
+    DBUG_ASSERT(!new_name.str || !default_value);
+    return new_name.str;
+  }
 };
 
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index ea60d0cebd0..3ab1a46f95d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -511,7 +511,7 @@ void LEX::add_key_to_list(LEX_CSTRING *field_name,
 }
 
 
-bool LEX::add_alter_list(const char *name, Virtual_column_info *expr,
+bool LEX::add_alter_list(LEX_CSTRING name, Virtual_column_info *expr,
                          bool exists)
 {
   MEM_ROOT *mem_root= thd->mem_root;
@@ -524,6 +524,17 @@ bool LEX::add_alter_list(const char *name, Virtual_column_info *expr,
 }
 
 
+bool LEX::add_alter_list(LEX_CSTRING name, LEX_CSTRING new_name)
+{
+  Alter_column *ac= new (thd->mem_root) Alter_column(name, new_name);
+  if (unlikely(ac == NULL))
+    return true;
+  alter_info.alter_list.push_back(ac, thd->mem_root);
+  alter_info.flags|= ALTER_RENAME_COLUMN;
+  return false;
+}
+
+
 void LEX::init_last_field(Column_definition *field,
                           const LEX_CSTRING *field_name,
                           const CHARSET_INFO *cs)
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 2d08aa6c79b..c75833ae282 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -4320,8 +4320,9 @@ public:
     alter_info.check_constraint_list.push_back(constr);
     return false;
   }
-  bool add_alter_list(const char *par_name, Virtual_column_info *expr,
+  bool add_alter_list(LEX_CSTRING par_name, Virtual_column_info *expr,
                       bool par_exists);
+  bool add_alter_list(LEX_CSTRING name, LEX_CSTRING new_name);
   void set_command(enum_sql_command command,
                    DDL_options_st options)
   {
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 240f001f7de..b5cc07b1411 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -6167,7 +6167,7 @@ drop_create_field:
       for (f_ptr=table->field; *f_ptr; f_ptr++)
       {
         if (my_strcasecmp(system_charset_info,
-                           acol->name, (*f_ptr)->field_name.str) == 0)
+                           acol->name.str, (*f_ptr)->field_name.str) == 0)
           break;
       }
       if (unlikely(*f_ptr == NULL))
@@ -6175,7 +6175,7 @@ drop_create_field:
         push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
                             ER_BAD_FIELD_ERROR,
                             ER_THD(thd, ER_BAD_FIELD_ERROR),
-                            acol->name, table->s->table_name.str);
+                            acol->name.str, table->s->table_name.str);
         it.remove();
         if (alter_info->alter_list.is_empty())
         {
@@ -8138,24 +8138,6 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       continue;
     }
 
-    /*
-      If we are doing a rename of a column, update all references in virtual
-      column expressions, constraints and defaults to use the new column name
-    */
-    if (alter_info->flags & ALTER_RENAME_COLUMN)
-    {
-      if (field->vcol_info)
-        field->vcol_info->expr->walk(&Item::rename_fields_processor, 1,
-                                     &column_rename_param);
-      if (field->check_constraint)
-        field->check_constraint->expr->walk(&Item::rename_fields_processor, 1,
-                                            &column_rename_param);
-      if (field->default_value)
-        field->default_value->expr->walk(&Item::rename_fields_processor, 1,
-                                         &column_rename_param);
-      table->m_needs_reopen= 1; // because new column name is on thd->mem_root
-    }
-
     /* Check if field is changed */
     def_it.rewind();
     while ((def=def_it++))
@@ -8229,19 +8211,61 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       while ((alter=alter_it++))
       {
 	if (!my_strcasecmp(system_charset_info,field->field_name.str,
-                           alter->name))
+                           alter->name.str))
 	  break;
       }
       if (alter)
       {
-	if ((def->default_value= alter->default_value))
-          def->flags&= ~NO_DEFAULT_VALUE_FLAG;
+        if (alter->is_rename())
+        {
+          def->change= alter->name;
+          def->field_name= alter->new_name;
+          column_rename_param.fields.push_back(def);
+        }
         else
-          def->flags|= NO_DEFAULT_VALUE_FLAG;
+        {
+          if ((def->default_value= alter->default_value))
+            def->flags&= ~NO_DEFAULT_VALUE_FLAG;
+          else
+            def->flags|= NO_DEFAULT_VALUE_FLAG;
+        }
 	alter_it.remove();
       }
     }
   }
+
+  /*
+    If we are doing a rename of a column, update all references in virtual
+    column expressions, constraints and defaults to use the new column name
+  */
+  if (alter_info->flags & ALTER_RENAME_COLUMN)
+  {
+    alter_it.rewind();
+    Alter_column *alter;
+    while ((alter=alter_it++))
+    {
+      if (alter->is_rename())
+      {
+        my_error(ER_BAD_FIELD_ERROR, MYF(0), alter->name.str,
+                 table->s->table_name.str);
+        goto err;
+      }
+    }
+    for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++)
+    {
+      if (field->vcol_info)
+        field->vcol_info->expr->walk(&Item::rename_fields_processor, 1,
+                                    &column_rename_param);
+      if (field->check_constraint)
+        field->check_constraint->expr->walk(&Item::rename_fields_processor, 1,
+                                            &column_rename_param);
+      if (field->default_value)
+        field->default_value->expr->walk(&Item::rename_fields_processor, 1,
+                                        &column_rename_param);
+    }
+    table->m_needs_reopen= 1; // because new column name is on thd->mem_root
+  }
+
   dropped_sys_vers_fields &= VERS_SYSTEM_FIELD;
   if ((dropped_sys_vers_fields ||
        alter_info->flags & ALTER_DROP_PERIOD) &&
@@ -8357,7 +8381,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
     while ((alter=alter_it++))
     {
       if (!my_strcasecmp(system_charset_info,def->field_name.str,
-                         alter->name))
+                         alter->name.str))
         break;
     }
     if (alter)
@@ -8372,7 +8396,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
   if (unlikely(alter_info->alter_list.elements))
   {
     my_error(ER_BAD_FIELD_ERROR, MYF(0),
-             alter_info->alter_list.head()->name, table->s->table_name.str);
+             alter_info->alter_list.head()->name.str, table->s->table_name.str);
     goto err;
   }
   if (unlikely(!new_create_list.elements))
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index f24da3ed412..1d4a8a7e228 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -7705,13 +7705,12 @@ alter_list_item:
           }
         | ALTER opt_column opt_if_exists_table_element field_ident SET DEFAULT column_default_expr
           {
-            if (unlikely(Lex->add_alter_list($4.str, $7, $3)))
+            if (unlikely(Lex->add_alter_list($4, $7, $3)))
               MYSQL_YYABORT;
           }
         | ALTER opt_column opt_if_exists_table_element field_ident DROP DEFAULT
           {
-            if (unlikely(Lex->add_alter_list($4.str, (Virtual_column_info*) 0,
-                                             $3)))
+            if (unlikely(Lex->add_alter_list($4, (Virtual_column_info*) 0, $3)))
               MYSQL_YYABORT;
           }
         | RENAME opt_to table_ident
@@ -7728,6 +7727,11 @@ alter_list_item:
             lex->name= $3->table;
             lex->alter_info.flags|= ALTER_RENAME;
           }
+        | RENAME COLUMN_SYM ident TO_SYM ident
+          {
+            if (unlikely(Lex->add_alter_list($3, $5)))
+              MYSQL_YYABORT;
+          }
         | CONVERT_SYM TO_SYM charset charset_name_or_default opt_collate
           {
             if (!$4)