Simple query failed: When alias used as column name and used in ORDER BY

[anitajjx]

Can be reproduced ?

Yes

Steps:

CREATE TABLE t1 (spID int,userID int,score smallint);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (2,2,2);
INSERT INTO t1 VALUES (2,1,4);
INSERT INTO t1 VALUES (3,3,3);
INSERT INTO t1 VALUES (1,1,5);
INSERT INTO t1 VALUES (4,6,10);
INSERT INTO t1 VALUES (5,11,99);

select userID as user_id, spID sp_id from t1 order by user_id, sp_id;

Expected behavior:

mysql> select userID as user_id, spID sp_id from t1 order by user_id, sp_id;
+---------+-------+
| user_id | sp_id |
+---------+-------+
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 3 |
| 6 | 4 |
| 11 | 5 |
+---------+-------+
7 rows in set (0.00 sec)

Actual behavior:

mysql> select userID as user_id, spID sp_id from t1 order by user_id, sp_id;
ERROR 1105 (HY000): unknown error:[42000]unknown column 'user_id' in expr

Environment:

• Version or commit-id (e.g. v0.1.0 or 8b23a93):commit 758c531
• Hardware parameters:
• OS type:
• Others:

Configuration file:

Additional context:

• Error message from client:
• Server log:
• Other information:

[nnsgmsone]

This writing method sql is not currently supported.

[anitajjx]

Could you please specify what exactly isn't supported? If it relates to the alias used for a column name, why the following SQL can run successfully?
mysql> select distinct sum(spID) as sum from t1 where score>1 group by userID order by sum asc;
+------+
| sum |
+------+
| 2 |
| 3 |
| 4 |
| 5 |
+------+
4 rows in set (0.02 sec)

[nnsgmsone]

Currently, attributes of order by only aliases of aggregated results are supported, aliases of common projections are not supported, and support will be added later。

[m-schen]

this issue is same to #1109