From: Date: September 8 2008 9:04am
Subject: bzr commit into mysql-5.1 branch (v.narayanan:2735) Bug#38338
List-Archive: http://lists.mysql.com/commits/53475
X-Bug: 38338
Message-Id: <20080908070428.C472C74@sa64-v40za-blr03.india.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///export/home/log/Narayanan/mysql_checkouts_bazaar/5.1_main_repository/mysql-5.1-bugteam-38338/

 2735 Narayanan V	2008-09-08
      Bug#38338: REPLACE causes last_insert_id() to return an incorrect value
                        
      Fix the write_record function to record auto increment
      values in a consistent way.
modified:
  mysql-test/r/auto_increment.result
  mysql-test/t/auto_increment.test
  sql/sql_insert.cc

per-file messages:
  mysql-test/r/auto_increment.result
    Updated the test result file with the output of the
    new test case added to verify this bug.
  mysql-test/t/auto_increment.test
    Added a new test case to verify this bug.
  sql/sql_insert.cc
    The algorithm for the write_record function
    in sql_insert.cc is (more emphasis given to
    the parts that deal with the autogenerated values)
=== modified file 'mysql-test/r/auto_increment.result'
--- a/mysql-test/r/auto_increment.result	2008-01-11 01:06:08 +0000
+++ b/mysql-test/r/auto_increment.result	2008-09-08 07:04:20 +0000
@@ -454,3 +454,11 @@ select last_insert_id();
 last_insert_id()
 3
 drop table t1;
+create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e));
+insert into t1 values(null,1,1,1,now());
+insert into t1 values(null,0,0,0,null);
+replace into t1 values(null,1,0,2,null);
+select last_insert_id();
+last_insert_id()
+3
+drop table t1;

=== modified file 'mysql-test/t/auto_increment.test'
--- a/mysql-test/t/auto_increment.test	2008-01-11 01:06:08 +0000
+++ b/mysql-test/t/auto_increment.test	2008-09-08 07:04:20 +0000
@@ -314,5 +314,15 @@ insert into t1 values(null,0,0,null);
 # this will delete two rows
 replace into t1 values(null,1,0,null);
 select last_insert_id();
+drop table t1;
 
+# Test of REPLACE when it does a INSERT+DELETE for all the conflicting rows
+# (i.e.) when there are three rows conflicting in unique key columns with
+# a row that is being inserted, all the three rows will be deleted and then
+# the new rows will be inserted.
+create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e));
+insert into t1 values(null,1,1,1,now());
+insert into t1 values(null,0,0,0,null);
+replace into t1 values(null,1,0,2,null);
+select last_insert_id();
 drop table t1;

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	2008-08-29 14:20:08 +0000
+++ b/sql/sql_insert.cc	2008-09-08 07:04:20 +0000
@@ -1545,6 +1545,17 @@ int write_record(THD *thd, TABLE *table,
         }
       }
     }
+    
+    /*
+        If more than one iteration of the above while loop is done, from the second 
+        one the row being inserted will have an explicit value in the autoinc field, 
+        which was set at the first call of handler::update_auto_increment(). This 
+        value is saved to avoid thd->insert_id_for_cur_row becoming 0. Use this saved
+        autoinc value.
+     */
+    if (table->file->insert_id_for_cur_row == 0)
+      table->file->insert_id_for_cur_row= insert_id_for_cur_row;
+      
     thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row);
     /*
       Restore column maps if they where replaced during an duplicate key