[CALCITE-3935] Materialization-Failed, meeting to pull up bottom filter of Left/Right Join

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.28.0
• Labels: pull-request-available
• Assignee: Xurenhe
• Reporter: Xurenhe
• Created: 17/Apr/20 12:00
• Updated: 20/Oct/21 05:24
• Resolved: 20/Sep/21 08:29

Description

Hi all.
`SubstitutionVisitor` support more join_type in some rules of `Join`.
Now `SubstitutionVisitor` only support join type of `INNER`, when pulling up bottom filters of join.

It doesn't work as follows.

@Test public void testJoinOnLeftProjectWithFilterToJoin() {
    String mv = ""
        + "select \"emps\".\"empid\", \"depts\".\"name\", \"emps\".\"salary\"\n" +
        "from \"emps\"\n" +
        "left join \"depts\"\n" +
        "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
        + "where \"emps\".\"empid\" > 10";
    String query = ""
        + "select \"emps\".\"empid\", \"depts\".\"name\", \"emps\".\"salary\"\n" +
        "from \"emps\"\n" +
        "left join \"depts\"\n" +
        "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
        + "where \"emps\".\"empid\" > 40";
    sql(mv, query).withOnlyBySubstitution(true).ok();
  }

Issue Links

• links to: GitHub Pull Request #2013