"mysql 'values function' is deprecated" Code Answer

basically, mysql is looking toward removing a longstanding non-standard use of the values function to clear the way for some future work where the sql standard allows using a values keyword for something very different, and because how the values function works in subqueries or not in a on duplicate key update clause can be surprising.

you need to add an alias to the values clause and then use that alias instead of the non-standard values function in the on duplicate key update clause, e.g. change

insert into foo (bar, baz) values (1,2)
on duplicate key update baz=values(baz)

to

insert into foo (bar, baz) values (1,2) as new_foo
on duplicate key update baz=new_foo.baz

(this only works on mysql 8+, not on older versions or in any version of mariadb through at least 10.6.1)

from https://dev.mysql.com/worklog/task/?id=13325:

according to the sql standard, values is a table value constructor that returns a table. in mysql this is true for simple insert and replace statements, but mysql also uses values to refer to values in insert ... on duplicate key update statements. e.g.:

insert into t(a,b) values (1, 2) on duplicate key update a = values (b) + 1;

values (b) refers to the value for b in the table value constructor for the insert, in this case 2.

to make the value available in simple arithmetic expressions, it is part of the parser rule for simple_expr. unfortunately, this also means that values can be used in this way in a lot of other statements, e.g.:

select a from t where a=values(a);

in all such statements, values returns null, so the above query would not have the intended effect. the only meaningful usage of values as a function, rather than a table value constructor, is in insert ... on duplicate key update. also, the non-standard use in insert ... on duplicate key update does not extend to subqueries. e.g.:

insert into t1 values(1,2) on duplicate key update a=(select a from t2 where b=values(b));

this does not do what the user expects. values(b) will return null, even if it is in an insert .. on duplicate key update statement.

the non-standard syntax also makes it harder (impossible?) to implement standard behavior of values as specified in feature f641 "row and table constructors".