Merge.

3 files changed, 33 insertions, 19 deletions

diff --git a/mysql-test/suite/pbxt/r/derived.result b/mysql-test/suite/pbxt/r/derived.result
index b4c1762e81a..75d5fcf8b16 100644
--- a/mysql-test/suite/pbxt/r/derived.result
+++ b/mysql-test/suite/pbxt/r/derived.result
@@ -57,8 +57,9 @@ a	b	a	b
 3	c	3	c
 explain select * from t1 as x1, (select * from t1) as x2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	x1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
+1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	
 drop table if exists  t2,t3;
 select * from (select 1) as a;
 1
@@ -112,8 +113,9 @@ a	b
 3	c
 explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
 drop table t1, t2;
 create table t1(a int not null, t char(8), index(a));
 SELECT * FROM (SELECT * FROM t1) as b ORDER BY a  ASC LIMIT 0,20;
@@ -140,8 +142,9 @@ a	t
 20	20
 explain select count(*) from t1 as tt1, (select * from t1) as tt2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tt1	index	NULL	a	4	NULL	10000	Using index
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10000	Using join buffer (flat, BNL join)
+1	PRIMARY	tt1	index	NULL	a	4	NULL	10000	Using index
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10000	Using join buffer (flat, BNL join)
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	10000	
 drop table t1;
 SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
 (SELECT * FROM (SELECT 1 as a) as a )
@@ -170,30 +173,30 @@ insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd'
 insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
 SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 pla_id	mat_id
-102	1
-101	1
 100	1
-104	2
+101	1
+102	1
 103	2
+104	2
 105	3
 SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 pla_id	test
-102	1
-101	1
 100	1
-104	2
+101	1
+102	1
 103	2
+104	2
 105	3
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
-1	PRIMARY	<derived2>	ref	key0	key0	7	test.m2.matintnum	2	
+1	PRIMARY	<derived2>	ALL	NULL	$hj	7	test.m2.matintnum	9	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
-1	PRIMARY	<derived2>	ref	key0	key0	7	test.m2.matintnum	2	
+1	PRIMARY	<derived2>	ALL	NULL	$hj	7	test.m2.matintnum	9	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
 2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 drop table t1,t2;
@@ -231,8 +234,9 @@ count(*)
 2
 explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.A.E2	1	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	THEMAX.E2	1	Using where
+2	DERIVED	A	ALL	NULL	NULL	NULL	NULL	2	Using where
 3	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	2	Using where
 drop table t1;
 create table t1 (a int);
@@ -322,7 +326,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
 explain select a from (select a from t2 where a>1) tt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t2	index	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
 drop table t2;
 CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY  (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`));
 insert into t1 values (128, 'rozn', 2, now(), 10),(128, 'rozn', 1, now(), 10);
diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result
index 066b7519481..f2a5def6336 100644
--- a/mysql-test/suite/pbxt/r/subselect.result
+++ b/mysql-test/suite/pbxt/r/subselect.result
@@ -199,10 +199,11 @@ select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from
 explain extended select (select t3.a from t3 where a<8 order by 1 desc limit 1), a from 
 (select * from t2 where a>1) as tt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using filesort
 Warnings:
-Note	1003	select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`test`.`t2`.`a` AS `a` from `test`.`t2` where (`test`.`t2`.`a` > 1)
+Note	1003	select (select `test`.`t3`.`a` from `test`.`t3` where (`test`.`t3`.`a` < 8) order by 1 desc limit 1) AS `(select t3.a from t3 where a<8 order by 1 desc limit 1)`,`tt`.`a` AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
 select * from t1 where t1.a=(select t2.a from t2 where t2.b=(select max(a) from t3) order by 1 desc limit 1);
 a
 2
@@ -2626,6 +2627,8 @@ createDate datetime NOT NULL,
 modifyDate timestamp NOT NULL
 );
 INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='derived_merge=on';
 select
 count(distinct t2.userid) pass,
 groupstuff.*,
@@ -2667,6 +2670,7 @@ pass	userid	parentid	parentgroup	childid	groupname	grouptypeid	crse	categoryid	c
 1	5141	12	group2	12	group2	5	1	1	87	Oct04
 1	5141	12	group2	12	group2	5	1	2	88	Oct04
 1	5141	12	group2	12	group2	5	1	2	89	Oct04
+set optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3, t4, t5;
 create table t1 (a int);
 insert into t1 values (1), (2), (3);
diff --git a/mysql-test/suite/pbxt/t/subselect.test b/mysql-test/suite/pbxt/t/subselect.test
index 3f4d6a9a870..4c78d2f805b 100644
--- a/mysql-test/suite/pbxt/t/subselect.test
+++ b/mysql-test/suite/pbxt/t/subselect.test
@@ -1674,6 +1674,9 @@ CREATE TABLE t5 (
 );
 INSERT INTO t5 VALUES (5141,12,'2004-08-06','2004-08-06');
 
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='derived_merge=on';
+
 select
   count(distinct t2.userid) pass,
   groupstuff.*,
@@ -1706,6 +1709,8 @@ join
 group by 
   groupstuff.groupname, colhead , t2.courseid;
 
+set optimizer_switch=@save_optimizer_switch; 
+
 drop table t1, t2, t3, t4, t5;
 
 #