merge with 5.5

24 files changed, 485 insertions, 99 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 5446406924f..ba01db4a66f 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1687,6 +1687,7 @@ SELECT t.b, t.c, t1.a
 FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
 WHERE t.b AND t.c = t1.a;
 b	c	a
+8	c	c
 EXPLAIN EXTENDED
 SELECT t.b, t.c, t1.a
 FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
@@ -1701,6 +1702,7 @@ SELECT t.b, t.c, t1.a
 FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
 WHERE t.b <> 0 AND t.c = t1.a;
 b	c	a
+8	c	c
 INSERT INTO t3 VALUES (100), (200);
 EXPLAIN EXTENDED
 SELECT t.b, t.c, t1.a
@@ -1716,7 +1718,7 @@ SELECT t.b, t.c, t1.a
 FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
 WHERE t.b AND t.c = t1.a;
 b	c	a
-NULL	NULL	c
+8	c	c
 EXPLAIN EXTENDED
 SELECT t.b, t.c, t1.a
 FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
@@ -1731,7 +1733,7 @@ SELECT t.b, t.c, t1.a
 FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
 WHERE t.b <> 0 AND t.c = t1.a;
 b	c	a
-NULL	NULL	c
+8	c	c
 SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2,t3;
 #
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 205197bc7f6..80729545902 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -2867,3 +2867,16 @@ SET @@global.max_allowed_packet:= @tmp_max;
 #
 # End of 5.5 tests
 #
+SELECT @tmp_max:= @@global.max_allowed_packet;
+@tmp_max:= @@global.max_allowed_packet
+1048576
+SET @@global.max_allowed_packet=1024*1024*1024;
+SELECT @@global.max_allowed_packet;
+@@global.max_allowed_packet
+1073741824
+SELECT CHAR_LENGTH(EXPORT_SET(1,1,1,REPEAT(1,100000000)));
+CHAR_LENGTH(EXPORT_SET(1,1,1,REPEAT(1,100000000)))
+NULL
+Warnings:
+Warning	1301	Result of repeat() was larger than max_allowed_packet (1048576) - truncated
+SET @@global.max_allowed_packet:= @tmp_max;
diff --git a/mysql-test/r/information_schema_all_engines.result b/mysql-test/r/information_schema_all_engines.result
index 7d47f0404bb..4dac9a3e53c 100644
--- a/mysql-test/r/information_schema_all_engines.result
+++ b/mysql-test/r/information_schema_all_engines.result
@@ -16,6 +16,7 @@ INDEX_STATISTICS
 INNODB_BUFFER_POOL_PAGES
 INNODB_BUFFER_POOL_PAGES_BLOB
 INNODB_BUFFER_POOL_PAGES_INDEX
+INNODB_CHANGED_PAGES
 INNODB_CMP
 INNODB_CMPMEM
 INNODB_CMPMEM_RESET
@@ -88,6 +89,7 @@ INDEX_STATISTICS	TABLE_SCHEMA
 INNODB_BUFFER_POOL_PAGES	page_type
 INNODB_BUFFER_POOL_PAGES_BLOB	space_id
 INNODB_BUFFER_POOL_PAGES_INDEX	index_id
+INNODB_CHANGED_PAGES	space_id
 INNODB_CMP	page_size
 INNODB_CMPMEM	page_size
 INNODB_CMPMEM_RESET	page_size
@@ -160,6 +162,7 @@ INDEX_STATISTICS	TABLE_SCHEMA
 INNODB_BUFFER_POOL_PAGES	page_type
 INNODB_BUFFER_POOL_PAGES_BLOB	space_id
 INNODB_BUFFER_POOL_PAGES_INDEX	index_id
+INNODB_CHANGED_PAGES	space_id
 INNODB_CMP	page_size
 INNODB_CMPMEM	page_size
 INNODB_CMPMEM_RESET	page_size
@@ -238,6 +241,7 @@ INDEX_STATISTICS	information_schema.INDEX_STATISTICS	1
 INNODB_BUFFER_POOL_PAGES	information_schema.INNODB_BUFFER_POOL_PAGES	1
 INNODB_BUFFER_POOL_PAGES_BLOB	information_schema.INNODB_BUFFER_POOL_PAGES_BLOB	1
 INNODB_BUFFER_POOL_PAGES_INDEX	information_schema.INNODB_BUFFER_POOL_PAGES_INDEX	1
