second88 created DRILL-5866:
-------------------------------
Summary: Methods supportsFullOuterJoins and
supportsLimitedOuterJoins of DatabaseMetaData return incorrect values
Key: DRILL-5866
URL: https://issues.apache.org/jira/browse/DRILL-5866
Project: Apache Drill
Issue Type: Bug
Components: Client - JDBC, Metadata, Storage - JDBC
Affects Versions: 1.10.0
Reporter: second88
Priority: Minor
Currently, supportsFullOuterJoins() returns true and supportsLimitedOuterJoins
returns false.
The expected result should be supportsFullOuterJoins() returns false and
supportsLimitedOuterJoins returns true.
My interpretations to the descriptions of the methods in [JDBC
API|https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html]:
*supportsFullOuterJoins*
If "LEFT OUTER JOIN", "RIGHT OUTER JOIN" and "FULL OUTER JOIN" clauses and
nested joins are supported, returns true, otherwise, returns false.
*supportsLimitedOuterJoins*
If "LEFT OUTER JOIN", "RIGHT OUTER JOIN" and "FULL OUTER JOIN" clauses, returns
true, otherwise, returns false.
The [current
implementation|https://github.com/apache/drill/blob/master/exec/jdbc/src/main/java/org/apache/drill/jdbc/impl/DrillDatabaseMetaDataImpl.java]
seems to be wrong.
supportsFullOuterJoins() does not check if nested joins are supported.
supportsLimitedOuterJoins() contradicts the description of the method that it
returns false while supportsFullOuterJoins() returns true.
>From
>[MySQL|https://dev.mysql.com/doc/refman/5.7/en/nested-join-optimization.html]
>and [join_clause of
>Oracle|http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2080416],
> I think nested join in general means using parentheses to override default
>precedence.
An example of nested join
{code:sql}
--Given that the involved schema / default workspace, tables and columns are
available, this select statment is tested valid in Oracle but not in Drill
(PARSE ERROR: Non-query expression encountered in illegal context)
select a.id
from dfs.a a
left outer join (
dfs.b b
full outer join dfs.c c
on b.id = c.id
)
on a.id = b.id
{code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
