TiDB Update with Natural join on Partitioned table results are not consistent with mysql.

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1;

CREATE TABLE t1(partkey int,nokey int
) PARTITION BY HASH(partkey) PARTITIONS 3;

INSERT INTO t1 VALUES (1, 1) , (10, 10);

SELECT * FROM t1;
UPDATE t1 AS A NATURAL JOIN t1 B SET A.partkey = 2, B.nokey = 3;
SELECT * FROM t1;

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

mysql> SELECT * FROM t1;
+---------+-------+
| partkey | nokey |
+---------+-------+
|       1 |     1 |
|      10 |    10 |
+---------+-------+
2 rows in set (0.00 sec)
mysql> UPDATE t1 AS A NATURAL JOIN t1 B SET A.partkey = 2, B.nokey = 3;
ERROR 1706 (HY000): Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'

3. What did you see instead (Required)

mysql> SELECT * FROM t1;
+---------+-------+
| partkey | nokey |
+---------+-------+
|       1 |     1 |
|      10 |    10 |
+---------+-------+
2 rows in set (0.00 sec)

mysql> UPDATE t1 AS A NATURAL JOIN t1 B SET A.partkey = 2, B.nokey = 3;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM t1;
+---------+-------+
| partkey | nokey |
+---------+-------+
|       2 |     3 |
|       2 |     3 |
|       2 |     1 |
|       2 |    10 |
+---------+-------+
4 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-264-g6efa36df6
Edition: Community
Git Commit Hash: 6efa36df6cff325106f67ecfe3d79816ba37ca6a
Git Branch: master
UTC Build Time: 2021-12-28 02:03:55
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[mjonss]

I don't think it is critical, maybe major, due to non common command (update with natural join on it-self).

[mjonss]

/component tablepartition