You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "傅建庆(天池)" <ji...@alibaba-inc.com> on 2018/05/25 06:00:27 UTC
Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory in Drill
Hi, all!
I am an engineering working in alibaba, could i ask you a question?
One of my sqls runs failed during the phase of getPlan, because it is regarded as containing an inequality join, but in fact it is'nt.
I found that there is a condition "!remaining.isAlwaysTrue()" in line 253 of JoinUtils.java, and there is a line of comment "for practical purposes these cases could be treated as inequality" in line 254 .
If i remove the condition "!remaining.isAlwaysTrue()"，then my sql runs well in drill.
So my question is "Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory?", could you show me a sql?
Thank you！
Jianqing Fu
JoinUtils.getJoinCategory in Drill:
public static JoinCategory getJoinCategory(RelNode left, RelNode right, RexNode condition,
List<Integer> leftKeys, List<Integer> rightKeys, List<Boolean> filterNulls) {
if (condition.isAlwaysTrue()) {
return JoinCategory.CARTESIAN;
}
leftKeys.clear();
rightKeys.clear();
filterNulls.clear();
RexNode remaining = RelOptUtil.splitJoinCondition(left, right, condition, leftKeys, rightKeys, filterNulls);
if (!remaining.isAlwaysTrue() || (leftKeys.size() == 0 || rightKeys.size() == 0) ) {
// for practical purposes these cases could be treated as inequality
return JoinCategory.INEQUALITY;
}
return JoinCategory.EQUALITY;
}
My SQL:
select * from
odps.tbbi.dim_tm_fans_brand_info a
left join (select * from odps.tbcdm.dim_tb_cate where ds='20180514')b
on a.ds = b.ds
and a.brand_id = '20021'
where a.ds='20180514'
limit 1
回复：Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory in Drill
Posted by "傅建庆(天池)" <ji...@alibaba-inc.com>.
Hi Aman
Thank you very much
Jianqing F
-----------------------------------------------------------------
发件人：Aman Sinha <amansinha@apache.org
发送时间：2018年5月27日(星期日) 10:0
收件人：傅建庆(天池) <jianqing.fujq@alibaba-inc.com
抄 送：dev <dev@drill.apache.org
主 题：Re: Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory in Dril
Hi Jianqing
this is the enhancement JIRA
https://issues.apache.org/jira/browse/DRILL-2454
The queries in the JIRA have similar pattern as yours. I will follow u
and see if we can address this gap in the join support in one of Drill'
upcoming releases since none of these queries actually have cartesian o
inequality join
Thanks for your interest and please continue reporting issues like this
Ama
On Sat, May 26, 2018 at 7:04 AM, 傅建庆(天池) <jianqing.fujq@alibaba-inc.com
wrote
> Hi Aman
> Thank you for reply，please tell me when you find th
> enhancement JIRA
> Jianqing F
> -----------------------------------------------------------------
> 发件人：Aman Sinha <amansinha@apache.org
> 发送时间：2018年5月25日(星期五) 23:1
> 收件人：dev <de...@drill.apache.org>; 傅建庆(天池) <jianqing.fujq@alibaba-inc.com
> 主 题：Re: Why remaining.isAlwaysTrue() is necessary i
> JoinUtils.getJoinCategory in Dril
> Hi Jianqing
> This happens because the ON clause of the join has a single colum
> predicate (in addition to the join predicate). Currently, Drill does no
> support that regardless of equality or in-equality
> Here's a simplified query's Calcite logical plan. Note that the loca
> predicate l_suppkey = 5 is on the DrillJoinRel, not pushed down since it i
> on the left side of the LOJ (it would be pushed down if this condition i
> in the WHERE clause instead of ON)
> Currently Drill's join operator only evaluates predicates of type t1.a1
> t2.a2, so when Drill's planner tries to generate the physical plan it fail
> with the error message about cartesian join or 'inquality join'. So, th
> code you sent is needed for now but I believe there's an enhancement JIR
> about supporting such predicates in the join operator (I will need to fin
> it). Separately, the error message should be improved
> explain plan without implementation for select * fro
> cp.`tpch/lineitem.parquet` l left outer join cp.`tpch/orders.parquet` o o
> l.l_orderkey = o.o_orderkey AND l.l_suppkey = 5
> DrillScreenRe
> DrillProjectRel(**=[$0], **0=[$3]
> DrillJoinRel(condition=[AND(=($1, $4), =($2, 5))], joinType=[left]
> DrillScanRel(table=[[cp, tpch/lineitem.parquet]]
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPat
> [path=classpath:/tpch/lineitem.parquet]], selectionRoot=classpath:/tpch/lineitem.parquet
> numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`]]]
> DrillScanRel(table=[[cp, tpch/orders.parquet]]
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPat
> [path=classpath:/tpch/orders.parquet]], selectionRoot=classpath:/tpch/orders.parquet
> numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`]]]
> 0: jdbc:drill:zk=local> explain plan for select * fro
> cp.`tpch/lineitem.parquet` l left outer join cp.`tpch/orders.parquet` o o
> l.l_orderkey = o.o_orderkey AND l.l_suppkey = 5
> *Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibl
> due to either a cartesian join or an inequality join
> On Thu, May 24, 2018 at 11:00 PM, 傅建庆(天池) <jianqing.fujq@alibaba-inc.com
> wrote
> Hi, all
> I am an engineering working in alibaba, could i ask you
> question
> One of my sqls runs failed during the phase of getPlan, becaus
> it is regarded as containing an inequality join, but in fact it is'nt
> I found that there is a condition "!remaining.isAlwaysTrue()" i
> line 253 of JoinUtils.java, and there is a line of comment "for practica
> purposes these cases could be treated as inequality" in line 254
> If i remove the condition "!remaining.isAlwaysTrue()"，then m
> sql runs well in drill
> So my question is "Why remaining.isAlwaysTrue() is necessary i
> JoinUtils.getJoinCategory?", could you show me a sql
> Thank you
> Jianqing F
> JoinUtils.getJoinCategory in Drill
> public static JoinCategory getJoinCategory(RelNode left, RelNode right
> RexNode condition
> List<Integer> leftKeys, List<Integer> rightKeys, List<Boolean
> filterNulls)
> if (condition.isAlwaysTrue())
> return JoinCategory.CARTESIAN
>
> leftKeys.clear()
> rightKeys.clear()
> filterNulls.clear()
> RexNode remaining = RelOptUtil.splitJoinCondition(left, right
> condition, leftKeys, rightKeys, filterNulls)
> if (!remaining.isAlwaysTrue() || (leftKeys.size() == 0 |
> rightKeys.size() == 0) )
> // for practical purposes these cases could be treate
> as inequalit
> return JoinCategory.INEQUALITY
>
> return JoinCategory.EQUALITY
>
> My SQL
> select * fro
> odps.tbbi.dim_tm_fans_brand_info
> left join (select * from odps.tbcdm.dim_tb_cate where ds='20180514')
> on a.ds = b.d
> and a.brand_id = '20021
> where a.ds='20180514
> limit
Re: Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory
in Drill
Posted by Aman Sinha <am...@apache.org>.
Hi Jianqing,
this is the enhancement JIRA:
https://issues.apache.org/jira/browse/DRILL-2454.
The queries in the JIRA have similar pattern as yours. I will follow up
and see if we can address this gap in the join support in one of Drill's
upcoming releases since none of these queries actually have cartesian or
inequality join.
Thanks for your interest and please continue reporting issues like this.
Aman
On Sat, May 26, 2018 at 7:04 AM, 傅建庆(天池) <ji...@alibaba-inc.com>
wrote:
> Hi Aman,
> Thank you for reply，please tell me when you find the
> enhancement JIRA.
>
> Jianqing Fu
>
> ------------------------------------------------------------------
> 发件人：Aman Sinha <am...@apache.org>
> 发送时间：2018年5月25日(星期五) 23:16
> 收件人：dev <de...@drill.apache.org>; 傅建庆(天池) <ji...@alibaba-inc.com>
> 主 题：Re: Why remaining.isAlwaysTrue() is necessary in
> JoinUtils.getJoinCategory in Drill
>
> Hi Jianqing,
> This happens because the ON clause of the join has a single column
> predicate (in addition to the join predicate). Currently, Drill does not
> support that regardless of equality or in-equality.
> Here's a simplified query's Calcite logical plan. Note that the local
> predicate l_suppkey = 5 is on the DrillJoinRel, not pushed down since it is
> on the left side of the LOJ (it would be pushed down if this condition is
> in the WHERE clause instead of ON).
>
> Currently Drill's join operator only evaluates predicates of type t1.a1 =
> t2.a2, so when Drill's planner tries to generate the physical plan it fails
> with the error message about cartesian join or 'inquality join'. So, the
> code you sent is needed for now but I believe there's an enhancement JIRA
> about supporting such predicates in the join operator (I will need to find
> it). Separately, the error message should be improved.
>
>
> explain plan without implementation for select * from
> cp.`tpch/lineitem.parquet` l left outer join cp.`tpch/orders.parquet` o on
> l.l_orderkey = o.o_orderkey AND l.l_suppkey = 5;
>
>
> DrillScreenRel
>
> DrillProjectRel(**=[$0], **0=[$3])
>
> DrillJoinRel(condition=[AND(=($1, $4), =($2, 5))], joinType=[left])
>
> DrillScanRel(table=[[cp, tpch/lineitem.parquet]],
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=classpath:/tpch/lineitem.parquet]], selectionRoot=classpath:/tpch/lineitem.parquet,
> numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`]]])
>
> DrillScanRel(table=[[cp, tpch/orders.parquet]],
> groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
> [path=classpath:/tpch/orders.parquet]], selectionRoot=classpath:/tpch/orders.parquet,
> numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`]]])
>
>
> 0: jdbc:drill:zk=local> explain plan for select * from
> cp.`tpch/lineitem.parquet` l left outer join cp.`tpch/orders.parquet` o on
> l.l_orderkey = o.o_orderkey AND l.l_suppkey = 5;
>
> *Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly
> due to either a cartesian join or an inequality join*
>
>
>
>
> On Thu, May 24, 2018 at 11:00 PM, 傅建庆(天池) <ji...@alibaba-inc.com>
> wrote:
> Hi, all!
> I am an engineering working in alibaba, could i ask you a
> question?
> One of my sqls runs failed during the phase of getPlan, because
> it is regarded as containing an inequality join, but in fact it is'nt.
> I found that there is a condition "!remaining.isAlwaysTrue()" in
> line 253 of JoinUtils.java, and there is a line of comment "for practical
> purposes these cases could be treated as inequality" in line 254 .
> If i remove the condition "!remaining.isAlwaysTrue()"，then my
> sql runs well in drill.
> So my question is "Why remaining.isAlwaysTrue() is necessary in
> JoinUtils.getJoinCategory?", could you show me a sql?
>
> Thank you！
>
> Jianqing Fu
>
>
>
> JoinUtils.getJoinCategory in Drill:
>
> public static JoinCategory getJoinCategory(RelNode left, RelNode right,
> RexNode condition,
> List<Integer> leftKeys, List<Integer> rightKeys, List<Boolean>
> filterNulls) {
> if (condition.isAlwaysTrue()) {
> return JoinCategory.CARTESIAN;
> }
> leftKeys.clear();
> rightKeys.clear();
> filterNulls.clear();
> RexNode remaining = RelOptUtil.splitJoinCondition(left, right,
> condition, leftKeys, rightKeys, filterNulls);
>
> if (!remaining.isAlwaysTrue() || (leftKeys.size() == 0 ||
> rightKeys.size() == 0) ) {
> // for practical purposes these cases could be treated
> as inequality
> return JoinCategory.INEQUALITY;
> }
> return JoinCategory.EQUALITY;
> }
>
>
> My SQL:
>
> select * from
> odps.tbbi.dim_tm_fans_brand_info a
> left join (select * from odps.tbcdm.dim_tb_cate where ds='20180514')b
> on a.ds = b.ds
> and a.brand_id = '20021'
> where a.ds='20180514'
> limit 1
>
>
>
>
回复：Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory in Drill
Posted by "傅建庆(天池)" <ji...@alibaba-inc.com>.
Hi Aman
Thank you for reply，please tell me when you find the enhancement JIRA
Jianqing F
-----------------------------------------------------------------
发件人：Aman Sinha <amansinha@apache.org
发送时间：2018年5月25日(星期五) 23:1
收件人：dev <de...@drill.apache.org>; 傅建庆(天池) <jianqing.fujq@alibaba-inc.com
主 题：Re: Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory in Dril
Hi Jianqing,
This happens because the ON clause of the join has a single column predicate (in addition to the join predicate). Currently, Drill does not support that regardless of equality or in-equality.
Here's a simplified query's Calcite logical plan. Note that the local predicate l_suppkey = 5 is on the DrillJoinRel, not pushed down since it is on the left side of the LOJ (it would be pushed down if this condition is in the WHERE clause instead of ON).
Currently Drill's join operator only evaluates predicates of type t1.a1 = t2.a2, so when Drill's planner tries to generate the physical plan it fails with the error message about cartesian join or 'inquality join'. So, the code you sent is needed for now but I believe there's an enhancement JIRA about supporting such predicates in the join operator (I will need to find it). Separately, the error message should be improved.
explain plan without implementation for select * from cp.`tpch/lineitem.parquet` l left outer join cp.`tpch/orders.parquet` o on l.l_orderkey = o.o_orderkey AND l.l_suppkey = 5
DrillScreenRe
DrillProjectRel(**=[$0], **0=[$3]
DrillJoinRel(condition=[AND(=($1, $4), =($2, 5))], joinType=[left]
DrillScanRel(table=[[cp, tpch/lineitem.parquet]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=classpath:/tpch/lineitem.parquet]], selectionRoot=classpath:/tpch/lineitem.parquet, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`]]]
DrillScanRel(table=[[cp, tpch/orders.parquet]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=classpath:/tpch/orders.parquet]], selectionRoot=classpath:/tpch/orders.parquet, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`]]]
0: jdbc:drill:zk=local> explain plan for select * from cp.`tpch/lineitem.parquet` l left outer join cp.`tpch/orders.parquet` o on l.l_orderkey = o.o_orderkey AND l.l_suppkey = 5
Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality joi
On Thu, May 24, 2018 at 11:00 PM, 傅建庆(天池) <ji...@alibaba-inc.com> wrote
Hi, all
I am an engineering working in alibaba, could i ask you a question
One of my sqls runs failed during the phase of getPlan, because it is regarded as containing an inequality join, but in fact it is'nt
I found that there is a condition "!remaining.isAlwaysTrue()" in line 253 of JoinUtils.java, and there is a line of comment "for practical purposes these cases could be treated as inequality" in line 254
If i remove the condition "!remaining.isAlwaysTrue()"，then my sql runs well in drill
So my question is "Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory?", could you show me a sql
Thank you
Jianqing F
JoinUtils.getJoinCategory in Drill
public static JoinCategory getJoinCategory(RelNode left, RelNode right, RexNode condition
List<Integer> leftKeys, List<Integer> rightKeys, List<Boolean> filterNulls)
if (condition.isAlwaysTrue())
return JoinCategory.CARTESIAN
leftKeys.clear()
rightKeys.clear()
filterNulls.clear()
RexNode remaining = RelOptUtil.splitJoinCondition(left, right, condition, leftKeys, rightKeys, filterNulls)
if (!remaining.isAlwaysTrue() || (leftKeys.size() == 0 || rightKeys.size() == 0) )
// for practical purposes these cases could be treated as inequalit
return JoinCategory.INEQUALITY
return JoinCategory.EQUALITY
}
My SQL
select * fro
odps.tbbi.dim_tm_fans_brand_info
left join (select * from odps.tbcdm.dim_tb_cate where ds='20180514')
on a.ds = b.d
and a.brand_id = '20021
where a.ds='20180514
limit
Re: Why remaining.isAlwaysTrue() is necessary in JoinUtils.getJoinCategory
in Drill
Posted by Aman Sinha <am...@apache.org>.
Hi Jianqing,
This happens because the ON clause of the join has a single column
predicate (in addition to the join predicate). Currently, Drill does not
support that regardless of equality or in-equality.
Here's a simplified query's Calcite logical plan. Note that the local
predicate l_suppkey = 5 is on the DrillJoinRel, not pushed down since it is
on the left side of the LOJ (it would be pushed down if this condition is
in the WHERE clause instead of ON).
Currently Drill's join operator only evaluates predicates of type t1.a1 =
t2.a2, so when Drill's planner tries to generate the physical plan it fails
with the error message about cartesian join or 'inquality join'. So, the
code you sent is needed for now but I believe there's an enhancement JIRA
about supporting such predicates in the join operator (I will need to find
it). Separately, the error message should be improved.
explain plan without implementation for select * from
cp.`tpch/lineitem.parquet` l left outer join cp.`tpch/orders.parquet` o on
l.l_orderkey = o.o_orderkey AND l.l_suppkey = 5;
DrillScreenRel
DrillProjectRel(**=[$0], **0=[$3])
DrillJoinRel(condition=[AND(=($1, $4), =($2, 5))], joinType=[left])
DrillScanRel(table=[[cp, tpch/lineitem.parquet]],
groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=classpath:/tpch/lineitem.parquet]],
selectionRoot=classpath:/tpch/lineitem.parquet, numFiles=1, numRowGroups=1,
usedMetadataFile=false, columns=[`**`]]])
DrillScanRel(table=[[cp, tpch/orders.parquet]],
groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath
[path=classpath:/tpch/orders.parquet]],
selectionRoot=classpath:/tpch/orders.parquet, numFiles=1, numRowGroups=1,
usedMetadataFile=false, columns=[`**`]]])
0: jdbc:drill:zk=local> explain plan for select * from
cp.`tpch/lineitem.parquet` l left outer join cp.`tpch/orders.parquet` o on
l.l_orderkey = o.o_orderkey AND l.l_suppkey = 5;
*Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly
due to either a cartesian join or an inequality join*
On Thu, May 24, 2018 at 11:00 PM, 傅建庆(天池) <ji...@alibaba-inc.com>
wrote:
> Hi, all!
> I am an engineering working in alibaba, could i ask you a
> question?
> One of my sqls runs failed during the phase of getPlan, because
> it is regarded as containing an inequality join, but in fact it is'nt.
> I found that there is a condition "!remaining.isAlwaysTrue()" in
> line 253 of JoinUtils.java, and there is a line of comment "for practical
> purposes these cases could be treated as inequality" in line 254 .
> If i remove the condition "!remaining.isAlwaysTrue()"，then my
> sql runs well in drill.
> So my question is "Why remaining.isAlwaysTrue() is necessary in
> JoinUtils.getJoinCategory?", could you show me a sql?
>
> Thank you！
>
> Jianqing Fu
>
>
>
> JoinUtils.getJoinCategory in Drill:
>
> public static JoinCategory getJoinCategory(RelNode left, RelNode right,
> RexNode condition,
> List<Integer> leftKeys, List<Integer> rightKeys, List<Boolean>
> filterNulls) {
> if (condition.isAlwaysTrue()) {
> return JoinCategory.CARTESIAN;
> }
> leftKeys.clear();
> rightKeys.clear();
> filterNulls.clear();
> RexNode remaining = RelOptUtil.splitJoinCondition(left, right,
> condition, leftKeys, rightKeys, filterNulls);
>
> if (!remaining.isAlwaysTrue() || (leftKeys.size() == 0 ||
> rightKeys.size() == 0) ) {
> // for practical purposes these cases could be treated
> as inequality
> return JoinCategory.INEQUALITY;
> }
> return JoinCategory.EQUALITY;
> }
>
>
> My SQL:
>
> select * from
> odps.tbbi.dim_tm_fans_brand_info a
> left join (select * from odps.tbcdm.dim_tb_cate where ds='20180514')b
> on a.ds = b.ds
> and a.brand_id = '20021'
> where a.ds='20180514'
> limit 1
>
>