MySQL Bugs: #99278: order by clause don't work with a subquery

Bug #99278	order by clause don't work with a subquery
Submitted:	16 Apr 2020 10:05	Modified:	16 Apr 2020 20:49
Reporter:	Carol WU	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7, 8.0	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Q1:
mysql> select * from t1 order by 1 desc ;
+------+------+
| pk   | c1   |
+------+------+
|    3 |    6 |
|    2 |    4 |
|    1 |    2 |
+------+------+
3 rows in set (0.01 sec)

Q2:
mysql> select * from t1 order by (select 1 from dual) desc ;
+------+------+
| pk   | c1   |
+------+------+
|    1 |    2 |
|    2 |    4 |
|    3 |    6 |
+------+------+
3 rows in set (0.01 sec)

Expectation:   Q2  returns same result set like Q1.   
Actually:  The order of the Q2 result mistake.

How to repeat:
drop table t1;
create table t1 (c1 int , c2 int);
insert into t1 values (1, 2);
insert into t1 values (2, 4);
insert into t1 values (3, 6);
select * from t1 order by 1 desc ;
select * from t1 order by (select 1 from dual) desc ;

Hi Mr. WU,

Thank you for your bug report.

I have tested it on both, latest 5.7 and latest 8.0 and I have got identical results:

+------+------+
| c1   | c2   |
+------+------+
|    3 |    6 |
|    2 |    4 |
|    1 |    2 |
+------+------+
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
|    2 |    4 |
|    3 |    6 |
+------+------+
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
|    2 |    4 |
|    3 |    6 |
+------+------+

I have run three queries, as I have run one without " from dual" , just with " ........ order by (select 1) desc ". They both produced the same, erroneous results.

Verified as reported for both 5.7 and 8.0.

I think this behavior is NOT a bug.

select * from t1 order by (select 1 from dual) desc;

is same as

select * from t1 order by '1' desc;

>     [ORDER BY {col_name | expr | position}

"ORDER BY 1" 's "1" is position but "ORDER BY '1'" 's "1" is literal-expression.

Literal-expression for ORDER BY clause always returns same literal for each row, so we seems they didn't be sorted.

> select * from t1 order by (select 1 from dual) desc;
> 
> is same as
> 
> select * from t1 order by '1' desc;

Oops, it's same as

select * from t1 order by CAST(1 AS UNSIGNED) desc ;

Posted by developer:
 
This is not a bug.

Indeed, specifying a literal integer after ORDER BY means ordering
the result based on the selected expression with ordinal number
corresponding to the value of the integer.
However, any other way of specifying an integer (such as via a subquery)
makes the executor order the result based on this value.
If this value is a constant, then sorting becomes arbitrary.