The static query optimizer currently has a side-effect on the evaluation order of the tails in a rule. That side-effect is used to decide the order in which we output the joins in a group. That decision is made at the time the pipeline bops are produced for those joins.
This ticket is to modify the integration of the static optimizer to have a side-effect on the ordering of the children in the AST join group nodes.
The advantage of this approach is two fold:
First, we have better information in the AST as it directly reflects the evaluation order. This makes it easier to analyze the query in terms of the optimized AST as there is less uncertainty concerning how the AST was translated into a physical query plan.
Second, this will allow us to write accurate and correct code to decide the join variables for a sub-group or sub-select. This second issue is becoming a blocker for progress on query optimization. For example, I can not get the join variable analysis right for sub-select even on simple queries because the sub-select is also a required join and it is being (incorrectly) included in the set of "required joins" that determine the incoming bound variables for the sub-select itself.
This ticket will also help us to prepare for the RTO integration. The RTO will operate by incrementally building up alternative orders of the AST nodes. Each such order expresses a possible join path. Initially those paths will be one step joins. By the time the RTO is done they will include all of the joins and the AST node order will reflect the optimizer evaluation order.