[jira] [Created] (DRILL-5866) Methods supportsFullOuterJoins and supportsLimitedOuterJoins of DatabaseMetaData return incorrect values

["second88 (JIRA)" <ji...@apache.org>]

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)