[MDEV-18114] Foreign Key Constraint actions don't affect Virtual Column Created: 2018-12-31  Updated: 2023-11-10  Resolved: 2023-08-02

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Virtual Columns
Affects Version/s: 10.0, 10.1, 10.3.11, 10.2, 10.3, 10.4
Fix Version/s: 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2

Type: Bug Priority: Critical
Reporter: Aurelien Marchand Assignee: Elena Stepanova
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 7

Issue Links:
Duplicate
is duplicated by MDEV-31434 stored generated columns are not prop... Closed
PartOf
includes MDEV-31322 FKs with 'ON UPDATE CASCADE' can be s... Closed
includes MDEV-32764 Segmentation fault at /mariadb-11.3.0... Closed
Problem/Incident
causes MDEV-31931 FK fields cannot be used anymore in g... Closed
causes MDEV-32030 Error in restoring SQL dump with virt... Closed
Relates
relates to MDEV-22602 WITHOUT OVERLAPS constraint is ignore... Closed
relates to MDEV-30674 Implement CHECK constraints validatio... Closed
relates to MDEV-31654 Support STORED generated columns with... Open
relates to MDEV-31112 vcol circular references lead to stac... Closed

 Description   

One of my tables has several columns with FK ON DELETE SET NULL constraints on them, plus one virtual column with mildly complex expression based on these FK columns.

When a DELETE is issued on the foreign table, the FK fields get set to NULL but the STORED Virtual column is not updated.

There is no trace of this behaviour on the documentation.

 Comments   
Comment by Aurelien Marchand [ 2018-12-31 ] 

CREATE TABLE `t1` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
 
 
 
CREATE TABLE `t2` (
 
  `id` int(11) NOT NULL,
 
  `f_id` int(11) DEFAULT NULL,
 
  `v_id` int(11) GENERATED ALWAYS AS (`f_id` MOD 2) STORED,
 
  PRIMARY KEY (`id`),
 
  KEY `f_id` (`f_id`),
 
  CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`f_id`) REFERENCES `t1` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
 
) ENGINE=InnoDB;
 
 
 
INSERT INTO t1 VALUES (1),(2),(3),(4);
 
 
 
INSERT INTO t2 VALUES (1,1,NULL), (2,2,NULL),(3,3,NULL),(4,4,NULL);
 
 
 
SELECT * FROM t2;
 
 
 
DELETE FROM t1 WHERE id IN (2,3);
 
 
 
SELECT * FROM t2;

Comment by Elena Stepanova [ 2019-01-23 ]

Thanks for the report, reproducible as described.

MySQL 5.7 rejects the attempt to create a table with a foreign key (with a cascade action) defined on a base column of a virtual column, even though I can't find this particular limitation in MySQL manual. MySQL 8.0 rejects this CREATE TABLE as well, although it's less verbose about the reason.

Comment by Sergei Golubchik [ 2023-07-13 ]

please, review these five commits:

b4b547bb14c MDEV-18114 Foreign Key Constraint actions don't affect Virtual Column
 
757a0e4cba1 cleanup: key->key_create_info.check_for_duplicate_indexes -> key->old
 
e1b389612cd cleanup: Item_field::check_vcol_func_processor()
 
a4fd56df7a1 cleanup: put db/table_name into Alter_info
 
1633ac631f3 cleanup: reorder enum_fk_option

as usual "cleanup" means "internal code rearrangement, no user visible effects"

Comment by Nikita Malyavin [ 2023-08-02 ]

The fix looks correct and exhaustive. Ok to push after the minor issues are resolved

Generated at Thu Feb 08 08:41:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.