Bug #23170 if Insert ignore ...select fails on violation unique key, @@identity is <> 0
Submitted: 11 Oct 2006 10:03 Modified: 26 Feb 2007 20:49
Reporter: Aurel Pekarcik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26, 5.0.36-BK OS:Linux (Linux, windows XP)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[11 Oct 2006 10:03] Aurel Pekarcik
Description:
After INSERT IGNORE ... SELECT command, when no record is inserted becouse unique key violation occurs, variable @@IDENTITY contains next available value for auto_increment column.

How to repeat:
drop table if exists T1,T2;
create table T1 ( ID int primary key auto_increment, Name char(40), K2 int unique ) engine=MyISAM default character set cp1250 collate cp1250_general_ci;

create table T2 ( Identity int )engine=MyISAM default character set cp1250 collate cp1250_general_ci;

insert ignore T1 (Name,K2) values ('one', 11);
set @ID=@@Identity;
-- @ID contains value 1 - ok
insert T2 (identity) values (@ID);

insert ignore T1 (Name,K2) values ('too', 22);
set @ID=@@Identity;
-- @ID contains value 2 - ok
insert T2 (identity) values (@ID);

insert ignore T1 (Name,K2) values ('three', 22);
set @ID=@@Identity;
-- key K2 violation - @ID contains value 0 - ok
insert T2 (identity) values (@ID);

insert ignore T1 (Name,K2) select 'three', 22;
set @ID=@@Identity;
-- key K2 violation - @ID contains value 3 - ???
insert T2 (identity) values (@ID);

Suggested fix:
After INSERT IGNORE ... SELECT ... command, when no record inserted then @@Identity will contain zero, when any record inserted then @@identity will contain value by last inserted row
[11 Oct 2006 10:53] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources
[3 Feb 2007 8:46] Valeriy Kravchuk
On 5.1.15-BK I've got:

mysql> select * from T2;
+----------+
| Identity |
+----------+
|        1 |
|        2 |
|        2 |
|        2 |
+----------+
4 rows in set (0.00 sec)

after running this test case. Not sure it is OK (and it differs from 5.0.26).
[3 Feb 2007 8:51] Valeriy Kravchuk
Looks like on 5.1-BK it works as expected by the bug reporter. While on 5.0.36-BK we still have a bug:

mysql> select * from T2;
+----------+
| Identity |
+----------+
|        1 |
|        2 |
|        0 |
|        3 |
+----------+
4 rows in set (0.00 sec)
[9 Feb 2007 19:26] 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/commits/19638

ChangeSet@1.2408, 2007-02-09 22:25:09+03:00, evgen@moonbone.local +3 -0
  Bug#23170: LAST_INSERT_ID isn't reset to 0 in INSERT .. SELECT when no rows were
  inserted.
  
  The select_insert::send_eof() function now resets LAST_INSERT_ID variable if
  no rows were inserted.
[14 Feb 2007 10:30] Igor Babaev
The fix has been pushed to 5.0.36 ONLY.
5.1 follows a different semantics (see the manual)
[26 Feb 2007 20:49] Paul Dubois
Noted in 5.0.36 changelog.