Victoria Markman created DRILL-2627:
---------------------------------------
Summary: 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: Jinfeng Ni
{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)
