[jira] [Comment Edited] (CALCITE-4074) Wrong plan for IS NULL in join query

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

    [ https://issues.apache.org/jira/browse/CALCITE-4074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17140952#comment-17140952 ] 

Zhixiong Chen edited comment on CALCITE-4074 at 6/20/20, 3:48 AM:
------------------------------------------------------------------

Hi [~danny0405]

The join condition I want is  
{code:java}
 T6.NUM = T5.NUM
 OR ((T6.NUM IS NULL) AND (T5.NUM IS NULL)){code}
But  in logical plan the  join condition only has  " T6.NUM = T5.NUM "


was (Author: zhixiong chen):
Hi [~danny0405]

The join condition I want is  
T6.NUM = T5.NUM
 OR ((T6.NUM IS NULL) AND (T5.NUM IS NULL))
But  in logical plan the  join condition only has  " T6.NUM = T5.NUM "

> Wrong plan for IS NULL in join query
> ------------------------------------
>
>                 Key: CALCITE-4074
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4074
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Zhixiong Chen
>            Priority: Major
>
> sql: 
> {code:java}
>  SELECT count(*) FROM t6
>  INNER JOIN t5 
>  ON T6.NUM = T5.NUM
>  OR ((T6.NUM IS NULL) AND (T5.NUM IS NULL));{code}
> However the plan is 
> {code:java}
> AggregateRel(group=[{0}], ctx=[])
>   ProjectRel(NUM1=[$2], ctx=[])
>     JoinRel(condition=[=($1, $5)], joinType=[inner], ctx=[])
>       TableScan(table=[[SAMPLE, T6]], ctx=[], fields=[[0, 1, 2, 3]])
>       TableScan(table=[[SAMPLE, T5]], ctx=[], fields=[[0, 1, 2, 3]])
> {code}
> It seem the filter IS NULL  is optimized , and the plan is wrong.



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