From: Date: March 12 2007 1:57pm
Subject: bk commit into 5.0 tree (evgen:1.2476) BUG#27033
List-Archive: http://lists.mysql.com/commits/21727
X-Bug: 27033
Message-Id: <20070312125702.319BB22D256@moonbone.moonbone.local>

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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, 2007-03-12 15:56:58+03:00, evgen@stripped +4 -0
  Bug#27033: Wrong LAST_INSERT_ID() value after INSERT .. ON DUPLICATE if no
  rows were actually changed.
  
  The LAST_INSERT_ID() is reset to 0 if no rows were inserted or changed.
  This is the case when an INSERT ... ON DUPLICATE KEY UPDATE updates a row
  with the same values as the row contains.
  
  Now the LAST_INSERT_ID() values is reset to 0 only if there were no rows
  successfully inserted or updated.
  The new 'touched' field is added to the COPY_INFO structure. It holds the
  number of rows that were updated no matter whether they were actually
  changed or not.

  mysql-test/r/insert_update.result@stripped, 2007-03-12 15:48:46+03:00, evgen@stripped +11 -0
    Added a test case for the bug#27033: Wrong LAST_INSERT_ID() value after INSERT .. ON DUPLICATE if no rows were actually changed.

  mysql-test/t/insert_update.test@stripped, 2007-03-12 15:48:21+03:00, evgen@stripped +12 -0
    Added a test case for the bug#27033: Wrong LAST_INSERT_ID() value after INSERT .. ON DUPLICATE if no rows were actually changed.

  sql/sql_class.h@stripped, 2007-03-12 15:49:51+03:00, evgen@stripped +1 -0
    Bug#27033: Wrong LAST_INSERT_ID() value after INSERT .. ON DUPLICATE if no
    rows were actually changed.
    The new 'touched' field is added to the COPY_INFO structure. It holds the
    number of rows that were updated no matter whether they were actually
    changed or not.

  sql/sql_insert.cc@stripped, 2007-03-12 15:49:10+03:00, evgen@stripped +9 -7
    Bug#27033: Wrong LAST_INSERT_ID() value after INSERT .. ON DUPLICATE if no
    rows were actually changed.
    Now the LAST_INSERT_ID() values is reset to 0 only if there were no rows
    successfully inserted or updated.

# 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:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/27033-bug-5.0-opt-mysql

--- 1.324/sql/sql_class.h	2007-03-08 01:55:18 +03:00
+++ 1.325/sql/sql_class.h	2007-03-12 15:49:51 +03:00
@@ -363,6 +363,7 @@
   ha_rows updated;
   ha_rows copied;
   ha_rows error_count;
+  ha_rows touched;
   enum enum_duplicates handle_duplicates;
   int escape_char, last_errno;
   bool ignore;

--- 1.221/sql/sql_insert.cc	2007-03-08 20:29:58 +03:00
+++ 1.222/sql/sql_insert.cc	2007-03-12 15:49:10 +03:00
@@ -522,7 +522,7 @@
   /*
     Fill in the given fields and dump it to the table file
   */
-  info.records= info.deleted= info.copied= info.updated= 0;
+  info.records= info.deleted= info.copied= info.updated= info.touched= 0;
   info.ignore= ignore;
   info.handle_duplicates=duplic;
   info.update_fields= &update_fields;
@@ -767,8 +767,8 @@
       (!table->triggers || !table->triggers->has_delete_triggers()))
     table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
 
-  /* Reset value of LAST_INSERT_ID if no rows where inserted */
-  if (!info.copied && thd->insert_id_used)
+  /* Reset value of LAST_INSERT_ID if no rows where inserted or touched */
+  if (!info.copied && !info.touched && thd->insert_id_used)
   {
     thd->insert_id(0);
     id=0;
@@ -1221,14 +1221,16 @@
           }
           goto err;
         }
+
+        if (table->next_number_field)
+          table->file->adjust_next_insert_id_after_explicit_value(
+            table->next_number_field->val_int());
+        info->touched++;
+
         if ((table->file->table_flags() & HA_PARTIAL_COLUMN_READ) ||
             compare_record(table, thd->query_id))
         {
           info->updated++;
-
-          if (table->next_number_field)
-            table->file->adjust_next_insert_id_after_explicit_value(
-              table->next_number_field->val_int());
 
           trg_error= (table->triggers &&
                       table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,

--- 1.21/mysql-test/r/insert_update.result	2007-02-19 15:39:24 +03:00
+++ 1.22/mysql-test/r/insert_update.result	2007-03-12 15:48:46 +03:00
@@ -236,3 +236,14 @@
 INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
 ERROR 42S22: Unknown column 'a' in 'field list'
 DROP TABLE t1,t2;
+CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY,
+f2 VARCHAR(5) NOT NULL UNIQUE);
+INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+drop table t1;

--- 1.21/mysql-test/t/insert_update.test	2007-02-19 15:39:25 +03:00
+++ 1.22/mysql-test/t/insert_update.test	2007-03-12 15:48:21 +03:00
@@ -162,3 +162,15 @@
 --error ER_BAD_FIELD_ERROR
 INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
 DROP TABLE t1,t2;
+
+#
+# Bug#27033: Wrong LAST_INSERT_ID() value after INSERT .. ON DUPLICATE if no
+#            rows were actually changed.
+#
+CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY,
+                 f2 VARCHAR(5) NOT NULL UNIQUE);
+INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
+SELECT LAST_INSERT_ID();
+INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
+SELECT LAST_INSERT_ID();
+drop table t1;