[jira] [Created] (CALCITE-6528) JoinUnifyRule may alter semantics in some cases

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

suibianwanwan created CALCITE-6528:
--------------------------------------

             Summary: JoinUnifyRule may alter semantics in some cases
                 Key: CALCITE-6528
                 URL: https://issues.apache.org/jira/browse/CALCITE-6528
             Project: Calcite
          Issue Type: Bug
            Reporter: suibianwanwan


Test:
{code:java}
 @Test void testInCorrectMvRewrite() {
    String mv = "select * from \n"
        + "(select \"name\" from \"emps\") \"t1\"\n"
        + "right join (select \"name\" from \"depts\") \"t2\"\n"
        + "on \"t1\".\"name\" = \"t2\".\"name\"";
    String query = "select * from \n"
        + "(select (CASE WHEN \"name\" IS NULL THEN 1 else NULL END) a2, \"name\" from \"emps\") \"t1\"\n"
        + "right join (select \"name\" from \"depts\") \"t2\"\n"
        + "on \"t1\".\"name\" = \"t2\".\"name\"";
    sql(mv, query).ok();
  } {code}
It will be rewritten:
{code:java}
LogicalCalc(expr#0..1=[{inputs}], expr#2=[IS NULL($t0)], expr#3=[1], expr#4=[null:INTEGER], expr#5=[CASE($t2, $t3, $t4)], A2=[$t5], name=[$t0], name0=[$t1])
  EnumerableTableScan(table=[[hr, MV0]])
{code}
"CASE WHEN name is NULL THEN 1 ELSE 0 END" is pulled up from nullable side on top of the join. This will change the unmatched columns in the right join that are set to null to 1 Produces different results from the original query

 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)