[
https://issues.apache.org/jira/browse/CALCITE-4074?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhixiong Chen updated CALCITE-4074:
-----------------------------------
Description:
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.
was:
sql:
{code:java}
SELECT count(*), T1.NUM
FROM
(
SELECT NUM FROM t6 GROUP BY NUM
) AS T1
INNER JOIN
(
SELECT NUM FROM t5 GROUP BY NUM
) AS T2
ON T1.NUM = T2.NUM
OR ((T1.NUM IS NULL) AND (T2.NUM IS NULL))
GROUP BY T1.NUM;{code}
However the plan is
{code:java}
ProjectRel(EXPR$0=[$1], NUM=[$0], ctx=[])
AggregateRel(group-set=[[0]], groups=[null], EXPR$0=[COUNT()], ctx=[])
ProjectRel(NUM=[$0], ctx=[])
JoinRel(condition=[=($0, $1)], joinType=[inner], ctx=[])
AggregateRel(group-set=[[0]], groups=[null], ctx=[0@null])
ProjectRel(NUM=[$1], ctx=[0@null])
TableScan(table=[[DEFAULT, T6]], ctx=[0@null], fields=[[0, 1, 2]])
AggregateRel(group-set=[[0]], groups=[null], ctx=[1@null])
ProjectRel(NUM=[$1], ctx=[1@null])
TableScan(table=[[DEFAULT, T5]], ctx=[1@null], fields=[[0, 1, 2]])
{code}
It seem the filter IS NULL is optimized , and the plan is wrong.
> 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)
