Bug #88623 Modifying virtually generated column is not online
Submitted: 23 Nov 2017 12:03 Modified: 24 Nov 2017 11:13
Reporter: Karthik Appigatla (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8, 5.7.20 OS:Any
Assigned to: CPU Architecture:Any

[23 Nov 2017 12:03] Karthik Appigatla 
Description:
Adding or dropping a virtual generated column modifies only the metadata. However modifying the virtual column is rebuilding the table.

How to repeat:
CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert data into employees table.

mysql>  ALTER TABLE employees ADD COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, '-', last_name)) VIRTUAL;
Query OK, 300026 rows affected (4.37 sec)
Records: 300026  Duplicates: 0  Warnings: 0
[23 Nov 2017 14:50] Peter Laursen 
MariaDB does not rebuild table with last statement

1 queries executed, 1 success, 0 errors, 0 warnings

Query: ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, '-', last_name)) VIRTUAL

0 row(s) affected

Execution Time : 0.013 sec
Transfer Time  : 1.142 sec
Total Time     : 1.155 sec

-- Peter
-- not a MySQL/Oracle (nor MariaDB) person
[24 Nov 2017 11:13] MySQL Verification Team 
Hello Karthik,

Thank you for the report.
Per documentation "For non-partitioned tables, ADD COLUMN and DROP COLUMN are in-place operations for virtual columns. However, adding or dropping a virtual column cannot be performed in place in combination with other ALTER TABLE operations" - https://dev.mysql.com/doc/refman/5.7/en/alter-table-generated-columns.html
I'm not able to locate the reason for the restrictions on this, if this is an expected then it might end up as doc request.

## 5.7.20 (For employee schema - https://dev.mysql.com/doc/employee/en/employees-installation.html)

mysql> ALTER TABLE employees ADD COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, '-', last_name)) VIRTUAL;
Query OK, 300024 rows affected (2.82 sec)
Records: 300024  Duplicates: 0  Warnings: 0

-- Attempted with ALGORITHM= <>

mysql> ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL, ALGORITHM= INPLACE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, '-', last_name)) VIRTUAL, ALGORITHM= INPLACE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
mysql>
mysql> ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(40) AS (CONCAT(first_name, '-', last_name)) VIRTUAL, ALGORITHM= COPY;
Query OK, 300024 rows affected (2.73 sec)
Records: 300024  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees DROP COLUMN full_name, ALGORITHM= INPLACE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thanks,
Umesh