MySQL Bugs: #118729: Select Query is showing wrong column name alias from sub-query

Bug #118729	Select Query is showing wrong column name alias from sub-query
Submitted:	28 Jul 2025 13:17	Modified:	18 Sep 11:51
Reporter:	Zafar Malik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.4.4 (Any), 8.0.43, 8.4.6, 9.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	MySQL, mysql bug

Description:
Select statement is showing wrong column name alias from sub-query.

How to repeat:
SELECT version();
 
CREATE TABLE test.t(i int);
INSERT test.t VALUES(1);
SELECT t.*,
     a AS b
FROM
(
    SELECT 1 AS a
    FROM test.t
    ORDER BY RAND()
) t
;
 
Test output:
 
> SELECT version()
 
version()       |
----------------+
8.4.4-commercial|
 
1 row(s) fetched.
 
> CREATE TABLE test.t(i int)
 
0 row(s) modified.
 
> INSERT test.t VALUES(1)
 
1 row(s) modified.
 
 
> SELECT t.*,
     a AS b
FROM
(
    SELECT 1 AS a
    FROM test.t
    ORDER BY RAND()
) t
 
 
b|b|
-+-+
1|1|
 
1 row(s) fetched.
 
The name of the first column in the output is incorrect. It should be “a”.

Seems RAND() function in ordering is causing issue as without it, it is showing column name alias fine as per below-

mysql> SELECT t.*, a AS b FROM (SELECT 1 AS a FROM test.t) t;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)

mysql> SELECT t.*, a AS b FROM (SELECT 1 AS a FROM test.t order by i) t;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)

but as we use order by RAND(), it is showing wrongly.
mysql> SELECT t.*, a AS b FROM (SELECT 1 AS a FROM test.t order by rand()) t;
+---+---+
| b | b |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)

Hello Zafar Malik,

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

regards,
Umesh

Posted by developer: Bug status updated to 'Patch pending'

Posted by developer: Bug status updated to 'Documenting'

Posted by developer:
 
Added the following note to the MySQL Server 26.10.0 release notes:

Queries exposing the same derived-table expression under different output
aliases could report both result columns with the later alias even though
their values were correct. This affected populated results ordered by
RAND() and empty results using the optimizer. The hypergraph optimizer was
not affected.