Merge branch '10.0' into 10.1

28 files changed, 798 insertions, 54 deletions

diff --git a/mysql-test/r/alter_table_online.result b/mysql-test/r/alter_table_online.result
index 537614c710b..05d03aeae11 100644
--- a/mysql-test/r/alter_table_online.result
+++ b/mysql-test/r/alter_table_online.result
@@ -2,15 +2,9 @@ drop table if exists t1,t2,t3;
 create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
 insert into t1 (a) values (1),(2),(3);
 alter online table t1 modify b int default 5;
-ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
 alter online table t1 change b new_name int;
-ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
 alter online table t1 modify e enum('a','b','c');
-ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
 alter online table t1 comment "new comment";
-ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
-alter online table t1 rename to t2;
-ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
 alter online table t1 algorithm=INPLACE, lock=NONE;
 alter online table t1;
 alter table t1 algorithm=INPLACE;
@@ -40,10 +34,13 @@ alter online table t1 add f int;
 ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED.
 alter online table t1 engine=memory;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
+alter online table t1 rename to t2;
+ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
 alter table t1 engine=innodb;
 alter table t1 add index (b);
 alter online table t1 add index c (c);
 alter online table t1 drop index b;
+alter online table t1 comment "new comment";
 drop table t1;
 create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b'));
 insert into t1 (a) values (1),(2),(3);
diff --git a/mysql-test/r/contributors.result b/mysql-test/r/contributors.result
index d578d43cb62..5669c6b5269 100644
--- a/mysql-test/r/contributors.result
+++ b/mysql-test/r/contributors.result
@@ -1,15 +1,13 @@
 SHOW CONTRIBUTORS;
 Name	Location	Comment
-Booking.com	http://www.booking.com	Founding member of the MariaDB foundation
-SkySQL Ab	http://www.skysql.com	Founding member of the MariaDB foundation
-Auttomatic	http://automattic.com	Member of the MariaDB foundation
-Parallels	http://www.parallels.com/products/plesk	Founding member of the MariaDB foundation
-Verkkokauppa.com	Finland	Sponsor of the MariaDB foundation
-Webyog	Bangalor	Sponsor of the MariaDB foundation
-Percona	USA	Sponsor of the MariaDB foundation
-Jelastic.com	Russia	Sponsor of the MariaDB foundation
-Planetta.net	Finland	Sponsor of the MariaDB foundation
-Open query	Australia	Sponsor of the MariaDB foundation
+Booking.com	http://www.booking.com	Founding member of the MariaDB Foundation
+MariaDB Corporation	https://mariadb.com	Founding member of the MariaDB Foundation
+Auttomattic	http://automattic.com	Member of the MariaDB Foundation
+Parallels	http://www.parallels.com/products/plesk	Founding member of the MariaDB Foundation
+Acronis	http://www.acronis.com	Member of the MariaDB Foundation
+Verkkokauppa.com	Finland	Sponsor of the MariaDB Foundation
+Webyog	Bangalore	Sponsor of the MariaDB Foundation
+Wikimedia Foundation	USA	Sponsor of the MariaDB Foundation
 Google	USA	Sponsoring parallel replication and GTID
 Facebook	USA	Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc
 Ronald Bradford	Brisbane, Australia	EFF contribution for UC2006 Auction
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 6f664d306a1..cad46f34c75 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -1889,5 +1889,21 @@ Warnings:
 Note	1291	Column 'a' has duplicated value '' in ENUM
 drop table t1;
 set @@session.collation_server=default;
+#
+# MDEV-7765: Crash (Assertion `!table || (!table->write_set ||
+# bitmap_is_set(table->write_set, field_index) ||
+# bitmap_is_set(table->vcol_set, field_index))' fails)
+# on using function over not created table
+#
+CREATE function f1() returns int
+BEGIN
+declare n int;
+set n:= (select count(*) from t1);
+return n;
+end|
+create table t1 as select f1();
+ERROR 42S02: Table 'test.t1' doesn't exist
+drop function f1;
+End of 5.5 tests
 create table t1;
 ERROR 42000: A table must have at least 1 column
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 90bc6b51177..57a6bfa1a87 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -116,7 +116,7 @@ hex(a)	STRCMP(a,'a')	STRCMP(a,'a ')
 DROP TABLE t1;
 select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
 insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
-this	is a	test
+this	is 	test
 select insert("aa",100,1,"b"),insert("aa",1,3,"b");
 insert("aa",100,1,"b")	insert("aa",1,3,"b")
 aa	b
@@ -5371,11 +5371,10 @@ SET sql_mode=default;
 SET NAMES utf8;
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (0), (0), (1), (0), (0);
-SELECT COUNT(*) FROM t1, t1 t2 
+SELECT COUNT(*) FROM t1, t1 t2
 GROUP BY INSERT('', t2.a, t1.a, (@@global.max_binlog_size));
 COUNT(*)
-20
-5
+25
 DROP TABLE t1;
 #
 # Bug#11764503 (Bug#57341) Query in EXPLAIN EXTENDED shows wrong characters
diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result
index ac53a7e5a4e..cf053854256 100644
--- a/mysql-test/r/ctype_utf8mb4.result
+++ b/mysql-test/r/ctype_utf8mb4.result
@@ -116,7 +116,7 @@ hex(a)	STRCMP(a,'a')	STRCMP(a,'a ')
 DROP TABLE t1;
 select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
 insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
-this	is a	test
+this	is 	test
 select insert("aa",100,1,"b"),insert("aa",1,3,"b");
 insert("aa",100,1,"b")	insert("aa",1,3,"b")
 aa	b
