bk commit into 4.1 tree (ingo:1.2176) BUG#7806

[ingo]

Below is the list of changes that have just been committed into a local
4.1 repository of mydev. When mydev 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
  1.2176 05/04/15 21:17:51 ingo@stripped +4 -0
  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.

  sql/table.h
    1.71 05/04/15 21:16:22 ingo@stripped +4 -0
    Bug#7806 - insert on duplicate key and auto-update of timestamp
    Extended a comment to warn about usage of enum timestamp_auto_set_type.

  sql/sql_insert.cc
    1.156 05/04/15 21:16:22 ingo@stripped +77 -5
    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().

  mysql-test/t/type_timestamp.test
    1.24 05/04/15 21:16:22 ingo@stripped +21 -0
    Bug#7806 - insert on duplicate key and auto-update of timestamp
    The test case.

  mysql-test/r/type_timestamp.result
    1.26 05/04/15 21:16:22 ingo@stripped +47 -0
    Bug#7806 - insert on duplicate key and auto-update of timestamp
    The test result.

# 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:	ingo
# Host:	chilla.local
# Root:	/home/mydev/mysql-4.1-bug7806

--- 1.155/sql/sql_insert.cc	Mon Feb 28 10:59:41 2005
+++ 1.156/sql/sql_insert.cc	Fri Apr 15 21:16:22 2005
@@ -42,10 +42,26 @@
 #define DELAYED_LOG_UPDATE 1
 #define DELAYED_LOG_BIN    2
 
+
 /*
   Check if insert fields are correct.
-  Sets table->timestamp_field_type to TIMESTAMP_NO_AUTO_SET or leaves it
-  as is, depending on if timestamp should be updated or not.
+
+  SYNOPSIS
+    check_insert_fields()
+    thd                         The current thread.
+    table                       The table for insert.
+    fields                      The insert fields.
+    values                      The insert values.
+    counter                     the number of values.
+
+  DESCRIPTION
+    Clears TIMESTAMP_AUTO_SET_ON_INSERT from table->timestamp_field_type
+    or leaves it as is, depending on if timestamp should be updated or
+    not.
+
+  RETURN
+    0           OK
+    -1          Error
 */
 
 int
@@ -66,7 +82,7 @@
 	check_grant_all_columns(thd,INSERT_ACL,table))
       return -1;
 #endif
-    table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
+    (int) table->timestamp_field_type&= ~ (int) TIMESTAMP_AUTO_SET_ON_INSERT;
   }
   else
   {						// Part field list
@@ -96,7 +112,7 @@
     }
     if (table->timestamp_field &&	// Don't set timestamp if used
 	table->timestamp_field->query_id == thd->query_id)
-      table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
+      (int) table->timestamp_field_type&= ~ (int) TIMESTAMP_AUTO_SET_ON_INSERT;
   }
   // For the values we need select_priv
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -106,6 +122,62 @@
 }
 
 
+/*
+  Check update fields for the timestamp field.
+
+  SYNOPSIS
+    check_update_fields()
+    thd                         The current thread.
+    insert_table_list           The insert table list.
+    table                       The table for update.
+    update_fields               The update fields.
+
+  DESCRIPTION
+    If the update fields include the timestamp field,
+    remove TIMESTAMP_AUTO_SET_ON_UPDATE from table->timestamp_field_type.
+
+  RETURN
+    0           OK
+    -1          Error
+*/
+
+static int check_update_fields(THD *thd, TABLE *table,
+                               TABLE_LIST *insert_table_list,
+                               List<Item> &update_fields)
+{
+  ulong		timestamp_query_id;
+  LINT_INIT(timestamp_query_id);
+
+  /*
+    Change the query_id for the timestamp column so that we can
+    check if this is modified directly.
+  */
+  if (table->timestamp_field)
+  {
+    timestamp_query_id= table->timestamp_field->query_id;
+    table->timestamp_field->query_id= thd->query_id-1;
+  }
+
+  /*
+    Check the fields we are going to modify. This will set the query_id
+    of all used fields to the threads query_id.
+  */
+  if (setup_fields(thd, 0, insert_table_list, update_fields, 1, 0, 0))
+    return -1;
+
+  if (table->timestamp_field)
+  {
+    /* Don't set timestamp column if this is modified. */
+    if (table->timestamp_field->query_id == thd->query_id)
+      (int) table->timestamp_field_type&= ~ (int) TIMESTAMP_AUTO_SET_ON_UPDATE;
+    else
+      table->timestamp_field->query_id= timestamp_query_id;
+  }
+
+  return 0;
+}
+
+
 int mysql_insert(THD *thd,TABLE_LIST *table_list,
                  List<Item> &fields,
                  List<List_item> &values_list,
@@ -454,7 +526,7 @@
       setup_tables(insert_table_list) ||
       (values && setup_fields(thd, 0, insert_table_list, *values, 0, 0, 0)) ||
       (duplic == DUP_UPDATE &&
-       (setup_fields(thd, 0, insert_table_list, update_fields, 1, 0, 0) ||
+       (check_update_fields(thd, table, insert_table_list, update_fields) ||
         setup_fields(thd, 0, insert_table_list, update_values, 1, 0, 0))))
     DBUG_RETURN(-1);
   if (values && find_real_table_in_list(table_list->next, table_list->db,

--- 1.70/sql/table.h	Wed Mar 30 13:57:38 2005
+++ 1.71/sql/table.h	Fri Apr 15 21:16:22 2005
@@ -60,6 +60,10 @@
 /*
   Values in this enum are used to indicate during which operations value
   of TIMESTAMP field should be set to current timestamp.
+  WARNING: The values are used for bit operations. If you change the enum,
+  you must keep the bitwise relation of the values. For example:
+  (int) TIMESTAMP_AUTO_SET_ON_BOTH ==
+    (int) TIMESTAMP_AUTO_SET_ON_INSERT | (int) TIMESTAMP_AUTO_SET_ON_UPDATE.
 */
 enum timestamp_auto_set_type
 {

--- 1.25/mysql-test/r/type_timestamp.result	Sun Apr  3 08:04:59 2005
+++ 1.26/mysql-test/r/type_timestamp.result	Fri Apr 15 21:16:22 2005
@@ -432,3 +432,50 @@
 )
 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
[phone]:01:01
[phone]:02:02
[phone]:03:03
+update t1 set b = 2, c = c where a = 2;
+select * from t1;
+a	b	c
[phone]:01:01
[phone]:02:02
[phone]:03:03
+insert into t1 (a) values (4);
+select * from t1;
+a	b	c
[phone]:01:01
[phone]:02:02
[phone]: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
[phone]:01:01
[phone]:02:02
[phone]: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
[phone]:01:01
[phone]:02:02
[phone]: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
[phone]:01:01
[phone]:02:02
[phone]:03:03
[phone]:46:59
+5	NULL	2001-09-09 04:46:59
+6	NULL	2006-06-06 06:06:06
+drop table t1;

--- 1.23/mysql-test/t/type_timestamp.test	Sun Apr  3 08:04:59 2005
+++ 1.24/mysql-test/t/type_timestamp.test	Fri Apr 15 21:16:22 2005
@@ -298,3 +298,24 @@
 # 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;
+