bk commit into 5.0 tree (evgen:1.2476) BUG#27033

[eugene]

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-15 23:21:29+03:00, evgen@stripped +4 -0
  Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were
  touched but not 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 touched.
  The new 'touched' field is added to the COPY_INFO structure. It holds the
  number of rows that were touched no matter whether they were actually
  changed or not.

  mysql-test/r/insert_update.result@stripped, 2007-03-15 23:20:59+03:00, evgen@stripped
+11 -0
    Added a test case for the bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON
DUPLICATE if rows were
    touched but not actually changed.
    

  mysql-test/t/insert_update.test@stripped, 2007-03-15 23:20:59+03:00, evgen@stripped
+12 -0
    Added a test case for the bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON
DUPLICATE if rows were
    touched but not actually changed.
    

  sql/sql_class.h@stripped, 2007-03-15 23:20:57+03:00, evgen@stripped +17 -5
    Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were
    touched but not actually changed.
    
    The new 'touched' field is added to the COPY_INFO structure. It holds the
    number of rows that were touched no matter whether they were actually
    changed or not.

  sql/sql_insert.cc@stripped, 2007-03-15 23:20:59+03:00, evgen@stripped +9 -7
    Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were
    touched but not actually changed.
    
    Now the LAST_INSERT_ID() values is reset to 0 only if there were no rows
    successfully inserted or touched.

# 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-15 23:20:57 +03:00
@@ -356,13 +356,25 @@
   inline uint32 get_open_count() { return open_count; }
 };
 
-
+/*
+  The COPY_INFO structure is used by INSERT/REPLACE code.
+  The schema of the row counting by the INSERT/INSERT ... ON DUPLICATE KEY
+  UPDATE code:
+    If a row is inserted then the copied variable is incremented.
+    If a row is updated by the INSERT ... ON DUPLICATE KEY UPDATE and the
+      new data differs from the old one then the copied and the updated
+      variables are incremented.
+    The touched variable is incremented if a row was touched by the update part
+      of the INSERT ... ON DUPLICATE KEY UPDATE no matter whether the row
+      was actually changed or not.
+*/
 typedef struct st_copy_info {
-  ha_rows records;
-  ha_rows deleted;
-  ha_rows updated;
-  ha_rows copied;
+  ha_rows records; /* Number of processed records */
+  ha_rows deleted; /* Number of deleted records */
+  ha_rows updated; /* Number of updated records */
+  ha_rows copied;  /* Number of copied records */
   ha_rows error_count;
+  ha_rows touched; /* Number of touched records */
   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-15 23:20:59 +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 were 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-15 23:20:59 +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-15 23:20:59 +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: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were
+#            touched but not 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;