MySQL Bugs: #108788: The temporary alias is incorrect when the SQL statement is executed.

Bug #108788	The temporary alias is incorrect when the SQL statement is executed.
Submitted:	15 Oct 2022 7:34	Modified:	17 Oct 2022 6:59
Reporter:	Chi Zhang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.40	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
The subquery column is a 0-element function and is used as the main query column. The main query column contains multiple queries of this column with different aliases. After the Group By operation is performed, the column aliases in the query result are incorrect.

How to repeat:
Here is the content of a sql script you just need to load :
-------------------------------TEST.sql---------------------------------
drop table if exists t1;
create table t1(id int);
insert into t1 values(1);
-------------------------------TEST.sql---------------------------------
Execute query now:
mysql>select  subq_0.c0 as c0, subq_0.c0 as c1 from (select  database() as c0 from t1) as subq_0 group by 1,2;
+------+------+
| c1   | c1   |
+------+------+
| test | test |
+------+------+
But my expected result is:
+------+------+
| c0   | c1   |
+------+------+
| test | test |
+------+------+

Suggested fix:
After I made some modifications (based on commit c4f63caa8d9f30b2850672291e0ad0928dd89d0e), the problem seems to be fixed. For details about the modification, see the attachment.
+------+------+
| c0   | c1   |
+------+------+
| test | test |
+------+------+

The file may be used to fix the problem.

Attachment: 0001-Fixed-the-issue-where-the-temporary-alias-of-SQL-que.patch (application/octet-stream, text), 802 bytes.

This bug is related to an ancient problem fix:https://bugs.mysql.com/bug.php?id=20466

Hello Chi Zhang,

Thank you for the report and test case.
Verified as described.

regards,
Umesh