From: Date: April 18 2006 6:59pm
Subject: bk commit into 5.0 tree (kroki:1.2156) BUG#15728
List-Archive: http://lists.mysql.com/commits/5096
X-Bug: 15728
Message-Id: <200604181659.k3IGxISa019424@moonlight.intranet>

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
  1.2156 06/04/18 20:59:10 kroki@stripped +5 -0
  Bug#15728: LAST_INSERT_ID function inside a stored function returns 0
  
  In order to allow encapsulation of LAST_INSERT_ID() in a stored function
  or a trigger we do not reset its value in sub-statement.

  sql/sql_class.cc
    1.232 06/04/18 20:59:04 kroki@stripped +3 -2
    Do not reset value of LAST_INSERT_ID() in sub-statement.

  mysql-test/t/sp.test
    1.183 06/04/18 20:59:04 kroki@stripped +25 -0
    Add test case for bug#15728.

  mysql-test/t/rpl_insert_id.test
    1.15 06/04/18 20:59:04 kroki@stripped +66 -0
    Add test case for bug#15728.

  mysql-test/r/sp.result
    1.195 06/04/18 20:59:03 kroki@stripped +17 -0
    Add result for bug#15728.

  mysql-test/r/rpl_insert_id.result
    1.14 06/04/18 20:59:03 kroki@stripped +49 -0
    Add result for bug#15728.

# 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-bug15728

--- 1.231/sql/sql_class.cc	2006-04-13 01:46:36 +04:00
+++ 1.232/sql/sql_class.cc	2006-04-18 20:59:04 +04:00
@@ -2025,7 +2025,7 @@
   The following things is done
   - Disable binary logging for the duration of the statement
   - Disable multi-result-sets for the duration of the statement
-  - Value of last_insert_id() is reset and restored
+  - Value of last_insert_id() is saved and restored
   - Value set by 'SET INSERT_ID=#' is reset and restored
   - Value for found_rows() is reset and restored
   - examined_row_count is added to the total
@@ -2037,6 +2037,8 @@
     We reset examined_row_count and cuted_fields and add these to the
     result to ensure that if we have a bug that would reset these within
     a function, we are not loosing any rows from the main statement.
+
+    We do not reset value of last_insert_id().
 ****************************************************************************/
 
 void THD::reset_sub_statement_state(Sub_statement_state *backup,
@@ -2062,7 +2064,6 @@
   /* Disable result sets */
   client_capabilities &= ~CLIENT_MULTI_RESULTS;
   in_sub_stmt|= new_state;
-  last_insert_id= 0;
   next_insert_id= 0;
   insert_id_used= 0;
   examined_row_count= 0;

--- 1.13/mysql-test/r/rpl_insert_id.result	2006-01-26 19:54:26 +03:00
+++ 1.14/mysql-test/r/rpl_insert_id.result	2006-04-18 20:59:03 +04:00
@@ -74,3 +74,52 @@
 INSERT INTO t1 VALUES (1),(1);
 ERROR 23000: Duplicate entry '1' for key 1
 drop table t1;
+drop function if exists bug15728;
+drop function if exists bug15728_insert;
+drop table if exists t1, t2;
+create table t1 (
+id int not null auto_increment,
+last_id int,
+primary key (id)
+);
+create function bug15728() returns int(11)
+return last_insert_id();
+insert into t1 (last_id) values (0);
+insert into t1 (last_id) values (last_insert_id());
+insert into t1 (last_id) values (bug15728());
+create table t2 (
+id int not null auto_increment,
+last_id int,
+primary key (id)
+);
+create function bug15728_insert() returns int(11) modifies sql data
+begin
+insert into t2 (last_id) values (bug15728());
+return bug15728();
+end|
+create trigger t1_bi before insert on t1 for each row
+begin
+declare res int;
+select bug15728_insert() into res;
+set NEW.last_id = res;
+end|
+insert into t1 (last_id) values (0);
+drop trigger t1_bi;
+select bug15728_insert();
+bug15728_insert()
+2
+insert into t1 (last_id) values (bug15728());
+select * from t1;
+id	last_id
+1	0
+2	1
+3	2
+4	1
+5	4
+select * from t2;
+id	last_id
+1	3
+2	4
+drop function bug15728;
+drop function bug15728_insert;
+drop table t1, t2;

--- 1.14/mysql-test/t/rpl_insert_id.test	2006-01-26 19:54:28 +03:00
+++ 1.15/mysql-test/t/rpl_insert_id.test	2006-04-18 20:59:04 +04:00
@@ -78,3 +78,69 @@
 drop table t1;
 sync_slave_with_master;
 # End of 4.1 tests
+
+
+#
+# BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
+#
+# The solution is not to reset last_insert_id on enter to sub-statement.
+#
+connection master;
+--disable_warnings
+drop function if exists bug15728;
+drop function if exists bug15728_insert;
+drop table if exists t1, t2;
+--enable_warnings
+
+create table t1 (
+  id int not null auto_increment,
+  last_id int,
+  primary key (id)
+);
+create function bug15728() returns int(11)
+  return last_insert_id();
+
+insert into t1 (last_id) values (0);
+insert into t1 (last_id) values (last_insert_id());
+insert into t1 (last_id) values (bug15728());
+
+# Check that nested call replicates too.
+create table t2 (
+  id int not null auto_increment,
+  last_id int,
+  primary key (id)
+);
+delimiter |;
+create function bug15728_insert() returns int(11) modifies sql data
+begin
+  insert into t2 (last_id) values (bug15728());
+  return bug15728();
+end|
+create trigger t1_bi before insert on t1 for each row
+begin
+  declare res int;
+  select bug15728_insert() into res;
+  set NEW.last_id = res;
+end|
+delimiter ;|
+
+insert into t1 (last_id) values (0);
+
+drop trigger t1_bi;
+
+# Check that nested call doesn't affect outer context.
+select bug15728_insert();
+insert into t1 (last_id) values (bug15728());
+
+save_master_pos;
+connection slave;
+sync_with_master;
+select * from t1;
+select * from t2;
+connection master;
+
+drop function bug15728;
+drop function bug15728_insert;
+drop table t1, t2;
+
+# End of 5.0 tests

--- 1.194/mysql-test/r/sp.result	2006-04-06 14:18:57 +04:00
+++ 1.195/mysql-test/r/sp.result	2006-04-18 20:59:03 +04:00
@@ -4848,4 +4848,21 @@
 b	3
 a	1
 delete from t1|
+drop function if exists bug15728|
+drop table if exists t3|
+create table t3 (
+id int not null auto_increment,
+primary key (id)
+)|
+create function bug15728() returns int(11)
+return last_insert_id()|
+insert into t3 values (0)|
+select last_insert_id()|
+last_insert_id()
+1
+select bug15728()|
+bug15728()
+1
+drop function bug15728|
+drop table t3|
 drop table t1,t2;

--- 1.182/mysql-test/t/sp.test	2006-04-06 14:18:57 +04:00
+++ 1.183/mysql-test/t/sp.test	2006-04-18 20:59:04 +04:00
@@ -5699,6 +5699,31 @@
 
 
 #
+# BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
+#
+# The solution is not to reset last_insert_id on enter to sub-statement.
+#
+--disable_warnings
+drop function if exists bug15728|
+drop table if exists t3|
+--enable_warnings
+
+create table t3 (
+  id int not null auto_increment,
+  primary key (id)
+)|
+create function bug15728() returns int(11)
+  return last_insert_id()|
+
+insert into t3 values (0)|
+select last_insert_id()|
+select bug15728()|
+
+drop function bug15728|
+drop table t3|
+
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings