MDEV-23722 InnoDB: Assertion: result != FTS_INVALID in fts_trx_row_get_new_statebb-10.1-thiru

Marking of deletion of row in fts index happens twice in
self-referential foreign key relation. So while performing
referential checks of foreign key, InnoDB can avoid updating
of fts index if the foreign key has self-referential relationship.

Reviewed-by: Marko Mäkelä

10 files changed, 238 insertions, 70 deletions

diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index e9fbc742379..4ea68b65e06 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -244,3 +244,53 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 XA END 'xid';
 XA ROLLBACK 'xid';
 DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+f1 VARCHAR(10), f2 VARCHAR(10),
+f3 VARCHAR(10), f4 VARCHAR(10),
+f5 VARCHAR(10), f6 VARCHAR(10),
+f7 VARCHAR(10), f8 VARCHAR(10),
+INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
+INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'innodb', 'innodb', 'innodb', 'innodb',
+'mariadb', 'innodb', 'mariadb', 'innodb'),
+(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+'mariadb', 'mariadb', 'innodb', 'mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON UPDATE CASCADE;
+ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE SET NULL;
+ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
+ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON DELETE CASCADE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `f1` varchar(10) DEFAULT NULL,
+  `f2` varchar(10) DEFAULT NULL,
+  `f3` varchar(10) DEFAULT NULL,
+  `f4` varchar(10) DEFAULT NULL,
+  `f5` varchar(10) DEFAULT NULL,
+  `f6` varchar(10) DEFAULT NULL,
+  `f7` varchar(10) DEFAULT NULL,
+  `f8` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `f1` (`f1`),
+  KEY `f2` (`f2`),
+  KEY `f3` (`f3`),
+  KEY `f4` (`f4`),
+  KEY `f5` (`f5`),
+  KEY `f6` (`f6`),
+  KEY `f7` (`f7`),
+  KEY `f8` (`f8`),
+  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE,
+  CONSTRAINT `t1_ibfk_2` FOREIGN KEY (`f3`) REFERENCES `t1` (`f4`) ON DELETE SET NULL,
+  CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL,
+  CONSTRAINT `t1_ibfk_4` FOREIGN KEY (`f7`) REFERENCES `t1` (`f8`) ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+UPDATE t1 SET f2='cascade';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE)
+UPDATE t1 SET f6='null_pointer';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+DELETE FROM t1 WHERE f4='mariadb';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+DELETE FROM t1 WHERE f8='mariadb';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 3269aeed62b..740a1c75867 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -299,6 +299,35 @@ ALTER TABLE t1 ADD FOREIGN KEY f (a) REFERENCES t1 (pk), LOCK=EXCLUSIVE;# Cleanu
 XA END 'xid';
 XA ROLLBACK 'xid';
 DROP TABLE t1;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+                 f1 VARCHAR(10), f2 VARCHAR(10),
+		 f3 VARCHAR(10), f4 VARCHAR(10),
+		 f5 VARCHAR(10), f6 VARCHAR(10),
+		 f7 VARCHAR(10), f8 VARCHAR(10),
+		 INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
+		 INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'innodb', 'innodb', 'innodb', 'innodb',
+		       'mariadb', 'innodb', 'mariadb', 'innodb'),
+		      (2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+		       'mariadb', 'mariadb', 'innodb', 'mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON UPDATE CASCADE;
+ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE SET NULL;
+ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
+ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON DELETE CASCADE;
+SHOW CREATE TABLE t1;
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET f2='cascade';
+
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET f6='null_pointer';
+
+--error ER_ROW_IS_REFERENCED_2
+DELETE FROM t1 WHERE f4='mariadb';
+
+--error ER_ROW_IS_REFERENCED_2
+DELETE FROM t1 WHERE f8='mariadb';
+DROP TABLE t1;
 #
 # End of 10.1 tests
 #
diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
index 9f5c1f271fc..ff1546a25a4 100644
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
@@ -913,4 +913,69 @@ DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
 DROP TABLE t4;
+#
+# InnoDB: Failing assertion: result != FTS_INVALID in
+#        fts_trx_row_get_new_state
+#
+SET FOREIGN_KEY_CHECKS=1;
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+f1 VARCHAR(10), f2 VARCHAR(10),
+f3 VARCHAR(10), f4 VARCHAR(10),
+f5 VARCHAR(10), f6 VARCHAR(10),
+f7 VARCHAR(10), f8 VARCHAR(10),
+FULLTEXT(f1), FULLTEXT(f2), FULLTEXT(f3), FULLTEXT(f4),
+FULLTEXT(f5), FULLTEXT(f6), FULLTEXT(f7), FULLTEXT(f8),
+INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
+INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'innodb', 'innodb', 'innodb', 'innodb',
+'mariadb', 'innodb', 'mariadb', 'innodb'),
+(2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+'mariadb', 'mariadb', 'innodb', 'mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON UPDATE CASCADE;
+ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE SET NULL;
+ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
+ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON DELETE CASCADE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `f1` varchar(10) DEFAULT NULL,
+  `f2` varchar(10) DEFAULT NULL,
+  `f3` varchar(10) DEFAULT NULL,
+  `f4` varchar(10) DEFAULT NULL,
+  `f5` varchar(10) DEFAULT NULL,
+  `f6` varchar(10) DEFAULT NULL,
+  `f7` varchar(10) DEFAULT NULL,
+  `f8` varchar(10) DEFAULT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `f1_2` (`f1`),
+  KEY `f2_2` (`f2`),
+  KEY `f3_2` (`f3`),
+  KEY `f4_2` (`f4`),
+  KEY `f5_2` (`f5`),
+  KEY `f6_2` (`f6`),
+  KEY `f7_2` (`f7`),
+  KEY `f8_2` (`f8`),
+  FULLTEXT KEY `f1` (`f1`),
+  FULLTEXT KEY `f2` (`f2`),
+  FULLTEXT KEY `f3` (`f3`),
+  FULLTEXT KEY `f4` (`f4`),
+  FULLTEXT KEY `f5` (`f5`),
+  FULLTEXT KEY `f6` (`f6`),
+  FULLTEXT KEY `f7` (`f7`),
+  FULLTEXT KEY `f8` (`f8`),
+  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE,
+  CONSTRAINT `t1_ibfk_2` FOREIGN KEY (`f3`) REFERENCES `t1` (`f4`) ON DELETE SET NULL,
+  CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL,
+  CONSTRAINT `t1_ibfk_4` FOREIGN KEY (`f7`) REFERENCES `t1` (`f8`) ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+UPDATE t1 SET f2='cascade';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE)
+UPDATE t1 SET f6='null_pointer';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+DELETE FROM t1 WHERE f4='mariadb';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+DELETE FROM t1 WHERE f8='mariadb';
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
+DROP TABLE t1;
 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test
index 6ef0452f4c6..af89243af16 100644
--- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test
+++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test
@@ -907,4 +907,40 @@ DROP TABLE t2;
 DROP TABLE t3;
 DROP TABLE t4;
 
+--echo #
+--echo # InnoDB: Failing assertion: result != FTS_INVALID in
+--echo #        fts_trx_row_get_new_state
+--echo #
+SET FOREIGN_KEY_CHECKS=1;
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+                 f1 VARCHAR(10), f2 VARCHAR(10),
+                 f3 VARCHAR(10), f4 VARCHAR(10),
+                 f5 VARCHAR(10), f6 VARCHAR(10),
+		 f7 VARCHAR(10), f8 VARCHAR(10),
+		 FULLTEXT(f1), FULLTEXT(f2), FULLTEXT(f3), FULLTEXT(f4),
+		 FULLTEXT(f5), FULLTEXT(f6), FULLTEXT(f7), FULLTEXT(f8),
+                 INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
+		 INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 'innodb', 'innodb', 'innodb', 'innodb',
+                       'mariadb', 'innodb', 'mariadb', 'innodb'),
+                      (2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
+                       'mariadb', 'mariadb', 'innodb', 'mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON UPDATE CASCADE;
+ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE SET NULL;
+ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
+ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON DELETE CASCADE;
+SHOW CREATE TABLE t1;
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET f2='cascade';
+
+--error ER_ROW_IS_REFERENCED_2
+UPDATE t1 SET f6='null_pointer';
+
+--error ER_ROW_IS_REFERENCED_2
+DELETE FROM t1 WHERE f4='mariadb';
+
+--error ER_ROW_IS_REFERENCED_2
+DELETE FROM t1 WHERE f8='mariadb';
+DROP TABLE t1;
+
 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
diff --git a/storage/innobase/dict/dict0mem.cc b/storage/innobase/dict/dict0mem.cc
index 6ace0ee9a75..157e695f696 100644
--- a/storage/innobase/dict/dict0mem.cc
+++ b/storage/innobase/dict/dict0mem.cc
@@ -852,3 +852,22 @@ operator<< (std::ostream& out, const dict_foreign_set& fk_set)
 	return(out);
 }
 
