[jira] [Commented] (DRILL-1986) Natural join query returns wrong result
[
https://issues.apache.org/jira/browse/DRILL-1986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14535710#comment-14535710
]
Victoria Markman commented on DRILL-1986:
-
Verified fixed in 1.0.0
#Thu May 07 19:07:43 EDT 2015
git.commit.id.abbrev=79a712a
0: jdbc:drill:schema=dfs> select * from t1 natural join t2;
Error: SYSTEM ERROR: NATURAL JOIN is not supported
See Apache Drill JIRA: DRILL-1986
[Error Id: 3f642783-5ac6-46b2-9497-94fba6ac2a72 on atsqa4-133.qa.lab:31010]
(state=,code=0)
> 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: Sean Hsuan-Yi Chu
> Labels: no_verified_test
>
> 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)
[jira] [Commented] (DRILL-1986) Natural join query returns wrong result
[
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)
