[
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: (was: 1.2.0)
1.4.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: 1.4.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)
