bk commit into 5.1 tree (davi:1.2546) BUG#23771

[Davi Arnaut]

Below is the list of changes that have just been committed into a local
5.1 repository of davi.  When davi does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-02-12 09:44:27-02:00, davi@stripped +3 -0
  Bug#23771 AFTER UPDATE trigger not invoked when there are no changes of the data
  
  The problem is that AFTER UPDATE triggers will fire only if the
  new data is different from the old data on the row. The trigger
  should fire regardless of whether there are changes to the data.
  
  The solution is to fire the trigger on UPDATE even if there are
  no changes to the value (because the value is the same).

  mysql-test/r/trigger.result@stripped, 2008-02-12 09:44:24-02:00, davi@stripped +38 -0
    Add test case result for Bug#23771

  mysql-test/t/trigger.test@stripped, 2008-02-12 09:44:24-02:00, davi@stripped +33 -0
    Add test case for Bug#23771

  sql/sql_update.cc@stripped, 2008-02-12 09:44:24-02:00, davi@stripped +17 -17
    Move the invocation of the after update trigger so that
    the trigger will fire even if the records are the same.

diff -Nrup a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
--- a/mysql-test/r/trigger.result	2007-11-29 09:42:23 -02:00
+++ b/mysql-test/r/trigger.result	2008-02-12 09:44:24 -02:00
@@ -2016,4 +2016,42 @@ i	j
 10	10
 unlock tables;
 drop table t1;
+drop table if exists t1, t2;
+drop trigger if exists trg1;
+drop trigger if exists trg2;
+create table t1 (a int);
+create table t2 (b int);
+create trigger trg1 after update on t1 for each row set @a= @a+1;
+create trigger trg2 after update on t2 for each row set @b= @b+1;
+insert into t1 values (1), (2), (3);
+insert into t2 values (1), (2), (3);
+set @a= 0;
+set @b= 0;
+update t1, t2 set t1.a= t1.a, t2.b= t2.b;
+select @a, @b;
+@a	@b
+3	3
+update t1, t2 set t1.a= t2.b, t2.b= t1.a;
+select @a, @b;
+@a	@b
+6	6
+update t1 set a= a;
+select @a, @b;
+@a	@b
+9	6
+update t2 set b= b;
+select @a, @b;
+@a	@b
+9	9
+update t1 set a= 1;
+select @a, @b;
+@a	@b
+12	9
+update t2 set b= 1;
+select @a, @b;
+@a	@b
+12	12
+drop trigger trg1;
+drop trigger trg2;
+drop table t1, t2;
 End of 5.1 tests.
diff -Nrup a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
--- a/mysql-test/t/trigger.test	2007-11-29 09:42:23 -02:00
+++ b/mysql-test/t/trigger.test	2008-02-12 09:44:24 -02:00
@@ -2304,4 +2304,37 @@ unlock tables;
 
 drop table t1;
 
+#
+# Bug#23771 AFTER UPDATE trigger not invoked when there are no changes of the data
+#
+
+--disable_warnings
+drop table if exists t1, t2;
+drop trigger if exists trg1;
+drop trigger if exists trg2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (b int);
+create trigger trg1 after update on t1 for each row set @a= @a+1;
+create trigger trg2 after update on t2 for each row set @b= @b+1;
+insert into t1 values (1), (2), (3);
+insert into t2 values (1), (2), (3);
+set @a= 0;
+set @b= 0;
+update t1, t2 set t1.a= t1.a, t2.b= t2.b;
+select @a, @b;
+update t1, t2 set t1.a= t2.b, t2.b= t1.a;
+select @a, @b;
+update t1 set a= a;
+select @a, @b;
+update t2 set b= b;
+select @a, @b;
+update t1 set a= 1;
+select @a, @b;
+update t2 set b= 1;
+select @a, @b;
+drop trigger trg1;
+drop trigger trg2;
+drop table t1, t2;
+
 --echo End of 5.1 tests.
diff -Nrup a/sql/sql_update.cc b/sql/sql_update.cc
--- a/sql/sql_update.cc	2008-01-25 15:15:00 -02:00
+++ b/sql/sql_update.cc	2008-02-12 09:44:24 -02:00
@@ -643,14 +643,6 @@ int mysql_update(THD *thd,
             updated++;
           else
             error= 0;
-
-          if (table->triggers &&
-              table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
-                                                TRG_ACTION_AFTER, TRUE))
-          {
-            error= 1;
-            break;
-          }
 	}
  	else if (!ignore ||
                  table->file->is_fatal_error(error, HA_CHECK_DUP_KEY))
@@ -669,6 +661,14 @@ int mysql_update(THD *thd,
 	}
       }
 
+      if (table->triggers &&
+          table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+                                            TRG_ACTION_AFTER, TRUE))
+      {
+        error= 1;
+        break;
+      }
+
       if (!--limit && using_limit)
       {
         /*
@@ -1644,12 +1644,12 @@ bool multi_update::send_data(List<Item> 
             trans_safe= 0;
             thd->transaction.stmt.modified_non_trans_table= TRUE;
           }
-          if (table->triggers &&
-              table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
-                                                TRG_ACTION_AFTER, TRUE))
-            DBUG_RETURN(1);
         }
       }
+      if (table->triggers &&
+          table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+                                            TRG_ACTION_AFTER, TRUE))
+        DBUG_RETURN(1);
     }
     else
     {
@@ -1881,12 +1881,12 @@ int multi_update::do_updates()
           updated++;
         else
           local_error= 0;
-
-        if (table->triggers &&
-            table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
-                                              TRG_ACTION_AFTER, TRUE))
-          goto err2;
       }
+
+      if (table->triggers &&
+          table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+                                            TRG_ACTION_AFTER, TRUE))
+        goto err2;
     }
 
     if (updated != org_updated)