MWL#90: Subqueries: Inside-out execution for materialized non-sj subqueries

- Merge into 5.3-main

25 files changed, 2458 insertions, 1017 deletions

diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index e709db6887c..f1ee91916b9 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -1522,6 +1522,12 @@ INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
                  FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+--echo # MariaDB note:
+--echo #  This will show 2 for table which has 5 rows. 
+--echo #  This is because the access method employed is actually range access
+--echo #  which scans 2 records (yes, EXPLAIN displays it incorrectly).
+--echo #  our correct printing is an artifact of changing in select_describe()
+--echo #  from printing table->starts.records() to tab->records.
 EXPLAIN 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
                  FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
@@ -1535,6 +1541,7 @@ INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
                  FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+--echo # See above "MariaDB note"
 EXPLAIN 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
                  FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
@@ -1549,6 +1556,7 @@ INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
                  FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+--echo # See above "MariaDB note"
 EXPLAIN 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
                  FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 85569ad9d35..c71ce043a4d 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2255,7 +2255,8 @@ 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	t1_outer	index	NULL	a	10	NULL	15	Using where; Using index
+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	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
 EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
 a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result
index 2f9fb6b67f5..e7f4527e080 100644
--- a/mysql-test/r/metadata.result
+++ b/mysql-test/r/metadata.result
@@ -55,7 +55,7 @@ id	data	data
 2	female	no
 select t1.id from t1 union select t2.id from t2;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def				id	id	1	4	1	Y	32768	0	63
+def				id	id	1	4	1	Y	49152	0	63
 id
 1
 2
@@ -66,7 +66,7 @@ insert into t1 values (2,'two');
 set @arg00=1 ;
 select @arg00 FROM t1 where a=1 union distinct select 1 FROM t1 where a=1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def				@arg00	@arg00	8	20	1	Y	32768	0	63
+def				@arg00	@arg00	8	20	1	Y	49152	0	63
 @arg00
 1
 select * from (select @arg00) aaa;
@@ -76,7 +76,7 @@ def			aaa	@arg00	@arg00	8	20	1	Y	32768	0	63
 1
 select 1 union select 1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def				1	1	8	20	1	N	32769	0	63
+def				1	1	8	20	1	N	49153	0	63
 1
 1
 select * from (select 1 union select 1) aaa;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 3e983ecf333..a5e058190ab 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1361,7 +1361,7 @@ explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using index
 1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using index
-1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.61	Using index; FirstMatch(t2)
+1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.00	Using index; FirstMatch(t2)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`))
 insert into t1 values (3,31);
@@ -3418,7 +3418,8 @@ AAA	8
 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	t1	ALL	NULL	NULL	NULL	NULL	9	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	23	test.t1.a,test.t1.b	1	
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 ALTER TABLE t1 ADD INDEX(a);
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
@@ -3429,7 +3430,8 @@ AAA	8
 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	t1	ALL	a	NULL	NULL	NULL	9	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	23	test.t1.a,test.t1.b	1	
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 DROP TABLE t1;
 create table t1( f1 int,f2 int);
@@ -4323,16 +4325,18 @@ CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1),(2);
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
 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	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`))))))
+Note	1003	select 1 AS `1` from  <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`1` = 1)
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
 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	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
 Warnings:
-Note	1003	select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`))))))
+Note	1003	select 1 AS `1` from  <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`1` = 1)
 DROP TABLE t1;
 #
 # Bug#45061: Incorrectly market field caused wrong result.
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index efb7a417bb0..a77ea2164cd 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1022,7 +1022,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	8	Using temporary; Using filesort
+1	PRIMARY	<subquery2>	ALL	distinct_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
@@ -1040,7 +1040,7 @@ 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	subselect3	eq_ref	unique_key	unique_key	5	func	1	
+2	DEPENDENT SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_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
@@ -1163,7 +1163,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	ALL	distinct_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);
@@ -1212,29 +1212,33 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	ALL	distinct_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
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.A.a	1	
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	
 explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.t2.a	1	
 2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
 explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.t2.a	1	
 2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
 explain select straight_join * from t2 X, t2 Y 
 where X.a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.X.a	1	
 2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
 create table t0 (a int, b int);
@@ -1242,14 +1246,14 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	ALL	distinct_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	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	ALL	distinct_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;
@@ -1275,14 +1279,14 @@ 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	subselect2	eq_ref	unique_key	unique_key	10	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,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	subselect2	eq_ref	unique_key	unique_key	10	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	
 set @@optimizer_search_depth=@save_optimizer_search_depth;
 set @@optimizer_switch=default;
@@ -1321,7 +1325,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	15	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	15	func,func,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
@@ -1394,7 +1398,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	2	1.00	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.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	
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index d24c1baa814..140e26ffbd2 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -1026,7 +1026,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	8	Using temporary; Using filesort
+1	PRIMARY	<subquery2>	ALL	distinct_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
@@ -1035,7 +1035,6 @@ 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
 256	67	NULL
-256	67	NULL
 drop table t1, t11, t12, t21, t22;
 create table t1(a int);
 insert into t1 values (0),(1);
@@ -1045,7 +1044,7 @@ 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	subselect3	eq_ref	unique_key	unique_key	5	func	1	
+2	DEPENDENT SUBQUERY	<subquery3>	eq_ref	distinct_key	distinct_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
@@ -1168,7 +1167,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	ALL	distinct_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);
@@ -1217,29 +1216,33 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	ALL	distinct_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
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.A.a	1	
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	10	
 explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.t2.a	1	
 2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
 explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.t2.a	1	
 2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
 explain select straight_join * from t2 X, t2 Y 
 where X.a in (select straight_join A.a from t1 A, t1 B);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	test.X.a	1	
 2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
 create table t0 (a int, b int);
@@ -1247,14 +1250,14 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	ALL	distinct_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	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	ALL	distinct_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;
@@ -1280,14 +1283,14 @@ 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	subselect2	eq_ref	unique_key	unique_key	10	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,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	subselect2	eq_ref	unique_key	unique_key	10	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	100	
 set @@optimizer_search_depth=@save_optimizer_search_depth;
 set @@optimizer_switch=default;
@@ -1326,7 +1329,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	15	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	15	func,func,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
@@ -1399,7 +1402,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	2	1.00	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.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
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 00354d61110..70baef4d791 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -345,7 +345,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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
@@ -360,13 +360,13 @@ 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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	SIMPLE	<subquery2>	eq_ref	distinct_key	distinct_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;
@@ -383,7 +383,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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
@@ -398,13 +398,13 @@ 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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	SIMPLE	<subquery2>	eq_ref	distinct_key	distinct_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;
@@ -420,7 +420,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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
@@ -435,13 +435,13 @@ 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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	SIMPLE	<subquery2>	eq_ref	distinct_key	distinct_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;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 537129a4434..5a69dc2333b 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1,3 +1,5 @@
+set @@optimizer_switch='semijoin=off';
+set optimizer_switch='firstmatch=off';
 drop table if exists t1, t2, t3, t1i, t2i, t3i;
 drop view if exists v1, v2, v1m, v2m;
 create table t1 (a1 char(8), a2 char(8));
@@ -30,7 +32,6 @@ create index it3i3 on t3i (c1, c2);
 insert into t1i select * from t1;
 insert into t2i select * from t2;
 insert into t3i select * from t3;
-set @@optimizer_switch='semijoin=off';
 /******************************************************************************
 * Simple tests.
 ******************************************************************************/
@@ -82,8 +83,8 @@ a1	a2
 explain extended
 select * from t1i where a1 in (select b1 from t2i where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1i	index	NULL	it1i3	18	NULL	3	100.00	Using where; Using index
-2	SUBQUERY	t2i	index	it2i1,it2i3	it2i1	9	NULL	5	100.00	Using where; Using index
+1	PRIMARY	t1i	index	NULL	_it1_idx	#	NULL	3	100.00	Using where; 
+2	SUBQUERY	t2i	index	it2i1,it2i3	it2i1	#	NULL	5	100.00	Using where; 
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`>(<in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select `test`.`t2i`.`b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`))))))
 select * from t1i where a1 in (select b1 from t2i where b1 > '0');
@@ -93,8 +94,8 @@ a1	a2
 explain extended
 select * from t1i where a1 in (select 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	NULL	it1i3	18	NULL	3	100.00	Using where; Using index
-2	SUBQUERY	t2i	range	it2i1,it2i3	it2i1	9	NULL	3	100.00	Using where; Using index for group-by
+1	PRIMARY	t1i	index	NULL	#	18	#	3	100.00	#
+2	SUBQUERY	t2i	range	it2i1,it2i3	#	9	#	3	100.00	#
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`>(<in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select `test`.`t2i`.`b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`))))))
 select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
@@ -104,8 +105,8 @@ a1	a2
 explain extended
 select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1i	index	NULL	it1i3	18	NULL	3	100.00	Using where; Using index
-2	SUBQUERY	t2i	index	it2i1,it2i3	it2i3	18	NULL	5	100.00	Using where; Using index
+1	PRIMARY	t1i	index	NULL	_it1_idx	#	NULL	3	100.00	Using where; 
+2	SUBQUERY	t2i	index	it2i1,it2i3	it2i3	#	NULL	5	100.00	Using where; 
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a2`,`test`.`t1i`.`a1`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))))
 select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
@@ -115,8 +116,8 @@ a1	a2
 explain extended
 select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1i	index	NULL	it1i3	18	NULL	3	100.00	Using where; Using index
-2	SUBQUERY	t2i	range	it2i1,it2i3	it2i3	18	NULL	3	100.00	Using where; Using index for group-by
+1	PRIMARY	t1i	index	NULL	#	#	#	3	100.00	#
+2	SUBQUERY	t2i	range	it2i1,it2i3	#	#	#	3	100.00	#
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a2`,`test`.`t1i`.`a1`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))))
 select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
@@ -126,8 +127,8 @@ a1	a2
 explain extended
 select * from t1i 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	t1i	index	NULL	it1i3	18	NULL	3	100.00	Using where; Using index
-2	SUBQUERY	t2i	range	it2i1,it2i3	it2i3	18	NULL	3	100.00	Using where; Using index for group-by
+1	PRIMARY	t1i	index	NULL	#	#	#	3	100.00	#
+2	SUBQUERY	t2i	range	it2i1,it2i3	#	#	#	3	100.00	#
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a2`,`test`.`t1i`.`a1`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,min(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`min(b2)`))))))
 select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
@@ -176,6 +177,7 @@ a1	a2
 1 - 02	2 - 02
 select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
 ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch='default,semijoin=off';
 prepare st1 from
 "select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
@@ -202,7 +204,7 @@ execute st1;
 a1	a2
 1 - 01	2 - 01
 1 - 02	2 - 02
-set @@optimizer_switch='default,semijoin=off';
+set @@optimizer_switch=@save_optimizer_switch;
 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
@@ -289,10 +291,10 @@ where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
 (a1, a2) in (select c1, c2 from t3i
 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	t1i	index	NULL	it1i3	18	NULL	3	100.00	Using where; Using index
-3	SUBQUERY	t3i	index	NULL	it3i3	18	NULL	4	100.00	Using where; Using index
-4	SUBQUERY	t2i	index	it2i2	it2i3	18	NULL	5	100.00	Using where; Using index
-2	SUBQUERY	t2i	index	it2i1,it2i3	it2i3	18	NULL	5	100.00	Using where; Using index
+1	PRIMARY	t1i	index	NULL	#	#	#	3	100.00	#
+3	SUBQUERY	t3i	index	NULL	#	#	#	4	100.00	#
+4	SUBQUERY	t2i	index	it2i2	#	#	#	5	100.00	#
+2	SUBQUERY	t2i	index	it2i1,it2i3	#	#	#	5	100.00	#
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<expr_cache><`test`.`t1i`.`a2`,`test`.`t1i`.`a1`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))) and <expr_cache><`test`.`t1i`.`a2`,`test`.`t1i`.`a1`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t3i`.`c1`,`test`.`t3i`.`c2` from `test`.`t3i` where <expr_cache><`test`.`t3i`.`c2`,`test`.`t3i`.`c1`>(<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key where ((`test`.`t3i`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3i`.`c2` = `materialized subselect`.`b2`)))))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`c2`)))))))
 select * from t1i
@@ -366,17 +368,17 @@ where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
 (a1, a2) in (select c1, c2 from t3i
 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
-5	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-6	SUBQUERY	t2i	index	it2i2	it2i3	18	NULL	5	100.00	Using where; Using index
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
-4	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
-7	UNION	t1i	index	NULL	it1i3	18	NULL	3	100.00	Using where; Using index
-9	SUBQUERY	t3i	index	NULL	it3i3	18	NULL	4	100.00	Using where; Using index
-10	SUBQUERY	t2i	index	it2i2	it2i3	18	NULL	5	100.00	Using where; Using index
-8	SUBQUERY	t2i	index	it2i1,it2i3	it2i3	18	NULL	5	100.00	Using where; Using index
-NULL	UNION RESULT	<union1,7>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+1	PRIMARY	t1	ALL	NULL	#	#	#	3	100.00	#
+5	SUBQUERY	t3	ALL	NULL	#	#	#	4	100.00	#
+6	SUBQUERY	t2i	index	it2i2	#	#	#	5	100.00	#
+2	SUBQUERY	t2	ALL	NULL	#	#	#	5	100.00	#
+4	SUBQUERY	t3	ALL	NULL	#	#	#	4	100.00	#
+3	SUBQUERY	t3	ALL	NULL	#	#	#	4	100.00	#
+7	UNION	t1i	index	NULL	#	#	#	3	100.00	#
+9	SUBQUERY	t3i	index	NULL	#	#	#	4	100.00	#
+10	SUBQUERY	t2i	index	it2i2	#	#	#	5	100.00	#
+8	SUBQUERY	t2i	index	it2i1,it2i3	#	#	#	5	100.00	#
+NULL	UNION RESULT	<union1,7>	ALL	NULL	#	#	#	NULL	NULL	#
 Warnings:
 Note	1003	(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<expr_cache><`test`.`t1`.`a2`,`test`.`t1`.`a1`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`)))))) and <expr_cache><`test`.`t1`.`a2`,`test`.`t1`.`a1`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1`,`test`.`t3`.`c2` from `test`.`t3` where <expr_cache><`test`.`t3`.`c2`,`test`.`t3`.`c1`>(<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`)))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`)))))))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<expr_cache><`test`.`t1i`.`a2`,`test`.`t1i`.`a1`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))) and <expr_cache><`test`.`t1i`.`a2`,`test`.`t1i`.`a1`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t3i`.`c1`,`test`.`t3i`.`c2` from `test`.`t3i` where <expr_cache><`test`.`t3i`.`c2`,`test`.`t3i`.`c1`>(<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key where ((`test`.`t3i`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3i`.`c2` = `materialized subselect`.`b2`)))))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`c2`))))))))
 (select * from t1
@@ -549,7 +551,6 @@ a1	a2
 Test that BLOBs are not materialized (except when arguments of some functions).
 */
 # force materialization to be always considered
-set @@optimizer_switch='semijoin=off';
 set @prefix_len = 6;
 set @blob_len = 16;
 set @suffix_len = @blob_len - @prefix_len;
@@ -951,7 +952,6 @@ insert into t1bit values (b'010', b'110');
 insert into t2bit values (b'001', b'101');
 insert into t2bit values (b'010', b'110');
 insert into t2bit values (b'110', b'111');
-set @@optimizer_switch='semijoin=off';
 explain extended select bin(a1), bin(a2)
 from t1bit
 where (a1, a2) in (select b1, b2 from t2bit);
@@ -994,7 +994,7 @@ drop table t1, t2, t3, t1i, t2i, t3i, columns;
 /******************************************************************************
 * Test the cache of the left operand of IN.
 ******************************************************************************/
-set @@optimizer_switch='semijoin=off';
+# Test that default values of Cached_item are not used for comparison
 create table t1 (s1 int);
 create table t2 (s2 int);
 insert into t1 values (5),(1),(0);
@@ -1142,6 +1142,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 min(a1)
+set @save_optimizer_switch=@@optimizer_switch;
 set @@optimizer_switch='default,materialization=off';
 explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1157,17 +1158,25 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select min(a1) from t1 where 7 in (select b1 from t2);
 min(a1)
 set @@optimizer_switch='default,materialization=off';
+# with MariaDB and MWL#90, this particular case is solved:
 explain select min(a1) from t1 where 7 in (select b1 from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 select min(a1) from t1 where 7 in (select b1 from t2);
 min(a1)
 NULL
+# but when we go around MWL#90 code, the problem still shows up:
+explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+min(a1)
+set @@optimizer_switch= @save_optimizer_switch;
 drop table t1,t2;
 create table t1 (a char(2), b varchar(10));
 insert into t1 values ('a',  'aaa');
 insert into t1 values ('aa', 'aaaa');
-set @@optimizer_switch='default,semijoin=off';
 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	Using where
@@ -1187,15 +1196,17 @@ INSERT INTO t1 (f1, f2) VALUES (10, 1.668);
 CREATE TABLE t2 LIKE t1;
 INSERT INTO t2 VALUES (1, 1.789);
 INSERT INTO t2 VALUES (13, 1.454);
+set @save_optimizer_switch=@@optimizer_switch;
 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	subselect2	ALL	unique_key	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	ALL	distinct_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
+set @@optimizer_switch= @save_optimizer_switch;
 DROP TABLE t1, t2;
 CREATE TABLE t1 (
 pk int,
@@ -1208,11 +1219,12 @@ PRIMARY KEY (pk)
 INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
 CREATE TABLE t2 LIKE t1;
 INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+set @save_optimizer_switch=@@optimizer_switch;
 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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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
@@ -1221,6 +1233,7 @@ SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
 pk
 2
 DROP TABLE t1, t2;
+set optimizer_switch=@save_optimizer_switch;
 #
 # BUG#50019: Wrong result for IN-subquery with materialization
 #
@@ -1272,6 +1285,8 @@ a	a in (select a from t1)
 1	0
 2	0
 drop table t0, t1;
+set optimizer_switch='firstmatch=on';
+set @@optimizer_switch=default;
 #
 # LPBUG#609121: RQG: wrong result on aggregate + NOT IN + HAVING and
 # partial_match_table_scan=on
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 2601e79e37e..64945b0becc 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -1365,7 +1365,7 @@ explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using index
 1	PRIMARY	t3	index	a	a	5	NULL	3	100.00	Using index
-1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.61	Using index; FirstMatch(t2)
+1	PRIMARY	t1	ref	a	a	10	test.t2.a,test.t3.a	116	100.00	Using index; FirstMatch(t2)
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` = `test`.`t3`.`a`))
 insert into t1 values (3,31);
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 02ca5a17830..ed278e3c1a4 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -809,7 +809,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	subselect2	eq_ref	unique_key	unique_key	13	func	1	1.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	13	func,func	1	100.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))
@@ -978,7 +978,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	8	func	1	1.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.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`))
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index f17edf3be6c..401a8e740d0 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -32,7 +32,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	3	
+1	PRIMARY	<subquery2>	ALL	distinct_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);
@@ -74,7 +74,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	ALL	distinct_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;
@@ -101,7 +101,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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
 2	SUBQUERY	t2	index	b	b	5	NULL	10	Using index
 select * from t1;
 a	b
@@ -129,7 +129,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
+1	PRIMARY	<subquery2>	ALL	distinct_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 
@@ -163,7 +163,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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
 2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	32	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -197,7 +197,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
+1	PRIMARY	<subquery2>	ALL	distinct_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 
@@ -231,7 +231,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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
 2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	52	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -348,7 +348,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	3	func	1	
 2	SUBQUERY	t2	ALL	CountryCode	NULL	NULL	NULL	545	Using where
 SELECT Name FROM t1 
 WHERE t1.Code IN (
@@ -359,6 +359,10 @@ Canada
 China
 Czech Republic
 drop table t1, t2;
+drop procedure if exists p1;
+drop procedure if exists p2;
+drop procedure if exists p3;
+drop procedure if exists p4;
 CREATE TABLE t1(a INT);
 CREATE TABLE t2(c INT);
 CREATE PROCEDURE p1(v1 int)
@@ -692,7 +696,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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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)
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 67215d1715e..94fe7f6d821 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -36,7 +36,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	3	
+1	PRIMARY	<subquery2>	ALL	distinct_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);
@@ -78,7 +78,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	10	
+1	PRIMARY	<subquery2>	ALL	distinct_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;
@@ -105,7 +105,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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
 2	SUBQUERY	t2	index	b	b	5	NULL	10	Using index
 select * from t1;
 a	b
@@ -133,7 +133,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
+1	PRIMARY	<subquery2>	ALL	distinct_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 
@@ -167,7 +167,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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
 2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	32	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -201,7 +201,7 @@ 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	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
+1	PRIMARY	<subquery2>	ALL	distinct_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 
@@ -235,7 +235,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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
 2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	52	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) 
