Bug #118729 Select Query is showing wrong column name alias from sub-query

• Submitted: 28 Jul 2025 13:17
• Modified: 29 Jul 2025 6:27
• Reporter: Zafar Malik
• Status: Patch pending
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.4.4 (Any), 8.0.43, 8.4.6, 9.4.0
• OS: Any
• CPU Architecture: Any
• Tags: MySQL, mysql bug

[28 Jul 2025 13:17] Zafar Malik

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)

[29 Jul 2025 6:27] MySQL Verification Team

Hello Zafar Malik,

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

regards,
Umesh

[1 Jul 8:48] MySQL Admin

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