MWL#110: Make EXPLAIN always show materialization separately

- Add Item_in_subselect::get_identifier() that returns subquery's id
- Change select_describe() to produce output in new format
- Update test results (checked)

8 files changed, 123 insertions, 70 deletions

diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 482cf2ba349..af483939598 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1017,10 +1017,11 @@ update t22 set c = '2005-12-08 15:58:27' where a = 255;
 explain select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort; Start materialize; Scan
-1	PRIMARY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; End materialize; Using join buffer
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	8	Using temporary; Using filesort
 1	PRIMARY	t21	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
 1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
+2	SUBQUERY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer
 select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 a	b	c
@@ -1034,7 +1035,8 @@ select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) a
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	Y	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	DEPENDENT SUBQUERY	Z	ALL	NULL	NULL	NULL	NULL	2	Materialize
+2	DEPENDENT SUBQUERY	subselect3	eq_ref	unique_key	unique_key	5	func	1	
+3	SUBQUERY	Z	ALL	NULL	NULL	NULL	NULL	2	
 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
 subq
 NULL
@@ -1156,8 +1158,9 @@ create table t3 ( a int , filler char(100), key(a));
 insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	2	
 1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -1204,8 +1207,9 @@ insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C
 explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
 1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
 explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -1233,14 +1237,16 @@ insert into t0 values(1,1);
 explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
 1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
 1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using where
+2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where
 drop table t0,t1,t2,t3,t4;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1264,13 +1270,15 @@ set @@optimizer_switch='firstmatch=off';
 explain select * from t1 where (a,b) in (select a,b from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	10	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	
 set @save_optimizer_search_depth=@@optimizer_search_depth;
 set @@optimizer_search_depth=63;
 explain select * from t1 where (a,b) in (select a,b from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	10	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	
 set @@optimizer_search_depth=@save_optimizer_search_depth;
 set @@optimizer_switch=default;
 drop table t0, t1, t2;
@@ -1308,9 +1316,10 @@ insert into t2 select * from t2;
 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
-1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	End materialize; Using join buffer
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	15	func	1	
+2	SUBQUERY	X	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	Y	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
+2	SUBQUERY	Z	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
 drop table t0,t1,t2;
 
 BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
@@ -1380,9 +1389,10 @@ INNER JOIN t2 c ON c.idContact=cona.idContact
 WHERE cona.postalStripped='T2H3B2'
 	);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	cona	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start materialize; Scan
-1	PRIMARY	c	eq_ref	PRIMARY	PRIMARY	4	test.cona.idContact	1	100.00	End materialize
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	2	1.00	
 1	PRIMARY	a	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer
+2	SUBQUERY	cona	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	SUBQUERY	c	eq_ref	PRIMARY	PRIMARY	4	test.cona.idContact	1	100.00	
 Warnings:
 Note	1003	select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2'))
 drop table t1,t2,t3;
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 902908f1922..8d0ccd7c9f5 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -1021,10 +1021,11 @@ update t22 set c = '2005-12-08 15:58:27' where a = 255;
 explain select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary; Using filesort; Start materialize; Scan
-1	PRIMARY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; End materialize; Using join buffer
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	8	Using temporary; Using filesort
 1	PRIMARY	t21	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
 1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer
+2	SUBQUERY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	SUBQUERY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer
 select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 a	b	c
@@ -1039,7 +1040,8 @@ select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) a
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	2	
 2	DEPENDENT SUBQUERY	Y	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	DEPENDENT SUBQUERY	Z	ALL	NULL	NULL	NULL	NULL	2	Materialize
+2	DEPENDENT SUBQUERY	subselect3	eq_ref	unique_key	unique_key	5	func	1	
+3	SUBQUERY	Z	ALL	NULL	NULL	NULL	NULL	2	
 select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
 subq
 NULL
@@ -1161,8 +1163,9 @@ create table t3 ( a int , filler char(100), key(a));
 insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
 explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	2	
 1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	Using join buffer
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 select * from t3 where a in (select a from t2);
 a	filler
 1	filler
@@ -1209,8 +1212,9 @@ insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C
 explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
 1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using join buffer
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
 explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
@@ -1238,14 +1242,16 @@ insert into t0 values(1,1);
 explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
 1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	Using join buffer
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
 1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using where; Using join buffer
