[DRILL-5866] Methods supportsFullOuterJoins and supportsLimitedOuterJoins of DatabaseMetaData return incorrect values

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.10.0
• Component/s: Client - JDBC, Metadata, Storage - JDBC
• Assignee: Unassigned
• Reporter: second88
• Created: 12/Oct/17 02:12
• Updated: 12/Oct/17 02:12

Description

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:

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 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 and join_clause of Oracle, I think nested join in general means using parentheses to override default precedence.

An example of nested join

--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