MDEV-16210 FK constraints on versioned tables use historical rows, which may cause constraint violation

Constraint check is done on secondary index update.
F.ex. DELETE does row_upd_sec_index_entry() and checks constraints in
row_upd_check_references_constraints(). UPDATE is optimized for the
case when order is not changed (node->cmpl_info & UPD_NODE_NO_ORD_CHANGE)
and doesn't do row_upd_sec_index_entry(), so it doesn't check constraints.

Since for versioned DELETE we do UPDATE actually, but expect behaviour
of DELETE in terms of constraints, we should deny this optimization to
get constraints checked.

Fix wrong referenced table check when versioned DELETE inserts history in parent
table. Set check_ref to false in this case.

Removed unused dup_chk_only argument for row_ins_sec_index_entry() and
added check_ref argument.

MDEV-18057 fix was superseded by this fix and reverted.

foreign.test:
All key_type combinations: pk, unique, sec(ondary).

5 files changed, 46 insertions, 29 deletions

diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result
index e5ffb6c5f0c..6a9746b62e2 100644
--- a/mysql-test/suite/versioning/r/foreign.result
+++ b/mysql-test/suite/versioning/r/foreign.result
@@ -298,15 +298,18 @@ select count(*) from subchild;
 count(*)
 0
 drop table subchild, child, parent;
-CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=InnoDB;
-CREATE TABLE t2 (f2 INT, FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=InnoDB WITH SYSTEM VERSIONING;
-SET FOREIGN_KEY_CHECKS= OFF;
-INSERT IGNORE INTO t2 VALUES (1);
-SET FOREIGN_KEY_CHECKS= ON;
-UPDATE t2 SET f2= 2;
+#
+# MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation
+#
+create or replace table t1 (f1 int, key(f1)) engine=innodb;
+create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning;
+set foreign_key_checks= off;
+insert ignore into t2 values (1);
+set foreign_key_checks= on;
+update t2 set f2= 2;
 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`))
-DELETE FROM t2;
-DROP TABLE t2, t1;
+delete from t2;
+drop table t2, t1;
 #
 # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation
 #
diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test
index d60172f99bc..9f015630e0a 100644
--- a/mysql-test/suite/versioning/t/foreign.test
+++ b/mysql-test/suite/versioning/t/foreign.test
@@ -1,7 +1,6 @@
+--source suite/versioning/key_type.inc
 --source suite/versioning/common.inc
 
-let $KEY_TYPE= primary key;
-
 --echo #################
 --echo # Test RESTRICT #
 --echo #################
@@ -331,19 +330,21 @@ select count(*) from subchild;
 
 drop table subchild, child, parent;
 
+--echo #
+--echo # MDEV-18057 Assertion `(node->state == 5) || (node->state == 6)' failed in row_upd_sec_step upon DELETE after UPDATE failed due to FK violation
+--echo #
+create or replace table t1 (f1 int, key(f1)) engine=innodb;
+create or replace table t2 (f2 int, foreign key (f2) references t1 (f1)) engine=innodb with system versioning;
 
-CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=InnoDB;
-CREATE TABLE t2 (f2 INT, FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=InnoDB WITH SYSTEM VERSIONING;
-
-SET FOREIGN_KEY_CHECKS= OFF;
-INSERT IGNORE INTO t2 VALUES (1);
+set foreign_key_checks= off;
+insert ignore into t2 values (1);
 
-SET FOREIGN_KEY_CHECKS= ON;
+set foreign_key_checks= on;
 --error ER_NO_REFERENCED_ROW_2
-UPDATE t2 SET f2= 2;
-DELETE FROM t2;
+update t2 set f2= 2;
+delete from t2;
 
-DROP TABLE t2, t1;
+drop table t2, t1;
 
 --echo #
 --echo # MDEV-18879 Corrupted record inserted by FOREIGN KEY operation
diff --git a/storage/innobase/include/row0ins.h b/storage/innobase/include/row0ins.h
index ddc3db8c694..87a72d88eb6 100644
--- a/storage/innobase/include/row0ins.h
+++ b/storage/innobase/include/row0ins.h
@@ -145,7 +145,10 @@ row_ins_sec_index_entry(
 /*====================*/
 	dict_index_t*	index,	/*!< in: secondary index */
 	dtuple_t*	entry,	/*!< in/out: index entry to insert */
-	que_thr_t*	thr)	/*!< in: query thread */
+	que_thr_t*	thr,	/*!< in: query thread */
+	bool		check_ref) /*!< in: TRUE if we want to check that
+				the referenced table is ok, FALSE if we
+				want to check the foreign key table */
 	MY_ATTRIBUTE((warn_unused_result));
 /***********************************************************//**
 Inserts a row to a table. This is a high-level function used in
diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc
index 62115beddc0..88deacbc823 100644
--- a/storage/innobase/row/row0ins.cc
+++ b/storage/innobase/row/row0ins.cc
@@ -1988,7 +1988,10 @@ row_ins_check_foreign_constraints(
 	dict_index_t*	index,	/*!< in: index */
 	bool		pk,	/*!< in: index->is_primary() */
 	dtuple_t*	entry,	/*!< in: index entry for index */
-	que_thr_t*	thr)	/*!< in: query thread */
+	que_thr_t*	thr,	/*!< in: query thread */
+	bool		check_ref = true) /*!< in: TRUE if we want to check that
+				the referenced table is ok, FALSE if we
+				want to check the foreign key table */
 {
 	dict_foreign_t*	foreign;
 	dberr_t		err;
@@ -2037,7 +2040,7 @@ row_ins_check_foreign_constraints(
 			table from being dropped while the check is running. */
 
 			err = row_ins_check_foreign_constraint(
-				TRUE, foreign, table, entry, thr);
+				check_ref, foreign, table, entry, thr);
 
 			if (referenced_table) {
 				foreign->foreign_table->dec_fk_checks();
@@ -3267,7 +3270,10 @@ row_ins_sec_index_entry(
 /*====================*/
 	dict_index_t*	index,	/*!< in: secondary index */
 	dtuple_t*	entry,	/*!< in/out: index entry to insert */
-	que_thr_t*	thr)	/*!< in: query thread */
+	que_thr_t*	thr,	/*!< in: query thread */
+	bool		check_ref) /*!< in: true if we want to check that
+				the referenced table is ok, false if we
+				want to check the foreign key table */
 {
 	dberr_t		err;
 	mem_heap_t*	offsets_heap;
@@ -3280,7 +3286,8 @@ row_ins_sec_index_entry(
 
 	if (!index->table->foreign_set.empty()) {
 		err = row_ins_check_foreign_constraints(index->table, index,
-							false, entry, thr);
+							false, entry, thr,
+							check_ref);
 		if (err != DB_SUCCESS) {
 
 			return(err);
@@ -3355,7 +3362,7 @@ row_ins_index_entry(
 	if (index->is_primary()) {
 		return row_ins_clust_index_entry(index, entry, thr, 0);
 	} else {
-		return row_ins_sec_index_entry(index, entry, thr);
+		return(row_ins_sec_index_entry(index, entry, thr, true));
 	}
 }
 
diff --git a/storage/innobase/row/row0upd.cc b/storage/innobase/row/row0upd.cc
index d081c5c2122..64021b48ee2 100644
--- a/storage/innobase/row/row0upd.cc
+++ b/storage/innobase/row/row0upd.cc
@@ -2529,7 +2529,8 @@ row_upd_sec_index_entry(
 	ut_a(entry);
 
 	/* Insert new index entry */
-	err = row_ins_sec_index_entry(index, entry, thr);
+	err = row_ins_sec_index_entry(index, entry, thr,
+				      node->is_delete != VERSIONED_DELETE);
 
 func_exit:
 	mem_heap_free(heap);
@@ -3191,9 +3192,8 @@ row_upd_clust_step(
 		row_upd_eval_new_vals(node->update);
 	}
 
-	if (node->cmpl_info & UPD_NODE_NO_ORD_CHANGE) {
+	if (!node->is_delete && node->cmpl_info & UPD_NODE_NO_ORD_CHANGE) {
 
-		node->index = NULL;
 		err = row_upd_clust_rec(
 			flags, node, index, offsets, &heap, thr, &mtr);
 		goto exit_func;
@@ -3237,7 +3237,10 @@ row_upd_clust_step(
 			goto exit_func;
 		}
 
-		node->state = UPD_NODE_UPDATE_SOME_SEC;
+		ut_ad(node->is_delete != PLAIN_DELETE);
+		node->state = node->is_delete ?
+			UPD_NODE_UPDATE_ALL_SEC :
+			UPD_NODE_UPDATE_SOME_SEC;
 	}
 
 	node->index = dict_table_get_next_index(index);