Bug #33992 Date column not named in update statement is set to current date

• Submitted: 22 Jan 2008 20:19
• Modified: 22 Jan 2008 21:19
• Reporter: Bob Hucker
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.45
• OS: Any
• CPU Architecture: Any
• Tags: update incorrect result

[22 Jan 2008 20:19] Bob Hucker

Description:
When I tried to update a table containing a date, the existing value was replaced with the current date even though I did not specify that column among those to be updated. Isn't that a bug?

The minimal test below shows that if I try to fill in the null column "display_name" with the value from "user_name," the "created" column is reset to the current date. If I uncomment the line ", created = created" then the query acts as expected. I don't see any reason I should need to specify writing the current value back to the created column.

How to repeat:

drop table if exists test_users;
create table if not exists test_users (
	user_name varchar(20),
	created timestamp,
	display_name varchar(60)
);

insert into test_users (user_name, created)
select * from (
select 'Mary', '2007-12-01 12:05:00' union
select 'Bob',  '2007-09-01 10:30:00' union
select 'Joe',  '2006-06-30 14:12:00'
) users;

select * from test_users;
/*
Mary	2007-12-01 12:05:00	(NULL)
Bob	2007-09-01 10:30:00	(NULL)
Joe	2006-06-30 14:12:00	(NULL)
*/

update test_users
set display_name = user_name
-- , created = created
where display_name is null;

select * from test_users;
/*
Mary	2008-01-22 12:02:25	Mary
Bob	2008-01-22 12:02:25	Bob
Joe	2008-01-22 12:02:25	Joe
*/

Suggested fix:
Fix the update statement so that it operates only on columns named in the column list.

[22 Jan 2008 20:27] Paul DuBois

You don't have a DATE column, you have a TIMESTAMP column,
and it is behaving as expected:

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

You might want to use DATETIME instead.

[22 Jan 2008 21:19] Bob Hucker

Thank you for the quick answer. Apparently creating the timestamp column with 	DEFAULT 0 also solves my problem.

I have read the discussion in the documentation, but does this behavior really make any sense? Is it true that for this one data type, the update statement works differently than for other data types?