+/** Check whether fulltext index gets affected by foreign
+key constraint. */
+bool dict_foreign_t::affects_fulltext() const
+{
+  if (foreign_table == referenced_table || !foreign_table->fts)
+    return false;
+
+  for (ulint i = 0; i < n_fields; i++)
+  {
+    if (dict_table_is_fts_column(
+          foreign_table->fts->indexes,
+          dict_index_get_nth_col_no(foreign_index, i))
+        != ULINT_UNDEFINED)
+      return true;
+  }
+
+  return false;
+}
+
diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h
index 505d4925324..7c59789d313 100644
--- a/storage/innobase/include/dict0mem.h
+++ b/storage/innobase/include/dict0mem.h
@@ -848,6 +848,10 @@ struct dict_foreign_t{
 					does not generate new indexes
 					implicitly */
 	dict_index_t*	referenced_index;/*!< referenced index */
+
+	/** Check whether the fulltext index gets affected by
+	foreign key constraint */
+	bool affects_fulltext() const;
 };
 
 std::ostream&
diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc
index ecbfd03ec93..4d86b0f333c 100644
--- a/storage/innobase/row/row0ins.cc
+++ b/storage/innobase/row/row0ins.cc
@@ -510,7 +510,7 @@ row_ins_cascade_calc_update_vec(
 
 	n_fields_updated = 0;
 
-	*fts_col_affected = FALSE;
+	*fts_col_affected = foreign->affects_fulltext();
 
 	if (table->fts) {
 		doc_id_pos = dict_table_get_nth_col_pos(
@@ -631,16 +631,6 @@ row_ins_cascade_calc_update_vec(
 							padded_data, min_size);
 				}
 
-				/* Check whether the current column has
-				FTS index on it */
-				if (table->fts
-				    && dict_table_is_fts_column(
-					table->fts->indexes,
-					dict_col_get_no(col))
-					!= ULINT_UNDEFINED) {
-					*fts_col_affected = TRUE;
-				}
-
 				/* If Doc ID is updated, check whether the
 				Doc ID is valid */
 				if (table->fts
@@ -977,7 +967,6 @@ row_ins_foreign_check_on_constraint(
 	upd_t*		update;
 	ulint		n_to_update;
 	dberr_t		err;
-	ulint		i;
 	trx_t*		trx;
 	mem_heap_t*	tmp_heap	= NULL;
 	doc_id_t	doc_id = FTS_NULL_DOC_ID;
@@ -1191,7 +1180,7 @@ row_ins_foreign_check_on_constraint(
 		UNIV_MEM_INVALID(update->fields,
 				 update->n_fields * sizeof *update->fields);
 
-		for (i = 0; i < foreign->n_fields; i++) {
+		for (ulint i = 0; i < foreign->n_fields; i++) {
 			upd_field_t*	ufield = &update->fields[i];
 
 			ufield->field_no = dict_table_get_nth_col_pos(
@@ -1200,32 +1189,14 @@ row_ins_foreign_check_on_constraint(
 			ufield->orig_len = 0;
 			ufield->exp = NULL;
 			dfield_set_null(&ufield->new_val);
-
-			if (table->fts && dict_table_is_fts_column(
-				table->fts->indexes,
-				dict_index_get_nth_col_no(index, i))
-				!= ULINT_UNDEFINED) {
-				fts_col_affacted = TRUE;
-			}
 		}
 
-		if (fts_col_affacted) {
-			fts_trx_add_op(trx, table, doc_id, FTS_DELETE, NULL);
-		}
-	} else if (table->fts && cascade->is_delete) {
-		/* DICT_FOREIGN_ON_DELETE_CASCADE case */
-		for (i = 0; i < foreign->n_fields; i++) {
-			if (table->fts && dict_table_is_fts_column(
-				table->fts->indexes,
-				dict_index_get_nth_col_no(index, i))
-				!= ULINT_UNDEFINED) {
-				fts_col_affacted = TRUE;
-			}
-		}
-
-		if (fts_col_affacted) {
+		if (foreign->affects_fulltext()) {
 			fts_trx_add_op(trx, table, doc_id, FTS_DELETE, NULL);
 		}
+	} else if (table->fts && cascade->is_delete
+		   && foreign->affects_fulltext()) {
+		fts_trx_add_op(trx, table, doc_id, FTS_DELETE, NULL);
 	}
 
 	if (!node->is_delete
diff --git a/storage/xtradb/dict/dict0mem.cc b/storage/xtradb/dict/dict0mem.cc
index 89b9bd9aead..82480b13827 100644
--- a/storage/xtradb/dict/dict0mem.cc
+++ b/storage/xtradb/dict/dict0mem.cc
@@ -853,3 +853,22 @@ operator<< (std::ostream& out, const dict_foreign_set& fk_set)
 	return(out);
 }
 
+/** Check whether fulltext index gets affected by foreign
+key constraint. */
+bool dict_foreign_t::affects_fulltext() const
+{
+  if (foreign_table == referenced_table || !foreign_table->fts)
+    return false;
+
+  for (ulint i = 0; i < n_fields; i++)
+  {
+    if (dict_table_is_fts_column(
+          foreign_table->fts->indexes,
+          dict_index_get_nth_col_no(foreign_index, i))
+        != ULINT_UNDEFINED)
+      return true;
+  }
+
+  return false;
+}
+
diff --git a/storage/xtradb/include/dict0mem.h b/storage/xtradb/include/dict0mem.h
index 1dcc3ebdd0c..f39c89d8e4b 100644
--- a/storage/xtradb/include/dict0mem.h
+++ b/storage/xtradb/include/dict0mem.h
@@ -864,6 +864,10 @@ struct dict_foreign_t{
 					does not generate new indexes
 					implicitly */
 	dict_index_t*	referenced_index;/*!< referenced index */
+
+	/** Check whether the fulltext index gets affected by
+	foreign key constraint */
+	bool affects_fulltext() const;
 };
 
 std::ostream&
diff --git a/storage/xtradb/row/row0ins.cc b/storage/xtradb/row/row0ins.cc
index f7371d6b83e..c78f5bc8dc4 100644
--- a/storage/xtradb/row/row0ins.cc
+++ b/storage/xtradb/row/row0ins.cc
@@ -516,7 +516,7 @@ row_ins_cascade_calc_update_vec(
 
 	n_fields_updated = 0;
 
-	*fts_col_affected = FALSE;
+	*fts_col_affected = foreign->affects_fulltext();
 
 	if (table->fts) {
 		doc_id_pos = dict_table_get_nth_col_pos(
@@ -637,16 +637,6 @@ row_ins_cascade_calc_update_vec(
 							padded_data, min_size);
 				}
 
-				/* Check whether the current column has
-				FTS index on it */
-				if (table->fts
-				    && dict_table_is_fts_column(
-					table->fts->indexes,
-					dict_col_get_no(col))
-					!= ULINT_UNDEFINED) {
-					*fts_col_affected = TRUE;
-				}
-
 				/* If Doc ID is updated, check whether the
 				Doc ID is valid */
 				if (table->fts
@@ -983,7 +973,6 @@ row_ins_foreign_check_on_constraint(
 	upd_t*		update;
 	ulint		n_to_update;
 	dberr_t		err;
-	ulint		i;
 	trx_t*		trx;
 	mem_heap_t*	tmp_heap	= NULL;
 	doc_id_t	doc_id = FTS_NULL_DOC_ID;
@@ -1197,7 +1186,7 @@ row_ins_foreign_check_on_constraint(
 		UNIV_MEM_INVALID(update->fields,
 				 update->n_fields * sizeof *update->fields);
 
-		for (i = 0; i < foreign->n_fields; i++) {
+		for (ulint i = 0; i < foreign->n_fields; i++) {
 			upd_field_t*	ufield = &update->fields[i];
 
 			ufield->field_no = dict_table_get_nth_col_pos(
@@ -1206,32 +1195,14 @@ row_ins_foreign_check_on_constraint(
 			ufield->orig_len = 0;
 			ufield->exp = NULL;
 			dfield_set_null(&ufield->new_val);
-
-			if (table->fts && dict_table_is_fts_column(
-				table->fts->indexes,
-				dict_index_get_nth_col_no(index, i))
-				!= ULINT_UNDEFINED) {
-				fts_col_affacted = TRUE;
-			}
 		}
 
-		if (fts_col_affacted) {
-			fts_trx_add_op(trx, table, doc_id, FTS_DELETE, NULL);
-		}
-	} else if (table->fts && cascade->is_delete) {
-		/* DICT_FOREIGN_ON_DELETE_CASCADE case */
-		for (i = 0; i < foreign->n_fields; i++) {
-			if (table->fts && dict_table_is_fts_column(
-				table->fts->indexes,
-				dict_index_get_nth_col_no(index, i))
-				!= ULINT_UNDEFINED) {
-				fts_col_affacted = TRUE;
-			}
-		}
-
-		if (fts_col_affacted) {
+		if (foreign->affects_fulltext()) {
 			fts_trx_add_op(trx, table, doc_id, FTS_DELETE, NULL);
 		}
+	} else if (table->fts && cascade->is_delete
+		   && foreign->affects_fulltext()) {
+		fts_trx_add_op(trx, table, doc_id, FTS_DELETE, NULL);
 	}
 
 	if (!node->is_delete