[jira] [Commented] (CALCITE-3011) Support outer joins with AggregateJoinTransposeRule

["Vineet Garg (JIRA)" <ji...@apache.org>]

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

Vineet Garg commented on CALCITE-3011:
--------------------------------------

Found a bug with full outer join. 
{code:sql}
select e.mgr, d.mgr
from sales.emp as e
full outer join sales.emp as d on e.mgr = d.mgr
group by d.mgr, e.mgr
{code}

{noformat}
LogicalProject(MGR=[$1], MGR0=[$0])
  LogicalProject(MGR0=[$1], MGR=[$0])
    LogicalJoin(condition=[=($0, $1)], joinType=[full])
      LogicalAggregate(group=[{3}])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{3}])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}

This is missing {{LogicalAggregate}} on top. This can produce wrong results if both join keys have at least one NULL.

> Support outer joins with AggregateJoinTransposeRule
> ---------------------------------------------------
>
>                 Key: CALCITE-3011
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3011
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>            Reporter: Vineet Garg
>            Assignee: Vineet Garg
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 40m
>  Remaining Estimate: 0h
>
> Currently {{AggregateJoinTransposeRule}} only support INNER join. Aggregates (at least the ones without aggregate functions) could be pushed through OUTER joins with current logic.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)