[ https://issues.apache.org/jira/browse/CALCITE-2798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17190500#comment-17190500 ]
Jiatao Tao commented on CALCITE-2798: ------------------------------------- Hi [~julianhyde] What I opened seems an improvement, not a bug. > Optimizer should remove ORDER BY in sub-query, provided it has no LIMIT or > OFFSET > --------------------------------------------------------------------------------- > > 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 > Fix For: 1.19.0 > > > 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 (v8.3.4#803005)