Merge 10.2 into 10.3

21 files changed, 911 insertions, 2 deletions

diff --git a/mysql-test/main/gis-precise.result b/mysql-test/main/gis-precise.result
index 2d78b6e53de..32b536b6e7f 100644
--- a/mysql-test/main/gis-precise.result
+++ b/mysql-test/main/gis-precise.result
@@ -504,6 +504,10 @@ GEOMETRYFROMTEXT('POINT(4599 60359)'),
     ) as relate_res;
 relate_res
 0
+prepare s from 'do st_convexhull(st_aswkb(multipoint(point(-11702,15179),point(-5031,27960),point(-30557,11158),point(-27804,30314))))';
+execute s;
+execute s;
+deallocate prepare s;
 with cte1 as(   select (st_symdifference(point(1,1),point(1,1))) as a1 ), cte2 as(select 1 as a2)  select 1 from cte1 where cte1.a1 < '1';
 1
 1
diff --git a/mysql-test/main/gis-precise.test b/mysql-test/main/gis-precise.test
index 0bb445924b8..24f4ac9113e 100644
--- a/mysql-test/main/gis-precise.test
+++ b/mysql-test/main/gis-precise.test
@@ -382,6 +382,12 @@ SELECT ST_RELATE(
     'F*FFFF**F'
     ) as relate_res;
 
+# MDEV-18920 Prepared statements with st_convexhull hang and eat 100% cpu.
+prepare s from 'do st_convexhull(st_aswkb(multipoint(point(-11702,15179),point(-5031,27960),point(-30557,11158),point(-27804,30314))))';
+execute s;
+execute s;
+deallocate prepare s;
+
 # MDEV- 16050 cte + geometry functions lead to crash.
 
 with cte1 as(   select (st_symdifference(point(1,1),point(1,1))) as a1 ), cte2 as(select 1 as a2)  select 1 from cte1 where cte1.a1 < '1';
diff --git a/mysql-test/main/olap.result b/mysql-test/main/olap.result
index 1931fac6029..b7681179be0 100644
--- a/mysql-test/main/olap.result
+++ b/mysql-test/main/olap.result
@@ -786,6 +786,66 @@ t	COUNT(*)
 12:12:13	1
 DROP TABLE t1;
 #
+# MDEV-17830 Server crashes in Item_null_result::field_type upon SELECT with CHARSET(date) and ROLLUP
+#
+# Note, different MariaDB versions can return different results
+# in the two rows (such as "latin1" vs "binary"). This is wrong.
+# Both lines should return equal values.
+# The point in this test is to make sure it does not crash.
+# As this is a minor issue, bad result will be fixed
+# in a later version, presumably in 10.4.
+CREATE TABLE t (d DATE) ENGINE=MyISAM;
+INSERT INTO t VALUES ('2018-12-12');
+SELECT CHARSET(d) AS f FROM t GROUP BY d WITH ROLLUP;
+f
+binary
+latin1
+DROP TABLE t;
+#
+# MDEV-14041 Server crashes in String::length on queries with functions and ROLLUP
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT GET_LOCK( 'foo', 0 );
+GET_LOCK( 'foo', 0 )
+1
+SELECT HEX( RELEASE_LOCK( 'foo' ) ) AS f FROM t1 GROUP BY f WITH ROLLUP;
+f
+NULL
+1
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT i FROM t1 GROUP BY i WITH ROLLUP
+UNION ALL
+SELECT ELT( FOUND_ROWS(), 1 ) f FROM t1 GROUP BY f WITH ROLLUP;
+i
+1
+2
+NULL
+NULL
+NULL
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 GROUP BY NULLIF( CONVERT('', DATE), '2015-10-15' ) WITH ROLLUP;
+a
+1
+1
+Warnings:
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+Warning	1292	Incorrect datetime value: ''
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
 #
diff --git a/mysql-test/main/olap.test b/mysql-test/main/olap.test
index 3756712b49d..078eacb851b 100644
--- a/mysql-test/main/olap.test
+++ b/mysql-test/main/olap.test
@@ -424,6 +424,45 @@ DROP TABLE t1;
 
 
 --echo #
