Merge 10.3 into 10.4, except for MDEV-20265

The MDEV-20265 commit e746f451d57def4be679caafc29976741b3e89f7
introduces DBUG_ASSERT(right_op == r_tbl) in
st_select_lex::add_cross_joined_table(), and that assertion would
fail in several tests that exercise joins. That commit was skipped
in this merge, and a separate fix of MDEV-20265 will be necessary in 10.4.

28 files changed, 413 insertions, 51 deletions

diff --git a/mysql-test/lib/My/SafeProcess/safe_process.cc b/mysql-test/lib/My/SafeProcess/safe_process.cc
index 9b544a25178..4d0d1e2a3a0 100644
--- a/mysql-test/lib/My/SafeProcess/safe_process.cc
+++ b/mysql-test/lib/My/SafeProcess/safe_process.cc
@@ -1,4 +1,5 @@
 /* Copyright (c) 2008, 2012, Oracle and/or its affiliates
+   Copyright (c) 2019, MariaDB Corporation.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -219,6 +220,7 @@ int main(int argc, char* const argv[] )
   sigemptyset(&sa.sa_mask);
 
   sa_abort.sa_handler= handle_abort;
+  sa_abort.sa_flags= 0;
   sigemptyset(&sa_abort.sa_mask);
   /* Install signal handlers */
   sigaction(SIGTERM, &sa,NULL);
diff --git a/mysql-test/main/invisible_field_debug.result b/mysql-test/main/invisible_field_debug.result
index 32eb3a274d8..582c935333c 100644
--- a/mysql-test/main/invisible_field_debug.result
+++ b/mysql-test/main/invisible_field_debug.result
@@ -385,3 +385,15 @@ SET debug_dbug="+d,test_completely_invisible,test_invisible_index";
 CREATE TABLE t2 LIKE t1;
 SET debug_dbug= DEFAULT;
 DROP TABLE t1, t2;
+#
+# MDEV-20210
+# If you have an INVISIBLE VIRTUAL column, SHOW CREATE TABLE doesn't list it as INVISIBLE
+#
+CREATE TABLE t1 (i INT, v int GENERATED ALWAYS AS (1) VIRTUAL INVISIBLE);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL,
+  `v` int(11) GENERATED ALWAYS AS (1) VIRTUAL INVISIBLE
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
diff --git a/mysql-test/main/invisible_field_debug.test b/mysql-test/main/invisible_field_debug.test
index 77e65cf7a6b..3e844fc4521 100644
--- a/mysql-test/main/invisible_field_debug.test
+++ b/mysql-test/main/invisible_field_debug.test
@@ -281,3 +281,12 @@ SET debug_dbug="+d,test_completely_invisible,test_invisible_index";
 CREATE TABLE t2 LIKE t1;
 SET debug_dbug= DEFAULT;
 DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-20210
+--echo # If you have an INVISIBLE VIRTUAL column, SHOW CREATE TABLE doesn't list it as INVISIBLE
+--echo #
+
+CREATE TABLE t1 (i INT, v int GENERATED ALWAYS AS (1) VIRTUAL INVISIBLE);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index d83239eb25f..390c7834a0a 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2179,7 +2179,7 @@ count(*)
 6
 explain extended select count(*) from t1 where a in (22,83,11) and b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref	b,a	b	5	const	59	55.93	Using where
+1	SIMPLE	t1	ref	b,a	b	5	const	59	3.30	Using where
 Warnings:
 Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11)
 select * from t1 where a in (22,83,11) and b=2;
@@ -2196,7 +2196,7 @@ count(*)
 6
 explain extended select count(*) from t1 where a in (22,83,11) and b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	59 (3%)	55.93	Using where; Using rowid filter
+1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	59 (3%)	3.30	Using where; Using rowid filter
 Warnings:
 Note	1003	select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11)
 select * from t1 where a in (22,83,11) and b=2;
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index 33c7c9be47a..7db951ccafe 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1637,3 +1637,37 @@ set @@use_stat_tables= @save_use_stat_tables;
 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
 drop function f1;
