Merge 5.5->10.0-base

23 files changed, 793 insertions, 102 deletions

diff --git a/include/probes_mysql_nodtrace.h b/include/probes_mysql_nodtrace.h
index bc3b65a00e5..a84bf7726c3 100644
--- a/include/probes_mysql_nodtrace.h
+++ b/include/probes_mysql_nodtrace.h
@@ -6,121 +6,124 @@
 #define	_PROBES_MYSQL_D
 
 #ifdef	__cplusplus
+#define MYSQL_PROBES_FALSE false
 extern "C" {
+#else
+#define MYSQL_PROBES_FALSE 0
 #endif
 
 #define	MYSQL_CONNECTION_START(arg0, arg1, arg2)
-#define	MYSQL_CONNECTION_START_ENABLED() (0)
+#define	MYSQL_CONNECTION_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_CONNECTION_DONE(arg0, arg1)
-#define	MYSQL_CONNECTION_DONE_ENABLED() (0)
+#define	MYSQL_CONNECTION_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_COMMAND_START(arg0, arg1, arg2, arg3)
-#define	MYSQL_COMMAND_START_ENABLED() (0)
+#define	MYSQL_COMMAND_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_COMMAND_DONE(arg0)
-#define	MYSQL_COMMAND_DONE_ENABLED() (0)
+#define	MYSQL_COMMAND_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_QUERY_START(arg0, arg1, arg2, arg3, arg4)
-#define	MYSQL_QUERY_START_ENABLED() (0)
+#define	MYSQL_QUERY_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_QUERY_DONE(arg0)
-#define	MYSQL_QUERY_DONE_ENABLED() (0)
+#define	MYSQL_QUERY_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_QUERY_PARSE_START(arg0)
-#define	MYSQL_QUERY_PARSE_START_ENABLED() (0)
+#define	MYSQL_QUERY_PARSE_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_QUERY_PARSE_DONE(arg0)
-#define	MYSQL_QUERY_PARSE_DONE_ENABLED() (0)
+#define	MYSQL_QUERY_PARSE_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_QUERY_CACHE_HIT(arg0, arg1)
-#define	MYSQL_QUERY_CACHE_HIT_ENABLED() (0)
+#define	MYSQL_QUERY_CACHE_HIT_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_QUERY_CACHE_MISS(arg0)
-#define	MYSQL_QUERY_CACHE_MISS_ENABLED() (0)
+#define	MYSQL_QUERY_CACHE_MISS_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_QUERY_EXEC_START(arg0, arg1, arg2, arg3, arg4, arg5)
-#define	MYSQL_QUERY_EXEC_START_ENABLED() (0)
+#define	MYSQL_QUERY_EXEC_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_QUERY_EXEC_DONE(arg0)
-#define	MYSQL_QUERY_EXEC_DONE_ENABLED() (0)
+#define	MYSQL_QUERY_EXEC_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_INSERT_ROW_START(arg0, arg1)
-#define	MYSQL_INSERT_ROW_START_ENABLED() (0)
+#define	MYSQL_INSERT_ROW_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_INSERT_ROW_DONE(arg0)
-#define	MYSQL_INSERT_ROW_DONE_ENABLED() (0)
+#define	MYSQL_INSERT_ROW_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_UPDATE_ROW_START(arg0, arg1)
-#define	MYSQL_UPDATE_ROW_START_ENABLED() (0)
+#define	MYSQL_UPDATE_ROW_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_UPDATE_ROW_DONE(arg0)
-#define	MYSQL_UPDATE_ROW_DONE_ENABLED() (0)
+#define	MYSQL_UPDATE_ROW_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_DELETE_ROW_START(arg0, arg1)
-#define	MYSQL_DELETE_ROW_START_ENABLED() (0)
+#define	MYSQL_DELETE_ROW_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_DELETE_ROW_DONE(arg0)
-#define	MYSQL_DELETE_ROW_DONE_ENABLED() (0)
+#define	MYSQL_DELETE_ROW_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_READ_ROW_START(arg0, arg1, arg2)
-#define	MYSQL_READ_ROW_START_ENABLED() (0)
+#define	MYSQL_READ_ROW_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_READ_ROW_DONE(arg0)
-#define	MYSQL_READ_ROW_DONE_ENABLED() (0)
+#define	MYSQL_READ_ROW_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_INDEX_READ_ROW_START(arg0, arg1)
-#define	MYSQL_INDEX_READ_ROW_START_ENABLED() (0)
+#define	MYSQL_INDEX_READ_ROW_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_INDEX_READ_ROW_DONE(arg0)
-#define	MYSQL_INDEX_READ_ROW_DONE_ENABLED() (0)
+#define	MYSQL_INDEX_READ_ROW_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_HANDLER_RDLOCK_START(arg0, arg1)
-#define	MYSQL_HANDLER_RDLOCK_START_ENABLED() (0)
+#define	MYSQL_HANDLER_RDLOCK_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_HANDLER_WRLOCK_START(arg0, arg1)
-#define	MYSQL_HANDLER_WRLOCK_START_ENABLED() (0)
+#define	MYSQL_HANDLER_WRLOCK_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_HANDLER_UNLOCK_START(arg0, arg1)
-#define	MYSQL_HANDLER_UNLOCK_START_ENABLED() (0)
+#define	MYSQL_HANDLER_UNLOCK_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_HANDLER_RDLOCK_DONE(arg0)
-#define	MYSQL_HANDLER_RDLOCK_DONE_ENABLED() (0)
+#define	MYSQL_HANDLER_RDLOCK_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_HANDLER_WRLOCK_DONE(arg0)
-#define	MYSQL_HANDLER_WRLOCK_DONE_ENABLED() (0)
+#define	MYSQL_HANDLER_WRLOCK_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_HANDLER_UNLOCK_DONE(arg0)
-#define	MYSQL_HANDLER_UNLOCK_DONE_ENABLED() (0)
+#define	MYSQL_HANDLER_UNLOCK_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_FILESORT_START(arg0, arg1)
-#define	MYSQL_FILESORT_START_ENABLED() (0)
+#define	MYSQL_FILESORT_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_FILESORT_DONE(arg0, arg1)
-#define	MYSQL_FILESORT_DONE_ENABLED() (0)
+#define	MYSQL_FILESORT_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_SELECT_START(arg0)
-#define	MYSQL_SELECT_START_ENABLED() (0)
+#define	MYSQL_SELECT_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_SELECT_DONE(arg0, arg1)
-#define	MYSQL_SELECT_DONE_ENABLED() (0)
+#define	MYSQL_SELECT_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_INSERT_START(arg0)
-#define	MYSQL_INSERT_START_ENABLED() (0)
+#define	MYSQL_INSERT_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_INSERT_DONE(arg0, arg1)
-#define	MYSQL_INSERT_DONE_ENABLED() (0)
+#define	MYSQL_INSERT_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_INSERT_SELECT_START(arg0)
-#define	MYSQL_INSERT_SELECT_START_ENABLED() (0)
+#define	MYSQL_INSERT_SELECT_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_INSERT_SELECT_DONE(arg0, arg1)
-#define	MYSQL_INSERT_SELECT_DONE_ENABLED() (0)
+#define	MYSQL_INSERT_SELECT_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_UPDATE_START(arg0)
-#define	MYSQL_UPDATE_START_ENABLED() (0)
+#define	MYSQL_UPDATE_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_UPDATE_DONE(arg0, arg1, arg2)
-#define	MYSQL_UPDATE_DONE_ENABLED() (0)
+#define	MYSQL_UPDATE_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_MULTI_UPDATE_START(arg0)
-#define	MYSQL_MULTI_UPDATE_START_ENABLED() (0)
+#define	MYSQL_MULTI_UPDATE_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_MULTI_UPDATE_DONE(arg0, arg1, arg2)
-#define	MYSQL_MULTI_UPDATE_DONE_ENABLED() (0)
+#define	MYSQL_MULTI_UPDATE_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_DELETE_START(arg0)
-#define	MYSQL_DELETE_START_ENABLED() (0)
+#define	MYSQL_DELETE_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_DELETE_DONE(arg0, arg1)
-#define	MYSQL_DELETE_DONE_ENABLED() (0)
+#define	MYSQL_DELETE_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_MULTI_DELETE_START(arg0)
-#define	MYSQL_MULTI_DELETE_START_ENABLED() (0)
+#define	MYSQL_MULTI_DELETE_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_MULTI_DELETE_DONE(arg0, arg1)
-#define	MYSQL_MULTI_DELETE_DONE_ENABLED() (0)
+#define	MYSQL_MULTI_DELETE_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_NET_READ_START()
-#define	MYSQL_NET_READ_START_ENABLED() (0)
+#define	MYSQL_NET_READ_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_NET_READ_DONE(arg0, arg1)
-#define	MYSQL_NET_READ_DONE_ENABLED() (0)
+#define	MYSQL_NET_READ_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_NET_WRITE_START(arg0)
-#define	MYSQL_NET_WRITE_START_ENABLED() (0)
+#define	MYSQL_NET_WRITE_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_NET_WRITE_DONE(arg0)
-#define	MYSQL_NET_WRITE_DONE_ENABLED() (0)
+#define	MYSQL_NET_WRITE_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_KEYCACHE_READ_START(arg0, arg1, arg2, arg3)
-#define	MYSQL_KEYCACHE_READ_START_ENABLED() (0)
+#define	MYSQL_KEYCACHE_READ_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_KEYCACHE_READ_BLOCK(arg0)
-#define	MYSQL_KEYCACHE_READ_BLOCK_ENABLED() (0)
+#define	MYSQL_KEYCACHE_READ_BLOCK_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_KEYCACHE_READ_HIT()
-#define	MYSQL_KEYCACHE_READ_HIT_ENABLED() (0)
+#define	MYSQL_KEYCACHE_READ_HIT_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_KEYCACHE_READ_MISS()
-#define	MYSQL_KEYCACHE_READ_MISS_ENABLED() (0)
+#define	MYSQL_KEYCACHE_READ_MISS_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_KEYCACHE_READ_DONE(arg0, arg1)
-#define	MYSQL_KEYCACHE_READ_DONE_ENABLED() (0)
+#define	MYSQL_KEYCACHE_READ_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_KEYCACHE_WRITE_START(arg0, arg1, arg2, arg3)
-#define	MYSQL_KEYCACHE_WRITE_START_ENABLED() (0)
+#define	MYSQL_KEYCACHE_WRITE_START_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_KEYCACHE_WRITE_BLOCK(arg0)
-#define	MYSQL_KEYCACHE_WRITE_BLOCK_ENABLED() (0)
+#define	MYSQL_KEYCACHE_WRITE_BLOCK_ENABLED() (MYSQL_PROBES_FALSE)
 #define	MYSQL_KEYCACHE_WRITE_DONE(arg0, arg1)
-#define	MYSQL_KEYCACHE_WRITE_DONE_ENABLED() (0)
+#define	MYSQL_KEYCACHE_WRITE_DONE_ENABLED() (MYSQL_PROBES_FALSE)
 
 #ifdef  __cplusplus
 }
