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

[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] MySQL Verification Team
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.
[17 Apr 2019 18:03] Paul DuBois
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.