Bug#30302: Tables that were optimized away are printed in the

EXPLAIN EXTENDED warning.

Query optimizer searches for the constant tables and optimizes them away. This
means that fields of such tables are substituted for their values and on later
phases they are treated as constants. After this constant tables are removed
from the query execution plan. Nevertheless constant tables were shown in 
the EXPLAIN EXTENDED warning thus producing query that might be not an
equivalent of the original query.
        
Now the print_join function skips all tables that were optimized away from
printing to the EXPLAIN EXTENDED warning. If all tables were optimized away it
produces the 'FROM dual' clause.


mysql-test/r/explain.result:
  A test case added for the bug#30302.
mysql-test/r/func_default.result:
  Adjusted test case result after fix for the bug#30302.
mysql-test/r/func_regexp.result:
  Adjusted test case result after fix for the bug#30302.
mysql-test/r/func_test.result:
  Adjusted test case result after fix for the bug#30302.
mysql-test/r/having.result:
  Adjusted test case result after fix for the bug#30302.
mysql-test/r/select.result:
  Adjusted test case result after fix for the bug#30302.
mysql-test/r/subselect.result:
  Adjusted test case result after fix for the bug#30302.
mysql-test/r/subselect3.result:
  Adjusted test case result after fix for the bug#30302.
mysql-test/r/type_datetime.result:
  Adjusted test case result after fix for the bug#30302.
mysql-test/t/explain.test:
  A test case added for the bug#30302.
sql/sql_select.cc:
  Bug#30302: Tables that were optimized away are printed in the
  EXPLAIN EXTENDED warning.
  Now the print_join function skips all tables that were optimized away from
  printing to the EXPLAIN EXTENDED warning. If all tables were optimized away it
  produces the 'FROM dual' clause.
sql/table.h:
  Adjusted test case result after fix for the bug#30302.
  The optimized_away flag is added to the TABLE_LIST struct.

10 files changed, 48 insertions, 16 deletions

diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 96fcbc33d3f..fbeea8bab45 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -64,7 +64,7 @@ explain extended select * from v1 where f2=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select '1' AS `f1`,'1' AS `f2` from `test`.`t1` where 1
+Note	1003	select '1' AS `f1`,'1' AS `f2` from dual where 1
 explain extended select * from t1 where 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
@@ -74,7 +74,7 @@ explain extended select * from t1 where 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select '1' AS `f1`,'1' AS `f2` from `test`.`t1` where 1
+Note	1003	select '1' AS `f1`,'1' AS `f2` from dual where 1
 explain extended select * from t1 having 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING
@@ -84,7 +84,7 @@ explain extended select * from t1 having 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select '1' AS `f1`,'1' AS `f2` from `test`.`t1` having 1
+Note	1003	select '1' AS `f1`,'1' AS `f2` from dual having 1
 drop view v1;
 drop table t1;
 CREATE TABLE t1(c INT);
@@ -194,4 +194,24 @@ dt
 2001-01-01 01:01:01
 2001-01-01 01:01:01
 drop tables t1, t2;
