Updates with and without Key Columns

Help Query Tuning
1

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);

2

You are upserting the key value to itself in the 2nd one, so that is a NOOP. So it becomes an UPDATE, not an INSERT, and does not change the key. If the system doesn’t error, and it gives you the same results, it doesn’t matter which one you use. In general, for an INSERT of a new key value, the 2nd one would fail.