[
https://issues.apache.org/jira/browse/CALCITE-2798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16750379#comment-16750379
]
Julian Hyde commented on CALCITE-2798:
--------------------------------------
In academic relational algebra, Sort is not a first-class relational operator
unlike, say, Filter. Calcite is not at liberty to just drop a Filter. It is at
liberty to drop a Sort, if is under another relational operator, say a Filter,
Window, Project or Join, because those operators do not commit to preserving
sort order.
I know you have written elsewhere that you think that LogicalWindow should
preserve sort order. So we have philosophical differences on this one.
Pragmatically, I don't want to (logical) operators to commit to preserving sort
order, because it ties the optimizer's hands. Sorts at the top of the query
tree will be preserved. Other sorts (e.g. on inputs to a join) exist because
there is a RelSubset asking for them, and the Join that uses that RelSubset
gets chosen because it is significantly cheaper in the final plan.
> Optimizer should remove unneeded Sort
> -------------------------------------
>
> Key: CALCITE-2798
> URL: https://issues.apache.org/jira/browse/CALCITE-2798
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.18.0
> Reporter: Vladimir Sitnikov
> Assignee: Julian Hyde
> Priority: Major
>
> The following SQL performs sort twice, however inner sort can be eliminated
> {code}select * from (
> select * from "emps"
> order by "emps"."deptno"
> ) order by 1 desc{code}
> The same goes for (window calculation will sort on its own)
> {code}select row_number() over (order by "emps"."deptno") from (
> select * from "emps"
> order by "emps"."deptno" desc
> ){code}
> The same goes for SetOp (union, minus):
> {code}select * from (
> select * from "emps"
> order by "emps"."deptno"
> ) union select * from (
> select * from "emps"
> order by "emps"."deptno" desc
> ){code}
> There might be other cases like that (e.g. Aggregate, Join, Exchange,
> SortExchange)
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
