Bug#7806 - insert on duplicate key and auto-update of timestamp

Modified the check for the timestamp field so that the flags for
the automatic for inserts and updates are cleared independently.


mysql-test/r/type_timestamp.result:
  Bug#7806 - insert on duplicate key and auto-update of timestamp
  The test result.
mysql-test/t/type_timestamp.test:
  Bug#7806 - insert on duplicate key and auto-update of timestamp
  The test case.
sql/mysql_priv.h:
  Bug#7806 - insert on duplicate key and auto-update of timestamp
  Made check_insert_fields() static. It is used only in sql_insert.cc.
sql/sql_insert.cc:
  Bug#7806 - insert on duplicate key and auto-update of timestamp
  Modified the check of the insert fields so that an explicit
  assignment of the timestamp field does only disable the automatic
  for inserts and retains the automatic for updates.
  Added a check if the update fields contain the timestamp field.
  In this case, the automatic on update is disabled, but not the
  automatic on insert. This is called from mysql_prepare_insert().
sql/table.h:
  Bug#7806 - insert on duplicate key and auto-update of timestamp
  Extended a comment to warn about usage of enum timestamp_auto_set_type.

2 files changed, 68 insertions, 0 deletions

diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index 6c46d308e7e..c0396e4640d 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -432,3 +432,50 @@ t1	CREATE TABLE "t1" (
 )
 set sql_mode='';
 drop table t1;
+create table t1 (a int auto_increment primary key, b int, c timestamp);
+insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
+(2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
+select * from t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	0	2002-02-02 02:02:02
+3	0	2003-03-03 03:03:03
+update t1 set b = 2, c = c where a = 2;
+select * from t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	0	2003-03-03 03:03:03
+insert into t1 (a) values (4);
+select * from t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	0	2003-03-03 03:03:03
+4	NULL	2001-09-09 04:46:59
+update t1 set c = '2004-04-04 04:04:04' where a = 4;
+select * from t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	0	2003-03-03 03:03:03
+4	NULL	2004-04-04 04:04:04
+insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
+select * from t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	3	2003-03-03 03:03:03
+4	NULL	2004-04-04 04:04:04
+5	NULL	2001-09-09 04:46:59
+insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
+(6, '2006-06-06 06:06:06') on duplicate key update b = 4;
+select * from t1;
+a	b	c
+1	0	2001-01-01 01:01:01
+2	2	2002-02-02 02:02:02
+3	3	2003-03-03 03:03:03
+4	4	2001-09-09 04:46:59
+5	NULL	2001-09-09 04:46:59
+6	NULL	2006-06-06 06:06:06
+drop table t1;
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index 783e310f02d..3c7f07dfbce 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -298,3 +298,24 @@ show create table t1;
 # restore default mode
 set sql_mode='';
 drop table t1;
+
+#
+# Bug#7806 - insert on duplicate key and auto-update of timestamp
+#
+create table t1 (a int auto_increment primary key, b int, c timestamp);
+insert into t1 (a, b, c) values (1, 0, '2001-01-01 01:01:01'),
+  (2, 0, '2002-02-02 02:02:02'), (3, 0, '2003-03-03 03:03:03');
+select * from t1;
+update t1 set b = 2, c = c where a = 2;
+select * from t1;
+insert into t1 (a) values (4);
+select * from t1;
+update t1 set c = '2004-04-04 04:04:04' where a = 4;
+select * from t1;
+insert into t1 (a) values (3), (5) on duplicate key update b = 3, c = c;
+select * from t1;
+insert into t1 (a, c) values (4, '2004-04-04 00:00:00'),
+  (6, '2006-06-06 06:06:06') on duplicate key update b = 4;
+select * from t1;
+drop table t1;
+