pingcap / pingcap/tidb

update multiple-table use ORDER BY and LIMIT should report error

Open
#37,604 0 comments 0 reactions 0 assignees View on GitHub
severity/moderate sig/transaction 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)
```sql
create table t1(a int,b char, index(a));
create table t2(a int primary key, b int, c int);
update t1 inner join t2 set t1.a = t2.a order by t2.a desc;
update t1 inner join t2 set t1.a = t2.a limit 1;
```

### 2. What did you expect to see? (Required)
```sql
MySQL > update t1 inner join t2 set t1.a = t2.a order by t2.a desc;
(1221, 'Incorrect usage of UPDATE and ORDER BY')
MySQL > update t1 inner join t2 set t1.a = t2.a limit 1;
(1221, 'Incorrect usage of UPDATE and LIMIT')

```
### 3. What did you see instead (Required)
```sql
MySQL root@127.0.0.1:test> update t1 inner join t2 set t1.a = t2.a order by t2.a desc;
->
Query OK, 0 rows affected
Time: 0.002s
MySQL root@127.0.0.1:test> show warnings;
+-------+------+---------+
| Level | Code | Message |
+-------+------+---------+

0 rows in set
Time: 0.025s
MySQL root@127.0.0.1:test> update t1 inner join t2 set t1.a = t2.a limit 1;
Query OK, 0 rows affected
Time: 0.002s
MySQL root@127.0.0.1:test> show errors;
+-------+------+---------+
| Level | Code | Message |
+-------+------+---------+
```
### 4. What is your TiDB version? (Required)
```sql
tidb_version() | Release Version: v6.3.0-alpha-132-g57c493821
Edition: Community
Git Commit Hash: 57c493821f7101b4714cbdff1415cbbd09c361be
Git Branch: master
UTC Build Time: 2022-09-02 08:10:33
GoVersion: go1.19
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore
```

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.