Bug #88111 generated column not accept foreign key for column included
Submitted: 16 Oct 2017 17:19 Modified: 17 May 2019 19:22
Reporter: mohamed atef Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[16 Oct 2017 17:19] mohamed atef
Description:
create database db1;
use db1;
create table db1.tb1
(`ID_` bigint(20) unsigned NOT NULL,
`NAME_EN` varchar(160) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`ID_`),
  UNIQUE KEY `NAME_EN_UNIQUE` (`NAME_EN`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

use db1;
create table db1.tb2
(`ID_` bigint(20) unsigned NOT NULL,
`NAME_ID` bigint(20) unsigned NOT NULL,
`JOB_EN` varchar(160) COLLATE utf8_unicode_ci NOT NULL,
`JOB_EN_UQ` varchar(160) COLLATE utf8_unicode_ci 
GENERATED always AS ((CONCAT(JOB_EN,NAME_ID))) STORED,
PRIMARY KEY (`ID_`),
  UNIQUE KEY `JOB_EN_UQUNIQUE` (`JOB_EN_UQ`) USING BTREE,
 KEY `NAME_ID_idx` (`NAME_ID`) USING BTREE,
  CONSTRAINT `NAME_ID_FK1` FOREIGN KEY (`NAME_ID`) REFERENCES `tb1` (`ID_`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

RETURN ERROR NO 
Error Code: 1215. Cannot add foreign key constraint

BUT 

use db1;
create table db1.tb2
(`ID_` bigint(20) unsigned NOT NULL,
`NAME_ID` bigint(20) unsigned NOT NULL,
`JOB_EN` varchar(160) COLLATE utf8_unicode_ci NOT NULL,
`JOB_EN_UQ` varchar(160) COLLATE utf8_unicode_ci 
NOT NULL,
PRIMARY KEY (`ID_`),
  UNIQUE KEY `JOB_EN_UQUNIQUE` (`JOB_EN_UQ`) USING BTREE,
 KEY `NAME_ID_idx` (`NAME_ID`) USING BTREE,
  CONSTRAINT `NAME_ID_FK1` FOREIGN KEY (`NAME_ID`) REFERENCES `tb1` (`ID_`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

RETURN TABLE CREATED

How to repeat:
generated column not accept foreign key for one of its component

Suggested fix:
I DON`T KNOW
[16 Oct 2017 17:39] mohamed atef
correction
table with generated column not accept foreign key for one of the columns
assigned in generated column
[19 Oct 2017 14:30] MySQL Verification Team
Hi!

I have been able to repeat the error that you get. Also, I can not find anything in our documentation that would prohibit creation of the foreign key on the non-generated column(s).

However, it still does not mean that this bug will not turn out to be a documentation bug. But, so far, this is verified as a code bug.
[20 Oct 2017 5:36] MySQL Verification Team
Neater testcase:
----------------
set foreign_key_checks=0;
drop database if exists d;
create database if not exists d;
use d;

drop table if exists t,s;
set foreign_key_checks=1;
create table t(a int primary key)engine=innodb;
create table s(
  a int, 
  b int  generated always as (a) stored,
  key(a,b),
  constraint foreign key(a) references t(a) on update cascade
)engine=innodb;
show errors;
select version();
[20 Oct 2017 11:30] MySQL Verification Team
There is simply no need for any explanation. It is a bug in the code and this is why this bug is verified.

That also means that one day it will get its schedule for fixing.
[18 Dec 2017 15:32] Jon Stephens
Fixed in mysqldoc rev 55197.

Closed.
[18 Dec 2017 16:00] mohamed atef
Why this bug is converted to documentation bug
When it available in server 8 
Where you can add foreign key on column included in generated column
[18 Dec 2017 17:21] mohamed atef
If ihad column3 is generated as concat(column1,column2)
What the difference 
If column2 updated by query 
Or updated by foreign key
[19 Dec 2017 11:59] Jon Stephens
This bug needs to be re-verified. Returning to Open/Server and reverting the previous change in the docs until this is resolved.
[19 Dec 2017 12:16] mohamed atef
If ihad column3 is generated as concat(column1,column2)
and column2 have referenced to column4 by foreign key in other table 
with on update cascade

What the difference 
If column2 updated by query update
Or updated by foreign key when i update column4 in other table
??????????????????????????????????????????????????????????????
[19 Dec 2017 15:55] mohamed atef
what is this
bug verified as a code bug. by Sinisa Milivojevic at [19 Oct 14:30].

{
[19 Oct 14:30] Sinisa Milivojevic
Hi!

I have been able to repeat the error that you get. Also, I can not find anything in our documentation that would prohibit creation of the foreign key on the non-generated column(s).

However, it still does not mean that this bug will not turn out to be a documentation bug. But, so far, this is verified as a code bug.

[19 Oct 22:17] mohamed atef
Sorry I didn’t get you
I had read documentation of generated column 
And i get that not allowed to create foriegn key in generated column
With on update cascade or on update set null 
As generated column don’t 
accept values by insert or update by query
As its value is generated and changed according to the value of 
The included columns 
But here 
In my example
I tried to create foreign key in one of the columns 
included in the generated columns
So why it didn’t accept foreign key with 
on update cascade
While i can update the the non generated column by query
Thanks 
Waiting for your clarification

[20 Oct 11:30] Sinisa Milivojevic
There is simply no need for any explanation. It is a bug in the code and this is why this bug is verified.

That also means that one day it will get its schedule for fixing.

}

then converted to doc. bug by Jon Stephens

then not a bug by Jon Stephens
then verified by Jon Stephens
then closed  by Jon Stephens

{
[18 Dec 15:32] Jon Stephens
Fixed in mysqldoc rev 55197.

Closed.
}

the i reopened it
to ask you one question 
{
If ihad column3 is generated as concat(column1,column2)
and column2 have referenced to column4 by foreign key in other table 
with on update cascade

What the difference 
If column2 updated by query update
Or updated by foreign key when column4 in the other table is updated
??????????????????????????????????????????????????????????????
}
then verified by Godofredo Miguel Solorazano
then reopened by Godofredo Miguel Solorazano

what happen
is it so difficult for mysql company to answer my question ??????
[20 Dec 2017 13:23] MySQL Verification Team
I have run this simplified test case:

=====================================================

set foreign_key_checks=0;
drop database if exists d;
create database if not exists d;
use d;

drop table if exists t,s;
set foreign_key_checks=1;
create table t(a int primary key)engine=innodb;
create table s(
 a int,
 b int  generated always as (a) stored,
 key(a,b),
 constraint foreign key(a) references t(a) on update cascade
)engine=innodb;
show errors;
select version();
drop table if exists t,s;
drop database if exists d;

================================================

And I have got this output :

ERROR 1215 (HY000) at line 9: Cannot add foreign key constraint

Hence, this bug is present in 5.7 and hence, once again, it is verified as a code bug.

Will check 8.0 once when I find some time.

Thank you for your input.
[22 Dec 2017 17:02] MySQL Verification Team
I hereby confirm that this bug is also present in 8.0.4.

I get the same error message.
[12 Feb 2019 13:16] Simon Banaan
Is there any progress on this bug? Or a way I can follow the progress?
[18 Apr 2019 15:51] Dmitry Lenev
Posted by developer:
 
Hello!

Let me clarify the situation.

The creation of foreign keys with CASCADE/SET NULL/SET DEFAULT
as ON UPDATE or ON DELETE referential actions on a base column
of stored generated column is not allowed (in both recent 5.7
and 8.0 versions of server).

This is due to fix for bug#22687023 / #80304 "GENERATED COLUMNS
DON'T WORK WITH FOREIGN KEY ACTIONS" which was introduced in
MySQL 5.7.14. This fact is even mentioned in "Changes in MySQL
5.7.14 (2016-07-29, General Availability)" page of our manual
(https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-14.html):

"
  * InnoDB: InnoDB did not block the creation of a foreign key
    constraint with referential actions on the base column of
    a stored generated column. (Bug #22687023, Bug #80304)
"

So the observed behavior is not a code bug.

However, I agree that it is a documentation issue since this
fact is not mentioned clearly at such pages as:

"13.1.18.8 CREATE TABLE and Generated Columns"
(https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html)
or
"14.6.1.5 InnoDB and FOREIGN KEY Constraints"
(https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html#innodb-foreign...)

where other limitations related to generated columns and
foreign keys are listed.

Because of this I am changing category of this bug to
Documentation.

I also think that, if filed, request to relax this limitation
would be a valid feature request.

It is also worth noting that similar limitation on base columns
of indexed virtual colums was removed in the same MySQL 5.7.14
see (Bug #22469130, Bug #79772). My guess for the reason, why
the limitation for base columns of stored generated columns was
not relaxed at the same time, is that stored generated columns
are mostly processed by SQL-layer and not by storage engine.
So SE expects their values be evaluated for them by SQL-layer.
While for indexed virtual columns value evaluation also happens
on SE level, i.e. on the same level where handling of referential
actions happens.
[18 Apr 2019 20:18] mohamed atef
Dmitry Lenev
sorry it is code bug
because i tried to create foreign key on non generated column
if i have three column 
col1 col2 col3
col3 is generated as concat(col1,col2)
case 1
if i update col2 or col1 by update statement
col3 value will change 
case 2
if i create foreign key on col1 on update cascade
and the referred column value changed 
col1 value will change, so col3 value will change
why it accept the first case
and don`t accept the second case
what is the difference
you have problem in your code
you should respect our mind
[23 Apr 2019 13:05] Dmitry Lenev
Posted by developer:
 
Hello!

As I have tried to explain above there is a difference in how we 
implement processing of direct updates to base columns of generated
stored columns and updates to the same columns which are caused by
cascading actions.

In the former case, update is mostly handled by SQL-layer. Specifically,
new value for generated column is produced by SQL-layer as well. So
storage engine (InnoDB) gets updated value for generated stored column
from SQL-layer, similarly to how it happens for non-generated column.

In the latter case, cascading actions is initiated by the storage engine
after it gets information that parent row needs to be updated. Since,
storage engine is not aware about relation between base column of
generated stored column and generated stored column itself, and doesn't
handle generated stored column re-evaluation it can't update 
generated column to proper value in this case.

Hence, we block scenarios in which such generated stored column re-evaluation
can be caused by cascading actions of foreign keys.
[17 May 2019 19:22] Daniel Price
Posted by developer:
 
The referenced content was updated with the following information:

"A foreign key constraint on the base column of a stored generated column
cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE
referential actions." 

"CREATE TABLE and Generated Columns"
(https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html)

"InnoDB and FOREIGN KEY Constraints"
(https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html#i
nnodb-foreign-key-generated-columns)

Changes should appear online soon.

Thank you for the bug report.