TiDB resulting ambiguous column error...forcing table name.

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1,t2;
create table t1 (a int unique);
create table t2 (a int, b int);
insert into t1 values (1),(2);
insert into t2 values (1,2);
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;

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

Query 1 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.008, SQL query: 0.008, Reading results: 0
Query 2 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.008, SQL query: 0.008, Reading results: 0
Query 3 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.006, SQL query: 0.006, Reading results: 0
2 Row(s) Inserted
Query 4 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.002, SQL query: 0.002, Reading results: 0
1 Row(s) Inserted
Query 5 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.002, SQL query: 0.002, Reading results: 0
2 Row(s) Inserted
Query 6 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.001, SQL query: 0.001, Reading results: 0

3. What did you see instead (Required)

Query 1 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.397, SQL query: 0.397, Reading results: 0

Query 2 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.072, SQL query: 0.072, Reading results: 0

Query 3 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.077, SQL query: 0.077, Reading results: 0
2 Row(s) Inserted
Query 4 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.032, SQL query: 0.032, Reading results: 0
1 Row(s) Inserted
Query 5 of 6, Rows read: 0, Elapsed time (seconds) - Total: 0.006, SQL query: 0.006, Reading results: 0
Error: Column 'a' in field list is ambiguous
SQLState:  23000
ErrorCode: 1052
Error occurred in:
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10

4. What is your TiDB version? (Required)

+-------------------------+--------------------------------------------------------------------------+
| Variable_name           | Value                                                                    |
+-------------------------+--------------------------------------------------------------------------+
| innodb_version          | 5.6.25                                                                   |
| protocol_version        | 10                                                                       |
| tidb_analyze_version    | 2                                                                        |
| tidb_row_format_version | 2                                                                        |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                                                    |
| version                 | 5.7.25-TiDB-v5.2.1                                                       |
| version_comment         | TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible |
| version_compile_machine | x86_64                                                                   |
| version_compile_os      | osx10.8                                                                  |
+-------------------------+--------------------------------------------------------------------------+

[AilinKid]

once we specified the table name, the error is gone, ambiguity sources from same column name in/out the subquery.

mysql> insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;
ERROR 1052 (23000): Column 'a' in field list is ambiguous
mysql> insert into t1 select t2.a from t2 group by t2.a on duplicate key update t1.a= t1.a + 10;
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

posgresql

postgres=# insert into t1 select t2.a from t2 group by t2.a on conflict (a) do update set a= a + 10;
ERROR:  column reference "a" is ambiguous
LINE 1: ...om t2 group by t2.a on conflict (a) do update set a= a + 10;

[winoros]

This is also one issue about the MySQL compatibility problem. Other databases would fail in the same situation.

[winoros]

Since it's not a common case. We move this to a compatibility issue.

[winoros]

BTW, may i ask that where do you meet this problem @ramanich1 ?