Merge branch '10.0' into bb-10.1-merge-sanja

36 files changed, 566 insertions, 7 deletions

diff --git a/mysql-test/include/ctype_mdev13118.inc b/mysql-test/include/ctype_mdev13118.inc
new file mode 100644
index 00000000000..efcb57299df
--- /dev/null
+++ b/mysql-test/include/ctype_mdev13118.inc
@@ -0,0 +1,15 @@
+--echo #
+--echo # MDEV-13118 Wrong results with LOWER and UPPER and subquery
+--echo #
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+--sorted_result
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+--sorted_result
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 512368a2d26..627ba8a35a5 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -3047,6 +3047,29 @@ DROP TABLE t1;
 SELECT _binary 0x7E, _binary X'7E', _binary B'01111110';
 _binary 0x7E	_binary X'7E'	_binary B'01111110'
 ~	~	~
+SET NAMES utf8, character_set_connection=binary;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varbinary(10) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+abcdefghi-abcdefghi
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/ctype_eucjpms.result b/mysql-test/r/ctype_eucjpms.result
index f9cb4f1eecc..160c4758122 100644
--- a/mysql-test/r/ctype_eucjpms.result
+++ b/mysql-test/r/ctype_eucjpms.result
@@ -33868,6 +33868,29 @@ HEX(a)	CHAR_LENGTH(a)
 DROP TABLE t1;
 SELECT _eucjpms 0x8EA0;
 ERROR HY000: Invalid eucjpms character string: '8EA0'
+SET NAMES eucjpms;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET eucjpms NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/ctype_euckr.result b/mysql-test/r/ctype_euckr.result
index 90353c6af2a..a23ced76df9 100644
--- a/mysql-test/r/ctype_euckr.result
+++ b/mysql-test/r/ctype_euckr.result
@@ -25428,3 +25428,32 @@ A1A1A1A1A1A120202020202020202020202020202020202020
 #
 # End of 5.6 tests
 #
+#
+# Start of 10.0 tests
+#
+SET NAMES utf8, character_set_connection=euckr;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET euckr NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
+#
+# End of 10.0 tests
+#
diff --git a/mysql-test/r/ctype_gbk.result b/mysql-test/r/ctype_gbk.result
index d10d5f4bf75..ac8de1e229c 100644
--- a/mysql-test/r/ctype_gbk.result
+++ b/mysql-test/r/ctype_gbk.result
@@ -5099,6 +5099,29 @@ E05C5B
 E05B
 DROP TABLE t1;
 # Start of ctype_E05C.inc
+SET NAMES utf8, character_set_connection=gbk;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET gbk NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant
 #
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index 66c5a37750d..3b9b2633480 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -7989,6 +7989,29 @@ a
 0
 DROP VIEW v1;
 DROP TABLE t1;
+SET NAMES latin1;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 55caabbaa12..9c598780edf 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -5705,6 +5705,29 @@ c2
 YWJjZGVmZ2hp-YWJjZGVmZ2hp
 DROP TABLE t1;
 SET optimizer_switch=@save_optimizer_switch;
+SET NAMES utf8, character_set_connection=ucs2;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET ucs2 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result
index 61541ec7678..9146356b5f9 100644
--- a/mysql-test/r/ctype_ujis.result
+++ b/mysql-test/r/ctype_ujis.result
@@ -26173,6 +26173,29 @@ HEX(a)	CHAR_LENGTH(a)
 DROP TABLE t1;
 SELECT _ujis 0x8EA0;
 ERROR HY000: Invalid ujis character string: '8EA0'
+SET NAMES ujis;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET ujis NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
index c8ba1fedab8..ba72a859351 100644
--- a/mysql-test/r/ctype_utf16.result
+++ b/mysql-test/r/ctype_utf16.result
@@ -2138,6 +2138,29 @@ EXECUTE stmt USING @arg00;
 CONCAT(_utf16'a' COLLATE utf16_unicode_ci, ?)
 aÿ
 DEALLOCATE PREPARE stmt;