+#
+# MDEV-19834 Selectivity of an equality condition discounted twice
+#
+set @@optimizer_use_condition_selectivity=4;
+set @@use_stat_tables='preferably';
+create table t1 (a int, b int, key (b), key (a));
+insert into t1
+select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
+analyze table t1 ;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+# Check what info the optimizer has about selectivities
+explain extended select * from t1 use index () where a in (17,51,5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	3.90	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5)
+explain extended select * from t1 use index () where b=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	5.47	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`b` = 2
+# Now, the equality is used for ref access, while the range condition
+# gives selectivity data
+explain extended select * from t1 where a in (17,51,5) and b=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	58 (3%)	2.90	Using where; Using rowid filter
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5)
+drop table t1;
+set use_stat_tables= @save_use_stat_tables;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+# End of 10.1 tests
diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test
index eb3f6e2893a..d911957ddc7 100644
--- a/mysql-test/main/selectivity.test
+++ b/mysql-test/main/selectivity.test
@@ -1,4 +1,5 @@
 --source include/have_stat_tables.inc
+--source include/have_sequence.inc
 
 --disable_warnings
 drop table if exists t0,t1,t2,t3;
@@ -1104,3 +1105,26 @@ set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectiv
 drop table t1;
 drop function f1;
 
+--echo #
+--echo # MDEV-19834 Selectivity of an equality condition discounted twice
+--echo #
+set @@optimizer_use_condition_selectivity=4;
+set @@use_stat_tables='preferably';
+create table t1 (a int, b int, key (b), key (a));
+insert into t1
+select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
+analyze table t1 ;
+
+--echo # Check what info the optimizer has about selectivities
+explain extended select * from t1 use index () where a in (17,51,5);
+explain extended select * from t1 use index () where b=2;
+
+--echo # Now, the equality is used for ref access, while the range condition
+--echo # gives selectivity data
+explain extended select * from t1 where a in (17,51,5) and b=2;
+drop table t1;
+
+set use_stat_tables= @save_use_stat_tables;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+--echo # End of 10.1 tests
+
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 92091f0d6db..16f84ff3402 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1647,6 +1647,40 @@ set @@use_stat_tables= @save_use_stat_tables;
 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
 drop function f1;
+#
+# MDEV-19834 Selectivity of an equality condition discounted twice
+#
+set @@optimizer_use_condition_selectivity=4;
+set @@use_stat_tables='preferably';
+create table t1 (a int, b int, key (b), key (a));
+insert into t1
+select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000;
+analyze table t1 ;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+# Check what info the optimizer has about selectivities
+explain extended select * from t1 use index () where a in (17,51,5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	3.90	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`a` in (17,51,5)
+explain extended select * from t1 use index () where b=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	5.47	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX () where `test`.`t1`.`b` = 2
+# Now, the equality is used for ref access, while the range condition
+# gives selectivity data
+explain extended select * from t1 where a in (17,51,5) and b=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref|filter	b,a	b|a	5|5	const	59 (3%)	2.90	Using where; Using rowid filter
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5)
+drop table t1;
+set use_stat_tables= @save_use_stat_tables;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+# End of 10.1 tests
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 set @tmp_ust= @@use_stat_tables;
 set @tmp_oucs= @@optimizer_use_condition_selectivity;
diff --git a/mysql-test/main/type_datetime.result b/mysql-test/main/type_datetime.result
index 0c33ddc3df1..6e527398f8f 100644
--- a/mysql-test/main/type_datetime.result
+++ b/mysql-test/main/type_datetime.result
@@ -1156,6 +1156,17 @@ ExtractValue('foo','bar')	i	MIN(d)
 	3	1976-12-14 13:21:07
 DROP TABLE t1;
 #
+# MDEV-19034 ASAN unknown-crash in get_date_time_separator with PAD_CHAR_TO_FULL_LENGTH
+#
+SET SQL_MODE=DEFAULT;
+CREATE OR REPLACE TABLE t1 (a CHAR(11));
+CREATE OR REPLACE TABLE t2 (b DATETIME);
+INSERT INTO t1 VALUES ('2010-02-19') ;
+SET SQL_MODE= 'PAD_CHAR_TO_FULL_LENGTH';
+INSERT INTO t2 SELECT * FROM t1;
+DROP TABLE t1, t2;
+SET SQL_MODE=DEFAULT;
+#
 # End of 10.1 tests
 #
 #
