You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Aman Sinha (JIRA)" <ji...@apache.org> on 2015/03/30 22:51:53 UTC
[jira] [Updated] (DRILL-2627) Full outer join does not work in
views when order by is present
[ https://issues.apache.org/jira/browse/DRILL-2627?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Aman Sinha updated DRILL-2627:
------------------------------
Fix Version/s: 0.9.0
> Full outer join does not work in views when order by is present
> ---------------------------------------------------------------
>
> Key: DRILL-2627
> URL: https://issues.apache.org/jira/browse/DRILL-2627
> Project: Apache Drill
> Issue Type: New Feature
> Components: Query Planning & Optimization
> Affects Versions: 0.8.0
> Reporter: Victoria Markman
> Assignee: Aman Sinha
> Fix For: 0.9.0
>
>
> {code}
> 0: jdbc:drill:schema=dfs> select * from t1;
> +------------+------------+------------+
> | a1 | b1 | c1 |
> +------------+------------+------------+
> | 1 | 2015-03-01 | aaaaa |
> | 2 | 2015-03-02 | bbbbb |
> | null | null | null |
> +------------+------------+------------+
> 3 rows selected (0.074 seconds)
> 0: jdbc:drill:schema=dfs> select * from t2;
> +------------+------------+------------+
> | a2 | b2 | c2 |
> +------------+------------+------------+
> | 5 | 2017-03-01 | a |
> +------------+------------+------------+
> 1 row selected (0.056 seconds)
> 0: jdbc:drill:schema=dfs> select * from t1 full outer join t2 on (t1.a1 = t2.a2);
> +------------+------------+------------+------------+------------+------------+
> | a1 | b1 | c1 | a2 | b2 | c2 |
> +------------+------------+------------+------------+------------+------------+
> | 1 | 2015-03-01 | aaaaa | null | null | null |
> | 2 | 2015-03-02 | bbbbb | null | null | null |
> | null | null | null | null | null | null |
> | null | null | null | 5 | 2017-03-01 | a |
> +------------+------------+------------+------------+------------+------------+
> 4 rows selected (0.277 seconds)
> 0: jdbc:drill:schema=dfs> create or replace view v2 as select cast(a2 as integer) a2, cast(b2 as date) as b2, cast(c2 as varchar(30)) as c2 from t2 order by a2, b2, c2;
> +------------+------------+
> | ok | summary |
> +------------+------------+
> | true | View 'v2' replaced successfully in 'dfs.test' schema |
> +------------+------------+
> 1 row selected (0.1 seconds)
> 0: jdbc:drill:schema=dfs> create or replace view v1 as select cast(a1 as integer) a1, cast(b1 as date) as b1, cast(c1 as varchar(30)) as c1 from t1 order by a1, b1, c1;
> +------------+------------+
> | ok | summary |
> +------------+------------+
> | true | View 'v1' replaced successfully in 'dfs.test' schema |
> +------------+------------+
> 1 row selected (0.104 seconds)
> {code}
> Merge join plan is planned because input is sorted (order by in both views).
> Since full outer join is not supported with merge join, we get an error.
> {code}
> 0: jdbc:drill:schema=dfs> select * from v1 full outer join v2 on (v1.a1 = v2.a2);
> Query failed: IllegalArgumentException: Full outer join not currently supported
> Error: exception while executing query: Failure while executing query. (state=,code=0)
> {code}
> or subqueries
> {code}
> 0: jdbc:drill:schema=dfs> select * from (select a1, b1, c1 from t1 order by a1, b1, c1) as sq1(a1, b1, c1) full outer join (select a2, b2, c2 from t2 order by a2, b2,c2) as sq2(a2,b2,c2) on (sq1.a1 = sq2.a2);
> Query failed: IllegalArgumentException: Full outer join not currently supported
> Error: exception while executing query: Failure while executing query. (state=,code=0)
> 0: jdbc:drill:schema=dfs> select * from (select a1, b1, c1 from t1 order by a1) as sq1(a1, b1, c1) full outer join (select a2, b2, c2 from t2 order by a2) as sq2(a2,b2,c2) on (sq1.a1 = sq2.a2);
> Query failed: IllegalArgumentException: Full outer join not currently supported
> Error: exception while executing query: Failure while executing query. (state=,code=0)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)