MySQL Bugs: #109891: Contribution by Tencent: insert on duplicate key update would be abnormal

Bug #109891	Contribution by Tencent: insert on duplicate key update would be abnormal
Submitted:	2 Feb 2023 9:10	Modified:	3 Feb 2023 6:49
Reporter:	Quanan Han (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.30,8.0.31, 8.0.32, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Insert on duplicate key update would be abnormal when the auto-increment field is full.

1. table  ta and recoreds just like this:

mysql> show create table ta\G
*************************** 1. row ***************************
       Table: ta
Create Table: CREATE TABLE `ta` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` int unsigned DEFAULT NULL,
  `c3` int unsigned DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

mysql> select * from ta;
+------------+------+------+
| c1         | c2   | c3   |
+------------+------+------+
|         10 |   10 |   10 |
| 4294967295 |    1 |    1 |
+------------+------+------+
2 rows in set (0.00 sec)

2. Execute sql：
mysql> insert into ta(c2,c3) values(10,10) on duplicate key update c2=10, c3=10;
ERROR 1062 (23000): Duplicate entry '10' for key 'ta.c2'
mysql> insert into ta(c2,c3) values(3,3) on duplicate key update c2=3, c3=3;
Query OK, 2 rows affected (0.02 sec)

mysql> select * from ta;
+------------+------+------+
| c1         | c2   | c3   |
+------------+------+------+
|         10 |   10 |   10 |
| 4294967295 |    3 |    3 |
+------------+------+------+
2 rows in set (0.00 sec)

3. Our expected result
the following  insert sql1 would do nothing:
" insert into ta(c2,c3) values(10,10) on duplicate key update c2=10, c3=10;"

the following insert sql2 would be a insert and failed because of auto_increment is full
"insert into ta(c2,c3) values(3,3) on duplicate key update c2=3, c3=3;"

How to repeat:
just in Description

Hello Quanan Han,

Thank you for the report and feedback.

regards,
Umesh

Hi Quanan Han,

I don't agree with you.

`insert into ta(c2,c3) values(10,10) on duplicate key update c2=10, c3=10;` is equivalent to `insert into ta(c1,c2,c3) values(4294967295,10,10) on duplicate key update c1 = 4294967295, c2=10, c3=10;`, which is reasonable to throw an duplicate key error.

Hi, huahua xu
Because of the column c2 is unique key，this must be update；
Also you can also  execute this query when the autu_incmrent is not full。

`insert into ... on duplicate key update` can only handle conflicts on one uniqueness constraint, but `insert into ta(c2,c3) values(10,10) on duplicate key update c2=10, c3=10;` produces two conflicts on primary key and unique key.