Mailing-List: contact issues-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Thu, 16 Feb 2017 22:42:41 +0000 (UTC)
From: "ASF GitHub Bot (JIRA)" <jira@apache.org>
To: issues@drill.apache.org
Message-ID: <JIRA.13042911.1487072401000.104234.1487284961712@Atlassian.JIRA>
In-Reply-To: <JIRA.13042911.1487072401000@Atlassian.JIRA>
References: <JIRA.13042911.1487072401000@Atlassian.JIRA> <JIRA.13042911.1487072401780@jira-lw-us.apache.org>
Subject: [jira] [Commented] (DRILL-5263) Prevent left NLJoin with non scalar
 subqueries
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Thu, 16 Feb 2017 22:42:50 -0000


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

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

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

    https://github.com/apache/drill/pull/748#discussion_r101645954
  
    --- 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 --
    
    If we already have tests that are checking correctness for the left nested outer join where one side is scalar, then we are ok with this change. 


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