+--echo # MDEV-17830 Server crashes in Item_null_result::field_type upon SELECT with CHARSET(date) and ROLLUP
+--echo #
+
+--echo # Note, different MariaDB versions can return different results
+--echo # in the two rows (such as "latin1" vs "binary"). This is wrong.
+--echo # Both lines should return equal values.
+--echo # The point in this test is to make sure it does not crash.
+--echo # As this is a minor issue, bad result will be fixed
+--echo # in a later version, presumably in 10.4.
+
+CREATE TABLE t (d DATE) ENGINE=MyISAM;
+INSERT INTO t VALUES ('2018-12-12');
+SELECT CHARSET(d) AS f FROM t GROUP BY d WITH ROLLUP;
+DROP TABLE t;
+
+
+--echo #
+--echo # MDEV-14041 Server crashes in String::length on queries with functions and ROLLUP
+--echo #
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT GET_LOCK( 'foo', 0 );
+SELECT HEX( RELEASE_LOCK( 'foo' ) ) AS f FROM t1 GROUP BY f WITH ROLLUP;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT i FROM t1 GROUP BY i WITH ROLLUP
+UNION ALL
+SELECT ELT( FOUND_ROWS(), 1 ) f FROM t1 GROUP BY f WITH ROLLUP;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 GROUP BY NULLIF( CONVERT('', DATE), '2015-10-15' ) WITH ROLLUP;
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.1 tests
 --echo #
 
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index e2b05ccec9e..db096acb162 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -3253,3 +3253,16 @@ Warnings:
 Note	1003	select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id`
 set optimizer_switch= @save_optimizer_switch;
 DROP TABLE books, wings;
+#
+# MDEV-17796: query with DISTINCT, GROUP BY and ORDER BY
+#
+CREATE TABLE t1 (id int, gr int, v1 varchar(10));
+INSERT INTO t1 VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL), (4,4,'C');
+SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1
+WHERE gr in (4,2)
+GROUP BY id
+ORDER BY id+1 DESC;
+NULLIF(GROUP_CONCAT(v1), null)
+C
+B
+DROP TABLE t1;
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index b047a31c863..d67c67de89c 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -2187,3 +2187,17 @@ eval explain extended $q;
 set optimizer_switch= @save_optimizer_switch;
 
 DROP TABLE books, wings;
+
+--echo #
+--echo # MDEV-17796: query with DISTINCT, GROUP BY and ORDER BY
+--echo #
+
+CREATE TABLE t1 (id int, gr int, v1 varchar(10));
+INSERT INTO t1 VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL), (4,4,'C');
+
+SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1
+  WHERE gr in (4,2)
+GROUP BY id
+ORDER BY id+1 DESC;
+
+DROP TABLE t1;
diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result
index 61ba2116969..b7022559dc5 100644
--- a/mysql-test/main/sp.result
+++ b/mysql-test/main/sp.result
@@ -7643,6 +7643,22 @@ c1	c2	count(c3)
 2012-03-01 02:00:00	3	1
 DROP PROCEDURE p1;
 # End of 5.5 test
+CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
+CALL sp;
+Table	Op	Msg_type	Msg_text
+test.non_existing_table	optimize	Error	Table 'test.non_existing_table' doesn't exist
+test.non_existing_table	optimize	status	Operation failed
+SELECT 1;
+1
+1
+DROP PROCEDURE sp;
+CREATE PROCEDURE sp() SHOW USER_STATISTICS;
+CALL sp;
+User	Total_connections	Concurrent_connections	Connected_time	Busy_time	Cpu_time	Bytes_received	Bytes_sent	Binlog_bytes_written	Rows_read	Rows_sent	Rows_deleted	Rows_inserted	Rows_updated	Select_commands	Update_commands	Other_commands	Commit_transactions	Rollback_transactions	Denied_connections	Lost_connections	Access_denied	Empty_queries	Total_ssl_connections	Max_statement_time_exceeded
+SELECT 1;
+1
+1
+DROP PROCEDURE sp;
 #
 # Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS
 #
diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test
index 658d41a4736..743f0f6781e 100644
--- a/mysql-test/main/sp.test
+++ b/mysql-test/main/sp.test
@@ -9068,6 +9068,15 @@ DROP PROCEDURE p1;
 
 --echo # End of 5.5 test
 
+#MDEV-17610
+CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0;
+CALL sp;
+SELECT 1;
+DROP PROCEDURE sp;
+CREATE PROCEDURE sp() SHOW USER_STATISTICS;
+CALL sp;
+SELECT 1;
+DROP PROCEDURE sp;
 
 --echo #
 --echo # Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS
diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result
index be868e55e84..7fd0b5902ec 100644
--- a/mysql-test/main/stat_tables.result
+++ b/mysql-test/main/stat_tables.result
@@ -625,6 +625,76 @@ MAX(pk)
 NULL
 DROP TABLE t1;
 #
+# MDEV-17605: SHOW INDEXES with use_stat_tables='preferably'
+#
+set use_stat_tables='preferably';
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=off';
+select * from mysql.table_stats;
+db_name	table_name	cardinality
+dbt3_s001	lineitem	6005
+select * from mysql.index_stats;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	lineitem	PRIMARY	1	4.0033
+dbt3_s001	lineitem	PRIMARY	2	1.0000
+dbt3_s001	lineitem	i_l_shipdate	1	2.6500
+dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
+dbt3_s001	lineitem	i_l_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey	1	600.5000
+dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
+dbt3_s001	lineitem	i_l_orderkey	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
+dbt3_s001	lineitem	i_l_commitdate	1	2.7160
+SHOW INDEXES FROM lineitem;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+lineitem	0	PRIMARY	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+lineitem	0	PRIMARY	2	l_linenumber	A	6005	NULL	NULL		BTREE		
+lineitem	1	i_l_shipdate	1	l_shipDATE	A	2266	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_suppkey_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_suppkey_partkey	2	l_suppkey	A	699	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_suppkey	1	l_suppkey	A	10	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_receiptdate	1	l_receiptDATE	A	2268	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_orderkey	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+lineitem	1	i_l_orderkey_quantity	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+lineitem	1	i_l_orderkey_quantity	2	l_quantity	A	5771	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_commitdate	1	l_commitDATE	A	2210	NULL	NULL	YES	BTREE		
+SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	INDEX_COMMENT
+def	dbt3_s001	lineitem	0	dbt3_s001	PRIMARY	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+def	dbt3_s001	lineitem	0	dbt3_s001	PRIMARY	2	l_linenumber	A	6005	NULL	NULL		BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_shipdate	1	l_shipDATE	A	2266	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey_partkey	2	l_suppkey	A	699	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey	1	l_suppkey	A	10	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_receiptdate	1	l_receiptDATE	A	2268	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey_quantity	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey_quantity	2	l_quantity	A	5771	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_commitdate	1	l_commitDATE	A	2210	NULL	NULL	YES	BTREE		
+SELECT
+COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber),
+COUNT(DISTINCT l_shipDATE),
+COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey),
+COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE),
+COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE)
+FROM lineitem;
+COUNT(DISTINCT l_orderkey)	COUNT(DISTINCT l_orderkey,l_linenumber)	COUNT(DISTINCT l_shipDATE)	COUNT(DISTINCT l_partkey)	COUNT(DISTINCT l_partkey,l_suppkey)	COUNT(DISTINCT l_suppkey)	COUNT(DISTINCT l_receiptDATE)	COUNT(DISTINCT l_orderkey, l_quantity)	COUNT(DISTINCT l_commitDATE)
+1500	6005	2266	200	700	10	2268	5772	2211
+set optimizer_switch=@save_optimizer_switch;
+DROP DATABASE dbt3_s001;
+USE test;
+delete from mysql.table_stats;
+delete from mysql.column_stats;
+delete from mysql.index_stats;
+set @save_optimizer_switch=@@optimizer_switch;
+set use_stat_tables=@save_use_stat_tables;
+#
 # MDEV-18899: Server crashes in Field::set_warning_truncated_wrong_value
 #
 set names utf8;
diff --git a/mysql-test/main/stat_tables.test b/mysql-test/main/stat_tables.test
index 89c11ed4acf..93caa47ce79 100644
--- a/mysql-test/main/stat_tables.test
+++ b/mysql-test/main/stat_tables.test
@@ -401,6 +401,61 @@ SELECT MAX(pk) FROM t1;
 
 DROP TABLE t1;
 
+
+--echo #
+--echo # MDEV-17605: SHOW INDEXES with use_stat_tables='preferably'
+--echo #
+
+set use_stat_tables='preferably';
+
+CREATE DATABASE dbt3_s001;
+
+use dbt3_s001;
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=off';
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/dbt3_s001.inc
+create index i_p_retailprice on part(p_retailprice);
+delete from mysql.table_stats;
+delete from mysql.column_stats;
+delete from mysql.index_stats;
+ANALYZE TABLE lineitem;
+FLUSH TABLE mysql.table_stats, mysql.index_stats;
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+select * from mysql.table_stats;
+select * from mysql.index_stats;
+
+SHOW INDEXES FROM lineitem;
+
+SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem';
+
+SELECT
+  COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber),
+  COUNT(DISTINCT l_shipDATE),
+  COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey),
+  COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE),
+  COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE)
+FROM lineitem;
+
+set optimizer_switch=@save_optimizer_switch;
+
+DROP DATABASE dbt3_s001;
+USE test;
+
+delete from mysql.table_stats;
+delete from mysql.column_stats;
+delete from mysql.index_stats;
+
+set @save_optimizer_switch=@@optimizer_switch;
+set use_stat_tables=@save_use_stat_tables;
+
 --echo #
 --echo # MDEV-18899: Server crashes in Field::set_warning_truncated_wrong_value
 --echo #
diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result
index 86088490871..2bc69c24104 100644
--- a/mysql-test/main/stat_tables_innodb.result
+++ b/mysql-test/main/stat_tables_innodb.result
@@ -652,6 +652,76 @@ MAX(pk)
 NULL
 DROP TABLE t1;
 #
+# MDEV-17605: SHOW INDEXES with use_stat_tables='preferably'
+#
+set use_stat_tables='preferably';
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='extended_keys=off';
+select * from mysql.table_stats;
+db_name	table_name	cardinality
+dbt3_s001	lineitem	6005
+select * from mysql.index_stats;
+db_name	table_name	index_name	prefix_arity	avg_frequency
+dbt3_s001	lineitem	PRIMARY	1	4.0033
+dbt3_s001	lineitem	PRIMARY	2	1.0000
+dbt3_s001	lineitem	i_l_shipdate	1	2.6500
+dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
+dbt3_s001	lineitem	i_l_partkey	1	30.0250
+dbt3_s001	lineitem	i_l_suppkey	1	600.5000
+dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
+dbt3_s001	lineitem	i_l_orderkey	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
+dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
+dbt3_s001	lineitem	i_l_commitdate	1	2.7160
+SHOW INDEXES FROM lineitem;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+lineitem	0	PRIMARY	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+lineitem	0	PRIMARY	2	l_linenumber	A	6005	NULL	NULL		BTREE		
+lineitem	1	i_l_shipdate	1	l_shipDATE	A	2266	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_suppkey_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_suppkey_partkey	2	l_suppkey	A	699	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_suppkey	1	l_suppkey	A	10	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_receiptdate	1	l_receiptDATE	A	2268	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_orderkey	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+lineitem	1	i_l_orderkey_quantity	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+lineitem	1	i_l_orderkey_quantity	2	l_quantity	A	5771	NULL	NULL	YES	BTREE		
+lineitem	1	i_l_commitdate	1	l_commitDATE	A	2210	NULL	NULL	YES	BTREE		
+SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	INDEX_COMMENT
+def	dbt3_s001	lineitem	0	dbt3_s001	PRIMARY	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+def	dbt3_s001	lineitem	0	dbt3_s001	PRIMARY	2	l_linenumber	A	6005	NULL	NULL		BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_shipdate	1	l_shipDATE	A	2266	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey_partkey	2	l_suppkey	A	699	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_partkey	1	l_partkey	A	200	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_suppkey	1	l_suppkey	A	10	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_receiptdate	1	l_receiptDATE	A	2268	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey_quantity	1	l_orderkey	A	1500	NULL	NULL		BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_orderkey_quantity	2	l_quantity	A	5771	NULL	NULL	YES	BTREE		
+def	dbt3_s001	lineitem	1	dbt3_s001	i_l_commitdate	1	l_commitDATE	A	2210	NULL	NULL	YES	BTREE		
+SELECT
+COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber),
+COUNT(DISTINCT l_shipDATE),
+COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey),
+COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE),
+COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE)
+FROM lineitem;
+COUNT(DISTINCT l_orderkey)	COUNT(DISTINCT l_orderkey,l_linenumber)	COUNT(DISTINCT l_shipDATE)	COUNT(DISTINCT l_partkey)	COUNT(DISTINCT l_partkey,l_suppkey)	COUNT(DISTINCT l_suppkey)	COUNT(DISTINCT l_receiptDATE)	COUNT(DISTINCT l_orderkey, l_quantity)	COUNT(DISTINCT l_commitDATE)
+1500	6005	2266	200	700	10	2268	5772	2211
+set optimizer_switch=@save_optimizer_switch;
+DROP DATABASE dbt3_s001;
+USE test;
+delete from mysql.table_stats;
+delete from mysql.column_stats;
+delete from mysql.index_stats;
+set @save_optimizer_switch=@@optimizer_switch;
+set use_stat_tables=@save_use_stat_tables;
+#
 # MDEV-18899: Server crashes in Field::set_warning_truncated_wrong_value
 #
 set names utf8;
diff --git a/mysql-test/main/update_innodb.result b/mysql-test/main/update_innodb.result
index 0a85c6dab3e..695561122f0 100644
--- a/mysql-test/main/update_innodb.result
+++ b/mysql-test/main/update_innodb.result
@@ -65,3 +65,27 @@ SELECT * FROM t1;
 a_id	b_id	c_id
 1	NULL	NULL
 drop table t1,t2;
+#
+# MDEV-18300: ASAN error in Field_blob::get_key_image upon UPDATE with subquery
+#
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set @save_use_stat_tables= @@use_stat_tables;
+set use_stat_tables=preferably;
+set optimizer_use_condition_selectivity=4;
+CREATE TABLE t1 (a INT, b CHAR(8)) ENGINE=InnoDB;
+insert into t1 values (1,'foo'),(2, 'abc');
+CREATE TABLE t2 (c CHAR(8), d BLOB) ENGINE=InnoDB;
+insert into t2 values ('abc', 'foo'),('edf', 'food');
+ANALYZE TABLE t1,t2;
+UPDATE t1 SET a = 1 WHERE b = ( SELECT c FROM t2 WHERE d = 'foo' );
+SELECT * FROM t1;
+a	b
+1	foo
+1	abc
+DROP TABLE t1, t2;
+create table t1 (a int not null, b int, c int) engine=InnoDB;
+create table t2 (d int, e int) engine=InnoDB;
+update t1, t2 set a=NULL, b=2, c=NULL where b=d and e=200;
+drop table t1,t2;
+set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set @@use_stat_tables= @save_use_stat_tables;
diff --git a/mysql-test/main/update_innodb.test b/mysql-test/main/update_innodb.test
index acc8aceab00..a29dd071cf8 100644
--- a/mysql-test/main/update_innodb.test
+++ b/mysql-test/main/update_innodb.test
@@ -75,3 +75,32 @@ SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id;
 UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id);
 SELECT * FROM t1;
 drop table t1,t2;
+
+--echo #
+--echo # MDEV-18300: ASAN error in Field_blob::get_key_image upon UPDATE with subquery
+--echo #
+
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set @save_use_stat_tables= @@use_stat_tables;
+set use_stat_tables=preferably;
+set optimizer_use_condition_selectivity=4;
+
+CREATE TABLE t1 (a INT, b CHAR(8)) ENGINE=InnoDB;
+insert into t1 values (1,'foo'),(2, 'abc');
+CREATE TABLE t2 (c CHAR(8), d BLOB) ENGINE=InnoDB;
+insert into t2 values ('abc', 'foo'),('edf', 'food');
+
+--disable_result_log
+ANALYZE TABLE t1,t2;
+--enable_result_log
+UPDATE t1 SET a = 1 WHERE b = ( SELECT c FROM t2 WHERE d = 'foo' );
+SELECT * FROM t1;
+DROP TABLE t1, t2;
+
+create table t1 (a int not null, b int, c int) engine=InnoDB;
+create table t2 (d int, e int) engine=InnoDB;
+update t1, t2 set a=NULL, b=2, c=NULL where b=d and e=200;
+drop table t1,t2;
+
+set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set @@use_stat_tables= @save_use_stat_tables;
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index d9fab5f60f0..917fccaef21 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -3506,6 +3506,18 @@ id	a	b	sum_a_b
 2	2	2	4
 drop table t1;
 #
+# MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
+# failed in compare_order_elements function
+#
+CREATE TABLE t1 (a1 int);
+insert into t1 values (1),(2),(3);
+SELECT  rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1;
+rank() OVER (ORDER BY 1)	ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4)))
+1	1
+1	2
+1	3
+drop table t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index 6ebe1d653cf..81d3be19c10 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2256,6 +2256,16 @@ from t1 e;
 drop table t1;
 
 --echo #
+--echo # MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
+--echo # failed in compare_order_elements function
+--echo #
+
+CREATE TABLE t1 (a1 int);
+insert into t1 values (1),(2),(3);
+SELECT  rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1;
+drop table t1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
 
diff --git a/mysql-test/suite/archive/archive_eits.result b/mysql-test/suite/archive/archive_eits.result
new file mode 100644
index 00000000000..e077c2e4954
--- /dev/null
+++ b/mysql-test/suite/archive/archive_eits.result
@@ -0,0 +1,24 @@
+drop table if exists t1;
+#
+# MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command
+#
+CREATE TABLE t1 (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT)engine=archive;
+INSERT INTO t1 VALUES 
+(101, PointFromText('POINT(10 10)')),
+(102, PointFromText('POINT(20 10)')),
+(103, PointFromText('POINT(20 20)')),
+(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
+set @tmp1= @@optimizer_use_condition_selectivity;
+set @tmp2= @@use_stat_tables;
+set optimizer_use_condition_selectivity=4;
+set use_stat_tables=PREFERABLY;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	note	The storage engine for the table doesn't support analyze
+select * from mysql.table_stats where table_name='t1' and db_name=database();
+db_name	table_name	cardinality
+test	t1	4
+drop table t1;
+set optimizer_use_condition_selectivity=@tmp1;
+set use_stat_tables=@tmp2;
diff --git a/mysql-test/suite/archive/archive_eits.test b/mysql-test/suite/archive/archive_eits.test
new file mode 100644
index 00000000000..04c4ccdb709
--- /dev/null
+++ b/mysql-test/suite/archive/archive_eits.test
@@ -0,0 +1,32 @@
+-- source include/have_archive.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+--echo #
+--echo # MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command
+--echo #
+
+CREATE TABLE t1 (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT)engine=archive;
+INSERT INTO t1 VALUES 
+(101, PointFromText('POINT(10 10)')),
+(102, PointFromText('POINT(20 10)')),
+(103, PointFromText('POINT(20 20)')),
+(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
+
+set @tmp1= @@optimizer_use_condition_selectivity;
+set @tmp2= @@use_stat_tables;
+
+set optimizer_use_condition_selectivity=4;
+set use_stat_tables=PREFERABLY;
+ANALYZE TABLE t1;
+
+select * from mysql.table_stats where table_name='t1' and db_name=database();
+
+drop table t1;
+
+set optimizer_use_condition_selectivity=@tmp1;
+set use_stat_tables=@tmp2;
+
+
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index 75197099c11..397b4f9304c 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -261,10 +261,18 @@ ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b);
 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
 SHOW WARNINGS;
 Level	Code	Message
-Warning	150	Alter  table test/#sql-temporary with foreign key constraint failed. Referenced table `test`.`t2` not found in the data dictionary near 'FOREIGN KEY (a) REFERENCES t2 (b)'.
+Warning	150	Alter  table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t2` not found in the data dictionary near 'FOREIGN KEY (a) REFERENCES t2 (b)'.
 Error	1005	Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
 Warning	1215	Cannot add foreign key constraint for `t1`
 DROP TABLE t1;
