[CALCITE-4619] "Full join" generates an incorrect execution plan under mysql

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.26.0
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: JIasen Sheng
• Created: 24/May/21 08:50
• Updated: 20/Oct/21 05:24
• Resolved: 11/Jun/21 12:28

Description

The current jdbcJoinRules can match any join type, but not all jdbc databases can support full join, such as mysql and H2.
Calcite will generate the following plan for

select count(*) as c from \"foodmart\".\"store\" as p1 full join \"foodmart\".\"store\" as p2 using (\"store_id\")

.

JdbcToEnumerableConverter
  JdbcAggregate(group=[{}], C=[COUNT()])
    JdbcJoin(condition=[=($0, $1)], joinType=[full])
      JdbcProject(store_id=[$0])
        JdbcTableScan(table=[[foodmart, store]])
      JdbcProject(store_id=[$0])
        JdbcTableScan(table=[[foodmart, store]])

When the plan is executed in Mysql, the execution will fail due to the existence of a full join.

 

 

I think it is necessary to add supportsFullJoin method in SqlDialect, and add JdbcJoinRule matching rules to solve this problem

 

Issue Links

• links to: GitHub Pull Request #2430