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

Description

drop table if exists t1;
CREATE TABLE t1 (x int);
INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));

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

mysql> INSERT INTO t1 (x) VALUES ((SELECT x FROM t1));
Query OK, 1 row affected (0.00 sec)

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

Contributor guide

Tech stack

gosql

Domain

database

Issue type

bug

DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.

3

Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.

1-3 hours

Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.

active

ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.

clear

Prerequisites

Understanding of SQL subquery semanticsBasic knowledge of TiDB architecture

Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.

30

Research direction

Investigate the INSERT statement execution path in TiDB (likely in the planner or executor code under the 'planner' or 'executor' directories). Look for how subqueries in the VALUES clause are handled and whether there is a check to prevent referencing the target table in a subquery. The expected error is ERROR 1093 (HY000) from MySQL. Check if this validation is missing or incorrectly implemented. Review comments from the assignee for any progress.