drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Victoria Markman (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-2627) Full outer join does not work in views when order by is present
Date Mon, 30 Mar 2015 20:28:52 GMT
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)

Mime
View raw message