+INNODB_CHANGED_PAGES	information_schema.INNODB_CHANGED_PAGES	1
 INNODB_CMP	information_schema.INNODB_CMP	1
 INNODB_CMPMEM	information_schema.INNODB_CMPMEM	1
 INNODB_CMPMEM_RESET	information_schema.INNODB_CMPMEM_RESET	1
@@ -299,6 +303,7 @@ Database: information_schema
 | INNODB_BUFFER_POOL_PAGES              |
 | INNODB_BUFFER_POOL_PAGES_BLOB         |
 | INNODB_BUFFER_POOL_PAGES_INDEX        |
+| INNODB_CHANGED_PAGES                  |
 | INNODB_CMP                            |
 | INNODB_CMPMEM                         |
 | INNODB_CMPMEM_RESET                   |
@@ -361,6 +366,7 @@ Database: INFORMATION_SCHEMA
 | INNODB_BUFFER_POOL_PAGES              |
 | INNODB_BUFFER_POOL_PAGES_BLOB         |
 | INNODB_BUFFER_POOL_PAGES_INDEX        |
+| INNODB_CHANGED_PAGES                  |
 | INNODB_CMP                            |
 | INNODB_CMPMEM                         |
 | INNODB_CMPMEM_RESET                   |
@@ -411,5 +417,5 @@ Wildcard: inf_rmation_schema
 | information_schema |
 SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA;
 table_schema	count(*)
-information_schema	57
+information_schema	58
 mysql	23
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 6471a2f8bbd..bb455f2b39a 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5589,4 +5589,52 @@ set join_buffer_size=default;
 set join_cache_level=default;
 set optimizer_switch=@tmp_optimizer_switch;
 DROP TABLE t1,t2,t3;
+#
+# Bug #1058071: LEFT JOIN using blobs 
+# (mdev-564)    when join buffer size is small 
+#
+CREATE TABLE t1 (
+col269 decimal(31,10) unsigned DEFAULT NULL,
+col280 multipoint DEFAULT NULL,
+col281 tinyint(1) DEFAULT NULL,
+col282 time NOT NULL,
+col284 datetime DEFAULT NULL,
+col286 date DEFAULT NULL,
+col287 datetime DEFAULT NULL,
+col288 decimal(30,29) DEFAULT NULL,
+col291 time DEFAULT NULL,
+col292 time DEFAULT NULL
+) ENGINE=Aria;
+INSERT INTO t1 VALUES
+(0.0,PointFromText('POINT(9 0)'),0,'11:24:05','2013-04-14 21:30:28',NULL,'2011-12-20 06:00:34',9.9,'13:04:39',NULL),
+(0.0,NULL,127,'05:43:12','2012-09-05 06:15:27','2027-01-01','2011-10-29 10:48:29',0.0,'06:24:05','11:33:37'),
+(0.0,NULL,127,'12:54:41','2013-01-12 11:32:58','2011-11-03','2013-01-03 02:00:34',00,'11:54:15','20:19:15'),
+(0.0,PointFromText('POINT(9 0)'),0,'19:48:07','2012-07-16 15:45:25','2012-03-25','2013-09-07 17:21:52',0.5,'17:36:54','21:24:19'),
+(0.0,PointFromText('POINT(9 0)'),0,'03:43:48','2012-09-28 00:00:00','2012-06-26','2011-11-16 05:01:09',00,'01:25:42','19:30:06'),
+(0.0,LineStringFromText('LINESTRING(0 0,9 9,0 0,9 0,0 0)'),127,'11:33:21','2012-03-31 10:29:22','2012-10-10','2012-04-21 19:21:06',NULL,'05:13:22','09:48:34'),
+(NULL,PointFromText('POINT(9 0)'),127,'00:00:00','0000-00-00','2012-04-04 21:26:12','2013-03-04',0.0,'12:54:30',NULL),
+(NULL,PointFromText('POINT(9 0)'),1,'00:00:00','2013-05-01 22:37:49','2013-06-26','2012-09-22 17:31:03',0.0,'08:09:57','11:15:36');
+Warnings:
+Note	1265	Data truncated for column 'col286' at row 7
+CREATE TABLE t2 (b int) ENGINE=Aria;
+INSERT INTO t2 VALUES (NULL);
+CREATE TABLE t3 (c int) ENGINE=Aria;
+INSERT INTO t3 VALUES (NULL);
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on';
+set join_buffer_size=128;
+EXPLAIN
+SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1
+GROUP BY elt(t1.col282,1,t1.col280);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1
+GROUP BY elt(t1.col282,1,t1.col280);
+c
+1
+set join_buffer_size=default;
+set optimizer_switch=@tmp_optimizer_switch;
+DROP table t1,t2,t3;
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index f4242f17a14..a51798a5883 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -318,7 +318,7 @@ LIMIT ROWS EXAMINED 9;
 c1
 bb
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 10 rows, which exceeds LIMIT ROWS EXAMINED (9). The query result may be incomplete.
+Warning	1931	Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (9). The query result may be incomplete.
 Same as above, without subquery cache
 set @@optimizer_switch='subquery_cache=off';
 select * from t1
