MySQL Bugs: #111183: When use ON DUPLICATE UPDATE syntax, TIMESTAMP in primary key, get wrong answer

Bug #111183	When use ON DUPLICATE UPDATE syntax, TIMESTAMP in primary key, get wrong answer
Submitted:	29 May 2023 9:46	Modified:	29 May 2023 12:20
Reporter:	linkang zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	UPDATE DUPLICATE affected_rows

Description:
When we use ON DUPLICATE UPDATE syntax to insert data, CURRENT_TIMESTAMP in primary key, we get wrong answer.

How to repeat:
// we can repeat this by mysql-test.

// create one file named 'test_update.test' in directory mysql-test/t

// The content of 'test_update.test' is:

CREATE TABLE `test_up`(
    `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `data` int,
    PRIMARY KEY(`ts`) 
);

INSERT INTO test_up VALUES("2023-05-29 17:34:55.01", 1);

INSERT INTO test_up VALUES("2023-05-29 17:34:55.01", 2) ON DUPLICATE KEY UPDATE data=2;
INSERT INTO test_up VALUES("2023-05-29 17:34:55.01", 3) ON DUPLICATE KEY UPDATE data=3;
INSERT INTO test_up VALUES("2023-05-29 17:34:55.01", 4) ON DUPLICATE KEY UPDATE data=4;

// now, we can use this command in directory mysql-test: 
// ./mtr mysql_update

// now, the wrong answer is:

mysqltest: At line 11: Query 'INSERT INTO test_up VALUES("2023-05-29 17:34:55.01", 4) ON DUPLICATE KEY UPDATE data=4' failed.
ERROR 1062 (23000): Duplicate entry '2023-05-29 12:42:03' for key 'test_up.PRIMARY'

Hello  Mr. zhang,

Thank you for your bug report.

We ran your test case and concluded that it is a bug. Bug is not in the DML statements, but it is in not following of the definition of the TIMESTAMP in the table.

This bug is affecting, both 5.7 and latest 8.0.

Verified as reported.