Bug #103632 Can't write; duplicate key in table
Submitted: 8 May 12:23 Modified: 8 May 13:12
Reporter: Luuk V Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.23, 8.0.24, 5.7.34 OS:Ubuntu
Assigned to: CPU Architecture:Any

[8 May 12:23] Luuk V
Description:
When running next query, I am receiving a duplicate key in table.

ERROR 1022 (23000): Can't write; duplicate key in table 'C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp\#sql65c0_d_10f'

How to repeat:
DROP TABLE IF EXISTS ints;
CREATE TABLE ints (i INT primary key);
INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

SELECT r, count(*) 
FROM (
  SELECT i, floor(rand()*4)+1 r 
  FROM ints 
  WHERE i<=10) x 
GROUP BY x.r ;

Suggested fix:
Providing the correct output, which is a counted result of the random numbers `r` begin generated.
[8 May 12:50] Luuk V
I did a test on Ubuntu with version:
8.0.23-0ubuntu0.20.04.1

And the problem is also there.
[8 May 13:12] MySQL Verification Team
Hello Luuk V,

Thank you for the report and test case.

regards,
Umesh
[24 Aug 23:21] Nino Skopac
Not a full test case, but an idea:

select date(created_at), type, status, count(*)
from api_logs
where date(created_at) = curdate() 
group by date(created_at), type, status with rollup 

Causes "Can't write; duplicate key in table '/rdsdbdata/tmp/#sql2a82_773_8'"

Removing with rollup and then running the query works.
Removing date(created_at) from both the select and the group by and then running the query also works, i.e.:

select type, status, count(*)
from api_logs
where date(created_at) = curdate() 
group by type, status with rollup

CREATE TABLE `api_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('subtype1','subtype2') DEFAULT NULL,
  `request_uri` varchar(255) NOT NULL DEFAULT '',
  `status` enum('successful','failed') DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `composite` (`type`,`status`,`request_uri`,`created_at`) USING BTREE,
  KEY `type_on_date_and_time_with_status` (`type`,(cast(`created_at` as date)),(cast(`created_at` as time)),`status`)
) ENGINE=InnoDB AUTO_INCREMENT=17056969 DEFAULT CHARSET=utf8mb3;

Running MySQL 8.0.25.
[25 Aug 1:58] Nino Skopac
An addendum to my previous comment: 

Removing the columns' DEFAULT and making them all NOT NULL doesn't have an effect on the issue.