Bug #46997 ON DUPLICATE UPDATE does not react on change in one field
Submitted: 29 Aug 2009 22:19 Modified: 4 Sep 2009 9:14
Reporter: Miran Cvenkel Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.1.31-community, 4.1, 5.0, 5.1, next bzr OS:Any (MS Windows, Linux)
Assigned to: CPU Architecture:Any
Tags: duplicate, EFFECT, no, on, UPDATE
Triage: Needs Triage: D2 (Serious)

[29 Aug 2009 22:19] Miran Cvenkel
Description:
/*sample table,data*/
/*had myisam, long col names, changed all that, no change*/
/*each time update on second col i.e. 'c_i' does not work*/

CREATE TABLE `galery_1_plant` (
  `id_galery` int(10) unsigned zerofill NOT NULL COMMENT 'ključ na galery_1',
  `a_i` int(10) unsigned NOT NULL,
  `b_c` varchar(100) COLLATE utf8_slovenian_ci DEFAULT NULL,
  `c_i` int(10) unsigned NOT NULL,
  `d_c` varchar(100) COLLATE utf8_slovenian_ci DEFAULT NULL,
  PRIMARY KEY (`id_galery`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci ROW_FORMAT=DYNAMIC;

INSERT INTO `galery_1_plant` (`id_galery`,`a_i`,`b_c`,`c_i`,`d_c`) VALUES 
 (0000000000,10,'45',0,'34');

How to repeat:
/*if you look after ON DUPLICATE KEY UPDATE*/
/*there are values 1,2,3,4*/
/*change any of 1,2,4 works ok, i.e update happens*/
/*change of 2 has no effect, update does not happen, no error either*/

INSERT INTO galery_1_plant
    (
      id_galery,
      a_i,
      c_i,
      b_c,
      d_c
    )
    values
    (
      0,
      0,
      0,
      '',
      ''
    )
    ON DUPLICATE KEY UPDATE
    a_i = if(a_i='',a_i,1),
    c_i = if(c_i='',c_i,2),
    b_c = if(b_c='',b_c,'3'),
    d_c = if(d_c='',d_c,'4')

Suggested fix:
have no idea.
[29 Aug 2009 22:25] Miran Cvenkel
even went and order columns in update sql in order as they appear in table, no change , c_i does not want to update.
[29 Aug 2009 22:34] Miran Cvenkel
hmm, went to mysql query analyzer, changed 0 in c_i column to something randome else. Now it works. 
Tested it in a_i column, same thing . 0 data is causing the problem. ON DUPLICATE UPDATE does not see 0 as something that needs to be changed.
[29 Aug 2009 22:52] Miran Cvenkel
mybe this is not a bug at all if '' = 0
[30 Aug 2009 6:44] Valeriy Kravchuk
Sorry, but the only key you have on this table is:

  PRIMARY KEY (`id_galery`)

So, duplicates in any other column are not identifiable. Please, check.
[3 Sep 2009 21:25] Miran Cvenkel
the bottom line here is that MYSQL consider this (on field type of INT):

if(field_of_type_int='' ...
if(field_of_type_int=0  ...

as same thing, so update wouldn't take place if there was 0 in database at condition if(field_of_type_int=''

looks strange.
[4 Sep 2009 5:21] Sveta Smirnova
Thank you for the feedback.

Verified as described:

INSERT INTO `galery_1_plant` (`id_galery`,`a_i`,`b_c`,`c_i`,`d_c`) VALUES  (0,10,'45',0,'34');
select * from galery_1_plant;
id_galery       a_i     b_c     c_i     d_c
0000000000      10      45      0       34
INSERT ... ON DUPLICATE KEY UPDATE ...
select * from galery_1_plant;
id_galery       a_i     b_c     c_i     d_c
0000000000      1       3       0       4
[4 Sep 2009 7:32] Sergei Golubchik
of course, it does. see http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html
[4 Sep 2009 7:42] Sveta Smirnova
Sergei,

correct.

But look into table definition:

...
 `a_i` int(10) unsigned NOT NULL,
...
 `c_i` int(10) unsigned NOT NULL,
...

a_i and c_i have same type. But why a_i is updated while c_i is not?
[4 Sep 2009 9:14] Sergei Golubchik
let me guess... because he compares with zero and c_i in fact *is* zero ?
:)