select a from t1 union all select a from t2 order by 2;

>> Server execution not reaching find_order_in_list() in sql/sql_select.cc is the problem/bug here
Such a union is processed in MariaDB in the following way (when there is no pushdown).
First results from parts of the union are stored into a temporary table (select a from t1 union select a from t2). Then a special structure fake_select_lex is created which is responsible for retrieving data from that temporary table applying ORDER BY. So the error about wrong ORDER BY appears only at the stage of fake_select_lex processing.
In the case of pushdown fake_select_lex is not involved in processing that's why there is no error on MariaDB side either.

find_order_in_list() is called for a JOIN corresponding to the execution of fake_select_lex mentioned above. When fake_select_lex isn't employed there's no call to find_order_in_list().
So it's more an architectural drawback than a bug. We'll think whether this can be solved without significant rework of unions processing.

tntnatbry, I've pushed 10.6-mdev-30828 to the ES which addresses the first issue: pushing down SQL with incorrect ORDER BY. Now such statements will error out on the MariaDB side. There is another issue with FederatedX producing unordered results which I'm planning to fix next.

Regarding the bad side effect: statements

select a from t1 order by a;

and

select a from t1 union all select a from t2 order by a;

are different in terms of column "a" meaning. The "order by a" clause in the case of union relates to the result of the union and not to the particular table column. So the statement can be represented as

(select a from t1 union all select a from t2) order by a;

There can even be a statement like this:

select b as a from t1 union select c as a from t2 order by a;

It's not possible to have an Item* referring to "t1.a" since there's no such column in t1. "a" in the ORDER BY must probably refer to a temporary table containing results of the UNION.

It turned out to be not so easy to implement global ORDER BY (i.e. related to the whole UNION/EXCEPT/INTERSECT) validation before the pushdown. The problem is we select and initialize the pushdown handler in st_select_unit::prepare(). The structure responsible for global ORDER BY is fake_select_lex. But fake_select_lex is not validated completely at prepare(), some validation steps are performed later, during the execution phase at st_select_unit::exec_inner(), when calling mysql_select(). I haven't succeded with trying to add the complete fake_select_lex validation to st_select_unit::prepare(). A lot of test cases start failing in different places of code since it's an architectural change.

My suggestion is to restrict pushing down units having global ORDER BY and/or GROUP BY.

The fix is pushed to ES 10.6-mdev-30828.

OK to push