Support MySQL 8.0.19 feature - aliases in the values and the set clause of insert into ... on duplicate key update

[yahonda]

Feature Request

I want TiDB supports one of the MySQL 8.0.19 new feature, aliases in the values and the set clause of insert into ... on duplicate key update. Not only adding as a new feature MySQL 8.0.20 deprecates the "old" syntax.

• MySQL 8.0.19 supports aliases in the VALUES and SET clauses of INSERT INTO ... ON DUPLICATE KEY UPDATE statement https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html

MySQL now supports aliases in the VALUES and SET clauses of INSERT INTO ... ON DUPLICATE KEY UPDATE statement
for the row to be inserted and its columns. Consider a statement such as this one:

https://dev.mysql.com/worklog/task/?id=6312
mysql/mysql-server@c39355e

• MySQL 8.0.20 deprecates the old VALUES() syntax in INSERT ... ON DUPLICATE KEY UPDATE statements

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html

The use of VALUES() to access new row values in INSERT ... ON DUPLICATE KEY UPDATE statements
is now deprecated, and is subject to removal in a future MySQL release.
Instead, you should use aliases for the new row and its columns as implemented in MySQL 8.0.19 and later.

https://dev.mysql.com/worklog/task/?id=13325
mysql/mysql-server@6f3b9df

Describe the feature you'd like:
Support aliases in the values and the set clause of insert into ... on duplicate key update.

• MySQL 8.0.20 and newer versions of MySQL supports aliases in the values insert into ... on duplicate key update.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.3.0     |
+-----------+
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE t1
    -> (col1 integer primary key,
    -> col2 integer
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO t1 values(1,1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO t1(col1, col2)
    ->   VALUES (1, 2) AS new(col1, new2)
    ->   ON DUPLICATE KEY UPDATE col1 = new.col1 + new2;
Query OK, 2 rows affected (0.00 sec)

mysql>
mysql> select * from t1;
+------+------+
| col1 | col2 |
+------+------+
|    3 |    1 |
+------+------+
1 row in set (0.00 sec)

• MySQL 8.0.20 and newer versions of MySQL raises the warnings for "old" syntax.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.3.0     |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t
    -> (
    -> a integer primary key,
    -> b integer
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO t
    ->     VALUES (9,5), (7,7), (11,-1)
    ->     ON DUPLICATE KEY UPDATE a = a + VALUES(a) - VALUES(b);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'VALUES function' 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 |
| Warning | 1287 | 'VALUES function' 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 |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Describe alternatives you've considered:
Use the "old" syntax.

Teachability, Documentation, Adoption, Migration Strategy:

[dveeden]

Directly related, but without ON DUPLICATE KEY UPDATE:

create table t1(id int primary key);
insert into t1(id) values (1) as abc;

MySQL 8.4:

mysql-8.4.7 [test]> create table t1(id int primary key);
Query OK, 0 rows affected (0.009 sec)

mysql-8.4.7 [test]> insert into t1(id) values (1) as abc;
Query OK, 1 row affected (0.020 sec)

TiDB v8.5:

mysql-8.0.11-TiDB-v8.5.4 [test]> create table t1(id int primary key);
Query OK, 0 rows affected (0.018 sec)

mysql-8.0.11-TiDB-v8.5.4 [test]> insert into t1(id) values (1) as abc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 32 near "as abc" 

[dveeden]

This is where this is used in Hibernate:

https://github.com/hibernate/hibernate-orm/blob/f8d7767b8a37152b5aa6f1bf70e0e418bb21b163/hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/MySQLSqlAstTranslator.java#L437-L460

It might be possible to modify the TiDBDialect of Hibernate to avoid this. However this is probably not possible with the MySQLDialect. In any case it would be good to implement support for this.

[yahonda]

Ruby on Rails 7.1.4 or higher version of Rails supports this syntax via rails/rails#51274 and it switches
If TiDB version says it is higher than 8.0.19 rails/rails#51348, it would fail.

In any case it would be good to implement support for this.

Agreed.