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/in...urce-tree.html

ChangeSet
1.2198 05/04/19 15:12:32 ingo (AT) mysql (DOT) com +5 -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/19 15:12:29 ingo (AT) mysql (DOT) com +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/19 15:12:29 ingo (AT) mysql (DOT) com +82 -12
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/mysql_priv.h
1.345 05/04/19 15:12:29 ingo (AT) mysql (DOT) com +0 -2
Bug#7806 - insert on duplicate key and auto-update of timestamp
Made check_insert_fields() static. It is used only in sql_insert.cc.

mysql-test/t/type_timestamp.test
1.24 05/04/19 15:12:29 ingo (AT) mysql (DOT) com +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/19 15:12:29 ingo (AT) mysql (DOT) com +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.344/sql/mysql_priv.h Fri Apr 1 18:56:42 2005
+++ 1.345/sql/mysql_priv.h Tue Apr 19 15:12:29 2005
@@ -668,8 +668,6 @@
void mysql_stmt_free(THD *thd, char *packet);
void mysql_stmt_reset(THD *thd, char *packet);
void mysql_stmt_get_longdata(THD *thd, char *pos, ulong packet_length);
-int check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
- List<Item> &values, ulong counter);

/* sql_error.cc */
MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code,

--- 1.155/sql/sql_insert.cc Mon Feb 28 10:59:41 2005
+++ 1.156/sql/sql_insert.cc Tue Apr 19 15:12:29 2005
@@ -42,15 +42,29 @@
#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.
+
+ NOTE
+ 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
-check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
- List<Item> &values, ulong counter)
+static int check_insert_fields(THD *thd, TABLE *table, List<Item> &fields,
+ List<Item> &values)
{
if (fields.elements == 0 && values.elements != 0)
{
@@ -58,7 +72,7 @@
{
my_printf_error(ER_WRONG_VALUE_COUNT_ON_ROW,
ER(ER_WRONG_VALUE_COUNT_ON_ROW),
- MYF(0),counter);
+ MYF(0), 1);
return -1;
}
#ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -66,7 +80,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
@@ -74,7 +88,7 @@
{
my_printf_error(ER_WRONG_VALUE_COUNT_ON_ROW,
ER(ER_WRONG_VALUE_COUNT_ON_ROW),
- MYF(0),counter);
+ MYF(0), 1);
return -1;
}
TABLE_LIST table_list;
@@ -96,7 +110,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 +120,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.
+
+ NOTE
+ 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,
@@ -450,11 +520,11 @@
if (!table->insert_values)
DBUG_RETURN(-1);
}
- if ((values && check_insert_fields(thd, table, fields, *values, 1)) ||
+ if ((values && check_insert_fields(thd, table, fields, *values)) ||
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,
@@ -1457,7 +1527,7 @@
DBUG_ENTER("select_insert:repare");

unit= u;
- if (check_insert_fields(thd,table,*fields,values,1))
+ if (check_insert_fields(thd, table, *fields, values))
DBUG_RETURN(1);

restore_record(table,default_values); // Get empty record

--- 1.70/sql/table.h Wed Mar 30 13:57:38 2005
+++ 1.71/sql/table.h Tue Apr 19 15:12:29 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 Tue Apr 19 15:12:29 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 Tue Apr 19 15:12:29 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;
+

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw