ERROR 1054 (42S22): Unknown column 't2.a' in 'field list' (insert_update.test)

[Alkaagr81]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE if exists t0, t1,t2;
CREATE TABLE t0 (k INTEGER PRIMARY KEY);
CREATE TABLE t1(a INTEGER);
CREATE TABLE t2(a INTEGER);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (3);
INSERT INTO t0 SELECT a FROM t1 ON DUPLICATE KEY UPDATE k= a + t1.a + 10;
INSERT INTO t0 SELECT a FROM t1 JOIN t2 USING (a) ON DUPLICATE KEY UPDATE k= t1.a + t2.a + 10; 

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

mysql> CREATE TABLE t0 (k INTEGER PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1(a INTEGER);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t2(a INTEGER);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1), (3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t0 SELECT a FROM t1 ON DUPLICATE KEY UPDATE k= a + t1.a + 10;
Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> INSERT INTO t0  SELECT a FROM t1 JOIN t2 USING (a) ON DUPLICATE KEY UPDATE k= t1.a + t2.a + 10;
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

3. What did you see instead (Required)

mysql> CREATE TABLE t0 (k INTEGER PRIMARY KEY);
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE t1(a INTEGER);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE t2(a INTEGER);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1), (3);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t0 SELECT a FROM t1 ON DUPLICATE KEY UPDATE k= a + t1.a + 10;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t0 SELECT a FROM t1 JOIN t2 USING (a) ON DUPLICATE KEY UPDATE k= t1.a + t2.a + 10; 
ERROR 1054 (42S22): Unknown column 't2.a' in 'field list'

4. What is your TiDB version? (Required)

| Release Version: v5.2.1
Edition: Community
Git Commit Hash: cd8fb24c5f7ebd9d479ed228bb41848bd5e97445
Git Branch: heads/refs/tags/v5.2.1
UTC Build Time: 2021-09-08 02:29:53
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[AilinKid]

even with INSERT INTO t0 SELECT a FROM t1 JOIN t2 USING (a) ON DUPLICATE KEY UPDATE k= t1.a + t2.b + 10; TiDB still gets a error. ERROR 1054 (42S22): Unknown column t2.b in 'field list' while MySQL succeeds.

So the problem must be: the clause behind the ON DUPLICATE KEY UPDATE should see the from scope, which means t1 JOIN t2 here (of course a is not deduplicated). TiDB will extract what it needs from the bottom up.