Bug #19978 INSERT ... ON DUPLICATE KEY - rows affected incorrect
Submitted: 21 May 2006 6:15 Modified: 27 Feb 2007 20:30
Reporter: Richard Dale Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.20-BK, 4.1.19 OS:Linux (Fedora Core 4 (Linux) 2.6.16)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[21 May 2006 6:15] Richard Dale
Description:
Mysql 4.1.19 running on Fedora Core 4 (Linus) X86_64 with InnoDB tables

According to:
http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html
The rows-affected value is 1 if the row is inserted as a new record and 2 if
an existing record is updated. 

But what happens if the row is neither inserted nor updated?  Right now it
appears to returns '2'.  It should return '0'.

How to repeat:
I have a table called  which has a (combined) primary key of assetid and
date plus several data fields (open, high, low, close - all doubles, and
voume - a bigint).  See the bottom of the email for the table definition.

INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES 
(202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE
KEY UPDATE
open='334.25',high='334.25',low='334.25',close='334.25',volume='1';

The first time around, the insert works fine:
Query OK, 1 row affected (0.03 sec);

The second time around, when there should be neither an insert nor an update
that affects any rows I get:
Query OK, 2 rows affected (0.05 sec)

To check that the update wasn't in fact updating any rows, I tried:

UPDATE price SET
open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE
assetid=202690 AND date='2006-05-18';
Query OK, 0 rows affected (0.02 sec)

Rows matched: 1  Changed: 0  Warnings: 0

So, it appears that the "rows affected" returned by an INSERT ... ON
DUPLICATE KEY is incorrect.

-----
Table definition:

CREATE TABLE `price` (
  `assetid` int(11) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `open` double default NULL,
  `high` double default NULL,
  `low` double default NULL,
  `close` double default NULL,
  `volume` bigint(20) default NULL,
  PRIMARY KEY  (`assetid`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

------

I also tried changing the table definition to from DOUBLE to DECIMAL(10,2) and also VARCHAR(20) to avoid any issues with precision but this also has the same problem.

Suggested fix:
If INSERT ... ON DUPLICATE KEY UPDATE
neither inserts nor updates a record then it should return 0.
[21 May 2006 14:41] Valeriy Kravchuk
Thank you for a problem report. I was able to repeat the behaviour you described with 4.1.20-BK:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `price` (
    ->   `assetid` int(11) NOT NULL default '0',
    ->   `date` date NOT NULL default '0000-00-00',
    ->   `open` double default NULL,
    ->   `high` double default NULL,
    ->   `low` double default NULL,
    ->   `close` double default NULL,
    ->   `volume` bigint(20) default NULL,
    ->   PRIMARY KEY  (`assetid`,`date`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES
    -> (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICA
TE
    -> KEY UPDATE
    -> open='334.25',high='334.25',low='334.25',close='334.25',volume='1';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES  (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1';
Query OK, 2 rows affected (0.01 sec)

mysql> UPDATE price SET
    -> open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE
    -> assetid=202690 AND date='2006-05-18';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES  (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='2';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from price;
+---------+------------+--------+--------+--------+--------+--------+
| assetid | date       | open   | high   | low    | close  | volume |
+---------+------------+--------+--------+--------+--------+--------+
|  202690 | 2006-05-18 | 334.25 | 334.25 | 334.25 | 334.25 |      2 |
+---------+------------+--------+--------+--------+--------+--------+
1 row in set (0.00 sec)

So, there is no way to distinguish real update (with volume=2) with a "fake" update (all the same values used), if INSERT ... ON DUPLICATE KEY UPDATE is used. It is a bug. Something like

Rows matched: 1  Changed: 0  Warnings: 0

should be returned, at least.
[24 May 2006 10:17] Heikki Tuuri
This is probably a bug/feature request in MySQL. InnoDB does not take part in calculating 'affected rows'.
[6 Feb 2007 19:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19434

ChangeSet@1.2403, 2007-02-06 22:32:56+03:00, evgen@moonbone.local +5 -0
  Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were
  updated.
  
  INSERT ... ON DUPLICATE KEY UPDATE reports that a record was updated when
  the duplicate key occurs even if the record wasn't actually changed
  because the update values are the same as these in the record.
  
  Now the compare_record() function is used to check whether the record was
  changed and the update of a record reported only if the record differs
  from the original one.
[6 Feb 2007 21:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19450

ChangeSet@1.2403, 2007-02-07 00:46:03+03:00, evgen@moonbone.local +5 -0
  Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were
  updated.
  
  INSERT ... ON DUPLICATE KEY UPDATE reports that a record was updated when
  the duplicate key occurs even if the record wasn't actually changed
  because the update values are the same as those in the record.
  
  Now the compare_record() function is used to check whether the record was
  changed and the update of a record reported only if the record differs
  from the original one.
[14 Feb 2007 10:28] Igor Babaev
The fix has been pushed to 5.0.36 and 5.1.16-beta.
(It will not be fixed in 4.1)
[27 Feb 2007 20:30] Paul Dubois
Noted in 5.0.36, 5.1.16 changelogs.
[22 Mar 2007 14:48] Laurent Goujon
I just saw this new behaviour and I don't understand (apart from the meaning of 'updated') where was the bug ?

The old behaviour permitted to compute how many rows were new and how many were presents deducted on the return value and the number of rows in the request. I don't think it is still possible to do with the new behaviour.

As a matter of fact, it is a incompatible change of behaviour and should'nt happen in a stable release (One of my application relies on the old behaviour to perform check and will be broken with this change).
[22 Mar 2007 15:35] Evgeny Potemkin
Side effects of the fix for this bug are bug#27033(fixed in 5.0.40/5.1.17) and
bug#27006(fixed in 5.0.36/5.1.16 by Monty)
[22 Mar 2007 17:18] Evgeny Potemkin
Bug#27006 is fixed in the version 5.0.38, not in 5.0.36.
[2 Apr 2007 8:03] Corin Langosch
affected rows does not seem to be correctly calculated in all situations, as you can see from the following example:

CREATE TABLE `webcams` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `owner_id` int(10) unsigned NOT NULL default '0',
  `last_online` int(10) unsigned NOT NULL default '0',
  `num_online` int(10) unsigned NOT NULL default '0',
  `descr` varchar(200) collate latin1_german1_ci NOT NULL default '',
  `last_ip` varchar(15) collate latin1_german1_ci NOT NULL default '',
  `last_port` int(10) unsigned NOT NULL default '0',
  `last_width` smallint(5) unsigned NOT NULL default '0',
  `last_height` smallint(5) unsigned NOT NULL default '0',
  `online` tinyint(3) unsigned NOT NULL default '0',
  `active` tinyint(3) unsigned NOT NULL default '0',
  `pass` varchar(20) collate latin1_german1_ci NOT NULL default '',
  `local_ip` varchar(15) collate latin1_german1_ci NOT NULL default '',
  `skey` varchar(32) collate latin1_german1_ci NOT NULL default '',
  `mkey` varchar(32) collate latin1_german1_ci NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `owner_id` (`owner_id`),
  KEY `online` (`online`),
  KEY `combo4` (`active`,`online`,`last_online`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=3 ;

INSERT INTO webcams SET owner_id=1,active='1',descr='super cam!!',pass='test' ON DUPLICATE KEY UPDATE owner_id=1,active='1',descr='super cam!!',pass='test',online=online+11;
affected rows: 1

INSERT INTO webcams SET owner_id=1,active='1',descr='super cam!!',pass='test' ON DUPLICATE KEY UPDATE owner_id=1,active='1',descr='super cam!!',pass='test',online=online+1;
affected rows: 0 (should be 2!!!)
[13 Jan 2009 16:21] Valeriy Kravchuk
Corin,

What exact version do you use?