bk commit into 5.0 tree (kroki:1.2293) BUG#21726

[kroki]

Below is the list of changes that have just been committed into a local
5.0 repository of tomash. When tomash does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-10-02 14:28:23+04:00, kroki@stripped +14 -0
  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@stripped, 2006-10-02 14:28:18+04:00,
kroki@stripped +129 -0
    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@stripped, 2006-10-02 14:28:18+04:00,
kroki@stripped +112 -0
    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@stripped, 2006-10-02 14:28:18+04:00, kroki@stripped +32 -3
    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@stripped, 2006-10-02 14:28:18+04:00, kroki@stripped +1 -0
    Add declaration of Item_func_last_insert_id::fix_fields().

  sql/log_event.cc@stripped, 2006-10-02 14:28:18+04:00, kroki@stripped +0 -1
    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@stripped, 2006-10-02 14:28:18+04:00, kroki@stripped +11 -2
    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@stripped, 2006-10-02 14:28:19+04:00, kroki@stripped +15 -1
    Reset THD::last_insert_id_used after each statement execution.

  sql/sql_class.h@stripped, 2006-10-02 14:28:19+04:00, kroki@stripped +33 -15
    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@stripped, 2006-10-02 14:28:19+04:00, kroki@stripped +3 -5
    THD::insert_id() is removed, use THD::last_insert_id directly.

  sql/sql_load.cc@stripped, 2006-10-02 14:28:19+04:00, kroki@stripped +4 -8
    THD::insert_id() is removed, using THD::last_insert_id directly is OK.

  sql/sql_parse.cc@stripped, 2006-10-02 14:28:19+04:00, kroki@stripped +15 -1
    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@stripped, 2006-10-02 14:28:19+04:00, kroki@stripped +9 -2
    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@stripped, 2006-10-02 14:28:20+04:00, kroki@stripped +2 -2
    THD::insert_id() is removed, use THD::last_insert_id directly.

  tests/mysql_client_test.c@stripped, 2006-10-02 14:28:20+04:00, kroki@stripped +39
-1
    Add test case for bug#21726: Incorrect result with multiple invocations
    of LAST_INSERT_ID.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.0-real-bug21726

--- 1.304/sql/item_func.cc	2006-10-02 14:28:32 +04:00
+++ 1.305/sql/item_func.cc	2006-10-02 14:28:32 +04:00
@@ -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,11 +3382,12 @@ longlong Item_func_last_insert_id::val_i
     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 */
 

--- 1.153/sql/item_func.h	2006-10-02 14:28:32 +04:00
+++ 1.154/sql/item_func.h	2006-10-02 14:28:32 +04:00
@@ -891,6 +891,7 @@ public:
     if (arg_count)
       max_length= args[0]->max_length;
   }
+  bool fix_fields(THD *thd, Item **ref);
 };
 
 

