1 files changed, 86 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-fk-virtual.result b/mysql-test/suite/innodb/r/innodb-fk-virtual.result
new file mode 100644
index 00000000..b39e770c
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-fk-virtual.result
@@ -0,0 +1,86 @@
+create or replace table a (
+cola int(10) primary key,
+v_cola int(10) as (cola mod 10) virtual,
+p_cola int(10) as (cola mod 10) persistent
+) engine=innodb;
+create index v_cola on a (v_cola);
+create index p_cola on a (p_cola);
+create or replace table b(
+cola int(10),
+v_cola int(10),
+p_cola int(10),
+c_cola int(10) as (cola + 2) virtual
+) engine=innodb;
+alter table b add constraint `p_cola_fk`
+foreign key (p_cola) references a (p_cola)
+on delete restrict
+on update restrict;
+show warnings;
+Level	Code	Message
+show create table b;
+Table	Create Table
+b	CREATE TABLE `b` (
+  `cola` int(10) DEFAULT NULL,
+  `v_cola` int(10) DEFAULT NULL,
+  `p_cola` int(10) DEFAULT NULL,
+  `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL,
+  KEY `p_cola_fk` (`p_cola`),
+  CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table b add constraint `v_cola_fk`
+foreign key (v_cola) references a (v_cola)
+on delete restrict
+on update restrict;
+show warnings;
+Level	Code	Message
+show create table b;
+Table	Create Table
+b	CREATE TABLE `b` (
+  `cola` int(10) DEFAULT NULL,
+  `v_cola` int(10) DEFAULT NULL,
+  `p_cola` int(10) DEFAULT NULL,
+  `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL,
+  KEY `p_cola_fk` (`p_cola`),
+  KEY `v_cola_fk` (`v_cola`),
+  CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`),
+  CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+alter table b add constraint `c_cola_fk`
+foreign key (c_cola) references a (cola)
+on delete restrict
+on update restrict;
+show warnings;
+Level	Code	Message
+show create table b;
+Table	Create Table
+b	CREATE TABLE `b` (
+  `cola` int(10) DEFAULT NULL,
+  `v_cola` int(10) DEFAULT NULL,
+  `p_cola` int(10) DEFAULT NULL,
+  `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL,
+  KEY `p_cola_fk` (`p_cola`),
+  KEY `v_cola_fk` (`v_cola`),
+  KEY `c_cola_fk` (`c_cola`),
+  CONSTRAINT `c_cola_fk` FOREIGN KEY (`c_cola`) REFERENCES `a` (`cola`),
+  CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`),
+  CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+insert into a(cola) values (12);
+select * from a;
+cola	v_cola	p_cola
+12	2	2
+insert into b(cola, v_cola, p_cola) values (10,2,2);
+select * from b;
+cola	v_cola	p_cola	c_cola
+10	2	2	12
+insert into b(cola, v_cola, p_cola) values (10,1,1);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`))
+delete from a;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `c_cola_fk` FOREIGN KEY (`c_cola`) REFERENCES `a` (`cola`))
+select * from a;
+cola	v_cola	p_cola
+12	2	2
+select * from b;
+cola	v_cola	p_cola	c_cola
+10	2	2	12
+drop table b, a;