[jira] [Commented] (DRILL-1986) Natural join query returns wrong result

["Jinfeng Ni (JIRA)" <ji...@apache.org>]

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

Jinfeng Ni commented on DRILL-1986:
-----------------------------------

Natural join is not supported in Drill currently. 

Further, I feel we might not be able to support natural join in the near future. The challenging part is that optimizer would not know the schema of two sides during the planning phase, while the current execution operator assumes optimizer has already set the join keys.  To support natural join, one new set of JOIN operators have to be introduced.   

For now, We probably should modify the documentation and modify the Drill code to block natural join, until it is supported.  







> Natural join query returns wrong result
> ---------------------------------------
>
>                 Key: DRILL-1986
>                 URL: https://issues.apache.org/jira/browse/DRILL-1986
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 0.8.0
>            Reporter: Victoria Markman
>            Assignee: Jinfeng Ni
>
> Natural join returns wrong result:
> {code}
> 0: jdbc:drill:schema=dfs> select * from `t1.json`;
> +------------+------------+------------+
> |     a1     |     b1     |     c1     |
> +------------+------------+------------+
> | 1          | 1          | 2015-01-01 |
> | 2          | 2          | 2015-01-02 |
> +------------+------------+------------+
> 2 rows selected (0.087 seconds)
> 0: jdbc:drill:schema=dfs> select * from `t2.json`;
> +------------+------------+------------+
> |     a1     |     b1     |     c1     |
> +------------+------------+------------+
> | 1          | 1          | 2015-01-01 |
> +------------+------------+------------+
> 1 row selected (0.112 seconds)
> 0: jdbc:drill:schema=dfs> select * from `t1.json` natural join `t2.json`;
> +------------+------------+------------+------------+------------+------------+
> |     a1     |     b1     |     c1     |    a10     |    b10     |    c10     |
> +------------+------------+------------+------------+------------+------------+
> +------------+------------+------------+------------+------------+------------+
> No rows selected (0.223 seconds)
> {code}
> Equivalent inner join query returns one row:
> {code}
> 0: jdbc:drill:schema=dfs> select * from `t1.json` t1, `t2.json` t2 where t1.a1=t2.a1 and t1.b1=t2.b1 and t1.c1=t2.c1;
> +------------+------------+------------+------------+------------+------------+
> |     a1     |     b1     |     c1     |    a10     |    b10     |    c10     |
> +------------+------------+------------+------------+------------+------------+
> | 1          | 1          | 2015-01-01 | 1          | 1          | 2015-01-01 |
> +------------+------------+------------+------------+------------+------------+
> 1 row selected (0.732 seconds)
> {code}
> Natural join is listed as supported in our documentation. 
> If we decide not to support it, we need to make sure to remove it from docs as well.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)