Fix for bug#20670 "UPDATE using key and invoking trigger that modifies

this key does not stop" (version for 5.0 only).

UPDATE statement which WHERE clause used key and which invoked trigger
that modified field in this key worked indefinetely.

This problem occured because in cases when UPDATE statement was
executed in update-on-the-fly mode (in which row is updated right
during evaluation of select for WHERE clause) the new version of
the row became visible to select representing WHERE clause and was
updated again and again.
We already solve this problem for UPDATE statements which does not
invoke triggers by detecting the fact that we are going to update
field in key used for scanning and performing update in two steps,
during the first step we gather information about the rows to be
updated and then doing actual updates. We also do this for
MULTI-UPDATE and in its case we even detect situation when such
fields are updated in triggers (actually we simply assume that
we always update fields used in key if we have before update
trigger).

The fix simply extends this check which is done in check_if_key_used()/
QUICK_SELECT_I::check_if_keys_used() routine/method in such way that
it also detects cases when field used in key is updated in trigger.
As nice side-effect we have more precise and thus more optimal
perfomance-wise check for the MULTI-UPDATE.
Also check_if_key_used()/QUICK_SELECT_I::check_if_keys_used() were
renamed to is_key_used()/QUICK_SELECT_I::is_keys_used() in order to
better reflect that boolean predicate.

Note that this check is implemented in much more elegant way in 5.1 


mysql-test/r/trigger.result:
  Added test case for bug#20670 "UPDATE using key and invoking trigger that
  modifies this key does not stop".
mysql-test/t/trigger.test:
  Added test case for bug#20670 "UPDATE using key and invoking trigger that
  modifies this key does not stop".
sql/key.cc:
  Renamed check_if_key_used() to is_key_used(). Also this routine checks if
  key uses field which can be updated by before update trigger defined on the
  table. As result we avoid using update-on-the-fly method in cases when trigger
  updates part of key which is used by select which filters rows to be updated
  and thus avoid infinite updates. By doing such check here we cover both UPDATE
  and MULTI-UPDATE cases.
sql/mysql_priv.h:
  Renamed check_if_key_used() to is_key_used().
sql/opt_range.cc:
  Renamed check_if_key_used()/QUICK_SELECT_I::check_if_keys_used() to
  is_key_used()/QUICK_SELECT_I::is_keys_used().
sql/opt_range.h:
  Renamed QUICK_SELECT_I::check_if_keys_used() method to is_keys_used(),
  also updated comment describing it to reflect its extended semantics
  (this change was caused by change in check_if_key_used()/is_key_used()
   routine semantics).
sql/sql_trigger.cc:
  Introduced Table_triggers_list::is_updated_in_before_update_triggers()
  method which is needed for checking if field of subject table can be
  changed in before update trigger.
sql/sql_trigger.h:
  Table_triggers_list:
    Removed has_before_update_triggers() method which is not used any longer.
    Added declaration of is_updated_in_before_update_triggers() which is
    needed for checking if field of subject table can be changed by before
    update trigger.
sql/sql_update.cc:
  safe_update_on_fly():
    check_if_key_used() routine and check_if_keys_used() method were
    renamed to is_key_used()/is_keys_used(). 
    Now cases when trigger updates fields which are part of key used for
    filtering rows for update are caught directly in is_key_used().
    This also allows to cover both UPDATE and MULTI-UPDATE cases.

1 files changed, 32 insertions, 0 deletions

diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 6bb50d602c3..c0c9c34cbeb 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -1548,6 +1548,38 @@ void Table_triggers_list::mark_fields_used(THD *thd, trg_event_type event)
 
 
 /*
+  Check if field of subject table can be changed in before update trigger.
+
+  SYNOPSIS
+    is_updated_in_before_update_triggers()
+      field  Field object for field to be checked
+
+  NOTE
+    Field passed to this function should be bound to the same
+    TABLE object as Table_triggers_list.
+
+  RETURN VALUE
+    TRUE   Field is changed
+    FALSE  Otherwise
+*/
+
+bool Table_triggers_list::is_updated_in_before_update_triggers(Field *fld)
+{
+  Item_trigger_field *trg_fld;
+  for (trg_fld= trigger_fields[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE];
+       trg_fld != 0;
+       trg_fld= trg_fld->next_trg_field)
+  {
+    if (trg_fld->get_settable_routine_parameter() &&
+        trg_fld->field_idx != (uint)-1 &&
+        table->field[trg_fld->field_idx]->eq(fld))
+      return TRUE;
+  }
+  return FALSE;
+}
+
+
+/*
   Trigger BUG#14090 compatibility hook
 
   SYNOPSIS