MySQL Bugs: #92727: Swapping column by ALTER TABLE RENAME COLUMN breaks STORED generated column

Bug #92727	Swapping column by ALTER TABLE RENAME COLUMN breaks STORED generated column
Submitted:	10 Oct 2018 6:48	Modified:	17 Apr 2019 18:04
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.12	OS:	CentOS (7.5)
Assigned to:		CPU Architecture:	x86

Description:
Generated column does NOT following column-renaming(this is described behavior in the doc).

> For columns renamed by CHANGE or RENAME COLUMN, MySQL does not automatically rename these references to the renamed column:
> 
> Generated column and partition expressions that refer to the renamed column. You must use CHANGE to redefine such expressions in the same ALTER TABLE statement as the one that renames the column.

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

But in a specific situation, STORED generated column keeps calculated by old-data.
See "How to repeat".

How to repeat:
mysql80 25> CREATE TABLE t1 (a int, b int, gcol int AS (a + 1) STORED);
Query OK, 0 rows affected (0.11 sec)

mysql80 25> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `gcol` int(11) GENERATED ALWAYS AS ((`a` + 1)) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.01 sec)

mysql80 25> INSERT INTO t1 (a, b) VALUES (1, 2);
Query OK, 1 row affected (0.09 sec)

mysql80 25> SELECT * FROM t1;
+------+------+------+
| a    | b    | gcol |
+------+------+------+
|    1 |    2 |    2 |
+------+------+------+
1 row in set (0.00 sec)

mysql80 25> ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 25> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `b` int(11) DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `gcol` int(11) GENERATED ALWAYS AS ((`a` + 1)) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)

mysql80 25> SELECT * FROM t1;
+------+------+------+
| b    | a    | gcol |
+------+------+------+
|    1 |    2 |    2 |  <--- b(old "a") + 1, not a(current "a") + 1
+------+------+------+
1 row in set (0.00 sec)

mysql80 25> INSERT INTO t1 (a, b) VALUES (1, 2);
Query OK, 1 row affected (0.08 sec)

mysql80 25> SELECT * FROM t1;
+------+------+------+
| b    | a    | gcol |
+------+------+------+
|    1 |    2 |    2 |  <--- b(old "a") + 1, not a(current "a") + 1
|    2 |    1 |    2 |  <-- a(current "a") + 1
+------+------+------+
2 rows in set (0.00 sec)

Hello Tanaka-San,

Thank you for the report and test case.

regards,
Umesh

mtr (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug92727.test (application/octet-stream, text), 356 bytes.

mtr result (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug92727.result (application/octet-stream, text), 280 bytes.

Workaround is executing `OPTIMIZE TABLE` and generated column is re-calculated.

Posted by developer:
 
Fixed in 8.0.17.

Stored generated column values and indexes on virtual generated
columns were not correctly updated after columns on which these
generated columns depended were swapped using ALTER TABLE with RENAME
COLUMN or CHANGE COLUMN.

Renaming of base columns for generated columns, generated defaults,
and functional indexes is now prohibited unless the same ALTER TABLE
statement satisfies one of these conditions:

* The statement removes the generated column, generated default, or
  functional index.

* The statement updates the dependent expression in question. This enables
  supporting existing scenarios in which generation expressions are
  updated to follow base column renaming.

Restrictions on dropping columns on which generated columns,
generated defaults, or functional indexes depend were relaxed in a
similar way.