ON DUPLICATE KEY UPDATE not work

[devin-zhao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   create table:
   CREATE TABLE video_comment (
   video_id bigint(20) NOT NULL COMMENT '视频id',
   comment_id bigint(20) NOT NULL DEFAULT 0 COMMENT '评论id',
   content varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '评论内容',
   publish_time timestamp(0) NULL DEFAULT NULL COMMENT '评论时间',
   commenter_id bigint(20) NOT NULL DEFAULT 0 COMMENT '用户唯一标识',
   is_invalid tinyint(3) NOT NULL DEFAULT 1 COMMENT '是否为有效评论 1有效 0无效',
   is_owner_published tinyint(3) NOT NULL DEFAULT 0 COMMENT '是否是视频拥有者发布的评论 0不是 1是',
   created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '抓取时间',
   sentiment float NOT NULL DEFAULT 0,
   PRIMARY KEY (comment_id) USING BTREE,
   INDEX idx_vid_time_cid(video_id, publish_time, commenter_id) USING BTREE
   ) CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '视频评论表';

execute sql:
INSERT INTO video_comment (video_id, sentiment, comment_id) VALUES (974712634567720044, 0.1234, 4109668666), (974712634567720044, 0.0167704, 4109668667) ON DUPLICATE KEY UPDATE sentiment = VALUES(sentiment)
insert success

execute sql:
INSERT INTO video_comment (video_id, sentiment, comment_id) VALUES (974712634567720044, 0.2222, 4109668666), (974712634567720044, 0.2222, 4109668667) ON DUPLICATE KEY UPDATE sentiment = VALUES(sentiment)

2. What did you expect to see?
   the sentiment field value are changed to 0.2222

3. What did you see instead?
   no field value are changed

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   Release Version: v2.1.2-1-g8ba8096
   Git Commit Hash: 8ba8096
   Git Branch: release-2.1
   UTC Build Time: 2018-12-21 03:45:55
   GoVersion: go version go1.11.2 linux/amd64
   Race Enabled: false
   TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
   Check Table Before Drop: false

[jackysp]

Thanks for your report, @devin-zhao ! I'll take a look.

[jackysp]

Hi @devin-zhao ,
For the question

What did you see instead?
no field value are changed

What did you see exactly?
I've tried your SQL, the sentiment field is always 0, both before or after the following SQL executed

INSERT INTO video_comment (video_id, sentiment, comment_id) VALUES (974712634567720044, 0.2222, 4109668666), (974712634567720044, 0.2222, 4109668667) ON DUPLICATE KEY UPDATE sentiment = VALUES(sentiment)

mysql> select sentiment from video_comment;
+-----------+
| sentiment |
+-----------+
|         0 |
|         0 |
+-----------+
2 rows in set (0.00 sec)

[devin-zhao]

@jackysp update info

1. what did you do?
   execute sql:
   INSERT INTO video_comment (video_id, sentiment, comment_id) VALUES (974712634567720044, 0.1234, 4109668666), (974712634567720044, 0.0167704, 4109668667)

execute sql:
INSERT INTO video_comment (video_id, sentiment, comment_id) VALUES (974712634567720044, 0.2222, 4109668666), (974712634567720044, 0.2222, 4109668667) ON DUPLICATE KEY UPDATE sentiment = VALUES(sentiment)

2. What did you expect to see?
   the sentiment field value are changed to 0.2222

3. What did you see instead?
   Affected rows: 4 and the sentiment field value are changed to 0

[jackysp]

Thanks, @devin-zhao !
I could reproduce it by the following SQLs.

drop table t;
create table t (i int key, j float);
insert into t values (1, 0.01);
select * from t;
insert into t values (1, 0.2) on duplicate key update j = values (j);
select * from t;

It may be caused by values() function does not return the correct data.
PTAL @zz-jason .

[devin-zhao]

@jackysp it will be slient? no solution?

[jackysp]

Hi @devin-zhao ,
Sorry for the late reply! The amount of modification for this may be large and requires an internal assessment. Please refer to @zz-jason for details.