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