INSERT ... ON DUPLICATE KEY UPDATE ...

7 files changed, 130 insertions, 25 deletions

diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
new file mode 100644
index 00000000000..e02d4ebb588
--- /dev/null
+++ b/mysql-test/r/insert_update.result
@@ -0,0 +1,44 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
+INSERT t1 VALUES (1,2,10), (3,4,20);
+INSERT t1 VALUES (5,6,30) ON DUPLICATE KEY UPDATE SET c=c+100;
+SELECT * FROM t1;
+a	b	c
+1	2	10
+3	4	20
+5	6	30
+INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE SET c=c+100;
+SELECT * FROM t1;
+a	b	c
+1	2	10
+3	4	20
+5	6	130
+INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE SET c=c+1000;
+SELECT * FROM t1;
+a	b	c
+1	2	10
+3	4	1020
+5	6	130
+INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE SET c=c+10000;
+SELECT * FROM t1;
+a	b	c
+1	2	10010
+3	4	1020
+5	6	130
+INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE SET c=c+100000, b=4;
+Duplicate entry '4' for key 2
+SELECT * FROM t1;
+a	b	c
+1	2	10010
+3	4	1020
+5	6	130
+TRUNCATE TABLE t1;
+INSERT t1 VALUES (1,2,10), (3,4,20);
+INSERT t1 VALUES (5,6,30), (7,4,40), (8,9,60) ON DUPLICATE KEY UPDATE SET c=c+100;
+SELECT * FROM t1;
+a	b	c
+1	2	10
+3	4	120
+5	6	30
+8	9	60
+DROP TABLE t1;
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
new file mode 100644
index 00000000000..1c0cfff8167
--- /dev/null
+++ b/mysql-test/t/insert_update.test
@@ -0,0 +1,19 @@
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
+INSERT t1 VALUES (1,2,10), (3,4,20);
+INSERT t1 VALUES (5,6,30) ON DUPLICATE KEY UPDATE SET c=c+100;
+SELECT * FROM t1;
+INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE SET c=c+100;
+SELECT * FROM t1;
+INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE SET c=c+1000;
+SELECT * FROM t1;
+INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE SET c=c+10000;
+SELECT * FROM t1;
+-- error 1062
+INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE SET c=c+100000, b=4;
+SELECT * FROM t1;
+TRUNCATE TABLE t1;
+INSERT t1 VALUES (1,2,10), (3,4,20);
+INSERT t1 VALUES (5,6,30), (7,4,40), (8,9,60) ON DUPLICATE KEY UPDATE SET c=c+100;
+SELECT * FROM t1;
+DROP TABLE t1;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 242f8601b23..94065599fc8 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -429,7 +429,7 @@ int mysql_alter_table(THD *thd, char *new_db, char *new_name,
 		      bool drop_primary,
 		      enum enum_duplicates handle_duplicates,
 		      enum enum_enable_or_disable keys_onoff=LEAVE_AS_IS,
-    		      bool simple_alter=0);
+		      bool simple_alter=0);
 bool mysql_rename_table(enum db_type base,
 			const char *old_db,
 			const char * old_name,
@@ -440,11 +440,12 @@ int mysql_create_index(THD *thd, TABLE_LIST *table_list, List<Key> &keys);
 int mysql_drop_index(THD *thd, TABLE_LIST *table_list,
 		     List<Alter_drop> &drop_list);
 int mysql_update(THD *thd,TABLE_LIST *tables,List<Item> &fields,
-		 List<Item> &values,COND *conds, 
+		 List<Item> &values,COND *conds,
                  ORDER *order, ha_rows limit,
 		 enum enum_duplicates handle_duplicates);
 int mysql_insert(THD *thd,TABLE_LIST *table,List<Item> &fields,
-		 List<List_item> &values, enum_duplicates flag);
+		 List<List_item> &values, List<Item> &update_fields,
+		 List<Item> &update_values, enum_duplicates flag);
 void kill_delayed_threads(void);
 int mysql_delete(THD *thd, TABLE_LIST *table, COND *conds, ORDER *order,
                  ha_rows rows, ulong options);
