[jira] [Commented] (CALCITE-1498) Avoid LIMIT with trivial ORDER BY being pushed through JOIN endlessly

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1498?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15687667#comment-15687667 ] 

Julian Hyde commented on CALCITE-1498:
--------------------------------------

In that query, neither side is count-preserving. So I don't think it's valid to push either LIMIT or OFFSET.

In the following query, the {{dept}} side is count-preserving:

{code}
select d.deptno, empno
    from sales.dept d
    right join sales.emp e using (deptno)
    order by d.deptno offset 1
{code}

For a given {{emp}} record, there is no more than one matching {{dept}} row (because {{dept.deptno}} is unique) and now fewer than one row (because {{right join}}).

> Avoid LIMIT with trivial ORDER BY being pushed through JOIN endlessly
> ---------------------------------------------------------------------
>
>                 Key: CALCITE-1498
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1498
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.10.0
>            Reporter: Maryann Xue
>            Assignee: Maryann Xue
>            Priority: Minor
>
> Allow LIMIT with trivial ORDER BY to be pushed through a JOIN that does not affect the number of rows. Currently it cannot, because {{RelMdUtil.checkInputForCollationAndLimit}} does not know that an sort on zero keys is trivially satisfied (without requiring a Sort) by any relational expression:
> {code}
>     // Check if the input is already sorted
>     boolean alreadySorted = false;
>     if (!alreadySorted) {
>       for (RelCollation inputCollation : mq.collations(input)) {
>         if (inputCollation.satisfies(collation)) {
>           alreadySorted = true;
>           break;
>         }
>       }
>     }
> {code}
> if {{mq.collations(input)}} returns an empty array, {{alreadySorted}} will always be false even if the required {{collation}} is an empty collation (which indicates there's no need to sort).



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)