MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:eugene Date:February 16 2007 4:39pm
Subject:bk commit into 5.0 tree (evgen:1.2409) BUG#16630
View as plain text  
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-02-16 19:39:28+03:00, evgen@stripped +6 -0
  Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY
  UPDATE contains wrong data if the SELECT employs a temporary table.
  
  If the UPDATE values of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE
  statement contains fields from the SELECT part and the select employs a
  temporary table then those fields will contain wrong values because they
  aren't corrected to get data from the temporary table.
  
  The solution is to add these fields to the selects all_fields list,
  to store pointers to those fields in the selects ref_pointer_array and
  to access them via Item_ref objects.
  
  The substitution for Item_ref objects is done in the new function called 
  Item_field::update_value_transformer(). It is called through the
  item->transform() mechanism at the end of the select_insert::prepare()
  function.

  mysql-test/r/insert_select.result@stripped, 2007-02-16 19:38:47+03:00, evgen@stripped +13 -0
    Added a test case for bug#16630: The update fields of the INSERT .. SELECT ..
    ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a 
    temporary table.

  mysql-test/t/insert_select.test@stripped, 2007-02-16 19:38:50+03:00, evgen@stripped +15 -0
    Added a test case for bug#16630: The update fields of the INSERT .. SELECT ..
    ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a 
    temporary table.

  sql/item.cc@stripped, 2007-02-16 19:38:21+03:00, evgen@stripped +45 -0
    Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY
    UPDATE contains wrong data if the SELECT employs a temporary table.
    The new method Item_field::update_value_transformer() is added. It
    substitutes fields in the update values list for references 
    (Item_ref objects) to them.

  sql/item.h@stripped, 2007-02-16 19:38:36+03:00, evgen@stripped +2 -0
    Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY
    UPDATE contains wrong data if the SELECT employs a temporary table.
    The update_value_transformer() method is added to the Item and to the
    Item_field classes.

  sql/sql_insert.cc@stripped, 2007-02-16 19:38:40+03:00, evgen@stripped +16 -0
    Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY
    UPDATE contains wrong data if the SELECT employs a temporary table.
    Traverse update values and substitute fields from the select for
    references (Item_ref objects) to them.

  sql/sql_select.cc@stripped, 2007-02-16 19:38:45+03:00, evgen@stripped +3 -3
    Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY
    UPDATE contains wrong data if the SELECT employs a temporary table.
    Traverse update values and substitute fields from the select for
    references (Item_ref objects) to them.

# 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/16630-bug-5.0-opt-mysql

--- 1.254/sql/item.cc	2007-02-09 12:05:17 +03:00
+++ 1.255/sql/item.cc	2007-02-16 19:38:21 +03:00
@@ -4809,6 +4809,51 @@
 }
 
 
+/*
+  Add the field to the select list and substitute it for the reference to
+  the field.
+
+  SYNOPSIS
+    Item_field::update_value_transformer()
+    select_arg      current select
+
+  DESCRIPTION
+    If the field doesn't belong to the table being inserted into then it is
+    added to the select list, pointer to it is stored in the ref_pointer_array
+    of the select and the field itself is substituted for the Item_ref object.
+    This is done in order to get correct values from update fields that
+    belongs to the SELECT part in the INSERT .. SELECT .. ON DUPLICATE KEY
+    UPDATE statement.
+
+  RETURN
+    0             if error occured
+    ref           if all conditions are met
+    this field    otherwise
+*/
+
+Item *Item_field::update_value_transformer(byte *select_arg)
+{
+  SELECT_LEX *select= (SELECT_LEX*)select_arg;
+  DBUG_ASSERT(fixed);
+
+  if (field->table != select->context.table_list->table &&
+      type() != Item::TRIGGER_FIELD_ITEM)
+  {
+    List<Item> *all_fields= &select->join->all_fields;
+    Item **ref_pointer_array= select->ref_pointer_array;
+    int el= all_fields->elements;
+    Item_ref *ref;
+
+    ref_pointer_array[el]= (Item*)this;
+    all_fields->push_front((Item*)this);
+    ref= new Item_ref(&select->context, ref_pointer_array + el,
+                      table_name, field_name);
+    return ref;
+  }
+  return this;
+}
+
+
 Item_ref::Item_ref(Name_resolution_context *context_arg,
                    Item **item, const char *table_name_arg,
                    const char *field_name_arg)

