drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-5263) Prevent left NLJoin with non scalar subqueries
Date Tue, 14 Feb 2017 17:11:41 GMT

    [ https://issues.apache.org/jira/browse/DRILL-5263?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15866147#comment-15866147
] 

ASF GitHub Bot commented on DRILL-5263:
---------------------------------------

Github user zfong commented on a diff in the pull request:

    https://github.com/apache/drill/pull/748#discussion_r101089177
  
    --- 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 --
    
    I see.  So, Calcite is converting a query with a subquery into an equivalent left join
with a scalar subquery.  Do you know if the query returns the correct result in that case
if a nested loop join is used?  If not, then it seems like we should still be returning an
error.
    
    Also, how do you distinguish the case where the query has been converted to this form
by Calcite vs a user explicitly doing a left outer join to a scalar subquery.  It seems like
in this case, if Drill uses a nested loop join, it will also return wrong result.


> 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)

Mime
View raw message