maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

At file:///home/psergey/dev/maria-5.1-table-elim-r11/

------------------------------------------------------------
revno: 2742
revision-id: psergey@xxxxxxxxxxxx-20090824081242-32o90vv8awk27sut
parent: psergey@xxxxxxxxxxxx-20090821133606-2t7hib7wuctqller
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
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();