[CALCITE-4982] Do not push 'cast to not null' through Join in ProjectJoinTransposeRule

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.33.0
• Labels: pull-request-available
• Assignee: Xurenhe
• Reporter: Xurenhe
• Created: 11/Jan/22 04:02
• Updated: 07/Feb/23 22:33
• Resolved: 05/Nov/22 08:50

Description

I meet a bug, when applying rule of 'ProjectJoinTransposeRule'.

Test Case:

@Test void testPushProjectPastLeftJoin2() {
  final String sql = "select coalesce(d.name, b.job, '')\n"
      + "from emp e\n"
      + "left join bonus b on e.ename = b.ename\n"
      + "left join dept d on e.deptno = d.deptno";
  sql(sql).withRule(CoreRules.PROJECT_JOIN_TRANSPOSE).check();
} 

Exception Stack:

Suppressed: java.lang.AssertionError: Cannot add expression of different type to set:
set type is RecordType(INTEGER NOT NULL DEPTNO, BOOLEAN NOT NULL EXPR$0, VARCHAR(10) NOT NULL EXPR$1) NOT NULL
expression type is RecordType(INTEGER NOT NULL DEPTNO, BOOLEAN NOT NULL EXPR$0, VARCHAR(10) EXPR$1) NOT NULL
set is rel#20:LogicalProject.(input=HepRelVertex#14,exprs=[$7, IS NOT NULL($10), CAST($10):VARCHAR(10) NOT NULL])
expression is LogicalProject(DEPTNO=[$1], EXPR$0=[IS NOT NULL($3)], EXPR$1=[$4])
  LogicalJoin(condition=[=($0, $2)], joinType=[left])
    LogicalProject(ENAME=[$1], DEPTNO=[$7])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalProject(ENAME=[$0], JOB=[$1], EXPR$1=[$1])
      LogicalTableScan(table=[[CATALOG, SALES, BONUS]]) 

I found that right with wrap not-null shouldn't be push down in one stage.

 

// before applying rule
LogicalProject(DEPTNO=[$7], EXPR$0=[IS NOT NULL($10)], EXPR$1=[CAST($10):VARCHAR(10) NOT NULL])
  LogicalJoin(condition=[=($1, $9)], joinType=[left])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalTableScan(table=[[CATALOG, SALES, BONUS]])

// after applying rule
LogicalProject(DEPTNO=[$1], EXPR$0=[IS NOT NULL($3)], EXPR$1=[$4])
  LogicalJoin(condition=[=($0, $2)], joinType=[left])
    LogicalProject(ENAME=[$1], DEPTNO=[$7])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalProject(ENAME=[$0], JOB=[$1], EXPR$1=[$1])
      LogicalTableScan(table=[[CATALOG, SALES, BONUS]])
 

`EXPR$1=[CAST($10):VARCHAR(10) NOT NULL]` should be pushed down, because of left-join.

 

 

Issue Links

• is related to: CALCITE-5315 Error when pushing filters with operations that may throw (CAST/DIVISION) below joins
• links to: GitHub Pull Request #2686
• links to: PR