--- 1.219/sql/item.h	2007-01-26 20:33:16 +03:00
+++ 1.220/sql/item.h	2007-02-16 19:38:36 +03:00
@@ -817,6 +817,7 @@
   virtual Item_field *filed_for_view_update() { return 0; }
 
   virtual Item *neg_transformer(THD *thd) { return NULL; }
+  virtual Item *update_value_transformer(byte *select_arg) { return this; }
   virtual Item *safe_charset_converter(CHARSET_INFO *tocs);
   void delete_self()
   {
@@ -1295,6 +1296,7 @@
   Item_field *filed_for_view_update() { return this; }
   Item *safe_charset_converter(CHARSET_INFO *tocs);
   int fix_outer_field(THD *thd, Field **field, Item **reference);
+  virtual Item *update_value_transformer(byte *select_arg);
   friend class Item_default_value;
   friend class Item_insert_value;
   friend class st_select_lex_unit;

--- 1.216/sql/sql_insert.cc	2007-02-09 22:24:54 +03:00
+++ 1.217/sql/sql_insert.cc	2007-02-16 19:38:40 +03:00
@@ -2388,7 +2388,23 @@
         next_name_resolution_table=          ctx_state.save_next_local;
     }
     res= res || setup_fields(thd, 0, *info.update_values, 1, 0, 0);
+    if (!res)
+    {
+      /*
+        Traverse the update values list and substitute fields from the
+        select for references (Item_ref objects) to them. This is done in
+        order to get correct values from those fields when the select
+        employs a temporary table.
+      */
+      List_iterator<Item> li(*info.update_values);
+      Item *item;
 
+      while ((item= li++))
+      {
+        item->transform(&Item::update_value_transformer,
+                        (byte*)lex->current_select);
+      }
+    }
     /* Restore the current context. */
     ctx_state.restore_state(context, table_list);
   }

--- 1.488/sql/sql_select.cc	2007-02-01 11:50:33 +03:00
+++ 1.489/sql/sql_select.cc	2007-02-16 19:38:45 +03:00
@@ -482,6 +482,9 @@
     }
   }
 
+  if (!procedure && result && result->prepare(fields_list, unit_arg))
+    goto err;					/* purecov: inspected */
+
   /* Init join struct */
   count_field_types(&tmp_table_param, all_fields, 0);
   ref_pointer_array_size= all_fields.elements*sizeof(Item*);
@@ -495,9 +498,6 @@
     goto err;
   }
 #endif
-  if (!procedure && result && result->prepare(fields_list, unit_arg))
-    goto err;					/* purecov: inspected */
-
   if (select_lex->olap == ROLLUP_TYPE && rollup_init())
     goto err;
   if (alloc_func_list())

--- 1.44/mysql-test/r/insert_select.result	2007-02-09 22:24:54 +03:00
+++ 1.45/mysql-test/r/insert_select.result	2007-02-16 19:38:47 +03:00
@@ -731,3 +731,16 @@
 @@identity
 0
 drop table t1;
+CREATE TABLE t1 (f1 INT, f2 INT );
+CREATE TABLE t2  (f1 INT PRIMARY KEY, f2 INT);
+INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
+INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
+INSERT INTO t2 (f1, f2)
+SELECT f1, f1 FROM t2 src WHERE f1 < 2
+ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
+SELECT * FROM t2;
+f1	f2
+101	1
+2	2
+10	10
+DROP TABLE t1, t2;

--- 1.36/mysql-test/t/insert_select.test	2007-02-09 22:24:52 +03:00
+++ 1.37/mysql-test/t/insert_select.test	2007-02-16 19:38:50 +03:00
@@ -292,3 +292,18 @@
 insert ignore t1(f2) select 1;
 select @@identity;
 drop table t1;
+
+#
+# Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE
+#
+CREATE TABLE t1 (f1 INT, f2 INT );
+CREATE TABLE t2  (f1 INT PRIMARY KEY, f2 INT);
+INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
+INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
+INSERT INTO t2 (f1, f2)
+  SELECT f1, f1 FROM t2 src WHERE f1 < 2
+  ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
+SELECT * FROM t2;
+DROP TABLE t1, t2;
+
+ 
Thread
bk commit into 5.0 tree (evgen:1.2409) BUG#16630eugene20 Feb