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 

1 files changed, 5 insertions, 4 deletions

diff --git a/sql/opt_range.h b/sql/opt_range.h
index 9474f2d469f..40a95beb894 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -225,8 +225,9 @@ public:
     Return 1 if any index used by this quick select
      a) uses field that is listed in passed field list or
      b) is automatically updated (like a timestamp)
+     c) can be updated by one of before update triggers defined on table
   */
-  virtual bool check_if_keys_used(List<Item> *fields);
+  virtual bool is_keys_used(List<Item> *fields);
 
   /*
     rowid of last row retrieved by this quick select. This is used only when
@@ -423,7 +424,7 @@ public:
   int get_type() { return QS_TYPE_INDEX_MERGE; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
-  bool check_if_keys_used(List<Item> *fields);
+  bool is_keys_used(List<Item> *fields);
 #ifndef DBUG_OFF
   void dbug_dump(int indent, bool verbose);
 #endif
@@ -482,7 +483,7 @@ public:
   int get_type() { return QS_TYPE_ROR_INTERSECT; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
-  bool check_if_keys_used(List<Item> *fields);
+  bool is_keys_used(List<Item> *fields);
 #ifndef DBUG_OFF
   void dbug_dump(int indent, bool verbose);
 #endif
@@ -536,7 +537,7 @@ public:
   int get_type() { return QS_TYPE_ROR_UNION; }
   void add_keys_and_lengths(String *key_names, String *used_lengths);
   void add_info_string(String *str);
-  bool check_if_keys_used(List<Item> *fields);
+  bool is_keys_used(List<Item> *fields);
 #ifndef DBUG_OFF
   void dbug_dump(int indent, bool verbose);
 #endif