[
https://issues.apache.org/jira/browse/CALCITE-6528?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17873621#comment-17873621
]
suibianwanwan edited comment on CALCITE-6528 at 8/14/24 4:50 PM:
-----------------------------------------------------------------
[~julianhyde] I think yes, a valid expression would be that he won't let a null
value be returned as a non-null value.
Thanks for the link, I'd like to try to solve this problem.
was (Author: JIRAUSER301334):
[~julianhyde] I think yes, a valid expression would be that he won't let a null
value be returned as a non-null value.
But it seems a bit difficult to distinguish whether an expression satisfies it
or not. Do you have any suggestions?
> 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
> Priority: Minor
>
> 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)
