[
https://issues.apache.org/jira/browse/DRILL-5263?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15866129#comment-15866129
]
ASF GitHub Bot commented on DRILL-5263:
---------------------------------------
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/748#discussion_r101086894
--- Diff:
exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/NestedLoopJoinPrule.java
---
@@ -49,7 +49,7 @@ protected boolean checkPreconditions(DrillJoinRel join,
RelNode left, RelNode ri
PlannerSettings settings) {
JoinRelType type = join.getJoinType();
- if (! (type == JoinRelType.INNER || type == JoinRelType.LEFT)) {
+ if (!(type == JoinRelType.INNER || (type == JoinRelType.LEFT &&
JoinUtils.hasScalarSubqueryInput(left, right)))) {
--- End diff --
In the case when table joining with the result of the query with aggregate
function, Drill creates physical plan with left nested loop join.
It is due to this code line:
https://github.com/mapr/incubator-calcite/blob/DrillCalcite1.4.0/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1078
So to allow execution of such queries without changes in calcite - allow
left nested loop join for scalar subqueries.
> Prevent left NLJoin with non scalar subqueries
> ----------------------------------------------
>
> Key: DRILL-5263
> URL: https://issues.apache.org/jira/browse/DRILL-5263
> Project: Apache Drill
> Issue Type: Bug
> Reporter: Serhii Harnyk
> Assignee: Serhii Harnyk
> Attachments: tmp.tar.gz
>
>
> Nested loop join operator in Drill supports only inner join and returns
> incorrect result for queries with left join and non scalar sub-queries. Drill
> should throw error in this case.
> Example:
> {code:sql}
> alter session set planner.enable_nljoin_for_scalar_only=false;
> select t2.dt, t1.fyq, t2.who, t2.event
> from t2
> left join t1 on t2.dt between t1.dts and t1.dte
> order by t2.dt;
> {code}
> Result:
> {noformat}
> +-------------+----------+----------+--------------------+
> | dt | fyq | who | event |
> +-------------+----------+----------+--------------------+
> | 2016-12-26 | 2016-Q2 | aperson | had chrsitmas |
> | 2017-01-06 | 2016-Q3 | aperson | did somthing |
> | 2017-01-12 | 2016-Q3 | aperson | did somthing else |
> +-------------+----------+----------+--------------------+
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
