UnionAll should be able to reserve the order property comming from its children

[winoros]

Enhancement

suppose that we have table

create table t(a int, index idx(a));

and a SQL select * from (select a from t union all select a from t) t order by a limit 1.

The best plan is that we keep the order property by reading index idx of table t and the union can also use the order property to perform a merge sort to output the data. In this way, we don't need any extra sort to sort the data can directly perform limit upon the union all.

current TiDB lacks the functionality and will do a sort after the union all. no rewrite can achieve the best possible plan until the union all can do the merge sort.

[winoros]

the problem will become clearer if we attach a lot of children to the union all.

The SQL just wants 10k rows, but we read 1 million rows in total. the read amplification is 10x(the number of its children)