pingcap / pingcap/tidb

tidb doesn't support INSERT...AS alias

Open
#65,918 2 comments 1 reaction 0 assignees View on GitHub
contribution severity/moderate sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```
CREATE TABLE t3 (
id INT PRIMARY KEY,
val INT
);

INSERT INTO t3
SET id = 1, val = 100
AS new (a, b)
ON DUPLICATE KEY UPDATE val = b;

-- 第二次执行会触发ON DUPLICATE KEY
INSERT INTO t3
SET id = 1, val = 200
AS new (a, b)
ON DUPLICATE KEY UPDATE val = b;

SELECT * FROM t3;
-- 预期结果: id=1, val=200
```

### 2. What did you expect to see? (Required)

mysql:
```
mysql> INSERT INTO t3
-> SET id = 1, val = 100
-> AS new (a, b)
-> ON DUPLICATE KEY UPDATE val = b;
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> -- 第二次执行会触发ON DUPLICATE KEY
mysql> INSERT INTO t3
-> SET id = 1, val = 200
-> AS new (a, b)
-> ON DUPLICATE KEY UPDATE val = b;
Query OK, 2 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM t3;
+----+------+
| id | val |
+----+------+
| 1 | 200 |
+----+------+
1 row in set (0.00 sec)
```
### 3. What did you see instead (Required)
```
mysql> INSERT INTO t3
-> SET id = 1, val = 100
-> AS new (a, b)
-> ON DUPLICATE KEY UPDATE val = b;
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 3 column 3 near "AS new (a, b)
ON DUPLICATE KEY UPDATE val = b"
mysql>
mysql> -- 第二次执行会触发ON DUPLICATE KEY
mysql> INSERT INTO t3
-> SET id = 1, val = 200
-> AS new (a, b)
-> ON DUPLICATE KEY UPDATE val = b;
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 3 column 3 near "AS new (a, b)
ON DUPLICATE KEY UPDATE val = b"
mysql>
```

### 4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()
-> ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.4.0-this-is-a-placeholder
Edition: Community
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.25.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore
Kernel Type: Classic |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@VM-214-205-tencentos tidb]# git branch
* master

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.