bk commit into 5.0 tree (evgen:1.2485) BUG#27006

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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, 2007-03-16 17:23:26+03:00, evgen@stripped +3 -0
  Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY
  UPDATE if the row wasn't actually changed.
  
  This bug was caused by fix for bug#19978. It causes AFTER UPDATE triggers
  not firing if a row wasn't actually changed by the update part of the
  INSERT .. ON DUPLICATE KEY UPDATE.
  
  Now triggers are always fired if a row is touched by the INSERT ... ON
  DUPLICATE KEY UPDATE.

  mysql-test/r/trigger.result@stripped, 2007-03-16 17:21:22+03:00, evgen@stripped +41 -0
    Added a test case for the bug#27006: AFTER UPDATE triggers not fired with INSERT ...
ON DUPLICATE KEY
    UPDATE if the row wasn't actually changed.

  mysql-test/t/trigger.test@stripped, 2007-03-16 17:21:43+03:00, evgen@stripped +38 -0
    Added a test case for the bug#27006: AFTER UPDATE triggers not fired with INSERT ...
ON DUPLICATE KEY
    UPDATE if the row wasn't actually changed.

  sql/sql_insert.cc@stripped, 2007-03-16 17:20:54+03:00, evgen@stripped +6 -6
    Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY
    UPDATE if the row wasn't actually changed.
    Now triggers are always fired if a row is touched by the INSERT ... ON
    DUPLICATE KEY UPDATE.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/27006-bug-5.0-opt-mysql

--- 1.223/sql/sql_insert.cc	2007-03-16 11:47:51 +03:00
+++ 1.224/sql/sql_insert.cc	2007-03-16 17:20:54 +03:00
@@ -1238,19 +1238,19 @@
         if (table->next_number_field)
           table->file->adjust_next_insert_id_after_explicit_value(
             table->next_number_field->val_int());
-        info->touched++;
 
+        info->touched++;
         if ((table->file->table_flags() & HA_PARTIAL_COLUMN_READ) ||
             compare_record(table, thd->query_id))
         {
           info->updated++;
-
-          trg_error= (table->triggers &&
-                      table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
-                                                        TRG_ACTION_AFTER,
-                                                        TRUE));
           info->copied++;
         }
+
+        trg_error= (table->triggers &&
+                    table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+                                                      TRG_ACTION_AFTER,
+                                                      TRUE));
         goto ok_or_after_trg_err;
       }
       else /* DUP_REPLACE */

--- 1.55/mysql-test/r/trigger.result	2007-03-07 00:30:25 +03:00
+++ 1.56/mysql-test/r/trigger.result	2007-03-16 17:21:22 +03:00
@@ -1372,4 +1372,45 @@
 DROP TABLE bug22580_t1;
 DROP PROCEDURE bug22580_proc_1;
 DROP PROCEDURE bug22580_proc_2;
+DROP TRIGGER IF EXISTS trg27006_a_update;
+DROP TRIGGER IF EXISTS trg27006_a_insert;
+CREATE TABLE t1 (
+`id` int(10) unsigned NOT NULL auto_increment,
+`val` varchar(10) NOT NULL,
+PRIMARY KEY  (`id`)
+);
+CREATE TABLE t2 like t1;
+CREATE TRIGGER trg27006_a_insert AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+insert into t2 values (NULL,new.val);
+END |
+CREATE TRIGGER trg27006_a_update AFTER UPDATE ON t1 FOR EACH ROW
+BEGIN
+insert into t2 values (NULL,new.val);
+END |
+INSERT INTO t1(val) VALUES ('test1'),('test2');
+SELECT * FROM t1;
+id	val
+1	test1
+2	test2
+SELECT * FROM t2;
+id	val
+1	test1
+2	test2
+INSERT INTO t1 VALUES (2,'test2') ON DUPLICATE KEY UPDATE val=VALUES(val);
+INSERT INTO t1 VALUES (3,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val);
+SELECT * FROM t1;
+id	val
+1	test1
+2	test2
+3	test3
+SELECT * FROM t2;
+id	val
+1	test1
+2	test2
+3	test2
+4	test3
+DROP TRIGGER trg27006_a_insert;
+DROP TRIGGER trg27006_a_update;
+drop table t1,t2;
 End of 5.0 tests

--- 1.62/mysql-test/t/trigger.test	2007-03-07 00:30:25 +03:00
+++ 1.63/mysql-test/t/trigger.test	2007-03-16 17:21:43 +03:00
@@ -1699,4 +1699,42 @@
 DROP PROCEDURE bug22580_proc_1;
 DROP PROCEDURE bug22580_proc_2;
 
+#
+# Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY
+#            UPDATE if the row wasn't actually changed.
+#
+--disable_warnings
+DROP TRIGGER IF EXISTS trg27006_a_update;
+DROP TRIGGER IF EXISTS trg27006_a_insert;
+--enable_warnings
+
+CREATE TABLE t1 (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `val` varchar(10) NOT NULL,
+  PRIMARY KEY  (`id`)
+);
+CREATE TABLE t2 like t1;
+DELIMITER |;
+
+CREATE TRIGGER trg27006_a_insert AFTER INSERT ON t1 FOR EACH ROW
+BEGIN
+    insert into t2 values (NULL,new.val);
+END |
+CREATE TRIGGER trg27006_a_update AFTER UPDATE ON t1 FOR EACH ROW
+BEGIN
+    insert into t2 values (NULL,new.val);
+END |
+DELIMITER ;|
+
+INSERT INTO t1(val) VALUES ('test1'),('test2');
+SELECT * FROM t1;
+SELECT * FROM t2;
+INSERT INTO t1 VALUES (2,'test2') ON DUPLICATE KEY UPDATE val=VALUES(val);
+INSERT INTO t1 VALUES (3,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val);
+SELECT * FROM t1;
+SELECT * FROM t2;
+DROP TRIGGER trg27006_a_insert;
+DROP TRIGGER trg27006_a_update;
+drop table t1,t2;
+
 --echo End of 5.0 tests