pingcap / pingcap/tidb

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

Open
#51,650 3 comments 1 reaction 0 assignees View on GitHub
compatibility-mysql80 type/compatibility type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## 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
https://github.com/mysql/mysql-server/commit/c39355e9e6985a8aac4df58d8a56ce91f166d27d

- 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
https://github.com/mysql/mysql-server/commit/6f3b9df50b6aee5f1f868dc099cc27c660cac130

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

```sql
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.
```sql
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:**

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.