Bug #100150 Order by in derived table in prepared statement resolved to wrong field

• Submitted: 8 Jul 2020 6:17
• Modified: 9 Aug 2020 7:44
• Reporter: SUMMER SHENG
• Status: Closed
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• Version: 8.0.20, 5.7.30, 5.7.11, 8.0.11
• OS: Any
• CPU Architecture: Any
• Tags: regression

[8 Jul 2020 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 2020 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 2020 7:44] Jon Stephens

Fixed in MySQL 8.0.22 by WL#9384; see same for changelog entry/docs info.

Closed.