[ https://issues.apache.org/jira/browse/CALCITE-5928?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17754214#comment-17754214 ]
Julian Hyde commented on CALCITE-5928: -------------------------------------- Thanks for logging this case. Complex cases may be difficult to solve with transformation rules because the goal (the interesting order required by, say, a join) may be several relational operators above the source of the ordering. But let's see how far we can go with stepwise transformation rules. > Add SortHomogenizeRule for homogenization sort fields for futher optimize > ------------------------------------------------------------------------- > > Key: CALCITE-5928 > URL: https://issues.apache.org/jira/browse/CALCITE-5928 > Project: Calcite > Issue Type: New Feature > Reporter: JingDas > Assignee: JingDas > Priority: Minor > > When an interesting order is pushed down, some columns may have to be > substituted with equivalent columns in the new context. This is referred to > as homogenuzatzon. > For example, consider the following query: > > {code:java} > select * from sales.emp e > left join ( > select * from sales.dept d) d on e.deptno = d.deptno > order by e.sal, d.deptno{code} > The filed `e.sal`, `d.deptno` will not push down through join, because > d.deptno is unavalilable until after the join. > > We can homogenize the sort fields from `e.sal`, `d.deptno` to `e.sal`, > `e.deptno` since > e.deptno = d.deptno. Then sort fields can be pushed down by > `SortJoinTransposeRule` > The theoretical basis comes from paper [Fundamental Techniques for Order > Optimization|https://dl.acm.org/doi/pdf/10.1145/233269.233320] -- This message was sent by Atlassian Jira (v8.20.10#820010)