We are trying to investigate a scenario where we are updating a singlestore table using the ON DUPLICATE KEY UPDATE clause and the VALUES() function.
The following two cases are such that one of the INSERT query is using the VALUES() function on the primary key ‘key1’ as well, whereas the other does not.
We see that both of them are working and giving out the same results. Which one is the correct one here? and why?
Code for example:
create table sandbox.abc (key1 int(11), col1 varchar(3), col2 varchar(3),
UNIQUE KEY PRIMARY
(key1
) USING HASH,
SHARD KEY __SHARDKEY
(key1
));
select * from sandbox.abc;
insert into sandbox.abc values(123, ‘name1’, ‘abc’);
insert into sandbox.abc values(123, ‘name1’, ‘cee’)
on duplicate key update
col1 = values(col1),
col2 = values(col2);
insert into sandbox.abc values(123, ‘name1’, ‘cde’)
on duplicate key update
key1 = values(key1),
col1 = values(col1),
col2 = values(col2);