[ https://issues.apache.org/jira/browse/CALCITE-2798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17102060#comment-17102060 ]
Stamatis Zampetakis commented on CALCITE-2798: ---------------------------------------------- To be honest, I am not sure. Nevertheless I cannot find a reason of why the standard declares it as a feature if the optimizer is free to kill it at any time. For the record the plan in Postgres for the query given by [~julianhyde] is the following: {noformat} Merge Join (cost=246.68..480.99 rows=14351 width=44) Merge Cond: (dept.deptno = emp.deptno) -> Sort (cost=88.17..91.35 rows=1270 width=36) Sort Key: dept.deptno -> Seq Scan on dept (cost=0.00..22.70 rows=1270 width=36) -> Sort (cost=158.51..164.16 rows=2260 width=8) Sort Key: emp.deptno -> Seq Scan on emp (cost=0.00..32.60 rows=2260 width=8) {noformat} On top of that there are other similar features in the standard, like top-level <order by clause> in views (F852), that confuse me even more. By definition a relation is unordered and a view is a relation so what does it mean to have an order by in there. Anyways if I find a clear answer I will come back again to this. > 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)