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: | |
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
[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;