Bug #80304 generated columns don't work with foreign key actions
Submitted: 8 Feb 2016 15:32 Modified: 16 May 2016 14:55
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7, 5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[8 Feb 2016 15:32] Guilhem Bichot 
Description:
One testcase, by Peter Gulutzan, can be found in
http://ocelot.ca/blog/blog/2016/02/04/generated-columns-in-mariadb-and-mysql/
Search there for
"MySQL lacks some restrictions about foreign keys, eh? That could lead to interesting results. I tried this sequence of statements:" then comes the testcase.

How to repeat:
see above.

Suggested fix:
When fixing, make sure that all foreign key actions work (ON UPDATE/DELETE, CASCADE/RESTRICT/SET NULL/NO ACTION), with stored and virtual generated columns.
[9 Feb 2016 4:49] MySQL Verification Team 
Hello Guilhem,

Thank you for the report.

Thanks,
Umesh
[9 Feb 2016 10:33] Guilhem Bichot 
Also, when fixing, test a virtual indexed column; I imagine that the index may not be updated, so CHECK TABLE may report a corruption.
[16 May 2016 14:55] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.7.14 release, and here's the changelog entry:

InnoDB did not block the creation of a foreign key constraint with
referential actions on the base column of a generated stored column.