MySQL Bugs: #93670: virtual generated column index data inconsistency

Bug #93670	virtual generated column index data inconsistency
Submitted:	19 Dec 2018 2:46	Modified:	14 May 2019 17:31
Reporter:	rui xu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7, 8.0	OS:	Linux
Assigned to:		CPU Architecture:	x86
Tags:	virtual generated column foreign key update on cascade

Description:

virtual generated column created on user_table(has primary key),

this column depends on another column which has foreign key on update constraint on anther target_table.

when the target_table column updated, query user_table using virtual column index and using primary key have different query results.

It seems that  virtual column index not updated correctly when dependent column updated on cascade 

How to repeat:
## t1 is the target_table

MySQL [st5]>  CREATE TABLE t1(fld1 INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)

## pkt2 has primary key, fld1 has foreign key constrait(update on cascade on t1.fld1), fld2 is an virtual generated column depends on pkt2.fld1

CREATE TABLE pkt2(pk1 int primary key, fld1 INT NOT NULL, fld2 INT AS (fld1) VIRTUAL,KEY(fld2),FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE);
Query OK, 0 rows affected (0.01 sec)

INSERT INTO t1 VALUES(1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

select * from t1;
+------+
| fld1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

# query pkt2 using primary key 

 select * from pkt2;
+-----+------+------+
| pk1 | fld1 | fld2 |
+-----+------+------+
|   1 |    1 |    1 |
|   2 |    2 |    2 |
+-----+------+------+
2 rows in set (0.00 sec)

# query pkt2 using index fld2
select fld2 from pkt2; 
+------+
| fld2 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
  
## same result as expected
## now we try to update t1 

 UPDATE t1 SET fld1= 3 WHERE fld1= 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

## query t2 again using primary key and index fld2

select * from pkt2;                  
+-----+------+------+
| pk1 | fld1 | fld2 |
+-----+------+------+
|   1 |    1 |    1 |
|   2 |    3 |    3 |
+-----+------+------+
2 rows in set (0.01 sec)

##
 select fld2 from pkt2;   
+------+
| fld2 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

fld2 resturn value incorrect when using index fld2

###
this could also be repeated from  mysqltest case
"innodb.virtual_fk_restart"

changing the testing table into table with primary key

##
from Tencent TXSQL team

Suggested fix:

virtual generated column index not updated as expected when the dependent column updated from the foreign key constraint "on update cascade"

Hi,

I have tested this with 5.7.24 and I get the following results:

fld1
1
2
pk1	fld1	fld2
1	1	1
2	2	2
fld2
1
2
pk1	fld1	fld2
1	1	1
2	3	3
fld2
1
2

Hence, you have proved your point. After update we get the wrong results with different query.

Verified as reported.

Same results are returned with 8.0.

Hence, the "Version" field is properly updated.

Posted by developer:
 
Fixed in 5.7.27.

An index defined on a virtual generated column could fail to be
updated if the column had a base column in a foreign key
relationship.

Thank you, Paul.