Bug#43580: Issue with Innodb on multi-table update

                        
Certain multi-updates gave different results on InnoDB from
to MyISAM, due to on-the-fly updates being used on the former and
the update order matters.
Fixed by turning off on-the-fly updates when update order 
dependencies are present.


mysql-test/r/innodb_mysql.result:
  Bug#43580: Test result.
mysql-test/suite/rpl/r/rpl_slave_skip.result:
  Bug#43580: Changed test result. The InnoDB result is now what it would have been on MyISAM.
mysql-test/t/innodb_mysql.test:
  Bug#43580: Test case.
sql/sql_base.cc:
  Bug#43580: Added a word of caution about using tmp_set here.
sql/sql_update.cc:
  Bug#43580: Fix.
  Calls to TABLE::mark_columns_needed_for_update() are moved
  from mysql_multi_update_prepare() and right before the decison
  to do on-the-fly updates to the place where we do (or don't do)
  on-the-fly updates.

1 files changed, 40 insertions, 5 deletions

diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index b3bd5d0bc57..4b313badb6b 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1031,7 +1031,6 @@ reopen_tables:
         DBUG_RETURN(TRUE);
       }
 
-      table->mark_columns_needed_for_update();
       DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
       /*
         If table will be updated we should not downgrade lock for it and
@@ -1275,12 +1274,40 @@ int multi_update::prepare(List<Item> &not_used_values,
   }
 
   /*
+    We gather the set of columns read during evaluation of SET expression in
+    TABLE::tmp_set by pointing TABLE::read_set to it and then restore it after
+    setup_fields().
+  */
+  for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
+  {
+    TABLE *table= table_ref->table;
+    if (tables_to_update & table->map)
+    {
+      DBUG_ASSERT(table->read_set == &table->def_read_set);
+      table->read_set= &table->tmp_set;
+      bitmap_clear_all(table->read_set);
+    }
+  }
+
+  /*
     We have to check values after setup_tables to get covering_keys right in
     reference tables
   */
 
-  if (setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0))
-    DBUG_RETURN(1);
+  int error= setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0);
+
+  for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
+  {
+    TABLE *table= table_ref->table;
+    if (tables_to_update & table->map)
+    {
+      table->read_set= &table->def_read_set;
+      bitmap_union(table->read_set, &table->tmp_set);
+    }
+  }
+  
+  if (error)
+    DBUG_RETURN(1);    
 
   /*
     Save tables beeing updated in update_tables
@@ -1375,6 +1402,8 @@ int multi_update::prepare(List<Item> &not_used_values,
     a row in this table will never be read twice. This is true under
     the following conditions:
 
+    - No column is both written to and read in SET expressions.
+
     - We are doing a table scan and the data is in a separate file (MyISAM) or
       if we don't update a clustered key.
 
@@ -1389,6 +1418,9 @@ int multi_update::prepare(List<Item> &not_used_values,
   WARNING
     This code is a bit dependent of how make_join_readinfo() works.
 
+    The field table->tmp_set is used for keeping track of which fields are
+    read during evaluation of the SET expression. See multi_update::prepare.
+
   RETURN
     0		Not safe to update
     1		Safe to update
@@ -1409,6 +1441,8 @@ static bool safe_update_on_fly(THD *thd, JOIN_TAB *join_tab,
   case JT_REF_OR_NULL:
     return !is_key_used(table, join_tab->ref.key, table->write_set);
   case JT_ALL:
+    if (bitmap_is_overlapping(&table->tmp_set, table->write_set))
+      return FALSE;
     /* If range search on index */
     if (join_tab->quick)
       return !join_tab->quick->is_keys_used(table->write_set);
@@ -1464,17 +1498,18 @@ multi_update::initialize_tables(JOIN *join)
     ORDER     group;
     TMP_TABLE_PARAM *tmp_param;
 
-    table->mark_columns_needed_for_update();
     if (ignore)
       table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
     if (table == main_table)			// First table in join
     {
       if (safe_update_on_fly(thd, join->join_tab, table_ref, all_tables))
       {
-	table_to_update= main_table;		// Update table on the fly
+        table->mark_columns_needed_for_update();
+	table_to_update= table;			// Update table on the fly
 	continue;
       }
     }
+    table->mark_columns_needed_for_update();
     table->prepare_for_position();
 
     /*