Merge 5.3->5.5.

25 files changed, 636 insertions, 143 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 4756daaf687..28f4e11b8a2 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1468,14 +1468,14 @@ WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a  FROM t3 t);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
-2	DEPENDENT SUBQUERY	t	ref	PRIMARY,c	c	4	func	2	Using where; Using index
+2	DEPENDENT SUBQUERY	t	eq_ref	PRIMARY,c	PRIMARY	4	func	1	Using where
 EXPLAIN 
 SELECT * FROM t1 , t2
 WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a  FROM (SELECT * FROM t3) t);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
-2	DEPENDENT SUBQUERY	t3	ref	PRIMARY,c	c	4	func	2	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,c	PRIMARY	4	func	1	Using where
 SELECT * FROM t1 , t2
 WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a  FROM (SELECT * FROM t3) t);
 b	a
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index aa93424f2ae..6ac8a8bbd5f 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1845,7 +1845,7 @@ NULL
 EXPLAIN EXTENDED
 SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
 1	PRIMARY	t1	range	a	a	4	NULL	4	100.00	Using where; Using index; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 Warnings:
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 5c46e65411d..c5c5f65df29 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2407,7 +2407,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	8	Using where
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	8	
 1	PRIMARY	t1_outer	ref	a	a	5	<subquery2>.max(b)	2	Using index
 2	MATERIALIZED	t1	range	NULL	a	5	NULL	8	Using index for group-by
 EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index e32cabf261f..5961a84cddc 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -573,6 +573,17 @@ f1
 DROP TABLE t1,t2;
 End of 5.1 tests
 #
+# LP bug:938518 HAVING does not reject the result of aggregation
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, a INT);
+INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
+SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1;
+MIN(t.pk)
+NULL
+SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1 HAVING MIN(t.pk) < 10;
+MIN(t.pk)
+drop table t1;
+#
 # LP bug #791761: MAX over an empty join + HAVING
 #
 CREATE TABLE t1 (a int, b int , KEY (b)) ;
@@ -596,3 +607,33 @@ SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0
 WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ;
 f
 DROP TABLE t1,t2,t3;
