MDEV-23722 InnoDB: Failing assertion: result != FTS_INVALID inbb-10.2-MDEV-23722

			fts_trx_row_get_new_state

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.

5 files changed, 73 insertions, 45 deletions

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 b76784d4ffd..cf5ccb118f2 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
@@ -919,4 +919,33 @@ 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
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+a VARCHAR(1), b VARCHAR(1),
+KEY (a), FULLTEXT (a), index(b), FULLTEXT(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'i','i'),(2,'i','i');
+ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE SET NULL;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `a` varchar(1) DEFAULT NULL,
+  `b` varchar(1) DEFAULT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `a` (`a`),
+  KEY `b` (`b`),
+  FULLTEXT KEY `a_2` (`a`),
+  FULLTEXT KEY `b_2` (`b`),
+  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DELETE FROM t1;
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+SELECT * FROM t1 WHERE MATCH(b) against ('i');
+pk	a	b
+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 3c794ec6440..26525a636b1 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
@@ -889,4 +889,19 @@ 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 #
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+		 a VARCHAR(1), b VARCHAR(1),
+		 KEY (a), FULLTEXT (a), index(b), FULLTEXT(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'i','i'),(2,'i','i');
+ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE SET NULL;
+SHOW CREATE TABLE t1;
+DELETE FROM t1;
+CHECK TABLE t1;
+SELECT * FROM t1 WHERE MATCH(b) against ('i');
+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 8327318e46c..5b4cd0ac964 100644
--- a/storage/innobase/dict/dict0mem.cc
+++ b/storage/innobase/dict/dict0mem.cc
@@ -1223,3 +1223,23 @@ dict_mem_table_is_system(
 		return true;
 	}
 }
+
+/** Check whether fulltext index gets affected by foreign
+key constraint. */
+bool dict_foreign_t::affects_fulltext()
+{
+  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),
+          dict_col_is_virtual(dict_index_get_nth_col(foreign_index, i)))
+        != ULINT_UNDEFINED)
+      return true;
+  }
+
+  return false;
+}
diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h
index 21d9e763178..e8fbfb42f03 100644
--- a/storage/innobase/include/dict0mem.h
+++ b/storage/innobase/include/dict0mem.h
@@ -1214,6 +1214,9 @@ struct dict_foreign_t{
 
 	dict_vcol_set*	v_cols;		/*!< set of virtual columns affected
 					by foreign key constraint. */
+	/** Check whether fulltext index gets affected by foreign key
+	constraint. */
+	bool affects_fulltext();
 };
 
 std::ostream&
diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc
index 87f62125c03..ccfa04ea5fe 100644
--- a/storage/innobase/row/row0ins.cc
+++ b/storage/innobase/row/row0ins.cc
@@ -496,13 +496,13 @@ row_ins_cascade_calc_update_vec(
 
 	n_fields_updated = 0;
 
-	bool affects_fulltext = false;
-
 	if (table->fts) {
 		doc_id_pos = dict_table_get_nth_col_pos(
 			table, table->fts->doc_col, &prefix_col);
 	}
 
+	bool affects_fulltext = foreign->affects_fulltext();
+
 	for (i = 0; i < foreign->n_fields; i++) {
 
 		parent_field_no = dict_table_get_nth_col_pos(
@@ -617,17 +617,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),
-					dict_col_is_virtual(col))
-					!= ULINT_UNDEFINED) {
-					affects_fulltext = true;
-				}
-
 				/* If Doc ID is updated, check whether the
 				Doc ID is valid */
 				if (table->fts
@@ -1257,8 +1246,6 @@ row_ins_foreign_check_on_constraint(
 			      update->n_fields * sizeof *update->fields);
 #endif /* HAVE_valgrind_or_MSAN */
 
-		bool affects_fulltext = false;
-
 		for (ulint i = 0; i < foreign->n_fields; i++) {
 			upd_field_t*	ufield = &update->fields[i];
 			ulint		col_no = dict_index_get_nth_col_no(
@@ -1274,19 +1261,9 @@ row_ins_foreign_check_on_constraint(
 			ufield->orig_len = 0;
 			ufield->exp = NULL;
 			dfield_set_null(&ufield->new_val);
-
-			if (!affects_fulltext
-			    && table->fts && dict_table_is_fts_column(
-				    table->fts->indexes,
-				    dict_index_get_nth_col_no(index, i),
-				    dict_col_is_virtual(
-					    dict_index_get_nth_col(index, i)))
-			    != ULINT_UNDEFINED) {
-				affects_fulltext = true;
-			}
 		}
 
-		if (affects_fulltext) {
+		if (foreign->affects_fulltext()) {
 			fts_trx_add_op(trx, table, doc_id, FTS_DELETE, NULL);
 		}
 
@@ -1300,25 +1277,9 @@ row_ins_foreign_check_on_constraint(
 				goto nonstandard_exit_func;
 			}
 		}
-	} else if (table->fts && cascade->is_delete) {
-		/* DICT_FOREIGN_ON_DELETE_CASCADE case */
-		bool affects_fulltext = false;
-
-		for (ulint i = 0; i < foreign->n_fields; i++) {
-			if (dict_table_is_fts_column(
-				table->fts->indexes,
-				dict_index_get_nth_col_no(index, i),
-				dict_col_is_virtual(
-					dict_index_get_nth_col(index, i)))
-			    != ULINT_UNDEFINED) {
-				affects_fulltext = true;
-				break;
-			}
-		}
-
-		if (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