same table alias is allowed in MySQL when tables come from different database

[longfeiss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create database test;
create database other_db;
use test
create table a(id int, name varchar(255));
create table other_db.a(id int, name varchar(255));
select * from a as foo, other_db.a as foo;

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

no error

3. What did you see instead (Required)

ERROR 1066 (42000): Not unique table/alias: 'foo'

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: None
Edition: Community
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

• b76d467 (HEAD -> master, origin/master, origin/HEAD) planner/core: fix bug that injected proj get wrong index. (planner/core: fix bug that injected proj get wrong index. #25336)

[yudongusa]

seems a similar issue: #24663

[winoros]

@oops-lgtm Hi, may i ask that where do you meet this problem?

[winoros]

mysql> select * from a as foo, other_db.a as foo;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `foo`
Org_table:  `a`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM

Field   2:  `name`
Catalog:    `def`
Database:   `test`
Table:      `foo`
Org_table:  `a`
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     1020
Max_length: 0
Decimals:   0
Flags:

Field   3:  `id`
Catalog:    `def`
Database:   `other_db`
Table:      `foo`
Org_table:  `a`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM

Field   4:  `name`
Catalog:    `def`
Database:   `other_db`
Table:      `foo`
Org_table:  `a`
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     1020
Max_length: 0
Decimals:   0
Flags:


0 rows in set (0.00 sec)

When you create the table alias, MySQL holds the database name unchanged. It's against other databases like PostgreSQL, Oracle, and SQL Server. (They all throw out errors).
Maybe we don't need to be compatible with it.

[winoros]

For me, clearing the database name when you set an alias is a more comfortable behavior. And this can make our codes cleaner than be compatible with the MySQL.