--- 1.211/sql/log_event.cc	2006-10-02 14:28:32 +04:00
+++ 1.212/sql/log_event.cc	2006-10-02 14:28:32 +04:00
@@ -3365,7 +3365,6 @@ int Intvar_log_event::exec_event(struct 
 {
   switch (type) {
   case LAST_INSERT_ID_EVENT:
-    thd->last_insert_id_used = 1;
     thd->last_insert_id = val;
     break;
   case INSERT_ID_EVENT:

--- 1.247/sql/sql_class.cc	2006-10-02 14:28:32 +04:00
+++ 1.248/sql/sql_class.cc	2006-10-02 14:28:32 +04:00
@@ -553,10 +553,24 @@ bool THD::store_globals()
 }
 
 
-/* Cleanup after a query */
+/*
+  Cleanup after query.
 
+  SYNOPSIS
+    THD::cleanup_after_query()
+
+  DESCRIPTION
+    This function is used to reset thread data to it's default state.
+
+  NOTE
+    This function is not suitable for setting thread data to some
+    non-default values, as there is only one replication thread, so
+    different master threads may overwrite data of each other on
+    slave.
+*/
 void THD::cleanup_after_query()
 {
+  last_insert_id_used= FALSE;
   if (clear_next_insert_id)
   {
     clear_next_insert_id= 0;

--- 1.300/sql/sql_class.h	2006-10-02 14:28:32 +04:00
+++ 1.301/sql/sql_class.h	2006-10-02 14:28:32 +04:00
@@ -1252,17 +1252,29 @@ public:
   ulonglong  next_insert_id;
   /* Remember last next_insert_id to reset it if something went wrong */
   ulonglong  prev_insert_id;
+
   /*
-    The insert_id used for the last statement or set by SET LAST_INSERT_ID=#
-    or SELECT LAST_INSERT_ID(#).  Used for binary log and returned by
-    LAST_INSERT_ID()
+    At the beginning of the statement last_insert_id holds the first
+    generated value of the previous statement.  During statement
+    execution it is updated to the value just generated, but then
+    restored to the value that was generated first, so for the next
+    statement it will again be "the first generated value of the
+    previous statement".
+
+    It may also be set with "LAST_INSERT_ID(expr)" or
+    "@@LAST_INSERT_ID= expr", but the effect of such setting will be
+    seen only in the next statement.
   */
   ulonglong  last_insert_id;
+
   /*
-    Set to the first value that LAST_INSERT_ID() returned for the last
-    statement.  When this is set, last_insert_id_used is set to true.
+    current_insert_id remembers the first generated value of the
+    previous statement, and does not change during statement
+    execution.  Its value returned from LAST_INSERT_ID() and
+    @@LAST_INSERT_ID.
   */
   ulonglong  current_insert_id;
+
   ulonglong  limit_found_rows;
   ulonglong  options;           /* Bitmap of states */
   longlong   row_count_func;	/* For the ROW_COUNT() function */
@@ -1325,7 +1337,22 @@ public:
   bool       last_cuted_field;
   bool	     no_errors, password, is_fatal_error;
   bool	     query_start_used, rand_used, time_zone_used;
-  bool	     last_insert_id_used,insert_id_used, clear_next_insert_id;
+
+  /*
+    last_insert_id_used is set when current statement calls
+    LAST_INSERT_ID() or reads @@LAST_INSERT_ID, so that binary log
+    LAST_INSERT_ID_EVENT be generated.
+  */
+  bool	     last_insert_id_used;
+
+  /*
+    insert_id_used is set when current statement updates
+    THD::last_insert_id, so that binary log INSERT_ID_EVENT be
+    generated.
+  */
+  bool       insert_id_used;
+
+  bool       clear_next_insert_id;
   /* for IS NULL => = last_insert_id() fix in remove_eq_conds() */
   bool       substitute_null_with_insert_id;
   bool	     in_lock_tables;
@@ -1460,15 +1487,6 @@ public:
     last_insert_id= id_arg;
     insert_id_used=1;
     substitute_null_with_insert_id= TRUE;
-  }
-  inline ulonglong insert_id(void)
-  {
-    if (!last_insert_id_used)
-    {
-      last_insert_id_used=1;
-      current_insert_id=last_insert_id;
-    }
-    return last_insert_id;
   }
   inline ulonglong found_rows(void)
   {

--- 1.202/sql/sql_insert.cc	2006-10-02 14:28:32 +04:00
+++ 1.203/sql/sql_insert.cc	2006-10-02 14:28:32 +04:00
@@ -590,10 +590,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
 #endif
       error=write_record(thd, table ,&info);
     /*
-      If auto_increment values are used, save the first one
-       for LAST_INSERT_ID() and for the update log.
-       We can't use insert_id() as we don't want to touch the
-       last_insert_id_used flag.
+      If auto_increment values are used, save the first one for
+      LAST_INSERT_ID() and for the update log.
     */
     if (! id && thd->insert_id_used)
     {						// Get auto increment value
@@ -2447,7 +2445,7 @@ bool select_insert::send_data(List<Item>
       */
       table->next_number_field->reset();
       if (!last_insert_id && thd->insert_id_used)
-        last_insert_id= thd->insert_id();
+        last_insert_id= thd->last_insert_id;
     }
   }
   DBUG_RETURN(error);

--- 1.98/sql/sql_load.cc	2006-10-02 14:28:32 +04:00
+++ 1.99/sql/sql_load.cc	2006-10-02 14:28:32 +04:00
@@ -616,10 +616,8 @@ read_fixed_length(THD *thd, COPY_INFO &i
     thd->no_trans_update= no_trans_update;
    
     /*
-      If auto_increment values are used, save the first one
-       for LAST_INSERT_ID() and for the binary/update log.
-       We can't use insert_id() as we don't want to touch the
-       last_insert_id_used flag.
+      If auto_increment values are used, save the first one for
+      LAST_INSERT_ID() and for the binary/update log.
     */
     if (!id && thd->insert_id_used)
       id= thd->last_insert_id;
@@ -784,10 +782,8 @@ read_sep_field(THD *thd, COPY_INFO &info
     if (write_record(thd, table, &info))
       DBUG_RETURN(1);
     /*
-      If auto_increment values are used, save the first one
-       for LAST_INSERT_ID() and for the binary/update log.
-       We can't use insert_id() as we don't want to touch the
-       last_insert_id_used flag.
+      If auto_increment values are used, save the first one for
+      LAST_INSERT_ID() and for the binary/update log.
     */
     if (!id && thd->insert_id_used)
       id= thd->last_insert_id;

--- 1.580/sql/sql_parse.cc	2006-10-02 14:28:32 +04:00
+++ 1.581/sql/sql_parse.cc	2006-10-02 14:28:32 +04:00
@@ -2422,6 +2422,20 @@ mysql_execute_command(THD *thd)
   thd->net.no_send_error= 0;
 
   /*
+    Remember first generated insert id value of the previous
+    statement.  We remember it here at the beginning of the statement,
+    and also in Item_func_last_insert_id::fix_fields() and
+    sys_var_last_insert_id::value_ptr().  Last two places are required
+    because LAST_INSERT_ID() and @@LAST_INSERT_ID may also be used in
+    expression that is not executed with mysql_execute_command().
+
+    And we remember it here because some statements read
+    @@LAST_INSERT_ID indirectly, like "SELECT * FROM t1 WHERE id IS
+    NULL", that may replace "id IS NULL" with "id = <LAST_INSERT_ID>".
+  */
+  thd->current_insert_id= thd->last_insert_id;
+
+  /*
     In many cases first table of main SELECT_LEX have special meaning =>
     check that it is first table in global list and relink it first in 
     queries_tables list if it is necessary (we need such relinking only
@@ -5636,7 +5650,7 @@ void mysql_reset_thd_for_next_command(TH
   DBUG_ENTER("mysql_reset_thd_for_next_command");
   thd->free_list= 0;
   thd->select_number= 1;
-  thd->last_insert_id_used= thd->query_start_used= thd->insert_id_used=0;
+  thd->query_start_used= thd->insert_id_used=0;
   thd->is_fatal_error= thd->time_zone_used= 0;
   thd->server_status&= ~ (SERVER_MORE_RESULTS_EXISTS | 
                           SERVER_QUERY_NO_INDEX_USED |

--- 1.458/sql/sql_select.cc	2006-10-02 14:28:33 +04:00
+++ 1.459/sql/sql_select.cc	2006-10-02 14:28:33 +04:00
@@ -8142,7 +8142,7 @@ remove_eq_conds(THD *thd, COND *cond, It
       Field *field=((Item_field*) args[0])->field;
       if (field->flags & AUTO_INCREMENT_FLAG &&
!field->table->maybe_null &&
 	  (thd->options & OPTION_AUTO_IS_NULL) &&
-	  thd->insert_id() && thd->substitute_null_with_insert_id)
+          thd->current_insert_id && thd->substitute_null_with_insert_id)
       {
 #ifdef HAVE_QUERY_CACHE
 	query_cache_abort(&thd->net);
@@ -8150,9 +8150,16 @@ remove_eq_conds(THD *thd, COND *cond, It
 	COND *new_cond;
 	if ((new_cond= new Item_func_eq(args[0],
 					new Item_int("last_insert_id()",
-						     thd->insert_id(),
+						     thd->current_insert_id,
 						     21))))
 	{
+          /*
+            Set THD::last_insert_id_used manually, as this statement
+            uses LAST_INSERT_ID() in a sense, and should issue
+            LAST_INSERT_ID_EVENT.
+          */
+          thd->last_insert_id_used= TRUE;
+
 	  cond=new_cond;
           /*
             Item_func_eq can't be fixed after creation so we do not check

--- 1.197/sql/sql_update.cc	2006-10-02 14:28:33 +04:00
+++ 1.198/sql/sql_update.cc	2006-10-02 14:28:33 +04:00
@@ -568,7 +568,7 @@ int mysql_update(THD *thd,
     thd->row_count_func=
       (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
     send_ok(thd, (ulong) thd->row_count_func,
-	    thd->insert_id_used ? thd->insert_id() : 0L,buff);
+	    thd->insert_id_used ? thd->last_insert_id : 0L,buff);
     DBUG_PRINT("info",("%d records updated",updated));
   }
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;		/* calc cuted fields */
@@ -1567,6 +1567,6 @@ bool multi_update::send_eof()
   thd->row_count_func=
     (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
   ::send_ok(thd, (ulong) thd->row_count_func,
-	    thd->insert_id_used ? thd->insert_id() : 0L,buff);
+	    thd->insert_id_used ? thd->last_insert_id : 0L,buff);
   return FALSE;
 }

--- 1.18/mysql-test/r/rpl_insert_id.result	2006-10-02 14:28:33 +04:00
+++ 1.19/mysql-test/r/rpl_insert_id.result	2006-10-02 14:28:33 +04:00
@@ -234,6 +234,135 @@ n	b
 2	100
 3	350
 drop table t1;
+DROP PROCEDURE IF EXISTS p1;
+DROP TABLE IF EXISTS t1, t2;
+SELECT LAST_INSERT_ID(0);
+LAST_INSERT_ID(0)
+0
+CREATE TABLE t1 (
+id INT NOT NULL DEFAULT 0,
+last_id INT,
+PRIMARY KEY (id)
+);
+CREATE TABLE t2 (
+id INT NOT NULL AUTO_INCREMENT,
+last_id INT,
+PRIMARY KEY (id)
+);
+CREATE PROCEDURE p1()
+BEGIN
+INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
+INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
+END|
+CALL p1();
+SELECT * FROM t1;
+id	last_id
+0	1
+SELECT * FROM t2;
+id	last_id
+1	0
+SELECT * FROM t1;
+id	last_id
+0	1
+SELECT * FROM t2;
+id	last_id
+1	0
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+DROP PROCEDURE IF EXISTS p1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (
+i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+j INT DEFAULT 0
+);
+CREATE TABLE t2 (i INT);
+CREATE PROCEDURE p1()
+BEGIN
+INSERT INTO t1 (i) VALUES (NULL);
+INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
+INSERT INTO t1 (i) VALUES (NULL), (NULL);
+INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
+END |
+CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
+BEGIN
+INSERT INTO t1 (i) VALUES (NULL);
+INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
+INSERT INTO t1 (i) VALUES (NULL), (NULL);
+INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
+RETURN 0;
+END |
+CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
+RETURN LAST_INSERT_ID() |
+INSERT INTO t1 VALUES (NULL, -1);
+CALL p1();
+SELECT f1();
+f1()
+0
+INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
+INSERT INTO t1 VALUES (NULL, f2());
+INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID(5)),
+(NULL, @@LAST_INSERT_ID);
+INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
+UPDATE t1 SET j= -1 WHERE i IS NULL;
+SELECT * FROM t1;
+i	j
+1	-1
+2	0
+3	0
+4	0
+5	0
+6	0
+7	0
+8	3
+9	3
+10	3
+11	3
+12	3
+13	8
+14	13
+15	5
+16	13
+17	-1
+18	14
+SELECT * FROM t2;
+i
+2
+3
+5
+6
+SELECT * FROM t1;
+i	j
+1	-1
+2	0
+3	0
+4	0
+5	0
+6	0
+7	0
+8	3
+9	3
+10	3
+11	3
+12	3
+13	8
+14	13
+15	5
+16	13
+17	-1
+18	14
+SELECT * FROM t2;
+i
+2
+3
+5
+6
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1, t2;
 
 # End of 5.0 tests
 

--- 1.19/mysql-test/t/rpl_insert_id.test	2006-10-02 14:28:33 +04:00
+++ 1.20/mysql-test/t/rpl_insert_id.test	2006-10-02 14:28:33 +04:00
@@ -244,6 +244,118 @@ select * from t1 order by n;
 
 connection master;
 drop table t1;
+
+#
+# BUG#20339: stored procedure using LAST_INSERT_ID() does not
+# replicate statement-based 
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+# Reset result of LAST_INSERT_ID().
+SELECT LAST_INSERT_ID(0);
+
+CREATE TABLE t1 (
+  id INT NOT NULL DEFAULT 0,
+  last_id INT,
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE t2 (
+  id INT NOT NULL AUTO_INCREMENT,
+  last_id INT,
+  PRIMARY KEY (id)
+);
+
+delimiter |;
+CREATE PROCEDURE p1()
+BEGIN
+  INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
+  INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
+END|
+delimiter ;|
+
+CALL p1();
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+sync_slave_with_master;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection master;
+
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+
+
+#
+# BUG#21726: Incorrect result with multiple invocations of
+# LAST_INSERT_ID
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (
+    i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+    j INT DEFAULT 0
+);
+CREATE TABLE t2 (i INT);
+
+delimiter |;
+CREATE PROCEDURE p1()
+BEGIN
+  INSERT INTO t1 (i) VALUES (NULL);
+  INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
+  INSERT INTO t1 (i) VALUES (NULL), (NULL);
+  INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
+END |
+
+CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
+BEGIN
+  INSERT INTO t1 (i) VALUES (NULL);
+  INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
+  INSERT INTO t1 (i) VALUES (NULL), (NULL);
+  INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
+  RETURN 0;
+END |
+
+CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
+  RETURN LAST_INSERT_ID() |
+delimiter ;|
+
+INSERT INTO t1 VALUES (NULL, -1);
+CALL p1();
+SELECT f1();
+INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
+                      (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
+INSERT INTO t1 VALUES (NULL, f2());
+INSERT INTO t1 VALUES (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID(5)),
+                      (NULL, @@LAST_INSERT_ID);
+# Test replication of substitution "IS NULL" -> "= LAST_INSERT_ID".
+INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
+UPDATE t1 SET j= -1 WHERE i IS NULL;
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+sync_slave_with_master;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection master;
+DROP PROCEDURE p1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1, t2;
+
+
 sync_slave_with_master;
 
 --echo 

--- 1.163/sql/set_var.cc	2006-10-02 14:28:33 +04:00
+++ 1.164/sql/set_var.cc	2006-10-02 14:28:33 +04:00
@@ -2571,8 +2571,17 @@ bool sys_var_last_insert_id::update(THD 
 byte *sys_var_last_insert_id::value_ptr(THD *thd, enum_var_type type,
 					LEX_STRING *base)
 {
-  thd->sys_var_tmp.long_value= (long) thd->insert_id();
-  return (byte*) &thd->last_insert_id;
+  if (!thd->last_insert_id_used)
+  {
+    /*
+      As this statement reads @@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;
+  }
+  return (byte*) &thd->current_insert_id;
 }
 
 

--- 1.208/tests/mysql_client_test.c	2006-10-02 14:28:33 +04:00
+++ 1.209/tests/mysql_client_test.c	2006-10-02 14:28:33 +04:00
@@ -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 }
 };