MySQL Bugs: #9481: mysql_insert_id() returns 0 after insert ... select

Bug #9481	mysql_insert_id() returns 0 after insert ... select
Submitted:	30 Mar 2005 11:36	Modified:	24 Jan 2008 20:35
Reporter:	Sergei Kulakov (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.22, 4.0.15, 3.23.58, 4.1, 5.0	OS:	Linux (Linux, XP)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
According to documentation (20.2.3.32 mysql_insert_id()), mysql_insert_id() C API (and PHP) function, is supposed to return the last value inserted into an autoincrement column a little more often than MySql Last_Insert_Id() function, and one of the cases is: 

INSERT statements that store a value into an AUTO_INCREMENT column. This is true whether the value is automatically generated by storing the special values NULL or 0 into the column, or is an explicit non-special value. 

This is great and it works as described, for ex., Insert Into A Set i=10 where i is an autoincrement column will make mysql_insert_id() return 10. But I found that after executing queries like Insert ... Select the function returns 0. 

How to repeat:
Create Table A (i int not null auto_increment primary key)
Create Table B (i int not null auto_increment primary key)
Insert Into B Set i=10
 - mysql_insert_id() returns 10 
Insert Into A Select 10 From B Limit 1
 - mysql_insert_id() returns 0!
I found this workaround:
Insert Into A Select Last_Insert_Id(20) From B Limit 1
 - mysql_insert_id() returns 20

Suggested fix:
-

verified w/4.0.24 from bk

mysql> Create Table A (i int not null auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> Create Table B (i int not null auto_increment primary key); 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into b values (10);
ERROR 1146: Table 'test.b' doesn't exist
mysql> insert into B values (10); 
Query OK, 1 row affected (0.00 sec)

mysql> insert into A select 10 from B limit 1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select last_insert_id() from B;
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> select last_insert_id() from A;
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> Insert Into A Select Last_Insert_Id(20) From B Limit 1
    -> ;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select last_insert_id() from A;                       
+------------------+
| last_insert_id() |
+------------------+
|               20 |
|               20 |
+------------------+
2 rows in set (0.00 sec)

mysql>

Note that you were verifying using a wrong function - last_insert_id() - not API mysql_insert_id(), and last_insert_id() is supposed to return 0 in cases like that. It is only mysql_insert_id() (for ex. in PHP) that is supposed to return the last inserted value. Also, the function last_insert_id() is not called against tables - it does not depend on a table - it is just called as a function:
Select last_insert_id()
So, calling it against different tables makes no sense.

Not verified correctly

Output from the application at the bottom:

C:\temp>bug9481
Connected with the Server: 4.0.24-nt
Value returned by mysql_insert_id query3: 10
Value returned by mysql_insert_id query4: 0
Value returned by mysql_insert_id query5: 20

#ifdef WIN32
typedef unsigned __int64 ulonglong;
typedef __int64 longlong;
typedef unsigned long ulong;
#endif

#include <stdio.h>
#include <string.h>
#include <my_global.h>
#include <m_ctype.h>
#include <m_string.h>
#include <mysql.h>

#define query1 "Create Table A (i int not null auto_increment primary key)"
#define query2 "Create Table B (i int not null auto_increment primary key)"
#define query3 "Insert Into B Set i=10"
#define query4 "Insert Into A Select 10 From B Limit 1"
#define query5 "Insert Into A Select Last_Insert_Id(20) From B Limit 1"

void main()
{
 
  MYSQL *MySQL;
  
  if ( (MySQL = mysql_init((MYSQL*) 0)) && 
        mysql_real_connect( MySQL,"localhost","root",
                           "",	"test",	0, NULL, 0 ))
  {  
     printf("Connected with the Server: %s\n",
            mysql_get_server_info(MySQL));
  }
  else
  {
    printf("Failed to connect with the server\n");
    mysql_close( MySQL );
    return;
  }
    
 if (mysql_real_query(MySQL,query1,sizeof(query1)) < 0)
 {
   printf("query1 failed (%s)\n",mysql_error(MySQL));
   mysql_close( MySQL );
   return;
 }

 if (mysql_real_query(MySQL,query2,sizeof(query2)) < 0)
 {
   printf("query2 failed (%s)\n",mysql_error(MySQL));
   mysql_close( MySQL );
   return;
 }

 if (mysql_real_query(MySQL,query3,sizeof(query3)) < 0)
 {
   printf("query3 failed (%s)\n",mysql_error(MySQL));
   mysql_close( MySQL );
   return;
 }

 longlong rtv1 = mysql_insert_id(MySQL);
 printf("Value returned by mysql_insert_id query3: %d\n",rtv1);
  
 if (mysql_real_query(MySQL,query4,sizeof(query4)) < 0)
 {
   printf("query4 failed (%s)\n",mysql_error(MySQL));
   mysql_close( MySQL );
   return;
 }

 longlong rtv2 = mysql_insert_id(MySQL);
 printf("Value returned by mysql_insert_id query4: %d\n",rtv2);

  if (mysql_real_query(MySQL,query5,sizeof(query5)) < 0)
 {
   printf("query5 failed (%s)\n",mysql_error(MySQL));
   mysql_close( MySQL );
   return;
 }

 longlong rtv3 = mysql_insert_id(MySQL);
 printf("Value returned by mysql_insert_id query5: %d\n",rtv3);
 
 mysql_close( MySQL );
   
}

Has been fixed in 5.1 (confirmed on Windows and Linux) by Guilhem with code for WL#3146

Bug still exists in versions 5.0 and 4.1

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/38529

ChangeSet@1.2570, 2007-11-26 18:36:05+03:00, kaa@polly.(none) +3 -0
  5.0 version of the fix for bug #9481: mysql_insert_id() returns 0 after
  insert ... select.
  
  The 5.0 manual page for mysql_insert_id() does not mention anything
  about INSERT ... SELECT, though its current behavior is incosistent
  with what the manual says about the plain INSERT.
  
  Fixed by changing the AUTO_INCREMENT and mysql_insert_id() handling
  logic in INSERT ... SELECT to be consistent with the INSERT behavior,
  the manual, and the changes in 5.1 introduced by WL3146:
  
  
  - mysql_insert_id() now returns the first automatically generated
  AUTO_INCREMENT value that was successfully inserted by INSERT ... SELECT
  
  -  if an INSERT ... SELECT statement is executed, and no automatically
  generated value is successfully inserted, mysql_insert_id() now returns
  the ID of the last inserted row.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted the change in the 5.0.54 changelog, and in the
description for mysql_insert_id().