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.
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.