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

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

F-1: If an INSERT statement has an ON DUPLICATE KEY UPDATE clause, and the ON DUPLICATE KEY UPDATE clause references the VALUE function in the top-level query block, a deprecation warning should be raised and the message text should suggest using the new syntax.

F-2: If an INSERT statement has an ON DUPLICATE KEY UPDATE clause, and the ON DUPLICATE KEY UPDATE clause references the VALUE function in a subquery, a deprecation warning should be raised and the message text should say that VALUES always returns NULL in this context, and that the user should check if it was a mistake and consider using the new syntax.

F-3: If the VALUES function (not the VALUES table value constructor) is used in another context than the ON DUPLICATE KEY UPDATE clause of an INSERT statement, a deprecation warning should be raised and the message text should tell that the syntax will be removed in a future release.

Warnings will be raised by Item_insert_value::fix_fields().

The deprecated function is used by the X DevAPI for UPSERT operations. The worklog will change how UPSERT operations are transformed into a SQL statements, and produce SQL statements that don't use the deprecated function.