Description:
Given a table with two or more TIMESTAMP fields, any SQL UPDATE of a row in the table will always update the row's first TIMESTAMP field, even though none of the TIMESTAMP columns are participating in the SET clause of the UPDATE statement. The updated TIMESTAMP column value is set to the current date and time. The second and subsequent TIMESTAMP fields are unaffected by the update, but the first field is always updated.
How to repeat:
Given the following table schema:
CREATE TABLE timestamp_example (
id BIGINT NOT NULL AUTO_INCREMENT,
timestamp1 TIMESTAMP NULL DEFAULT 00000000000000,
timestamp2 TIMESTAMP NULL DEFAULT 00000000000000,
timestamp3 TIMESTAMP NULL DEFAULT 00000000000000,
update_value int NOT NULL,
PRIMARY KEY (id)
);
Please note that I observed, through the use of DbVisualizer 4.1, that the COLUMN_DEF is different between timestamp1 field and the timestamp2 and timestamp3 fields. The COLUMN_DEF is null for the timestamp1 field, but set to '00000000000000' for the other two fields. Seems strange, since they're all defined the same.
Now insert one row into the table, setting all the timestamps to the current date and time:
INSERT INTO timestamp_example (timestamp1, timestamp2, timestamp3, update_value)
VALUES (NOW(), NOW(), NOW(), 1);
Now update the update_value in that single row in the table:
UPDATE timestamp_example SET update_value = 2
Observe the timestamp fields now. The first timestamp field's value has changed, even though you did not explicitly set it in the UPDATE statement.
