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

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