MySQL Bugs: #23170: if Insert ignore ...select fails on violation unique key, @@identity is <> 0

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

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

Thank you for the report.

Verified as described on Linux using last BK sources

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

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)

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.

The fix has been pushed to 5.0.36 ONLY.
5.1 follows a different semantics (see the manual)

Noted in 5.0.36 changelog.