At file:///home/psergey/dev/maria-5.1-table-elim-r11/ ------------------------------------------------------------ revno: 2742 revision-id: psergey@askmonty.org-20090824081242-32o90vv8awk27sut parent: psergey@askmonty.org-20090821133606-2t7hib7wuctqller committer: Sergey Petrunya <psergey@askmonty.org> branch nick: maria-5.1-table-elim-r11 timestamp: Mon 2009-08-24 10:12:42 +0200 message: MWL#17: Table elimination - Correctly handle the case where we have multi-table DELETE and a table that we're deleting from looks like it could be eliminated. === modified file 'mysql-test/r/table_elim.result' --- a/mysql-test/r/table_elim.result 2009-08-21 07:48:22 +0000 +++ b/mysql-test/r/table_elim.result 2009-08-24 08:12:42 +0000 @@ -278,3 +278,32 @@ 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index 1 SIMPLE t2 ref a a 3 test.t1.a 2 drop table t1, t2; +# +# check UPDATE/DELETE that look like they could be eliminated +# +create table t1 (a int primary key, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2 like t1; +insert into t2 select * from t1; +update t1 left join t2 using (a) set t2.a=t2.a+100; +select * from t1; +a b +1 1 +2 2 +3 3 +select * from t2; +a b +101 1 +102 2 +103 3 +delete from t2; +insert into t2 select * from t1; +delete t2 from t1 left join t2 using (a); +select * from t1; +a b +1 1 +2 2 +3 3 +select * from t2; +a b +drop table t1, t2; === modified file 'mysql-test/t/table_elim.test' --- a/mysql-test/t/table_elim.test 2009-08-21 07:48:22 +0000 +++ b/mysql-test/t/table_elim.test 2009-08-24 08:12:42 +0000 @@ -229,3 +229,23 @@ drop table t1, t2; +--echo # +--echo # check UPDATE/DELETE that look like they could be eliminated +--echo # +create table t1 (a int primary key, b int); +insert into t1 values (1,1),(2,2),(3,3); + +create table t2 like t1; +insert into t2 select * from t1; +update t1 left join t2 using (a) set t2.a=t2.a+100; +select * from t1; +select * from t2; + +delete from t2; +insert into t2 select * from t1; + +delete t2 from t1 left join t2 using (a); +select * from t1; +select * from t2; +drop table t1, t2; + === modified file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 2009-08-21 13:36:06 +0000 +++ b/sql/opt_table_elimination.cc 2009-08-24 08:12:42 +0000 @@ -1069,16 +1069,26 @@ if (join->select_lex == &thd->lex->select_lex) { - /* Multi-table UPDATE and DELETE: don't eliminate the tables we modify: */ - used_tables |= thd->table_map_for_update; /* Multi-table UPDATE: don't eliminate tables referred from SET statement */ if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI) { + /* Multi-table UPDATE and DELETE: don't eliminate the tables we modify: */ + used_tables |= thd->table_map_for_update; List_iterator<Item> it2(thd->lex->value_list); while ((item= it2++)) used_tables |= item->used_tables(); } + + if (thd->lex->sql_command == SQLCOM_DELETE_MULTI) + { + TABLE_LIST *tbl; + for (tbl= (TABLE_LIST*)thd->lex->auxiliary_table_list.first; + tbl; tbl= tbl->next_local) + { + used_tables |= tbl->table->map; + } + } } table_map all_tables= join->all_tables_map();