[ https://issues.apache.org/jira/browse/CALCITE-4619?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17351046#comment-17351046 ]
Stamatis Zampetakis commented on CALCITE-4619: ---------------------------------------------- One way to treat the problem would be to modify {{JdbcJoinRule}} so that it does not perform the rewrite to {{JdbcJoin}} when the dialect does not support full outer joins. Other rules could be used to rewrite a FULL outer join to something equivalent so that it can be pushed completely to JDBC. > "Full join" generates an incorrect execution plan under mysql > ------------------------------------------------------------- > > Key: CALCITE-4619 > URL: https://issues.apache.org/jira/browse/CALCITE-4619 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.26.0 > Reporter: JIasen Sheng > Priority: Major > > 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 > {code:java} > select count(*) as c from \"foodmart\".\"store\" as p1 full join > \"foodmart\".\"store\" as p2 using (\"store_id\") > {code} > . > {code:java} > 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]]){code} > 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 > -- This message was sent by Atlassian Jira (v8.3.4#803005)