You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Zhixiong Chen (Jira)" <ji...@apache.org> on 2020/06/19 05:43:00 UTC
[jira] [Updated] (CALCITE-4074) Wrong plan for IS NULL in join
query
[ 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(*), 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;{code}
However the plan is
{code:java}
KapProjectRel(EXPR$0=[$2], ID=[$0], NUM=[$1], ctx=[])
KapAggregateRel(group-set=[[0, 1]], groups=[null], EXPR$0=[COUNT()], ctx=[])
KapProjectRel(ID=[$0], NUM=[$1], ctx=[])
KapJoinRel(condition=[=($1, $4)], joinType=[inner], ctx=[])
KapAggregateRel(group-set=[[0, 1, 2]], groups=[null], ctx=[])
KapTableScan(table=[[DEFAULT, T6]], ctx=[], fields=[[0, 1, 2]])
KapAggregateRel(group-set=[[0, 1, 2]], groups=[null], ctx=[])
KapTableScan(table=[[DEFAULT, T5]], ctx=[], fields=[[0, 1, 2]])
{code}
It seem the filter IS NULL is optimized
was:
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
> 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(*), 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;{code}
> However the plan is
> {code:java}
> KapProjectRel(EXPR$0=[$2], ID=[$0], NUM=[$1], ctx=[])
> KapAggregateRel(group-set=[[0, 1]], groups=[null], EXPR$0=[COUNT()], ctx=[])
> KapProjectRel(ID=[$0], NUM=[$1], ctx=[])
> KapJoinRel(condition=[=($1, $4)], joinType=[inner], ctx=[])
> KapAggregateRel(group-set=[[0, 1, 2]], groups=[null], ctx=[])
> KapTableScan(table=[[DEFAULT, T6]], ctx=[], fields=[[0, 1, 2]])
> KapAggregateRel(group-set=[[0, 1, 2]], groups=[null], ctx=[])
> KapTableScan(table=[[DEFAULT, T5]], ctx=[], fields=[[0, 1, 2]])
> {code}
> It seem the filter IS NULL is optimized
--
This message was sent by Atlassian Jira
(v8.3.4#803005)