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, 39 insertions, 1 deletions

diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c
index 8a444590301..d3f39296445 100644
--- a/tests/mysql_client_test.c
+++ b/tests/mysql_client_test.c
@@ -15237,6 +15237,43 @@ static void test_bug21206()
   DBUG_VOID_RETURN;
 }
 
+/*
+  Bug#21726: Incorrect result with multiple invocations of
+  LAST_INSERT_ID
+
+  Test that client gets updated value of insert_id on UPDATE that uses
+  LAST_INSERT_ID(expr).
+*/
+static void test_bug21726()
+{
+  const char *create_table[]=
+  {
+    "DROP TABLE IF EXISTS t1",
+    "CREATE TABLE t1 (i INT)",
+    "INSERT INTO t1 VALUES (1)",
+  };
+  const char *update_query= "UPDATE t1 SET i= LAST_INSERT_ID(i + 1)";
+  int rc;
+  my_ulonglong insert_id;
+
+  DBUG_ENTER("test_bug21726");
+  myheader("test_bug21726");
+
+  fill_tables(create_table, sizeof(create_table) / sizeof(*create_table));
+
+  rc= mysql_query(mysql, update_query);
+  myquery(rc);
+  insert_id= mysql_insert_id(mysql);
+  DIE_UNLESS(insert_id == 2);
+
+  rc= mysql_query(mysql, update_query);
+  myquery(rc);
+  insert_id= mysql_insert_id(mysql);
+  DIE_UNLESS(insert_id == 3);
+
+  DBUG_VOID_RETURN;
+}
+
 
 /*
   Read and parse arguments and MySQL options from my.cnf
@@ -15511,7 +15548,8 @@ static struct my_tests_st my_tests[]= {
   { "test_bug17667", test_bug17667 },
   { "test_bug19671", test_bug19671 },
   { "test_bug15752", test_bug15752 },
-  { "test_bug21206", test_bug21206},
+  { "test_bug21206", test_bug21206 },
+  { "test_bug21726", test_bug21726 },
   { 0, 0 }
 };