+SET NAMES utf8, character_set_connection=utf16;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET utf16 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/ctype_utf16le.result b/mysql-test/r/ctype_utf16le.result
index 93fce843dc8..c7c8c210014 100644
--- a/mysql-test/r/ctype_utf16le.result
+++ b/mysql-test/r/ctype_utf16le.result
@@ -2324,6 +2324,35 @@ DFFFFFDFFFFF9CFFFF9DFFFF9EFFFF
 # End of 5.6 tests
 #
 #
+# Start of 10.0 tests
+#
+SET NAMES utf8, character_set_connection=utf16le;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET utf16le NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
+#
+# Start of 10.0 tests
+#
+#
 # Start of 10.1 tests
 #
 #
diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
index 22b0e9c0fc0..024f8aa7875 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -2237,6 +2237,29 @@ EXECUTE stmt USING @arg00;
 CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?)
 aÿ
 DEALLOCATE PREPARE stmt;
+SET NAMEs utf8, character_set_connection=utf32;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET utf32 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 3da74d12877..7b486f0f443 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -10278,6 +10278,29 @@ SELECT * FROM v1;
 c
 ß
 DROP VIEW v1;
+SET NAMES utf8;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET utf8 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result
index 904f432af20..de9ba321ad9 100644
--- a/mysql-test/r/ctype_utf8mb4.result
+++ b/mysql-test/r/ctype_utf8mb4.result
@@ -3469,6 +3469,29 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
 SET NAMES default;
+SET NAMES utf8mb4;
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_latin1'derived_merge=on';
+CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `t` varchar(10) CHARACTER SET utf8mb4 NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
+c2
+abcdefghi-abcdefghi
+abcdefghi-abcdefghi
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
+c2
+ABCDEFGHI-ABCDEFGHI
+ABCDEFGHI-ABCDEFGHI
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
 #
 # End of 10.0 tests
 #
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 312b36c16ca..6bd12dbe1a6 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2462,5 +2462,55 @@ id	sid	id
 1	NULL	NULL
 2	NULL	NULL
 drop table t1, t2;
+#
+# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN
+#             converted to INNER JOIN with first constant inner table
+#
+CREATE TABLE t1 (
+pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1)
+) engine=MyISAM;
+INSERT INTO t1 VALUES
+(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),
+(14,226,'m','m'),(15,133,'p','p');
+CREATE TABLE t2 (
+pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1)
+) engine=MyISAM;
+INSERT INTO t2 VALUES (10,6,'p','p');
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+RIGHT JOIN
+(t2,t1)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+(t2,t1)
+LEFT JOIN
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL
+SELECT STRAIGHT_JOIN DISTINCT t2.v2
+FROM
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+RIGHT JOIN
+(t2,t1)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+v2
+DROP TABLE t1,t2;
 # end of 5.5 tests
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index a006bafcfc0..faad87468c5 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2473,6 +2473,56 @@ id	sid	id
 1	NULL	NULL
 2	NULL	NULL
 drop table t1, t2;
