Bug #100150 Order by in derived table in prepared statement resolved to wrong field
Submitted: 8 Jul 6:17 Modified: 9 Aug 7:44
Reporter: SUMMER SHENG Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:8.0.20, 5.7.30, 5.7.11, 8.0.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[8 Jul 6:17] SUMMER SHENG
Description:
order by in derived table in prepared statement lead to different result in prepared statement and command line query.

How to repeat:
1. create tables and insert data
create schema test1;
use test1;
create table t1 (a int,b int,c int);
insert into t1 values(1,2,3),(2,3,1),(3,1,2);

2. perform the query
select a from (select a, b as c from t1 order by c)dt;
select a from (select a, b as d from t1 order by d)dt;
the result is
a
3
1
2
and
a
3
1
2

3. prepare & execute the query
prepare stmt1 from 'select a from (select a, b as c from t1 order by c)dt';
execute stmt1;
prepare stmt2 from 'select a from (select a, b as d from t1 order by d)dt';
execute stmt2;
the result is 
a
2
3
1
and
error: Unknown column 'd' in 'order clause'
[8 Jul 7:31] MySQL Verification Team
Hello SUMMER SHENG,

Thank you for the report and test case.
Observed this with 5.7.30(lowest checked 5.7.11) and 8.0.20(lowest checked 8.0.11).

regards,
Umesh
[9 Aug 7:44] Jon Stephens
Fixed in MySQL 8.0.22 by WL#9384; see same for changelog entry/docs info.

Closed.