bk commit into 4.1 tree (acurtis:1.2355) BUG#10109

[antony]

Below is the list of changes that have just been committed into a local
4.1 repository of antony. When antony 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
  1.2355 05/07/28 16:33:52 acurtis@stripped +3 -0
  Bug#10109
    "INSERT .. SELECT ... ON DUPLICATE KEY UPDATE fails"
    Fix bogus "Duplicate column" error message.

  sql/sql_insert.cc
    1.171 05/07/28 16:33:33 acurtis@stripped +35 -3
    Fix for Bug#10109

  mysql-test/t/insert_update.test
    1.16 05/07/28 16:33:32 acurtis@stripped +31 -0
    Test for Bug#10109

  mysql-test/r/insert_update.result
    1.15 05/07/28 16:33:32 acurtis@stripped +22 -0
    Test for Bug#10109

# 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:	acurtis
# Host:	ltantony.xiphis.org
# Root:	/usr/home/antony/work2/p2-bug10109

--- 1.170/sql/sql_insert.cc	2005-06-28 13:06:12 +01:00
+++ 1.171/sql/sql_insert.cc	2005-07-28 16:33:33 +01:00
@@ -52,11 +52,15 @@
     table                       The table for insert.
     fields                      The insert fields.
     values                      The insert values.
+    save_query_id               for duplicate column check
 
   NOTE
     Clears TIMESTAMP_AUTO_SET_ON_INSERT from table->timestamp_field_type
     or leaves it as is, depending on if timestamp should be updated or
     not.
+    save_query_id should be set to TRUE if there has been any prior
+    operations which has set any referenced field's query_id value to
+    equal thd->query_id otherwise the duplicate column check breaks.
 
   RETURN
     0           OK
@@ -64,7 +68,7 @@
 */
 
 static int check_insert_fields(THD *thd, TABLE *table, List<Item> &fields,
-                               List<Item> &values)
+                               List<Item> &values, bool save_query_id)
 {
   if (fields.elements == 0 && values.elements != 0)
   {
@@ -92,6 +96,21 @@
 		      MYF(0), 1L);
       return -1;
     }
+        
+    /* Save query-id and clear so that setup_fields below doesn't
+       issue bogus "Duplicate Columns" when a INSERT...UPDATE is performed */
+    bool *saved_queryid= (save_query_id && table->fields) ?
+                (bool *) my_alloca(sizeof(bool) * table->fields) : NULL;
+    if (saved_queryid)
+    {
+      bool *ptr= saved_queryid;
+      Field **field_ptr= table->field;
+
+      for (;field_ptr && *field_ptr; ++field_ptr, ++ptr)
+        if ((*ptr= (thd->query_id == (*field_ptr)->query_id)))
+          (*field_ptr)->query_id= 0;
+    }
+
     TABLE_LIST table_list;
     bzero((char*) &table_list,sizeof(table_list));
     table_list.db=  table->table_cache_key;
@@ -109,6 +128,18 @@
       my_error(ER_FIELD_SPECIFIED_TWICE,MYF(0), thd->dupp_field->field_name);
       return -1;
     }
+
+    /* Reset saved query-id where neccessary */
+    if (saved_queryid)
+    {
+      bool *ptr= saved_queryid;
+      Field **field_ptr= table->field;
+      
+      for (;field_ptr && *field_ptr; ++field_ptr, ++ptr)
+        if (*ptr)
+          (*field_ptr)->query_id= thd->query_id;
+    }
+
     if (table->timestamp_field &&	// Don't set timestamp if used
 	table->timestamp_field->query_id == thd->query_id)
       clear_timestamp_auto_bits(table->timestamp_field_type,
@@ -543,7 +574,7 @@
     if (!table->insert_values)
       DBUG_RETURN(-1);
   }
-  if ((values && check_insert_fields(thd, table, fields, *values)) ||
+  if ((values && check_insert_fields(thd, table, fields, *values, 0)) ||
       setup_tables(insert_table_list) ||
       (values && setup_fields(thd, 0, insert_table_list, *values, 0, 0, 0)) ||
       (duplic == DUP_UPDATE &&
@@ -1610,7 +1641,8 @@
     we are fixing fields from insert list.
   */
   lex->current_select= &lex->select_lex;
-  res= check_insert_fields(thd, table, *fields, values);
+  res= check_insert_fields(thd, table, *fields, values,
+                           info.handle_duplicates == DUP_UPDATE);
   lex->current_select= lex_current_select_save;
   if (res)
     DBUG_RETURN(1);

--- 1.14/mysql-test/r/insert_update.result	2005-06-27 14:46:33 +01:00
+++ 1.15/mysql-test/r/insert_update.result	2005-07-28 16:33:32 +01:00
@@ -191,3 +191,25 @@
 insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ;
 ERROR 23000: Column 't1.a' in field list is ambiguous
 drop table t1;
+CREATE TABLE `t1` (
+`a` bigint(20) NOT NULL default '0',
+`b` datetime NOT NULL default '0000-00-00 00:00:00',
+`c` bigint(20) unsigned NOT NULL default '0',
+PRIMARY KEY  (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci;
+CREATE TABLE `t2` (
+`a` int(11) NOT NULL default '0',
+`b` datetime NOT NULL default '0000-00-00 00:00:00',
+`r` smallint(5) unsigned NOT NULL default '0',
+`c` bigint(20) unsigned NOT NULL default '0',
+`u` text NOT NULL,
+`f` text NOT NULL,
+KEY `a` (`a`),
+KEY `b` (`b`)
+) ENGINE=MyISAM ;
+INSERT INTO t1 ( `a` , `b` , `c` ) 
+SELECT a, DATE_FORMAT( b, '%Y-%m-%d %H:00:00' ) , SUM( c ) FROM t2
+WHERE c>0 AND r !=404 GROUP BY a, b
+ON DUPLICATE KEY UPDATE c = c + VALUES (c) ;
+DROP TABLE `t1`;
+DROP TABLE `t2`;

--- 1.15/mysql-test/t/insert_update.test	2005-07-28 01:21:43 +01:00
+++ 1.16/mysql-test/t/insert_update.test	2005-07-28 16:33:32 +01:00
@@ -101,4 +101,35 @@
 insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ;
 drop table t1;
 
+#
+# Bug#10109 - INSERT .. SELECT ... ON DUPLICATE KEY UPDATE fails
+# Bogus "Duplicate columns" error message
+#
+
+CREATE TABLE `t1` (
+  `a` bigint(20) NOT NULL default '0',
+  `b` datetime NOT NULL default '0000-00-00 00:00:00',
+  `c` bigint(20) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci;
+
+CREATE TABLE `t2` (
+  `a` int(11) NOT NULL default '0',
+  `b` datetime NOT NULL default '0000-00-00 00:00:00',
+  `r` smallint(5) unsigned NOT NULL default '0',
+  `c` bigint(20) unsigned NOT NULL default '0',
+  `u` text NOT NULL,
+  `f` text NOT NULL,
+  KEY `a` (`a`),
+  KEY `b` (`b`)
+) ENGINE=MyISAM ;
+
+INSERT INTO t1 ( `a` , `b` , `c` ) 
+  SELECT a, DATE_FORMAT( b, '%Y-%m-%d %H:00:00' ) , SUM( c ) FROM t2
+  WHERE c>0 AND r !=404 GROUP BY a, b
+  ON DUPLICATE KEY UPDATE c = c + VALUES (c) ;
+
+DROP TABLE `t1`;
+DROP TABLE `t2`;
+
 # End of 4.1 tests