[jira] [Created] (CALCITE-4391) When the join condition order is different, materialized view recognition fails

["xzh_dz (Jira)" <ji...@apache.org>]

xzh_dz created CALCITE-4391:
-------------------------------

             Summary: When the join condition order is different, materialized view recognition fails
                 Key: CALCITE-4391
                 URL: https://issues.apache.org/jira/browse/CALCITE-4391
             Project: Calcite
          Issue Type: Wish
            Reporter: xzh_dz


When the child nodes of join are identical, but the order of join conditions is different, materialized view recognition will fail.
{code:java}
// code placeholder
String query = ""
        + "select a.empid, a.deptno\n"
        + "from emps a left join emps_table b\n"
        + "on a.empid = b.empid and a.deptno = b.deptno";

String mvLogic = ""
        + "select a.deptno, a.empid\n"
        + "from emps a left join emps_table b\n"
        + "on a.deptno = b.deptno and a.empid = b.empid";
Query:
LogicalProject(empid=[$0], deptno=[$1])
  LogicalJoin(condition=[AND(=($0, $5), =($1, $6))], joinType=[left])
    LogicalTableScan(table=[[default, emps]])
    LogicalTableScan(table=[[default, emps_table]])

MV:
LogicalProject(deptno=[$1], empid=[$0])
  LogicalJoin(condition=[AND(=($1, $6), =($0, $5))], joinType=[left])
    LogicalTableScan(table=[[default, emps]])
    LogicalTableScan(table=[[default, emps_table]]){code}
 

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)