[
https://issues.apache.org/jira/browse/CALCITE-4619?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17353916#comment-17353916
]
JIasen Sheng commented on CALCITE-4619:
---------------------------------------
[~zabetak], i agree with you, i try to rewrite FULL outer join to something
equivalent, according to
[https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql],
we could use
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL
to rewrite the full outer join
SELECT * FROM t1
FULL JOIN t2 ON t1.id = t2.id
when the dialect does not support full outer joins.
But there is one issue that needs to be discussed. What additional conditions
do we need to add in the right join？
If it is an equivalent condition, we can use the condition column is null as an
additional filter condition.
If the condition is rightColumn <constant value like t2.id < 10, we can get the
correct result by adding the condition to the left join.
{code:java}
SELECT * FROM t1 LEFT JOIN t2 ON t2.id < 10 WHERE t2.id IS NULL UNION ALL
SELECT * FROM t1 RIGHT JOIN t2 ON t2.id < 10
{code}
But if it is a complicated condition, I am not sure how to perform equivalent
conversion.
Do you have any good suggestions?
> "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)