+2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where
 drop table t0,t1,t2,t3,t4;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -1269,13 +1275,15 @@ set @@optimizer_switch='firstmatch=off';
 explain select * from t1 where (a,b) in (select a,b from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	10	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	
 set @save_optimizer_search_depth=@@optimizer_search_depth;
 set @@optimizer_search_depth=63;
 explain select * from t1 where (a,b) in (select a,b from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	10	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	
 set @@optimizer_search_depth=@save_optimizer_search_depth;
 set @@optimizer_switch=default;
 drop table t0, t1, t2;
@@ -1313,9 +1321,10 @@ insert into t2 select * from t2;
 explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
-1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	End materialize; Using join buffer
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	15	func	1	
+2	SUBQUERY	X	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	Y	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
+2	SUBQUERY	Z	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
 drop table t0,t1,t2;
 
 BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
@@ -1385,9 +1394,10 @@ INNER JOIN t2 c ON c.idContact=cona.idContact
 WHERE cona.postalStripped='T2H3B2'
 	);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	cona	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start materialize; Scan
-1	PRIMARY	c	eq_ref	PRIMARY	PRIMARY	4	test.cona.idContact	1	100.00	End materialize; Using join buffer
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	2	1.00	
 1	PRIMARY	a	index	PRIMARY	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer
+2	SUBQUERY	cona	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	SUBQUERY	c	eq_ref	PRIMARY	PRIMARY	4	test.cona.idContact	1	100.00	Using join buffer
 Warnings:
 Note	1003	select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2'))
 drop table t1,t2,t3;
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index bab2b96a2bf..2956587d83f 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -216,8 +216,9 @@ FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -230,13 +231,15 @@ WHERE EMPNUM IN
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	SIMPLE	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 CREATE INDEX t1_IDX ON t1(EMPNUM);
@@ -251,8 +254,9 @@ FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -265,13 +269,15 @@ WHERE EMPNUM IN
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	SIMPLE	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 DEALLOCATE PREPARE stmt;
 DROP INDEX t1_IDX ON t1;
 EXPLAIN SELECT EMPNAME
@@ -285,8 +291,9 @@ FROM t2
 WHERE PTYPE = 'Design'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
 FROM t1
 WHERE EMPNUM IN
@@ -299,13 +306,15 @@ WHERE EMPNUM IN
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; End materialize; Using join buffer
+1	SIMPLE	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
 DEALLOCATE PREPARE stmt;
 SET SESSION optimizer_switch = @old_optimizer_switch;
 SET SESSION join_cache_level = @old_join_cache_level;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index a850d8b5564..7b175d5216a 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1190,8 +1190,9 @@ INSERT INTO t2 VALUES (13, 1.454);
 SET @@optimizer_switch='default,semijoin=on,materialization=on';
 EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	2	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
 COUNT(*)
 2
@@ -1211,7 +1212,8 @@ 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	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR
 SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
 pk
 2
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index e004d0924bb..1b7965442c0 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -848,7 +848,8 @@ 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	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	13	func	1	1.00	
+2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR
 Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
@@ -1016,7 +1017,8 @@ FROM t1
 WHERE `varchar_nokey`  < 'n' XOR `pk`  )   ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	18	100.00	
-1	PRIMARY	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	8	func	1	1.00	
+2	SUBQUERY	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`))
 SELECT varchar_nokey
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index e74058f7fd6..f17edf3be6c 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -32,8 +32,9 @@ a	b
 9	5
 explain select * from t2 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	3	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ref	b	b	5	test.t1.a	2	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -73,8 +74,9 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
 1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	
+2	SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	
 set @save_ecp= @@engine_condition_pushdown;
 set engine_condition_pushdown=0;
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
@@ -99,7 +101,8 @@ 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	t2	index	b	b	5	NULL	10	Using index; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	t2	index	b	b	5	NULL	10	Using index
 select * from t1;
 a	b
 1	1
@@ -126,8 +129,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	22	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	Using where; Using join buffer
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -159,7 +163,8 @@ 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	it	ALL	NULL	NULL	NULL	NULL	32	Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	32	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 from t2 ot where a in (select a from t1 it);
@@ -192,8 +197,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	22	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	Using where; Using join buffer
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -225,7 +231,8 @@ 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	it	ALL	NULL	NULL	NULL	NULL	52	Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	52	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 from t2 ot where a in (select a from t1 it);
@@ -341,7 +348,8 @@ WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	
-1	PRIMARY	t2	ALL	CountryCode	NULL	NULL	NULL	545	Using where; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	CountryCode	NULL	NULL	NULL	545	Using where
 SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -684,7 +692,8 @@ 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	t3	index	a	a	5	NULL	30000	Using index; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	t3	index	a	a	5	NULL	30000	Using index
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
 1000
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 15b0bdc24a6..67215d1715e 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -36,8 +36,9 @@ a	b
 9	5
 explain select * from t2 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	3	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ref	b	b	5	test.t1.a	2	Using join buffer
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -77,8 +78,9 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
 1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	Using join buffer
+2	SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	
 set @save_ecp= @@engine_condition_pushdown;
 set engine_condition_pushdown=0;
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
@@ -103,7 +105,8 @@ 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	t2	index	b	b	5	NULL	10	Using index; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	t2	index	b	b	5	NULL	10	Using index
 select * from t1;
 a	b
 1	1
@@ -130,8 +133,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	22	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	Using where; Using join buffer
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -163,7 +167,8 @@ 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	it	ALL	NULL	NULL	NULL	NULL	32	Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	32	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 from t2 ot where a in (select a from t1 it);
@@ -196,8 +201,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	22	Materialize; Scan
+1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	Using where; Using join buffer
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -229,7 +235,8 @@ 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	it	ALL	NULL	NULL	NULL	NULL	52	Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	52	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
 from t2 ot where a in (select a from t1 it);
@@ -345,7 +352,8 @@ WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	
-1	PRIMARY	t2	ALL	CountryCode	NULL	NULL	NULL	545	Using where; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+2	SUBQUERY	t2	ALL	CountryCode	NULL	NULL	NULL	545	Using where
 SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -690,7 +698,8 @@ 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	t3	index	a	a	5	NULL	30000	Using index; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	t3	index	a	a	5	NULL	30000	Using index
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
 1000
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 48cd7cc4d66..47c3131f944 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -852,7 +852,8 @@ 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	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	13	func	1	1.00	
+2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	2	100.00	Using index condition; Using MRR
 Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
@@ -1020,7 +1021,8 @@ FROM t1
 WHERE `varchar_nokey`  < 'n' XOR `pk`  )   ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	18	100.00	
-1	PRIMARY	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where; Materialize
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	8	func	1	1.00	
+2	SUBQUERY	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`))
 SELECT varchar_nokey