diff --git a/mysql-test/main/type_datetime.test b/mysql-test/main/type_datetime.test
index cecdda593d0..3e989df1a2d 100644
--- a/mysql-test/main/type_datetime.test
+++ b/mysql-test/main/type_datetime.test
@@ -711,6 +711,20 @@ SELECT ExtractValue('foo','bar'), i, MIN(d) FROM t1 GROUP BY i;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-19034 ASAN unknown-crash in get_date_time_separator with PAD_CHAR_TO_FULL_LENGTH
+--echo #
+
+SET SQL_MODE=DEFAULT;
+CREATE OR REPLACE TABLE t1 (a CHAR(11));
+CREATE OR REPLACE TABLE t2 (b DATETIME);
+INSERT INTO t1 VALUES ('2010-02-19') ;
+SET SQL_MODE= 'PAD_CHAR_TO_FULL_LENGTH';
+INSERT INTO t2 SELECT * FROM t1;
+DROP TABLE t1, t2;
+SET SQL_MODE=DEFAULT;
+
+
+--echo #
 --echo # End of 10.1 tests
 --echo #
 
diff --git a/mysql-test/main/type_int.result b/mysql-test/main/type_int.result
index dfdff29f083..db08563a191 100644
--- a/mysql-test/main/type_int.result
+++ b/mysql-test/main/type_int.result
@@ -1,4 +1,28 @@
 #
