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)
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)