@@ -347,7 +347,7 @@ LIMIT ROWS EXAMINED 5;
 c1
 bb
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 6 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete.
+Warning	1931	Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete.
 Subqueries with materialization
 set @@optimizer_switch='semijoin=off,in_to_exists=off,materialization=on,subquery_cache=on';
 explain
diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result
index 52f98494942..3ccd1451bc4 100644
--- a/mysql-test/r/log_state.result
+++ b/mysql-test/r/log_state.result
@@ -199,7 +199,7 @@ SELECT @@general_log, @@log;
 1	1
 SET GLOBAL log = 0;
 Warnings:
-Warning	1287	The syntax '@@log' is deprecated and will be removed in MariaDB 10.1. Please use '@@general_log' instead
+Warning	1287	'@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead
 SHOW VARIABLES LIKE 'general_log';
 Variable_name	Value
 general_log	OFF
@@ -230,7 +230,7 @@ SELECT @@slow_query_log, @@log_slow_queries;
 0	0
 SET GLOBAL log_slow_queries = 0;
 Warnings:
-Warning	1287	The syntax '@@log_slow_queries' is deprecated and will be removed in MariaDB 10.1. Please use '@@slow_query_log' instead
+Warning	1287	'@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead
 SHOW VARIABLES LIKE 'slow_query_log';
 Variable_name	Value
 slow_query_log	OFF
@@ -283,16 +283,16 @@ SET GLOBAL slow_query_log_file = @old_slow_query_log_file;
 deprecated:
 SET GLOBAL log = 0;
 Warnings:
-Warning	1287	The syntax '@@log' is deprecated and will be removed in MariaDB 10.1. Please use '@@general_log' instead
+Warning	1287	'@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead
 SET GLOBAL log_slow_queries = 0;
 Warnings:
-Warning	1287	The syntax '@@log_slow_queries' is deprecated and will be removed in MariaDB 10.1. Please use '@@slow_query_log' instead
+Warning	1287	'@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead
 SET GLOBAL log = DEFAULT;
 Warnings:
-Warning	1287	The syntax '@@log' is deprecated and will be removed in MariaDB 10.1. Please use '@@general_log' instead
+Warning	1287	'@@log' is deprecated and will be removed in a future release. Please use '@@general_log' instead
 SET GLOBAL log_slow_queries = DEFAULT;
 Warnings:
-Warning	1287	The syntax '@@log_slow_queries' is deprecated and will be removed in MariaDB 10.1. Please use '@@slow_query_log' instead
+Warning	1287	'@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead
 not deprecated:
 SELECT @@global.general_log_file INTO @my_glf;
 SELECT @@global.slow_query_log_file INTO @my_sqlf;
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index 5b8e6ea3348..c065f949802 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -204,6 +204,10 @@ The following options may be given as the first argument:
  -?, --help          Display this help and exit.
  --ignore-builtin-innodb 
  Disable initialization of builtin InnoDB plugin
+ --ignore-db-dirs=name 
+ Specifies a directory to add to the ignore list when
+ collecting database names from the datadir. Put a blank
+ argument to reset the list accumulated so far.
  --init-connect=name Command(s) that are executed for each new connection
  (unless the user has SUPER privilege)
  --init-file=name    Read SQL commands from this file at startup
@@ -979,6 +983,7 @@ general-log FALSE
 group-concat-max-len 1024
 help TRUE
 ignore-builtin-innodb FALSE