+#
+# LP bug:806955 HAVING not observed with aggregate +subquery
+#
+CREATE TABLE t1 (f3 int, f10 varchar(1), f11 int, KEY (f10) );
+INSERT INTO t1 VALUES (NULL,'a',0),(8,'b',0);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (7);
+CREATE TABLE t3 (f3 int);
+INSERT INTO t3 VALUES (0),(8);
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=off,materialization=off';
+SELECT MIN( t1.f10 ) AS field1
+FROM t1 , t2
+WHERE t2.f2 IN ( SELECT f3 FROM t3 )
+HAVING field1 < 's';
+field1
+explain extended 
+SELECT MIN( t1.f10 ) AS field1
+FROM t1 , t2
+WHERE t2.f2 IN ( SELECT f3 FROM t3 )
+HAVING field1 < 's';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t1	index	NULL	f10	4	NULL	2	100.00	Using index
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` where <expr_cache><7>(<in_optimizer>(7,<exists>(select `test`.`t3`.`f3` from `test`.`t3` where (<cache>(7) = `test`.`t3`.`f3`)))) having (<cache>(`field1`) < 's')
+set optimizer_switch=@save_optimizer_switch;
+drop table t1,t2,t3;
+End of 5.2 tests
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index d826fbe2aa7..bd661859929 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1277,6 +1277,55 @@ Handler_read_rnd_next	1
 DROP TABLE t1, t2;
 End of 5.1 tests
 #
+# Bug #43368: STRAIGHT_JOIN DOESN'T WORK FOR NESTED JOINS 
+#
+create table t1(c1 int primary key, c2 char(10)) engine=myisam;
+create table t2(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t3(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+create table t4(c1 int primary key, c2 char(10), ref_t1 int) engine=myisam;
+insert into t1 values(1,'a');
+insert into t2 values(1,'a', 1);
+insert into t3 values(1,'a', 1);
+insert into t3 values(2,'b',2);
+insert into t4 values(1,'a', 1);
+insert into t4 values(2,'a', 2);
+insert into t4 values(3,'a', 3);
+insert into t4 values(4,'a', 4);
+insert into t1 values(2,'b');
+insert into t1 values(3,'c');
+EXPLAIN
+SELECT * 
+FROM t4 JOIN
+(t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1)
+ON t4.ref_t1=t1.c1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+EXPLAIN
+SELECT STRAIGHT_JOIN * 
+FROM t4 JOIN
+(t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1)
+ON t4.ref_t1=t1.c1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	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)
+EXPLAIN
+SELECT * 
+FROM t4 STRAIGHT_JOIN
+(t1 JOIN t3 ON t3.ref_t1=t1.c1 JOIN t2 ON t2.ref_t1=t1.c1)
+ON t4.ref_t1=t1.c1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t4.ref_t1	1	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+drop table t1,t2,t3,t4;
+End of 5.2 tests
+#
 # BUG#724275: Crash in JOIN::optimize in maria-5.3
 #
 create table t1 (a int);
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 842ac2e81cd..163043c2cef 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1905,4 +1905,88 @@ b	b	a	b
 DEALLOCATE PREPARE stmt;
 SET SESSION join_cache_level=default;
 DROP TABLE t1,t2,t3;
+#
+# LP bug #943543: LEFT JOIN converted to JOIN with 
+#                 ORed IS NULL(primary key) in WHERE clause                
+#
+CREATE TABLE t1 (
+a int, b int NOT NULL, pk int NOT NULL,
+PRIMARY KEY (pk), INDEX idx(b)
+);
+INSERT INTO t1 VALUES
+(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4),
+(1,9,6), (8,5,7), (NULL,8,8), (8,1,5);
+CREATE TABLE t2 (pk int PRIMARY KEY);
+INSERT INTO t2 VALUES (3), (8), (5);
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+DROP TABLE t2;
+CREATE TABLE t2 (c int, d int, KEY (c));
+INSERT INTO t2 VALUES
+(3,30), (8,88), (5,50), (8,81),
+(4,40), (9,90), (7,70), (9,90),
+(13,130), (18,188), (15,150), (18,181),
+(14,140), (19,190), (17,170), (19,190);
+INSERT INTO t1 VALUES (8,5,9);
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	idx	idx	4	const	2	100.00	Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where; Using filesort
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+DROP TABLE t1,t2;
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
index 5a99cb83b90..650946d8b06 100644
--- a/mysql-test/r/join_outer_innodb.result
+++ b/mysql-test/r/join_outer_innodb.result
@@ -40,8 +40,7 @@ WHERE t1.col_int_key BETWEEN 5 AND 6
 AND t1.pk IS NULL OR t1.pk IN (5)
 ORDER BY pk;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	const	PRIMARY,col_int_key	PRIMARY	4	const	1	Using where
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.col_int	1	Using index
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 
 SELECT t1.pk
 FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int
@@ -60,3 +59,130 @@ WHERE (t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b) AND t2.b = 4
 GROUP BY 1;
 a
 DROP TABLE t1,t2;
+#
+Bug #59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (1,'1');
+CREATE TABLE t2 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+col_int int(11) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t4 VALUES (1,1,1,'1');
+CREATE TABLE t5 (
+col_int int(11) DEFAULT NULL,
+col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t5 VALUES (1,'1');
+CREATE TABLE t6 (
+col_int_key int(11) DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+pk int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t6 VALUES (1,'1',1);
+SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM t6 AS t6a
+LEFT JOIN
+(
+t2 
+RIGHT JOIN
+(
+(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk)
+LEFT JOIN
+(t5 JOIN t6 AS t6b 
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE;
+pk	pk
+1	NULL
+EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
+FROM t6 AS t6a
+LEFT JOIN
+(
+t2 
+RIGHT JOIN
+(
+(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk)
+LEFT JOIN
+(t5 JOIN t6 AS t6b 
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6a	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
+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	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)
+SELECT t6a.pk, t2.pk
+FROM t6 AS t6a
+LEFT JOIN
+(
+t2 
+RIGHT JOIN
+(
+(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk)
+LEFT JOIN
+(t5 JOIN t6 AS t6b 
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE;
+pk	pk
+1	NULL
+EXPLAIN SELECT t6a.pk, t2.pk
+FROM t6 AS t6a
+LEFT JOIN
+(
+t2 
+RIGHT JOIN
+(
+(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk)
+LEFT JOIN
+(t5 JOIN t6 AS t6b 
+ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key)
+ON t1.pk = t5.col_int
+)
+ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
+AND t5.col_varchar_10_utf8_key = 0
+)
+ON t6a.pk IS TRUE
+WHERE t6b.col_int_key IS TRUE;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6a	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
+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)
+drop table t1,t2,t3,t4,t5,t6;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 046c758a751..b29c6241f14 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1916,6 +1916,90 @@ b	b	a	b
 DEALLOCATE PREPARE stmt;
 SET SESSION join_cache_level=default;
 DROP TABLE t1,t2,t3;
+#
+# LP bug #943543: LEFT JOIN converted to JOIN with 
+#                 ORed IS NULL(primary key) in WHERE clause                
+#
+CREATE TABLE t1 (
+a int, b int NOT NULL, pk int NOT NULL,
+PRIMARY KEY (pk), INDEX idx(b)
+);
+INSERT INTO t1 VALUES
+(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4),
+(1,9,6), (8,5,7), (NULL,8,8), (8,1,5);
+CREATE TABLE t2 (pk int PRIMARY KEY);
+INSERT INTO t2 VALUES (3), (8), (5);
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+DROP TABLE t2;
+CREATE TABLE t2 (c int, d int, KEY (c));
+INSERT INTO t2 VALUES
+(3,30), (8,88), (5,50), (8,81),
+(4,40), (9,90), (7,70), (9,90),
+(13,130), (18,188), (15,150), (18,181),
+(14,140), (19,190), (17,170), (19,190);
+INSERT INTO t1 VALUES (8,5,9);
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	idx	idx	4	const	2	100.00	Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where; Using filesort
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+DROP TABLE t1,t2;
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';
diff --git a/mysql-test/r/maria_mrr.result b/mysql-test/r/maria_mrr.result
index d0bf94d7dab..af7789eebff 100644
--- a/mysql-test/r/maria_mrr.result
+++ b/mysql-test/r/maria_mrr.result
@@ -390,6 +390,8 @@ col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL,
 PRIMARY KEY (pk),
 KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key)
 ) ENGINE=Aria;
+Warnings:
+Warning	1071	Specified key was too long; max key length is 1000 bytes
 INSERT INTO t1 VALUES
 (1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'),
 (11,'zx'), (12,'abcdefjhjm'), (13,'jn'), (14,'abcdefjhjp'), (16,'abcdefjhjr');
@@ -403,7 +405,7 @@ WHERE
 table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	table2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	SIMPLE	table1	ref	PRIMARY,col_varchar_1024_latin1_key	col_varchar_1024_latin1_key	1027	test.table2.col_varchar_10_latin1	2	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	table1	ref	PRIMARY,col_varchar_1024_latin1_key	col_varchar_1024_latin1_key	1003	test.table2.col_varchar_10_latin1	2	Using where
 SELECT count(*)
 FROM t1 AS table1, t2 AS table2
 WHERE
@@ -425,6 +427,8 @@ f4 varchar(1024) COLLATE utf8_bin,
 f5 varchar(1024) COLLATE latin1_bin, 
 KEY (f5)
 ) ENGINE=Aria TRANSACTIONAL=0 ;
+Warnings:
+Warning	1071	Specified key was too long; max key length is 1000 bytes
 # Fill the table with some data
 SELECT alias2.* , alias1.f2 
 FROM 
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index d2e3594dfc6..3533001ccec 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4891,10 +4891,10 @@ SET SESSION join_buffer_size = 2048;
 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
-1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.f1	1	
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index f5aa02fe94c..a5416eb6718 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -4902,10 +4902,10 @@ SET SESSION join_buffer_size = 2048;
 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
-1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	12	
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	const	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.f1	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
 SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index d2e3594dfc6..3533001ccec 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -4891,10 +4891,10 @@ SET SESSION join_buffer_size = 2048;
 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
-1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	12	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.f1	1	
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index e7883c5801e..db0c1c99d76 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2973,7 +2973,7 @@ Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cac
 explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	9	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`flag` = 'N'))
@@ -3563,7 +3563,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	23	test.t1.a,test.t1.b	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	21	test.t1.a,test.t1.b	1	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary
 ALTER TABLE t1 ADD INDEX(a);
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
@@ -3575,7 +3575,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	23	test.t1.a,test.t1.b	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	21	test.t1.a,test.t1.b	1	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary
 DROP TABLE t1;
 create table t1( f1 int,f2 int);
@@ -4524,14 +4524,14 @@ SET @save_join_cache_level=@@join_cache_level;
 SET join_cache_level=0;
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	5	const	1	100.00	
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary
 Warnings:
 Note	1003	select 1 AS `1` from  <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1)
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	5	const	1	100.00	
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary
 Warnings:
@@ -5984,7 +5984,7 @@ WHERE col_varchar_nokey IN
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it1	ref	idx_cvk_cik	idx_cvk_cik	9	const,const	1	Using where; Using index
 SELECT col_int_nokey FROM ot 
 WHERE col_varchar_nokey IN 
@@ -5997,7 +5997,7 @@ WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	it2	ref	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	const,const	1	Using where; Using index
 SELECT col_int_nokey FROM ot
 WHERE (col_varchar_nokey, 'x') IN
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 14c4254028d..82360f9fe9f 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -564,7 +564,7 @@ WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 ));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	f3	f3	5	const	0	Using where
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
-2	SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	5	func	1	
+2	SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
 SELECT * FROM t1
 WHERE f3 = (
@@ -579,7 +579,7 @@ WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 ));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ref	f3	f3	5	const	0	Using where
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
-2	SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+2	SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
 SELECT * FROM t1
 WHERE f3 = (
@@ -1129,7 +1129,7 @@ EXPLAIN
 SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
@@ -1138,7 +1138,7 @@ c1	c1	c1
 EXPLAIN
 SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t4	index	NULL	PRIMARY	3	NULL	2	Using index; Using join buffer (flat, BNL join)
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
@@ -1704,37 +1704,6 @@ AND t2.f2 = t1.f1;
 f1	f2	f1	f2
 drop table t1,t2,t3,t4;
 #
-# LP BUG#806943 Second crash with select_describe with nested subqueries in maria-5.3
-#
-CREATE TABLE t1 ( f4 int) ;
-INSERT INTO t1 VALUES (0),(0);
-CREATE TABLE t2 ( f2 int) ;
-CREATE TABLE t3 ( f1 int NOT NULL );
-CREATE TABLE t4 ( f2 int, f3 int) ;
-INSERT INTO t4 VALUES (8,0),(3,0);
-EXPLAIN SELECT *
-FROM t2, t3
-WHERE t3.f1 = (
-SELECT SUM( f2 )
-FROM t4
-WHERE EXISTS (
-SELECT DISTINCT f4
-FROM t1));
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	
-3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
-SELECT *
-FROM t2, t3
-WHERE t3.f1 = (
-SELECT SUM( f2 )
-FROM t4
-WHERE EXISTS (
-SELECT DISTINCT f4
-FROM t1));
-f2	f1
-drop table t1, t2, t3, t4;
-#
 # LP BUG#611690 Crash in select_describe() with nested subqueries
 #
 CREATE TABLE t1 (
@@ -2031,6 +2000,20 @@ WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT t4.a FROM t4) and t3
 c	a	b
 10	7	0
 drop table t2, t3, t4;
+#
+# BUG#934597: Assertion `! is_set()' failed in Diagnostics_area::set_ok_status(THD...
+#
+CREATE TABLE t1 ( a VARCHAR(1) );
+INSERT INTO t1 VALUES ('u'),('k');
+CREATE TABLE t2 AS
+SELECT a AS field1 FROM t1
+WHERE ( SELECT alias1.a
+FROM t1 AS alias1
+) IS NOT NULL;
+ERROR 21000: Subquery returns more than 1 row
+DROP TABLE t2;
+ERROR 42S02: Unknown table 't2'
+DROP TABLE t1;
 set optimizer_switch=@subselect4_tmp;
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index b6e31a6ec34..04e95baef88 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1473,7 +1473,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on';
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Rowid-ordered scan
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 pk
@@ -1861,6 +1861,58 @@ WHERE 'condition'='impossible'
 MIN(a)
 NULL
 DROP TABLE t1;
