5.3->5.5 merge

1 files changed, 32 insertions, 21 deletions

diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index b410df3bab9..c95aaab1c13 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -46,8 +46,9 @@ a	b
 19	14
 explain select * from t2 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Start temporary
-1	PRIMARY	t2	ref	b	b	5	test.t1.a	2	End temporary
+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	Using where
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -65,8 +66,9 @@ insert into t3 select a,a, a,a,a from t0;
 insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Start temporary
-1	PRIMARY	t3	ref	b	b	5	test.t1.a	1	End temporary
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
@@ -90,8 +92,9 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; Start temporary
-1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	End temporary
+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	Using where
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 a	b	pk1	pk2
 0	0	0	0
@@ -140,8 +143,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	22	Start temporary
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -173,7 +177,8 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2)
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	32	Using where; FirstMatch(ot)
+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) 
 from t2 ot where a in (select a from t1 it);
@@ -206,8 +211,9 @@ explain select
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	22	Start temporary
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	22	
+1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -239,7 +245,8 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2)
 from t2 ot where a in (select a from t1 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	22	
-1	PRIMARY	it	ALL	NULL	NULL	NULL	NULL	52	Using where; FirstMatch(ot)
+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) 
 from t2 ot where a in (select a from t1 it);
@@ -279,9 +286,10 @@ from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	a	a	5	NULL	10	Using where; Using index
-1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using index; FirstMatch(t0)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	9	func	1	Using where
+2	SUBQUERY	t1	index	a	a	5	NULL	10	Using where; Using index
+2	SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
+2	SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 drop table t0, t1,t2,t3;
 CREATE TABLE t1 (
 ID int(11) NOT NULL auto_increment,
@@ -358,7 +366,8 @@ WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	
-1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; FirstMatch(t1)
+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 (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -593,7 +602,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+2	SUBQUERY	t3	index	PRIMARY	PRIMARY	4	NULL	10	Using index
 drop table t0, t1, t2, t3;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -705,8 +714,9 @@ alter table t3 add primary key(id), add key(a);
 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 where; Using index
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	30	Using index; FirstMatch(t2)
+1	PRIMARY	t2	index	a	a	5	NULL	1000	Using index
+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)
 1000
@@ -861,10 +871,11 @@ EXPLAIN
 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+2	SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	1	
 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 WHERE t3.b IN (SELECT b FROM t4);