MDEV-11850: Can't create foreign key referencing a virtual column

Both dict_foreign_find_index and dict_foreign_qualify_index
did not consider virtual columns as possible foreign key
columns and there was assertion to disable virtual columns.

Fixed by also looking referencing and referenced column
from virtual columns if needed.

3 files changed, 175 insertions, 6 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 00000000000..a79cd133aa3
--- /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
+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
+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
+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;
diff --git a/mysql-test/suite/innodb/t/innodb-fk-virtual.test b/mysql-test/suite/innodb/t/innodb-fk-virtual.test
new file mode 100644
index 00000000000..094fda35abb
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-fk-virtual.test
@@ -0,0 +1,61 @@
+--source include/have_innodb.inc
+
+#
+# MDEV-11850: Can't create foreign key referencing a virtual column
+#
+
+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;
+show create table b;
+
+alter table b add constraint `v_cola_fk`
+foreign key (v_cola) references a (v_cola)
+on delete restrict
+on update restrict;
+
+show warnings;
+show create table b;
+
+alter table b add constraint `c_cola_fk`
+foreign key (c_cola) references a (cola)
+on delete restrict
+on update restrict;
+
+show warnings;
+show create table b;
+
+#
+# Test that fk really works
+#
+
+insert into a(cola) values (12);
+select * from a;
+insert into b(cola, v_cola, p_cola) values (10,2,2);
+select * from b;
+--error 1452
+insert into b(cola, v_cola, p_cola) values (10,1,1);
+--error 1451
+delete from a;
+select * from a;
+select * from b;
+drop table b, a;
diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
index 73c7bf027ca..21380cffae3 100644
--- a/storage/innobase/dict/dict0dict.cc
+++ b/storage/innobase/dict/dict0dict.cc
@@ -3518,7 +3518,6 @@ dict_foreign_find_index(
 		if (types_idx != index
 		    && !(index->type & DICT_FTS)
 		    && !dict_index_is_spatial(index)
-		    && !dict_index_has_virtual(index)
 		    && !index->to_be_dropped
 		    && dict_foreign_qualify_index(
 			    table, col_names, columns, n_cols,
@@ -4026,6 +4025,23 @@ dict_scan_col(
 				break;
 			}
 		}
+
+		for (i = 0; i < dict_table_get_n_v_cols(table); i++) {
+
+			const char*	col_name = dict_table_get_v_col_name(
+				table, i);
+
+			if (0 == innobase_strcasecmp(col_name, *name)) {
+				/* Found */
+				dict_v_col_t * vcol;
+				*success = TRUE;
+				vcol = dict_table_get_nth_v_col(table, i);
+				*column = &vcol->m_col;
+				strcpy((char*) *name, col_name);
+
+				break;
+			}
+		}
 	}
 
 	return(ptr);
@@ -5010,9 +5026,7 @@ col_loop1:
 
 	for (i = 0; i < foreign->n_fields; i++) {
 		foreign->foreign_col_names[i] = mem_heap_strdup(
-			foreign->heap,
-			dict_table_get_col_name(table,
-						dict_col_get_no(columns[i])));
+                        foreign->heap, column_names[i]);
 	}
 
 	ptr = dict_scan_table_name(cs, ptr, &referenced_table, name,
@@ -5330,6 +5344,7 @@ try_find_index:
 						ref_column_names, i,
 						foreign->foreign_index,
 			TRUE, FALSE, &index_error, &err_col, &err_index);
+
 		if (!index) {
 			mutex_enter(&dict_foreign_err_mutex);
 			dict_foreign_error_report_low(ef, create_name);
@@ -7032,8 +7047,6 @@ dict_foreign_qualify_index(
 		field = dict_index_get_nth_field(index, i);
 		col_no = dict_col_get_no(field->col);
 
-		ut_ad(!dict_col_is_virtual(field->col));
-
 		if (field->prefix_len != 0) {
 			/* We do not accept column prefix
 			indexes here */
@@ -7059,6 +7072,15 @@ dict_foreign_qualify_index(
 			? col_names[col_no]
 			: dict_table_get_col_name(table, col_no);
 
+		if (dict_col_is_virtual(field->col)) {
+			for (ulint j = 0; j < table->n_v_def; j++) {
+				col_name = dict_table_get_v_col_name(table, j);
+				if (innobase_strcasecmp(field->name,col_name) == 0) {
+					break;
+				}
+			}
+		}
+
 		if (0 != innobase_strcasecmp(columns[i], col_name)) {
 			return(false);
 		}