+#
+# BUG#938131: Subquery materialization is not used in CREATE TABLE SELECT
+# 
+CREATE TABLE t1(a int);
+INSERT INTO t1 values(1),(2);
+CREATE TABLE t2(a int);
+INSERT INTO t2 values(1),(2);
+# Should use Materialization:
+EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+flush status;
+CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
+SHOW STATUS LIKE 'Created_tmp_tables';
+Variable_name	Value
+Created_tmp_tables	3
+DROP TABLE t1,t2,t3;
+# 
+# BUG#939009: Crash with aggregate function in IN subquery 
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='materialization=on,semijoin=on';
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (7,1), (4,2), (7,7);
+CREATE TABLE t2 ( c INT );
+INSERT INTO t2 VALUES (4), (7), (6);
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (`test`.`t1`.`b` = 7) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7)))
+SELECT * FROM t1
+WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
+a	b
+7	7
+EXPLAIN
+SELECT * FROM t1
+WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT * FROM t1
+WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
+a	b
+SET optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1,t2;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;
@@ -1983,7 +2035,7 @@ SET @@optimizer_switch='default,semijoin=on,materialization=on';
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 pk
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
index 6cf5062c61b..893209a07fc 100644
--- a/mysql-test/r/subselect_mat_cost.result
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -348,7 +348,7 @@ FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000))
 AND Language IN ('English','Spanish');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	CountryLanguage	range	Language	Language	30	NULL	72	Using index condition; Using where; Rowid-ordered scan
