[CALCITE-2798] Optimizer should remove ORDER BY in sub-query, provided it has no LIMIT or OFFSET

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.18.0
• Fix Version/s: 1.19.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Vladimir Sitnikov
• Created: 23/Jan/19 10:44
• Updated: 16/Nov/21 11:38
• Resolved: 31/Jan/19 21:55

Description

The following SQL performs sort twice, however inner sort can be eliminated

select * from (
  select * from "emps" 
order by "emps"."deptno"
) order by 1 desc

The same goes for (window calculation will sort on its own)

select row_number() over (order by "emps"."deptno")  from (
  select * from "emps" 
order by "emps"."deptno" desc
)

The same goes for SetOp (union, minus):

select * from (
  select * from "emps" 
order by "emps"."deptno"
) union select * from (
  select * from "emps" 
order by "emps"."deptno" desc
)

There might be other cases like that (e.g. Aggregate, Join, Exchange, SortExchange)

Issue Links

• causes: CALCITE-2978 sorting not applied in subqueries
• causes: CALCITE-4160 Add configuration to retain ORDER BY in sub-query
• is related to: CALCITE-4886 When converting SQL to RelNode, SqlOrderBy is missing in sub-query that contains SqlSetOperator
• is related to: HIVE-6348 Order by/Sort by in subquery
• relates to: CALCITE-3664 Sort in subquery is lost when converting SqlNode to Relnode