[
https://issues.apache.org/jira/browse/CALCITE-2798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16750441#comment-16750441
]
Vladimir Sitnikov commented on CALCITE-2798:
--------------------------------------------
{quote}Pragmatically, I don't want to (logical) operators to commit to
preserving sort order, because it ties the optimizer's hands.{quote}
I think I understand what you mean, however my understanding is we don't really
want to try all the different permutations of OVER expressions at Logical level.
In other words, if SQL contains 10 OVER expressions side by site, then we don't
want to throw 3628800 various permutations of
LogicalWindow(LogicalWindow(...)). At Logical level those relations would have
exactly the same cost and traits, so it would just multiply the search space
with virtually zero outcome.
For instance, CALCITE-2223 shows that a single runaway rule is enough to defeat
Volcano.
Current Volcano planner isn't good enough to automatically find the best order
or LogicalWindow computation.
> 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)
