make JOIN::prepare, JOIN::optimize only once for EXPLAIN of derived table

(BUG#2120 sfter merge)


mysql-test/r/derived.result:
  correct results of derived tble EXPLAIN
  test of "Using Index" with derived tables
mysql-test/t/derived.test:
  test of "Using Index" with derived tables
sql/mysql_priv.h:
  way to force derived table save JOIN after execution
sql/sql_derived.cc:
  way to force derived table save JOIN after execution
sql/sql_lex.h:
  way to force derived table save JOIN after execution
sql/sql_select.cc:
  make JOIN::prepare, JOIN::optimize only once for EXPLAIN of derived table

1 files changed, 16 insertions, 5 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index a89494645fa..dea4dd9b4c2 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -141,7 +141,7 @@ a	t
 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	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
-2	DERIVED	t1	index	NULL	a	4	NULL	10000	Using index
+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 )
@@ -189,13 +189,13 @@ 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>	ALL	NULL	NULL	NULL	NULL	6	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
-2	DERIVED	m1	index	NULL	PRIMARY	3	NULL	9	Using index
+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>	ALL	NULL	NULL	NULL	NULL	6	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
-2	DERIVED	m1	index	NULL	PRIMARY	3	NULL	9	Using index
+2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 drop table t1,t2;
 SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1;
 x
@@ -229,8 +229,8 @@ explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 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	index	NULL	PRIMARY	4	NULL	2	Using where; Using index
-3	DEPENDENT SUBQUERY	B	index	NULL	PRIMARY	4	NULL	2	Using where; Using index
+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);
 insert into t1 values (1),(2);
@@ -309,3 +309,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
 2	DERIVED	x	ALL	NULL	NULL	NULL	NULL	17	Using temporary; Using filesort
 2	DERIVED	y	ALL	NULL	NULL	NULL	NULL	17	Using where
+drop table t1;
+create table t2 (a int, b int, primary key (a));
+insert into t2 values (1,7),(2,7);
+explain select a from t2 where a>1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	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	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+drop table t2;