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
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.51
• OS: Any
• 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;