diff --git a/mysql-test/r/ctype_utf8mb4_heap.result b/mysql-test/r/ctype_utf8mb4_heap.result
index 5f77d417fe5..d70e009228e 100644
--- a/mysql-test/r/ctype_utf8mb4_heap.result
+++ b/mysql-test/r/ctype_utf8mb4_heap.result
@@ -116,7 +116,7 @@ hex(a)	STRCMP(a,'a')	STRCMP(a,'a ')
 DROP TABLE t1;
 select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
 insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
-this	is a	test
+this	is 	test
 select insert("aa",100,1,"b"),insert("aa",1,3,"b");
 insert("aa",100,1,"b")	insert("aa",1,3,"b")
 aa	b
diff --git a/mysql-test/r/ctype_utf8mb4_innodb.result b/mysql-test/r/ctype_utf8mb4_innodb.result
index fc1d2b9d315..7d193f397ac 100644
--- a/mysql-test/r/ctype_utf8mb4_innodb.result
+++ b/mysql-test/r/ctype_utf8mb4_innodb.result
@@ -116,7 +116,7 @@ hex(a)	STRCMP(a,'a')	STRCMP(a,'a ')
 DROP TABLE t1;
 select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
 insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
-this	is a	test
+this	is 	test
 select insert("aa",100,1,"b"),insert("aa",1,3,"b");
 insert("aa",100,1,"b")	insert("aa",1,3,"b")
 aa	b
diff --git a/mysql-test/r/ctype_utf8mb4_myisam.result b/mysql-test/r/ctype_utf8mb4_myisam.result
index a033138a6fe..28cf36c7492 100644
--- a/mysql-test/r/ctype_utf8mb4_myisam.result
+++ b/mysql-test/r/ctype_utf8mb4_myisam.result
@@ -116,7 +116,7 @@ hex(a)	STRCMP(a,'a')	STRCMP(a,'a ')
 DROP TABLE t1;
 select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
 insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
-this	is a	test
+this	is 	test
 select insert("aa",100,1,"b"),insert("aa",1,3,"b");
 insert("aa",100,1,"b")	insert("aa",1,3,"b")
 aa	b
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index a59f8a6dfee..1d643333424 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -603,6 +603,351 @@ select x.id, message from (select id from t1) x left join
 where coalesce(message,0) <> 0;
 id	message
 drop table t1,t2;