diff --git a/mysql-test/disabled.def b/mysql-test/disabled.def
index 37b4626e6db..3d33be79473 100644
--- a/mysql-test/disabled.def
+++ b/mysql-test/disabled.def
@@ -13,7 +13,6 @@ tablespace               : disabled in MariaDB (no TABLESPACE table attribute)
 events_time_zone         : Test is not predictable as it depends on precise timing.
 lowercase_table3         : Bug#11762269 2010-06-30 alik main.lowercase_table3 on Mac OSX
 read_many_rows_innodb    : Bug#11748886 2010-11-15 mattiasj report already exists
-sum_distinct-big         : Bug#11764126 2010-11-15 mattiasj was not tested
 archive-big              : Bug#11817185 2011-03-10 Anitha Disabled since this leads to timeout on Solaris Sparc
 log_tables-big           : Bug#11756699 2010-11-15 mattiasj report already exists
 mysql_embedded           : Bug#12561297 2011-05-14 Anitha Dependent on PB2 changes - eventum#41836
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 55afba245bd..c6fa040246a 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1837,10 +1837,10 @@ INSERT INTO t2 VALUES
 EXPLAIN EXTENDED
 SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	range	a	a	4	NULL	4	100.00	Using where; Using index
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<cache>(<in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10))
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where 0
 SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
 MAX(a)
 NULL
diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
index 650946d8b06..0184e236d14 100644
--- a/mysql-test/r/join_outer_innodb.result
+++ b/mysql-test/r/join_outer_innodb.result
@@ -183,6 +183,296 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (incremental, BNL join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t6b	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
 drop table t1,t2,t3,t4,t5,t6;
+#
+Bug mdev-4318: view over a complex query with outer joins
+#
+CREATE TABLE t1 (
+a1 int NOT NULL, a2 int NOT NULL, a3 int DEFAULT NULL, a4 tinyint NOT NULL,
+a5 int NOT NULL, a6 tinyint NOT NULL, a7 tinyint(4) DEFAULT NULL,
+a8 smallint(6) DEFAULT NULL, a9 smallint(6) DEFAULT NULL, a10 tinyint NOT NULL,
+PRIMARY KEY (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4), KEY a6 (a6),
+KEY a5 (a5), KEY a7 (a7), KEY a8 (a8), KEY a9 (a9)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t1 VALUES 
+(3360,5684,2219,1,316832,1,0,NULL,NULL,NULL),
+(3362,2754,597,2,316844,1,0,NULL,NULL,NULL),
+(3363,369,NULL,1,317295,1,0,NULL,NULL,NULL);
+Warnings:
+Warning	1048	Column 'a10' cannot be null
+Warning	1048	Column 'a10' cannot be null
+Warning	1048	Column 'a10' cannot be null
+CREATE TABLE t2 (
+b1 int NOT NULL, b2 int NOT NULL, PRIMARY KEY (b1,b2), KEY b2 (b2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE t3 (
+c1 int NOT NULL, PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t3 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
+(1000),(1001),(1002),(1003),(9999);
+CREATE TABLE t4 (
+d1 int NOT NULL, PRIMARY KEY (d1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t4 VALUES (5674),(5676),(5680),(5684),(5685);
+CREATE TABLE t5 (
+e1 int NOT NULL, e2 varchar(64) NOT NULL, PRIMARY KEY (e1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t5 VALUES
+(5684,'51a5de7a9f56314e082094d78f58be082c3cf0c1'),
+(5685,'754dc8292cb9f5eb9ade126fe7e961c62412a349'),
+(5686,'75eeb33f1c819bac21f6d023b4c5b24185eeda5c');
+CREATE TABLE t6 (
+f1 int NOT NULL, PRIMARY KEY (f1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t6 VALUES (5542),(5620),(5686);
+CREATE TABLE t7 (
+g1 tinyint NOT NULL DEFAULT '0', g2 varchar(20) NOT NULL, PRIMARY KEY (g1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t7 VALUES
+(1,'60feec2b20ed19f55ad0'),(3,'9ddb18bff7fcbd1e3133'),
+(5,'a05599df9222bb160d11'),(7,'e31bae372f7d01df0589'),
+(9,'8f8372dd7fc8eb46c8a3'),(11,'f8d0e28529e990a09309');
+CREATE TABLE t8 (
+h1 tinyint NOT NULL, h2 varchar(128) DEFAULT NULL, PRIMARY KEY (h1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t8 VALUES (1,'b'),(2,'c'),(3,'d'),(4,'e');
+CREATE TABLE t9 (
+i1 tinyint NOT NULL, i2 varchar(7) NOT NULL, i3 varchar(128) NOT NULL,
+PRIMARY KEY (i1,i2), KEY i2 (i2), KEY i3 (i3)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t9 VALUES
+(2,'a','07630d223c7e5f7b1feb19b3caafb0833fd028eb'),
+(3,'b','1ca53dcc50b68af86f4b1b4676dbed917b543c30'),
+(1,'b','2c01ac36c1ce9a7de66be89f85d8aa5f0052e2e8'),
+(4,'a','496c486b3a9edc439477fef7d34cbefdebba86df'),
+(3,'a','98bf72d8d467201058a5f69bd7709bfc74a8637e'),
+(2,'b','9a45425f6160fb59d7f8a02c721498d4ce945302'),
+(4,'b','9c9a7300f3e708f8e430f9f3376d966f5951f583'),
+(1,'a','c0af3f076b905f31cbb51af304b9c7ad539e0861');
+CREATE TABLE t10 (
+j1 tinyint NOT NULL, j2 varchar(20) NOT NULL, PRIMARY KEY (j1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t10 VALUES (1,'b'),(2,'c'),(3,'d');
+CREATE TABLE t11 (
+k1 int NOT NULL, k2 datetime DEFAULT NULL, k3 int DEFAULT NULL,
+k4 int DEFAULT NULL, PRIMARY KEY (k1), KEY k3 (k3), KEY k4 (k4)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t11 VALUES
+(317422,'2013-03-18 11:43:03',1,NULL),(317423,'2013-03-18 11:43:11',1,NULL),
+(317424,'2013-03-18 11:52:01',1,1),(317425,'2013-03-18 11:52:01',1,1),
+(317426,'2013-03-18 11:56:38',1,1),(317427,'2013-03-18 12:18:25',1,NULL),
+(317428,'2013-03-18 12:46:28',1,NULL),(317429,'2013-03-18 12:46:28',1,NULL),
+(317430,'2013-03-18 12:46:28',1,NULL),(317431,'2013-03-18 12:46:28',1,NULL),
+(317432,'2013-03-18 12:46:28',1,NULL),(317433,'2013-03-18 12:46:28',1,NULL),
+(317434,'2013-03-18 12:46:28',1,NULL),(317435,'2013-03-18 12:46:28',1,NULL),
+(317436,'2013-03-18 12:46:28',1,NULL),(317437,'2013-03-18 12:46:28',1,NULL),
+(317438,'2013-03-18 12:46:28',1,NULL),(317439,'2013-03-18 12:46:28',1,NULL),
+(317440,'2013-03-18 12:55:20',1,NULL),(317441,'2013-03-18 12:58:29',1,NULL),
+(317442,'2013-03-18 13:06:02',1,NULL),(317443,'2013-03-18 15:23:18',21,NULL);
+CREATE TABLE t12 (
+l1 int NOT NULL, l2 varchar(64) NOT NULL, PRIMARY KEY (l1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t12 VALUES
+(552,'59a498252ef59f96fbdc13a414abe244d8e8bc30'),
+(554,'c6025c7cb2d9dfb1be7ce4a61f35b45bb9e61ba3'),
+(555,'b245bcc672082bb6d10794b2b4ac972dd14b1cf5');
+CREATE TABLE t13 (
+m1 int NOT NULL, m2 int NOT NULL, m3 int NOT NULL,
+PRIMARY KEY (m1,m2,m3), KEY m3 (m3), KEY m2 (m2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t13 VALUES (3324,43,4),(3332,263,1),(3348,27,3);
+CREATE TABLE t14 (
+n1 smallint NOT NULL, n2 varchar(64) NOT NULL, PRIMARY KEY (n1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t14 VALUES
+(21,'685bf7ca576af964c7cff564d5e4473b81499b8b'),
+(23,'b8e42dab1ab952406b3accfb47089c61478138a8'),
+(25,'3fea441e411db8c70bf039b50c8f18f59515be53'),
+(27,'998aecc30fd0e0b8a1cac6590e5eccc2d7822223');
+CREATE TABLE t15 (
+o1 smallint NOT NULL, PRIMARY KEY (o1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t15 VALUES (1),(3);
+CREATE TABLE t16 (
+p1 smallint NOT NULL, p2 varchar(7) NOT NULL, p3 varchar(64) NOT NULL,
+PRIMARY KEY (p1,p2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t16 VALUES
+(1,'a','66bdbb389456f3ae97206da115a7b397c31400e8'),
+(1,'b','66bdbb389456f3ae97206da115a7b397c31400e8'),
+(3,'a','386c10e454278c6e27feb16258089166422f79b4'),
+(3,'b','386c10e454278c6e27feb16258089166422f79b4');
+create view v1 as select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2,
+t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1
+from
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+t1
+left join t2 on t1.a1 = t2.b1
+) 
+left join t3 on t2.b2 = t3.c1
+)
+left join t4 on t1.a2 = t4.d1
+)
+left join t5 on t4.d1 = t5.e1
+) 
+left join t6 on t1.a3 = t6.f1
+) 
+left join t5 e2 on t6.f1 = e2.e1
+) 
+join t7 on t1.a7 = t7.g1
+) 
+join t8 on t1.a4 = t8.h1
+) 
+join t9 on t8.h1 = t9.i1
+) 
+join t10 on t1.a6 = t10.j1
+)
+join t11 on t1.a5 = t11.k1
+) 
+left join t12 on t11.k3 = t12.l1
+) 
+left join t12 l2 on t11.k4 = l2.l1
+) 
+left join t13 on t1.a1 = t13.m1 and t13.m3 = 4
+)
+left join t12 l4 on l4.l1 = t13.m2
+) 
+left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3
+)
+left join t12 l3 on l3.l1 = m2.m2
+)
+left join t14 on t1.a8 = t14.n1
+)
+left join t15 on t1.a9 = t15.o1
+)
+left join t16 on t15.o1 = t16.p1
+where t1.a10 = 1;
+explain select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2,
+t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1
+from
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+t1
+left join t2 on t1.a1 = t2.b1
+) 
+left join t3 on t2.b2 = t3.c1
+)
+left join t4 on t1.a2 = t4.d1
+)
+left join t5 on t4.d1 = t5.e1
+) 
+left join t6 on t1.a3 = t6.f1
+) 
+left join t5 e2 on t6.f1 = e2.e1
+) 
+join t7 on t1.a7 = t7.g1
+) 
+join t8 on t1.a4 = t8.h1
+) 
+join t9 on t8.h1 = t9.i1
+) 
+join t10 on t1.a6 = t10.j1
+)
+join t11 on t1.a5 = t11.k1
+) 
+left join t12 on t11.k3 = t12.l1
+) 
+left join t12 l2 on t11.k4 = l2.l1
+) 
+left join t13 on t1.a1 = t13.m1 and t13.m3 = 4
+)
+left join t12 l4 on l4.l1 = t13.m2
+) 
+left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3
+)
+left join t12 l3 on l3.l1 = m2.m2
+)
+left join t14 on t1.a8 = t14.n1
+)
+left join t15 on t1.a9 = t15.o1
+)
+left join t16 on t15.o1 = t16.p1
+where t1.a10 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a4,a6,a5,a7	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.b2	1	Using where; Using index
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.a2	1	Using index
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.d1	1	Using where
+1	SIMPLE	t6	eq_ref	PRIMARY	PRIMARY	4	test.t1.a3	1	Using where; Using index
+1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	1	test.t1.a4	1	Using index
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	1	test.t1.a7	1	
+1	SIMPLE	t9	ref	PRIMARY	PRIMARY	1	test.t1.a4	1	
+1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t1.a5	1	
+1	SIMPLE	t12	eq_ref	PRIMARY	PRIMARY	4	test.t11.k3	1	Using where
+1	SIMPLE	l2	eq_ref	PRIMARY	PRIMARY	4	test.t11.k4	1	Using where
+1	SIMPLE	t13	ref	PRIMARY,m3	PRIMARY	4	test.t1.a1	1	Using where; Using index
+1	SIMPLE	l4	eq_ref	PRIMARY	PRIMARY	4	test.t13.m2	1	Using where; Using index
+1	SIMPLE	m2	ref	PRIMARY,m3	PRIMARY	4	test.t1.a1	1	Using where; Using index
+1	SIMPLE	l3	eq_ref	PRIMARY	PRIMARY	4	test.m2.m2	1	Using where
+1	SIMPLE	t14	eq_ref	PRIMARY	PRIMARY	2	test.t1.a8	1	Using where
+1	SIMPLE	t15	eq_ref	PRIMARY	PRIMARY	2	test.t1.a9	1	Using where; Using index
+1	SIMPLE	t16	ref	PRIMARY	PRIMARY	2	test.t15.o1	1	Using where
+1	SIMPLE	t10	ALL	PRIMARY	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+explain select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a4,a6,a5,a7	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.b2	1	Using where; Using index
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.a2	1	Using index
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t4.d1	1	Using where
+1	SIMPLE	t6	eq_ref	PRIMARY	PRIMARY	4	test.t1.a3	1	Using where; Using index
+1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	1	test.t1.a4	1	Using index
+1	SIMPLE	t7	eq_ref	PRIMARY	PRIMARY	1	test.t1.a7	1	
+1	SIMPLE	t9	ref	PRIMARY	PRIMARY	1	test.t1.a4	1	
+1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t1.a5	1	
+1	SIMPLE	t12	eq_ref	PRIMARY	PRIMARY	4	test.t11.k3	1	Using where
+1	SIMPLE	l2	eq_ref	PRIMARY	PRIMARY	4	test.t11.k4	1	Using where
+1	SIMPLE	t13	ref	PRIMARY,m3	PRIMARY	4	test.t1.a1	1	Using where; Using index
+1	SIMPLE	l4	eq_ref	PRIMARY	PRIMARY	4	test.t13.m2	1	Using where; Using index
+1	SIMPLE	m2	ref	PRIMARY,m3	PRIMARY	4	test.t1.a1	1	Using where; Using index
+1	SIMPLE	l3	eq_ref	PRIMARY	PRIMARY	4	test.m2.m2	1	Using where
+1	SIMPLE	t14	eq_ref	PRIMARY	PRIMARY	2	test.t1.a8	1	Using where
+1	SIMPLE	t15	eq_ref	PRIMARY	PRIMARY	2	test.t1.a9	1	Using where; Using index
+1	SIMPLE	t16	ref	PRIMARY	PRIMARY	2	test.t15.o1	1	Using where
+1	SIMPLE	t10	ALL	PRIMARY	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+drop view v1;
+drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16;
diff --git a/mysql-test/r/ps_11bugs.result b/mysql-test/r/ps_11bugs.result
index 56894302505..3bc7039583b 100644
--- a/mysql-test/r/ps_11bugs.result
+++ b/mysql-test/r/ps_11bugs.result
@@ -120,7 +120,7 @@ create table t1 (a int primary key);
 insert into t1 values (1);
 explain select * from t1 where 3 in (select (1+1) union select 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 96a20a3ea27..b3536661125 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -429,7 +429,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -558,10 +558,10 @@ Warnings:
 Note	1003	select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using where; Using index
+1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -1371,7 +1371,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 0d5209d0ec2..ff768886434 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -869,7 +869,7 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 f1	f2
@@ -960,7 +960,7 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 f1	f2
@@ -1055,7 +1055,7 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -1146,7 +1146,7 @@ NULL
 EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
@@ -2012,7 +2012,7 @@ FROM t2 JOIN t3 ON t3.f4 = t2.f4
 WHERE t3.f1 = 8
 GROUP BY 1, 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	Using filesort
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 3	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
@@ -2325,5 +2325,19 @@ ORDER BY alias1.b;
 pk	b	pk	b
 1	1	1	1
 drop table t1, t2, t3;
+#
+# MDEV-4144 simple subquery causes full scan instead of range scan
+#
+CREATE TABLE t1 (id int not null auto_increment, x int not null, primary key(id));
+INSERT INTO t1 (x) VALUES (0),(0),(0);
+EXPLAIN
+SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where; Using filesort
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1;
+x
+0
+drop table t1;
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
diff --git a/mysql-test/r/subselect_exists_to_in.result b/mysql-test/r/subselect_exists_to_in.result
index b639fc7eca0..579277f11f9 100644
--- a/mysql-test/r/subselect_exists_to_in.result
+++ b/mysql-test/r/subselect_exists_to_in.result
@@ -433,7 +433,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -562,10 +562,10 @@ Warnings:
 Note	1003	select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using where; Using index
+1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -1375,7 +1375,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index 8932acf8ffd..0b339738432 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -372,7 +372,7 @@ SELECT SUM( c )
 FROM t2
 WHERE (SELECT DISTINCT b FROM t3) > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	Using where; Using index
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	Using temporary
 SELECT *
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 8a5016d47ee..ce9a4ad444f 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -436,7 +436,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -565,10 +565,10 @@ Warnings:
 Note	1003	select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using where; Using index
+1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -1378,7 +1378,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
@@ -6913,7 +6913,7 @@ INSERT INTO t2 VALUES (1),(2);
 EXPLAIN
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
 SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2 WHERE EXISTS (SELECT * FROM t1));
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 13467916039..e017e67cfe6 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -432,7 +432,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -561,10 +561,10 @@ Warnings:
 Note	1003	select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using where; Using index
