[
https://issues.apache.org/jira/browse/CALCITE-2798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16756846#comment-16756846
]
Julian Hyde commented on CALCITE-2798:
--------------------------------------
I don't think we should equate the algebra (RelNodes) with the SQL. There may
be Sort nodes (including LogicalSort nodes) in the tree for other reasons than
that they were in the SQL. (Including that the nodes were built manually, using
RelBuilder, and not from SQL at all.)
Therefore I do not think it is safe to remove Sort nodes, even LogicalSort
nodes.
Frankly the semantics of our Logical convention are not very well defined. We
can't say the semantics are the same as SQL, and we can't say that sorts are
irrelevant.
I don't have an opinion about CALCITE-2593; it makes my head spin.
> 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
>
> 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)
