MDEV-23722 InnoDB: Assertion: result != FTS_INVALID inbb-10.1-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.

8 files changed, 199 insertions, 68 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 9f5c1f271fc..817b95be16a 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,102 @@ 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(10), b VARCHAR(10),
+KEY (a), FULLTEXT (a), index(b),
+FULLTEXT(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'innodb','innodb'),(2,'mariadb','mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b) ON UPDATE CASCADE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `a` varchar(10) DEFAULT NULL,
+  `b` varchar(10) 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 UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+UPDATE t1 SET b='InnoDB';
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+SELECT * FROM t1 WHERE MATCH(b) against ('InnoDB');
+pk	a	b
+1	innodb	InnoDB
+2	mariadb	InnoDB
+SELECT * FROM t1 WHERE MATCH(a) against ('InnoDB');
+pk	a	b
+1	innodb	InnoDB
+DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+a VARCHAR(10), b VARCHAR(10),
+KEY (a), FULLTEXT (a), index(b),
+FULLTEXT(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'innodb','innodb'),(2,'mariadb','mariadb');
+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(10) DEFAULT NULL,
+  `b` varchar(10) 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 where pk > 1;
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+SELECT * FROM t1 WHERE MATCH(b) against ('innodb');
+pk	a	b
+1	innodb	innodb
+SELECT * FROM t1 WHERE MATCH(a) against ('innodb');
+pk	a	b
+1	innodb	innodb
+SELECT * FROM t1;
+pk	a	b
+1	innodb	innodb
+DROP TABLE t1;
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+a VARCHAR(10), b VARCHAR(10),
+KEY (a), FULLTEXT (a), index(b),
+FULLTEXT(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'innodb','innodb'),(2,'mariadb','mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `a` varchar(10) DEFAULT NULL,
+  `b` varchar(10) 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 CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DELETE FROM t1 where pk > 1;
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+SELECT * FROM t1 WHERE MATCH(b) against ('innodb');
+pk	a	b
+1	innodb	innodb
+SELECT * FROM t1 WHERE MATCH(a) against ('innodb');
+pk	a	b
+1	innodb	innodb
+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..ba15ac23fce 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,48 @@ 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(10), b VARCHAR(10),
+		 KEY (a), FULLTEXT (a), index(b),
+		 FULLTEXT(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'innodb','innodb'),(2,'mariadb','mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b) ON UPDATE CASCADE;
+SHOW CREATE TABLE t1;
+UPDATE t1 SET b='InnoDB';
+CHECK TABLE t1;
+SELECT * FROM t1 WHERE MATCH(b) against ('InnoDB');
+SELECT * FROM t1 WHERE MATCH(a) against ('InnoDB');
+DROP TABLE t1;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+		 a VARCHAR(10), b VARCHAR(10),
+		 KEY (a), FULLTEXT (a), index(b),
+		 FULLTEXT(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'innodb','innodb'),(2,'mariadb','mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE SET NULL;
+SHOW CREATE TABLE t1;
+DELETE FROM t1 where pk > 1;
+CHECK TABLE t1;
+SELECT * FROM t1 WHERE MATCH(b) against ('innodb');
+SELECT * FROM t1 WHERE MATCH(a) against ('innodb');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (pk INT PRIMARY KEY,
+		 a VARCHAR(10), b VARCHAR(10),
+		 KEY (a), FULLTEXT (a), index(b),
+		 FULLTEXT(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,'innodb','innodb'),(2,'mariadb','mariadb');
+ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE;
+SHOW CREATE TABLE t1;
+DELETE FROM t1 where pk > 1;
+CHECK TABLE t1;
+SELECT * FROM t1 WHERE MATCH(b) against ('innodb');
+SELECT * FROM t1 WHERE MATCH(a) against ('innodb');
+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..1fee0a55ea4 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
@@ -1206,32 +1196,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