[jira] [Commented] (CALCITE-4619) "Full join" generates an incorrect execution plan under mysql

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4619?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17358458#comment-17358458 ] 

Stamatis Zampetakis commented on CALCITE-4619:
----------------------------------------------

[~angelzouxin] Rewritting outer joins is not straighforward and we need to be very careful for not introducing correctness problems. Another approach, that could be used to solve this problem would be to perform the full join using {{Enumerable}} operators when the underlying DBMS does not support a certain type of join. This might be less efficient but it is much easier to implement. 

In fact, I think it makes perfect sense to avoid transforming a {{LogicalJoin}} to {{JdbcJoin}} when the dialect does not support it. This could be the first step. Then if we find a correct way to rewrite the full outer join to something equivalent we could do it in a follow up JIRA. WDYT?

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