Maryann Xue created CALCITE-1508: ------------------------------------ Summary: SortJoinTransposeRule can remove the top Sort node if it is a trivial ORDER BY and the non-preserved side of the outer join is count-preserving Key: CALCITE-1508 URL: https://issues.apache.org/jira/browse/CALCITE-1508 Project: Calcite Issue Type: Improvement Components: core Affects Versions: 1.10.0 Reporter: Maryann Xue Assignee: Maryann Xue Priority: Minor
If the non-preserved side of the outer join is count-preserving, for each row from the preserved side, there can only be zero or one matches from the non-preserved side, which means the join can produce only one row. So it is safe to push a LIMIT and/or an OFFSET through, and meanwhile remove the original Sort node if it is a trivial ORDER-BY. For example, {code} select d.deptno, empno from sales.dept d right join sales.emp e using (deptno) limit 10 offset 2 {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)