From: Date: April 15 2005 9:18pm Subject: bk commit into 4.1 tree (ingo:1.2176) BUG#7806 List-Archive: http://lists.mysql.com/internals/24061 X-Bug: 7806 Message-Id: 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 &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 &fields, List &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 +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; --- 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; +