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: | |
| 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 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.
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) );