+#
+# MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error
+#
+CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB;
+CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB;
+ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f);
+ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1);
+DROP TABLE t1, t2;
 # Start of 10.2 tests
 #
 # MDEV-13246 Stale rows despite ON DELETE CASCADE constraint
diff --git a/mysql-test/suite/innodb/r/xa_debug.result b/mysql-test/suite/innodb/r/xa_debug.result
new file mode 100644
index 00000000000..902166f51c8
--- /dev/null
+++ b/mysql-test/suite/innodb/r/xa_debug.result
@@ -0,0 +1,361 @@
+call mtr.add_suppression("Found 50 prepared XA transactions");
+create table t1 (a int) engine=innodb;
+insert into t1 values(1);
+connect  con$trial, localhost, root,,;
+xa start 'test50';
+insert into t1 values(1);
+xa end 'test50';
+xa prepare 'test50';
+connect  con$trial, localhost, root,,;
+xa start 'test49';
+insert into t1 values(1);
+xa end 'test49';
+xa prepare 'test49';
+connect  con$trial, localhost, root,,;
+xa start 'test48';
+insert into t1 values(1);
+xa end 'test48';
+xa prepare 'test48';
+connect  con$trial, localhost, root,,;
+xa start 'test47';
+insert into t1 values(1);
+xa end 'test47';
+xa prepare 'test47';
+connect  con$trial, localhost, root,,;
+xa start 'test46';
+insert into t1 values(1);
+xa end 'test46';
+xa prepare 'test46';
+connect  con$trial, localhost, root,,;
+xa start 'test45';
+insert into t1 values(1);
+xa end 'test45';
+xa prepare 'test45';
+connect  con$trial, localhost, root,,;
+xa start 'test44';
+insert into t1 values(1);
+xa end 'test44';
+xa prepare 'test44';
+connect  con$trial, localhost, root,,;
+xa start 'test43';
+insert into t1 values(1);
+xa end 'test43';
+xa prepare 'test43';
+connect  con$trial, localhost, root,,;
+xa start 'test42';
+insert into t1 values(1);
+xa end 'test42';
+xa prepare 'test42';
+connect  con$trial, localhost, root,,;
+xa start 'test41';
+insert into t1 values(1);
+xa end 'test41';
+xa prepare 'test41';
+connect  con$trial, localhost, root,,;
+xa start 'test40';
+insert into t1 values(1);
+xa end 'test40';
+xa prepare 'test40';
+connect  con$trial, localhost, root,,;
+xa start 'test39';
+insert into t1 values(1);
+xa end 'test39';
+xa prepare 'test39';
+connect  con$trial, localhost, root,,;
+xa start 'test38';
+insert into t1 values(1);
+xa end 'test38';
+xa prepare 'test38';
+connect  con$trial, localhost, root,,;
+xa start 'test37';
+insert into t1 values(1);
+xa end 'test37';
+xa prepare 'test37';
+connect  con$trial, localhost, root,,;
+xa start 'test36';
+insert into t1 values(1);
+xa end 'test36';
+xa prepare 'test36';
+connect  con$trial, localhost, root,,;
+xa start 'test35';
+insert into t1 values(1);
+xa end 'test35';
+xa prepare 'test35';
+connect  con$trial, localhost, root,,;
+xa start 'test34';
+insert into t1 values(1);
+xa end 'test34';
+xa prepare 'test34';
+connect  con$trial, localhost, root,,;
+xa start 'test33';
+insert into t1 values(1);
+xa end 'test33';
+xa prepare 'test33';
+connect  con$trial, localhost, root,,;
+xa start 'test32';
+insert into t1 values(1);
+xa end 'test32';
+xa prepare 'test32';
+connect  con$trial, localhost, root,,;
+xa start 'test31';
+insert into t1 values(1);
+xa end 'test31';
+xa prepare 'test31';
+connect  con$trial, localhost, root,,;
+xa start 'test30';
+insert into t1 values(1);
+xa end 'test30';
+xa prepare 'test30';
+connect  con$trial, localhost, root,,;
+xa start 'test29';
+insert into t1 values(1);
+xa end 'test29';
+xa prepare 'test29';
+connect  con$trial, localhost, root,,;
+xa start 'test28';
+insert into t1 values(1);
+xa end 'test28';
+xa prepare 'test28';
+connect  con$trial, localhost, root,,;
+xa start 'test27';
+insert into t1 values(1);
+xa end 'test27';
+xa prepare 'test27';
+connect  con$trial, localhost, root,,;
+xa start 'test26';
+insert into t1 values(1);
+xa end 'test26';
+xa prepare 'test26';
+connect  con$trial, localhost, root,,;
+xa start 'test25';
+insert into t1 values(1);
+xa end 'test25';
+xa prepare 'test25';
+connect  con$trial, localhost, root,,;
+xa start 'test24';
+insert into t1 values(1);
+xa end 'test24';
+xa prepare 'test24';
+connect  con$trial, localhost, root,,;
+xa start 'test23';
+insert into t1 values(1);
+xa end 'test23';
+xa prepare 'test23';
+connect  con$trial, localhost, root,,;
+xa start 'test22';
+insert into t1 values(1);
+xa end 'test22';
+xa prepare 'test22';
+connect  con$trial, localhost, root,,;
+xa start 'test21';
+insert into t1 values(1);
+xa end 'test21';
+xa prepare 'test21';
+connect  con$trial, localhost, root,,;
+xa start 'test20';
+insert into t1 values(1);
+xa end 'test20';
+xa prepare 'test20';
+connect  con$trial, localhost, root,,;
+xa start 'test19';
+insert into t1 values(1);
+xa end 'test19';
+xa prepare 'test19';
+connect  con$trial, localhost, root,,;
+xa start 'test18';
+insert into t1 values(1);
+xa end 'test18';
+xa prepare 'test18';
+connect  con$trial, localhost, root,,;
+xa start 'test17';
+insert into t1 values(1);
+xa end 'test17';
+xa prepare 'test17';
+connect  con$trial, localhost, root,,;
+xa start 'test16';
+insert into t1 values(1);
+xa end 'test16';
+xa prepare 'test16';
+connect  con$trial, localhost, root,,;
+xa start 'test15';
+insert into t1 values(1);
+xa end 'test15';
+xa prepare 'test15';
+connect  con$trial, localhost, root,,;
+xa start 'test14';
+insert into t1 values(1);
+xa end 'test14';
+xa prepare 'test14';
+connect  con$trial, localhost, root,,;
+xa start 'test13';
+insert into t1 values(1);
+xa end 'test13';
+xa prepare 'test13';
+connect  con$trial, localhost, root,,;
+xa start 'test12';
+insert into t1 values(1);
+xa end 'test12';
+xa prepare 'test12';
+connect  con$trial, localhost, root,,;
+xa start 'test11';
+insert into t1 values(1);
+xa end 'test11';
+xa prepare 'test11';
+connect  con$trial, localhost, root,,;
+xa start 'test10';
+insert into t1 values(1);
+xa end 'test10';
+xa prepare 'test10';
+connect  con$trial, localhost, root,,;
+xa start 'test9';
+insert into t1 values(1);
+xa end 'test9';
+xa prepare 'test9';
+connect  con$trial, localhost, root,,;
+xa start 'test8';
+insert into t1 values(1);
+xa end 'test8';
+xa prepare 'test8';
+connect  con$trial, localhost, root,,;
+xa start 'test7';
+insert into t1 values(1);
+xa end 'test7';
+xa prepare 'test7';
+connect  con$trial, localhost, root,,;
+xa start 'test6';
+insert into t1 values(1);
+xa end 'test6';
+xa prepare 'test6';
+connect  con$trial, localhost, root,,;
+xa start 'test5';
+insert into t1 values(1);
+xa end 'test5';
+xa prepare 'test5';
+connect  con$trial, localhost, root,,;
+xa start 'test4';
+insert into t1 values(1);
+xa end 'test4';
+xa prepare 'test4';
+connect  con$trial, localhost, root,,;
+xa start 'test3';
+insert into t1 values(1);
+xa end 'test3';
+xa prepare 'test3';
+connect  con$trial, localhost, root,,;
+xa start 'test2';
+insert into t1 values(1);
+xa end 'test2';
+xa prepare 'test2';
+connect  con$trial, localhost, root,,;
+xa start 'test1';
+insert into t1 values(1);
+xa end 'test1';
+xa prepare 'test1';
+connection default;
+xa recover;
+formatID	gtrid_length	bqual_length	data
+1	5	0	test1
+1	5	0	test2
+1	5	0	test3
+1	5	0	test4
+1	5	0	test5
+1	5	0	test6
+1	5	0	test7
+1	5	0	test8
+1	5	0	test9
+1	6	0	test10
+1	6	0	test11
+1	6	0	test12
+1	6	0	test13
+1	6	0	test14
+1	6	0	test15
+1	6	0	test16
+1	6	0	test17
+1	6	0	test18
+1	6	0	test19
+1	6	0	test20
+1	6	0	test21
+1	6	0	test22
+1	6	0	test23
+1	6	0	test24
+1	6	0	test25
+1	6	0	test26
+1	6	0	test27
+1	6	0	test28
+1	6	0	test29
+1	6	0	test30
+1	6	0	test31
+1	6	0	test32
+1	6	0	test33
+1	6	0	test34
+1	6	0	test35
+1	6	0	test36
+1	6	0	test37
+1	6	0	test38
+1	6	0	test39
+1	6	0	test40
+1	6	0	test41
+1	6	0	test42
+1	6	0	test43
+1	6	0	test44
+1	6	0	test45
+1	6	0	test46
+1	6	0	test47
+1	6	0	test48
+1	6	0	test49
+1	6	0	test50
+xa recover;
+formatID	gtrid_length	bqual_length	data
+1	5	0	test1
+1	5	0	test2
+1	5	0	test3
+1	5	0	test4
+1	5	0	test5
+1	5	0	test6
+1	5	0	test7
+1	5	0	test8
+1	5	0	test9
+1	6	0	test10
+1	6	0	test11
+1	6	0	test12
+1	6	0	test13
+1	6	0	test14
+1	6	0	test15
+1	6	0	test16
+1	6	0	test17
+1	6	0	test18
+1	6	0	test19
+1	6	0	test20
+1	6	0	test21
+1	6	0	test22
+1	6	0	test23
+1	6	0	test24
+1	6	0	test25
+1	6	0	test26
+1	6	0	test27
+1	6	0	test28
+1	6	0	test29
+1	6	0	test30
+1	6	0	test31
+1	6	0	test32
+1	6	0	test33
+1	6	0	test34
+1	6	0	test35
+1	6	0	test36
+1	6	0	test37
+1	6	0	test38
+1	6	0	test39
+1	6	0	test40
+1	6	0	test41
+1	6	0	test42
+1	6	0	test43
+1	6	0	test44
+1	6	0	test45
+1	6	0	test46
+1	6	0	test47
+1	6	0	test48
+1	6	0	test49
+1	6	0	test50
+xa recover;
+formatID	gtrid_length	bqual_length	data
+drop table t1;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index a4e045d4d5e..e7ba5530b19 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -244,10 +244,18 @@ DROP TABLE t1;
 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
 --error ER_CANT_CREATE_TABLE
 ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b);