-2	DEPENDENT SUBQUERY	City	ref	CityName	CityName	35	func	2	Using index condition; Using where
+2	DEPENDENT SUBQUERY	City	ref	CityName	CityName	35	func	1	Using index condition; Using where
 2	DEPENDENT SUBQUERY	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using index
 select count(*)
 from CountryLanguage
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 3e2305ebe73..ec3206c4d90 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -2979,7 +2979,7 @@ Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optim
 explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	9	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`flag` = 'N'))
@@ -3569,7 +3569,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	23	test.t1.a,test.t1.b	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	21	test.t1.a,test.t1.b	1	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary
 ALTER TABLE t1 ADD INDEX(a);
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
@@ -3581,7 +3581,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	9	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	23	test.t1.a,test.t1.b	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	21	test.t1.a,test.t1.b	1	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary
 DROP TABLE t1;
 create table t1( f1 int,f2 int);
@@ -4530,14 +4530,14 @@ SET @save_join_cache_level=@@join_cache_level;
 SET join_cache_level=0;
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	5	const	1	100.00	
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary
 Warnings:
 Note	1003	select 1 AS `1` from  <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1)
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	5	const	1	100.00	
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary
 Warnings:
@@ -5990,7 +5990,7 @@ WHERE col_varchar_nokey IN
 (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it1	ref	idx_cvk_cik	idx_cvk_cik	9	const,const	1	Using where; Using index
 SELECT col_int_nokey FROM ot 
 WHERE col_varchar_nokey IN 
@@ -6003,7 +6003,7 @@ WHERE (col_varchar_nokey, 'x') IN
 (SELECT col_varchar_key, col_varchar_key2 FROM it2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	it2	ref	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	const,const	1	Using where; Using index
 SELECT col_int_nokey FROM ot
 WHERE (col_varchar_nokey, 'x') IN
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 3bc1d1f4293..924d809eb6e 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -802,7 +802,7 @@ INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii','iiii','ffff','ffff','ffff','f
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	13	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	11	func,func	1	100.00	
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Rowid-ordered scan
 Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0))
