Yes, we can. Currently the rule applies only on left/right outer join, because the cardinality of join will always be greater than or equal with the cardinality of outer relation. For inner join, the join cardinality may be much less, in which case, sorting on the join output might be cheaper. You can change the code to apply to inner join too.
But be aware that this rule is a hack, because it may generate useless plan, when the inner join is implemented as a MergeJoin that breaks the ordering of the input. - Haisheng ------------------------------------------------------------------ 发件人：Scott Reynolds<[email protected]> 日 期：2019年11月26日 07:28:40 收件人：<[email protected]> 主 题：Question about SortJoinTransposeRule and Inner Joins The performance of our queries are dependent on our ability to push the filter and sort into the RPC layer. Today the planner's SortJoinTransposeRule <https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java> pushes the sort through the join for LEFT OUTER and RIGHT OUTER joins. The logic comment states the following // 1) If join is not a left or right outer, we bail out // 2) If sort is not a trivial order-by, and if there is // any sort column that is not part of the input where the // sort is pushed, we bail out // 3) If sort has an offset, and if the non-preserved side // of the join is not count-preserving against the join // condition, we bail out I am wondering if we can actually push the sort down through the INNER JOIN if all the sort conditions are on one side of the join. SELECT b.title, b.published_date, b.sales FROM Book b INNER JOIN Author a ON b.author = a.id ORDER BY b.published_date, b.sales, b.title