+#
+# MDEV-7827: Assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))' failed
+# in Field_long::val_str on EXPLAIN EXTENDED
+#
+CREATE TABLE t1 (f1 INT, f2 INT, KEY(f2)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (6,9);
+CREATE TABLE t2 (f3 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (2),(0);
+EXPLAIN EXTENDED
+SELECT f1 FROM ( SELECT * FROM t1 ) AS sq
+WHERE f1 IN (
+SELECT f3 FROM t2 WHERE f2 IN (
+SELECT f3 FROM t2 HAVING f3 >= 8
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	4	sq.f2	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; FirstMatch(<subquery4>); Using join buffer (flat, BNL join)
+4	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1276	Field or reference 'sq.f2' of SELECT #3 was resolved in SELECT #1
+Note	1003	select 6 AS `f1` from  <materialize> (select `test`.`t2`.`f3` from `test`.`t2` having (`test`.`t2`.`f3` >= 8)) semi join (`test`.`t2`) where ((`test`.`t2`.`f3` = 6) and (9 = `<subquery4>`.`f3`))
+DROP TABLE t2,t1;
+#
+# MDEV-9462: Out of memory using explain on 2 empty tables 
+#
+CREATE TABLE `t1` (
+`REC_GROUP` char(2) DEFAULT NULL,
+`CLIENT_INFO` text CHARACTER SET utf8,
+`NAME` text,
+`PHONE_NUMBER` text,
+`ATTENTION_NAME` text,
+`PAYMENT_TERM` text CHARACTER SET utf8,
+`CREDIT_LIMIT` decimal(12,2) DEFAULT NULL,
+`LAST_PAY_DATE` text CHARACTER SET utf8,
+`TOTAL` double DEFAULT NULL,
+`TOTAL_MCL` double DEFAULT NULL,
+`TOTAL_MFS` double DEFAULT NULL,
+`TOTAL_MIS` double DEFAULT NULL,
+`BEFORE_DUE_7_MCL` double DEFAULT NULL,
+`BEFORE_DUE_7_MFS` double DEFAULT NULL,
+`BEFORE_DUE_7_MIS` double DEFAULT NULL,
+`PER1_MCL` double DEFAULT NULL,
+`PER1_MFS` double DEFAULT NULL,
+`PER1_MIS` double DEFAULT NULL,
+`PER2_MCL` double DEFAULT NULL,
+`PER2_MFS` double DEFAULT NULL,
+`PER2_MIS` double DEFAULT NULL,
+`PER3_MCL` double DEFAULT NULL,
+`PER3_MFS` double DEFAULT NULL,
+`PER3_MIS` double DEFAULT NULL,
+`PER4_MCL` double DEFAULT NULL,
+`PER4_MFS` double DEFAULT NULL,
+`PER4_MIS` double DEFAULT NULL,
+`PER5_MCL` double DEFAULT NULL,
+`PER5_MFS` double DEFAULT NULL,
+`PER5_MIS` double DEFAULT NULL,
+`PER6_MCL` double DEFAULT NULL,
+`PER6_MFS` double DEFAULT NULL,
+`PER6_MIS` double DEFAULT NULL,
+`PER7_MCL` double DEFAULT NULL,
+`PER7_MFS` double DEFAULT NULL,
+`PER7_MIS` double DEFAULT NULL,
+`BEFORE_DUE_7` double DEFAULT NULL,
+`PER1` double DEFAULT NULL,
+`PER2` double DEFAULT NULL,
+`PER3` double DEFAULT NULL,
+`PER4` double DEFAULT NULL,
+`PER5` double DEFAULT NULL,
+`PER6` double DEFAULT NULL,
+`PER7` double DEFAULT NULL,
+`REF` varchar(30) DEFAULT NULL,
+`TYPE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
+);
+CREATE TABLE `t2` (
+`RECEIVABLE_GROUP` char(2) DEFAULT NULL,
+`CLIENT_NUMBER` varchar(35) DEFAULT NULL,
+`CLIENT_NAME` varchar(73) DEFAULT NULL,
+`PHONE_NUMBER` char(12) DEFAULT NULL,
+`ATTENTION_NAME` char(26) DEFAULT NULL,
+`PAYMENT_TERM` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
+`CREDIT_LIMIT` decimal(12,2) DEFAULT NULL,
+`LAST_PAY_DATE` varchar(42) CHARACTER SET utf8 DEFAULT NULL,
+`TOTAL` decimal(12,2) DEFAULT NULL,
+`BEFORE_DUE_7` decimal(12,2) DEFAULT NULL,
+`PER1` decimal(12,2) DEFAULT NULL,
+`PER2` decimal(12,2) DEFAULT NULL,
+`PER3` decimal(12,2) DEFAULT NULL,
+`PER4` decimal(12,2) DEFAULT NULL,
+`PER5` decimal(12,2) DEFAULT NULL,
+`PER6` decimal(12,2) DEFAULT NULL,
+`PER7` decimal(12,2) DEFAULT NULL,
+`DIVISION` varchar(3) CHARACTER SET utf8 NOT NULL,
+`CLIENT_INFO` varchar(294) CHARACTER SET utf8 DEFAULT NULL,
+`EXCHANGE_RATE` double NOT NULL,
+`REF` varchar(30) DEFAULT NULL
+);
+explain
+SELECT A.RECEIVABLE_GROUP,A.CLIENT_INFO,A.CLIENT_NAME,A.PHONE_NUMBER,A.ATTENTION_NAME,A.PAYMENT_TERM,A.CREDIT_LIMIT,A.LAST_PAY_DATE,A.TOTAL,
+COALESCE(B.TOTAL_MCL,0) AS TOTAL_MCL,
+COALESCE(C.TOTAL_MFS,0) AS TOTAL_MFS,
+COALESCE(D.TOTAL_MIS,0) AS TOTAL_MIS,
+COALESCE(F.BEFORE_DUE_7_MCL,0) AS BEFORE_DUE_7_MCL,
+COALESCE(G.BEFORE_DUE_7_MFS,0) AS BEFORE_DUE_7_MFS,
+COALESCE(H.BEFORE_DUE_7_MIS,0) AS BEFORE_DUE_7_MIS,
+COALESCE(I.PER1_MCL,0) AS PER1_MCL,
+COALESCE(J.PER1_MFS,0) AS PER1_MFS,
+COALESCE(K.PER1_MIS,0) AS PER1_MIS,
+COALESCE(L.PER2_MCL,0) AS PER2_MCL,
+COALESCE(M.PER2_MFS,0) AS PER2_MFS,
+COALESCE(N.PER2_MIS,0) AS PER2_MIS,
+COALESCE(O.PER3_MCL,0) AS PER3_MCL,
+COALESCE(P.PER3_MFS,0) AS PER3_MFS,
+COALESCE(R.PER3_MIS,0) AS PER3_MIS,
+COALESCE(S.PER4_MCL,0) AS PER4_MCL,
+COALESCE(T.PER4_MFS,0) AS PER4_MFS,
+COALESCE(U.PER4_MIS,0) AS PER4_MIS,
+COALESCE(V.PER5_MCL,0) AS PER5_MCL,
+COALESCE(X.PER5_MFS,0) AS PER5_MFS,
+COALESCE(Z.PER5_MIS,0) AS PER5_MIS,
+COALESCE(Q.PER6_MCL,0) AS PER6_MCL,
+COALESCE(Y.PER6_MFS,0) AS PER6_MFS,
+COALESCE(W.PER6_MIS,0) AS PER6_MIS,
+COALESCE(A1.PER7_MCL,0) AS PER7_MCL,
+COALESCE(B1.PER7_MFS,0) AS PER7_MFS,
+COALESCE(C1.PER7_MIS,0) AS PER7_MIS,
+A.BEFORE_DUE_7,A.PER1,A.PER2,A.PER3,A.PER4,A.PER5,A.PER6,A.PER7,
+CONCAT(A.DIVISION,'-',A.CLIENT_NUMBER) AS REF,"2" AS TYPE FROM
+(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,
+GROUP_CONCAT(DISTINCT CLIENT_INFO SEPARATOR '<br>') AS CLIENT_INFO,
+GROUP_CONCAT(DISTINCT CLIENT_NAME SEPARATOR '<br>') AS CLIENT_NAME, 
+GROUP_CONCAT( DISTINCT `PHONE_NUMBER` SEPARATOR '<br>' ) AS PHONE_NUMBER , 
+GROUP_CONCAT( DISTINCT `ATTENTION_NAME` SEPARATOR '<br>' )  AS ATTENTION_NAME, 
+GROUP_CONCAT( DISTINCT `PAYMENT_TERM` SEPARATOR '<br>' ) AS PAYMENT_TERM, 
+CREDIT_LIMIT , 
+GROUP_CONCAT( `LAST_PAY_DATE` SEPARATOR '<br>' ) AS LAST_PAY_DATE, 
+SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL, 
+SUM( `BEFORE_DUE_7`*EXCHANGE_RATE ) AS BEFORE_DUE_7, 
+SUM( `PER1`*EXCHANGE_RATE ) AS PER1, 
+SUM( `PER2`*EXCHANGE_RATE ) AS PER2, 
+SUM( `PER3`*EXCHANGE_RATE ) AS PER3, 
+SUM( `PER4`*EXCHANGE_RATE ) AS PER4, 
+SUM( `PER5`*EXCHANGE_RATE ) AS PER5, 
+SUM( `PER6`*EXCHANGE_RATE ) AS PER6, 
+SUM( `PER7`*EXCHANGE_RATE ) AS PER7
+FROM `t2`
+WHERE REF IS NULL GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A 
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B ON A.CLIENT_NUMBER=B.CLIENT_NUMBER AND 
+A.DIVISION=B.DIVISION AND A.RECEIVABLE_GROUP=B.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C ON A.CLIENT_NUMBER=C.CLIENT_NUMBER 
+AND 
+A.DIVISION=C.DIVISION AND A.RECEIVABLE_GROUP=C.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS D ON A.CLIENT_NUMBER=D.CLIENT_NUMBER AND 
+A.DIVISION=D.DIVISION AND A.RECEIVABLE_GROUP=D.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=D.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS F ON A.CLIENT_NUMBER=F.CLIENT_NUMBER AND 
+A.DIVISION=F.DIVISION AND A.RECEIVABLE_GROUP=F.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=F.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS G ON A.CLIENT_NUMBER=G.CLIENT_NUMBER AND 
+A.DIVISION=G.DIVISION AND A.RECEIVABLE_GROUP=G.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=G.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS H ON A.CLIENT_NUMBER=H.CLIENT_NUMBER AND 
+A.DIVISION=H.DIVISION AND A.RECEIVABLE_GROUP=H.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=H.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS I ON A.CLIENT_NUMBER=I.CLIENT_NUMBER  AND 
+A.DIVISION=I.DIVISION AND A.RECEIVABLE_GROUP=I.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=I.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS J ON A.CLIENT_NUMBER=J.CLIENT_NUMBER AND 
+A.DIVISION=J.DIVISION AND A.RECEIVABLE_GROUP=J.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=J.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS K ON A.CLIENT_NUMBER=K.CLIENT_NUMBER AND 
+A.DIVISION=K.DIVISION AND A.RECEIVABLE_GROUP=K.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=K.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS L ON A.CLIENT_NUMBER=L.CLIENT_NUMBER AND 
+A.DIVISION=L.DIVISION AND A.RECEIVABLE_GROUP=L.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=L.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS M ON A.CLIENT_NUMBER=M.CLIENT_NUMBER AND 
+A.DIVISION=M.DIVISION AND A.RECEIVABLE_GROUP=M.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=M.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS N ON A.CLIENT_NUMBER=N.CLIENT_NUMBER AND 
+A.DIVISION=N.DIVISION AND A.RECEIVABLE_GROUP=N.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=N.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS O ON A.CLIENT_NUMBER=O.CLIENT_NUMBER AND 
+A.DIVISION=O.DIVISION AND A.RECEIVABLE_GROUP=O.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=O.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS P ON A.CLIENT_NUMBER=P.CLIENT_NUMBER AND 
+A.DIVISION=P.DIVISION AND A.RECEIVABLE_GROUP=P.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=P.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS R ON A.CLIENT_NUMBER=R.CLIENT_NUMBER AND 
+A.DIVISION=R.DIVISION AND A.RECEIVABLE_GROUP=R.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=R.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS S ON A.CLIENT_NUMBER=S.CLIENT_NUMBER AND 
+A.DIVISION=S.DIVISION AND A.RECEIVABLE_GROUP=S.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=S.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS T ON A.CLIENT_NUMBER=T.CLIENT_NUMBER AND 
+A.DIVISION=T.DIVISION AND A.RECEIVABLE_GROUP=T.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=T.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS U ON A.CLIENT_NUMBER=U.CLIENT_NUMBER AND 
+A.DIVISION=U.DIVISION AND A.RECEIVABLE_GROUP=U.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=U.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS V ON A.CLIENT_NUMBER=V.CLIENT_NUMBER AND 
+A.DIVISION=V.DIVISION AND A.RECEIVABLE_GROUP=V.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=V.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS X ON A.CLIENT_NUMBER=X.CLIENT_NUMBER AND 
+A.DIVISION=X.DIVISION AND A.RECEIVABLE_GROUP=X.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=X.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Z ON A.CLIENT_NUMBER=Z.CLIENT_NUMBER AND 
+A.DIVISION=Z.DIVISION AND A.RECEIVABLE_GROUP=Z.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Z.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Q ON A.CLIENT_NUMBER=Q.CLIENT_NUMBER AND 
+A.DIVISION=Q.DIVISION AND A.RECEIVABLE_GROUP=Q.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Q.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Y ON A.CLIENT_NUMBER=Y.CLIENT_NUMBER AND 
+A.DIVISION=Y.DIVISION AND A.RECEIVABLE_GROUP=Y.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Y.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS W ON A.CLIENT_NUMBER=W.CLIENT_NUMBER AND 
+A.DIVISION=W.DIVISION AND A.RECEIVABLE_GROUP=W.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=W.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MCL
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A1 ON A.CLIENT_NUMBER=A1.CLIENT_NUMBER AND 
+A.DIVISION=A1.DIVISION AND A.RECEIVABLE_GROUP=A1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=A1.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MFS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B1 ON A.CLIENT_NUMBER=B1.CLIENT_NUMBER AND 
+A.DIVISION=B1.DIVISION AND A.RECEIVABLE_GROUP=B1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B1.CREDIT_LIMIT
+LEFT JOIN
+(SELECT  RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MIS
+FROM `t2`
+WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C1 ON A.CLIENT_NUMBER=C1.CLIENT_NUMBER AND 
+A.DIVISION=C1.DIVISION AND A.RECEIVABLE_GROUP=C1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C1.CREDIT_LIMIT
+ORDER BY TOTAL DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived3>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived4>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived5>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived6>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived7>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived8>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived9>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived10>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived11>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived12>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived13>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived14>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived15>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived16>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived17>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived18>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived19>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived20>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived21>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived22>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived23>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived24>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived25>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived26>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived27>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived28>	system	NULL	NULL	NULL	NULL	0	const row not found
+1	PRIMARY	<derived29>	system	NULL	NULL	NULL	NULL	0	const row not found
+29	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+28	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+27	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+26	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+25	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+24	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+23	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+22	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+21	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+20	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+19	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+18	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+17	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+16	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+15	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+14	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+13	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+12	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+11	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+10	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+9	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+8	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+7	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+6	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+DROP TABLES t1,t2;
 set optimizer_switch=@save_derived_optimizer_switch;
 #
 # Start of 10.1 tests
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 678b6d22028..f196571218f 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -204,7 +204,7 @@ CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),repeat('
 "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb";"cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc";"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"
 select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
 insert('txs',2,1,'hi')	insert('is ',4,0,'a')	insert('txxxxt',2,4,'es')
-this	is a	test
+this	is 	test
 select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c');
 replace('aaaa','a','b')	replace('aaaa','aa','b')	replace('aaaa','a','bb')	replace('aaaa','','b')	replace('bbbb','a','c')
 bbbb	bb	bbbbbbbb	aaaa	bbbb
@@ -2343,7 +2343,7 @@ INSERT('abc', 3, 3, '1234')
 ab1234
 SELECT INSERT('abc', 4, 3, '1234');
 INSERT('abc', 4, 3, '1234')
-abc1234
+abc
 SELECT INSERT('abc', 5, 3, '1234');
 INSERT('abc', 5, 3, '1234')
 abc
@@ -2633,7 +2633,7 @@ CREATE TABLE t1 ( a TEXT );
 SELECT 'aaaaaaaaaaaaaa' INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt';;
 SELECT insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' );
 insert( substring_index( 'a', 'a', 'b' ), 1, 0, 'x' )
-x
+
 Warnings:
 Warning	1292	Truncated incorrect INTEGER value: 'b'
 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug58165.txt' INTO TABLE t1;;
@@ -4579,8 +4579,7 @@ CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (0),(0),(1),(0),(0);
 SELECT COUNT(*) FROM t1, t1 t2 GROUP BY INSERT('', t2.a, t1.a, @@global.max_binlog_size);
 COUNT(*)
-20
-5
+25
 DROP TABLE t1;
 #
 # End of 10.1 tests
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 994fa8580eb..ad51010de30 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1729,6 +1729,45 @@ Warnings:
 Warning	1292	Incorrect datetime value: '1'
 drop table t1;
 SET timestamp=DEFAULT;
+#
+# Bug #21564557: INCONSISTENT OUTPUT FROM 5.5 AND 5.6
+#                UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%M"
+#
+SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m"));
+UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m"))
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '201506' for function str_to_date
+SELECT UNIX_TIMESTAMP('2015-06-00');
+UNIX_TIMESTAMP('2015-06-00')
+NULL
+Warnings:
+Warning	1292	Incorrect datetime value: '2015-06-00'
+SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s'));
+UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s'))
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '0000-00-00 10:30:30' for function str_to_date
+set sql_mode= 'TRADITIONAL';
+SELECT @@sql_mode;
+@@sql_mode
+STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+SELECT UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m"));
+UNIX_TIMESTAMP(STR_TO_DATE('201506', "%Y%m"))
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '201506' for function str_to_date
+SELECT UNIX_TIMESTAMP('2015-06-00');
+UNIX_TIMESTAMP('2015-06-00')
+NULL
+Warnings:
+Warning	1292	Incorrect datetime value: '2015-06-00'
+SELECT UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s'));
+UNIX_TIMESTAMP(STR_TO_DATE('0000-00-00 10:30:30', '%Y-%m-%d %h:%i:%s'))
+NULL
+Warnings:
+Warning	1411	Incorrect datetime value: '0000-00-00 10:30:30' for function str_to_date
+set sql_mode= default;
 select time('10:10:10') > 10;
 time('10:10:10') > 10
 1
diff --git a/mysql-test/r/merge_innodb.result b/mysql-test/r/merge_innodb.result
index f6057d279b1..5aa344a9391 100644
--- a/mysql-test/r/merge_innodb.result
+++ b/mysql-test/r/merge_innodb.result
@@ -35,3 +35,36 @@ c1
 Ann
 Alice
 DROP TABLE t1, t2, t3, t4, t5;
+create table t1 (c1 varchar(100));
+create table t2 (c1 varchar(100));
+create view t3 as select * from t1;
+insert into t1 values ('ann'), ('alice');
+insert into t2 values ('bob'), ('brian');
+create temporary table t4 (c1 varchar(100)) engine=MERGE union=(t2, t1);
+create temporary table t5 (c1 varchar(100)) engine=MERGE union=(t3, t1);
+select * from t5;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+lock tables t1 read, t2 read, t3 read, t4 read;
+select * from t5;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+select * from t4;
+c1
+bob
+brian
+ann
+alice
+unlock tables;
+drop table t2;
+create view t2 as select * from t1;
+select * from t4;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+lock tables t1 read, t2 read, t3 read;
+select * from t4;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+select * from t4;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+select * from t4;
+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
+unlock tables;
+drop view t2, t3;
+drop table t1;
diff --git a/mysql-test/r/mysql_upgrade_view.result b/mysql-test/r/mysql_upgrade_view.result
index ef54ab6c9a5..f43f42f97fd 100644
--- a/mysql-test/r/mysql_upgrade_view.result
+++ b/mysql-test/r/mysql_upgrade_view.result
@@ -319,4 +319,64 @@ master-bin.000001	#	Gtid	#	#	GTID #-#-#
 master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW `v4`  FROM MYSQL
 drop table if exists kv;
 drop view v1,v2,v3,v4;
+rename table mysql.event to mysql.ev_bk;
+flush tables;
+The --upgrade-system-tables option was used, user tables won't be touched.
+MySQL upgrade detected
+Phase 1/6: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.ev_bk                                        OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.host                                         OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.innodb_table_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.user                                         OK
+
+Repairing tables
+mysql.innodb_index_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.innodb_table_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+Phase 2/6: Fixing views from mysql
+test.v1                                            OK
+test.v2                                            OK
+test.v3                                            OK
+Phase 3/6: Running 'mysql_fix_privilege_tables'
+Phase 4/6: Fixing table and database names ... Skipped
+Phase 5/6: Checking and upgrading tables... Skipped
+Phase 6/6: Running 'FLUSH PRIVILEGES'
+OK
+drop table mysql.event;
+rename table mysql.ev_bk to mysql.event;
+drop view v1,v2,v3;
 drop table t1;
diff --git a/mysql-test/r/second_frac-9175.result b/mysql-test/r/second_frac-9175.result
new file mode 100644
index 00000000000..dbf268b5c3b
--- /dev/null
+++ b/mysql-test/r/second_frac-9175.result
@@ -0,0 +1,13 @@
+select timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456');
+timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456')
+31622400123456
+explain extended select timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select timestampdiff(MICROSECOND,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456') AS `timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456')`
+create view v1 as select timestampdiff(microsecond,'2000-01-01 00:00:00','2001-01-01 00:00:00.123456');
+select * from v1;
+Name_exp_1
+31622400123456
+drop view v1;
diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result
index 92758fa134b..7b38515cf70 100644
--- a/mysql-test/r/select_found.result
+++ b/mysql-test/r/select_found.result
@@ -348,3 +348,18 @@ select found_rows();
 found_rows()
 75
 drop table t1;
+create table t1(c1 int);
+insert into t1 values(1),(2),(3),(4),(5);
+select * from t1 order by c1 limit 2,1;
+c1
+3
+select found_rows();
+found_rows()
+3
+select sql_calc_found_rows * from t1 order by c1 limit 2,1;
+c1
+3
+select found_rows();
+found_rows()
+5
+drop table t1;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 51c1b1924d1..9453a1da3ca 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4326,57 +4326,57 @@ test.t1	repair	status	OK
 test.t2	repair	status	OK
 test.t3	repair	status	OK
 test.v1	repair	Error	'test.v1' is not BASE TABLE
-test.v1	repair	error	Corrupt
+test.v1	repair	status	Operation failed
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
 test.t2	optimize	status	OK
 test.t3	optimize	status	OK
 test.v1	optimize	Error	'test.v1' is not BASE TABLE
-test.v1	optimize	error	Corrupt
+test.v1	optimize	status	Operation failed
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
 test.t2	analyze	status	Table is already up to date
 test.t3	analyze	status	Table is already up to date
 test.v1	analyze	Error	'test.v1' is not BASE TABLE
-test.v1	analyze	error	Corrupt
+test.v1	analyze	status	Operation failed
 call bug13012()|
 Table	Op	Msg_type	Msg_text
 test.t1	repair	status	OK
 test.t2	repair	status	OK
 test.t3	repair	status	OK
 test.v1	repair	Error	'test.v1' is not BASE TABLE
-test.v1	repair	error	Corrupt
+test.v1	repair	status	Operation failed
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
 test.t2	optimize	status	OK
 test.t3	optimize	status	OK
 test.v1	optimize	Error	'test.v1' is not BASE TABLE
-test.v1	optimize	error	Corrupt
+test.v1	optimize	status	Operation failed
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
 test.t2	analyze	status	Table is already up to date
 test.t3	analyze	status	Table is already up to date
 test.v1	analyze	Error	'test.v1' is not BASE TABLE
-test.v1	analyze	error	Corrupt
+test.v1	analyze	status	Operation failed
 call bug13012()|
 Table	Op	Msg_type	Msg_text
 test.t1	repair	status	OK
 test.t2	repair	status	OK
 test.t3	repair	status	OK
 test.v1	repair	Error	'test.v1' is not BASE TABLE
-test.v1	repair	error	Corrupt
+test.v1	repair	status	Operation failed
 Table	Op	Msg_type	Msg_text
 test.t1	optimize	status	OK
 test.t2	optimize	status	OK
 test.t3	optimize	status	OK
 test.v1	optimize	Error	'test.v1' is not BASE TABLE
-test.v1	optimize	error	Corrupt
+test.v1	optimize	status	Operation failed
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	Table is already up to date
 test.t2	analyze	status	Table is already up to date
 test.t3	analyze	status	Table is already up to date
 test.v1	analyze	Error	'test.v1' is not BASE TABLE
-test.v1	analyze	error	Corrupt
+test.v1	analyze	status	Operation failed
 drop procedure bug13012|
 drop view v1|
 select * from t1 order by data|
diff --git a/mysql-test/r/ssl_cert_verify.result b/mysql-test/r/ssl_cert_verify.result
new file mode 100644
index 00000000000..1da77329509
--- /dev/null
+++ b/mysql-test/r/ssl_cert_verify.result
@@ -0,0 +1,5 @@
+#T1: Host name (/CN=localhost/) as OU name in the server certificate, server certificate verification should fail.
+#T2: Host name (localhost) as common name in the server certificate, server certificate verification should pass.
+Variable_name	Value
+Ssl_version	TLS_VERSION
+# restart server using restart
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 75c8597590a..ac874f9943c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7110,6 +7110,20 @@ NULL
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
 #
+# MDEV-7122
+# Assertion `0' failed in subselect_hash_sj_engine::init
+#
+SET SESSION big_tables=1;
+CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+INSERT INTO t1 VALUES(0),(0),(0);
+SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1);
+a
+0
+0
+0
+DROP TABLE t1;
+SET SESSION big_tables=0;
+#
 # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE ||
 # m_lock_type != 2' failed in  handler::ha_index_read_map
 #
diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result
index e5b36055c91..79bca388181 100644
--- a/mysql-test/r/subselect_extra_no_semijoin.result
+++ b/mysql-test/r/subselect_extra_no_semijoin.result
@@ -349,9 +349,9 @@ WHERE t.a IN (SELECT b FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-3	MATERIALIZED	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+3	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,0 in ( <materialize> (select 0 from dual ), <primary_index_lookup>(0 in <temporary table> on distinct_key where ((0 = `<subquery3>`.`b`))))))
+Note	1003	select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from dual where (<cache>(0) = 0))))
 SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0
 WHERE t.a IN (SELECT b FROM t1);
 a	a	b