@@ -1053,8 +1053,8 @@ AND t1.val IN (SELECT t3.val FROM t3
 WHERE  t3.val LIKE 'a%' OR t3.val LIKE 'e%');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	14	func	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	14	func	1	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	13	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	13	func	1	
 3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
 SELECT *
@@ -1633,9 +1633,9 @@ select * from t1 A, t1 B
 where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	C	ALL	NULL	NULL	NULL	NULL	3	
 3	MATERIALIZED	D	ALL	NULL	NULL	NULL	NULL	3	
 drop table t1, t2;
@@ -2333,7 +2333,7 @@ explain
 SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	13	Using where
 2	MATERIALIZED	t2	ref	b	b	4	test.t3.a	1	Using index
 SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a);
@@ -2453,7 +2453,7 @@ EXPLAIN
 SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
 SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 2d8c189942e..0c41fef01ea 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -180,7 +180,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2)
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	32	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -248,7 +248,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2)
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	52	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -289,7 +289,7 @@ from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	9	func	1	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
 2	MATERIALIZED	t1	index	a	a	5	NULL	10	Using where; Using index
 2	MATERIALIZED	t2	ref	a	a	5	test.t1.a	1	Using index
 2	MATERIALIZED	t3	ref	a	a	5	test.t1.a	1	Using index
@@ -718,7 +718,7 @@ The following must use loose index scan over t3, key a:
 explain select count(a) from t2 where a in ( SELECT  a FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	1000	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	index	a	a	5	NULL	30000	Using index
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
@@ -875,7 +875,7 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index f2895662196..073f1619b91 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -130,7 +130,7 @@ set max_heap_table_size= @save_max_heap_table_size;
 explain select * from t1 where a in (select b from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	index	b	b	5	NULL	20	Using index
 select * from t1;
 a	b
@@ -192,7 +192,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2)
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	32	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -260,7 +260,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2)
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	52	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -301,7 +301,7 @@ from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	9	func	1	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
 2	MATERIALIZED	t1	index	a	a	5	NULL	10	Using where; Using index
 2	MATERIALIZED	t2	ref	a	a	5	test.t1.a	1	Using index
 2	MATERIALIZED	t3	ref	a	a	5	test.t1.a	1	Using index
@@ -732,7 +732,7 @@ The following must use loose index scan over t3, key a:
 explain select count(a) from t2 where a in ( SELECT  a FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	1000	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	index	a	a	5	NULL	30000	Using index
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
@@ -889,7 +889,7 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t2	hash_ALL	NULL	#hash#$hj	5	test.t3.a	1	Using where; Using join buffer (flat, BNLH join)
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	
@@ -987,7 +987,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	
 SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a);
 a	b