+#
+# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN
+#             converted to INNER JOIN with first constant inner table
+#
+CREATE TABLE t1 (
+pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1)
+) engine=MyISAM;
+INSERT INTO t1 VALUES
+(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),
+(14,226,'m','m'),(15,133,'p','p');
+CREATE TABLE t2 (
+pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1)
+) engine=MyISAM;
+INSERT INTO t2 VALUES (10,6,'p','p');
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+RIGHT JOIN
+(t2,t1)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+(t2,t1)
+LEFT JOIN
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(`test`.`tb2`.`v1`, NULL)) where 0 order by NULL
+SELECT STRAIGHT_JOIN DISTINCT t2.v2
+FROM
+(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+RIGHT JOIN
+(t2,t1)
+ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+v2
+DROP TABLE t1,t2;
 # end of 5.5 tests
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
diff --git a/mysql-test/r/rename.result b/mysql-test/r/rename.result
index c90aaf24e9b..f019527c951 100644
--- a/mysql-test/r/rename.result
+++ b/mysql-test/r/rename.result
@@ -133,3 +133,7 @@ select * from t2;
 a
 1
 drop table tmp,t2;
+create table t1 (a int) engine=memory;
+rename table t1 to non_existent.t2;
+ERROR 42000: Unknown database 'non_existent'
+drop table t1;
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index d33f1488e4d..03f4b3e1903 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -442,7 +442,7 @@ SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	DEPENDENT SUBQUERY	t3	const	PRIMARY	PRIMARY	4	const	1	
-2	DEPENDENT SUBQUERY	t2	index	NULL	i2	11	NULL	2	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	index	i2	i2	11	NULL	2	Using where; Using index
 DROP TABLE t1,t2,t3;
 #
 # MDEV-7599: in-to-exists chosen after min/max optimization
diff --git a/mysql-test/suite/parts/r/truncate_locked.result b/mysql-test/suite/parts/r/truncate_locked.result
new file mode 100644
index 00000000000..54a3722938e
--- /dev/null
+++ b/mysql-test/suite/parts/r/truncate_locked.result
@@ -0,0 +1,7 @@
+create table t1 (i int) engine=myisam partition by hash(i) partitions 2 ;
+lock table t1 write;
+truncate table t1;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+i	int(11)	YES		NULL	
+drop table t1;
diff --git a/mysql-test/suite/parts/t/truncate_locked.test b/mysql-test/suite/parts/t/truncate_locked.test
new file mode 100644
index 00000000000..4ff1016fb05
--- /dev/null
+++ b/mysql-test/suite/parts/t/truncate_locked.test
@@ -0,0 +1,10 @@
+#
+# MDEV-15551 Server hangs or assertion `strcmp(share->unique_file_name,filename) || share->last_version' fails in test_if_reopen or unexpected ER_LOCK_DEADLOCK
+#
+--source include/have_partition.inc
+create table t1 (i int) engine=myisam partition by hash(i) partitions 2 ;
+lock table t1 write;
+truncate table t1;
+desc t1;
+drop table t1;
+
diff --git a/mysql-test/suite/plugins/r/processlist.result b/mysql-test/suite/plugins/r/processlist.result
index d08ea9d3523..9200c865533 100644
--- a/mysql-test/suite/plugins/r/processlist.result
+++ b/mysql-test/suite/plugins/r/processlist.result
@@ -1,8 +1,6 @@
 create table t1 (a int) engine=innodb;
 start transaction;
 insert t1 values (1);
-state from show engine innodb status
+state from show engine innodb status, must be empty
  
-state from show processlist
-
 drop table t1;
diff --git a/mysql-test/suite/plugins/t/processlist.test b/mysql-test/suite/plugins/t/processlist.test
index 5aacef317b9..39b715b867b 100644
--- a/mysql-test/suite/plugins/t/processlist.test
+++ b/mysql-test/suite/plugins/t/processlist.test
@@ -8,10 +8,12 @@ start transaction;
 insert t1 values (1);
 let id=`select connection_id()`;
 connect con2,localhost,root;
-let s=query_get_value(show engine innodb status,Status,1);
+let $wait_condition=select state='' from information_schema.processlist where id = $id;
+--source include/wait_condition.inc
+replace_regex /\"/-/; #"
+let s=`show engine innodb status`;
 disable_query_log;
-eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status`;
-eval select state as `state from show processlist` from information_schema.processlist where id = $id;
+eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status, must be empty`;
 enable_query_log;
 disconnect con2;
 connection default;
diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test
index d1a597a535e..8508c6c9dc6 100644
--- a/mysql-test/t/bootstrap.test
+++ b/mysql-test/t/bootstrap.test
@@ -106,3 +106,12 @@ use test;
 EOF
 --exec $MYSQLD_BOOTSTRAP_CMD --ignore-db-dirs='some_dir' --ignore-db-dirs='some_dir' < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
 --remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql
+
+#
+# MDEV-13397 MariaDB upgrade fail when using default_time_zone
+#
+--write_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql
+use test;
+EOF
+--exec $MYSQLD_BOOTSTRAP_CMD --default-time-zone=Europe/Moscow < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1
+--remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql
diff --git a/mysql-test/t/ctype_binary.test b/mysql-test/t/ctype_binary.test
index 8da4eaff572..155d8548f77 100644
--- a/mysql-test/t/ctype_binary.test
+++ b/mysql-test/t/ctype_binary.test
@@ -24,6 +24,9 @@ SET NAMES binary;
 --echo #
 SELECT _binary 0x7E, _binary X'7E', _binary B'01111110';
 
+SET NAMES utf8, character_set_connection=binary;
+--source include/ctype_mdev13118.inc
+
 --echo #
 --echo # End of 10.0 tests
 --echo #
diff --git a/mysql-test/t/ctype_eucjpms.test b/mysql-test/t/ctype_eucjpms.test
index d533e38b2a2..ce11a648c35 100644
--- a/mysql-test/t/ctype_eucjpms.test
+++ b/mysql-test/t/ctype_eucjpms.test
@@ -537,6 +537,8 @@ DROP TABLE t1;
 --error ER_INVALID_CHARACTER_STRING
 SELECT _eucjpms 0x8EA0;
 
+SET NAMES eucjpms;
+--source include/ctype_mdev13118.inc
 
 --echo #
 --echo # End of 10.0 tests
diff --git a/mysql-test/t/ctype_euckr.test b/mysql-test/t/ctype_euckr.test
index 33b3e96cba8..71660314279 100644
--- a/mysql-test/t/ctype_euckr.test
+++ b/mysql-test/t/ctype_euckr.test
@@ -197,3 +197,14 @@ set collation_connection=euckr_bin;
 --echo # End of 5.6 tests
 --echo #
 
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+SET NAMES utf8, character_set_connection=euckr;
+--source include/ctype_mdev13118.inc
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
diff --git a/mysql-test/t/ctype_gbk.test b/mysql-test/t/ctype_gbk.test
index 022d4a3705a..351c323d711 100644
--- a/mysql-test/t/ctype_gbk.test
+++ b/mysql-test/t/ctype_gbk.test
@@ -199,6 +199,9 @@ let $ctype_unescape_combinations=selected;
 SET NAMES gbk;
 --source include/ctype_E05C.inc
 
+SET NAMES utf8, character_set_connection=gbk;
+--source include/ctype_mdev13118.inc
+
 --echo #
 --echo # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant
 --echo #
diff --git a/mysql-test/t/ctype_latin1.test b/mysql-test/t/ctype_latin1.test
index 3455b0437c1..2d7f186f3ec 100644
--- a/mysql-test/t/ctype_latin1.test
+++ b/mysql-test/t/ctype_latin1.test
@@ -260,6 +260,9 @@ SELECT * FROM v1;
 DROP VIEW v1;
 DROP TABLE t1;
 
+SET NAMES latin1;
+--source include/ctype_mdev13118.inc
+
 
 --echo #
 --echo # End of 10.0 tests
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index 0055a2f20e2..0d29ce9378e 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -988,6 +988,10 @@ DROP TABLE t1;
 SET optimizer_switch=@save_optimizer_switch;
 
 
+SET NAMES utf8, character_set_connection=ucs2;
+--source include/ctype_mdev13118.inc
+
+
 --echo #
 --echo # End of 10.0 tests
 --echo #
diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test
index 3f444580b13..93c83963283 100644
--- a/mysql-test/t/ctype_ujis.test
+++ b/mysql-test/t/ctype_ujis.test
@@ -1368,6 +1368,10 @@ DROP TABLE t1;
 SELECT _ujis 0x8EA0;
 
 
+SET NAMES ujis;
+--source include/ctype_mdev13118.inc
+
+
 --echo #
 --echo # End of 10.0 tests
 --echo #
diff --git a/mysql-test/t/ctype_utf16.test b/mysql-test/t/ctype_utf16.test
index 4b95257cbde..1febe3d8aea 100644
--- a/mysql-test/t/ctype_utf16.test
+++ b/mysql-test/t/ctype_utf16.test
@@ -866,6 +866,11 @@ SET @arg00=_binary 0x00FF;
 EXECUTE stmt USING @arg00;
 DEALLOCATE PREPARE stmt;
 
+
+SET NAMES utf8, character_set_connection=utf16;
+--source include/ctype_mdev13118.inc
+
+
 --echo #
 --echo # End of 10.0 tests
 --echo #
diff --git a/mysql-test/t/ctype_utf16le.test b/mysql-test/t/ctype_utf16le.test
index 42017f9a635..5e29408cbd0 100644
--- a/mysql-test/t/ctype_utf16le.test
+++ b/mysql-test/t/ctype_utf16le.test
@@ -745,6 +745,19 @@ SET NAMES utf8, collation_connection=utf16le_bin;
 --echo # End of 5.6 tests
 --echo #
 
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+
+SET NAMES utf8, character_set_connection=utf16le;
+--source include/ctype_mdev13118.inc
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
 --echo #
 --echo # Start of 10.1 tests
 --echo #
diff --git a/mysql-test/t/ctype_utf32.test b/mysql-test/t/ctype_utf32.test
index 3bef1524e5b..bf822291a21 100644
--- a/mysql-test/t/ctype_utf32.test
+++ b/mysql-test/t/ctype_utf32.test
@@ -979,6 +979,14 @@ SET @arg00=_binary 0x00FF;
 EXECUTE stmt USING @arg00;
 DEALLOCATE PREPARE stmt;
 
+
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET NAMEs utf8, character_set_connection=utf32;
+--source include/ctype_mdev13118.inc
+
+
 --echo #
 --echo # End of 10.0 tests
 --echo #
diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test
index be17eb461be..51b6d63ee2f 100644
--- a/mysql-test/t/ctype_utf8.test
+++ b/mysql-test/t/ctype_utf8.test
@@ -1881,6 +1881,13 @@ SELECT * FROM v1;
 DROP VIEW v1;
 
 
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET NAMES utf8;
+--source include/ctype_mdev13118.inc
+
+
 --echo #
 --echo # End of 10.0 tests
 --echo #
diff --git a/mysql-test/t/ctype_utf8mb4.test b/mysql-test/t/ctype_utf8mb4.test
index af4c169091a..ffc098ff938 100644
--- a/mysql-test/t/ctype_utf8mb4.test
+++ b/mysql-test/t/ctype_utf8mb4.test
@@ -1950,6 +1950,14 @@ DROP TABLE t1;
 
 SET NAMES default;
 
+
+#
+# MDEV-13118 Wrong results with LOWER and UPPER and subquery
+#
+SET NAMES utf8mb4;
+--source include/ctype_mdev13118.inc
+
+
 --echo #
 --echo # End of 10.0 tests
 --echo #
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 34b0551a32e..6d20c089bd9 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1992,6 +1992,54 @@ select * from t1 t
  on t.id=r.id ;
 drop table t1, t2;
 
+--echo #
+--echo # MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN
+--echo #             converted to INNER JOIN with first constant inner table
+--echo #
+
+CREATE TABLE t1 (
+  pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1)
+) engine=MyISAM;
+INSERT INTO t1 VALUES
+  (8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),
+  (14,226,'m','m'),(15,133,'p','p');
+
+CREATE TABLE t2 (
+  pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1)
+) engine=MyISAM;
+INSERT INTO t2 VALUES (10,6,'p','p');
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+  (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+  RIGHT JOIN
+  (t2,t1)
+  ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+
+EXPLAIN EXTENDED
+SELECT STRAIGHT_JOIN t2.v2
+FROM
+    (t2,t1)
+    LEFT JOIN
+    (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+   ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+
+SELECT STRAIGHT_JOIN DISTINCT t2.v2
+FROM
+  (t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2)
+  RIGHT JOIN
+  (t2,t1)
+  ON t1.pk = t2.pk AND t2.v2 = tb1.v1
+WHERE tb1.pk = 40
+ORDER BY tb1.i1;
+
+DROP TABLE t1,t2;
+
 --echo # end of 5.5 tests
 
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/rename.test b/mysql-test/t/rename.test
index 67732d5b5b9..215ecbcbb18 100644
--- a/mysql-test/t/rename.test
+++ b/mysql-test/t/rename.test
@@ -141,3 +141,10 @@ select * from tmp;
 select * from t2;
 drop table tmp,t2;
 
+#
+# MDEV-11741 handler::ha_reset(): Assertion `bitmap_is_set_all(&table->s->all_set)' failed or server crash in mi_reset or buffer overrun or unexpected ER_CANT_REMOVE_ALL_FIELDS
+#
+create table t1 (a int) engine=memory;
+--error ER_BAD_DB_ERROR
+rename table t1 to non_existent.t2;
+drop table t1;