@@ -362,9 +362,9 @@ WHERE t.a IN (SELECT b FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-2	MATERIALIZED	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,0 in ( <materialize> (select 0 from dual ), <primary_index_lookup>(0 in <temporary table> on distinct_key where ((0 = `<subquery2>`.`b`))))))
+Note	1003	select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from dual where (<cache>(0) = 0))))
 DROP VIEW v1;
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index e6238af4cdc..6a5bec062c7 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -7110,6 +7110,20 @@ NULL
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
 #
+# MDEV-7122
+# Assertion `0' failed in subselect_hash_sj_engine::init
+#
+SET SESSION big_tables=1;
+CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+INSERT INTO t1 VALUES(0),(0),(0);
+SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1);
+a
+0
+0
+0
+DROP TABLE t1;
+SET SESSION big_tables=0;
+#
 # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE ||
 # m_lock_type != 2' failed in  handler::ha_index_read_map
 #
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 70edc6463c4..a574319b175 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7103,6 +7103,20 @@ NULL
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
 #
+# MDEV-7122
+# Assertion `0' failed in subselect_hash_sj_engine::init
+#
+SET SESSION big_tables=1;
+CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+INSERT INTO t1 VALUES(0),(0),(0);
+SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1);
+a
+0
+0
+0
+DROP TABLE t1;
+SET SESSION big_tables=0;
+#
 # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE ||
 # m_lock_type != 2' failed in  handler::ha_index_read_map
 #
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index c89fd132b35..62eecd568f0 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7101,6 +7101,20 @@ NULL
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
 #