+ignore-db-dirs 
 init-connect 
 init-file (No default value)
 init-rpl-role MASTER
diff --git a/mysql-test/r/openssl_1.result b/mysql-test/r/openssl_1.result
index 439930b8fe4..b86925e0eb8 100644
--- a/mysql-test/r/openssl_1.result
+++ b/mysql-test/r/openssl_1.result
@@ -44,9 +44,9 @@ ERROR 42000: DELETE command denied to user 'ssl_user4'@'localhost' for table 't1
 drop user ssl_user1@localhost, ssl_user2@localhost,
 ssl_user3@localhost, ssl_user4@localhost, ssl_user5@localhost;
 drop table t1;
-mysqltest: Could not open connection 'default': 2026 SSL connection error: ASN: bad other signature confirmation
-mysqltest: Could not open connection 'default': 2026 SSL connection error: ASN: bad other signature confirmation
-mysqltest: Could not open connection 'default': 2026 SSL connection error: ASN: bad other signature confirmation
+mysqltest: Could not open connection 'default': 2026 SSL connection error: xxxx
+mysqltest: Could not open connection 'default': 2026 SSL connection error: xxxx
+mysqltest: Could not open connection 'default': 2026 SSL connection error: xxxx
 SSL error: Unable to get private key from ''
 mysqltest: Could not open connection 'default': 2026 SSL connection error: Unable to get private key
 SSL error: Unable to get certificate from ''
diff --git a/mysql-test/r/order_fill_sortbuf.result b/mysql-test/r/order_fill_sortbuf.result
index 2226e842901..6a0bd9d966b 100644
--- a/mysql-test/r/order_fill_sortbuf.result
+++ b/mysql-test/r/order_fill_sortbuf.result
@@ -1,4 +1,5 @@
 drop table if exists t1,t2;
