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, 傅建庆(天池) <jianqing.f...@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
>
>