diff --git a/sql/sql_class.h b/sql/sql_class.h
index a521da9a33f..5326b66e56e 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -29,12 +29,12 @@ class Slave_log_event;
 
 enum enum_enable_or_disable { LEAVE_AS_IS, ENABLE, DISABLE };
 enum enum_ha_read_modes { RFIRST, RNEXT, RPREV, RLAST, RKEY };
-enum enum_duplicates { DUP_ERROR, DUP_REPLACE, DUP_IGNORE };
+enum enum_duplicates { DUP_ERROR, DUP_REPLACE, DUP_IGNORE, DUP_UPDATE };
 enum enum_log_type { LOG_CLOSED, LOG_NORMAL, LOG_NEW, LOG_BIN };
 enum enum_delay_key_write { DELAY_KEY_WRITE_NONE, DELAY_KEY_WRITE_ON,
 			    DELAY_KEY_WRITE_ALL };
 
-// log info errors 
+// log info errors
 #define LOG_INFO_EOF -1
 #define LOG_INFO_IO  -2
 #define LOG_INFO_INVALID -3
@@ -188,6 +188,9 @@ typedef struct st_copy_info {
   ha_rows error_count;
   enum enum_duplicates handle_duplicates;
   int escape_char, last_errno;
+/* for INSERT ... UPDATE */
+  List<Item> *update_fields;
+  List<Item> *update_values;
 } COPY_INFO;
 
 
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 1ca44046997..66ddfab59a8 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -97,8 +97,12 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
 }
 
 
-int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
-		 List<List_item> &values_list,enum_duplicates duplic)
+int mysql_insert(THD *thd,TABLE_LIST *table_list,
+                 List<Item> &fields,
+                 List<List_item> &values_list,
+                 List<Item> &update_fields,
+                 List<Item> &update_values,
+                 enum_duplicates duplic)
 {
   int error;
   bool log_on= ((thd->options & OPTION_UPDATE_LOG) ||
@@ -126,7 +130,8 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
   if ((lock_type == TL_WRITE_DELAYED &&
        ((specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) ||
 	thd->slave_thread)) ||
-      (lock_type == TL_WRITE_CONCURRENT_INSERT && duplic == DUP_REPLACE))
+      (lock_type == TL_WRITE_CONCURRENT_INSERT && duplic == DUP_REPLACE) ||
+      (duplic == DUP_UPDATE))
     lock_type=TL_WRITE;
   table_list->lock_type= lock_type;
 
@@ -166,7 +171,10 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
   values= its++;
   if (check_insert_fields(thd,table,fields,*values,1) ||
       setup_tables(insert_table_list) ||
-      setup_fields(thd, insert_table_list, *values, 0, 0, 0))
+      setup_fields(thd, insert_table_list, *values, 0, 0, 0) ||
+      (duplic == DUP_UPDATE &&
+       (setup_fields(thd, insert_table_list, update_fields, 0, 0, 0) ||
+        setup_fields(thd, insert_table_list, update_values, 0, 0, 0))))
   {
     table->time_stamp= save_time_stamp;
     goto abort;
@@ -196,6 +204,8 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
 
   info.records=info.deleted=info.copied=0;
   info.handle_duplicates=duplic;
+  info.update_fields=&update_fields;
+  info.update_values=&update_values;
   // Don't count warnings for simple inserts
   if (values_list.elements > 1 || (thd->options & OPTION_WARNINGS))
     thd->count_cuted_fields = 1;
@@ -205,7 +215,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
   error=0;
   id=0;
   thd->proc_info="update";
-  if (duplic == DUP_IGNORE || duplic == DUP_REPLACE)
+  if (duplic != DUP_ERROR)
     table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
   if ((bulk_insert= (values_list.elements >= MIN_ROWS_TO_USE_BULK_INSERT &&
 		     lock_type != TL_WRITE_DELAYED &&
@@ -350,7 +360,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
   table->next_number_field=0;
   thd->count_cuted_fields=0;
   thd->next_insert_id=0;			// Reset this if wrongly used
-  if (duplic == DUP_IGNORE || duplic == DUP_REPLACE)
+  if (duplic != DUP_ERROR)
     table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
   if (error)
     goto abort;
@@ -402,7 +412,8 @@ int write_record(TABLE *table,COPY_INFO *info)
   char *key=0;
 
   info->records++;
-  if (info->handle_duplicates == DUP_REPLACE)
+  if (info->handle_duplicates == DUP_REPLACE ||
+      info->handle_duplicates == DUP_UPDATE)
   {
     while ((error=table->file->write_row(table->record[0])))
     {
@@ -419,7 +430,9 @@ int write_record(TABLE *table,COPY_INFO *info)
 	was used.  This ensures that we don't get a problem when the
 	whole range of the key has been used.
       */
-      if (table->next_number_field && key_nr == table->next_number_index &&
+      if (info->handle_duplicates == DUP_REPLACE &&
+          table->next_number_field &&
+          key_nr == table->next_number_index &&
 	  table->file->auto_increment_column_changed)
 	goto err;
       if (table->file->table_flags() & HA_DUPP_POS)
@@ -451,16 +464,33 @@ int write_record(TABLE *table,COPY_INFO *info)
 						HA_READ_KEY_EXACT))))
 	  goto err;
       }
-      if (last_uniq_key(table,key_nr))
+      if (info->handle_duplicates == DUP_UPDATE)
       {
-	if ((error=table->file->update_row(table->record[1],table->record[0])))
-	  goto err;
-	info->deleted++;
-	break;					/* Update logfile and count */
+        /* we don't check for other UNIQUE keys - the first row
+           that matches, is updated. If update causes a conflict again,
+           an error is returned
+        */
+        restore_record(table,1);
+        if (fill_record(*info->update_fields,*info->update_values))
+          goto err;
+        if ((error=table->file->update_row(table->record[1],table->record[0])))
+          goto err;
+        info->deleted++;
+        break;
+      }
+      else /* DUP_REPLACE */
+      {
+        if (last_uniq_key(table,key_nr))
+        {
+          if ((error=table->file->update_row(table->record[1],table->record[0])))
+            goto err;
+          info->deleted++;
+          break;					/* Update logfile and count */
+        }
+        else if ((error=table->file->delete_row(table->record[1])))
+          goto err;
+        info->deleted++;
       }
-      else if ((error=table->file->delete_row(table->record[1])))
-	goto err;
-      info->deleted++;
     }
     info->copied++;
   }
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index ad2a116b621..f7c5fac0b06 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1969,14 +1969,21 @@ mysql_execute_command(THD *thd)
   case SQLCOM_REPLACE:
   case SQLCOM_INSERT:
   {
+    my_bool update=(lex->value_list.elements ? UPDATE_ACL : 0);
     ulong privilege= (lex->duplicates == DUP_REPLACE ?
-                      INSERT_ACL | DELETE_ACL : INSERT_ACL);
+                      INSERT_ACL | DELETE_ACL : INSERT_ACL | update);
     if (check_access(thd,privilege,tables->db,&tables->grant.privilege))
       goto error; /* purecov: inspected */
     if (grant_option && check_grant(thd,privilege,tables))
       goto error;