+# MDEV-7122
+# Assertion `0' failed in subselect_hash_sj_engine::init
+#
+SET SESSION big_tables=1;
+CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+INSERT INTO t1 VALUES(0),(0),(0);
+SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1);
+a
+0
+0
+0
+DROP TABLE t1;
+SET SESSION big_tables=0;
+#
 # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE ||
 # m_lock_type != 2' failed in  handler::ha_index_read_map
 #
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index b12bf216327..174c791489c 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7116,6 +7116,20 @@ NULL
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
 #
+# MDEV-7122
+# Assertion `0' failed in subselect_hash_sj_engine::init
+#
+SET SESSION big_tables=1;
+CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+INSERT INTO t1 VALUES(0),(0),(0);
+SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1);
+a
+0
+0
+0
+DROP TABLE t1;
+SET SESSION big_tables=0;
+#
 # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE ||
 # m_lock_type != 2' failed in  handler::ha_index_read_map
 #
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 54f145d5c5e..e0d62ac6d14 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7101,6 +7101,20 @@ NULL
 deallocate prepare stmt;
 drop table t1,t2,t3,t4;
 #
+# MDEV-7122
+# Assertion `0' failed in subselect_hash_sj_engine::init
+#
+SET SESSION big_tables=1;
+CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
+INSERT INTO t1 VALUES(0),(0),(0);
+SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1);
+a
+0
+0
+0
+DROP TABLE t1;
+SET SESSION big_tables=0;
+#
 # MDEV-7930: Assertion `table_share->tmp_table != NO_TMP_TABLE ||
 # m_lock_type != 2' failed in  handler::ha_index_read_map
 #
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 18a79333e10..be252802c15 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -3003,4 +3003,69 @@ explain
 select 1 from t1 where _cp932 "1" in (select '1' from t1);
 ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '='
 drop table t1;
