Bug #92654 GROUP BY fails for queries when a temporary table is involved
Submitted: 3 Oct 2018 11:16 Modified: 3 Oct 2018 13:57
Reporter: Евгени Кутекин Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.12, 5.7.23 OS:Windows
Assigned to: CPU Architecture:x86

[3 Oct 2018 11:16] Евгени Кутекин
Description:
The query from the repeat section leads to an error like " Can't write; duplicate key in table 'C:\WINDOWS\SERVIC~2\NETWOR~1\AppData\Local\Temp\#sql3dc8_1ed_0'"

EXPLAIN shows that due to GROUP BY a temporary table is created
It is possible to execute the query without GROUP BY. Another option is to put 
"(CASE WHEN 1 > 2 THEN 'nothing' ELSE 'recent' END)" instead of 'recent' - the query is performed as expected. So it seems like selecting a constant value is what causes this error

How to repeat:
SELECT
  COUNT(1),
  host_connection_status.connection_time
FROM
  (SELECT id
   FROM domain_tree) AS hosts_with_status
  LEFT OUTER JOIN
  (SELECT
     domain_id,
     'recent' AS connection_time
   FROM
     host_connection_info) AS host_connection_status
    ON hosts_with_status.id = host_connection_status.domain_id
GROUP BY host_connection_status.connection_time

The tables structure is 

CREATE TABLE domain_tree
(
  id BIGINT NOT NULL PRIMARY KEY
);
CREATE TABLE host_connection_info
(
  domain_id BIGINT NOT NULL PRIMARY KEY,
  FOREIGN KEY (domain_id) REFERENCES domain_tree (id)
);
[3 Oct 2018 13:57] MySQL Verification Team
Hello Евгени Кутекин,

Thank you for the report and test case.

Thanks,
Umesh