Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-02-20 23:54:44+03:00, evgen@stripped +10 -0
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@stripped, 2008-02-20 23:42:57+03:00, evgen@stripped +23 -3
Added a test case for the bug#30302: Tables that were optimized away are
printed in the EXPLAIN EXTENDED warning.
mysql-test/r/func_default.result@stripped, 2008-02-20 23:42:43+03:00, evgen@stripped +1 -1
A test case was corrected after fix for the bug#30302.
mysql-test/r/func_regexp.result@stripped, 2008-02-20 23:42:42+03:00, evgen@stripped +1 -1
A test case was corrected after fix for the bug#30302.
mysql-test/r/func_test.result@stripped, 2008-02-20 23:42:40+03:00, evgen@stripped +1 -1
A test case was corrected after fix for the bug#30302.
mysql-test/r/having.result@stripped, 2008-02-20 23:42:39+03:00, evgen@stripped +1 -1
A test case was corrected after fix for the bug#30302.
mysql-test/r/subselect.result@stripped, 2008-02-20 23:42:30+03:00, evgen@stripped +5 -5
A test case was corrected after fix for the bug#30302.
mysql-test/r/type_datetime.result@stripped, 2008-02-20 23:41:42+03:00, evgen@stripped +2 -2
A test case was corrected after fix for the bug#30302.
mysql-test/t/explain.test@stripped, 2008-02-20 23:39:54+03:00, evgen@stripped +12 -0
Added a test case for the bug#30302: Tables that were optimized away are
printed in the EXPLAIN EXTENDED warning.
sql/sql_select.cc@stripped, 2008-02-20 23:54:10+03:00, evgen@stripped +29 -2
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@stripped, 2008-02-20 23:44:28+03:00, evgen@stripped +2 -0
Bug#30302: Tables that were optimized away are printed in the
EXPLAIN EXTENDED warning.
The optimized_away flag is added to the TABLE_LIST struct.
diff -Nrup a/mysql-test/r/explain.result b/mysql-test/r/explain.result
--- a/mysql-test/r/explain.result 2007-11-16 13:58:06 +03:00
+++ b/mysql-test/r/explain.result 2008-02-20 23:42:57 +03:00
@@ -64,7 +64,7 @@ explain extended select * from v1 where
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
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 Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
@@ -74,7 +74,7 @@ explain extended select * from t1 where
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
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 Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
@@ -84,7 +84,7 @@ explain extended select * from t1 having
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
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);
@@ -107,3 +107,23 @@ X X X X X X X X X
X X X X X X X X X Range checked for each record (index map: 0xFFFFFFFFFF)
DROP TABLE t2;
DROP TABLE t1;
+#
+# 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 Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+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 Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 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;
diff -Nrup a/mysql-test/r/func_default.result b/mysql-test/r/func_default.result
--- a/mysql-test/r/func_default.result 2007-03-10 00:18:44 +03:00
+++ b/mysql-test/r/func_default.result 2008-02-20 23:42:43 +03:00
@@ -8,7 +8,7 @@ explain extended select default(str), de
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
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 -Nrup a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result
--- a/mysql-test/r/func_regexp.result 2007-10-30 11:20:28 +03:00
+++ b/mysql-test/r/func_regexp.result 2008-02-20 23:42:42 +03:00
@@ -52,7 +52,7 @@ explain extended select * from t1 where
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
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 _latin1'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 _latin1'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 -Nrup a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result
--- a/mysql-test/r/func_test.result 2007-03-10 00:18:42 +03:00
+++ b/mysql-test/r/func_test.result 2008-02-20 23:42:40 +03:00
@@ -87,7 +87,7 @@ explain extended select - a from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
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 -Nrup a/mysql-test/r/having.result b/mysql-test/r/having.result
--- a/mysql-test/r/having.result 2007-07-21 23:34:03 +04:00
+++ b/mysql-test/r/having.result 2008-02-20 23:42:39 +03:00
@@ -12,7 +12,7 @@ explain extended select count(a) as b fr
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE 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 -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result 2008-01-20 13:46:54 +03:00
+++ b/mysql-test/r/subselect.result 2008-02-20 23:42:30 +03:00
@@ -50,7 +50,7 @@ id select_type table type possible_keys
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
3 DERIVED t2 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3 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 NU
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 Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
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
3 UNION t1 system NULL NULL NULL NULL 1
NULL UNION RESULT <union2,3> ALL 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 -Nrup a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
--- a/mysql-test/r/type_datetime.result 2007-12-09 00:04:56 +03:00
+++ b/mysql-test/r/type_datetime.result 2008-02-20 23:41:42 +03:00
@@ -517,7 +517,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY 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
2 DEPENDENT SUBQUERY 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 -Nrup a/mysql-test/t/explain.test b/mysql-test/t/explain.test
--- a/mysql-test/t/explain.test 2007-11-16 13:58:06 +03:00
+++ b/mysql-test/t/explain.test 2008-02-20 23:39:54 +03:00
@@ -94,4 +94,16 @@ EXPLAIN SELECT 1 FROM
DROP TABLE t2;
DROP TABLE t1;
+--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;
+
# End of 5.0 tests.
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2008-02-18 18:12:32 +03:00
+++ b/sql/sql_select.cc 2008-02-20 23:54:10 +03:00
@@ -2536,7 +2536,10 @@ make_join_statistics(JOIN *join, TABLE_L
DBUG_RETURN(1); // Fatal error
}
else
+ {
found_const_table_map|= s->table->map;
+ s->table->pos_in_table_list->optimized_away= TRUE;
+ }
}
/* loop until no more const tables are found */
@@ -15543,7 +15546,20 @@ static void print_join(THD *thd, String
{
/* List is reversed => we should reverse it before using */
List_iterator_fast<TABLE_LIST> ti(*tables);
- TABLE_LIST **table= (TABLE_LIST **)thd->alloc(sizeof(TABLE_LIST*) *
+ TABLE_LIST **table;
+ uint non_const_tables= 0;
+
+ for (TABLE_LIST *t= ti++; t ; t= ti++)
+ if (!t->optimized_away)
+ non_const_tables++;
+ if (!non_const_tables)
+ {
+ str->append(STRING_WITH_LEN("dual"));
+ return; // all tables were optimized away
+ }
+ ti.rewind();
+
+ table= (TABLE_LIST **)thd->alloc(sizeof(TABLE_LIST*) *
tables->elements);
if (table == 0)
return; // out of memory
@@ -15551,10 +15567,21 @@ static void print_join(THD *thd, String
for (TABLE_LIST **t= table + (tables->elements - 1); t >= table; t--)
*t= ti++;
+ /*
+ Skip tables that were optimized away from printing them by the
+ EXPLAIN EXTENDED.
+ */
+ for (uint s= 0, e= 0; e < tables->elements; e++)
+ {
+ if (table[e]->optimized_away)
+ continue;
+ table[s++]= table[e];
+ }
+
DBUG_ASSERT(tables->elements >= 1);
(*table)->print(thd, str);
- TABLE_LIST **end= table + tables->elements;
+ TABLE_LIST **end= table + non_const_tables;
for (TABLE_LIST **tbl= table + 1; tbl < end; tbl++)
{
TABLE_LIST *curr= *tbl;
diff -Nrup a/sql/table.h b/sql/table.h
--- a/sql/table.h 2007-09-24 16:34:09 +04:00
+++ b/sql/table.h 2008-02-20 23:44:28 +03:00
@@ -719,6 +719,8 @@ struct TABLE_LIST
the parsed tree is created.
*/
uint8 trg_event_map;
+ /* TRUE <=> this table is a const one and was optimized away. */
+ bool optimized_away;
enum enum_schema_table_state schema_table_state;
void calc_md5(char *buffer);
| Thread |
|---|
| • bk commit into 5.0 tree (evgen:1.2584) BUG#30302 | eugene | 20 Feb |
