2 files changed, 117 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 33b5ea1dc56..f3ae826ddb8 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -169,3 +169,56 @@ SET FOREIGN_KEY_CHECKS=DEFAULT;
 LOCK TABLE staff WRITE;
 UNLOCK TABLES;
 DROP TABLES staff, store;
+SET FOREIGN_KEY_CHECKS=1;
+#
+# MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
+#
+CREATE TABLE users (
+id int unsigned AUTO_INCREMENT PRIMARY KEY,
+name varchar(32) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_groups (
+id bigint unsigned AUTO_INCREMENT PRIMARY KEY,
+host_user_id int unsigned NOT NULL UNIQUE,
+CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
+ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_group_users (
+matchmaking_group_id bigint unsigned NOT NULL,
+user_id int unsigned NOT NULL,
+PRIMARY KEY (matchmaking_group_id,user_id),
+UNIQUE KEY user_id (user_id),
+CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
+CONSTRAINT FOREIGN KEY (user_id)
+REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE matchmaking_group_maps (
+matchmaking_group_id bigint unsigned NOT NULL,
+map_id tinyint unsigned NOT NULL,
+PRIMARY KEY (matchmaking_group_id,map_id),
+CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
+INSERT INTO matchmaking_groups VALUES (10,1),(11,2);
+INSERT INTO matchmaking_group_users VALUES (10,1),(11,2);
+INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66);
+BEGIN;
+UPDATE users SET name = 'qux' WHERE id = 1;
+connect  con1,localhost,root,,;
+SET innodb_lock_wait_timeout= 1;
+DELETE FROM matchmaking_groups WHERE id = 10;
+disconnect con1;
+connection default;
+COMMIT;
+SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+matchmaking_group_id	user_id
+SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+matchmaking_group_id	map_id
+SELECT * FROM users;
+id	name
+1	qux
+2	bar
+DROP TABLE
+matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 4a8da381e8c..72c7c34cc0d 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -1,4 +1,5 @@
 --source include/have_innodb.inc
+--source include/count_sessions.inc
 
 --echo #
 --echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
@@ -137,3 +138,66 @@ SET FOREIGN_KEY_CHECKS=DEFAULT;
 LOCK TABLE staff WRITE;
 UNLOCK TABLES;
 DROP TABLES staff, store;
+SET FOREIGN_KEY_CHECKS=1;
+
+--echo #
+--echo # MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
+--echo #
+
+CREATE TABLE users (
+  id int unsigned AUTO_INCREMENT PRIMARY KEY,
+  name varchar(32) NOT NULL DEFAULT ''
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_groups (
+  id bigint unsigned AUTO_INCREMENT PRIMARY KEY,
+  host_user_id int unsigned NOT NULL UNIQUE,
+  CONSTRAINT FOREIGN KEY (host_user_id) REFERENCES users (id)
+  ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_group_users (
+  matchmaking_group_id bigint unsigned NOT NULL,
+  user_id int unsigned NOT NULL,
+  PRIMARY KEY (matchmaking_group_id,user_id),
+  UNIQUE KEY user_id (user_id),
+  CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+  REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT FOREIGN KEY (user_id)
+  REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE matchmaking_group_maps (
+  matchmaking_group_id bigint unsigned NOT NULL,
+  map_id tinyint unsigned NOT NULL,
+  PRIMARY KEY (matchmaking_group_id,map_id),
+  CONSTRAINT FOREIGN KEY (matchmaking_group_id)
+  REFERENCES matchmaking_groups (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO users VALUES (NULL,'foo'),(NULL,'bar');
+INSERT INTO matchmaking_groups VALUES (10,1),(11,2);
+INSERT INTO matchmaking_group_users VALUES (10,1),(11,2);
+INSERT INTO matchmaking_group_maps VALUES (10,55),(11,66);
+
+BEGIN;
+UPDATE users SET name = 'qux' WHERE id = 1;
+
+--connect (con1,localhost,root,,)
+SET innodb_lock_wait_timeout= 1;
+DELETE FROM matchmaking_groups WHERE id = 10;
+--disconnect con1
+
+--connection default
+COMMIT;
+--sorted_result
+SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+--sorted_result
+SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
+--sorted_result
+SELECT * FROM users;
+
+DROP TABLE
+matchmaking_group_maps, matchmaking_group_users, matchmaking_groups, users;
+
+--source include/wait_until_count_sessions.inc