Bug #36898 INSERT ... ON DUPLICATE KEY UPDATE seem to compute wrong, if a new row is insert
Submitted: 22 May 2008 22:24 Modified: 25 May 2008 10:04
Reporter: Nikolaj Schutzbach Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.51 OS:Any
Assigned to: CPU Architecture:Any
Tags: insert, ON DUPLICATE KEY UPDATE

[22 May 2008 22:24] Nikolaj Schutzbach
Description:
INSERT ... ON DUPLICATE KEY UPDATE seem to compute wrong, if a new row is inserted and a colum is computed by itself and – for example – 1 ist added.

-- Server Version: 5.0.51
CREATE TABLE `neacmsffk_visit_count_daily` (
  `date` date NOT NULL default '0000-00-00',
  `visit_count` int(11) NOT NULL default '0',
  `visit_count1` int(11) NOT NULL default '0',
  `visit_count2` int(11) NOT NULL default '0',
  PRIMARY KEY  (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Computes wrong:
INSERT neacmsffk_visit_count_daily SET 
`date`='2008-05-19' 
ON DUPLICATE KEY UPDATE 
 visit_count=visit_count+1, visit_count2=visit_count2+1;

-- Computes right:
INSERT neacmsffk_visit_count_daily SET 
`date`='2008-05-18', visit_count=visit_count+1, visit_count2=visit_count2+1;

SELECT * FROM neacmsffk_visit_count_daily;

-- The result:
+------------+-------------+--------------+--------------+
| date       | visit_count | visit_count1 | visit_count2 |
+------------+-------------+--------------+--------------+
| 2008-05-19 |           0 |            0 |            0 |
| 2008-05-18 |           1 |            0 |            1 |
+------------+-------------+--------------+--------------+

-- Expected:
+------------+-------------+--------------+--------------+
| date       | visit_count | visit_count1 | visit_count2 |
+------------+-------------+--------------+--------------+
| 2008-05-19 |           1 |            0 |            1 |
| 2008-05-18 |           1 |            0 |            1 |
+------------+-------------+--------------+--------------+

How to repeat:
-

Suggested fix:
-
[23 May 2008 10:23] Valeriy Kravchuk
Thank you for a problem report. I do not think that it is a bug, though. Your first insert:

INSERT neacmsffk_visit_count_daily SET 
`date`='2008-05-19' 
ON DUPLICATE KEY UPDATE 
 visit_count=visit_count+1, visit_count2=visit_count2+1;

inserts the very first row for a `date`, '2008-05-19', and sets only that column explicitely. So, ON DUPLICATE KEY part does NOT work and other columns got default values, 0. What is a bug here?

Your second INSERT refers to columns without explicitely set values in SET expressions, so default values are used for evaluating expressions.
[25 May 2008 10:04] Nikolaj Schutzbach
Hello Valeriy,

thank you for your fast answer.

But, I think this function is useless, if you can’t insert a (new) row, setting default values and manipulate these (like value = value + 1) in one command. If this isn’t possible, you always need 2 commands for such operations like in my second example: first to check if the row is already inserted (existing), second do the update of the values. In these case, I never need " ON DUPLICATE KEY UPDATE". ;-)

These commands doesn’t the trick:

INSERT neacmsffk_visit_count_daily SET 
`date`='2008-05-19', visit_count=NULL 
ON DUPLICATE KEY UPDATE 
 visit_count=visit_count+1, visit_count2=visit_count2+1;

INSERT neacmsffk_visit_count_daily SET 
`date`='2008-05-19', visit_count='' 
ON DUPLICATE KEY UPDATE 
 visit_count=visit_count+1, visit_count2=visit_count2+1;

INSERT neacmsffk_visit_count_daily SET 
`date`='2008-05-19', visit_count=0 
ON DUPLICATE KEY UPDATE 
 visit_count=visit_count+1, visit_count2=visit_count2+1;