+    if (select_lex->item_list.elements != lex->value_list.elements)
+    {
+      send_error(thd,ER_WRONG_VALUE_COUNT);
+      DBUG_VOID_RETURN;
+    }
     res = mysql_insert(thd,tables,lex->field_list,lex->many_values,
-		       lex->duplicates);
+                       select_lex->item_list, lex->value_list,
+	               (update ? DUP_UPDATE : lex->duplicates));
     break;
   }
   case SQLCOM_REPLACE_SELECT:
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 13836902736..ef3b9002114 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1459,7 +1459,7 @@ alter_list_item:
 	    lex->simple_alter=0;
 	  }
 	| RENAME opt_to table_ident
-	  { 
+	  {
 	    LEX *lex=Lex;
 	    lex->select_lex.db=$3->db.str;
 	    lex->name= $3->table.str;
@@ -3045,7 +3045,7 @@ expr_or_default:
 
 opt_insert_update:
         /* empty */
-        | ON DUPLICATE KEY_SYM UPDATE_SYM SET update_list
+        | ON DUPLICATE
           { /* for simplisity, let's forget about
                INSERT ... SELECT ... UPDATE
                for a moment */
@@ -3055,6 +3055,7 @@ opt_insert_update:
               YYABORT;
             }
           }
+          KEY_SYM UPDATE_SYM SET update_list
         ;
 
 /* Update rows in a table */