Bug #80299 DEFAULT is not deterministic and should not be allowed in generated columns

• Submitted: 8 Feb 2016 9:04
• Modified: 25 Mar 2016 14:02
• Reporter: Roy Lyseng
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.11
• OS: Any
• CPU Architecture: Any

[8 Feb 2016 9:04] Roy Lyseng

Description:
Since one may change the DEFAULT value for a column using ALTER TABLE, the DEFAULT function may not be considered deterministic, and hence it should not be allowed in the definition of generated columns.

How to repeat:
create table tzz(a int default 5, gc1 int as (a+default(a)) virtual, gc2 int as (a+default(a)) stored);

insert into tzz(a) values 1;
select * from tzz;
+------+------+------+
| a    | gc1  | gc2  |
+------+------+------+
|    1 |    6 |    6 |
+------+------+------+
1 row in set (0,01 sec)
alter table tzz modify column a int default 6;
select * from tzz;
+------+------+------+
| a    | gc1  | gc2  |
+------+------+------+
|    1 |    7 |    6 |
+------+------+------+
1 row in set (0,00 sec) 

Suggested fix:
Disallow DEFAULT in generated columns.

[8 Feb 2016 9:38] MySQL Verification Team

Hello Roy,

Thank you for the report.

Thanks,
Umesh

[25 Mar 2016 14:02] Paul DuBois

Posted by developer:
 
Noted in 5.7.13, 5.8.0 changelogs.

It is possible to use ALTER TABLE to change the default value of a
column col_name, which may change the value of a generated column
expression that refers to the column using DEFAULT(col_name). For
this reason, ALTER TABLE operations that change the default value of
col_name now cause a table rebuild if any generated column expression
uses DEFAULT(col_name).

[29 Mar 2016 14:33] Paul DuBois

Revised changelog entry:

It is possible to use ALTER TABLE to change the default value of a
column col_name, which may change the value of a generated column
expression that refers to the column using DEFAULT(col_name). For
this reason, ALTER TABLE operations that change the definition of a
column now cause a table rebuild if any generated column expression
uses DEFAULT().