Error Bug: Derived Table Must Have Its Own Alias

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: derived table must have its own alias报错bug

| username: db_user

Version: 5.3.1

Reproduction steps:
First SQL: select * from (select 1) group by 1;
Second SQL: select * from (select 1 union all select 2) group by 1;

Expected result:
Both should report ERROR 1248 (42000): Every derived table must have its own alias

Actual result:
The second SQL executes normally

Version 4.0.13
Reproduction steps:
First SQL: select * from (select 1) group by 1;
Second SQL: select * from (select 1 union all select 2) group by 1;

Expected result:
Both should report ERROR 1248 (42000): Every derived table must have its own alias

Actual result:
The second SQL reports ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 49 near “group by 1”

2
| username: Min_Chen | Original post link

This should be an optimization of TiDB, allowing users to continue querying without specifying an alias.

3
| username: db_user | Original post link

Look at the reproduction steps: the first one reports an error, the second one does not, so I think it is not an optimization issue but a bug triggered by the union. If it were an optimization issue, both should report errors.

4
| username: Min_Chen | Original post link

OK, I didn’t see it clearly. Let me take another look.

5
| username: onlyacat | Original post link

I took a quick look, and it seems that during preprocessing, only the case of ast.SelectStmt was considered, without considering the case of ast.SetOprStmt, and union belongs to the latter.

I actually think this could be considered a feature and doesn’t necessarily need to be fixed :joy:. However, if it affects any scenarios, I can submit a PR to fix it.

6
| username: db_user | Original post link

Well, there is no impact at the moment, just mentioning it because I have encountered some issues caused by union before. So I was wondering if there are indeed some problems with the generated syntax tree and physical execution plan in this area.

7
| username: onlyacat | Original post link

Yes, I took a look, and in many scenarios, the situation of from (… union …) was not considered. If there are any other union-related issues, you can post them, and we can fix them together.

8
| username: db_user | Original post link

I forgot where I recorded it before. Let me find it and take a look. Thanks.

9
| username: db_user | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.