+set @@sort_buffer_size=32804;
 CREATE TABLE `t1` (
 `id` int(11) NOT NULL default '0',
 `id2` int(11) NOT NULL default '0',
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index 045a62a1d5a..3ca0cf8554f 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -777,3 +777,14 @@ execute stmt1 ;
 prepare stmt1 from ' select * from t5 ' ;
 execute stmt1 ;
 drop table t1, t5, t9;
+#
+# testcase for bug#11765413 - Crash with dependent subquery and
+#                             prepared statement
+create table t1 (c1 int);
+insert into t1 values (1);
+prepare stmt1 from "select 1 from t1 where 1=(select 1 from t1 having c1)";
+execute stmt1;
+1
+1
+drop prepare stmt1;
+drop table t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 30d5ca00bc0..378b20829f3 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4887,7 +4887,7 @@ CREATE TABLE t5 (f1 int) ;
 INSERT INTO t5 VALUES (20),(5);
 CREATE TABLE t6(f1 int);
 INSERT INTO t6 VALUES (9),(7);
-SET SESSION join_buffer_size = 2048;
+SET SESSION join_buffer_size = 2176;
 EXPLAIN
 SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -4901,50 +4901,50 @@ SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
 f1	f1	f1	f1	f2	f1	f1
 3	9	NULL	NULL	NULL	20	9
 7	9	NULL	NULL	NULL	20	9
-3	9	NULL	NULL	NULL	20	7
-7	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	5	7
-7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	20	9
 3	9	NULL	NULL	NULL	20	9
-18	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	20	7
-18	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	5	9
-18	9	NULL	NULL	NULL	5	7
-3	9	NULL	NULL	NULL	5	7
 7	9	NULL	NULL	NULL	20	9
-18	9	NULL	NULL	NULL	20	9
+3	9	NULL	NULL	NULL	20	7
 7	9	NULL	NULL	NULL	20	7
 18	9	NULL	NULL	NULL	20	7
-7	9	NULL	NULL	NULL	5	9
-18	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	7
-18	9	NULL	NULL	NULL	5	7
-3	9	NULL	NULL	NULL	20	9
-7	9	NULL	NULL	NULL	20	9
 3	9	NULL	NULL	NULL	20	7
 7	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	5	7
-7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	20	9
 3	9	NULL	NULL	NULL	20	9
+3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	5	9
 18	9	NULL	NULL	NULL	20	7
 3	9	NULL	NULL	NULL	20	7
+3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	5	7
+18	9	NULL	NULL	NULL	5	7
+3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	5	9
 18	9	NULL	NULL	NULL	5	9
 3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	20	9
+3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	5	7
 3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	20	7
+18	9	NULL	NULL	NULL	20	9
+3	9	NULL	NULL	NULL	20	9
 7	9	NULL	NULL	NULL	20	9
 18	9	NULL	NULL	NULL	20	9
+7	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	20	7
+3	9	NULL	NULL	NULL	20	7
 7	9	NULL	NULL	NULL	20	7
 18	9	NULL	NULL	NULL	20	7
+7	9	NULL	NULL	NULL	5	7
+18	9	NULL	NULL	NULL	5	9
+3	9	NULL	NULL	NULL	5	9
 7	9	NULL	NULL	NULL	5	9
 18	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	5	7
+3	9	NULL	NULL	NULL	5	7
 7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	5	7
 SET SESSION join_buffer_size = DEFAULT;
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index a9c74afdd9c..b5c6f89b87b 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -4898,7 +4898,7 @@ CREATE TABLE t5 (f1 int) ;
 INSERT INTO t5 VALUES (20),(5);
 CREATE TABLE t6(f1 int);
 INSERT INTO t6 VALUES (9),(7);
-SET SESSION join_buffer_size = 2048;
+SET SESSION join_buffer_size = 2176;
 EXPLAIN
 SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -4913,48 +4913,48 @@ f1	f1	f1	f1	f2	f1	f1
 3	9	NULL	NULL	NULL	20	9
 7	9	NULL	NULL	NULL	20	9
 18	9	NULL	NULL	NULL	20	9
-3	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	9
-18	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	20	7
-7	9	NULL	NULL	NULL	20	7
-18	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	5	7
-7	9	NULL	NULL	NULL	5	7
-18	9	NULL	NULL	NULL	5	7
 3	9	NULL	NULL	NULL	20	9
 7	9	NULL	NULL	NULL	20	9
 18	9	NULL	NULL	NULL	20	9
-3	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	9
-18	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	20	7
-7	9	NULL	NULL	NULL	20	7
-18	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	5	7
-7	9	NULL	NULL	NULL	5	7
-18	9	NULL	NULL	NULL	5	7
 3	9	NULL	NULL	NULL	20	9
 7	9	NULL	NULL	NULL	20	9
 18	9	NULL	NULL	NULL	20	9
-3	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	9
-18	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	20	7
-7	9	NULL	NULL	NULL	20	7
-18	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	5	7
-7	9	NULL	NULL	NULL	5	7
-18	9	NULL	NULL	NULL	5	7
 3	9	NULL	NULL	NULL	20	9
 7	9	NULL	NULL	NULL	20	9
 18	9	NULL	NULL	NULL	20	9
 3	9	NULL	NULL	NULL	5	9
 7	9	NULL	NULL	NULL	5	9
 18	9	NULL	NULL	NULL	5	9
+3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	5	9
+3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	5	9
+3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	5	9
 3	9	NULL	NULL	NULL	20	7
 7	9	NULL	NULL	NULL	20	7
 18	9	NULL	NULL	NULL	20	7
+3	9	NULL	NULL	NULL	20	7
+7	9	NULL	NULL	NULL	20	7
+18	9	NULL	NULL	NULL	20	7
+3	9	NULL	NULL	NULL	20	7
+7	9	NULL	NULL	NULL	20	7
+18	9	NULL	NULL	NULL	20	7
+3	9	NULL	NULL	NULL	20	7
+7	9	NULL	NULL	NULL	20	7
+18	9	NULL	NULL	NULL	20	7
+3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	5	7
+18	9	NULL	NULL	NULL	5	7
+3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	5	7
+18	9	NULL	NULL	NULL	5	7
+3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	5	7
+18	9	NULL	NULL	NULL	5	7
 3	9	NULL	NULL	NULL	5	7
 7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	5	7
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 30d5ca00bc0..378b20829f3 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -4887,7 +4887,7 @@ CREATE TABLE t5 (f1 int) ;
 INSERT INTO t5 VALUES (20),(5);
 CREATE TABLE t6(f1 int);
 INSERT INTO t6 VALUES (9),(7);
-SET SESSION join_buffer_size = 2048;
+SET SESSION join_buffer_size = 2176;
 EXPLAIN
 SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -4901,50 +4901,50 @@ SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
 f1	f1	f1	f1	f2	f1	f1
 3	9	NULL	NULL	NULL	20	9
 7	9	NULL	NULL	NULL	20	9
-3	9	NULL	NULL	NULL	20	7
-7	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	5	7
-7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	20	9
 3	9	NULL	NULL	NULL	20	9
-18	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	20	7
-18	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	5	9
-18	9	NULL	NULL	NULL	5	7
-3	9	NULL	NULL	NULL	5	7
 7	9	NULL	NULL	NULL	20	9
-18	9	NULL	NULL	NULL	20	9
+3	9	NULL	NULL	NULL	20	7
 7	9	NULL	NULL	NULL	20	7
 18	9	NULL	NULL	NULL	20	7
-7	9	NULL	NULL	NULL	5	9
-18	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	7
-18	9	NULL	NULL	NULL	5	7
-3	9	NULL	NULL	NULL	20	9
-7	9	NULL	NULL	NULL	20	9
 3	9	NULL	NULL	NULL	20	7
 7	9	NULL	NULL	NULL	20	7
-3	9	NULL	NULL	NULL	5	9
-7	9	NULL	NULL	NULL	5	9
-3	9	NULL	NULL	NULL	5	7
-7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	20	9
 3	9	NULL	NULL	NULL	20	9
+3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	5	9
 18	9	NULL	NULL	NULL	20	7
 3	9	NULL	NULL	NULL	20	7
+3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	5	7
+18	9	NULL	NULL	NULL	5	7
+3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	5	9
 18	9	NULL	NULL	NULL	5	9
 3	9	NULL	NULL	NULL	5	9
+7	9	NULL	NULL	NULL	20	9
+3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	5	7
 3	9	NULL	NULL	NULL	5	7
+7	9	NULL	NULL	NULL	20	7
+18	9	NULL	NULL	NULL	20	9
+3	9	NULL	NULL	NULL	20	9
 7	9	NULL	NULL	NULL	20	9
 18	9	NULL	NULL	NULL	20	9
+7	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	20	7
+3	9	NULL	NULL	NULL	20	7
 7	9	NULL	NULL	NULL	20	7
 18	9	NULL	NULL	NULL	20	7
+7	9	NULL	NULL	NULL	5	7
+18	9	NULL	NULL	NULL	5	9
+3	9	NULL	NULL	NULL	5	9
 7	9	NULL	NULL	NULL	5	9
 18	9	NULL	NULL	NULL	5	9
+18	9	NULL	NULL	NULL	5	7
+3	9	NULL	NULL	NULL	5	7
 7	9	NULL	NULL	NULL	5	7
 18	9	NULL	NULL	NULL	5	7
 SET SESSION join_buffer_size = DEFAULT;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index f775336299b..a4bad836d1f 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6638,6 +6638,23 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
 #
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
+# return optimizer switch changed in the beginning of this test
+set optimizer_switch=@subselect_tmp;
+#
 # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
@@ -6847,6 +6864,27 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
 # 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));
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index ed00e4ef684..7eff7f949a8 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -179,4 +179,23 @@ pk	a	b
 SET optimizer_switch=@tmp_optimizer_switch;
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+#
+# MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed
+#
+CREATE TABLE t1 (a int, b int, INDEX idx(a));
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,3), (2,1);
+CREATE TABLE t2 (a int, b int, INDEX idx(a));
+INSERT INTO t2 VALUES (2,1), (6,4), (7,6), (9,4);
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,3);
+SELECT * FROM t3
+WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
+WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
+AND t3.b = t1.b
+GROUP BY t1.b);
+a	b
+1	0
+1	1
+1	3
+DROP TABLE t1, t2, t3;
 set optimizer_switch=@subselect2_test_tmp;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index b33e7e113f2..049c4d14b1a 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -126,7 +126,7 @@ Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
