Bug #12695 Duplicate entry 'xxxxx for key 1
Submitted: 20 Aug 2005 15:35 Modified: 28 Sep 2005 2:08
Reporter: NULL ! NULL ! Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.13 OS:Linux (Linux(Redhat9.0))
Assigned to: Sergei Golubchik CPU Architecture:Any

[20 Aug 2005 15:35] NULL ! NULL !
Description:
i create table below
create table seq_test(s_index varchar(5) key, s_text varchar(5));

and try it.

insert into seq_test(s_index, s_text)
select if(max(s_index) is null, '2000',max(s_index)+1),'test' from seq_test

when i try 3 count it error occurred 'Duplicate entry 'xxxxx for key 1'
and value inserted

data was inserted and error message occurred..
what can i do?

How to repeat:
1. create table seq_test(s_index varchar(5) key, s_text varchar(5));

2. insert into seq_test(s_index, s_text)
select if(max(s_index) is null, '2000',max(s_index)+1),'test' from seq_test

--> it's ok

3. insert into seq_test(s_index, s_text)
select if(max(s_index) is null, '2000',max(s_index)+1),'test' from seq_test
-->it's ok

4. insert into seq_test(s_index, s_text)
select if(max(s_index) is null, '2000',max(s_index)+1),'test' from seq_test
--> it's Error but data inserted
mysql> select * from seq_test;
+---------+--------+
| s_index | s_text |
+---------+--------+
| 2000    | test   |
| 2001    | test   |
| 2002    | test   |
+---------+--------+

5. 
mysql> insert into seq_test(s_index, s_text)
    -> select if(max(s_index) is null, '2000',max(s_index)+1),'test' from seq_test
    -> ;
ERROR 1062 (23000): Duplicate entry '2003' for key 1
mysql> 

mysql> select * from seq_test;
+---------+--------+
| s_index | s_text |
+---------+--------+
| 2000    | test   |
| 2001    | test   |
| 2002    | test   |
| 2003    | test   |
+---------+--------+
4 rows in set (0.00 sec)
[8 Sep 2005 11:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29497
[8 Sep 2005 20:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29530
[9 Sep 2005 8:55] Sergei Golubchik
fixed in 4.0.15, 5.0.13.
[9 Sep 2005 22:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29587
[14 Sep 2005 4:41] Mike Hillyer
Documented in 4.1.15 and 5.0.13 changelogs:

<listitem><para>
       Performing an <literal>IS NULL</literal> check on the <literal>MIN()</literal> or <literal>MAX()</literal> of an indexed columns produced incorrect results. (Bug #12695)
      </para></listitem>
[28 Sep 2005 2:08] Mike Hillyer
Changed:

<listitem>
        <para>
          Performing an <literal>IS NULL</literal> check on the
          <literal>MIN()</literal> or <literal>MAX()</literal> of an
          indexed column in a complex query could produce incorrect results. (Bug #12695)
        </para>
      </listitem>