Automatic merge with 5.5

48 files changed, 1210 insertions, 311 deletions

diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result
index a95975f1097..3852da5d4b0 100644
--- a/mysql-test/r/case.result
+++ b/mysql-test/r/case.result
@@ -220,6 +220,10 @@ a	d
 3	11120436154190595086
 drop table t1, t2;
 End of 5.0 tests
+CREATE TABLE t1(a YEAR);
+SELECT 1 FROM t1 WHERE a=1 AND CASE 1 WHEN a THEN 1 ELSE 1 END;
+1
+DROP TABLE t1;
 create table t1 (f1 time);
 insert t1 values ('00:00:00'),('00:01:00');
 select case t1.f1 when '00:00:00' then 1 end from t1;
diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result
index 6ee01349022..cdd234f1478 100644
--- a/mysql-test/r/compare.result
+++ b/mysql-test/r/compare.result
@@ -96,3 +96,7 @@ SELECT * FROM t1 WHERE a > '2008-01-01' AND a = '0000-00-00';
 a
 DROP TABLE t1;
 End of 5.0 tests
+CREATE TABLE t1(a INT ZEROFILL);
+SELECT 1 FROM t1 WHERE t1.a IN (1, t1.a) AND t1.a=2;
+1
+DROP TABLE t1;
diff --git a/mysql-test/r/create-big.result b/mysql-test/r/create-big.result
index 34293d7e5cd..4cce5d8618c 100644
--- a/mysql-test/r/create-big.result
+++ b/mysql-test/r/create-big.result
@@ -53,8 +53,8 @@ set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
 create table t1 select 1 as i;;
 set debug_sync='now WAIT_FOR parked';
 alter table t3 rename to t1;
-ERROR 42S01: Table 't1' already exists
 set debug_sync='now SIGNAL go';
+ERROR 42S01: Table 't1' already exists
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -65,8 +65,8 @@ set debug_sync='create_table_select_before_create SIGNAL parked WAIT_FOR go';
 create table t1 select 1 as i;;
 set debug_sync='now WAIT_FOR parked';
 alter table t3 rename to t1, add k int;
-ERROR 42S01: Table 't1' already exists
 set debug_sync='now SIGNAL go';
+ERROR 42S01: Table 't1' already exists
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 1cb4b48ed69..c897108f793 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -2039,6 +2039,8 @@ hex(concat(a))	a
 30303030303030303030303030303031303030302E31	00000000000000010000.1
 drop table t1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 select hex(concat(a)) from t1;
 hex(concat(a))
@@ -2352,6 +2354,8 @@ hex(a)
 drop table t1;
 drop view v1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index 3d695a87701..5323469dff3 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -2431,6 +2431,8 @@ hex(concat(a))	a
 30303030303030303030303030303031303030302E31	00000000000000010000.1
 drop table t1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 select hex(concat(a)) from t1;
 hex(concat(a))
@@ -2744,6 +2746,8 @@ hex(a)
 drop table t1;
 drop view v1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index e2169e4e4e2..ae459fce8eb 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -2458,6 +2458,8 @@ hex(concat(a))	a
 30303030303030303030303030303031303030302E31	00000000000000010000.1
 drop table t1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 select hex(concat(a)) from t1;
 hex(concat(a))
@@ -2771,6 +2773,8 @@ hex(a)
 drop table t1;
 drop view v1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 501b9cb611b..d463ce87c92 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -3341,6 +3341,8 @@ hex(concat(a))	a
 00300030003000300030003000300030003000300030003000300030003000310030003000300030002E0031	00000000000000010000.1
 drop table t1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 select hex(concat(a)) from t1;
 hex(concat(a))
@@ -3654,6 +3656,8 @@ hex(a)
 drop table t1;
 drop view v1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
diff --git a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
index 03f60261450..6cf24d768f7 100644
--- a/mysql-test/r/ctype_utf32.result
+++ b/mysql-test/r/ctype_utf32.result
@@ -1182,5 +1182,11 @@ a	256
 Warnings:
 Warning	1260	Row 1 was cut by GROUP_CONCAT()
 #
+# incorrect charset for val_str_ascii
+#
+SELECT '2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second;
+'2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second
+2010-10-10 10:10:10
+#
 # End of 5.5 tests
 #
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 4dd9d787528..69e32977103 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -4197,6 +4197,8 @@ hex(concat(a))	a
 30303030303030303030303030303031303030302E31	00000000000000010000.1
 drop table t1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 select hex(concat(a)) from t1;
 hex(concat(a))
@@ -4510,6 +4512,8 @@ hex(a)
 drop table t1;
 drop view v1;
 create table t1 (a year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (1);
 create view v1(a) as select concat(a) from t1;
 show columns from v1;
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index e081b56e6ce..919e29f185e 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -287,7 +287,7 @@ WHERE t1.f1 GROUP BY t1.f1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 PREPARE stmt FROM
 'EXPLAIN SELECT 1 FROM t1
  WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a
@@ -297,12 +297,12 @@ EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 DEALLOCATE PREPARE stmt;
 PREPARE stmt FROM
 'EXPLAIN SELECT 1 FROM t1
@@ -313,12 +313,12 @@ EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	a	system	NULL	NULL	NULL	NULL	1	
-2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	
+2	SUBQUERY	t1	fulltext	f1_2,f1	f1	0		1	Using where
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 End of 5.1 tests.
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index c38201f6d9f..65323257300 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1527,6 +1527,8 @@ DROP TABLE t1;
 # Bug#43668: Wrong comparison and MIN/MAX for YEAR(2)
 #
 create table t1 (f1 year(2), f2 year(4), f3 date, f4 datetime);
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values
 (98,1998,19980101,"1998-01-01 00:00:00"),
 (00,2000,20000101,"2000-01-01 00:00:01"),
@@ -1838,7 +1840,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	range	a	a	4	NULL	4	100.00	Using where; Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<expr_cache><<cache>(1),<cache>(2)>(<in_optimizer>(<cache>((1,2)),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10))
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<cache>(<in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10))
 SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
 MAX(a)
 NULL
diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result
index 6485793d0e1..67ea44f187a 100644
--- a/mysql-test/r/func_group_innodb.result
+++ b/mysql-test/r/func_group_innodb.result
@@ -146,19 +146,6 @@ count(*)	min(7)	max(7)
 0	NULL	NULL
 drop table t1m, t1i, t2m, t2i;
 #
-# Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN
-#
-CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT,
-KEY(b, c, a(765))) ENGINE=INNODB;
-INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0);
-SELECT MIN(c) FROM t1 GROUP BY b;
-MIN(c)
-0
-EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	b	263	NULL	4	Using index for group-by
-DROP TABLE t1;
-#
 # Bug #57954: BIT_AND function returns incorrect results when 
 #   semijoin=on
 CREATE TABLE c (
@@ -230,4 +217,18 @@ SELECT member_id_to, COUNT(*) FROM t1 WHERE r_date =
 member_id_to	COUNT(*)
 518491	2
 DROP TABLE t1;
+# End of test  BUG#12713907
+#
+# Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN
+#
+CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT,
+KEY(b, c, a(765))) ENGINE=INNODB;
+INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0);
+SELECT MIN(c) FROM t1 GROUP BY b;
+MIN(c)
+0
+EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	b	263	NULL	4	Using index for group-by
+DROP TABLE t1;
 End of 5.5 tests
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 95caa6853b8..ce0793b70a6 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2403,7 +2403,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2421,7 +2421,7 @@ AND t1_outer1.b = t1_outer2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1_outer1	ref	a	a	5	const	1	Using where; Using index
 1	PRIMARY	t1_outer2	index	NULL	a	10	NULL	15	Using where; Using index; Using join buffer (flat, BNL join)
-2	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x
 FROM t1 AS t1_outer) x2 FROM t1 AS t1_outer2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -2757,7 +2757,7 @@ EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
-x	x	x	x	x	x	x	x	x	Using index
+x	x	x	x	x	x	x	x	x	Using where; Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
@@ -2829,7 +2829,7 @@ EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
-x	x	x	x	x	x	x	x	x	Using index
+x	x	x	x	x	x	x	x	x	Using where; Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
@@ -2908,7 +2908,7 @@ EXPLAIN
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 x	x	x	x	x	x	x	x	x	Impossible WHERE noticed after reading const tables
-x	x	x	x	x	x	x	x	x	Using index
+x	x	x	x	x	x	x	x	x	Using where; Using index
 SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0);
 MIN( a )
 NULL
diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result
index b8eda092291..92bcb2e88f0 100644
--- a/mysql-test/r/index_merge_innodb.result
+++ b/mysql-test/r/index_merge_innodb.result
@@ -777,4 +777,19 @@ commit;
 select * from t1 where t1.zone_id=830 AND modified=9;
 pk	zone_id	modified
 drop table t0, t1;
+#
+# MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join
+#
+CREATE TABLE t1 (
+a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d');
+SELECT ta.* FROM t1 AS ta, t1 AS tb
+WHERE ( tb.b != ta.b OR tb.a = ta.a )
+AND ( tb.b = ta.c OR tb.b = ta.b );
+a	b	c
+8	v	v
+8	m	m
+9	d	d
+DROP TABLE t1;
 set optimizer_switch= @optimizer_switch_save;
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result
index e215caadb1c..e63afeab126 100644
--- a/mysql-test/r/key.result
+++ b/mysql-test/r/key.result
@@ -599,7 +599,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
+2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 SELECT 1 as RES FROM t1 AS t1_outer WHERE 
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 RES
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index 3a5212818ef..f4242f17a14 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -679,7 +679,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Distinct
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
 SELECT DISTINCT a AS field1 FROM t1, t2 
 WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) 
 HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
diff --git a/mysql-test/r/mdev375.result b/mysql-test/r/mdev375.result
new file mode 100644
index 00000000000..32580804686
--- /dev/null
+++ b/mysql-test/r/mdev375.result
@@ -0,0 +1,14 @@
+SET GLOBAL log_warnings=4;
+SET GLOBAL max_connections=2;
+SELECT 1;
+1
+1
+SELECT 2;
+2
+2
+ERROR HY000: Too many connections
+SELECT 0;
+0
+0
+SET GLOBAL log_warnings=default;
+SET GLOBAL max_connections=default;
diff --git a/mysql-test/r/mysqlbinlog-innodb.result b/mysql-test/r/mysqlbinlog-innodb.result
index fed2c8a8651..41d15bc32d3 100644
--- a/mysql-test/r/mysqlbinlog-innodb.result
+++ b/mysql-test/r/mysqlbinlog-innodb.result
@@ -34,7 +34,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 INSERT INTO t1 VALUES (1)
 /*!*/;
