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