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)