@@ -996,7 +996,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	<subquery2>	hash_ALL	distinct_key	#hash#distinct_key	5	test.t1.b	1	Using join buffer (flat, BNLH join)
+1	PRIMARY	<subquery2>	hash_ALL	distinct_key	#hash#distinct_key	4	test.t1.b	1	Using join buffer (flat, BNLH join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using temporary
 SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a);
 a	b
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index 3e68c9ebeb5..f15492c7322 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -182,7 +182,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2)
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	32	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -250,7 +250,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2)
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	it	ALL	NULL	NULL	NULL	NULL	52	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -291,7 +291,7 @@ from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	9	func	1	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
 2	MATERIALIZED	t1	index	a	a	5	NULL	10	Using where; Using index
 2	MATERIALIZED	t2	ref	a	a	5	test.t1.a	1	Using index
 2	MATERIALIZED	t3	ref	a	a	5	test.t1.a	1	Using index
@@ -720,7 +720,7 @@ The following must use loose index scan over t3, key a:
 explain select count(a) from t2 where a in ( SELECT  a FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	1000	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	index	a	a	5	NULL	30000	Using index
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
@@ -877,7 +877,7 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 96fe8819705..6a45a5866f6 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -815,7 +815,7 @@ INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii','iiii','ffff','ffff','ffff','f
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	13	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	11	func,func	1	100.00	
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Rowid-ordered scan
 Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0))
@@ -1066,8 +1066,8 @@ AND t1.val IN (SELECT t3.val FROM t3
 WHERE  t3.val LIKE 'a%' OR t3.val LIKE 'e%');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	14	func	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	14	func	1	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	13	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	13	func	1	
 3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
 SELECT *
@@ -1646,9 +1646,9 @@ select * from t1 A, t1 B
 where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	C	ALL	NULL	NULL	NULL	NULL	3	
 3	MATERIALIZED	D	ALL	NULL	NULL	NULL	NULL	3	
 drop table t1, t2;
@@ -2003,7 +2003,7 @@ explain extended
 SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (incremental, BNL join)
 2	MATERIALIZED	t4	index	f2	f2	5	NULL	2	100.00	Using index
@@ -2347,7 +2347,7 @@ explain
 SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	13	Using where
 2	MATERIALIZED	t2	ref	b	b	4	test.t3.a	1	Using index
 SELECT * FROM t1 WHERE (a) IN (SELECT a FROM t2 JOIN t3 ON b = a);
@@ -2467,7 +2467,7 @@ EXPLAIN
 SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
 SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
@@ -2783,7 +2783,7 @@ SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
 WHERE (a, b) IN (SELECT a, b FROM t1 t);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	idx_a	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 1	PRIMARY	t2	ref	idx_c	idx_c	4	test.t1.b	2	Using where; Using index
 2	MATERIALIZED	t	ALL	idx_a	NULL	NULL	NULL	3	
 SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
@@ -2798,7 +2798,7 @@ SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
 WHERE (a, b) IN (SELECT a, b FROM t1 t);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	idx_a	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 1	PRIMARY	t3	ref	idx_c	idx_c	4	test.t1.b	2	Using where
 2	MATERIALIZED	t	ALL	idx_a	NULL	NULL	NULL	3	
 SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
@@ -2814,7 +2814,7 @@ SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
 WHERE (a, b) IN (SELECT a, b FROM t1 t);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	idx_a	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 1	PRIMARY	t2	ref	idx_c	idx_c	4	test.t1.b	2	Using where; Using index
 2	MATERIALIZED	t	ALL	idx_a	NULL	NULL	NULL	3	
 SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
@@ -2829,7 +2829,7 @@ SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
 WHERE (a, b) IN (SELECT a, b FROM t1 t);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	idx_a	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 1	PRIMARY	t3	ref	idx_c	idx_c	4	test.t1.b	2	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 2	MATERIALIZED	t	ALL	idx_a	NULL	NULL	NULL	3	
 SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index e3c5926ffee..e181fd20abc 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -47,7 +47,7 @@ explain extended
 select * from t1 where a1 in (select b1 from t2 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	9	func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0'))
@@ -59,7 +59,7 @@ explain extended
 select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	9	func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0'))
@@ -71,7 +71,7 @@ explain extended
 select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0'))
@@ -83,7 +83,7 @@ explain extended
 select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	test.t1.a1,test.t1.a2	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from  <materialize> (select `test`.`t2`.`b1`,min(`test`.`t2`.`b2`) from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
@@ -106,7 +106,7 @@ explain extended
 select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1i	index	it1i1,it1i3	#	18	#	3	100.00	#
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	9	#	1	100.00	#
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	8	#	1	100.00	#
 2	MATERIALIZED	t2i	index	it2i1,it2i3	#	9	#	5	100.00	#
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`<subquery2>`.`max(b1)` = `test`.`t1i`.`a1`)
@@ -153,7 +153,7 @@ explain extended
 select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	test.t1.a1,test.t1.a2	1	100.00	
 2	MATERIALIZED	t2i	range	NULL	it2i3	9	NULL	3	100.00	Using index for group-by
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`max(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
@@ -165,12 +165,12 @@ prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2)
 execute st1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	test.t1.a1,test.t1.a2	1	
 2	MATERIALIZED	t2i	range	NULL	it2i3	9	NULL	3	Using index for group-by
 execute st1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	test.t1.a1,test.t1.a2	1	
 2	MATERIALIZED	t2i	range	NULL	it2i3	9	NULL	3	Using index for group-by
 prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
 execute st2;
@@ -185,7 +185,7 @@ explain extended
 select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	test.t1.a1,test.t1.a2	1	100.00	
 2	MATERIALIZED	t2i	range	it2i1,it2i3	it2i3	18	NULL	3	100.00	Using where; Using index for group-by
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`<subquery2>`.`min(b2)` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
@@ -233,7 +233,7 @@ explain extended
 select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	test.t1.a1,test.t1.a2	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	
 Warnings:
 Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from  <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`<subquery2>`.`b2` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
@@ -245,7 +245,7 @@ explain extended
 select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1i	index	it1i1,it1i2,it1i3	it1i3	18	NULL	3	100.00	Using where; Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1i.a1,test.t1i.a2	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	test.t1i.a1,test.t1i.a2	1	100.00	
 2	MATERIALIZED	t2i	index	NULL	it2i3	18	NULL	5	100.00	Using index
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`<subquery2>`.`b2` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`))
@@ -299,8 +299,8 @@ where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
 3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 3	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
@@ -341,8 +341,8 @@ b2 in (select c2 from t3 where c2 LIKE '%03')) and
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
 5	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 5	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
@@ -367,7 +367,7 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
 5	MATERIALIZED	t3c	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 5	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
@@ -436,7 +436,7 @@ where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
 4	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 4	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
@@ -460,7 +460,7 @@ a1 = c1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	16	func,func	1	100.00	
 4	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 4	MATERIALIZED	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer (flat, BNL join)
 2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
@@ -645,7 +645,7 @@ from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	20	func	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	19	func	1	100.00	Using where
 2	MATERIALIZED	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` > '0') and (`test`.`t1_16`.`a1` = substr(`test`.`t2_16`.`b1`,1,16)))
@@ -675,7 +675,7 @@ from t1_16
 where a1 in (select group_concat(b1) from t2_16 group by b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_16.a1	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	260	test.t1_16.a1	1	100.00	Using where
 2	MATERIALIZED	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from  <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -760,7 +760,7 @@ from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	517	func	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	516	func	1	100.00	Using where
 2	MATERIALIZED	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` > '0') and (`test`.`t1_512`.`a1` = substr(`test`.`t2_512`.`b1`,1,512)))