+1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -1374,7 +1374,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index b53405ad8bd..492321c5d81 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -435,7 +435,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -564,10 +564,10 @@ Warnings:
 Note	1003	select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using where; Using index
+1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -1377,7 +1377,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 34cdb17e23e..eb92936e85d 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -432,7 +432,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where (1 = (select 1 union select 1))
+Note	1003	select 1 AS `1` from `test`.`t1` where 1
 drop table t1;
 CREATE TABLE `t1` (
 `numeropost` mediumint(8) unsigned NOT NULL auto_increment,
@@ -561,10 +561,10 @@ Warnings:
 Note	1003	select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = '1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using where; Using index
+1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	100.00	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
-Note	1003	select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = '1') and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where (`test`.`t1`.`numeropost` = '1'))))
+Note	1003	select 3 AS `numreponse` from `test`.`t1` where ((1 = '1'))
 drop table t1;
 CREATE TABLE t1 (a int(1));
 INSERT INTO t1 VALUES (1);
@@ -1374,7 +1374,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal
 insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000);
 explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ref	salary	salary	5	const	0	0.00	Using where
+1	PRIMARY	t1	ref	salary	salary	5	const	1	100.00	Using where
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`))
diff --git a/mysql-test/r/sum_distinct-big.result b/mysql-test/r/sum_distinct-big.result
index d4933b31f80..2d350826ac8 100644
--- a/mysql-test/r/sum_distinct-big.result
+++ b/mysql-test/r/sum_distinct-big.result
@@ -1,4 +1,8 @@
 DROP TABLE IF EXISTS t1, t2;
+set @save_tmp_table_size=@@tmp_table_size;
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @save_storage_engine=@@storage_engine;
+set storage_engine=MYISAM;
 CREATE TABLE t1 (id INTEGER);
 CREATE TABLE t2 (id INTEGER);
 INSERT INTO t1 (id) VALUES (1), (1), (1),(1);
@@ -120,3 +124,57 @@ sm
 536887296
 DROP TABLE t1;
 DROP TABLE t2;
+SET @@tmp_table_size=@save_tmp_table_size;
+SET @@max_heap_table_size=@save_max_heap_table_size;
+# 
+# Bug mdev-4311: COUNT(DISTINCT...) requiring a file for Unique 
+# (bug #68749)
+# 
+set @save_storage_engine=@@storage_engine;
+set storage_engine=INNODB;
+CREATE TABLE t1 (id INTEGER) ENGINE=InnoDB;
+CREATE TABLE t2 (id INTEGER) ENGINE=InnoDB;
+INSERT INTO t1 (id) VALUES (1), (1), (1),(1);
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 SELECT id+1 FROM t1;
+INSERT INTO t1 SELECT id+2 FROM t1;
+INSERT INTO t1 SELECT id+4 FROM t1;
+INSERT INTO t1 SELECT id+8 FROM t1;
+INSERT INTO t1 SELECT id+16 FROM t1;
+INSERT INTO t1 SELECT id+32 FROM t1;
+INSERT INTO t1 SELECT id+64 FROM t1;
+INSERT INTO t1 SELECT id+128 FROM t1;
+INSERT INTO t1 SELECT id+256 FROM t1;
+INSERT INTO t1 SELECT id+512 FROM t1;
+INSERT INTO t1 SELECT id+1024 FROM t1;
+INSERT INTO t1 SELECT id+2048 FROM t1;
+INSERT INTO t1 SELECT id+4096 FROM t1;
+INSERT INTO t1 SELECT id+8192 FROM t1;
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+INSERT INTO t2 VALUE(NULL);
+# With default tmp_table_size / max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+sm
+16384
+set @@tmp_table_size=1024*256;
+# With reduced tmp_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+sm
+16384
+set @@tmp_table_size=@save_tmp_table_size;
+SET @@max_heap_table_size=1024*256;
+# With reduced max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+sm
+16384
+SET @@max_heap_table_size=@save_max_heap_table_size;
+# Back to default tmp_table_size / max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+sm
+16384
+DROP TABLE t1,t2;
+set storage_engine=@save_storage_engine;
diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test
index cca1dde481d..adb555d5f85 100644
--- a/mysql-test/t/join_outer_innodb.test
+++ b/mysql-test/t/join_outer_innodb.test
@@ -143,3 +143,210 @@ eval SELECT $rest_of_query;
 eval EXPLAIN SELECT $rest_of_query;
 
 drop table t1,t2,t3,t4,t5,t6;
+
+--echo #
+--echo Bug mdev-4318: view over a complex query with outer joins
+--echo #
+
+CREATE TABLE t1 (
+  a1 int NOT NULL, a2 int NOT NULL, a3 int DEFAULT NULL, a4 tinyint NOT NULL,
+  a5 int NOT NULL, a6 tinyint NOT NULL, a7 tinyint(4) DEFAULT NULL,
+  a8 smallint(6) DEFAULT NULL, a9 smallint(6) DEFAULT NULL, a10 tinyint NOT NULL,
+  PRIMARY KEY (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4), KEY a6 (a6),
+  KEY a5 (a5), KEY a7 (a7), KEY a8 (a8), KEY a9 (a9)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t1 VALUES 
+  (3360,5684,2219,1,316832,1,0,NULL,NULL,NULL),
+  (3362,2754,597,2,316844,1,0,NULL,NULL,NULL),
+  (3363,369,NULL,1,317295,1,0,NULL,NULL,NULL);
+
+CREATE TABLE t2 (
+  b1 int NOT NULL, b2 int NOT NULL, PRIMARY KEY (b1,b2), KEY b2 (b2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE t3 (
+  c1 int NOT NULL, PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t3 VALUES
+  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
+ (1000),(1001),(1002),(1003),(9999);
+
+CREATE TABLE t4 (
+  d1 int NOT NULL, PRIMARY KEY (d1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t4 VALUES (5674),(5676),(5680),(5684),(5685);
+
+CREATE TABLE t5 (
+  e1 int NOT NULL, e2 varchar(64) NOT NULL, PRIMARY KEY (e1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t5 VALUES
+  (5684,'51a5de7a9f56314e082094d78f58be082c3cf0c1'),
+  (5685,'754dc8292cb9f5eb9ade126fe7e961c62412a349'),
+  (5686,'75eeb33f1c819bac21f6d023b4c5b24185eeda5c');
+
+CREATE TABLE t6 (
+  f1 int NOT NULL, PRIMARY KEY (f1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t6 VALUES (5542),(5620),(5686);
+
+CREATE TABLE t7 (
+  g1 tinyint NOT NULL DEFAULT '0', g2 varchar(20) NOT NULL, PRIMARY KEY (g1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t7 VALUES
+  (1,'60feec2b20ed19f55ad0'),(3,'9ddb18bff7fcbd1e3133'),
+  (5,'a05599df9222bb160d11'),(7,'e31bae372f7d01df0589'),
+  (9,'8f8372dd7fc8eb46c8a3'),(11,'f8d0e28529e990a09309');
+
+CREATE TABLE t8 (
+  h1 tinyint NOT NULL, h2 varchar(128) DEFAULT NULL, PRIMARY KEY (h1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t8 VALUES (1,'b'),(2,'c'),(3,'d'),(4,'e');
+
+CREATE TABLE t9 (
+  i1 tinyint NOT NULL, i2 varchar(7) NOT NULL, i3 varchar(128) NOT NULL,
+  PRIMARY KEY (i1,i2), KEY i2 (i2), KEY i3 (i3)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t9 VALUES
+  (2,'a','07630d223c7e5f7b1feb19b3caafb0833fd028eb'),
+  (3,'b','1ca53dcc50b68af86f4b1b4676dbed917b543c30'),
+  (1,'b','2c01ac36c1ce9a7de66be89f85d8aa5f0052e2e8'),
+  (4,'a','496c486b3a9edc439477fef7d34cbefdebba86df'),
+  (3,'a','98bf72d8d467201058a5f69bd7709bfc74a8637e'),
+  (2,'b','9a45425f6160fb59d7f8a02c721498d4ce945302'),
+  (4,'b','9c9a7300f3e708f8e430f9f3376d966f5951f583'),
+  (1,'a','c0af3f076b905f31cbb51af304b9c7ad539e0861');
+
+CREATE TABLE t10 (
+  j1 tinyint NOT NULL, j2 varchar(20) NOT NULL, PRIMARY KEY (j1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t10 VALUES (1,'b'),(2,'c'),(3,'d');
+
+CREATE TABLE t11 (
+  k1 int NOT NULL, k2 datetime DEFAULT NULL, k3 int DEFAULT NULL,
+  k4 int DEFAULT NULL, PRIMARY KEY (k1), KEY k3 (k3), KEY k4 (k4)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t11 VALUES
+  (317422,'2013-03-18 11:43:03',1,NULL),(317423,'2013-03-18 11:43:11',1,NULL),
+  (317424,'2013-03-18 11:52:01',1,1),(317425,'2013-03-18 11:52:01',1,1),
+  (317426,'2013-03-18 11:56:38',1,1),(317427,'2013-03-18 12:18:25',1,NULL),
+  (317428,'2013-03-18 12:46:28',1,NULL),(317429,'2013-03-18 12:46:28',1,NULL),
+  (317430,'2013-03-18 12:46:28',1,NULL),(317431,'2013-03-18 12:46:28',1,NULL),
+  (317432,'2013-03-18 12:46:28',1,NULL),(317433,'2013-03-18 12:46:28',1,NULL),
+  (317434,'2013-03-18 12:46:28',1,NULL),(317435,'2013-03-18 12:46:28',1,NULL),
+  (317436,'2013-03-18 12:46:28',1,NULL),(317437,'2013-03-18 12:46:28',1,NULL),
+  (317438,'2013-03-18 12:46:28',1,NULL),(317439,'2013-03-18 12:46:28',1,NULL),
+  (317440,'2013-03-18 12:55:20',1,NULL),(317441,'2013-03-18 12:58:29',1,NULL),
+  (317442,'2013-03-18 13:06:02',1,NULL),(317443,'2013-03-18 15:23:18',21,NULL);
+
+CREATE TABLE t12 (
+  l1 int NOT NULL, l2 varchar(64) NOT NULL, PRIMARY KEY (l1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t12 VALUES
+  (552,'59a498252ef59f96fbdc13a414abe244d8e8bc30'),
+  (554,'c6025c7cb2d9dfb1be7ce4a61f35b45bb9e61ba3'),
+  (555,'b245bcc672082bb6d10794b2b4ac972dd14b1cf5');
+
+CREATE TABLE t13 (
+  m1 int NOT NULL, m2 int NOT NULL, m3 int NOT NULL,
+  PRIMARY KEY (m1,m2,m3), KEY m3 (m3), KEY m2 (m2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t13 VALUES (3324,43,4),(3332,263,1),(3348,27,3);
+
+CREATE TABLE t14 (
+  n1 smallint NOT NULL, n2 varchar(64) NOT NULL, PRIMARY KEY (n1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t14 VALUES
+  (21,'685bf7ca576af964c7cff564d5e4473b81499b8b'),
+  (23,'b8e42dab1ab952406b3accfb47089c61478138a8'),
+  (25,'3fea441e411db8c70bf039b50c8f18f59515be53'),
+  (27,'998aecc30fd0e0b8a1cac6590e5eccc2d7822223');
+
+CREATE TABLE t15 (
+  o1 smallint NOT NULL, PRIMARY KEY (o1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t15 VALUES (1),(3);
+
+CREATE TABLE t16 (
+  p1 smallint NOT NULL, p2 varchar(7) NOT NULL, p3 varchar(64) NOT NULL,
+  PRIMARY KEY (p1,p2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t16 VALUES
+  (1,'a','66bdbb389456f3ae97206da115a7b397c31400e8'),
+  (1,'b','66bdbb389456f3ae97206da115a7b397c31400e8'),
+  (3,'a','386c10e454278c6e27feb16258089166422f79b4'),
+  (3,'b','386c10e454278c6e27feb16258089166422f79b4');
+
+let $Q=
+select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2,
+       t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1
+from
+(
+  (
+    (
+      (
+        (
+          (
+            (
+              (
+                (
+                  (
+                    (
+                      (
+                        (
+                          (
+                            (
+                              (
+                                (
+                                  (
+                                    (
+                                      t1
+                                      left join t2 on t1.a1 = t2.b1
+                                    ) 
+                                    left join t3 on t2.b2 = t3.c1
+                                  )
+                                  left join t4 on t1.a2 = t4.d1
+                                )
+                                left join t5 on t4.d1 = t5.e1
+                             ) 
+                             left join t6 on t1.a3 = t6.f1
+                           ) 
+                           left join t5 e2 on t6.f1 = e2.e1
+                         ) 
+                         join t7 on t1.a7 = t7.g1
+                       ) 
+                       join t8 on t1.a4 = t8.h1
+                     ) 
+                     join t9 on t8.h1 = t9.i1
+                   ) 
+                   join t10 on t1.a6 = t10.j1
+                 )
+                 join t11 on t1.a5 = t11.k1
+               ) 
+               left join t12 on t11.k3 = t12.l1
+             ) 
+             left join t12 l2 on t11.k4 = l2.l1
+           ) 
+           left join t13 on t1.a1 = t13.m1 and t13.m3 = 4
+         )
+         left join t12 l4 on l4.l1 = t13.m2
+       ) 
+       left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3
+     )
+     left join t12 l3 on l3.l1 = m2.m2
+   )
+   left join t14 on t1.a8 = t14.n1
+ )
+ left join t15 on t1.a9 = t15.o1
+)
+left join t16 on t15.o1 = t16.p1
+where t1.a10 = 1;
+
+eval create view v1 as $Q;
+
+eval explain $Q;
+
+explain select * from v1;
+
+drop view v1;
+
+drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16;
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 37f660d6682..e427253f65f 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1865,5 +1865,18 @@ ORDER BY alias1.b;
 
 drop table t1, t2, t3;
 
+--echo #
+--echo # MDEV-4144 simple subquery causes full scan instead of range scan
+--echo #
+
+CREATE TABLE t1 (id int not null auto_increment, x int not null, primary key(id));
+INSERT INTO t1 (x) VALUES (0),(0),(0);
+
+EXPLAIN
+SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1;
+SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1;
+
+drop table t1;
+
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
diff --git a/mysql-test/t/sum_distinct-big.test b/mysql-test/t/sum_distinct-big.test
index d3710056c9a..fee406ee46d 100644
--- a/mysql-test/t/sum_distinct-big.test
+++ b/mysql-test/t/sum_distinct-big.test
@@ -3,15 +3,23 @@
 #
 
 --source include/big_test.inc
+--source include/have_innodb.inc
 --disable_warnings
 DROP TABLE IF EXISTS t1, t2;
 --enable_warnings
 
+set @save_tmp_table_size=@@tmp_table_size;
+set @save_max_heap_table_size=@@max_heap_table_size;
+
+set @save_storage_engine=@@storage_engine;
+
 #
 # Test the case when distinct values doesn't fit in memory and 
 # filesort is used (see uniques.cc:merge_walk)
 #
 
+set storage_engine=MYISAM;
+
 CREATE TABLE t1 (id INTEGER);
 CREATE TABLE t2 (id INTEGER);
 
@@ -82,3 +90,64 @@ SELECT SUM(DISTINCT id) sm FROM t2;
 
 DROP TABLE t1;
 DROP TABLE t2;
+
+SET @@tmp_table_size=@save_tmp_table_size;
+SET @@max_heap_table_size=@save_max_heap_table_size;
+
+--echo # 
+--echo # Bug mdev-4311: COUNT(DISTINCT...) requiring a file for Unique 
+--echo # (bug #68749)
+--echo # 
+
+set @save_storage_engine=@@storage_engine;
+set storage_engine=INNODB;
+
+CREATE TABLE t1 (id INTEGER) ENGINE=InnoDB;
+CREATE TABLE t2 (id INTEGER) ENGINE=InnoDB;
+
+INSERT INTO t1 (id) VALUES (1), (1), (1),(1);
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 SELECT id+1 FROM t1;
+INSERT INTO t1 SELECT id+2 FROM t1;
+INSERT INTO t1 SELECT id+4 FROM t1;
+INSERT INTO t1 SELECT id+8 FROM t1;
+INSERT INTO t1 SELECT id+16 FROM t1;
+INSERT INTO t1 SELECT id+32 FROM t1;
+INSERT INTO t1 SELECT id+64 FROM t1;
+INSERT INTO t1 SELECT id+128 FROM t1;
+INSERT INTO t1 SELECT id+256 FROM t1;
+INSERT INTO t1 SELECT id+512 FROM t1;
+INSERT INTO t1 SELECT id+1024 FROM t1;
+INSERT INTO t1 SELECT id+2048 FROM t1;
+INSERT INTO t1 SELECT id+4096 FROM t1;
+INSERT INTO t1 SELECT id+8192 FROM t1;
+
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+INSERT INTO t2 VALUE(NULL);
+
+--echo # With default tmp_table_size / max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+
+set @@tmp_table_size=1024*256;
+
+--echo # With reduced tmp_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+
+set @@tmp_table_size=@save_tmp_table_size;
+SET @@max_heap_table_size=1024*256;
+
+--echo # With reduced max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+
+SET @@max_heap_table_size=@save_max_heap_table_size;
+
+--echo # Back to default tmp_table_size / max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+
+DROP TABLE t1,t2;
+
+set storage_engine=@save_storage_engine;
diff --git a/sql/item.cc b/sql/item.cc
index 1d8e466b2fd..a425b0cd4ce 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -8109,7 +8109,7 @@ bool Item_default_value::fix_fields(THD *thd, Item **items)
   }
   if (!(def_field= (Field*) sql_alloc(field_arg->field->size_of())))
     goto error;
-  memcpy(def_field, field_arg->field, field_arg->field->size_of());
+  memcpy((void *)def_field, (void *)field_arg->field, field_arg->field->size_of());
   def_field->move_field_offset((my_ptrdiff_t)
                                (def_field->table->s->default_values -
                                 def_field->table->record[0]));
@@ -8245,7 +8245,7 @@ bool Item_insert_value::fix_fields(THD *thd, Item **items)
     Field *def_field= (Field*) sql_alloc(field_arg->field->size_of());
     if (!def_field)
       return TRUE;
-    memcpy(def_field, field_arg->field, field_arg->field->size_of());
+    memcpy((void *)def_field, (void *)field_arg->field, field_arg->field->size_of());
     def_field->move_field_offset((my_ptrdiff_t)
                                  (def_field->table->insert_values -
                                   def_field->table->record[0]));
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index f2ef955af05..0544dceb9a0 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -559,8 +559,19 @@ bool Item_subselect::is_expensive()
     if (!cur_join)
       continue;
 
-    /* If a subquery is not optimized we cannot estimate its cost. */
-    if (!cur_join->join_tab)
+    /*
+      Subqueries whose result is known after optimization are not expensive.
+      Such subqueries have all tables optimized away, thus have no join plan.
+    */
+    if (cur_join->optimized &&
+        (cur_join->zero_result_cause || !cur_join->tables_list))
+      return false;
+
+    /*
+      If a subquery is not optimized we cannot estimate its cost. A subquery is
+      considered optimized if it has a join plan.
+    */
+    if (!(cur_join->optimized && cur_join->join_tab))
       return true;
 
     if (sl->first_inner_unit())
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 9410a12f21b..03afd1a4365 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -735,7 +735,15 @@ int simple_raw_key_cmp(void* arg, const void* key1, const void* key2)
 }
 
 
-int item_sum_distinct_walk(void *element, element_count num_of_dups,
+static int item_sum_distinct_walk_for_count(void *element, 
+                                            element_count num_of_dups,
+                                            void *item)
+{
+  return ((Aggregator_distinct*) (item))->unique_walk_function_for_count(element);
+}
+ 
+
+static int item_sum_distinct_walk(void *element, element_count num_of_dups,
                                   void *item)
 {
   return ((Aggregator_distinct*) (item))->unique_walk_function(element);
@@ -1105,7 +1113,12 @@ void Aggregator_distinct::endup()
   {
     /* go over the tree of distinct keys and calculate the aggregate value */
     use_distinct_values= TRUE;
-    tree->walk(table, item_sum_distinct_walk, (void*) this);
+    tree_walk_action func;
+    if (item_sum->sum_func() == Item_sum::COUNT_DISTINCT_FUNC)
+      func= item_sum_distinct_walk_for_count;
+    else
+      func= item_sum_distinct_walk;
+    tree->walk(table, func, (void*) this);
     use_distinct_values= FALSE;
   }
   /* prevent consecutive recalculations */
@@ -1482,6 +1495,22 @@ bool Aggregator_distinct::unique_walk_function(void *element)
 }
 
 
+/*
+  A variant of unique_walk_function() that is to be used with Item_sum_count.
+
+  COUNT is a special aggregate function: it doesn't need the values, it only
+  needs to count them. COUNT needs to know the values are not NULLs, but NULL
+  values are not put into the Unique, so we don't need to check for NULLs here.
+*/
+
+bool Aggregator_distinct::unique_walk_function_for_count(void *element)
+{
+  Item_sum_count *sum= (Item_sum_count *)item_sum;
+  sum->count++;
+  return 0;
+}
+
+
 Aggregator_distinct::~Aggregator_distinct()
 {
   if (tree)
diff --git a/sql/item_sum.h b/sql/item_sum.h
index 40a28d8beae..a954b0f65c1 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -642,6 +642,7 @@ public:
   virtual bool arg_is_null();
 
   bool unique_walk_function(void *element);
+  bool unique_walk_function_for_count(void *element);
   static int composite_key_cmp(void* arg, uchar* key1, uchar* key2);
 };
 
diff --git a/sql/sql_const.h b/sql/sql_const.h
index c6aa52197d5..7dadbb7b8b4 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -93,7 +93,7 @@
 #define FIELD_NR_MASK	16383			/* To get fieldnumber */
 #define FERR		-1			/* Error from my_functions */
 #define CREATE_MODE	0			/* Default mode on new files */
-#define NAMES_SEP_CHAR	'\377'			/* Char to sep. names */
+#define NAMES_SEP_CHAR	255			/* Char to sep. names */
 
 #define READ_RECORD_BUFFER	(uint) (IO_SIZE*8) /* Pointer_buffer_size */
 #define DISK_BUFFER_SIZE	(uint) (IO_SIZE*16) /* Size of diskbuffer */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0abac42eb73..ad4324fa7d1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13037,9 +13037,6 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top,
         table->prep_on_expr= table->on_expr= 0;
       }
     }
-    
-    if (!top)
-      continue;
 
     /* 
       Only inner tables of non-convertible outer joins