bzr commit into mysql-5.1 branch (v.narayanan:2675) Bug#38338

[Narayanan V]

#At file:///home/vn/work/mysql/workspaces-bazaar/shared_repository_directory/mysql-5.1-bugteam/

 2675 Narayanan V	2008-08-27 [merge]
      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-08-21 19:08:48 +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-08-21 19:08:48 +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-07-11 18:51:10 +0000
+++ b/sql/sql_insert.cc	2008-08-21 19:08:48 +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