@@ -775,7 +775,7 @@ from t1_512
 where a1 in (select group_concat(b1) from t2_512 group by b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_512.a1	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	260	test.t1_512.a1	1	100.00	Using where
 2	MATERIALIZED	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from  <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -789,7 +789,7 @@ from t1_512
 where a1 in (select group_concat(b1) from t2_512 group by b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_512.a1	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	260	test.t1_512.a1	1	100.00	Using where
 2	MATERIALIZED	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from  <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -870,7 +870,7 @@ from t1_1024
 where a1 in (select group_concat(b1) from t2_1024 group by b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_1024.a1	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	260	test.t1_1024.a1	1	100.00	Using where
 2	MATERIALIZED	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from  <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -884,7 +884,7 @@ from t1_1024
 where a1 in (select group_concat(b1) from t2_1024 group by b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_1024.a1	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	260	test.t1_1024.a1	1	100.00	Using where
 2	MATERIALIZED	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from  <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -965,7 +965,7 @@ from t1_1025
 where a1 in (select group_concat(b1) from t2_1025 group by b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_1025.a1	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	260	test.t1_1025.a1	1	100.00	Using where
 2	MATERIALIZED	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from  <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -979,7 +979,7 @@ from t1_1025
 where a1 in (select group_concat(b1) from t2_1025 group by b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_1025.a1	1	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	260	test.t1_1025.a1	1	100.00	Using where
 2	MATERIALIZED	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from  <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `<subquery2>`.`group_concat(b1)`)
@@ -1001,7 +1001,7 @@ from t1bit
 where (a1, a2) in (select b1, b2 from t2bit);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1bit	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
 2	MATERIALIZED	t2bit	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
 Note	1003	select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` semi join (`test`.`t2bit`) where 1
@@ -1187,7 +1187,7 @@ insert into t1 values (5);
 explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	5	const	1	
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	
 2	MATERIALIZED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1);
 min(a1)
@@ -1236,7 +1236,7 @@ insert into t1 values ('aa', 'aaaa');
 explain select a,b from t1 where b in (select a 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	<subquery2>	eq_ref	distinct_key	distinct_key	3	func	1	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	2	func	1	Using where
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	2	
 select a,b from t1 where b in (select a from t1);
 a	b
@@ -1273,7 +1273,7 @@ GROUP BY t3i
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	5	const	1	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	const	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
 3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	Using temporary
@@ -1510,7 +1510,7 @@ SET @@optimizer_switch='semijoin=on,materialization=on';
 EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Rowid-ordered scan
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 pk
@@ -1878,7 +1878,7 @@ insert into t2 values
 explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	i_a	NULL	NULL	NULL	8	Using where
 select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
 a	b
@@ -1898,6 +1898,59 @@ WHERE 'condition'='impossible'
 MIN(a)
 NULL
 DROP TABLE t1;
+#
+# BUG#938131: Subquery materialization is not used in CREATE TABLE SELECT
+# 
+CREATE TABLE t1(a int);
+INSERT INTO t1 values(1),(2);
+CREATE TABLE t2(a int);
+INSERT INTO t2 values(1),(2);
+# Should use Materialization:
+EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	test.t1.a	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+flush status;
+CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
+SHOW STATUS LIKE 'Created_tmp_tables';
+Variable_name	Value
+Created_tmp_tables	2
+DROP TABLE t1,t2,t3;
+# 
+# BUG#939009: Crash with aggregate function in IN subquery 
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='materialization=on,semijoin=on';
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (7,1), (4,2), (7,7);
+CREATE TABLE t2 ( c INT );
+INSERT INTO t2 VALUES (4), (7), (6);
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materialize> (select max(`test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where ((`test`.`t1`.`a` = `<subquery2>`.`MAX(c)`) and (`test`.`t1`.`b` = 7) and (<cache>(isnull(`<subquery2>`.`MAX(c)`)) or (`<subquery2>`.`MAX(c)` = 7)))
+SELECT * FROM t1
+WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
+a	b
+7	7
+EXPLAIN
+SELECT * FROM t1
+WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+SELECT * FROM t1
+WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
+a	b
+SET optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1,t2;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result
index 8df74536de7..2a3768c8c50 100644
--- a/mysql-test/r/subselect_sj_nonmerged.result
+++ b/mysql-test/r/subselect_sj_nonmerged.result
@@ -40,14 +40,14 @@ alter table t3 add primary key(a);
 #   (despite that subquery's join output estimate is 50 rows)
 explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	Using where
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	8	<subquery2>.max(t2.a)	1	Using where; Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 # Compare to this which really will have 50 record combinations:
 explain select * from t3 where a in (select max(t2.a) from t1, t2 group by t2.b, t1.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	50	Using where
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	50	
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	8	<subquery2>.max(t2.a)	1	Using where; Using index
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
@@ -57,7 +57,7 @@ SET optimizer_switch='outer_join_with_cache=off';
 explain select * from t3 
 where a in (select max(t2.a) from t1 left join t2 on t1.a=t2.a group by t2.b, t1.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	50	Using where
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	50	
 1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	8	<subquery2>.max(t2.a)	1	Using where; Using index
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using temporary
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
@@ -69,7 +69,7 @@ t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	t4	ALL	a	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.t4.a	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	test.t4.a	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B;
@@ -77,8 +77,8 @@ explain select * from t4 where
 t4.a in (select max(t2.a) from t1, t2 group by t2.b) and 
 t4.b in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	Using where
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	
+1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 1	PRIMARY	t4	ref	a	a	10	<subquery2>.max(t2.a),<subquery3>.max(t2.a)	12	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
@@ -114,7 +114,7 @@ insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
 # The following must use non-merged SJ-Materialization:
 explain select * from t1 X join t0 Y on X.a < Y.a where X.a in (select max(a) from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	Using where
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
 1	PRIMARY	X	ref	a	a	5	<subquery2>.max(a)	1	Using index
 1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	
diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result
index 5a484f3ce3c..5d89a1ee407 100644
--- a/mysql-test/r/type_year.result
+++ b/mysql-test/r/type_year.result
@@ -359,3 +359,20 @@ total_rows	min_value	MAX(c1+0)
 DROP TABLE t1;
 #
 End of 5.1 tests
+create function y2k() returns int deterministic return 2000;
+create table t1 (a year(2), b int);
+insert t1 values (0,2000);
+select a from t1 where a=2000;
+a
+00
+select a from t1 where a=1000+1000;
+a
+00
+select a from t1 where a=(select 2000 from dual where 1);
+a
+select a from t1 where a=y2k();
+a
+select a from t1 where a=b;
+a
+drop table t1;
+drop function y2k;