+# Start of 5.5 tests
+#
+#
+# MDEV-15955 Assertion `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG' failed in Protocol_text::store_longlong
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT @a := 1 FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
+@a := 1
+1
+SELECT COALESCE(1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
+COALESCE(1)
+1
+SELECT COALESCE(@a:=1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
+COALESCE(@a:=1)
+1
+SELECT COALESCE(@a) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
+COALESCE(@a)
+1
+DROP TABLE t1;
+#
+# End of 5.5 tests
+#
+#
 # Start of 10.1 tests
 #
 #
diff --git a/mysql-test/main/type_int.test b/mysql-test/main/type_int.test
index ea662254efb..748ba3c3c52 100644
--- a/mysql-test/main/type_int.test
+++ b/mysql-test/main/type_int.test
@@ -1,4 +1,25 @@
 --echo #
+--echo # Start of 5.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-15955 Assertion `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG' failed in Protocol_text::store_longlong
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT @a := 1 FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
+SELECT COALESCE(1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
+SELECT COALESCE(@a:=1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
+SELECT COALESCE(@a) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
+DROP TABLE t1;
+
+--echo #
+--echo # End of 5.5 tests
+--echo #
+
+
+--echo #
 --echo # Start of 10.1 tests
 --echo #
 
diff --git a/mysql-test/suite/encryption/r/file_creation.result b/mysql-test/suite/encryption/r/file_creation.result
new file mode 100644
index 00000000000..22fe271145c
--- /dev/null
+++ b/mysql-test/suite/encryption/r/file_creation.result
@@ -0,0 +1,26 @@
+SET GLOBAL innodb_encrypt_tables = ON;
+SET GLOBAL innodb_encryption_threads = 1;
+SET GLOBAL innodb_max_dirty_pages_pct = 99;
+SHOW VARIABLES LIKE 'innodb_encrypt%';
+Variable_name	Value
+innodb_encrypt_log	OFF
+innodb_encrypt_tables	ON
+innodb_encrypt_temporary_tables	OFF
+innodb_encryption_rotate_key_age	1
+innodb_encryption_rotation_iops	100
+innodb_encryption_threads	1
+CREATE TABLE t1(f1 INT NOT NULL, f2 CHAR(255), f3 CHAR(255),
+f4 CHAR(255), f5 CHAR(255))ENGINE=INNODB;
+INSERT INTO t1 VALUES(1, "mysql", "mariadb", "batman", "superman");
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+# Wait max 10 min for key encryption threads to encrypt all spaces
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+ALTER TABLE t1 FORCE;
+# Kill the server
+DROP TABLE t1;
diff --git a/mysql-test/suite/encryption/t/file_creation.opt b/mysql-test/suite/encryption/t/file_creation.opt
new file mode 100644
index 00000000000..7d3f2da7971
--- /dev/null
+++ b/mysql-test/suite/encryption/t/file_creation.opt
@@ -0,0 +1 @@
+--innodb-tablespaces-encryption
diff --git a/mysql-test/suite/encryption/t/file_creation.test b/mysql-test/suite/encryption/t/file_creation.test
new file mode 100644
index 00000000000..6b0126831a4
--- /dev/null
+++ b/mysql-test/suite/encryption/t/file_creation.test
@@ -0,0 +1,41 @@
+--source include/have_innodb.inc
+--source include/have_example_key_management_plugin.inc
+let $restart_noprint=2;
+# embedded does not support restart
+-- source include/not_embedded.inc
+
+#
+# MDEV-19348 MariaBackup prepare fails with InnoDB: Database page corruption
+#		on disk or a failed file read
+#
+
+SET GLOBAL innodb_encrypt_tables = ON;
+SET GLOBAL innodb_encryption_threads = 1;
+SET GLOBAL innodb_max_dirty_pages_pct = 99;
+SHOW VARIABLES LIKE 'innodb_encrypt%';
+
+CREATE TABLE t1(f1 INT NOT NULL, f2 CHAR(255), f3 CHAR(255),
+                f4 CHAR(255), f5 CHAR(255))ENGINE=INNODB;
+
+INSERT INTO t1 VALUES(1, "mysql", "mariadb", "batman", "superman");
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+
+--let $tables_count= `select count(*) + 1 from information_schema.tables where engine = 'InnoDB'`
+
+--echo # Wait max 10 min for key encryption threads to encrypt all spaces
+--let $wait_timeout= 600
+--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+--source include/wait_condition.inc
+
+OPTIMIZE TABLE t1;
+
+--source ../../suite/innodb/include/no_checkpoint_start.inc
+ALTER TABLE t1 FORCE;
+--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1;
+--source ../../suite/innodb/include/no_checkpoint_end.inc
+
+--source include/start_mysqld.inc
+DROP TABLE t1;
diff --git a/mysql-test/suite/galera/t/galera_load_data.test b/mysql-test/suite/galera/t/galera_load_data.test
index f1dfd1af7fc..66526bea5e1 100644
--- a/mysql-test/suite/galera/t/galera_load_data.test
+++ b/mysql-test/suite/galera/t/galera_load_data.test
@@ -1,6 +1,5 @@
 --source include/galera_cluster.inc
 
-
 --connection node_1
 
 create database cardtest02;
@@ -45,16 +44,30 @@ let $table_rows1 = `SELECT table_rows from information_schema.tables WHERE TABLE
 let $avg_row_length1 = `SELECT avg_row_length from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl'`;
 let $data_length1 = `SELECT data_length from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl'`;
 
+--let $wait_timeout=600
+--let $wait_condition = SELECT table_rows = 301 from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';
+--source include/wait_condition.inc
+
 select count(*) from cardtest_tbl;
 
+let $cardinality1 = `SELECT cardinality from information_schema.statistics WHERE TABLE_NAME = 'cardtest_tbl'`;
+let $table_rows1 = `SELECT table_rows from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl'`;
+let $avg_row_length1 = `SELECT avg_row_length from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl'`;
+let $data_length1 = `SELECT data_length from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl'`;
+
 --connection node_2
 set session wsrep_sync_wait=15;
 use cardtest02;
+
+--let $wait_timeout=600
+--let $wait_condition = SELECT table_rows = 301 from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';
+--source include/wait_condition.inc
+
 select count(*) from cardtest_tbl;
 
 if (`SELECT cardinality <> $cardinality1 from information_schema.statistics WHERE TABLE_NAME = 'cardtest_tbl'`)
 {
-SELECT cardinality from from information_schema.statistics WHERE TABLE_NAME = 'cardtest_tbl';
+SELECT cardinality from information_schema.statistics WHERE TABLE_NAME = 'cardtest_tbl';
 }
 if (`SELECT table_rows <> $table_rows1 from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl'`)
 {
diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result
index f64e84fe429..62b4d37a979 100644
--- a/mysql-test/suite/innodb/r/foreign-keys.result
+++ b/mysql-test/suite/innodb/r/foreign-keys.result
@@ -161,3 +161,62 @@ c	d
 6	30
 drop table t2, t1;
 drop user foo;
+#
+# MDEV-17187 table doesn't exist in engine after ALTER other tables
+# with CONSTRAINTs
+#
+set foreign_key_checks=on;
+create table t1 (id int not null primary key) engine=innodb;
+create table t2 (id int not null primary key, fid int not null,
+CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id))engine=innodb;
+insert into t1 values (1), (2), (3);
+insert into t2 values (1, 1), (2, 1), (3, 2);
+set foreign_key_checks=off;
+alter table t2 drop index fk_fid;
+set foreign_key_checks=on;
+delete from t1 where id=2;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`))
+insert into t2 values(4, 99);
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`))
+select * from t1;
+id
+1
+2
+3
+select * from t2;
+id	fid
+1	1
+2	1
+3	2
+set foreign_key_checks=off;
+delete from t1 where id=2;
+insert into t2 values(4, 99);
+set foreign_key_checks=on;
+select * from t1;
+id
+1
+3
+select * from t2;
+id	fid
+1	1
+2	1
+3	2
+4	99
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `id` int(11) NOT NULL,
+  `fid` int(11) NOT NULL,
+  PRIMARY KEY (`id`),
+  CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t1,t2;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
+drop table t1,t2;
+ERROR 42S02: Unknown table 'test.t2'
diff --git a/mysql-test/suite/innodb/r/innodb-fk-warnings.result b/mysql-test/suite/innodb/r/innodb-fk-warnings.result
index 0832e6ae9ff..21e7c23d249 100644
--- a/mysql-test/suite/innodb/r/innodb-fk-warnings.result
+++ b/mysql-test/suite/innodb/r/innodb-fk-warnings.result
@@ -25,7 +25,7 @@ create table t2(a int, constraint a foreign key a (a) references t1(a)) engine=i
 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
 show warnings;
 Level	Code	Message
-Warning	150	Create  table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' foreign key a (a) references t1(a)) engine=innodb'.
+Warning	150	Create  table `test`.`t2` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' foreign key a (a) references t1(a)) engine=innodb'.
 Error	1005	Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
 Warning	1215	Cannot add foreign key constraint for `t2`
 drop table t1;
@@ -42,7 +42,7 @@ alter table t2 add constraint b foreign key (b) references t2(b);
 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
 show warnings;
 Level	Code	Message
-Warning	150	Alter  table '`test`.`t2`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' foreign key (b) references t2(b)'.
+Warning	150	Alter  table `test`.`t2` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' foreign key (b) references t2(b)'.
 Error	1005	Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
 Warning	1215	Cannot add foreign key constraint for `t2`
 drop table t2, t1;
diff --git a/mysql-test/suite/innodb/r/innodb_bug68148.result b/mysql-test/suite/innodb/r/innodb_bug68148.result
index ee6c1d62f53..7206bccd50f 100644
--- a/mysql-test/suite/innodb/r/innodb_bug68148.result
+++ b/mysql-test/suite/innodb/r/innodb_bug68148.result
@@ -18,9 +18,7 @@ Tables_in_test
 main
 ref_table1
 ref_table2
-# restart and see if we can still access the main table
 # restart
-SET FOREIGN_KEY_CHECKS=0;
 ALTER TABLE `main` ADD INDEX `idx_1` (`ref_id1`);
 SHOW CREATE TABLE `main`;
 Table	Create Table
diff --git a/mysql-test/suite/innodb/r/innodb_bug84958.result b/mysql-test/suite/innodb/r/innodb_bug84958.result
index 1a59a10eb2f..b721c73a0fc 100644
--- a/mysql-test/suite/innodb/r/innodb_bug84958.result
+++ b/mysql-test/suite/innodb/r/innodb_bug84958.result
@@ -4,13 +4,17 @@
 #
 # Set up the test with a procedure and a function.
 #
+SET @saved_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+SET GLOBAL innodb_purge_rseg_truncate_frequency= 1;
 CREATE PROCEDURE insert_n(start int, end int)
 BEGIN
 DECLARE i INT DEFAULT start;
+START TRANSACTION;
 WHILE i <= end do
 INSERT INTO t1 VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = i;
 SET i = i + 1;
 END WHILE;
+COMMIT;
 END~~
 CREATE FUNCTION num_pages_get()
 RETURNS INT
@@ -47,13 +51,14 @@ connection default;
 # Connect to default and record how many pages were accessed
 # when selecting the record using the secondary key.
 #
+InnoDB		4 transactions not purged
 SET @num_pages_1 = num_pages_get();
 SELECT * FROM t1 force index (b);
 a	b	c
 SET @num_pages_2= num_pages_get();
-SELECT @num_pages_2 - @num_pages_1 < 500;
-@num_pages_2 - @num_pages_1 < 500
-1
+SELECT IF(@num_pages_2 - @num_pages_1 < 5000, 'OK', @num_pages_2 - @num_pages_1) num_pages_diff;
+num_pages_diff
+OK
 #
 # Commit and show the final record.
 #
@@ -76,6 +81,7 @@ test.t1	check	status	OK
 #
 disconnect con2;
 disconnect con3;
+SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency;
 DROP TABLE t1;
 DROP PROCEDURE insert_n;
 DROP FUNCTION num_pages_get;
diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test
index d16ae9ed826..aec6bb54e72 100644
--- a/mysql-test/suite/innodb/t/foreign-keys.test
+++ b/mysql-test/suite/innodb/t/foreign-keys.test
@@ -203,3 +203,49 @@ connection default;
 select * from t2;
 drop table t2, t1;
 drop user foo;
+
+--echo #
+--echo # MDEV-17187 table doesn't exist in engine after ALTER other tables
+--echo # with CONSTRAINTs
+--echo #
+
+set foreign_key_checks=on;
+create table t1 (id int not null primary key) engine=innodb;
+create table t2 (id int not null primary key, fid int not null,
+CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id))engine=innodb;
+
+insert into t1 values (1), (2), (3);
+insert into t2 values (1, 1), (2, 1), (3, 2);
+
+set foreign_key_checks=off;
+alter table t2 drop index fk_fid;
+set foreign_key_checks=on;
+
+--error ER_ROW_IS_REFERENCED_2
+delete from t1 where id=2;
+--error ER_NO_REFERENCED_ROW_2
+insert into t2 values(4, 99);
+
+select * from t1;
+select * from t2;
+
+set foreign_key_checks=off;
+delete from t1 where id=2;
+insert into t2 values(4, 99);
+set foreign_key_checks=on;
+
+select * from t1;
+select * from t2;
+
+show create table t1;
+show create table t2;
+
+# Optional: test DROP TABLE without any prior ha_innobase::open().
+# This was tested manually, but it would cause --embedded to skip the test,
+# and the restart would significantly increase the running time.
+# --source include/restart_mysqld.inc
+
+--error ER_ROW_IS_REFERENCED_2
+drop table t1,t2;
+--error ER_BAD_TABLE_ERROR
+drop table t1,t2;
diff --git a/mysql-test/suite/innodb/t/innodb_bug68148.test b/mysql-test/suite/innodb/t/innodb_bug68148.test
index 531baa30e48..ab4e0311656 100644
--- a/mysql-test/suite/innodb/t/innodb_bug68148.test
+++ b/mysql-test/suite/innodb/t/innodb_bug68148.test
@@ -28,11 +28,8 @@ SET FOREIGN_KEY_CHECKS=0;
 DROP INDEX `idx_1` ON `main`;
 SHOW TABLES;
 
---echo # restart and see if we can still access the main table
 --source include/restart_mysqld.inc
 
-# This is required to access the table
-SET FOREIGN_KEY_CHECKS=0;
 ALTER TABLE `main` ADD INDEX `idx_1` (`ref_id1`);
 SHOW CREATE TABLE `main`;
 
diff --git a/mysql-test/suite/innodb/t/innodb_bug84958.test b/mysql-test/suite/innodb/t/innodb_bug84958.test
index 4456df21cb9..cbcc5d1abc6 100644
--- a/mysql-test/suite/innodb/t/innodb_bug84958.test
+++ b/mysql-test/suite/innodb/t/innodb_bug84958.test
@@ -6,15 +6,19 @@
 --echo #
 
 --source include/have_innodb.inc
+SET @saved_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency;
+SET GLOBAL innodb_purge_rseg_truncate_frequency= 1;
 
 DELIMITER ~~;
 CREATE PROCEDURE insert_n(start int, end int)
 BEGIN
     DECLARE i INT DEFAULT start;
+    START TRANSACTION;
     WHILE i <= end do
         INSERT INTO t1 VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = i;
         SET i = i + 1;
     END WHILE;
+    COMMIT;
 END~~
 
 CREATE FUNCTION num_pages_get()
@@ -60,11 +64,13 @@ connection default;
 --echo # Connect to default and record how many pages were accessed
 --echo # when selecting the record using the secondary key.
 --echo #
+--let $wait_all_purged=4
+--source include/wait_all_purged.inc
 SET @num_pages_1 = num_pages_get();
 SELECT * FROM t1 force index (b);
 SET @num_pages_2= num_pages_get();
 
-SELECT @num_pages_2 - @num_pages_1 < 500;
+SELECT IF(@num_pages_2 - @num_pages_1 < 5000, 'OK', @num_pages_2 - @num_pages_1) num_pages_diff;
 
 --echo #
 --echo # Commit and show the final record.
@@ -81,6 +87,7 @@ CHECK TABLE t1;
 --echo #
 disconnect con2;
 disconnect con3;
+SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency;
 DROP TABLE t1;
 DROP PROCEDURE insert_n;
 DROP FUNCTION num_pages_get;
diff --git a/mysql-test/suite/innodb_gis/r/point_basic.result b/mysql-test/suite/innodb_gis/r/point_basic.result
index d88b01d514b..f24ddfeb761 100644
--- a/mysql-test/suite/innodb_gis/r/point_basic.result
+++ b/mysql-test/suite/innodb_gis/r/point_basic.result
@@ -1524,7 +1524,7 @@ ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
 ERROR HY000: Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
 show warnings;
 Level	Code	Message
-Warning	150	Alter  table '`test`.`child`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'.
+Warning	150	Alter  table `test`.`child` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'.
 Error	1005	Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
 Warning	1215	Cannot add foreign key constraint for `child`
 ALTER TABLE parent DROP INDEX idx1;
@@ -1532,7 +1532,7 @@ ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
 Got one of the listed errors
 show warnings;
 Level	Code	Message
-Warning	150	Alter  table '`test`.`child`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'.
+Warning	150	Alter  table `test`.`child` with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'.
 Error	1005	Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
 Warning	1215	Cannot add foreign key constraint for `child`
 ALTER TABLE child DROP INDEX idx2;
@@ -1540,7 +1540,7 @@ ALTER TABLE child ADD FOREIGN KEY(p) REFERENCES parent(p);
 Got one of the listed errors
 show warnings;
 Level	Code	Message
-Warning	150	Alter  table '`test`.`child`' with foreign key constraint failed. There is only prefix index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'.
+Warning	150	Alter  table `test`.`child` with foreign key constraint failed. There is only prefix index in the referenced table where the referenced columns appear as the first columns near 'FOREIGN KEY(p) REFERENCES parent(p)'.
 Error	1005	Can't create table `test`.`child` (errno: 150 "Foreign key constraint is incorrectly formed")
 Warning	1215	Cannot add foreign key constraint for `child`
 DROP TABLE child, parent;
diff --git a/mysql-test/suite/maria/partition.result b/mysql-test/suite/maria/partition.result
new file mode 100644
index 00000000000..929c6bab22e
--- /dev/null
+++ b/mysql-test/suite/maria/partition.result
@@ -0,0 +1,4 @@
+CREATE TABLE t (a INT, KEY(a)) ENGINE=Aria PARTITION BY KEY(a) PARTITIONS 2;
+SELECT * FROM t PARTITION (p1);
+a
+DROP TABLE t;
diff --git a/mysql-test/suite/maria/partition.test b/mysql-test/suite/maria/partition.test
new file mode 100644
index 00000000000..86f976a05e7
--- /dev/null
+++ b/mysql-test/suite/maria/partition.test
@@ -0,0 +1,11 @@
+--source include/have_partition.inc
+
+#
+# MDEV-19254
+# Server crashes in maria_status / ha_maria::info upon SELECT with partition
+# pruning
+#
+
+CREATE TABLE t (a INT, KEY(a)) ENGINE=Aria PARTITION BY KEY(a) PARTITIONS 2;
+SELECT * FROM t PARTITION (p1);
+DROP TABLE t;
diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result
index 9e532824414..aa651452108 100644
--- a/mysql-test/suite/versioning/r/partition.result
+++ b/mysql-test/suite/versioning/r/partition.result
@@ -555,17 +555,6 @@ t1	CREATE TABLE `t1` (
 create or replace table t1 (f int) with system versioning partition by hash(f);
 insert delayed into t1 values (1);
 #
-# MDEV-17613 MIN/MAX Optimization (Select tables optimized away) does not work
-#
-create or replace table t1 (pk int primary key) with system versioning
-partition by system_time (
-partition p1 history,
-partition pn current);
-insert into t1 values (1), (2);
-explain select max(pk) from t1;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
-#
 # MDEV-20068 History partition rotation is not done under LOCK TABLES
 #
 create or replace table t1 (x int) with system versioning partition by system_time limit 1
diff --git a/mysql-test/suite/versioning/r/partition_rotation.result b/mysql-test/suite/versioning/r/partition_rotation.result
index 69b30a56bd6..7e25f122238 100644
--- a/mysql-test/suite/versioning/r/partition_rotation.result
+++ b/mysql-test/suite/versioning/r/partition_rotation.result
@@ -44,7 +44,7 @@ i
 6
 explain partitions select * from t1;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	pn_pnsp0,pn_pnsp1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	pn_pnsp0,pn_pnsp1	ALL	NULL	NULL	NULL	NULL	2	Using where
 explain partitions select * from t1 for system_time as of '2001-02-04 10:20:30';
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p1_p1sp0,p1_p1sp1,p0_p0sp0,p0_p0sp1,p2_p2sp0,p2_p2sp1,pn_pnsp0,pn_pnsp1	ALL	NULL	NULL	NULL	NULL	#	Using where
diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test
index ac9b0de690a..73df539d965 100644
--- a/mysql-test/suite/versioning/t/partition.test
+++ b/mysql-test/suite/versioning/t/partition.test
@@ -489,27 +489,6 @@ create or replace table t1 (f int) with system versioning partition by hash(f);
 insert delayed into t1 values (1);
 
 --echo #
---echo # MDEV-17613 MIN/MAX Optimization (Select tables optimized away) does not work
---echo #
---disable_query_log
-set @saved_storage_engine= @@default_storage_engine;
-if ($MTR_COMBINATION_HEAP)
-{
-  # This case does not work with HEAP
-  set default_storage_engine= myisam;
-}
---enable_query_log
-create or replace table t1 (pk int primary key) with system versioning
-partition by system_time (
-  partition p1 history,
-  partition pn current);
-insert into t1 values (1), (2);
-explain select max(pk) from t1;
---disable_query_log
-set default_storage_engine= @saved_storage_engine;
---enable_query_log
-
---echo #
 --echo # MDEV-20068 History partition rotation is not done under LOCK TABLES
 --echo #
 create or replace table t1 (x int) with system versioning partition by system_time limit 1