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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.