Bug #110339 Incorrect interpretation in order by
Submitted: 10 Mar 2023 15:18 Modified: 13 Mar 2023 13:53
Reporter: moi moi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[10 Mar 2023 15:18] moi moi 
Description:
In the following query, C1 column in order by is not correctly interprated as the C1 column of the subquery result but as the C2 column renamed as C1 in the final result

SELECT 
`Project1`.`C2` AS `C1`,
`Project1`.`C1` AS `Cp`, 
`Project1`.`Date`, 
`Project1`.`Stat`
 FROM (SELECT 
CASE WHEN ('CLO' = `Extent1`.`Stat`) THEN (1)  ELSE (0) END AS `C1`, 
`Extent1`.`Date`, 
`Extent1`.`Stat`, 
1 AS `C2`
 FROM `mytable` AS `Extent1`) AS `Project1`
 ORDER BY 
`Project1`.`C1` ASC, 
`Project1`.`Date` ASC

this query is generated by entity framework which make the creation of a workaround difficult since I can't choose names for computed fields

How to repeat:
create a request with a subrequest, rename a column of the final query with the name of a column of the subquery and add an order by with this column name
[13 Mar 2023 13:53] MySQL Verification Team 
Hi Mr. moi,

Thank you for your bug report.

However, this is not a bug.

Simply, columns from the inner nested queries are not visible to the outer query.

There is a way to rewrite this query significantly, so that you get what you want.

If that is what you are looking for, please read the next comment carefully.
[13 Mar 2023 13:53] MySQL Verification Team 
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.