Hi Aman Thank you very much Jianqing F ----------------------------------------------------------------- 发件人：Aman Sinha <amansi...@apache.org 发送时间：2018年5月27日(星期日) 10:0 收件人：傅建庆(天池) <jianqing.f...@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.f...@alibaba-inc.com wrote > Hi Aman > Thank you for reply，please tell me when you find th > enhancement JIRA
> Jianqing F > ----------------------------------------------------------------- > 发件人：Aman Sinha <amansi...@apache.org > 发送时间：2018年5月25日(星期五) 23:1 > 收件人：dev <dev@drill.apache.org>; 傅建庆(天池) <jianqing.f...@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.f...@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
