[jira] [Commented] (CALCITE-5195) ArrayIndexOutOfBoundsException when inferring more equal conditions from join condition for semi join

[Chunwei Lei (Jira)]

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

Chunwei Lei commented on CALCITE-5195:
--------------------------------------

Thanks for your reply,[~libenchao]. I tried the following sql:

{code:sql}
select * 
from emp
where exists(select * from dept where emp.deptno = dept.deptno and emp.empno = 
dept.deptno)
{code}
. But the produced plan isn't what I want:

{code:java}
LogicalProject(DEPTNO=[$7])
  LogicalFilter(condition=[IS NOT NULL($11)])
    LogicalJoin(condition=[AND(=($0, $9), =($7, $10))], joinType=[left])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
        LogicalProject(DEPTNO=[$0], DEPTNO2=[$0], $f0=[true])
          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}

It seems difficult to use a sql to produce a semi join with join conditions 
like {{A.x = B.y and A.x = B.z}.



> ArrayIndexOutOfBoundsException when inferring more equal conditions from join 
> condition for semi join
> -----------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-5195
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5195
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Chunwei Lei
>            Assignee: Chunwei Lei
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.31.0
>
>          Time Spent: 1h 10m
>  Remaining Estimate: 0h
>
> The following test can reproduce the exception.
> {code:java}
>   @Test void testJoinConditionPushdown4() {
>     final Function<RelBuilder, RelNode> relFn = b -> {
>       RelNode left = b.scan("EMP")
>           .project(
>               b.field("DEPTNO"),
>               b.field("ENAME"))
>           .build();
>       RelNode right = b.scan("DEPT")
>           .project(
>               b.field("DEPTNO"),
>               b.field("DNAME"))
>           .build();
>       b.push(left).push(right);
>       RexInputRef ref1 = b.field(2, 0, "DEPTNO");
>       RexInputRef ref2 = b.field(2, 1, "DEPTNO");
>       RexInputRef ref3 = b.field(2, 1, "DNAME");
>       RexCall cond1 = (RexCall) b.equals(ref1, ref2);
>       RexCall cond2 = (RexCall) b.equals(ref1, ref3);
>       RexNode cond = b.and(cond1, cond2);
>       return b.semiJoin(cond)
>           .project(b.field(0))
>           .build();
>     };
>     relFn(relFn)
>         .withRule(
>             CoreRules.JOIN_PUSH_EXPRESSIONS,
>             CoreRules.JOIN_CONDITION_PUSH,
>             CoreRules.SEMI_JOIN_PROJECT_TRANSPOSE,
>             CoreRules.JOIN_REDUCE_EXPRESSIONS,
>             CoreRules.FILTER_REDUCE_EXPRESSIONS)
>         .check();
>   }
> {code}



--
This message was sent by Atlassian Jira
(v8.20.7#820007)