@@ -65,7 +65,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use foo/*!*/;
+use `foo`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 INSERT INTO t1 VALUES (1)
 /*!*/;
diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result
index 774761a0229..33904dfd9bd 100644
--- a/mysql-test/r/mysqlbinlog.result
+++ b/mysql-test/r/mysqlbinlog.result
@@ -18,7 +18,7 @@ flush logs;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -111,7 +111,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`word`)
 /*!*/;
@@ -202,7 +202,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 insert into t1 values ("Alas")
 /*!*/;
@@ -219,7 +219,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -312,7 +312,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`word`)
 /*!*/;
@@ -403,7 +403,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 insert into t1 values ("Alas")
 /*!*/;
@@ -427,7 +427,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1108844555/*!*/;
 insert t1 values (1)
 /*!*/;
@@ -445,7 +445,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1108844555/*!*/;
 insert t1 values (1)
 /*!*/;
@@ -498,7 +498,7 @@ ERROR 42000: PROCEDURE test.p1 does not exist
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -548,7 +548,7 @@ flush logs;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -725,7 +725,7 @@ FLUSH LOGS;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1253783037/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -825,7 +825,7 @@ BEGIN
 SET TIMESTAMP=1266652094/*!*/;
 SavePoint mixed_cases
 /*!*/;
-use db1/*!*/;
+use `db1`/*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 INSERT INTO db1.t2 VALUES("in savepoint mixed_cases")
 /*!*/;
diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result
index 52a589ca7c1..806cf74479e 100644
--- a/mysql-test/r/mysqlbinlog2.result
+++ b/mysql-test/r/mysqlbinlog2.result
@@ -19,7 +19,7 @@ insert into t1 values(null, "f");
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -103,7 +103,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=1/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
@@ -172,7 +172,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -209,7 +209,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -263,7 +263,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -289,7 +289,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -326,7 +326,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -369,7 +369,7 @@ flush logs;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -445,7 +445,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
@@ -474,7 +474,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=1/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
@@ -535,7 +535,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
@@ -564,7 +564,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -605,7 +605,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
@@ -622,7 +622,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -720,7 +720,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -761,7 +761,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
@@ -778,7 +778,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -820,7 +820,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -904,7 +904,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=1/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
@@ -972,7 +972,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -1009,7 +1009,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -1062,7 +1062,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -1088,7 +1088,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -1125,7 +1125,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -1167,7 +1167,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -1243,7 +1243,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
@@ -1272,7 +1272,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=1/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
@@ -1333,7 +1333,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
@@ -1361,7 +1361,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -1402,7 +1402,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
@@ -1419,7 +1419,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -1516,7 +1516,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=3/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
@@ -1557,7 +1557,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET INSERT_ID=6/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
@@ -1574,7 +1574,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -1616,7 +1616,7 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 ROLLBACK/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row.result b/mysql-test/r/mysqlbinlog_row.result
index 7ae1ea36899..ce46b7ea898 100644
--- a/mysql-test/r/mysqlbinlog_row.result
+++ b/mysql-test/r/mysqlbinlog_row.result
@@ -336,7 +336,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row_innodb.result b/mysql-test/r/mysqlbinlog_row_innodb.result
index 00a3eb79b64..63b64489a37 100644
--- a/mysql-test/r/mysqlbinlog_row_innodb.result
+++ b/mysql-test/r/mysqlbinlog_row_innodb.result
@@ -2253,7 +2253,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -3876,7 +3876,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -4243,7 +4243,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -4804,7 +4804,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row_myisam.result b/mysql-test/r/mysqlbinlog_row_myisam.result
index 7b0112d0a91..6e8c7cd64f9 100644
--- a/mysql-test/r/mysqlbinlog_row_myisam.result
+++ b/mysql-test/r/mysqlbinlog_row_myisam.result
@@ -2253,7 +2253,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -3898,7 +3898,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -4271,7 +4271,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
@@ -4842,7 +4842,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row_trans.result b/mysql-test/r/mysqlbinlog_row_trans.result
index 5c96db7d19b..33c997d76a7 100644
--- a/mysql-test/r/mysqlbinlog_row_trans.result
+++ b/mysql-test/r/mysqlbinlog_row_trans.result
@@ -132,7 +132,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 # at #
 #010909  4:46:40 server id 1  end_log_pos # 	Query	thread_id=#	exec_time=#	error_code=0
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=#/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index ad55bfa3003..63df79160cc 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -697,7 +697,7 @@ The following options may be given as the first argument:
  partial matching
  --rpl-recovery-rank=# 
  Unused, will be removed
- --safe-mode         Skip some optimize stages (for testing).
+ --safe-mode         Skip some optimize stages (for testing). Deprecated.
  --safe-user-create  Don't allow new user creation by the user who has no
  write privileges to the mysql.user table.
  --secure-auth       Disallow authentication for accounts that have old
@@ -737,6 +737,9 @@ The following options may be given as the first argument:
  --slave-load-tmpdir=name 
  The location where the slave should put its temporary
  files when replicating a LOAD DATA INFILE command
+ --slave-max-allowed-packet=# 
+ The maximum packet length to sent successfully from the
+ master to slave.
  --slave-net-timeout=# 
  Number of seconds to wait for more data from a
  master/slave connection before aborting the read