@@ -352,7 +352,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	3	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	3	func	1	
 2	SUBQUERY	t2	ALL	CountryCode	NULL	NULL	NULL	545	Using where
 SELECT Name FROM t1 
 WHERE t1.Code IN (
@@ -363,6 +363,10 @@ Canada
 China
 Czech Republic
 drop table t1, t2;
+drop procedure if exists p1;
+drop procedure if exists p2;
+drop procedure if exists p3;
+drop procedure if exists p4;
 CREATE TABLE t1(a INT);
 CREATE TABLE t2(c INT);
 CREATE PROCEDURE p1(v1 int)
@@ -698,7 +702,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	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_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)
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 9c60424740a..0f3315f83f9 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -813,7 +813,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	subselect2	eq_ref	unique_key	unique_key	13	func	1	1.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	13	func,func	1	100.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))
@@ -982,7 +982,7 @@ 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	subselect2	eq_ref	unique_key	unique_key	8	func	1	1.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.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`))
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
new file mode 100644
index 00000000000..53150d33204
--- /dev/null
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -0,0 +1,1326 @@
+set optimizer_switch='firstmatch=off';
+drop table if exists t1, t2, t3, t1i, t2i, t3i;
+drop view if exists v1, v2, v1m, v2m;
+create table t1 (a1 char(8), a2 char(8));
+create table t2 (b1 char(8), b2 char(8));
+create table t3 (c1 char(8), c2 char(8));
+insert into t1 values ('1 - 00', '2 - 00');
+insert into t1 values ('1 - 01', '2 - 01');
+insert into t1 values ('1 - 02', '2 - 02');
+insert into t2 values ('1 - 01', '2 - 01');
+insert into t2 values ('1 - 01', '2 - 01');
+insert into t2 values ('1 - 02', '2 - 02');
+insert into t2 values ('1 - 02', '2 - 02');
+insert into t2 values ('1 - 03', '2 - 03');
+insert into t3 values ('1 - 01', '2 - 01');
+insert into t3 values ('1 - 02', '2 - 02');
+insert into t3 values ('1 - 03', '2 - 03');
+insert into t3 values ('1 - 04', '2 - 04');
+create table t1i (a1 char(8), a2 char(8));
+create table t2i (b1 char(8), b2 char(8));
+create table t3i (c1 char(8), c2 char(8));
+create index it1i1 on t1i (a1);
+create index it1i2 on t1i (a2);
+create index it1i3 on t1i (a1, a2);
+create index it2i1 on t2i (b1);
+create index it2i2 on t2i (b2);
+create index it2i3 on t2i (b1, b2);
+create index it3i1 on t3i (c1);
+create index it3i2 on t3i (c2);
+create index it3i3 on t3i (c1, c2);
+insert into t1i select * from t1;
+insert into t2i select * from t2;
+insert into t3i select * from t3;
+/******************************************************************************
+* Simple tests.
+******************************************************************************/
+# non-indexed nullable fields
+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	
+2	SUBQUERY	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'))
+select * from t1 where a1 in (select b1 from t2 where b1 > '0');
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+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	test.t1.a1	1	100.00	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from  <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where (`<subquery2>`.`b1` = `test`.`t1`.`a1`)
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+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	test.t1.a1,test.t1.a2	1	100.00	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
+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` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`<subquery2>`.`b2` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`))
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using temporary; Using filesort
+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`))
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+select * from t1i where a1 in (select b1 from t2i where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2i	index	it2i1,it2i3	it2i1	#	NULL	5	40.00	Using where; Using index; LooseScan
+1	PRIMARY	t1i	ref	_it1_idx	_it1_idx	#	_ref_	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
+select * from t1i where a1 in (select b1 from t2i where b1 > '0');
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+select * from t1i where a1 in (select 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	<subquery2>	ALL	distinct_key	#	NULL	#	3	100.00	#
+1	PRIMARY	t1i	ref	it1i1,it1i3	#	9	#	1	100.00	#
+2	SUBQUERY	t2i	range	it2i1,it2i3	#	9	#	3	100.00	#
+Warnings:
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select `test`.`t2i`.`b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `<subquery2>`.`b1`)
+select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2i	index	it2i1,it2i2,it2i3	it2i3	#	NULL	5	40.00	Using where; Using index; LooseScan
+1	PRIMARY	t1i	ref	_it1_idx	_it1_idx	#	_ref_	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0'))
+select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1i	index	it1i1,it1i2,it1i3	#	#	#	3	100.00	#
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	#	#	1	100.00	#
+2	SUBQUERY	t2i	range	it2i1,it2i3	#	#	#	3	100.00	#
+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` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`<subquery2>`.`b2` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`))
+select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+select * from t1i 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	t1i	index	it1i1,it1i2,it1i3	#	#	#	3	100.00	#
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	#	#	1	100.00	#
+2	SUBQUERY	t2i	range	it2i1,it2i3	#	#	#	3	100.00	#
+Warnings:
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`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`.`t1i` where ((`<subquery2>`.`min(b2)` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`))
+select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
+2	SUBQUERY	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`))
+select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
+execute st1;
+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	18	test.t1.a1,test.t1.a2	1	
+2	SUBQUERY	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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	
+2	SUBQUERY	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;
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+execute st2;
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
+2	SUBQUERY	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`))
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
+ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='default,semijoin=off';
+prepare st1 from
+"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
+set @@optimizer_switch='default,materialization=off';
+execute st1;
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+set @@optimizer_switch='default,semijoin=off';
+execute st1;
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+set @@optimizer_switch='default,materialization=off';
+prepare st1 from
+"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
+set @@optimizer_switch='default,semijoin=off';
+execute st1;
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+set @@optimizer_switch='default,materialization=off';
+execute st1;
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+set @@optimizer_switch=@save_optimizer_switch;
+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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1.a1,test.t1.a2	1	100.00	
+2	SUBQUERY	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`))
+select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+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 index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	18	test.t1i.a1,test.t1i.a2	1	100.00	
+2	SUBQUERY	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`))
+select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+/******************************************************************************
+* Views, UNIONs, several levels of nesting.
+******************************************************************************/
+# materialize the result of subquery over temp-table view
+create algorithm=merge view v1 as
+select b1, c2 from t2, t3 where b2 > c2;
+create algorithm=merge view v2 as
+select b1, c2 from t2, t3 group by b2, c2;
+Warnings:
+Warning	1354	View merge algorithm can't be used here for now (assumed undefined algorithm)
+create algorithm=temptable view v1m as
+select b1, c2 from t2, t3 where b2 > c2;
+create algorithm=temptable view v2m as
+select b1, c2 from t2, t3 group by b2, c2;
+select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null);
+b1	c2
+1 - 02	2 - 01
+1 - 02	2 - 01
+1 - 03	2 - 01
+1 - 03	2 - 02
+select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null);
+b1	c2
+1 - 02	2 - 01
+1 - 02	2 - 01
+1 - 03	2 - 01
+1 - 03	2 - 02
+select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null);
+b1	c2
+1 - 02	2 - 01
+1 - 02	2 - 01
+1 - 03	2 - 01
+1 - 03	2 - 02
+select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null);
+b1	c2
+1 - 02	2 - 01
+1 - 02	2 - 01
+1 - 03	2 - 01
+1 - 03	2 - 02
+drop view v1, v2, v1m, v2m;
+explain extended
+select * from t1
+where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
+(a1, a2) in (select c1, c2 from t3
+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	<subquery2>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (`test`.`t2`.`b1` > '0') and (`test`.`t3`.`c2` > '0'))
+select * from t1
+where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
+(a1, a2) in (select c1, c2 from t3
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+select * from t1i
+where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
+(a1, a2) in (select c1, c2 from t3i
+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	t1i	index	it1i1,it1i2,it1i3	#	#	#	3	100.00	#
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	#	#	1	100.00	#
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	#	#	#	1	100.00	#
+2	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	#	#	#	5	100.00	#
+3	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	#	#	#	5	100.00	#
+3	SUBQUERY	t3i	index	it3i1,it3i2,it3i3	#	#	#	4	75.00	#
+Warnings:
+Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))
+select * from t1i
+where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
+(a1, a2) in (select c1, c2 from t3i
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+where b2 in (select c2 from t3 where c2 LIKE '%02') or
+b2 in (select c2 from t3 where c2 LIKE '%03')) and
+(a1, a2) in (select c1, c2 from t3
+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	<subquery2>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+5	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+5	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer
+4	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	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`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`)))))) and (`test`.`t3`.`c2` > '0'))
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+where b2 in (select c2 from t3 where c2 LIKE '%02') or
+b2 in (select c2 from t3 where c2 LIKE '%03')) and
+(a1, a2) in (select c1, c2 from t3
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+a1	a2
+1 - 02	2 - 02
+explain extended
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+where b2 in (select c2 from t3 t3a where c1 = a1) or
+b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
+(a1, a2) in (select c1, c2 from t3 t3c
+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	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; End temporary; Using join buffer
+1	PRIMARY	<subquery5>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+5	SUBQUERY	t3c	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+5	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer
+4	SUBQUERY	t3b	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	DEPENDENT SUBQUERY	t3a	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t3c`.`c2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3c`.`c1`) and (<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`)))))) and (`test`.`t3c`.`c2` > '0'))
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+where b2 in (select c2 from t3 t3a where c1 = a1) or
+b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
+(a1, a2) in (select c1, c2 from t3 t3c
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+(select * from t1
+where (a1, a2) in (select b1, b2 from t2
+where b2 in (select c2 from t3 where c2 LIKE '%02') or
+b2 in (select c2 from t3 where c2 LIKE '%03')
+group by b1, b2) and
+(a1, a2) in (select c1, c2 from t3
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
+UNION
+(select * from t1i
+where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
+(a1, a2) in (select c1, c2 from t3i
+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	<subquery2>	ALL	distinct_key	#	#	#	5	0.00	#
+1	PRIMARY	t3	ALL	NULL	#	#	#	4	100.00	#
+1	PRIMARY	t1	ALL	NULL	#	#	#	3	100.00	#
+1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	#	#	#	2	100.00	#
+2	SUBQUERY	t2	ALL	NULL	#	#	#	5	100.00	#
+4	SUBQUERY	t3	ALL	NULL	#	#	#	4	100.00	#
+3	SUBQUERY	t3	ALL	NULL	#	#	#	4	100.00	#
+7	UNION	t1i	index	it1i1,it1i2,it1i3	#	#	#	3	100.00	#
+7	UNION	<subquery8>	eq_ref	distinct_key	#	#	#	1	100.00	#
+7	UNION	<subquery9>	eq_ref	distinct_key	#	#	#	1	100.00	#
+8	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	#	#	#	5	100.00	#
+9	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	#	#	#	5	100.00	#
+9	SUBQUERY	t3i	index	it3i1,it3i2,it3i3	#	#	#	4	75.00	#
+NULL	UNION RESULT	<union1,7>	ALL	NULL	#	#	#	NULL	NULL	#
+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` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t1` where ((`test`.`t3`.`c2` = `<subquery2>`.`b2`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`) and (`test`.`t2i`.`b2` = `<subquery2>`.`b2`) and (`test`.`t3`.`c1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t2i`.`b1` = `<subquery2>`.`b1`) and (`<subquery2>`.`b2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0')))
+(select * from t1
+where (a1, a2) in (select b1, b2 from t2
+where b2 in (select c2 from t3 where c2 LIKE '%02') or
+b2 in (select c2 from t3 where c2 LIKE '%03')
+group by b1, b2) and
+(a1, a2) in (select c1, c2 from t3
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
+UNION
+(select * from t1i
+where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
+(a1, a2) in (select c1, c2 from t3i
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
+a1	a2
+1 - 02	2 - 02
+1 - 01	2 - 01
+explain extended
+select * from t1
+where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
+(a1, a2) in (select c1, c2 from t3
+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	
+4	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+4	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and (`test`.`t3`.`c2` > '0'))
+select * from t1
+where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
+(a1, a2) in (select c1, c2 from t3
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+a1	a2
+1 - 01	2 - 01
+1 - 02	2 - 02
+explain extended
+select * from t1, t3
+where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
+(c1, c2) in (select c1, c2 from t3
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
+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
+1	PRIMARY	<subquery4>	eq_ref	distinct_key	distinct_key	18	func,func	1	100.00	
+4	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+4	SUBQUERY	t2i	index	it2i1,it2i2,it2i3	it2i3	18	NULL	5	80.00	Using where; Using index; Using join buffer
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t3` where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1`,`test`.`t1`.`a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and (`test`.`t3`.`c2` > '0'))
+select * from t1, t3
+where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
+(c1, c2) in (select c1, c2 from t3
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
+a1 = c1;
+a1	a2	c1	c2
+1 - 01	2 - 01	1 - 01	2 - 01
+1 - 02	2 - 02	1 - 02	2 - 02
+/******************************************************************************
+* Negative tests, where materialization should not be applied.
+******************************************************************************/
+# UNION in a subquery
+explain extended
+select * from t3
+where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+3	DEPENDENT UNION	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`c1`,<exists>(select 1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`a1`)) union select 1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2`.`b1`))))
+select * from t3
+where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
+c1	c2
+1 - 01	2 - 01
+1 - 02	2 - 02
+1 - 03	2 - 03
+explain extended
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+where b2 in (select c2 from t3 t3a where c1 = a1) or
+b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
+(a1, a2) in (select c1, c2 from t3 t3c
+where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
+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	Start temporary
+1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	it2i3	18	test.t1.a1,test.t1.a2	2	100.00	Using index
+1	PRIMARY	t3c	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; End temporary; Using join buffer
+4	SUBQUERY	t3b	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	DEPENDENT SUBQUERY	t3a	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
+Note	1276	Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`)))))))
+explain extended
+select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
+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
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01'))))
+select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
+a1	a2
+1 - 01	2 - 01
+explain extended
+select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
+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
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select '1 - 01','2 - 01' having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01'))))
+select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
+a1	a2
+1 - 01	2 - 01
+/******************************************************************************
+* Subqueries in other uncovered clauses.
+******************************************************************************/
+/* SELECT clause */
+select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
+((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL
+0
+0
+0
+/* GROUP BY clause */
+create table columns (col int key);
+insert into columns values (1), (2);
+explain extended
+select * from t1 group by (select col from columns limit 1);
+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	
+2	SUBQUERY	columns	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by (select `test`.`columns`.`col` from `test`.`columns` limit 1)
+select * from t1 group by (select col from columns limit 1);
+a1	a2
+1 - 00	2 - 00
+explain extended
+select * from t1 group by (a1 in (select col from columns));
+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 temporary; Using filesort
+2	DEPENDENT SUBQUERY	columns	unique_subquery	PRIMARY	PRIMARY	4	func	1	100.00	Using index; Using where; Full scan on NULL key
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where trigcond((<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`)))))
+select * from t1 group by (a1 in (select col from columns));
+a1	a2
+1 - 00	2 - 00
+/* ORDER BY clause */
+explain extended
+select * from t1 order by (select col from columns limit 1);
+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	
+2	SUBQUERY	columns	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` order by (select `test`.`columns`.`col` from `test`.`columns` limit 1)
+select * from t1 order by (select col from columns limit 1);
+a1	a2
+1 - 00	2 - 00
+1 - 01	2 - 01
+1 - 02	2 - 02
+/******************************************************************************
+* Column types/sizes that affect materialization.
+******************************************************************************/
+/*
+Test that BLOBs are not materialized (except when arguments of some functions).
+*/
+# force materialization to be always considered
+set @prefix_len = 6;
+set @blob_len = 16;
+set @suffix_len = @blob_len - @prefix_len;
+create table t1_16 (a1 blob(16), a2 blob(16));
+create table t2_16 (b1 blob(16), b2 blob(16));
+create table t3_16 (c1 blob(16), c2 blob(16));
+insert into t1_16 values
+(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
+insert into t1_16 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t1_16 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_16 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t2_16 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_16 values
+(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_16 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t3_16 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t3_16 values
+(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_16 values
+(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
+explain extended select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select b1 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	Using where; Start temporary
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+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` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select b1 from t2_16 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,7)
+from t1_16
+where (a1,a2) in (select b1, b2 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	Using where; Start temporary
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+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`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+select left(a1,7), left(a2,7)
+from t1_16
+where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,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
+2	SUBQUERY	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)))
+select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,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
+2	DEPENDENT SUBQUERY	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 `test`.`t1_16` where <in_optimizer>(`test`.`t1_16`.`a1`,<exists>(select group_concat(`test`.`t2_16`.`b1` separator ',') from `test`.`t2_16` group by `test`.`t2_16`.`b2` having (<cache>(`test`.`t1_16`.`a1`) = <ref_null_helper>(group_concat(`test`.`t2_16`.`b1` separator ',')))))
+select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select group_concat(b1) from t2_16 group by b2);
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+set @@group_concat_max_len = 256;
+explain extended select left(a1,7), left(a2,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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_16.a1	1	100.00	Using where
+2	SUBQUERY	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)`)
+select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select group_concat(b1) from t2_16 group by b2);
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended
+select * from t1
+where concat(a1,'x') IN
+(select left(a1,8) from t1_16
+where (a1, a2) IN
+(select t2_16.b1, t2_16.b2 from t2_16, t2
+where t2.b2 = substring(t2_16.b2,1,6) and
+t2.b1 IN (select c1 from t3 where c2 > '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	Start temporary
+1	PRIMARY	t1_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
+1	PRIMARY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; End temporary; Using join buffer
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2_16` join `test`.`t2` join `test`.`t1_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2`.`b1` = `test`.`t3`.`c1`) and (`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and (`test`.`t3`.`c2` > '0') and (concat(`test`.`t1`.`a1`,'x') = left(`test`.`t1_16`.`a1`,8)))
+drop table t1_16, t2_16, t3_16;
+set @blob_len = 512;
+set @suffix_len = @blob_len - @prefix_len;
+create table t1_512 (a1 blob(512), a2 blob(512));
+create table t2_512 (b1 blob(512), b2 blob(512));
+create table t3_512 (c1 blob(512), c2 blob(512));
+insert into t1_512 values
+(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
+insert into t1_512 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t1_512 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_512 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t2_512 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_512 values
+(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_512 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t3_512 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t3_512 values
+(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_512 values
+(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
+explain extended select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select b1 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	Using where; Start temporary
+1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+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` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select b1 from t2_512 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,7)
+from t1_512
+where (a1,a2) in (select b1, b2 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	Using where; Start temporary
+1	PRIMARY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+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`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+select left(a1,7), left(a2,7)
+from t1_512
+where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,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
+2	SUBQUERY	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)))
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_512.a1	1	100.00	Using where
+2	SUBQUERY	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)`)
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+left(a1,7)	left(a2,7)
+set @@group_concat_max_len = 256;
+explain extended select left(a1,7), left(a2,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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_512.a1	1	100.00	Using where
+2	SUBQUERY	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)`)
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+left(a1,7)	left(a2,7)
+drop table t1_512, t2_512, t3_512;
+set @blob_len = 1024;
+set @suffix_len = @blob_len - @prefix_len;
+create table t1_1024 (a1 blob(1024), a2 blob(1024));
+create table t2_1024 (b1 blob(1024), b2 blob(1024));
+create table t3_1024 (c1 blob(1024), c2 blob(1024));
+insert into t1_1024 values
+(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
+insert into t1_1024 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t1_1024 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_1024 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t2_1024 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_1024 values
+(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_1024 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t3_1024 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t3_1024 values
+(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_1024 values
+(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
+explain extended select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select b1 from t2_1024 where b1 > '0');
+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; Start temporary
+1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+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 `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select b1 from t2_1024 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,7)
+from t1_1024
+where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
+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; Start temporary
+1	PRIMARY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+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 `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+select left(a1,7), left(a2,7)
+from t1_1024
+where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
+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	
+1	PRIMARY	<subquery2>	eq_ref	NULL	distinct_key	15	func,func	1	100.00	Using where
+2	SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+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 `test`.`t1_1024` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` > '0') and (`test`.`t1_1024`.`a1` = substr(`test`.`t2_1024`.`b1`,1,1024)))
+select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
+left(a1,7)	left(a2,7)
+explain extended select left(a1,7), left(a2,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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_1024.a1	1	100.00	Using where
+2	SUBQUERY	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)`)
+select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select group_concat(b1) from t2_1024 group by b2);
+left(a1,7)	left(a2,7)
+set @@group_concat_max_len = 256;
+explain extended select left(a1,7), left(a2,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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_1024.a1	1	100.00	Using where
+2	SUBQUERY	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)`)
+select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select group_concat(b1) from t2_1024 group by b2);
+left(a1,7)	left(a2,7)
+drop table t1_1024, t2_1024, t3_1024;
+set @blob_len = 1025;
+set @suffix_len = @blob_len - @prefix_len;
+create table t1_1025 (a1 blob(1025), a2 blob(1025));
+create table t2_1025 (b1 blob(1025), b2 blob(1025));
+create table t3_1025 (c1 blob(1025), c2 blob(1025));
+insert into t1_1025 values
+(concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
+insert into t1_1025 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t1_1025 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_1025 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t2_1025 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_1025 values
+(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_1025 values
+(concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t3_1025 values
+(concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t3_1025 values
+(concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_1025 values
+(concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
+explain extended select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select b1 from t2_1025 where b1 > '0');
+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; Start temporary
+1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+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 `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select b1 from t2_1025 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,7)
+from t1_1025
+where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
+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; Start temporary
+1	PRIMARY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+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 `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+select left(a1,7), left(a2,7)
+from t1_1025
+where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
+left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
+explain extended select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
+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	
+1	PRIMARY	<subquery2>	eq_ref	NULL	distinct_key	15	func,func	1	100.00	Using where
+2	SUBQUERY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+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 `test`.`t1_1025` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` > '0') and (`test`.`t1_1025`.`a1` = substr(`test`.`t2_1025`.`b1`,1,1025)))
+select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
+left(a1,7)	left(a2,7)
+explain extended select left(a1,7), left(a2,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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_1025.a1	1	100.00	Using where
+2	SUBQUERY	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)`)
+select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select group_concat(b1) from t2_1025 group by b2);
+left(a1,7)	left(a2,7)
+set @@group_concat_max_len = 256;
+explain extended select left(a1,7), left(a2,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	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	261	test.t1_1025.a1	1	100.00	Using where
+2	SUBQUERY	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)`)
+select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select group_concat(b1) from t2_1025 group by b2);
+left(a1,7)	left(a2,7)
+drop table t1_1025, t2_1025, t3_1025;
+create table t1bit (a1 bit(3), a2 bit(3));
+create table t2bit (b1 bit(3), b2 bit(3));
+insert into t1bit values (b'000', b'100');
+insert into t1bit values (b'001', b'101');
+insert into t1bit values (b'010', b'110');
+insert into t2bit values (b'001', b'101');
+insert into t2bit values (b'010', b'110');
+insert into t2bit values (b'110', b'111');
+explain extended select bin(a1), bin(a2)
+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	
+2	SUBQUERY	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
+select bin(a1), bin(a2)
+from t1bit
+where (a1, a2) in (select b1, b2 from t2bit);
+bin(a1)	bin(a2)
+1	101
+10	110
+drop table t1bit, t2bit;
+create table t1bb (a1 bit(3), a2 blob(3));
+create table t2bb (b1 bit(3), b2 blob(3));
+insert into t1bb values (b'000', '100');
+insert into t1bb values (b'001', '101');
+insert into t1bb values (b'010', '110');
+insert into t2bb values (b'001', '101');
+insert into t2bb values (b'010', '110');
+insert into t2bb values (b'110', '111');
+explain extended select bin(a1), a2
+from t1bb
+where (a1, a2) in (select b1, b2 from t2bb);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1bb	ALL	NULL	NULL	NULL	NULL	3	100.00	Start temporary
+1	PRIMARY	t2bb	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; End temporary; Using join buffer
+Warnings:
+Note	1003	select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` semi join (`test`.`t2bb`) where ((`test`.`t2bb`.`b2` = `test`.`t1bb`.`a2`) and (`test`.`t2bb`.`b1` = `test`.`t1bb`.`a1`))
+select bin(a1), a2
+from t1bb
+where (a1, a2) in (select b1, b2 from t2bb);
+bin(a1)	a2
+1	101
+10	110
+drop table t1bb, t2bb;
+drop table t1, t2, t3, t1i, t2i, t3i, columns;
+/******************************************************************************
+* Test the cache of the left operand of IN.
+******************************************************************************/
+# Test that default values of Cached_item are not used for comparison
+create table t1 (s1 int);
+create table t2 (s2 int);
+insert into t1 values (5),(1),(0);
+insert into t2 values (0), (1);
+select s2 from t2 where s2 in (select s1 from t1);
+s2
+0
+1
+drop table t1, t2;
+create table t1 (a int not null, b int not null);
+create table t2 (c int not null, d int not null);
+create table t3 (e int not null);
+insert into t1 values (1,10);
+insert into t1 values (1,20);
+insert into t1 values (2,10);
+insert into t1 values (2,20);
+insert into t1 values (2,30);
+insert into t1 values (3,20);
+insert into t1 values (4,40);
+insert into t2 values (2,10);
+insert into t2 values (2,20);
+insert into t2 values (2,40);
+insert into t2 values (3,20);
+insert into t2 values (4,10);
+insert into t2 values (5,10);
+insert into t3 values (10);
+insert into t3 values (10);
+insert into t3 values (20);
+insert into t3 values (30);
+explain extended
+select a from t1 where a in (select c from t2 where d >= 20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; Using join buffer
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`d` >= 20))
+select a from t1 where a in (select c from t2 where d >= 20);
+a
+2
+2
+2
+3
+create index it1a on t1(a);
+explain extended
+select a from t1 where a in (select c from t2 where d >= 20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	t1	ref	it1a	it1a	4	test.t2.c	2	100.00	Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`d` >= 20))
+select a from t1 where a in (select c from t2 where d >= 20);
+a
+2
+2
+2
+3
+insert into t2 values (1,10);
+explain extended
+select a from t1 where a in (select c from t2 where d >= 20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	index	it1a	it1a	4	NULL	7	100.00	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` >= 20))
+select a from t1 where a in (select c from t2 where d >= 20);
+a
+2
+2
+2
+3
+explain extended
+select a from t1 group by a having a in (select c from t2 where d >= 20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	index	NULL	it1a	4	NULL	7	100.00	Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`c`)))))
+select a from t1 group by a having a in (select c from t2 where d >= 20);
+a
+2
+3
+create index iab on t1(a, b);
+explain extended
+select a from t1 group by a having a in (select c from t2 where d >= 20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	index	NULL	it1a	4	NULL	7	100.00	Using index
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`c`)))))
+select a from t1 group by a having a in (select c from t2 where d >= 20);
+a
+2
+3
+explain extended
+select a from t1 group by a
+having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	index	NULL	iab	8	NULL	7	100.00	Using index
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where
+3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having <in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` where (<nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where (max(`test`.`t1`.`b`) = `test`.`t3`.`e`) having (<cache>(`test`.`t2`.`d`) >= <ref_null_helper>(`test`.`t3`.`e`))))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
+select a from t1 group by a
+having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
+a
+2
+3
+explain extended
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	100.00	Start temporary
+1	PRIMARY	t1	ref	it1a,iab	iab	4	test.t2.c	1	100.00	Using where; Using index; End temporary
+3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+a
+2
+2
+2
+3
+1
+drop table t1, t2, t3;
+create table t2 (a int, b int, key(a), key(b));
+insert into t2 values (3,3),(3,3),(3,3);
+select 1 from t2 where  
+t2.a > 1 
+or 
+t2.a = 3 and not t2.a not in (select t2.b from t2);
+1
+1
+1
+1
+drop table t2;
+create table t1 (a1 int key);
+create table t2 (b1 int);
+insert into t1 values (5);
+explain select min(a1) from t1 where 7 in (select 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	
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+min(a1)
+NULL
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='default,materialization=off';
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+min(a1)
+set @@optimizer_switch='default,semijoin=off';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+select min(a1) from t1 where 7 in (select b1 from t2);
+min(a1)
+set @@optimizer_switch='default,materialization=off';
+# with MariaDB and MWL#90, this particular case is solved:
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select min(a1) from t1 where 7 in (select b1 from t2);
+min(a1)
+NULL
+# but when we go around MWL#90 code, the problem still shows up:
+explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+min(a1)
+set @@optimizer_switch= @save_optimizer_switch;
+drop table t1,t2;
+create table t1 (a char(2), b varchar(10));
+insert into t1 values ('a',  'aaa');
+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
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+select a,b from t1 where b in (select a from t1);
+a	b
+prepare st1 from "select a,b from t1 where b in (select a from t1)";
+execute st1;
+a	b
+execute st1;
+a	b
+drop table t1;
+CREATE TABLE t1 (f1 INT, f2 DECIMAL(5,3)) ENGINE=MyISAM;
+INSERT INTO t1 (f1, f2) VALUES (1, 1.789);
+INSERT INTO t1 (f1, f2) VALUES (13, 1.454);
+INSERT INTO t1 (f1, f2) VALUES (10, 1.668);
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1, 1.789);
+INSERT INTO t2 VALUES (13, 1.454);
+set @save_optimizer_switch=@@optimizer_switch;
+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	<subquery2>	ALL	distinct_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
+set @@optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+pk int,
+a varchar(1),
+b varchar(4),
+c varchar(4),
+d varchar(4),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+set @save_optimizer_switch=@@optimizer_switch;
+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	
+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
+SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
+pk
+2
+DROP TABLE t1, t2;
+set optimizer_switch=@save_optimizer_switch;
+#
+# BUG#50019: Wrong result for IN-subquery with materialization
+#
+create table t1(i int);
+insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+create table t2(i int);
+insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+create table t3(i int);
+insert into t3 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+set @save_optimizer_switch=@@optimizer_switch;
+set session optimizer_switch='materialization=off';
+select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+set session optimizer_switch=@save_optimizer_switch;
+drop table t1, t2, t3;
+create table t0 (a int);
+insert into t0 values (0),(1),(2);
+create table t1 (a int);
+insert into t1 values (0),(1),(2);
+explain select a, a in (select a from t1) from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	3	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
+select a, a in (select a from t1) from t0;
+a	a in (select a from t1)
+0	1
+1	1
+2	1
+prepare s from 'select a, a in (select a from t1) from t0';
+execute s;
+a	a in (select a from t1)
+0	1
+1	1
+2	1
+update t1 set a=123;
+execute s;
+a	a in (select a from t1)
+0	0
+1	0
+2	0
+drop table t0, t1;
+set optimizer_switch='firstmatch=on';
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 29c3fc4d901..876a759b2a3 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -303,7 +303,7 @@ a+1
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 drop view v1;
 drop table t1;
 create table t1 (a int);
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index 12b1ce97df4..2665a3871c5 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -1727,12 +1727,18 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 1
 1
+# MariaDB note:
+#  This will show 2 for table which has 5 rows. 
+#  This is because the access method employed is actually range access
+#  which scans 2 records (yes, EXPLAIN displays it incorrectly).
+#  our correct printing is an artifact of changing in select_describe()
+#  from printing table->starts.records() to tab->records.
 EXPLAIN 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	index	c3,c2	c2	10	NULL	5	
+2	DERIVED	t1	index	c3,c2	c2	10	NULL	2	
 DROP TABLE t1;
 CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
 ENGINE=InnoDB;
@@ -1741,12 +1747,13 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 1
 1
+# See above "MariaDB note"
 EXPLAIN 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	index	c3,c2	c2	18	NULL	5	
+2	DERIVED	t1	index	c3,c2	c2	18	NULL	2	
 DROP TABLE t1;
 CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), 
 KEY (c3), KEY (c2, c3))
@@ -1756,12 +1763,13 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 1
 1
+# See above "MariaDB note"
 EXPLAIN 
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	index	c3,c2	c2	14	NULL	5	
+2	DERIVED	t1	index	c3,c2	c2	14	NULL	2	
 DROP TABLE t1;
 End of 5.1 tests
 drop table if exists t1, t2, t3;
diff --git a/mysql-test/suite/pbxt/r/group_min_max.result b/mysql-test/suite/pbxt/r/group_min_max.result
index 396451cf8a9..39d8ab92b33 100644
--- a/mysql-test/suite/pbxt/r/group_min_max.result
+++ b/mysql-test/suite/pbxt/r/group_min_max.result
@@ -2256,7 +2256,8 @@ 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	t1_outer	index	NULL	a	10	NULL	15	Using where; Using index
+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	SUBQUERY	t1	index	NULL	a	10	NULL	15	Using index
 EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING 
 a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
diff --git a/mysql-test/suite/pbxt/r/metadata.result b/mysql-test/suite/pbxt/r/metadata.result
index c46c6e26ce4..b2a8acd8e9b 100644
--- a/mysql-test/suite/pbxt/r/metadata.result
+++ b/mysql-test/suite/pbxt/r/metadata.result
@@ -55,7 +55,7 @@ id	data	data
 2	female	no
 select t1.id from t1 union select t2.id from t2;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def				id	id	1	4	1	Y	32768	0	63
+def				id	id	1	4	1	Y	49152	0	63
 id
 1
 2
@@ -66,7 +66,7 @@ insert into t1 values (2,'two');
 set @arg00=1 ;
 select @arg00 FROM t1 where a=1 union distinct select 1 FROM t1 where a=1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def				@arg00	@arg00	8	20	1	Y	32768	0	63
+def				@arg00	@arg00	8	20	1	Y	49152	0	63
 @arg00
 1
 select * from (select @arg00) aaa;
@@ -76,7 +76,7 @@ def			aaa	@arg00	@arg00	8	20	1	Y	32768	0	63
 1
 select 1 union select 1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def				1	1	8	20	1	N	32769	0	63
+def				1	1	8	20	1	N	49153	0	63
 1
 1
 select * from (select 1 union select 1) aaa;
diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result
index 737f098ec48..4485378bc24 100644
--- a/mysql-test/suite/pbxt/r/subselect.result
+++ b/mysql-test/suite/pbxt/r/subselect.result
@@ -3420,18 +3420,20 @@ AAA	8
 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	t1	ALL	NULL	NULL	NULL	NULL	9	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	23	test.t1.a,test.t1.b	1	
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 ALTER TABLE t1 ADD INDEX(a);
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 a	b
+AAA	8
 BBB	4
 CCC	7
-AAA	8
 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>	ALL	distinct_key	NULL	NULL	NULL	9	
+1	PRIMARY	t1	ref	a	a	8	<subquery2>.a	1	Using where
 2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 DROP TABLE t1;
 create table t1( f1 int,f2 int);
diff --git a/mysql-test/suite/vcol/inc/vcol_view.inc b/mysql-test/suite/vcol/inc/vcol_view.inc
index 2bf413e2471..64149a7bb31 100644
--- a/mysql-test/suite/vcol/inc/vcol_view.inc
+++ b/mysql-test/suite/vcol/inc/vcol_view.inc
@@ -69,10 +69,14 @@ create table t1 (a int not null,
 insert into t1 (a) values (1), (2), (3), (4);
 create view v1 as select b+1 from t1 order by 1 desc limit 2;
 select * from v1;
+--echo MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
+--echo MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 drop view v1;
 create view v1 as select c+1 from t1 order by 1 desc limit 2;
 select * from v1;
+--echo MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
+--echo MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 drop view v1;
 drop table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result
index ae834722606..3447fd5b4df 100644
--- a/mysql-test/suite/vcol/r/vcol_view_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result
@@ -103,20 +103,24 @@ select * from v1;
 b+1
 0
 -1
+MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
+MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 drop view v1;
 create view v1 as select c+1 from t1 order by 1 desc limit 2;
 select * from v1;
 c+1
 0
 -1
+MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
+MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 drop view v1;
 drop table t1;
 create table t1 (a int,
diff --git a/mysql-test/suite/vcol/r/vcol_view_myisam.result b/mysql-test/suite/vcol/r/vcol_view_myisam.result
index bd5999792ff..6275685e7f4 100644
--- a/mysql-test/suite/vcol/r/vcol_view_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result
@@ -103,20 +103,24 @@ select * from v1;
 b+1
 0
 -1
+MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
+MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 drop view v1;
 create view v1 as select c+1 from t1 order by 1 desc limit 2;
 select * from v1;
 c+1
 0
 -1
+MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
+MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using filesort
 drop view v1;
 drop table t1;
 create table t1 (a int,
diff --git a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test
index 81a33c95424..be924136850 100644
--- a/mysql-test/t/subselect_mat.test
+++ b/mysql-test/t/subselect_mat.test
@@ -3,908 +3,13 @@
 # (WL#1110: Subquery optimization: materialization)
 #
 
---disable_warnings
-drop table if exists t1, t2, t3, t1i, t2i, t3i;
-drop view if exists v1, v2, v1m, v2m;
---enable_warnings
-
-create table t1 (a1 char(8), a2 char(8));
-create table t2 (b1 char(8), b2 char(8));
-create table t3 (c1 char(8), c2 char(8));
-
-insert into t1 values ('1 - 00', '2 - 00');
-insert into t1 values ('1 - 01', '2 - 01');
-insert into t1 values ('1 - 02', '2 - 02');
-
-insert into t2 values ('1 - 01', '2 - 01');
-insert into t2 values ('1 - 01', '2 - 01');
-insert into t2 values ('1 - 02', '2 - 02');
-insert into t2 values ('1 - 02', '2 - 02');
-insert into t2 values ('1 - 03', '2 - 03');
-
-insert into t3 values ('1 - 01', '2 - 01');
-insert into t3 values ('1 - 02', '2 - 02');
-insert into t3 values ('1 - 03', '2 - 03');
-insert into t3 values ('1 - 04', '2 - 04');
-
-# Indexed columns
-create table t1i (a1 char(8), a2 char(8));
-create table t2i (b1 char(8), b2 char(8));
-create table t3i (c1 char(8), c2 char(8));
-create index it1i1 on t1i (a1);
-create index it1i2 on t1i (a2);
-create index it1i3 on t1i (a1, a2);
-
-create index it2i1 on t2i (b1);
-create index it2i2 on t2i (b2);
-create index it2i3 on t2i (b1, b2);
-
-create index it3i1 on t3i (c1);
-create index it3i2 on t3i (c2);
-create index it3i3 on t3i (c1, c2);
-
-insert into t1i select * from t1;
-insert into t2i select * from t2;
-insert into t3i select * from t3;
 
 # force the use of materialization
 set @@optimizer_switch='semijoin=off';
 
-/******************************************************************************
-* Simple tests.
-******************************************************************************/
-# non-indexed nullable fields
-explain extended
-select * from t1 where a1 in (select b1 from t2 where b1 > '0');
-select * from t1 where a1 in (select b1 from t2 where b1 > '0');
-
-explain extended
-select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
-select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
-
-explain extended
-select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
-select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
-
-explain extended
-select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
-select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
-
-# indexed columns
-explain extended
-select * from t1i where a1 in (select b1 from t2i where b1 > '0');
-select * from t1i where a1 in (select b1 from t2i where b1 > '0');
-
-explain extended
-select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
-select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
-
-explain extended
-select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
-select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
-
-explain extended
-select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
-select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
-
-explain extended
-select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
-select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
-
-# BUG#31639: Wrong plan for uncorrelated subquery when loose scan is applicable.
-explain extended
-select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
-select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
-
-prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
-execute st1;
-execute st1;
-prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
-execute st2;
-execute st2;
-
-explain extended
-select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
-select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
--- error 1235
-select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
-
-# test re-optimization/re-execution with different execution methods
-# prepare once, exec with different modes
-set @@optimizer_switch='default,semijoin=off';
-prepare st1 from
-"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
-set @@optimizer_switch='default,materialization=off';
-execute st1;
-set @@optimizer_switch='default,semijoin=off';
-execute st1;
-
-set @@optimizer_switch='default,materialization=off';
-prepare st1 from
-"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
-set @@optimizer_switch='default,semijoin=off';
-execute st1;
-set @@optimizer_switch='default,materialization=off';
-execute st1;
-set @@optimizer_switch='default,semijoin=off';
-
-# materialize the result of ORDER BY
-# non-indexed fields
-explain extended
-select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
-select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
-# indexed fields
-explain extended
-select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
-select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
-
-/******************************************************************************
-* Views, UNIONs, several levels of nesting.
-******************************************************************************/
-# materialize the result of subquery over temp-table view
-
-create algorithm=merge view v1 as
-select b1, c2 from t2, t3 where b2 > c2;
-
-create algorithm=merge view v2 as
-select b1, c2 from t2, t3 group by b2, c2;
-
-create algorithm=temptable view v1m as
-select b1, c2 from t2, t3 where b2 > c2;
-
-create algorithm=temptable view v2m as
-select b1, c2 from t2, t3 group by b2, c2;
-
-select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null);
-select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null);
-
-select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null);
-select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null);
-
-drop view v1, v2, v1m, v2m;
-
-# nested subqueries, views
-explain extended
-select * from t1
-where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
-      (a1, a2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-select * from t1
-where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
-      (a1, a2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-
-explain extended
-select * from t1i
-where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
-      (a1, a2) in (select c1, c2 from t3i
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-select * from t1i
-where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
-      (a1, a2) in (select c1, c2 from t3i
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-
-explain extended
-select * from t1
-where (a1, a2) in (select b1, b2 from t2
-                   where b2 in (select c2 from t3 where c2 LIKE '%02') or
-                         b2 in (select c2 from t3 where c2 LIKE '%03')) and
-      (a1, a2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-select * from t1
-where (a1, a2) in (select b1, b2 from t2
-                   where b2 in (select c2 from t3 where c2 LIKE '%02') or
-                         b2 in (select c2 from t3 where c2 LIKE '%03')) and
-      (a1, a2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-
-# as above with correlated innermost subquery
-explain extended
-select * from t1
-where (a1, a2) in (select b1, b2 from t2
-                   where b2 in (select c2 from t3 t3a where c1 = a1) or
-                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
-      (a1, a2) in (select c1, c2 from t3 t3c
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-select * from t1
-where (a1, a2) in (select b1, b2 from t2
-                   where b2 in (select c2 from t3 t3a where c1 = a1) or
-                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
-      (a1, a2) in (select c1, c2 from t3 t3c
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-
-
-# multiple levels of nesting subqueries, unions
-explain extended
-(select * from t1
-where (a1, a2) in (select b1, b2 from t2
-                   where b2 in (select c2 from t3 where c2 LIKE '%02') or
-                         b2 in (select c2 from t3 where c2 LIKE '%03')
-                   group by b1, b2) and
-      (a1, a2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
-UNION
-(select * from t1i
-where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
-      (a1, a2) in (select c1, c2 from t3i
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
-
-(select * from t1
-where (a1, a2) in (select b1, b2 from t2
-                   where b2 in (select c2 from t3 where c2 LIKE '%02') or
-                         b2 in (select c2 from t3 where c2 LIKE '%03')
-                   group by b1, b2) and
-      (a1, a2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
-UNION
-(select * from t1i
-where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
-      (a1, a2) in (select c1, c2 from t3i
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
-
-
-# UNION of subqueries as a subquery (thus it is not computed via materialization)
-explain extended
-select * from t1
-where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
-      (a1, a2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-select * from t1
-where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
-      (a1, a2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
-# as above, with a join conditon between the outer references
-explain extended
-select * from t1, t3
-where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
-      (c1, c2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
-       a1 = c1;
-select * from t1, t3
-where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
-      (c1, c2) in (select c1, c2 from t3
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
-       a1 = c1;
-
-
-/******************************************************************************
-* Negative tests, where materialization should not be applied.
-******************************************************************************/
-# UNION in a subquery
-explain extended
-select * from t3
-where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
-select * from t3
-where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
-
-# correlation
-explain extended
-select * from t1
-where (a1, a2) in (select b1, b2 from t2
-                   where b2 in (select c2 from t3 t3a where c1 = a1) or
-                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
-      (a1, a2) in (select c1, c2 from t3 t3c
-                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
-
-# subquery has no tables
-explain extended
-select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
-select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
-explain extended
-select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
-select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
-
-
-/******************************************************************************
-* Subqueries in other uncovered clauses.
-******************************************************************************/
-
-/* SELECT clause */
-select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
-
-/* GROUP BY clause */
-create table columns (col int key);
-insert into columns values (1), (2);
-
-explain extended
-select * from t1 group by (select col from columns limit 1);
-select * from t1 group by (select col from columns limit 1);
-
-explain extended
-select * from t1 group by (a1 in (select col from columns));
-select * from t1 group by (a1 in (select col from columns));
-
-/* ORDER BY clause */
-explain extended
-select * from t1 order by (select col from columns limit 1);
-select * from t1 order by (select col from columns limit 1);
-
-/******************************************************************************
-* Column types/sizes that affect materialization.
-******************************************************************************/
-
-/*
-  Test that BLOBs are not materialized (except when arguments of some functions).
-*/
-# force materialization to be always considered
-set @@optimizer_switch='semijoin=off';
-set @prefix_len = 6;
-
-# BLOB == 16 (small blobs that could be stored in HEAP tables)
-set @blob_len = 16;
-set @suffix_len = @blob_len - @prefix_len;
-
-create table t1_16 (a1 blob(16), a2 blob(16));
-create table t2_16 (b1 blob(16), b2 blob(16));
-create table t3_16 (c1 blob(16), c2 blob(16));
-
-insert into t1_16 values
- (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
-insert into t1_16 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t1_16 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-
-insert into t2_16 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t2_16 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-insert into t2_16 values
- (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
-
-insert into t3_16 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t3_16 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-insert into t3_16 values
- (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
-insert into t3_16 values
- (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
-
-# single value transformer
-explain extended select left(a1,7), left(a2,7)
-from t1_16
-where a1 in (select b1 from t2_16 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_16
-where a1 in (select b1 from t2_16 where b1 > '0');
-
-# row value transformer
-explain extended select left(a1,7), left(a2,7)
-from t1_16
-where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_16
-where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
-
-# string function with a blob argument, the return type may be != blob
-explain extended select left(a1,7), left(a2,7)
-from t1_16
-where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_16
-where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
-
-# group_concat with a blob argument - depends on
-# the variable group_concat_max_len, and
-# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
-explain extended select left(a1,7), left(a2,7)
-from t1_16
-where a1 in (select group_concat(b1) from t2_16 group by b2);
-
-select left(a1,7), left(a2,7)
-from t1_16
-where a1 in (select group_concat(b1) from t2_16 group by b2);
-
-set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512)
-
-explain extended select left(a1,7), left(a2,7)
-from t1_16
-where a1 in (select group_concat(b1) from t2_16 group by b2);
-
-select left(a1,7), left(a2,7)
-from t1_16
-where a1 in (select group_concat(b1) from t2_16 group by b2);
-
-# BLOB column at the second (intermediate) level of nesting
-explain extended
-select * from t1
-where concat(a1,'x') IN
-      (select left(a1,8) from t1_16
-       where (a1, a2) IN
-             (select t2_16.b1, t2_16.b2 from t2_16, t2
-              where t2.b2 = substring(t2_16.b2,1,6) and
-                    t2.b1 IN (select c1 from t3 where c2 > '0')));
-
-
-drop table t1_16, t2_16, t3_16;
-
-
-# BLOB == 512 (CONVERT_IF_BIGGER_TO_BLOB == 512)
-set @blob_len = 512;
-set @suffix_len = @blob_len - @prefix_len;
-
-create table t1_512 (a1 blob(512), a2 blob(512));
-create table t2_512 (b1 blob(512), b2 blob(512));
-create table t3_512 (c1 blob(512), c2 blob(512));
-
-insert into t1_512 values
- (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
-insert into t1_512 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t1_512 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-
-insert into t2_512 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t2_512 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-insert into t2_512 values
- (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
-
-insert into t3_512 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t3_512 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-insert into t3_512 values
- (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
-insert into t3_512 values
- (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
-
-# single value transformer
-explain extended select left(a1,7), left(a2,7)
-from t1_512
-where a1 in (select b1 from t2_512 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_512
-where a1 in (select b1 from t2_512 where b1 > '0');
-
-# row value transformer
-explain extended select left(a1,7), left(a2,7)
-from t1_512
-where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_512
-where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
-
-# string function with a blob argument, the return type may be != blob
-explain extended select left(a1,7), left(a2,7)
-from t1_512
-where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_512
-where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
-
-# group_concat with a blob argument - depends on
-# the variable group_concat_max_len, and
-# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
-explain extended select left(a1,7), left(a2,7)
-from t1_512
-where a1 in (select group_concat(b1) from t2_512 group by b2);
-
-select left(a1,7), left(a2,7)
-from t1_512
-where a1 in (select group_concat(b1) from t2_512 group by b2);
-
-set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512)
-
-explain extended select left(a1,7), left(a2,7)
-from t1_512
-where a1 in (select group_concat(b1) from t2_512 group by b2);
-
-select left(a1,7), left(a2,7)
-from t1_512
-where a1 in (select group_concat(b1) from t2_512 group by b2);
-
-drop table t1_512, t2_512, t3_512;
-
-
-# BLOB == 1024 (group_concat_max_len == 1024)
-set @blob_len = 1024;
-set @suffix_len = @blob_len - @prefix_len;
-
-create table t1_1024 (a1 blob(1024), a2 blob(1024));
-create table t2_1024 (b1 blob(1024), b2 blob(1024));
-create table t3_1024 (c1 blob(1024), c2 blob(1024));
-
-insert into t1_1024 values
- (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
-insert into t1_1024 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t1_1024 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-
-insert into t2_1024 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t2_1024 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-insert into t2_1024 values
- (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
-
-insert into t3_1024 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t3_1024 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-insert into t3_1024 values
- (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
-insert into t3_1024 values
- (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
-
-# single value transformer
-explain extended select left(a1,7), left(a2,7)
-from t1_1024
-where a1 in (select b1 from t2_1024 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_1024
-where a1 in (select b1 from t2_1024 where b1 > '0');
-
-# row value transformer
-explain extended select left(a1,7), left(a2,7)
-from t1_1024
-where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_1024
-where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
-
-# string function with a blob argument, the return type may be != blob
-explain extended select left(a1,7), left(a2,7)
-from t1_1024
-where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_1024
-where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
-
-# group_concat with a blob argument - depends on
-# the variable group_concat_max_len, and
-# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
-explain extended select left(a1,7), left(a2,7)
-from t1_1024
-where a1 in (select group_concat(b1) from t2_1024 group by b2);
-
-select left(a1,7), left(a2,7)
-from t1_1024
-where a1 in (select group_concat(b1) from t2_1024 group by b2);
-
-set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 1024)
-
-explain extended select left(a1,7), left(a2,7)
-from t1_1024
-where a1 in (select group_concat(b1) from t2_1024 group by b2);
-
-select left(a1,7), left(a2,7)
-from t1_1024
-where a1 in (select group_concat(b1) from t2_1024 group by b2);
-
-drop table t1_1024, t2_1024, t3_1024;
-
-
-# BLOB == 1025
-set @blob_len = 1025;
-set @suffix_len = @blob_len - @prefix_len;
-
-create table t1_1025 (a1 blob(1025), a2 blob(1025));
-create table t2_1025 (b1 blob(1025), b2 blob(1025));
-create table t3_1025 (c1 blob(1025), c2 blob(1025));
-
-insert into t1_1025 values
- (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
-insert into t1_1025 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t1_1025 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-
-insert into t2_1025 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t2_1025 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-insert into t2_1025 values
- (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
-
-insert into t3_1025 values
- (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
-insert into t3_1025 values
- (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
-insert into t3_1025 values
- (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
-insert into t3_1025 values
- (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
-
-# single value transformer
-explain extended select left(a1,7), left(a2,7)
-from t1_1025
-where a1 in (select b1 from t2_1025 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_1025
-where a1 in (select b1 from t2_1025 where b1 > '0');
-
-# row value transformer
-explain extended select left(a1,7), left(a2,7)
-from t1_1025
-where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_1025
-where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
-
-# string function with a blob argument, the return type may be != blob
-explain extended select left(a1,7), left(a2,7)
-from t1_1025
-where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
-
-select left(a1,7), left(a2,7)
-from t1_1025
-where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
-
-# group_concat with a blob argument - depends on
-# the variable group_concat_max_len, and
-# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
-explain extended select left(a1,7), left(a2,7)
-from t1_1025
-where a1 in (select group_concat(b1) from t2_1025 group by b2);
-
-select left(a1,7), left(a2,7)
-from t1_1025
-where a1 in (select group_concat(b1) from t2_1025 group by b2);
-
-set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 1025)
-
-explain extended select left(a1,7), left(a2,7)
-from t1_1025
-where a1 in (select group_concat(b1) from t2_1025 group by b2);
-
-select left(a1,7), left(a2,7)
-from t1_1025
-where a1 in (select group_concat(b1) from t2_1025 group by b2);
-
-drop table t1_1025, t2_1025, t3_1025;
-
-# test for BIT fields
-create table t1bit (a1 bit(3), a2 bit(3));
-create table t2bit (b1 bit(3), b2 bit(3));
-
-insert into t1bit values (b'000', b'100');
-insert into t1bit values (b'001', b'101');
-insert into t1bit values (b'010', b'110');
-
-insert into t2bit values (b'001', b'101');
-insert into t2bit values (b'010', b'110');
-insert into t2bit values (b'110', b'111');
-
-set @@optimizer_switch='semijoin=off';
-
-explain extended select bin(a1), bin(a2)
-from t1bit
-where (a1, a2) in (select b1, b2 from t2bit);
-
-select bin(a1), bin(a2)
-from t1bit
-where (a1, a2) in (select b1, b2 from t2bit);
-
-drop table t1bit, t2bit;
-
-# test mixture of BIT and BLOB
-create table t1bb (a1 bit(3), a2 blob(3));
-create table t2bb (b1 bit(3), b2 blob(3));
-
-insert into t1bb values (b'000', '100');
-insert into t1bb values (b'001', '101');
-insert into t1bb values (b'010', '110');
-
-insert into t2bb values (b'001', '101');
-insert into t2bb values (b'010', '110');
-insert into t2bb values (b'110', '111');
-
-explain extended select bin(a1), a2
-from t1bb
-where (a1, a2) in (select b1, b2 from t2bb);
-
-select bin(a1), a2
-from t1bb
-where (a1, a2) in (select b1, b2 from t2bb);
-
-drop table t1bb, t2bb;
-drop table t1, t2, t3, t1i, t2i, t3i, columns;
-
-/******************************************************************************
-* Test the cache of the left operand of IN.
-******************************************************************************/
-set @@optimizer_switch='semijoin=off';
-
-# Test that default values of Cached_item are not used for comparison
-create table t1 (s1 int);
-create table t2 (s2 int);
-insert into t1 values (5),(1),(0);
-insert into t2 values (0), (1);
-select s2 from t2 where s2 in (select s1 from t1);
-drop table t1, t2;
-
-create table t1 (a int not null, b int not null);
-create table t2 (c int not null, d int not null);
-create table t3 (e int not null);
-
-# the first outer row has no matching inner row
-insert into t1 values (1,10);
-insert into t1 values (1,20);
-insert into t1 values (2,10);
-insert into t1 values (2,20);
-insert into t1 values (2,30);
-insert into t1 values (3,20);
-insert into t1 values (4,40);
-
-insert into t2 values (2,10);
-insert into t2 values (2,20);
-insert into t2 values (2,40);
-insert into t2 values (3,20);
-insert into t2 values (4,10);
-insert into t2 values (5,10);
-
-insert into t3 values (10);
-insert into t3 values (10);
-insert into t3 values (20);
-insert into t3 values (30);
-
-explain extended
-select a from t1 where a in (select c from t2 where d >= 20);
-select a from t1 where a in (select c from t2 where d >= 20);
-
-create index it1a on t1(a);
-
-explain extended
-select a from t1 where a in (select c from t2 where d >= 20);
-select a from t1 where a in (select c from t2 where d >= 20);
-
-# the first outer row has a matching inner row
-insert into t2 values (1,10);
-
-explain extended
-select a from t1 where a in (select c from t2 where d >= 20);
-select a from t1 where a in (select c from t2 where d >= 20);
-
-# cacheing for IN predicates inside a having clause - here the cached
-# items are changed to point to temporary tables.
-explain extended
-select a from t1 group by a having a in (select c from t2 where d >= 20);
-select a from t1 group by a having a in (select c from t2 where d >= 20);
-
-# create an index that can be used for the outer query GROUP BY 
-create index iab on t1(a, b);
-explain extended
-select a from t1 group by a having a in (select c from t2 where d >= 20);
-select a from t1 group by a having a in (select c from t2 where d >= 20);
-
-explain extended
-select a from t1 group by a
-having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
-select a from t1 group by a
-having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
-explain extended
-select a from t1
-where a in (select c from t2 where d >= some(select e from t3 where b=e));
-select a from t1
-where a in (select c from t2 where d >= some(select e from t3 where b=e));
-
-drop table t1, t2, t3;
-
-#
-# BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &&"
-#
-create table t2 (a int, b int, key(a), key(b));
-insert into t2 values (3,3),(3,3),(3,3);
-select 1 from t2 where  
-    t2.a > 1 
-  or 
-    t2.a = 3 and not t2.a not in (select t2.b from t2);
-drop table t2;
-
-#
-# BUG#37896 Assertion on entry of Item_in_subselect::exec on subquery with AND NOT
-#
-create table t1 (a1 int key);
-create table t2 (b1 int);
-insert into t1 values (5);
-
-# Query with group by, executed via materialization
-explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
-select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
-# Query with group by, executed via IN=>EXISTS
-set @@optimizer_switch='default,materialization=off';
-explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
-select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
-
-# Executed with materialization
-set @@optimizer_switch='default,semijoin=off';
-explain select min(a1) from t1 where 7 in (select b1 from t2);
-select min(a1) from t1 where 7 in (select b1 from t2);
-# Executed with semi-join. Notice, this time we get a different result (NULL).
-# This is the only correct result of all four queries. This difference is
-# filed as BUG#40037.
-set @@optimizer_switch='default,materialization=off';
-explain select min(a1) from t1 where 7 in (select b1 from t2);
-select min(a1) from t1 where 7 in (select b1 from t2);
-drop table t1,t2;
-
-#
-# BUG#36752 "subquery materialization produces wrong results when comparing different types"
-#
-create table t1 (a char(2), b varchar(10));
-insert into t1 values ('a',  'aaa');
-insert into t1 values ('aa', 'aaaa');
-
-set @@optimizer_switch='default,semijoin=off';
-explain select a,b from t1 where b in (select a from t1);
-select a,b from t1 where b in (select a from t1);
-prepare st1 from "select a,b from t1 where b in (select a from t1)";
-execute st1;
-execute st1;
-drop table t1;
-
-#
-# Bug #44303 Assertion failures in Field_new_decimal::store_decimal
-#            when executing materialized InsideOut semijoin
-# 
-CREATE TABLE t1 (f1 INT, f2 DECIMAL(5,3)) ENGINE=MyISAM;
-INSERT INTO t1 (f1, f2) VALUES (1, 1.789);
-INSERT INTO t1 (f1, f2) VALUES (13, 1.454);
-INSERT INTO t1 (f1, f2) VALUES (10, 1.668);
-
-CREATE TABLE t2 LIKE t1;
-INSERT INTO t2 VALUES (1, 1.789);
-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);
-SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
-
-DROP TABLE t1, t2;
-
-#
-# BUG#46548 IN-subqueries return 0 rows with materialization=on
-#
-CREATE TABLE t1 (
-  pk int,
-  a varchar(1),
-  b varchar(4),
-  c varchar(4),
-  d varchar(4),
-  PRIMARY KEY (pk)
-);
-INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
-
-CREATE TABLE t2 LIKE t1;
-INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
-
-SET @@optimizer_switch='default,semijoin=on,materialization=on';
-EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
-SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
-SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
-DROP TABLE t1, t2;
-
---echo #
---echo # BUG#50019: Wrong result for IN-subquery with materialization
---echo #
-create table t1(i int);
-insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
-create table t2(i int);
-insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
-create table t3(i int);
-insert into t3 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
-select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5);
-set @save_optimizer_switch=@@optimizer_switch;
-set session optimizer_switch='materialization=off';
-select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5);
-set session optimizer_switch=@save_optimizer_switch;
-drop table t1, t2, t3;
+--source t/subselect_sj_mat.test
 
+set @@optimizer_switch=default;
 #
 # Test that the contents of the temp table of a materialized subquery is
 # cleaned up between PS re-executions.
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index e73e7cfade2..ddc7208076b 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -490,6 +490,12 @@ drop table t1, t2;
 # Bug#33062: subquery in stored routine cause crash
 #
 
+--disable_warnings
+drop procedure if exists p1;
+drop procedure if exists p2;
+drop procedure if exists p3;
+drop procedure if exists p4;
+--enable_warnings
 CREATE TABLE t1(a INT);
 CREATE TABLE t2(c INT);
 
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
new file mode 100644
index 00000000000..dfaf6fb412e
--- /dev/null
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -0,0 +1,938 @@
+#
+# Hash semi-join regression tests
+# (WL#1110: Subquery optimization: materialization)
+#
+
+set optimizer_switch='firstmatch=off';
+--disable_warnings
+drop table if exists t1, t2, t3, t1i, t2i, t3i;
+drop view if exists v1, v2, v1m, v2m;
+--enable_warnings
+
+create table t1 (a1 char(8), a2 char(8));
+create table t2 (b1 char(8), b2 char(8));
+create table t3 (c1 char(8), c2 char(8));
+
+insert into t1 values ('1 - 00', '2 - 00');
+insert into t1 values ('1 - 01', '2 - 01');
+insert into t1 values ('1 - 02', '2 - 02');
+
+insert into t2 values ('1 - 01', '2 - 01');
+insert into t2 values ('1 - 01', '2 - 01');
+insert into t2 values ('1 - 02', '2 - 02');
+insert into t2 values ('1 - 02', '2 - 02');
+insert into t2 values ('1 - 03', '2 - 03');
+
+insert into t3 values ('1 - 01', '2 - 01');
+insert into t3 values ('1 - 02', '2 - 02');
+insert into t3 values ('1 - 03', '2 - 03');
+insert into t3 values ('1 - 04', '2 - 04');
+
+# Indexed columns
+create table t1i (a1 char(8), a2 char(8));
+create table t2i (b1 char(8), b2 char(8));
+create table t3i (c1 char(8), c2 char(8));
+create index it1i1 on t1i (a1);
+create index it1i2 on t1i (a2);
+create index it1i3 on t1i (a1, a2);
+
+create index it2i1 on t2i (b1);
+create index it2i2 on t2i (b2);
+create index it2i3 on t2i (b1, b2);
+
+create index it3i1 on t3i (c1);
+create index it3i2 on t3i (c2);
+create index it3i3 on t3i (c1, c2);
+
+insert into t1i select * from t1;
+insert into t2i select * from t2;
+insert into t3i select * from t3;
+
+
+/******************************************************************************
+* Simple tests.
+******************************************************************************/
+# non-indexed nullable fields
+explain extended
+select * from t1 where a1 in (select b1 from t2 where b1 > '0');
+select * from t1 where a1 in (select b1 from t2 where b1 > '0');
+
+explain extended
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
+
+explain extended
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
+
+explain extended
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
+
+# indexed columns
+--replace_column 7 #
+--replace_regex /it1.*/_it1_idx/  /test.t2i.*/_ref_/  /Using index$// /Using where$//
+explain extended
+select * from t1i where a1 in (select b1 from t2i where b1 > '0');
+select * from t1i where a1 in (select b1 from t2i where b1 > '0');
+
+--replace_column 6 # 8 # 11 #
+explain extended
+select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
+select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
+
+--replace_column 7 #
+--replace_regex /it1.*/_it1_idx/  /test.t2i.*/_ref_/  /Using index$// /Using where$//
+explain extended
+select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
+select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
+
+--replace_column 6 # 7 # 8 # 11 #
+explain extended
+select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
+select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
+
+--replace_column 6 # 7 # 8 # 11 #
+explain extended
+select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+
+# BUG#31639: Wrong plan for uncorrelated subquery when loose scan is applicable.
+explain extended
+select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
+select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
+
+prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
+execute st1;
+execute st1;
+prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
+execute st2;
+execute st2;
+
+explain extended
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+-- error 1235
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
+
+# test re-optimization/re-execution with different execution methods
+# prepare once, exec with different modes
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='default,semijoin=off';
+prepare st1 from
+"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
+set @@optimizer_switch='default,materialization=off';
+execute st1;
+set @@optimizer_switch='default,semijoin=off';
+execute st1;
+
+set @@optimizer_switch='default,materialization=off';
+prepare st1 from
+"select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1)";
+set @@optimizer_switch='default,semijoin=off';
+execute st1;
+set @@optimizer_switch='default,materialization=off';
+execute st1;
+set @@optimizer_switch=@save_optimizer_switch;
+
+# materialize the result of ORDER BY
+# non-indexed fields
+explain extended
+select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
+select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
+# indexed fields
+explain extended
+select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
+select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
+
+/******************************************************************************
+* Views, UNIONs, several levels of nesting.
+******************************************************************************/
+# materialize the result of subquery over temp-table view
+
+create algorithm=merge view v1 as
+select b1, c2 from t2, t3 where b2 > c2;
+
+create algorithm=merge view v2 as
+select b1, c2 from t2, t3 group by b2, c2;
+
+create algorithm=temptable view v1m as
+select b1, c2 from t2, t3 where b2 > c2;
+
+create algorithm=temptable view v2m as
+select b1, c2 from t2, t3 group by b2, c2;
+
+select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null);
+select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null);
+
+select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null);
+select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null);
+
+drop view v1, v2, v1m, v2m;
+
+# nested subqueries, views
+explain extended
+select * from t1
+where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
+      (a1, a2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+select * from t1
+where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and
+      (a1, a2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+
+--replace_column 6 # 7 # 8 # 11 #
+explain extended
+select * from t1i
+where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
+      (a1, a2) in (select c1, c2 from t3i
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+select * from t1i
+where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
+      (a1, a2) in (select c1, c2 from t3i
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+
+explain extended
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+                   where b2 in (select c2 from t3 where c2 LIKE '%02') or
+                         b2 in (select c2 from t3 where c2 LIKE '%03')) and
+      (a1, a2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+                   where b2 in (select c2 from t3 where c2 LIKE '%02') or
+                         b2 in (select c2 from t3 where c2 LIKE '%03')) and
+      (a1, a2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+
+# as above with correlated innermost subquery
+explain extended
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+                   where b2 in (select c2 from t3 t3a where c1 = a1) or
+                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
+      (a1, a2) in (select c1, c2 from t3 t3c
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+                   where b2 in (select c2 from t3 t3a where c1 = a1) or
+                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
+      (a1, a2) in (select c1, c2 from t3 t3c
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+
+
+# multiple levels of nesting subqueries, unions
+--replace_column 6 # 7 # 8 # 11 #
+explain extended
+(select * from t1
+where (a1, a2) in (select b1, b2 from t2
+                   where b2 in (select c2 from t3 where c2 LIKE '%02') or
+                         b2 in (select c2 from t3 where c2 LIKE '%03')
+                   group by b1, b2) and
+      (a1, a2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
+UNION
+(select * from t1i
+where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
+      (a1, a2) in (select c1, c2 from t3i
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
+
+(select * from t1
+where (a1, a2) in (select b1, b2 from t2
+                   where b2 in (select c2 from t3 where c2 LIKE '%02') or
+                         b2 in (select c2 from t3 where c2 LIKE '%03')
+                   group by b1, b2) and
+      (a1, a2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
+UNION
+(select * from t1i
+where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and
+      (a1, a2) in (select c1, c2 from t3i
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
+
+
+# UNION of subqueries as a subquery (thus it is not computed via materialization)
+explain extended
+select * from t1
+where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
+      (a1, a2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+select * from t1
+where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
+      (a1, a2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
+# as above, with a join conditon between the outer references
+explain extended
+select * from t1, t3
+where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
+      (c1, c2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
+       a1 = c1;
+select * from t1, t3
+where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
+      (c1, c2) in (select c1, c2 from t3
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
+       a1 = c1;
+
+
+/******************************************************************************
+* Negative tests, where materialization should not be applied.
+******************************************************************************/
+# UNION in a subquery
+explain extended
+select * from t3
+where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
+select * from t3
+where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
+
+# correlation
+explain extended
+select * from t1
+where (a1, a2) in (select b1, b2 from t2
+                   where b2 in (select c2 from t3 t3a where c1 = a1) or
+                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
+      (a1, a2) in (select c1, c2 from t3 t3c
+                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
+
+# subquery has no tables
+explain extended
+select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
+select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
+explain extended
+select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
+select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
+
+
+/******************************************************************************
+* Subqueries in other uncovered clauses.
+******************************************************************************/
+
+/* SELECT clause */
+select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
+
+/* GROUP BY clause */
+create table columns (col int key);
+insert into columns values (1), (2);
+
+explain extended
+select * from t1 group by (select col from columns limit 1);
+select * from t1 group by (select col from columns limit 1);
+
+explain extended
+select * from t1 group by (a1 in (select col from columns));
+select * from t1 group by (a1 in (select col from columns));
+
+/* ORDER BY clause */
+explain extended
+select * from t1 order by (select col from columns limit 1);
+select * from t1 order by (select col from columns limit 1);
+
+/******************************************************************************
+* Column types/sizes that affect materialization.
+******************************************************************************/
+
+/*
+  Test that BLOBs are not materialized (except when arguments of some functions).
+*/
+# force materialization to be always considered
+set @prefix_len = 6;
+
+# BLOB == 16 (small blobs that could be stored in HEAP tables)
+set @blob_len = 16;
+set @suffix_len = @blob_len - @prefix_len;
+
+create table t1_16 (a1 blob(16), a2 blob(16));
+create table t2_16 (b1 blob(16), b2 blob(16));
+create table t3_16 (c1 blob(16), c2 blob(16));
+
+insert into t1_16 values
+ (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
+insert into t1_16 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t1_16 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+
+insert into t2_16 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t2_16 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_16 values
+ (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+
+insert into t3_16 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t3_16 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t3_16 values
+ (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_16 values
+ (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
+
+# single value transformer
+explain extended select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select b1 from t2_16 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select b1 from t2_16 where b1 > '0');
+
+# row value transformer
+explain extended select left(a1,7), left(a2,7)
+from t1_16
+where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_16
+where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
+
+# string function with a blob argument, the return type may be != blob
+explain extended select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
+
+# group_concat with a blob argument - depends on
+# the variable group_concat_max_len, and
+# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
+explain extended select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select group_concat(b1) from t2_16 group by b2);
+
+select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select group_concat(b1) from t2_16 group by b2);
+
+set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512)
+
+explain extended select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select group_concat(b1) from t2_16 group by b2);
+
+select left(a1,7), left(a2,7)
+from t1_16
+where a1 in (select group_concat(b1) from t2_16 group by b2);
+
+# BLOB column at the second (intermediate) level of nesting
+explain extended
+select * from t1
+where concat(a1,'x') IN
+      (select left(a1,8) from t1_16
+       where (a1, a2) IN
+             (select t2_16.b1, t2_16.b2 from t2_16, t2
+              where t2.b2 = substring(t2_16.b2,1,6) and
+                    t2.b1 IN (select c1 from t3 where c2 > '0')));
+
+
+drop table t1_16, t2_16, t3_16;
+
+
+# BLOB == 512 (CONVERT_IF_BIGGER_TO_BLOB == 512)
+set @blob_len = 512;
+set @suffix_len = @blob_len - @prefix_len;
+
+create table t1_512 (a1 blob(512), a2 blob(512));
+create table t2_512 (b1 blob(512), b2 blob(512));
+create table t3_512 (c1 blob(512), c2 blob(512));
+
+insert into t1_512 values
+ (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
+insert into t1_512 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t1_512 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+
+insert into t2_512 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t2_512 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_512 values
+ (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+
+insert into t3_512 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t3_512 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t3_512 values
+ (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_512 values
+ (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
+
+# single value transformer
+explain extended select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select b1 from t2_512 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select b1 from t2_512 where b1 > '0');
+
+# row value transformer
+explain extended select left(a1,7), left(a2,7)
+from t1_512
+where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_512
+where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
+
+# string function with a blob argument, the return type may be != blob
+explain extended select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
+
+# group_concat with a blob argument - depends on
+# the variable group_concat_max_len, and
+# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
+explain extended select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+
+set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512)
+
+explain extended select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+
+select left(a1,7), left(a2,7)
+from t1_512
+where a1 in (select group_concat(b1) from t2_512 group by b2);
+
+drop table t1_512, t2_512, t3_512;
+
+
+# BLOB == 1024 (group_concat_max_len == 1024)
+set @blob_len = 1024;
+set @suffix_len = @blob_len - @prefix_len;
+
+create table t1_1024 (a1 blob(1024), a2 blob(1024));
+create table t2_1024 (b1 blob(1024), b2 blob(1024));
+create table t3_1024 (c1 blob(1024), c2 blob(1024));
+
+insert into t1_1024 values
+ (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
+insert into t1_1024 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t1_1024 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+
+insert into t2_1024 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t2_1024 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_1024 values
+ (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+
+insert into t3_1024 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t3_1024 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t3_1024 values
+ (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_1024 values
+ (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
+
+# single value transformer
+explain extended select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select b1 from t2_1024 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select b1 from t2_1024 where b1 > '0');
+
+# row value transformer
+explain extended select left(a1,7), left(a2,7)
+from t1_1024
+where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_1024
+where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
+
+# string function with a blob argument, the return type may be != blob
+explain extended select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
+
+# group_concat with a blob argument - depends on
+# the variable group_concat_max_len, and
+# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
+explain extended select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select group_concat(b1) from t2_1024 group by b2);
+
+select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select group_concat(b1) from t2_1024 group by b2);
+
+set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 1024)
+
+explain extended select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select group_concat(b1) from t2_1024 group by b2);
+
+select left(a1,7), left(a2,7)
+from t1_1024
+where a1 in (select group_concat(b1) from t2_1024 group by b2);
+
+drop table t1_1024, t2_1024, t3_1024;
+
+
+# BLOB == 1025
+set @blob_len = 1025;
+set @suffix_len = @blob_len - @prefix_len;
+
+create table t1_1025 (a1 blob(1025), a2 blob(1025));
+create table t2_1025 (b1 blob(1025), b2 blob(1025));
+create table t3_1025 (c1 blob(1025), c2 blob(1025));
+
+insert into t1_1025 values
+ (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));
+insert into t1_1025 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t1_1025 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+
+insert into t2_1025 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t2_1025 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t2_1025 values
+ (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+
+insert into t3_1025 values
+ (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));
+insert into t3_1025 values
+ (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));
+insert into t3_1025 values
+ (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));
+insert into t3_1025 values
+ (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));
+
+# single value transformer
+explain extended select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select b1 from t2_1025 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select b1 from t2_1025 where b1 > '0');
+
+# row value transformer
+explain extended select left(a1,7), left(a2,7)
+from t1_1025
+where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_1025
+where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
+
+# string function with a blob argument, the return type may be != blob
+explain extended select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
+
+select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
+
+# group_concat with a blob argument - depends on
+# the variable group_concat_max_len, and
+# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB
+explain extended select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select group_concat(b1) from t2_1025 group by b2);
+
+select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select group_concat(b1) from t2_1025 group by b2);
+
+set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 1025)
+
+explain extended select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select group_concat(b1) from t2_1025 group by b2);
+
+select left(a1,7), left(a2,7)
+from t1_1025
+where a1 in (select group_concat(b1) from t2_1025 group by b2);
+
+drop table t1_1025, t2_1025, t3_1025;
+
+# test for BIT fields
+create table t1bit (a1 bit(3), a2 bit(3));
+create table t2bit (b1 bit(3), b2 bit(3));
+
+insert into t1bit values (b'000', b'100');
+insert into t1bit values (b'001', b'101');
+insert into t1bit values (b'010', b'110');
+
+insert into t2bit values (b'001', b'101');
+insert into t2bit values (b'010', b'110');
+insert into t2bit values (b'110', b'111');
+
+explain extended select bin(a1), bin(a2)
+from t1bit
+where (a1, a2) in (select b1, b2 from t2bit);
+
+select bin(a1), bin(a2)
+from t1bit
+where (a1, a2) in (select b1, b2 from t2bit);
+
+drop table t1bit, t2bit;
+
+# test mixture of BIT and BLOB
+create table t1bb (a1 bit(3), a2 blob(3));
+create table t2bb (b1 bit(3), b2 blob(3));
+
+insert into t1bb values (b'000', '100');
+insert into t1bb values (b'001', '101');
+insert into t1bb values (b'010', '110');
+
+insert into t2bb values (b'001', '101');
+insert into t2bb values (b'010', '110');
+insert into t2bb values (b'110', '111');
+
+explain extended select bin(a1), a2
+from t1bb
+where (a1, a2) in (select b1, b2 from t2bb);
+
+select bin(a1), a2
+from t1bb
+where (a1, a2) in (select b1, b2 from t2bb);
+
+drop table t1bb, t2bb;
+drop table t1, t2, t3, t1i, t2i, t3i, columns;
+
+/******************************************************************************
+* Test the cache of the left operand of IN.
+******************************************************************************/
+
+# Test that default values of Cached_item are not used for comparison
+create table t1 (s1 int);
+create table t2 (s2 int);
+insert into t1 values (5),(1),(0);
+insert into t2 values (0), (1);
+select s2 from t2 where s2 in (select s1 from t1);
+drop table t1, t2;
+
+create table t1 (a int not null, b int not null);
+create table t2 (c int not null, d int not null);
+create table t3 (e int not null);
+
+# the first outer row has no matching inner row
+insert into t1 values (1,10);
+insert into t1 values (1,20);
+insert into t1 values (2,10);
+insert into t1 values (2,20);
+insert into t1 values (2,30);
+insert into t1 values (3,20);
+insert into t1 values (4,40);
+
+insert into t2 values (2,10);
+insert into t2 values (2,20);
+insert into t2 values (2,40);
+insert into t2 values (3,20);
+insert into t2 values (4,10);
+insert into t2 values (5,10);
+
+insert into t3 values (10);
+insert into t3 values (10);
+insert into t3 values (20);
+insert into t3 values (30);
+
+explain extended
+select a from t1 where a in (select c from t2 where d >= 20);
+select a from t1 where a in (select c from t2 where d >= 20);
+
+create index it1a on t1(a);
+
+explain extended
+select a from t1 where a in (select c from t2 where d >= 20);
+select a from t1 where a in (select c from t2 where d >= 20);
+
+# the first outer row has a matching inner row
+insert into t2 values (1,10);
+
+explain extended
+select a from t1 where a in (select c from t2 where d >= 20);
+select a from t1 where a in (select c from t2 where d >= 20);
+
+# cacheing for IN predicates inside a having clause - here the cached
+# items are changed to point to temporary tables.
+explain extended
+select a from t1 group by a having a in (select c from t2 where d >= 20);
+select a from t1 group by a having a in (select c from t2 where d >= 20);
+
+# create an index that can be used for the outer query GROUP BY 
+create index iab on t1(a, b);
+explain extended
+select a from t1 group by a having a in (select c from t2 where d >= 20);
+select a from t1 group by a having a in (select c from t2 where d >= 20);
+
+explain extended
+select a from t1 group by a
+having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
+select a from t1 group by a
+having a in (select c from t2 where d >= some(select e from t3 where max(b)=e));
+explain extended
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+
+drop table t1, t2, t3;
+
+#
+# BUG#36133 "Assertion `exec_method != MATERIALIZATION || (exec_method == MATERIALIZATION &&"
+#
+create table t2 (a int, b int, key(a), key(b));
+insert into t2 values (3,3),(3,3),(3,3);
+select 1 from t2 where  
+    t2.a > 1 
+  or 
+    t2.a = 3 and not t2.a not in (select t2.b from t2);
+drop table t2;
+
+#
+# BUG#37896 Assertion on entry of Item_in_subselect::exec on subquery with AND NOT
+#
+create table t1 (a1 int key);
+create table t2 (b1 int);
+insert into t1 values (5);
+
+# Query with group by, executed via materialization
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+# Query with group by, executed via IN=>EXISTS
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='default,materialization=off';
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+
+# Executed with materialization
+set @@optimizer_switch='default,semijoin=off';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+select min(a1) from t1 where 7 in (select b1 from t2);
+# Executed with semi-join. Notice, this time we get a different result (NULL).
+# This is the only correct result of all four queries. This difference is
+# filed as BUG#40037.
+set @@optimizer_switch='default,materialization=off';
+-- echo # with MariaDB and MWL#90, this particular case is solved:
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+select min(a1) from t1 where 7 in (select b1 from t2);
+-- echo # but when we go around MWL#90 code, the problem still shows up:
+explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+set @@optimizer_switch= @save_optimizer_switch;
+drop table t1,t2;
+
+#
+# BUG#36752 "subquery materialization produces wrong results when comparing different types"
+#
+create table t1 (a char(2), b varchar(10));
+insert into t1 values ('a',  'aaa');
+insert into t1 values ('aa', 'aaaa');
+
+explain select a,b from t1 where b in (select a from t1);
+select a,b from t1 where b in (select a from t1);
+prepare st1 from "select a,b from t1 where b in (select a from t1)";
+execute st1;
+execute st1;
+drop table t1;
+
+#
+# Bug #44303 Assertion failures in Field_new_decimal::store_decimal
+#            when executing materialized InsideOut semijoin
+# 
+CREATE TABLE t1 (f1 INT, f2 DECIMAL(5,3)) ENGINE=MyISAM;
+INSERT INTO t1 (f1, f2) VALUES (1, 1.789);
+INSERT INTO t1 (f1, f2) VALUES (13, 1.454);
+INSERT INTO t1 (f1, f2) VALUES (10, 1.668);
+
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1, 1.789);
+INSERT INTO t2 VALUES (13, 1.454);
+
+set @save_optimizer_switch=@@optimizer_switch;
+SET @@optimizer_switch='default,semijoin=on,materialization=on';
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
+SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
+set @@optimizer_switch= @save_optimizer_switch;
+
+DROP TABLE t1, t2;
+
+#
+# BUG#46548 IN-subqueries return 0 rows with materialization=on
+#
+CREATE TABLE t1 (
+  pk int,
+  a varchar(1),
+  b varchar(4),
+  c varchar(4),
+  d varchar(4),
+  PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
+
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+
+set @save_optimizer_switch=@@optimizer_switch;
+SET @@optimizer_switch='default,semijoin=on,materialization=on';
+EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
+DROP TABLE t1, t2;
+set optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # BUG#50019: Wrong result for IN-subquery with materialization
+--echo #
+create table t1(i int);
+insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+create table t2(i int);
+insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+create table t3(i int);
+insert into t3 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5);
+set @save_optimizer_switch=@@optimizer_switch;
+set session optimizer_switch='materialization=off';
+select * from t1 where t1.i in (select t2.i from t2 join t3 where t2.i + t3.i = 5);
+set session optimizer_switch=@save_optimizer_switch;
+drop table t1, t2, t3;
+
+#
+# Test that the contentes of the temp table of a materialized subquery is
+# cleanup up between PS reexecutions.
+#
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2);
+create table t1 (a int);
+insert into t1 values (0),(1),(2);
+explain select a, a in (select a from t1) from t0;
+select a, a in (select a from t1) from t0;
+prepare s from 'select a, a in (select a from t1) from t0';
+execute s;
+update t1 set a=123;
+execute s;
+drop table t0, t1;
+set optimizer_switch='firstmatch=on';