Bug #90300 "unknown field in window order by " error even when field is found in table
Submitted: 4 Apr 2018 9:46 Modified: 20 Nov 2018 2:59
Reporter: Chaithra Gopala Reddy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[4 Apr 2018 9:46] Chaithra Gopala Reddy
Description:
A window function having order by "field" throws "Unknown field in window order by" even when field is found in the table

How to repeat:
create table t1 (i integer);
with der as (select case when i in ('0') then i end as a from t1), der1 as (select row_number() over (order by a),a  from der) select * from der1;

Suggested fix:
Do no use select list to resolve column "a" in window funciton's order by
[20 Nov 2018 2:59] Jon Stephens
Documented fix as follows in the MySQL 8.0.14 changelog:

    A windowing function that included an ORDER BY column clause
    failed with -Unknown field in window order by- even when the
    column was found in the table being queried.

Closed.
[28 Nov 2018 8:31] Erlend Dahl
Bug#93155 Unknown column 'id2' in window partition by

was marked as a duplicate.