Fixed LP bug #939009.

The result of materialization of the right part of an IN subquery predicate
is placed into a temporary table. Each row of the materialized table is
distinct. A unique key over all fields of the temporary table is defined and
created. It allows to perform key look-ups into the table.
The table created for a materialized subquery can be accessed by key as
any other table. The function best_access-path search for the best access
to join a table to a given partial join. With some where conditions this
function considers a possibility of a ref_or_null access. If such access
employs the unique key on the temporary table then when estimating
the cost this access the function tries to use the array rec_per_key. Yet,
such array is not built for this unique key. This causes a crash of the server.

Rows returned by the subquery that contain nulls don't have to be placed
into temporary table, as they cannot be match any row produced by the
left part of the subquery predicate. So all fields of the temporary table
can be defined as non-nullable. In this case any ref_or_null access
to the temporary table does not make any sense and it does not make sense
to estimate such an access.

The fix makes sure that the temporary table for a materialized IN subquery
is defined with columns that are all non-nullable. The also ensures that 
any row with nulls returned by the subquery is not placed into the
temporary table.  

18 files changed, 199 insertions, 104 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 49d5c409e31..2f0e0bd371b 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1458,14 +1458,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 9660640baaa..f1d3777e097 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 858d31e05c8..3d0502e163c 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/subselect.result b/mysql-test/r/subselect.result
index bca4eb677de..327cb5b2563 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);
@@ -4471,14 +4471,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:
@@ -5585,7 +5585,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 
@@ -5598,7 +5598,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 4e3247e9da8..b98c058a303 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1249,7 +1249,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
@@ -1258,7 +1258,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
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 4d02e5012e2..baf7e379882 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,39 @@ WHERE 'condition'='impossible'
 MIN(a)
 NULL
 DROP TABLE t1;
+#
+# 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	Using where
+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 (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
+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	Using where
+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 +2016,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 e5f2df1c5c1..900bad0183a 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 e170debd6d2..1167e9483c9 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);
@@ -4477,14 +4477,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:
@@ -5591,7 +5591,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 
@@ -5604,7 +5604,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 f92b33ff651..1d0aae275ed 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;
@@ -2323,7 +2323,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);
@@ -2443,7 +2443,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 4e9672446a3..1ee01030b4b 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -183,7 +183,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) 
@@ -251,7 +251,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) 
@@ -292,7 +292,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
@@ -721,7 +721,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)
@@ -878,7 +878,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 1b7d65275ab..e69548dcf5f 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -133,7 +133,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
@@ -195,7 +195,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) 
@@ -263,7 +263,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) 
@@ -304,7 +304,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
@@ -735,7 +735,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)
@@ -892,7 +892,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	
@@ -990,7 +990,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
@@ -999,7 +999,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 64d1ce2ef74..f6e98a37007 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -185,7 +185,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) 
@@ -253,7 +253,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) 
@@ -294,7 +294,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
@@ -723,7 +723,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)
@@ -880,7 +880,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 769373429da..baa2aec8aea 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;
@@ -1993,7 +1993,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
@@ -2337,7 +2337,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);
@@ -2457,7 +2457,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);
@@ -2773,7 +2773,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)
@@ -2788,7 +2788,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)
@@ -2804,7 +2804,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)
@@ -2819,7 +2819,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 b21236de7b9..3bec3f541a3 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,39 @@ WHERE 'condition'='impossible'
 MIN(a)
 NULL
 DROP TABLE t1;
+#
+# 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	Using where
+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 (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
+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	Using where
+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/suite/pbxt/r/group_min_max.result b/mysql-test/suite/pbxt/r/group_min_max.result
index b73b393f9da..15554216056 100644
--- a/mysql-test/suite/pbxt/r/group_min_max.result
+++ b/mysql-test/suite/pbxt/r/group_min_max.result
@@ -2256,7 +2256,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	15	Using where
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	15	
 1	PRIMARY	t1_outer	ref	a	a	5	<subquery2>.max(b)	1	Using index
 2	MATERIALIZED	t1	index	NULL	a	10	NULL	15	Using index
 EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result
index 0a065e61edf..96e14cdf785 100644
--- a/mysql-test/suite/pbxt/r/subselect.result
+++ b/mysql-test/suite/pbxt/r/subselect.result
@@ -2842,7 +2842,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'))
@@ -3435,7 +3435,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);
@@ -3447,7 +3447,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);
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 0d04b3f984a..fba15ede5a2 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1559,6 +1559,35 @@ WHERE a IN (
 
 DROP TABLE t1;
 
+--echo #
+--echo # BUG#939009: Crash with aggregate function in IN subquery 
+--echo #
+
+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);
+SELECT * FROM t1
+  WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
+
+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);
+SELECT * FROM t1
+  WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
+
+SET optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1,t2;
+
 --echo # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;