-Handler_read_rnd_next	50
+Handler_read_rnd_next	41
 select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
 Z
 No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 4660cd60603..815a8985d44 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -136,7 +136,7 @@ Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
-Handler_read_rnd_next	50
+Handler_read_rnd_next	41
 select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
 Z
 No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 7f290fb5b03..c35075d80a1 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -430,7 +430,7 @@ set @old_optimizer_switch          = @@session.optimizer_switch,
 SET SESSION OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off,mrr=on';
 SET SESSION engine_condition_pushdown = 1;
 Warnings:
-Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MariaDB 10.1. Please use '@@optimizer_switch' instead
+Warning	1287	'@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
 SELECT `time_nokey` G1  FROM t1  WHERE ( `varchar_nokey`  , `varchar_key`  )  IN ( 
 SELECT `varchar_nokey`  , `varchar_nokey`  )  AND `varchar_key`  >= 'c' HAVING G1  ORDER 
 BY `pk`   ;
@@ -438,7 +438,7 @@ G1
 set @@session.optimizer_switch          = @old_optimizer_switch,
 @@session.engine_condition_pushdown = @old_engine_condition_pushdown;
 Warnings:
-Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MariaDB 10.1. Please use '@@optimizer_switch' instead
+Warning	1287	'@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
 DROP TABLE t1;
 #
 # During work with BUG#45863 I had problems with a query that was
