BUG#21726: Incorrect result with multiple invocations of LAST_INSERT_ID

Non-upper-level INSERTs (the ones in the body of stored procedure,
stored function, or trigger) into a table that have AUTO_INCREMENT
column didn't affected the result of LAST_INSERT_ID() on this level.

The problem was introduced with the fix of bug 6880, which in turn was
introduced with the fix of bug 3117, where current insert_id value was
remembered on the first call to LAST_INSERT_ID() (bug 3117) and was
returned from that function until it was reset before the next
_upper-level_ statement (bug 6880).

The fix for bug#21726 brings back the behaviour of version 4.0, and
implements the following: remember insert_id value at the beginning
of the statement or expression (which at that point equals to
the first insert_id value generated by the previous statement), and
return that remembered value from LAST_INSERT_ID() or @@LAST_INSERT_ID.

Thus, the value returned by LAST_INSERT_ID() is not affected by values
generated by current statement, nor by LAST_INSERT_ID(expr) calls in
this statement.

Version 5.1 does not have this bug (it was fixed by WL 3146).


mysql-test/r/rpl_insert_id.result:
  Add results for bug#21726: Incorrect result with multiple invocations
  of LAST_INSERT_ID, and bug#20339: stored procedure using LAST_INSERT_ID()
  does not replicate statement-based.
mysql-test/t/rpl_insert_id.test:
  Add test cases for bug#21726: Incorrect result with multiple invocations
  of LAST_INSERT_ID, and bug#20339: stored procedure using LAST_INSERT_ID()
  does not replicate statement-based.
sql/item_func.cc:
  Add implementation of Item_func_last_insert_id::fix_fields(), where we
  remember in THD::current_insert_id the first value generated during
  execution of the previous statement, which is returned then from
  Item_func_last_insert_id::val_int().
sql/item_func.h:
  Add declaration of Item_func_last_insert_id::fix_fields().
sql/log_event.cc:
  Do not set THD::last_insert_id_used on LAST_INSERT_ID_EVENT.  Though we
  know the statement will call LAST_INSERT_ID(), it wasn't called yet.
sql/set_var.cc:
  In sys_var_last_insert_id::value_ptr() remember in
  THD::current_insert_id the first value generated during execution of the
  previous statement, and return this value for @@LAST_INSERT_ID.
sql/sql_class.cc:
  Reset THD::last_insert_id_used after each statement execution.
sql/sql_class.h:
  Rather then remember current insert_id value on first invocation of
  THD::insert_id(), remember it in Item_func_last_insert_id::fix_fields(),
  sys_var_last_insert_id::value_ptr(), or mysql_execute_command().
  Remove THD::insert_id(), as it lost its value now.
sql/sql_insert.cc:
  THD::insert_id() is removed, use THD::last_insert_id directly.
sql/sql_load.cc:
  THD::insert_id() is removed, using THD::last_insert_id directly is OK.
sql/sql_parse.cc:
  Remember in THD::current_insert_id first generated insert id value of
  the previous statement in mysql_execute_command().
  No need to reset THD::last_insert_id_used in
  mysql_reset_thd_for_next_command(), it will be reset after each
  statement.
sql/sql_select.cc:
  If "IS NULL" is replaced with "= <LAST_INSERT_ID>", use right value,
  which is THD::current_insert_id, and also set THD::last_insert_id_used
  to issue binary log LAST_INSERT_ID_EVENT.
sql/sql_update.cc:
  THD::insert_id() is removed, use THD::last_insert_id directly.
tests/mysql_client_test.c:
  Add test case for bug#21726: Incorrect result with multiple invocations
  of LAST_INSERT_ID.

1 files changed, 32 insertions, 3 deletions

diff --git a/sql/item_func.cc b/sql/item_func.cc
index 2e594c74031..e395a7a3af5 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -3345,6 +3345,34 @@ longlong Item_func_release_lock::val_int()
 }
 
 
+bool Item_func_last_insert_id::fix_fields(THD *thd, Item **ref)
+{
+  DBUG_ASSERT(fixed == 0);
+
+  if (Item_int_func::fix_fields(thd, ref))
+    return TRUE;
+
+  if (arg_count == 0)
+  {
+    if (!thd->last_insert_id_used)
+    {
+      /*
+        As this statement calls LAST_INSERT_ID(), set
+        THD::last_insert_id_used and remember first generated insert
+        id of the previous statement in THD::current_insert_id.
+      */
+      thd->last_insert_id_used= TRUE;
+      thd->current_insert_id= thd->last_insert_id;
+    }
+    null_value= FALSE;
+  }
+
+  thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
+
+  return FALSE;
+}
+
+
 longlong Item_func_last_insert_id::val_int()
 {
   THD *thd= current_thd;
@@ -3354,12 +3382,13 @@ longlong Item_func_last_insert_id::val_int()
     longlong value= args[0]->val_int();
     thd->insert_id(value);
     null_value= args[0]->null_value;
-    return value;                       // Avoid side effect of insert_id()
+    return value;
   }
-  thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
-  return thd->last_insert_id_used ? thd->current_insert_id : thd->insert_id();
+
+  return thd->current_insert_id;
 }
 
+
 /* This function is just used to test speed of different functions */
 
 longlong Item_func_benchmark::val_int()