It appears that VALUES() with IF() in MySQL INSERT..ON DUPLICATE not working as expected or as documented.
I'm working with some complex queries that are failing, likely due to it. Here is a simple example to illustrate the problem, using a table with a unique `id` and value `v`:
CREATE TABLE `test` (
`id` int unsigned NOT NULL DEFAULT '0',
`v` int unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
mysql> insert into test set id=1, v=if(1 > v, 1, v) on duplicate key update v=values(v);
Query OK, 2 rows affected, 1 warning (0.02 sec)
mysql> select * from test;
+----+---+
| id | v |
+----+---+
| 1 | 1 |
+----+---+
1 row in set (0.00 sec)
This inserts v=1 as expected.
After above insert, I use the second query, the value of v becomes 0 which I didn't expect.
mysql> insert into test set id=1, v=if(0 > v, 0, v) on duplicate key update v=values(v);
Query OK, 2 rows affected, 1 warning (0.01 sec)
mysql> select * from test;
+----+---+
| id | v |
+----+---+
| 1 | 0 |
+----+---+
1 row in set (0.00 sec)
I assumed that since the value of `v` is `1` at the time of second query, the v=if(0 > v, 0, v) evaluates to the existing value of v. Therefore, I expected the `ON DUPLICATE KEY UPDATE` to also retain the value as per the documentation quoted below. However, instead of that, values is updated to 0.
Can someone explain why the value of `v` is changed to 0 in the second query?
>
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
> In assignment value expressions in the ON DUPLICATE KEY UPDATE clause,
> you can use the VALUES(col_name) function to refer to column values
> from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE
> statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY
> UPDATE clause refers to the value of col_name that would be inserted,
> had no duplicate-key conflict occurred.