@@ -625,7 +625,7 @@ SELECT @old_icp:=@@engine_condition_pushdown;
 #
 SET SESSION engine_condition_pushdown = 'ON';
 Warnings:
-Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MariaDB 10.1. Please use '@@optimizer_switch' instead
+Warning	1287	'@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
 
 SELECT pk
 FROM t2
@@ -640,7 +640,7 @@ pk
 # Restore old value for Index condition pushdown
 SET SESSION engine_condition_pushdown=@old_icp;
 Warnings:
-Warning	1287	The syntax '@@engine_condition_pushdown' is deprecated and will be removed in MariaDB 10.1. Please use '@@optimizer_switch' instead
+Warning	1287	'@@engine_condition_pushdown' is deprecated and will be removed in a future release. Please use '@@optimizer_switch' instead
 DROP TABLE t1,t2;
 #
 # End of 5.3 tests.
@@ -1351,7 +1351,7 @@ ON SUBQUERY2_t3.f2)
 GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
-1	PRIMARY	t1	index	NULL	f4	5	NULL	11	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	11	Using where
 2	DEPENDENT SUBQUERY	SUBQUERY2_t1	system	NULL	NULL	NULL	NULL	1	
 2	DEPENDENT SUBQUERY	SUBQUERY2_t2	index	NULL	f4	5	NULL	11	Using index
 2	DEPENDENT SUBQUERY	SUBQUERY2_t3	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer (flat, BNL join)
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index e72d25fdafa..cb9847a0d99 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6637,6 +6637,23 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
 #
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
+# return optimizer switch changed in the beginning of this test
+set optimizer_switch=@subselect_tmp;
+#
 # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
@@ -6845,6 +6862,26 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
 # 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));
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 9030265356b..63eeb816b38 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6633,6 +6633,23 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
 #
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
+# return optimizer switch changed in the beginning of this test
+set optimizer_switch=@subselect_tmp;
+#
 # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
@@ -6842,6 +6859,27 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
 # 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));
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index e68f5990c08..6fd21f8d0b0 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6644,6 +6644,23 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
 #
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
+# return optimizer switch changed in the beginning of this test
+set optimizer_switch=@subselect_tmp;
+#
 # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
@@ -6853,6 +6870,27 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
 # 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));
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index f0ce541294a..b924a18ca8f 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6633,6 +6633,23 @@ a	( 3, 3 ) NOT IN ( SELECT NULL, NULL )
 set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
 #
+# MDEV-521 single value subselect transformation problem
+#
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+f1
+u1
+u2
+DROP TABLE t1;
+# return optimizer switch changed in the beginning of this test
+set optimizer_switch=@subselect_tmp;
+#
 # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 # precomputed and thus not part of optimization
 #
@@ -6842,6 +6859,27 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
+# MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+#
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+a
+drop table t1, t2, t3;
+#
 # 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));
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index a193751048c..ad5e39a2851 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -538,7 +538,7 @@ Warning	1292	Truncated incorrect read_buffer_size value: '100'
 set read_rnd_buffer_size=100;
 set global rpl_recovery_rank=100;
 Warnings:
-Warning	1287	The syntax '@@rpl_recovery_rank' is deprecated and will be removed in MariaDB 10.1.
+Warning	1287	'@@rpl_recovery_rank' is deprecated and will be removed in a future release.
 set global server_id=100;
 set global slow_launch_time=100;
 set sort_buffer_size=100;
