[
https://issues.apache.org/jira/browse/CALCITE-1719?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15948527#comment-15948527
]
Julian Hyde commented on CALCITE-1719:
--------------------------------------
I don't agree with your SQL semantics or relational algebra semantics.
In the line
{code} .sortLimit(firstOffset + secondOffset, fetch, first.getChildExps()){code}
the rule is applying the inner sort key, but I think it should apply the outer
sort key. For example, if you have {{ORDER BY x, y}} followed by {{ORDER BY
y}}, the output is sorted on {{y}}.
Similarly, in your SQL example, {code}SELECT * FROM (SELECT DISTINCT dim2 FROM
foo ORDER BY dim2) LIMIT 10{code} the correct semantic is that order is not
specified. Or equivalently, the output is ordered on a sort key of 0 columns.
To achieve the effect I think you want, you have to write {code}SELECT DISTINCT
dim2 FROM foo ORDER BY dim2 LIMIT 10{code}
The rule has the side condition that the outer Sort's key has to be empty. I
think the side condition should be as follows: If the inner or outer Sort has a
limit or offset, the outer Sort's key must be a prefix of the inner Sort's key.
In any case, the resulting Sort has a key equal to the outer Sort's key. (The
inner Sort's key is ignored, except for its effect when applying an inner limit
or offset.)
> SortCollapseRule for collapsing nested sorts
> --------------------------------------------
>
> Key: CALCITE-1719
> URL: https://issues.apache.org/jira/browse/CALCITE-1719
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Gian Merlino
> Assignee: Julian Hyde
>
> Useful for subqueries like {{SELECT * FROM (...) LIMIT X}}, where the inner
> query might have an order by or limit.
> Patch in https://github.com/apache/calcite/pull/410.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
