Tidb allow updates to a table when you are also using that same table in an inner select as your update criteria.

[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 (x int);
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));

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

mysql> INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

3. What did you see instead (Required)

mysql> INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
Query OK, 1 row affected (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 |

[morgo]

Verified as described. Note that MySQL can execute this if t1 in the subquery is aliased:

mysql [localhost:5735] {msandbox} (test) > INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
mysql [localhost:5735] {msandbox} (test) > INSERT INTO t1 (x) VALUES ((SELECT x FROM t1 as t));
Query OK, 1 row affected (0.00 sec)

[winoros]

This is just a compatibility problem and is one that would not cause a write error. We downgrade the severity first.

[fanrenhoo]

/assign