Bug #62097 unable to run a select statement
Submitted: 5 Aug 2011 18:18 Modified: 5 Aug 2011 19:32
Reporter: stephen bond Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.5 OS:Microsoft Windows (xp)
Assigned to: CPU Architecture:Any
Tags: GROUP BY

[5 Aug 2011 18:18] stephen bond
Description:
error 1022: 
Can't write; duplicate key in table 'G:\Temp\#sql6fc_7_5'

while trying to execute:
SELECT
	T2011.term, T2011.relAge, T2011.ins,T2011.sn,
        sum(T2011.prevbal) AS total_prevbal,
        sum(T2011.due) AS total_bal,
	count(T2011.id) as total_cnt, avg(T2011.rate) avgint
    FROM T2011 where T2011.ins in (1,2) and greatest(prevbal,due)>10000
    and relAge between 0 and 1 and term>11
    GROUP BY
        term,ins,relAge,sn 

--------------

--------------
select count(*) from T2011
--------------

+----------+
| count(*) |
+----------+
| 18924531 |
+----------+
desc T2011
--------------

+---------+-------------+------+-----+------------+-------+
| Field   | Type        | Null | Key | Default    | Extra |
+---------+-------------+------+-----+------------+-------+
| id      | int(11)     | YES  | MUL | 0          |       |
| ym      | date        | NO   |     | 0000-00-00 |       |
| prevbal | float       | YES  |     | NULL       |       |
| due     | float       | YES  |     | NULL       |       |
| pmt     | float       | YES  |     | NULL       |       |
| actpmt  | float       | YES  |     | NULL       |       |
| disc    | varchar(15) | YES  |     | NULL       |       |
| FV      | char(1)     | YES  |     | NULL       |       |
| rate    | float       | YES  |     | NULL       |       |
| ins     | tinyint(4)  | YES  |     | NULL       |       |
| term1   | smallint(6) | YES  |     | NULL       |       |
| disc2   | varchar(25) | YES  |     | NULL       |       |
| strt    | date        | YES  |     | NULL       |       |
| matd    | date        | YES  |     | NULL       |       |
| prod    | varchar(15) | YES  |     | NULL       |       |
| LTV     | float       | YES  |     | NULL       |       |
| term    | float       | YES  | MUL | NULL       |       |
| paydt   | date        | YES  |     | NULL       |       |
| termdt  | date        | YES  |     | NULL       |       |
| relAge  | float       | YES  | MUL | NULL       |       |
| turn    | tinyint(4)  | YES  |     | NULL       |       |
| sn      | tinyint(4)  | YES  |     | NULL       |       |
+---------+-------------+------+-----+------------+-------+

How to repeat:
not sure

Suggested fix:
it appears mysql is trying to create a unique index on a tmp table and that is somehow not working. there must be some implicit assumption about the data in the code, which my data violates.
[5 Aug 2011 19:32] Sveta Smirnova
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #58081