WL#13325: Deprecate VALUES syntax in INSERT ... ON DUPLICATE KEY UPDATE

Affects: Server-8.0   —   Status: Complete

WL#6312 implements a new syntax to access the new row values in INSERT ... ON DUPLICATE KEY UPDATE statements using aliases instead of the VALUES function. This WL deprecates the old use of VALUES to do the same.

The VALUES syntax should be deprecated and a warning message issued when the VALUES definition of the simple_expr rule is used. If the VALUES usage is meaningful (i.e. not always NULL in the given context), it should warn that: "'VALUES is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead".

If the usage is meaningless (i.e., always NULL in the given context), but in an ON DUPLICATE KEY UPDATE context, a warning should be issued that this syntax always returns NULL, but that using the new syntax may be what the user intends.

If the usage is meaningless and not in an ON DUPLICATE KEY UPDATE, a warning should be issued: "The syntax 'VALUES' is deprecated and will be removed in a future version".

Rationale

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".