List:Commits« Previous MessageNext Message »
From:kroki Date:September 22 2006 12:33pm
Subject:bk commit into 5.0 tree (kroki:1.2264) BUG#21726
View as plain text  
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-22 16:33:20+04:00, kroki@stripped +10 -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, where current
  insert_id value that was remembered on the first call to LAST_INSERT_ID()
  was returned from that function until it was reset before the next
  _upper-level_ statement.  The very procedure of remembering current
  insert_id had another defects that made the fix for bug 6880 only a
  partial solution.
  
  The fix for bug#21726 employs the following protocol:
  
   - remember insert_id value after the statement execution (which is at
     that point equals to the first insert_id value generated by the
     statement or the result of its explicit setting).
  
   - on statements "LAST_INSERT_ID(expr)", "SET LAST_INSERT_ID= expr", and
     on binary log command "LAST_INSERT_ID= constant" update remembered
     insert_id value.
  
  Thus, the value returned by LAST_INSERT_ID() is not affected by values
  generated by current statement, but affected by its explicit update.
  
  This fix is for 5.0 only, as in 5.1 WL 3146 solves the same problems in
  a more generic way.  This patch tries to produce the same results as 5.1.

  mysql-test/r/rpl_insert_id.result@stripped, 2006-09-22 16:33:15+04:00, kroki@stripped +127 -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-22 16:33:15+04:00, kroki@stripped +112 -1
    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-22 16:33:15+04:00, kroki@stripped +7 -2
    On call to LAST_INSERT_ID(expr) set THD::current_insert_id together with
    THD::last_insert_id.  Return THD::current_insert_id (via call to
    THD::insert_id()).

  sql/log_event.cc@stripped, 2006-09-22 16:33:15+04:00, kroki@stripped +5 -0
    For binary log command "LAST_INSERT_ID= expr" set THD::current_insert_id
    together with THD::last_insert_id.

  sql/set_var.cc@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +6 -2
    For statement "SET LAST_INSERT_ID= expr" set
    THD::current_insert_id together with THD::last_insert_id.
    Return THD::current_insert_id for "@@LAST_INSERT_ID".

  sql/sql_class.cc@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +2 -0
    Remember generated value for insert_id in THD::current_insert_id at the
    end of the statement, which then will be returned by THD::insert_id().

  sql/sql_class.h@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +2 -6
    Rather then remember current insert_id value on first invocation of
    THD::insert_id(), remember it in THD::current_insert_id at statement end,
    and return from THD::insert_id().

  sql/sql_insert.cc@stripped, 2006-09-22 16:33:16+04:00, kroki@stripped +1 -1
    Since THD::insert_id() now returns THD::current_insert_id that we have
    remembered from the previous statement, here we remember current value
    of THD::last_insert_id (and that is actually how the code worked before
    the fix for bug#6880).

  sql/sql_parse.cc@stripped, 2006-09-22 16:33:16+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-22 16:33:16+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-bug21726

--- 1.300/sql/item_func.cc	2006-09-22 16:33:28 +04:00
+++ 1.301/sql/item_func.cc	2006-09-22 16:33:28 +04:00
@@ -3305,11 +3305,16 @@ longlong Item_func_last_insert_id::val_i
   {
     longlong value= args[0]->val_int();
     thd->insert_id(value);
+    /*
+      Update THD::current_insert_id, which is returned by
+      THD::insert_id().
+    */
+    thd->current_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->insert_id();
 }
 
 /* This function is just used to test speed of different functions */

--- 1.208/sql/log_event.cc	2006-09-22 16:33:28 +04:00
+++ 1.209/sql/log_event.cc	2006-09-22 16:33:28 +04:00
@@ -3367,6 +3367,11 @@ int Intvar_log_event::exec_event(struct 
   case LAST_INSERT_ID_EVENT:
     thd->last_insert_id_used = 1;
     thd->last_insert_id = val;
+    /*
+      Update THD::current_insert_id, which is returned by
+      THD::insert_id().
+    */
+    thd->current_insert_id= val;
     break;
   case INSERT_ID_EVENT:
     thd->next_insert_id = val;

--- 1.247/sql/sql_class.cc	2006-09-22 16:33:28 +04:00
+++ 1.248/sql/sql_class.cc	2006-09-22 16:33:28 +04:00
@@ -557,6 +557,8 @@ bool THD::store_globals()
 
 void THD::cleanup_after_query()
 {
+  last_insert_id_used= 0;
+  current_insert_id= last_insert_id;
   if (clear_next_insert_id)
   {
     clear_next_insert_id= 0;

--- 1.295/sql/sql_class.h	2006-09-22 16:33:28 +04:00
+++ 1.296/sql/sql_class.h	2006-09-22 16:33:28 +04:00
@@ -1463,12 +1463,8 @@ 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;
+    last_insert_id_used= 1;
+    return current_insert_id;
   }
   inline ulonglong found_rows(void)
   {

--- 1.199/sql/sql_insert.cc	2006-09-22 16:33:28 +04:00
+++ 1.200/sql/sql_insert.cc	2006-09-22 16:33:28 +04:00
@@ -2438,7 +2438,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.572/sql/sql_parse.cc	2006-09-22 16:33:28 +04:00
+++ 1.573/sql/sql_parse.cc	2006-09-22 16:33:28 +04:00
@@ -5625,7 +5625,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.16/mysql-test/r/rpl_insert_id.result	2006-09-22 16:33:28 +04:00
+++ 1.17/mysql-test/r/rpl_insert_id.result	2006-09-22 16:33:28 +04:00
@@ -210,3 +210,130 @@ 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.18/mysql-test/t/rpl_insert_id.test	2006-09-22 16:33:28 +04:00
+++ 1.19/mysql-test/t/rpl_insert_id.test	2006-09-22 16:33:28 +04:00
@@ -228,6 +228,117 @@ select * from t1 order by n;
 connection master;
 drop table t1;
 
-# End of 5.0 tests
+
+#
+# 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;
+
+
+--echo End of 5.0 tests.
 
 sync_slave_with_master;

--- 1.163/sql/set_var.cc	2006-09-22 16:33:28 +04:00
+++ 1.164/sql/set_var.cc	2006-09-22 16:33:28 +04:00
@@ -2564,6 +2564,11 @@ byte *sys_var_timestamp::value_ptr(THD *
 bool sys_var_last_insert_id::update(THD *thd, set_var *var)
 {
   thd->insert_id(var->save_result.ulonglong_value);
+  /*
+    Update THD::current_insert_id, which is returned by
+    THD::insert_id().
+  */
+  thd->current_insert_id= var->save_result.ulonglong_value;
   return 0;
 }
 
@@ -2571,8 +2576,7 @@ 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;
+  return (byte*) &thd->current_insert_id;
 }
 
 

--- 1.207/tests/mysql_client_test.c	2006-09-22 16:33:28 +04:00
+++ 1.208/tests/mysql_client_test.c	2006-09-22 16:33:28 +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 }
 };
 
Thread
bk commit into 5.0 tree (kroki:1.2264) BUG#21726kroki22 Sep