Hi Aman
Thank you for reply，please tell me when you find the enhancement JIRA
Jianqing F
-----------------------------------------------------------------
发件人：Aman Sinha <[email protected]
发送时间：2018年5月25日(星期五) 23:1
收件人：dev <[email protected]>; 傅建庆(天池) <[email protected]
主 题：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, 傅建庆(天池) <[email protected]> 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