---replace_regex /#sql-[0-9_a-f-]*/#sql-temporary/
 SHOW WARNINGS;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error
+--echo #
+CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB;
+CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB;
+ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f);
+ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1);
+DROP TABLE t1, t2;
+
 --echo # Start of 10.2 tests
 
 --echo #
diff --git a/mysql-test/suite/innodb/t/xa_debug.test b/mysql-test/suite/innodb/t/xa_debug.test
new file mode 100644
index 00000000000..5724891bb65
--- /dev/null
+++ b/mysql-test/suite/innodb/t/xa_debug.test
@@ -0,0 +1,45 @@
+-- source include/have_innodb.inc
+-- source include/have_debug.inc
+-- source include/not_embedded.inc
+
+call mtr.add_suppression("Found 50 prepared XA transactions");
+create table t1 (a int) engine=innodb;
+insert into t1 values(1);
+
+let $trial = 50;
+while ($trial)
+{
+--connect (con$trial, localhost, root,,)
+let $st_pre = `select concat('test', $trial)`;
+eval xa start '$st_pre';
+insert into t1 values(1);
+eval xa end '$st_pre';
+eval xa prepare '$st_pre';
+dec $trial;
+}
+
+connection default;
+# Kill and restart the server.
+-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+-- shutdown_server 0
+-- source include/wait_until_disconnected.inc
+
+-- exec echo "restart:--debug_dbug=+d,min_xa_len" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+-- enable_reconnect
+-- source include/wait_until_connected_again.inc
+-- disable_reconnect
+--sorted_result
+xa recover;
+--sorted_result
+xa recover;
+--disable_query_log
+let $trial = 50;
+while ($trial)
+{
+let $st_pre = `select concat('test', $trial)`;
+eval xa commit '$st_pre';
+dec $trial;
+}
+--enable_query_log
+xa recover;
+drop table t1;