Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when JOINed

during an UPDATE

Extended the fix for bug 29310 to multi-table update:

When a table is being updated it has two set of fields - fields required for
checks of conditions and fields to be updated. A storage engine is allowed
not to retrieve columns marked for update. Due to this fact records can't
be compared to see whether the data has been changed or not. This makes the
server always update records independently of data change.
  
Now when an auto-updatable timestamp field is present and server sees that
a table handle isn't going to retrieve write-only fields then all of such
fields are marked as to be read to force the handler to retrieve them.

3 files changed, 68 insertions, 0 deletions

diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 0e691611f02..2bf1ef8fbf0 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -2350,4 +2350,32 @@ Null
 Index_type	BTREE
 Comment	
 DROP TABLE t1;
+#
+# Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when 
+#  JOINed during an UPDATE
+#
+CREATE TABLE t1 (d INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT, b INT, 
+c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
+set up our data elements
+INSERT INTO t1 (d) VALUES (1);
+INSERT INTO t2 (a,b) VALUES (1,1);
+SELECT SECOND(c) INTO @bug47453 FROM t2;
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+SECOND(c)-@bug47453
+0
+UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+SECOND(c)-@bug47453
+0
+SELECT SLEEP(1);
+SLEEP(1)
+0
+UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
+#should be 0
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+SECOND(c)-@bug47453
+0
+DROP TABLE t1, t2;
 End of 5.1 tests
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 88ca8d42e3d..9564d3b41fb 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -589,4 +589,34 @@ ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
 
 DROP TABLE t1;
 
+
+--echo #
+--echo # Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when 
+--echo #  JOINed during an UPDATE
+--echo #
+
+CREATE TABLE t1 (d INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT, b INT, 
+  c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+  ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
+
+--echo set up our data elements
+INSERT INTO t1 (d) VALUES (1);
+INSERT INTO t2 (a,b) VALUES (1,1);
+SELECT SECOND(c) INTO @bug47453 FROM t2;
+
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+
+SELECT SLEEP(1);
+
+UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
+
+--echo #should be 0
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+
+DROP TABLE t1, t2;
+
+
 --echo End of 5.1 tests
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 63af275cef3..d8141deba63 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1379,6 +1379,16 @@ int multi_update::prepare(List<Item> &not_used_values,
     {
       table->read_set= &table->def_read_set;
       bitmap_union(table->read_set, &table->tmp_set);
+      /*
+        If a timestamp field settable on UPDATE is present then to avoid wrong
+        update force the table handler to retrieve write-only fields to be able
+        to compare records and detect data change.
+        */
+      if (table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ &&
+          table->timestamp_field &&
+          (table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_UPDATE ||
+           table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH))
+        bitmap_union(table->read_set, table->write_set);
     }
   }