bk commit into 5.0 tree (kroki:1.2289) 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-09-28 23:04:11+04:00, kroki@stripped +13 -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 in
  Item_func_last_insert_id::fix_fields() (which at that point equals to
  the first insert_id value generated by the previous statement), and
  return that remembered value from THD::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-09-28 23:04:08+04:00,
kroki@stripped +126 -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-09-28 23:04:08+04:00,
kroki@stripped +111 -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-09-28 23:04:08+04:00, kroki@stripped +27 -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-09-28 23:04:08+04:00, kroki@stripped +1 -0
    Add declaration of Item_func_last_insert_id::fix_fields().

  sql/log.cc@stripped, 2006-09-28 23:04:08+04:00, kroki@stripped +2 -2
    THD::insert_id() is a proper way to access THD::last_insert_id, as it
    has no side effects now.

  sql/log_event.cc@stripped, 2006-09-28 23:04:08+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-09-28 23:04:08+04:00, kroki@stripped +0 -1
    Remove dead code.

  sql/sql_class.cc@stripped, 2006-09-28 23:04:08+04:00, kroki@stripped +1 -0
    Reset THD::last_insert_id_used after each statement execution.

  sql/sql_class.h@stripped, 2006-09-28 23:04:08+04:00, kroki@stripped +1 -6
    Rather then remember current insert_id value on first invocation of
    THD::insert_id(), remember it in Item_func_last_insert_id::fix_fields(),
    and return from THD::insert_id().

  sql/sql_insert.cc@stripped, 2006-09-28 23:04:08+04:00, kroki@stripped +1 -3
    THD::insert_id() has no side effects now, and should be used to access
    THD::last_insert_id.

  sql/sql_load.cc@stripped, 2006-09-28 23:04:08+04:00, kroki@stripped +2 -6
    THD::insert_id() has no side effects now, and should be used to access
    THD::last_insert_id.

  sql/sql_parse.cc@stripped, 2006-09-28 23:04:08+04:00, kroki@stripped +1 -1
    No need to reset THD::last_insert_id_used here, it will be reset after
    each statement.

  tests/mysql_client_test.c@stripped, 2006-09-28 23:04:09+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-09-28 23:04:19 +04:00
+++ 1.305/sql/item_func.cc	2006-09-28 23:04:19 +04:00
@@ -3345,6 +3345,29 @@ 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)
+    {
+      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 +3377,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-09-28 23:04:19 +04:00
+++ 1.154/sql/item_func.h	2006-09-28 23:04:19 +04:00
@@ -891,6 +891,7 @@ public:
     if (arg_count)
       max_length= args[0]->max_length;
   }
+  bool fix_fields(THD *thd, Item **ref);
 };
 
 

--- 1.194/sql/log.cc	2006-09-28 23:04:19 +04:00
+++ 1.195/sql/log.cc	2006-09-28 23:04:19 +04:00
@@ -1711,7 +1711,7 @@ bool MYSQL_LOG::write(Log_event *event_i
       }
       if (thd->insert_id_used)
       {
-	Intvar_log_event e(thd,(uchar) INSERT_ID_EVENT,thd->last_insert_id);
+	Intvar_log_event e(thd,(uchar) INSERT_ID_EVENT,thd->insert_id());
 	if (e.write(file))
 	  goto err;
       }
@@ -2005,7 +2005,7 @@ bool MYSQL_LOG::write(THD *thd,const cha
       if (!(specialflag & SPECIAL_SHORT_LOG_FORMAT))
       {
         end=strmov(end,",insert_id=");
-        end=longlong10_to_str((longlong) thd->last_insert_id,end,-10);
+        end=longlong10_to_str((longlong) thd->insert_id(),end,-10);
       }
     }
     if (thd->query_start_used)

--- 1.211/sql/log_event.cc	2006-09-28 23:04:19 +04:00
+++ 1.212/sql/log_event.cc	2006-09-28 23:04:19 +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-09-28 23:04:19 +04:00
+++ 1.248/sql/sql_class.cc	2006-09-28 23:04:19 +04:00
@@ -557,6 +557,7 @@ bool THD::store_globals()
 
 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-09-28 23:04:19 +04:00
+++ 1.301/sql/sql_class.h	2006-09-28 23:04:19 +04:00
@@ -1259,7 +1259,7 @@ public:
   */
   ulonglong  last_insert_id;
   /*
-    Set to the first value that LAST_INSERT_ID() returned for the last
+    Set to the first value generated during execution of the last
     statement.  When this is set, last_insert_id_used is set to true.
   */
   ulonglong  current_insert_id;
@@ -1463,11 +1463,6 @@ public:
   }
   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.201/sql/sql_insert.cc	2006-09-28 23:04:19 +04:00
+++ 1.202/sql/sql_insert.cc	2006-09-28 23:04:19 +04:00
@@ -592,12 +592,10 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
     /*
       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 (! id && thd->insert_id_used)
     {						// Get auto increment value
-      id= thd->last_insert_id;
+      id= thd->insert_id();
     }
     if (error)
       break;

--- 1.98/sql/sql_load.cc	2006-09-28 23:04:19 +04:00
+++ 1.99/sql/sql_load.cc	2006-09-28 23:04:19 +04:00
@@ -618,11 +618,9 @@ read_fixed_length(THD *thd, COPY_INFO &i
     /*
       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 (!id && thd->insert_id_used)
-      id= thd->last_insert_id;
+      id= thd->insert_id();
     /*
       We don't need to reset auto-increment field since we are restoring
       its default value at the beginning of each loop iteration.
@@ -786,11 +784,9 @@ read_sep_field(THD *thd, COPY_INFO &info
     /*
       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 (!id && thd->insert_id_used)
-      id= thd->last_insert_id;
+      id= thd->insert_id();
     /*
       We don't need to reset auto-increment field since we are restoring
       its default value at the beginning of each loop iteration.

--- 1.580/sql/sql_parse.cc	2006-09-28 23:04:19 +04:00
+++ 1.581/sql/sql_parse.cc	2006-09-28 23:04:19 +04:00
@@ -5636,7 +5636,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.18/mysql-test/r/rpl_insert_id.result	2006-09-28 23:04:19 +04:00
+++ 1.19/mysql-test/r/rpl_insert_id.result	2006-09-28 23:04:19 +04:00
@@ -234,6 +234,132 @@ 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);
+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	13
+16	-1
+17	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	13
+16	-1
+17	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-09-28 23:04:19 +04:00
+++ 1.20/mysql-test/t/rpl_insert_id.test	2006-09-28 23:04:19 +04:00
@@ -244,6 +244,117 @@ 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);
+# 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-09-28 23:04:19 +04:00
+++ 1.164/sql/set_var.cc	2006-09-28 23:04:19 +04:00
@@ -2571,7 +2571,6 @@ 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;
 }
 

--- 1.208/tests/mysql_client_test.c	2006-09-28 23:04:19 +04:00
+++ 1.209/tests/mysql_client_test.c	2006-09-28 23:04:19 +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 }
 };