@@ -1057,6 +1060,7 @@ skip-show-database FALSE
 skip-slave-start FALSE
 slave-compressed-protocol FALSE
 slave-exec-mode STRICT
+slave-max-allowed-packet 1073741824
 slave-net-timeout 3600
 slave-skip-errors (No default value)
 slave-sql-verify-checksum TRUE
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index ff591c6cb69..7b650addd3d 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -2073,7 +2073,7 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `a` varchar(30)
+  `a` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v2`*/;
@@ -2167,7 +2167,7 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `a` int(11)
+  `a` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v1`*/;
@@ -2241,7 +2241,7 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `a` varchar(30)
+  `a` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v2`*/;
@@ -2355,9 +2355,9 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `a` int(11),
-  `b` int(11),
-  `c` varchar(30)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 DROP TABLE IF EXISTS `v2`;
@@ -2365,7 +2365,7 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `a` int(11)
+  `a` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 DROP TABLE IF EXISTS `v3`;
@@ -2373,9 +2373,9 @@ DROP TABLE IF EXISTS `v3`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v3` (
-  `a` int(11),
-  `b` int(11),
-  `c` varchar(30)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v1`*/;
@@ -3112,9 +3112,9 @@ DROP TABLE IF EXISTS `v0`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v0` (
-  `a` int(11),
-  `b` varchar(32),
-  `c` varchar(32)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 DROP TABLE IF EXISTS `v1`;
@@ -3122,9 +3122,9 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `a` int(11),
-  `b` varchar(32),
-  `c` varchar(32)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 DROP TABLE IF EXISTS `v2`;
@@ -3132,9 +3132,9 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `a` int(11),
-  `b` varchar(32),
-  `c` varchar(32)
+  `a` tinyint NOT NULL,
+  `b` tinyint NOT NULL,
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
@@ -3514,7 +3514,7 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `id` int(11)
+  `id` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
@@ -3574,7 +3574,7 @@ USE `mysqldump_views`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `nasishnasifu` (
-  `id` bigint(20) unsigned
+  `id` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
@@ -3967,7 +3967,7 @@ DROP TABLE IF EXISTS `v2`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v2` (
-  `c` int(11)
+  `c` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 /*!50001 DROP TABLE IF EXISTS `v2`*/;
@@ -4384,7 +4384,7 @@ DROP TABLE IF EXISTS `v1`;
 SET @saved_cs_client     = @@character_set_client;
 SET character_set_client = utf8;
 /*!50001 CREATE TABLE `v1` (
-  `id` int(11)
+  `id` tinyint NOT NULL
 ) ENGINE=MyISAM */;
 SET character_set_client = @saved_cs_client;
 
diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result
index 25345227d59..fdb3029059f 100644
--- a/mysql-test/r/mysqltest.result
+++ b/mysql-test/r/mysqltest.result
@@ -690,6 +690,7 @@ Got one of the listed errors
 insert into t1 values ("Abcd");
 Got one of the listed errors
 garbage;
+SELECT * FROM non_existing_table;
 drop table t2;
 create table t1 ( f1 char(10));
 insert into t1 values ("Abcd");
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index d320b5b669f..53fed519a15 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1813,3 +1813,135 @@ Warnings:
 Note	1003	select `test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b`
 drop table t1,t2;
 End of 5.2 tests
+#
+# Bug mdev-449: ORDER BY with small sort_buffer_size
+#
+CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200));
+INSERT INTO t1(f1, f2) VALUES 
+(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"),
+(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"),
+(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"),
+(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"),
+(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"),
+(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"),
+(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"),
+(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"),
+(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"),
+(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"),
+(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"),
+(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"),
+(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"),
+(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"),
+(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"),
+(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"),
+(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"),
+(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"),
+(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"),
+(96,"96"),(97,"97"),(98,"98"),(99,"99");
+set @save_sort_buffer_size= @@sort_buffer_size;
+set sort_buffer_size= 2000;
+SELECT * FROM t1 ORDER BY f1 DESC, f0;
+f0	f1	f2
+100	99	99
+99	98	98
+98	97	97
+97	96	96
+96	95	95
+95	94	94
+94	93	93
+93	92	92
+92	91	91
+91	90	90
+90	89	89
+89	88	88
+88	87	87
+87	86	86
+86	85	85
+85	84	84
+84	83	83
+83	82	82
+82	81	81
+81	80	80
+80	79	79
+79	78	78
+78	77	77
+77	76	76
+76	75	75
+75	74	74
+74	73	73
+73	72	72
+72	71	71
+71	70	70
+70	69	69
+69	68	68
+68	67	67
+67	66	66
+66	65	65
+65	64	64
+64	63	63
+63	62	62
+62	61	61
+61	60	60
+60	59	59
+59	58	58
+58	57	57
+57	56	56
+56	55	55
+55	54	54
+54	53	53
+53	52	52
+52	51	51
+51	50	50
+50	49	49
+49	48	48
+48	47	47
+47	46	46
+46	45	45
+45	44	44
+44	43	43
+43	42	42
+42	41	41
+41	40	40
+40	39	39
+39	38	38
+38	37	37
+37	36	36
+36	35	35
+35	34	34
+34	33	33
+33	32	32
+32	31	31
+31	30	30
+30	29	29
+29	28	28
+28	27	27
+27	26	26
+26	25	25
+25	24	24
+24	23	23
+23	22	22
+22	21	21
+21	20	20
+20	19	19
+19	18	18
+18	17	17
+17	16	16
+16	15	15
+15	14	14
+14	13	13
+13	12	12
+12	11	11
+11	10	10
+10	9	9
+9	8	8
+8	7	7
+7	6	6
+6	5	5
+5	4	4
+4	3	3
+3	2	2
+2	1	1
+1	0	0
+set sort_buffer_size= @save_sort_buffer_size;
+DROP TABLE t1;
+End of 5.3 tests
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 431c5dda116..40586b8d54b 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2473,3 +2473,17 @@ SELECT * FROM vtmp;
 1
 DROP VIEW vtmp;
 DROP TABLE t1;
+#
+# MDEV-365 "Got assertion when doing alter table on a partition"
+#
+CREATE TABLE t1 ( i INT ) ENGINE=Aria PARTITION BY HASH(i) PARTITIONS 2;
+INSERT INTO t1 VALUES (1),(2),(2),(3),(4);
+ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
+SELECT * from t1 order by i;
+i
+1
+2
+2
+3
+4
+DROP TABLE t1;
diff --git a/mysql-test/r/partition_cache.result b/mysql-test/r/partition_cache.result
index ac2da9bb78a..cd579d00952 100644
--- a/mysql-test/r/partition_cache.result
+++ b/mysql-test/r/partition_cache.result
@@ -27,7 +27,7 @@ a
 3
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 drop table t1;
 commit;
 create table t1 (a int not null) PARTITION BY KEY (a) PARTITIONS 3;
@@ -50,7 +50,7 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	3
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	0
@@ -69,7 +69,7 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	6
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	0
@@ -93,14 +93,14 @@ a
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	2
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	0
+Qcache_hits	1
 commit;
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	2
 drop table t3,t2,t1;
 CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) PARTITION BY HASH (id) PARTITIONS 3;
 select count(*) from t1;
@@ -164,7 +164,7 @@ count(*)
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 connection connection1
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 count(*)
@@ -197,9 +197,9 @@ count(*)
 2
 show status like "Qcache_queries_in_cache";
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 show status like "Qcache_hits";
 Variable_name	Value
-Qcache_hits	0
+Qcache_hits	1
 set @@global.query_cache_size = @save_query_cache_size;
 drop table t2;
diff --git a/mysql-test/r/partition_cache_innodb.result b/mysql-test/r/partition_cache_innodb.result
new file mode 100644
index 00000000000..0d0abbb096c
--- /dev/null
+++ b/mysql-test/r/partition_cache_innodb.result
@@ -0,0 +1,151 @@
+SET SESSION STORAGE_ENGINE = innodb;
+drop table if exists t1;
+set @save_query_cache_size = @@global.query_cache_size;
+# Test that partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = InnoDB,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = InnoDB,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+# Test that sub-partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+)
+PARTITION BY RANGE (TO_DAYS(created_at))
+subpartition by hash(cool) subpartitions 3 (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+SUBPARTITION BY HASH (cool)
+SUBPARTITIONS 3
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = InnoDB,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = InnoDB,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+#
+# MySQL bug#53775 Query on partitioned table returns cached result
+# from previous transaction
+#
+flush query cache;
+flush status;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+INSERT INTO t1 VALUES (1, now(), 0);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+BEGIN;
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+BEGIN;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+drop table t1;
+set @@global.query_cache_size = @save_query_cache_size;
diff --git a/mysql-test/r/partition_cache_myisam.result b/mysql-test/r/partition_cache_myisam.result
new file mode 100644
index 00000000000..0b617c03590
--- /dev/null
+++ b/mysql-test/r/partition_cache_myisam.result
@@ -0,0 +1,153 @@
+SET SESSION STORAGE_ENGINE = myisam;
+drop table if exists t1;
+set @save_query_cache_size = @@global.query_cache_size;
+# Test that partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = MyISAM,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = MyISAM,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+# Test that sub-partitions works with query cache
+flush query cache;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+)
+PARTITION BY RANGE (TO_DAYS(created_at))
+subpartition by hash(cool) subpartitions 3 (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  `created_at` datetime NOT NULL,
+  `cool` tinyint(4) DEFAULT '0'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY RANGE (TO_DAYS(created_at))
+SUBPARTITION BY HASH (cool)
+SUBPARTITIONS 3
+(PARTITION month_2010_4 VALUES LESS THAN (734258) ENGINE = MyISAM,
+ PARTITION month_2010_5 VALUES LESS THAN (734289) ENGINE = MyISAM,
+ PARTITION month_max VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
+INSERT INTO t1 VALUES (1, now(), 0);
+flush status;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+0
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+#
+# MySQL bug#53775 Query on partitioned table returns cached result
+# from previous transaction
+#
+flush query cache;
+flush status;
+SET GLOBAL query_cache_size=1024*1024*512;
+CREATE TABLE `t1` (
+`id` int(11) NOT NULL ,
+`created_at` datetime NOT NULL,
+`cool` tinyint default 0
+);
+ALTER TABLE t1 PARTITION BY RANGE (TO_DAYS(created_at)) (
+PARTITION month_2010_4 VALUES LESS THAN (734258),
+PARTITION month_2010_5 VALUES LESS THAN (734289),
+PARTITION month_max VALUES LESS THAN MAXVALUE
+);
+INSERT INTO t1 VALUES (1, now(), 0);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+BEGIN;
+UPDATE `t1` SET `cool` = 1 WHERE `id` = 1;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+Warnings:
+Warning	1196	Some non-transactional changed tables couldn't be rolled back
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+BEGIN;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+ROLLBACK;
+SELECT cool FROM `t1` WHERE (`t1`.id = 1) LIMIT 1;
+cool
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+set @@global.query_cache_size = @save_query_cache_size;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 7629775b892..95217d9716a 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -3056,7 +3056,7 @@ DROP TABLE t1;
 
 End of 5.1 tests.
 #
-# LP bug#1001500 Crash on the second execution of the PS for
+# lp:1001500 Crash on the second execution of the PS for
 # a query with degenerated conjunctive condition
 # (see also mysql bug#12582849)
 #
@@ -3996,108 +3996,4 @@ Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 deallocate prepare st;
 drop table t1;
-#
-# LP bug#993459 Execution of PS for a query with GROUP BY
-# returns wrong result (see also mysql bug#13805127)
-#
-PREPARE s1 FROM 
-"
-SELECT c1, t2.c2, count(c3)
-FROM
-  (
-  SELECT 3 as c2 FROM dual WHERE @x = 1
-  UNION
-  SELECT 2       FROM dual WHERE @x = 1 OR @x = 2
-  ) AS t1,
-  (
-  SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
-  UNION
-  SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
-  UNION
-  SELECT '2012-03-01 01:00:00',       2,       1       FROM dual
-  ) AS t2
-WHERE t2.c2 = t1.c2
-GROUP BY c1, c2
-";
-
-SET @x = 1;
-SELECT c1, t2.c2, count(c3)
-FROM
-(
-SELECT 3 as c2 FROM dual WHERE @x = 1
-UNION
-SELECT 2       FROM dual WHERE @x = 1 OR @x = 2
-) AS t1,
-(
-SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
-UNION
-SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
-UNION
-SELECT '2012-03-01 01:99345900:00',       2,       1       FROM dual
-) AS t2
-WHERE t2.c2 = t1.c2
-GROUP BY c1, c2;
-c1	c2	count(c3)
-2012-03-01 01:00:00	3	1
-2012-03-01 01:99345900:00	2	1
-2012-03-01 02:00:00	3	1
-
-EXECUTE s1;
-c1	c2	count(c3)
-2012-03-01 01:00:00	2	1
-2012-03-01 01:00:00	3	1
-2012-03-01 02:00:00	3	1
-
-SET @x = 2;
-SELECT c1, t2.c2, count(c3)
-FROM
-(
-SELECT 3 as c2 FROM dual WHERE @x = 1
-UNION
-SELECT 2       FROM dual WHERE @x = 1 OR @x = 2
-) AS t1,
-(
-SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
-UNION
-SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
-UNION
-SELECT '2012-03-01 01:00:00',       2,       1       FROM dual
-) AS t2
-WHERE t2.c2 = t1.c2
-GROUP BY c1, c2;
-c1	c2	count(c3)
-2012-03-01 01:00:00	2	1
-
-EXECUTE s1;
-c1	c2	count(c3)
-2012-03-01 01:00:00	2	1
-
-SET @x = 1;
-SELECT c1, t2.c2, count(c3)
-FROM
-(
-SELECT 3 as c2 FROM dual WHERE @x = 1
-UNION
-SELECT 2       FROM dual WHERE @x = 1 OR @x = 2
-) AS t1,
-(
-SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
-UNION
-SELECT '2012-03-01 02:00:00',       3,       2       FROM dual
-UNION
-SELECT '2012-03-01 01:00:00',       2,       1       FROM dual
-) AS t2
-WHERE t2.c2 = t1.c2
-GROUP BY c1, c2;
-c1	c2	count(c3)
-2012-03-01 01:00:00	2	1
-2012-03-01 01:00:00	3	1
-2012-03-01 02:00:00	3	1
-
-EXECUTE s1;
-c1	c2	count(c3)
-2012-03-01 01:00:00	2	1
-2012-03-01 01:00:00	3	1
-2012-03-01 02:00:00	3	1
-DEALLOCATE PREPARE s1;
 # End of 5.3 tests
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index d49c67a35fd..293400ece2f 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -7811,9 +7811,12 @@ DROP FUNCTION f1;
 # -- End of 5.1 tests
 # ------------------------------------------------------------------
 #
-# LP bug#993459 Execution of PS for a query with GROUP BY
+# lp:993459 Execution of PS for a query with GROUP BY
 # returns wrong result (see also mysql bug#13805127)
 #
+
+# Bug#13805127: Stored program cache produces wrong result in same THD
+
 CREATE PROCEDURE p1(x INT UNSIGNED)
 BEGIN
 SELECT c1, t2.c2, count(c3)
@@ -7850,7 +7853,7 @@ c1	c2	count(c3)
 2012-03-01 02:00:00	3	1
 DROP PROCEDURE p1;
 #
-# LP bug#1002157 : testing stored function
+# lp:1002157 : testing stored function
 # bug#62125        result for null incorrectly yields 1292 warning.
 #
 DROP FUNCTION IF EXISTS f1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 1863cd09c38..f775336299b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -5966,7 +5966,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6127,7 +6127,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6612,7 +6612,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U
 min_a	a
 drop table t1;
 #
-# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
+#
+# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
 CREATE TABLE t1 ( a VARCHAR(16), KEY (a) );
@@ -6768,5 +6794,68 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
-# return optimizer switch changed in the beginning of this test
-set optimizer_switch=@subselect_tmp;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 41c445329cb..ed00e4ef684 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -161,18 +161,18 @@ SET optimizer_switch='materialization=on,in_to_exists=on';
 EXPLAIN
 SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	2	Using index
-1	PRIMARY	t3	index	b	b	5	NULL	4	Using where; Using index; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t1	index	PRIMARY,a	a	5	NULL	2	Using index
+1	PRIMARY	t2	index	a	a	5	NULL	2	Using where; Using index
+1	PRIMARY	t3	ref	b	b	5	test.t2.a	2	Using index
+2	SUBQUERY	t1	index_subquery	PRIMARY,a	a	5	const	0	Using index; Using where
 SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM t1) OR a = b;
 pk	a	b
 0	4	4
 EXPLAIN
 SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	2	Using index
-1	PRIMARY	t3	index	b	b	5	NULL	4	Using where; Using index; Using join buffer (flat, BNL join)
-2	MATERIALIZED	t1	index	PRIMARY,a	a	5	NULL	2	Using index
+1	PRIMARY	t2	index	a	a	5	NULL	2	Using where; Using index
+1	PRIMARY	t3	ref	b	b	5	test.t2.a	2	Using index
+2	SUBQUERY	t1	index_subquery	PRIMARY,a	a	5	const	0	Using index; Using where
 SELECT * FROM t2,t3 WHERE (2,9) IN (SELECT DISTINCT a,pk FROM v1) OR a = b;
 pk	a	b
 0	4	4
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 72b4f34d9de..28441e4019e 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -627,51 +627,51 @@ SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
 not_in
 NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	MATERIALIZED	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
 not_in
 NULL
@@ -679,7 +679,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
 not_in
 1
@@ -687,7 +687,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
 not_in
 NULL
@@ -695,7 +695,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
 not_in
 NULL
@@ -703,7 +703,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
 not_in
 NULL
@@ -718,51 +718,51 @@ SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in;
 not_in
 NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2);
 f1	f2
 EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in;
 not_in
 NULL
@@ -770,7 +770,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in;
 not_in
 1
@@ -778,7 +778,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in;
 not_in
 NULL
@@ -786,7 +786,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in;
 not_in
 NULL
@@ -794,7 +794,7 @@ EXPLAIN
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in;
 not_in
 NULL
@@ -813,21 +813,21 @@ SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -842,14 +842,14 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -904,21 +904,21 @@ SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -933,14 +933,14 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition; Rowid-ordered scan
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10);
 f1	f2
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index a0f05a26a46..a4670872fad 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -333,7 +333,7 @@ WHERE (SELECT DISTINCT b FROM t3) > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	Using where; Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-3	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	0	Using temporary
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	Using temporary
 SELECT *
 FROM t1
 WHERE t1.a = (
@@ -386,7 +386,7 @@ select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 gr
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	1	
-3	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
 select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1));
 1
 1
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 45d0b9ee519..9bb26e8a6e0 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2183,7 +2183,7 @@ NULL
 EXPLAIN
 SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1);
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 5a38fe7ca72..8c95c8637aa 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -148,7 +148,7 @@ FROM t2 GROUP BY f1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
-3	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 drop table t1, t2, t3;
 #
 # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed
@@ -162,7 +162,7 @@ EXPLAIN
 SELECT * FROM (SELECT * FROM t2) AS a2
 WHERE (SELECT distinct SUM(distinct f3 ) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
 3	SUBQUERY	t1	index	NULL	f3	5	NULL	2	Using index
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 insert into t2 values (1),(2);
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 78ab87dbfd6..e72d25fdafa 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -4200,7 +4200,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -5967,7 +5967,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6126,7 +6126,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6611,7 +6611,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U
 min_a	a
 drop table t1;
 #
-# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
+#
+# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
 CREATE TABLE t1 ( a VARCHAR(16), KEY (a) );
@@ -6766,8 +6792,71 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
-# return optimizer switch changed in the beginning of this test
-set optimizer_switch=@subselect_tmp;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index f822a4500f4..9030265356b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -5963,7 +5963,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6122,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6607,7 +6607,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U
 min_a	a
 drop table t1;
 #
-# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
+#
+# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
 CREATE TABLE t1 ( a VARCHAR(16), KEY (a) );
@@ -6763,6 +6789,69 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
-# return optimizer switch changed in the beginning of this test
-set optimizer_switch=@subselect_tmp;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index cdad363313d..e68f5990c08 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -4202,7 +4202,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -5972,7 +5972,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6133,7 +6133,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6618,7 +6618,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U
 min_a	a
 drop table t1;
 #
-# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
+#
+# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
 CREATE TABLE t1 ( a VARCHAR(16), KEY (a) );
@@ -6774,8 +6800,71 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
-# return optimizer switch changed in the beginning of this test
-set optimizer_switch=@subselect_tmp;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
 @@optimizer_switch like '%subquery_cache=on%'
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 8e6d4bfccc2..f0ce541294a 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -4196,7 +4196,7 @@ INSERT INTO t1 VALUES (1,1),(2,1);
 EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-2	SUBQUERY	internal_tmp_table	ALL	group_key	NULL	NULL	NULL	1	Using temporary; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
 INSERT INTO t1 VALUES
@@ -5963,7 +5963,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6122,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_switch;
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6607,7 +6607,33 @@ SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 U
 min_a	a
 drop table t1;
 #
-# LP BUG#944706 Query with impossible or constant subquery in WHERE or HAVING is not
+# MDEV-367: Different results with and without subquery_cache on
+# a query with a constant NOT IN condition
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2),(3);
+set @mdev367_optimizer_switch = @@optimizer_switch;
+set optimizer_switch = 'subquery_cache=on';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+set optimizer_switch = 'subquery_cache=off';
+SELECT * FROM t1 WHERE ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) OR a > 100;
+a
+SELECT *, ( 3, 3 ) NOT IN ( SELECT NULL, NULL ) FROM t1;
+a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
+1	NULL
+2	NULL
+3	NULL
+set optimizer_switch=@mdev367_optimizer_switch;
+DROP TABLE t1;
+#
+# lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
 CREATE TABLE t1 ( a VARCHAR(16), KEY (a) );
@@ -6763,7 +6789,70 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 a
 drop table t1,t2;
-# return optimizer switch changed in the beginning of this test
-set optimizer_switch=@subselect_tmp;
+#
+# MDEV-410: EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used
+#
+CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('USA');
+CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
+CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (12),(22),(9),(45);
+create table t4 like t3;
+insert into t4 select * from t3;
+# This should not show range access for table t2
+explain
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ref	d	d	5	test.t2.b	2	Using where; Using index
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @tmp_mdev410=@@global.userstat;
+set global userstat=on;
+flush table_statistics;
+flush index_statistics;
+SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 
+WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
+MIN(b)
+NULL
+# The following shows that t2 was indeed scanned with a full scan.
+show table_statistics;
+Table_schema	Table_name	Rows_read	Rows_changed	Rows_changed_x_#indexes
+test	t1	2	0	0
+test	t2	3	0	0
+show index_statistics;
+Table_schema	Table_name	Index_name	Rows_read
+test	t2	b	1
+set global userstat=@tmp_mdev410;
+DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index fc2e0854620..723efbdcb23 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -878,6 +878,8 @@ ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT
 CREATE TABLE b15776 (a char(4294967296));
 ERROR 42000: Display width out of range for 'a' (max = 4294967295)
 CREATE TABLE b15776 (a year(4294967295));
+Warnings:
+Note	1287	'YEAR(4294967295)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 INSERT INTO b15776 VALUES (42);
 SELECT * FROM b15776;
 a
@@ -886,6 +888,8 @@ DROP TABLE b15776;
 CREATE TABLE b15776 (a year(4294967296));
 ERROR 42000: Display width out of range for 'a' (max = 4294967295)
 CREATE TABLE b15776 (a year(0));
+Warnings:
+Note	1287	'YEAR(0)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 DROP TABLE b15776;
 CREATE TABLE b15776 (a year(-2));
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-2))' at line 1
diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result
index 5d89a1ee407..4b9bf6f433f 100644
--- a/mysql-test/r/type_year.result
+++ b/mysql-test/r/type_year.result
@@ -1,5 +1,7 @@
 drop table if exists t1;
 create table t1 (y year,y2 year(2));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69);
 select * from t1;
 y	y2
@@ -50,6 +52,8 @@ End of 5.0 tests
 # Bug #49480: WHERE using YEAR columns returns unexpected results
 #
 CREATE TABLE t2(yy YEAR(2), c2 CHAR(4));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 CREATE TABLE t4(yyyy YEAR(4), c4 CHAR(4));
 INSERT INTO t2 (c2) VALUES (NULL),(1970),(1999),(2000),(2001),(2069);
 INSERT INTO t4 (c4) SELECT c2 FROM t2;
@@ -358,9 +362,22 @@ total_rows	min_value	MAX(c1+0)
 3	0	2155
 DROP TABLE t1;
 #
+# WL#6219: Deprecate and remove YEAR(2) type
+#
+CREATE TABLE t1 (c1 YEAR(2), c2 YEAR(4));
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+ALTER TABLE t1 MODIFY COLUMN c2 YEAR(2);
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+DROP TABLE t1;
+#
 End of 5.1 tests
 create function y2k() returns int deterministic return 2000;
 create table t1 (a year(2), b int);
+Warnings:
+Note	1287	'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 insert t1 values (0,2000);
 select a from t1 where a=2000;
 a
diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result
index 251d0b39098..68d2364986d 100644
--- a/mysql-test/r/user_var-binlog.result
+++ b/mysql-test/r/user_var-binlog.result
@@ -34,7 +34,7 @@ SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET @`a b`:=_latin1 0x68656C6C6F COLLATE `latin1_swedish_ci`/*!*/;
-use test/*!*/;
+use `test`/*!*/;
 SET TIMESTAMP=10000/*!*/;
 INSERT INTO t1 VALUES(@`a b`)
 /*!*/;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 008f8f41ada..b50f6b81206 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4674,6 +4674,19 @@ INSERT INTO t2 VALUES (1);
 DROP TRIGGER tr;
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+#
+# LP bug#1007622 Server crashes in handler::increment_statistics on
+# inserting into a view over a view
+#
+CREATE TABLE t1 (a INT);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.* FROM t1 AS a1, t1 AS a2;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1;
+INSERT INTO v2 (a) VALUES (1) ;
+select * from t1;
+a
+1
+drop view v2,v1;
+drop table t1;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------