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