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

1 files changed, 288 insertions, 0 deletions

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
+#