Clickhouse: Aliases doesn't work in Array join for Order by

[hashbash]

Aliases for Array Join doesn't work in order by.

Example:

create table default.test
engine MergeTree()
    PARTITION BY toStartOfMonth(dt)
    order by (a, dt)
as
select 'x' a,
       [tuple('q', 'w', today(), now(), toFloat32(0.1234),
              'e', toFloat64(1.24234), toFloat64(2.324234))] b,
       today() dt;

Without alias for Array join

select b.1
    from default.test
        array join b
order by b.1;

Ok.

But, if I assign alias for array join column:

select c.1
    from default.test
        array join b as c
order by c.1;

CH raises this exception:

Code: 47, e.displayText() = DB::Exception: Missing columns: 'c' while processing query: 'c.1', required columns: 'c', source columns: 'b' (version 19.15.3.6 (official build))

Expected behavior - No errors there.

[CurtizJ]

The error is caused by optimization of order by. Before we will fix it, you can temporarly disable the optimization to avoid error by setting optimize_read_in_order=0.

[CurtizJ]

Fixed by code refactoring in #8130.