@@ -1064,7 +1064,7 @@ set global net_write_timeout         =@my_net_write_timeout;
 set global net_read_timeout          =@my_net_read_timeout;
 set global rpl_recovery_rank         =@my_rpl_recovery_rank;
 Warnings:
-Warning	1287	The syntax '@@rpl_recovery_rank' is deprecated and will be removed in MariaDB 10.1.
+Warning	1287	'@@rpl_recovery_rank' is deprecated and will be removed in a future release.
 set global server_id                 =@my_server_id;
 set global slow_launch_time          =@my_slow_launch_time;
 set global default_storage_engine            =@my_storage_engine;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 5af20fd0c3c..1bcc9fb727f 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4530,6 +4530,55 @@ WHERE t4.a >= v1.a);
 a	a
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+#
+# LP bug #823237: dependent subquery with LEFT JOIN
+#                 referencing view in WHERE
+# (duplicate of LP bug #823189)
+# 
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 ( b int, d int, e int);
+INSERT INTO t2 VALUES (7,8,0);
+CREATE TABLE t3 ( c int);
+INSERT INTO t3 VALUES (0);
+CREATE TABLE t4 (a int, b int, c int);
+INSERT INTO t4 VALUES (93,1,0), (95,NULL,0);
+CREATE VIEW v4 AS SELECT * FROM t4;
+EXPLAIN EXTENDED
+SELECT * FROM t3 , t4 
+WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+WHERE t2.b > t4.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1276	Field or reference 'test.t4.b' of SELECT #2 was resolved in SELECT #1
+Note	1003	select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from dual where (7 > `test`.`t4`.`b`))))
+SELECT * FROM t3 , t4 
+WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+WHERE t2.b > t4.b);
+c	a	b	c
+0	93	1	0
+EXPLAIN EXTENDED
+SELECT * FROM t3, v4 
+WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+WHERE t2.b > v4.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1276	Field or reference 'v4.b' of SELECT #2 was resolved in SELECT #1
+Note	1003	select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from dual where (7 > `test`.`t4`.`b`))))
+SELECT * FROM t3, v4 
+WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+WHERE t2.b > v4.b);
+c	a	b	c
+0	93	1	0
+DROP VIEW v4;
+DROP TABLE t1,t2,t3,t4;
 drop table if exists t_9801;
 drop view if exists v_9801;
 create table t_9801 (s1 int);
@@ -4730,6 +4779,49 @@ id	id	bbb	iddqd	val1
 30631	NULL	NULL	NULL	NULL
 drop view v2;
 drop table t1,t2;
+#
+# MDEV-589 (LP BUG#1007647) :
+# Assertion `vcol_table == 0 || vcol_table == table' failed in
+# fill_record(THD*, List<Item>&, List<Item>&, bool)
+#
+CREATE TABLE t1 (f1 INT, f2 INT);
+CREATE TABLE t2 (f1 INT, f2 INT);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t1 AS a2;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1;
+CREATE ALGORITHM=MERGE VIEW v3 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t2 AS a2;
+CREATE ALGORITHM=MERGE VIEW v4 AS SELECT * FROM v3;
+INSERT INTO v3 (f1, f2) VALUES (1, 2);
+ERROR HY000: Can not modify more than one base table through a join view 'test.v3'
+INSERT INTO v1 (f1, f2) VALUES (1, 2);
+ERROR HY000: Can not modify more than one base table through a join view 'test.v1'
+INSERT INTO v4 (f1, f2) VALUES (1, 2);
+ERROR HY000: Can not modify more than one base table through a join view 'test.v4'
+INSERT INTO v2 (f1, f2) VALUES (1, 2);
+ERROR HY000: Can not modify more than one base table through a join view 'test.v2'
+drop view v4,v3,v2,v1;
+drop table t1,t2;
+#
+# MDEV-3799 fix of above bugfix (MDEV-589) 
+# Wrong result (NULLs instead of real values) with RIGHT JOIN
+# in a FROM subquery and derived_merge=on
+#
+CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(6);
+CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (7),(8);
+SELECT * FROM (  
+SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2
+) AS alias;
+f1	f2
+NULL	7
+NULL	8
+SELECT * FROM (  
+SELECT * FROM t2 LEFT JOIN t1 ON f1 = f2
+) AS alias;
+f2	f1
+7	NULL
+8	NULL
+drop tables t1,t2;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------