+#
+# MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside
+#
+set @tmp_mdev7823=@@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (f2 INT, KEY(f2));
+INSERT INTO t2 VALUES (8),(0);
+CREATE TABLE t3 (f3 INT);
+INSERT INTO t3 VALUES (1),(2);
+CREATE TABLE t4 (f4 INT);
+INSERT INTO t4 VALUES (0),(5);
+explain 
+SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
+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	ref	f2	f2	5	const	0	Using where; Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
+f1	f2	f3
+1	0	1
+1	0	2
+drop table t1,t2,t3,t4;
+set optimizer_switch= @tmp_mdev7823;
+#
+# MDEV-6859: scalar subqueries in a comparison produced unexpected result
+#
+set @tmp_mdev6859=@@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (
+project_number varchar(50) NOT NULL,
+PRIMARY KEY (project_number)
+) ENGINE=MyISAM;
+INSERT INTO t1 (project_number) VALUES ('aaa'),('bbb');
+CREATE TABLE t2 (
+id int(10) unsigned NOT NULL AUTO_INCREMENT,
+project_number varchar(50) NOT NULL,
+history_date date NOT NULL,
+country varchar(50) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=MyISAM;
+INSERT INTO t2 (id, project_number, history_date, country) VALUES
+(1, 'aaa', '2014-08-09', 'france'),(2, 'aaa', '2014-09-09', 'singapore');
+CREATE TABLE t3 (
+region varchar(50) NOT NULL,
+country varchar(50) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t3 (region, country) VALUES ('apac', 'singapore'),('eame', 'france');
+SELECT SQL_NO_CACHE a.project_number 
+FROM t1 a 
+WHERE ( SELECT z.country 
+FROM t2 z 
+WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' 
+ORDER BY z.id DESC LIMIT 1 
+) IN ( 
+SELECT r.country 
+FROM t3 r 
+WHERE r.region = 'eame'
+      );
+project_number
+aaa
+drop table t1, t2, t3;
+set optimizer_switch= @tmp_mdev6859;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 13dc990b806..8e95b963971 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -3017,6 +3017,71 @@ explain
 select 1 from t1 where _cp932 "1" in (select '1' from t1);
 ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '='
 drop table t1;
+#
+# MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside
+#
+set @tmp_mdev7823=@@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (f2 INT, KEY(f2));
+INSERT INTO t2 VALUES (8),(0);
+CREATE TABLE t3 (f3 INT);
+INSERT INTO t3 VALUES (1),(2);
+CREATE TABLE t4 (f4 INT);
+INSERT INTO t4 VALUES (0),(5);
+explain 
+SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
+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	ref	f2	f2	5	const	0	Using where; Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
+f1	f2	f3
+1	0	1
+1	0	2
+drop table t1,t2,t3,t4;
+set optimizer_switch= @tmp_mdev7823;
+#
+# MDEV-6859: scalar subqueries in a comparison produced unexpected result
+#
+set @tmp_mdev6859=@@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (
+project_number varchar(50) NOT NULL,
+PRIMARY KEY (project_number)
+) ENGINE=MyISAM;
+INSERT INTO t1 (project_number) VALUES ('aaa'),('bbb');
+CREATE TABLE t2 (
+id int(10) unsigned NOT NULL AUTO_INCREMENT,
+project_number varchar(50) NOT NULL,
+history_date date NOT NULL,
+country varchar(50) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=MyISAM;
+INSERT INTO t2 (id, project_number, history_date, country) VALUES
+(1, 'aaa', '2014-08-09', 'france'),(2, 'aaa', '2014-09-09', 'singapore');
+CREATE TABLE t3 (
+region varchar(50) NOT NULL,
+country varchar(50) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t3 (region, country) VALUES ('apac', 'singapore'),('eame', 'france');
+SELECT SQL_NO_CACHE a.project_number 
+FROM t1 a 
+WHERE ( SELECT z.country 
+FROM t2 z 
+WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' 
+ORDER BY z.id DESC LIMIT 1 
+) IN ( 
+SELECT r.country 
+FROM t3 r 
+WHERE r.region = 'eame'
+      );
+project_number
+aaa
+drop table t1, t2, t3;
+set optimizer_switch= @tmp_mdev6859;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 1de9b989887..54f2aa91f2c 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -439,6 +439,15 @@ select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
 1
 drop table t1;
 #
+# MDEV-9511 Valgrind warnings 'Invalid read' in Field_newdate::cmp and Field_newdate::val_str
+#
+CREATE TABLE t1 (f1 DATE, f2 VARCHAR(1));
+INSERT INTO t1 VALUES ('2003-04-27','a'),('1900-01-01','a');
+SELECT GROUP_CONCAT(f2, IF(f1, f2, f1), f1 ORDER BY 2,1,3) FROM t1;
+GROUP_CONCAT(f2, IF(f1, f2, f1), f1 ORDER BY 2,1,3)
+aa1900-01-01,aa2003-04-27
+DROP TABLE t1;
+#
 # Start of 10.1 tests
 #
 #
@@ -511,12 +520,6 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = DATE'2001-01-01')
 DROP TABLE t1;
 #
-# End of 10.1 tests
-#
-#
-# Start of 10.1 tests
-#
-#
 #     MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'
 #
 CREATE TABLE t1 (a DATE);
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index dec85fbcd4b..8ee72c35d0b 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2425,28 +2425,28 @@ CREATE VIEW v1 AS SELECT id FROM t1;
 OPTIMIZE TABLE v1;
 Table	Op	Msg_type	Msg_text
 test.v1	optimize	Error	'test.v1' is not BASE TABLE
-test.v1	optimize	error	Corrupt
+test.v1	optimize	status	Operation failed
 ANALYZE TABLE v1;
 Table	Op	Msg_type	Msg_text
 test.v1	analyze	Error	'test.v1' is not BASE TABLE
-test.v1	analyze	error	Corrupt
+test.v1	analyze	status	Operation failed
 REPAIR TABLE v1;
 Table	Op	Msg_type	Msg_text
 test.v1	repair	Error	'test.v1' is not BASE TABLE
-test.v1	repair	error	Corrupt
+test.v1	repair	status	Operation failed
 DROP TABLE t1;
 OPTIMIZE TABLE v1;
 Table	Op	Msg_type	Msg_text
 test.v1	optimize	Error	'test.v1' is not BASE TABLE
-test.v1	optimize	error	Corrupt
+test.v1	optimize	status	Operation failed
 ANALYZE TABLE v1;
 Table	Op	Msg_type	Msg_text
 test.v1	analyze	Error	'test.v1' is not BASE TABLE
-test.v1	analyze	error	Corrupt
+test.v1	analyze	status	Operation failed
 REPAIR TABLE v1;
 Table	Op	Msg_type	Msg_text
 test.v1	repair	Error	'test.v1' is not BASE TABLE
-test.v1	repair	error	Corrupt
+test.v1	repair	status	Operation failed
 DROP VIEW v1;
 create definer = current_user() sql security invoker view v1 as select 1;
 show create view v1;
@@ -5540,6 +5540,14 @@ execute stmt;
 deallocate prepare stmt;
 drop view v1,v2;
 drop table `t1`;
+create table t1 (a int, b int);
+create view v1 as select a+b from t1;
+alter table v1 check partition p1;
+Table	Op	Msg_type	Msg_text
+test.v1	check	Error	'test.v1' is not BASE TABLE
+test.v1	check	status	Operation failed
+drop view v1;
+drop table t1;
 # -----------------------------------------------------------------
 # -- End of 5.5 tests.
 # -----------------------------------------------------------------