WL#10761: ALTER TABLE RENAME COLUMN

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

To rename a column, MySQL provides syntax:

ALTER TABLE <table_name> CHANGE ...

which requires re-specification of all the attributes of the column. 

Disadvantages of the above syntax :
- All the column information might not be available to the application trying to 
  do the rename. 
- There is a risk of accidental data type change in the above syntax which might 
  result in data loss.

The proposed syntax is :
ALTER TABLE ... RENAME COLUMN old_name TO new_name;

This worklog will do the parser changes and implement the syntax changes.
The new proposed syntax is expected to behave the same as  "ALTER TABLE ...
CHANGE" command used to rename column, without changing column attributes.
The old syntax "ALTER TABLE ... CHANGE" to rename the column will continue
to work.

Related Bugs: BUG#26949, BUG#32497, BUG#58006
FR1: "ALTER TABLE ... RENAME COLUMN <old_column_name> TO <new_column_name>"
     should be supported syntax.

FR2: ALTER TABLE ... RENAME COLUMN should not require table recreation
     (using INPLACE algorithm), but work even if table is recreated
     (using COPY algorithm) during ALTER command with multiple operations.

FR3: New syntax should have behavior same as "ALTER TABLE ... CHANGE" command
     to rename column.

FR4: Should work for all storage engines.

FR5: Should not break replication for 8.0 master to 8.0 slave, 5.7 master to 8.0 
     slave.

FR6: Should not break indexes or foreign key constraint which relies on column.

FR7: Should mark views that are based on old column name as invalid in DD.

NFR: No new functionalities/restrictions will be added to ALTER TABLE ...      
     RENAME COLUMN command than present with ALTER TABLE ... CHANGE command to 
     rename column.
ALTER TABLE ... RENAME COLUMN is a non-standard syntax extension.
(Also it is implemented the same way in the Oracle Database -
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2110476
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljrenamecolumnstatement.html)


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

Grammar for ALTER TABLE is adjusted to support new clause for renaming
columns. This is done by adding new alternative for <alter_list_item>
rule:

<alter_specification> ::= <add column clause> | .. | <rename key clause> |
                          <rename column clause> 

Where:

<rename column clause> ::= RENAME COLUMN <oldname> TO <newname>

<oldname> ::= <field_ident>
<newname> ::= <field_ident>

Example statements :
CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT);
ALTER TABLE t1 RENAME COLUMN a TO defg;
# Rename multiple column
ALTER TABLE t1 RENAME COLUMN defg TO ijkl,
               RENAME COLUMN b TO mno,
               RENAME COLUMN c TO pqr;

# Mix different ALTER operations
ALTER TABLE t1 CHANGE COLUMN ijkl a INT,
               RENAME COLUMN mno TO b, 
               RENAME COLUMN pqr to c;


Semantics
=========

<oldname> should be name of existing column in table being altered, which
is not dropped by the same alter table.

<newname> is new name for the column being renamed, should not coincide
with name of any existing column which is not dropped within the same
alter table or column added by the same alter table. 

If any of these preconditions fail, an appropriate error is emitted.

Effect on Replication
======================
- Replication from mysql-5.7 as master to either mysql-8.0 slave to mysql-5.7 
  slave should work.
- Cross version replication from mysql-8.0 master to mysql-5.7 slave will be 
  broken by new command. This is not a supported scenario.

Effect on View, Trigger, SP/SF
===============================
Renaming the column of a table on which a view is based on will make the view
invalid. This is existing behavior when ALTER TABLE ... CHANGE command is used
to rename a column.

If a trigger, stored procedure or stored function references the column being
renamed, RENAME command will succeed. The execution of trigger/SP/SF may later
fail when trying to access the column. This is consistent with when ALTER TABLE
CHANGE command is used to rename a column.

Effect on Generated Column
===========================
If a generation-clause of a generated column references the column, an attempt
to rename it will generate an error. Generation clause of the generated column
should be changed with CHANGE clause while renaming the column it is based on.
This is consistent with when ALTER TABLE ... CHANGE command is used to rename a
column.

Effect on Indexes and Foreign Keys
==================================
There should be no effect on indexes and foreign keys when column is renamed.
No index rebuild should be done by storage engines. Foreign key constraint
should keep working after renaming the column.

There is no behavior change here. This is existing behavior when ALTER TABLE ... 
CHANGE command is used to rename a column.

Scope of ALTER TABLE RENAME COLUMN command
==========================================
RENAME COLUMN command is for the sole purpose of renaming a column. Its scope of
very limited compared to ALTER TABLE ... CHANGE command which can change all the
attributes of the column.

RENAME COLUMN command should work for both INPLACE and COPY algorithms.
Documentation Notes:
====================
1. word COLUMN is not optional for ALTER TABLE RENAME COLUMN command.
   ALTER TABLE RENAME is existing syntax to rename the table.
2. ALTER command uses existing schema of table to find the mentioned column 
   names. For example :

   CREATE TABLE t1(a int);
   ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO c;

   Here, ALTER will fail as there is no existing column with name 'b'.
1. Class added to store <old_name,new_name> while parsing.

  /**
    Class to represent RENAME COLUMN clauses in  ALTER TABLE statement.
  */

  class Alter_rename_column : public Sql_alloc
  {
  public:
    const char *old_name;
    const char *new_name;

    Alter_rename_column(const char *old_name_arg, const char *new_name_arg)
      : old_name(old_name_arg), new_name(new_name_arg)
    { }
  };

2. Reuse Alter_info::ALTER_CHANGE_COLUMN flag to be set by parser.