+#
+# Bug#30302: Tables that were optimized away are printed in the
+#            EXPLAIN EXTENDED warning.
+#
+create table t1(f1 int);
+create table t2(f2 int);
+insert into t1 values(1);
+insert into t2 values(1),(2);
+explain extended select * from t1 where f1=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select '1' AS `f1` from dual where 1
+explain extended select * from t1 join t2 on f1=f2 where f1=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select '1' AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` where (`test`.`t2`.`f2` = 1)
+drop table t1,t2;
 End of 5.1 tests.
diff --git a/mysql-test/r/func_default.result b/mysql-test/r/func_default.result
index a8f59f73e88..68a3a58e63f 100644
--- a/mysql-test/r/func_default.result
+++ b/mysql-test/r/func_default.result
@@ -8,7 +8,7 @@ explain extended select default(str), default(strnull), default(intg), default(r
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select default('') AS `default(str)`,default('') AS `default(strnull)`,default('0') AS `default(intg)`,default('0') AS `default(rel)` from `test`.`t1`
+Note	1003	select default('') AS `default(str)`,default('') AS `default(strnull)`,default('0') AS `default(intg)`,default('0') AS `default(rel)` from dual
 select * from t1 where str <> default(str);
 str	strnull	intg	rel
 		0	0
diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result
index 54aad23402f..43f90e2e2e6 100644
--- a/mysql-test/r/func_regexp.result
+++ b/mysql-test/r/func_regexp.result
@@ -52,7 +52,7 @@ explain extended select * from t1 where xxx regexp('is a test of some long text
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select 'this is a test of some long text to see what happens' AS `xxx` from `test`.`t1` where ('this is a test of some long text to see what happens' regexp 'is a test of some long text to')
+Note	1003	select 'this is a test of some long text to see what happens' AS `xxx` from dual where ('this is a test of some long text to see what happens' regexp 'is a test of some long text to')
 select * from t1 where xxx regexp('is a test of some long text to ');
 xxx
 this is a test of some long text to see what happens
diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result
index a97e6869d09..96a7bafccfc 100644
--- a/mysql-test/r/func_test.result
+++ b/mysql-test/r/func_test.result
@@ -87,7 +87,7 @@ explain extended select - a from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select -('1') AS `- a` from `test`.`t1`
+Note	1003	select -('1') AS `- a` from dual
 drop table t1;
 select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1;
 5 between 0 and 10 between 0 and 1	(5 between 0 and 10) between 0 and 1
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index 9c3cc8fc89e..f0b934ebd3a 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -12,7 +12,7 @@ explain extended select count(a) as b from t1 where a=0 having b >=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select count('0') AS `b` from `test`.`t1` where 0 having (`b` >= 0)
+Note	1003	select count('0') AS `b` from dual where 0 having (`b` >= 0)
 drop table t1;
 CREATE TABLE t1 (
 raw_id int(10) NOT NULL default '0',
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 50b5c3c13fb..485bd4e5687 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4435,7 +4435,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select '1' AS `a`,'1' AS `b` from `test`.`t1` where 1
+Note	1003	select '1' AS `a`,'1' AS `b` from dual where 1
 SELECT * FROM t1 WHERE (a=a AND a=a) OR b > 2;
 a	b
 1	1
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index c60ac9790c5..fc0a933b732 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -50,7 +50,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having ((select '1' AS `a`) = 1)
+Note	1003	select 1 AS `1` from dual having ((select '1' AS `a`) = 1)
 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
@@ -203,7 +203,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 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` AS `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)`,'2' AS `a` from (select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` > 1)) `tt`
+Note	1003	select (select `test`.`t3`.`a` AS `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)`,'2' AS `a` from dual
 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
@@ -314,7 +314,7 @@ NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL
 Warnings:
 Note	1276	Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
 Note	1276	Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
-Note	1003	select (select '2' AS `a` from `test`.`t1` where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
+Note	1003	select (select '2' AS `a` from dual where ('2' = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
 select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
 ERROR 21000: Subquery returns more than 1 row
 create table t6 (patient_uq int, clinic_uq int, index i1 (clinic_uq));
@@ -1429,7 +1429,7 @@ explain extended (select * from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	(select 'tttt' AS `s1` from `test`.`t1`)
+Note	1003	(select 'tttt' AS `s1` from dual)
 (select * from t1);
 s1
 tttt
@@ -1617,7 +1617,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	UNION	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
 Warnings:
-Note	1003	select 'e' AS `s1` from `test`.`t1` where 1
+Note	1003	select 'e' AS `s1` from dual where 1
 drop table t1;
 CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1;
 INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874');
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index f055b40116a..f20ae6a31be 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -864,7 +864,7 @@ Level	Code	Message
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
 Note	1276	Field or reference 'test.t1.c' of SELECT #3 was resolved in SELECT #2
 Error	1054	Unknown column 'c' in 'field list'
-Note	1003	select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from (select count(`test`.`t1`.`b`) AS `COUNT(b)` from `test`.`t1`) `x` group by `c`) AS `(SELECT COUNT(a) FROM 
+Note	1003	select `c` AS `c` from (select (select count(`test`.`t1`.`a`) AS `COUNT(a)` from dual group by `c`) AS `(SELECT COUNT(a) FROM 
 (SELECT COUNT(b) FROM t1) AS x GROUP BY c
 )` from `test`.`t1` group by `test`.`t1`.`b`) `y`
 DROP TABLE t1;
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index b6281443751..054fda90c02 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -517,7 +517,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
 Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from `test`.`t1` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0))
+Note	1003	select '1' AS `id`,'2007-04-25 18:30:22' AS `cur_date` from dual where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t1` `x1` where 0))
 select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 id	cur_date
@@ -529,7 +529,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 Warnings:
 Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
-Note	1003	select '1' AS `id`,'2007-04-25' AS `cur_date` from `test`.`t2` where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0))
+Note	1003	select '1' AS `id`,'2007-04-25' AS `cur_date` from dual where <in_optimizer>('1',<exists>(select 1 AS `Not_used` from `test`.`t2` `x1` where 0))
 select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id	cur_date
diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test
index 18f1145a25d..162a310c0a6 100644
--- a/mysql-test/t/explain.test
+++ b/mysql-test/t/explain.test
@@ -167,4 +167,16 @@ flush tables;
 SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
 drop tables t1, t2;
 
+
+--echo #
+--echo # Bug#30302: Tables that were optimized away are printed in the
+--echo #            EXPLAIN EXTENDED warning.
+--echo #
+create table t1(f1 int);
+create table t2(f2 int);
+insert into t1 values(1);
+insert into t2 values(1),(2);
+explain extended select * from t1 where f1=1;
+explain extended select * from t1 join t2 on f1=f2 where f1=1;
+drop table t1,t2;
 --echo End of 5.1 tests.