The current order of query optimization rules in the logical plan stage does not cover all the corner cases for multistage queries (an example is #14198).

There is a need to define a concrete rule order after considering as many cases as possible, for the query optimizer to optimize most generic queries correctly.