Bug #92727 Swapping column by ALTER TABLE RENAME COLUMN breaks STORED generated column
Submitted: 10 Oct 2018 6:48 Modified: 10 Oct 2018 7:17
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Verified 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

[10 Oct 2018 6:48] tsubasa tanaka
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)
[10 Oct 2018 7:04] Umesh Shastry
Hello Tanaka-San,

Thank you for the report and test case.

regards,
Umesh
[10 Oct 2018 7:16] tsubasa tanaka
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.

[10 Oct 2018 7:16] tsubasa tanaka
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.

[10 Oct 2018 7:17] tsubasa tanaka
Workaround is executing `OPTIMIZE TABLE` and generated column is re-calculated.