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

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

Zhixiong Chen created CALCITE-4074:
--------------------------------------

             Summary: 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


sql: 

`

SELECT count(*), t1.id , t1.num
FROM
(
SELECT id,num,num1  FROM  t6  GROUP BY  id,num,num1
) AS  t1
INNER JOIN
(
SELECT  id,num,num1 FROM  t5  GROUP BY id,num,num1
) AS t2
ON t1.num = t2.num
OR ((t1.num IS  NULL) AND (t2.num IS  NULL))
GROUP BY  t1.id,t1.num;

`

However the plan is 

`

ProjectRel(EXPR$0=[$2], ID=[$0], NUM=[$1], ctx=[])
 AggregateRel(group-set=[[0, 1]], groups=[null], EXPR$0=[COUNT()], ctx=[])
 ProjectRel(ID=[$0], NUM=[$1], ctx=[])
 JoinRel(condition=[=($1, $4)], joinType=[inner], ctx=[])
 AggregateRel(group-set=[[0, 1, 2]], groups=[null], ctx=[])
 TableScan(table=[[DEFAULT, T6]], ctx=[], fields=[[0, 1, 2]])
 AggregateRel(group-set=[[0, 1, 2]], groups=[null], ctx=[])
 TableScan(table=[[DEFAULT, T5]], ctx=[], fields=[[0, 1, 2]])

`

It seem the filter IS NULL  is optimized 



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