[
https://issues.apache.org/jira/browse/CALCITE-1965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16140545#comment-16140545
]
Jesus Camacho Rodriguez commented on CALCITE-1965:
--------------------------------------------------
Yes, I agree with you that it might not make much sense to have such query. In
fact, there are other rules that might transform the OUTER join into a INNER
when certain conditions are met, e.g., the existence of null filtering
conditions. However, the rewriting needs to remain correct even when the query
does not make much sense from a user perspective, essentially because we might
end up with that plan by applying transformation. In addition, that is a simple
example: the algorithm would produce incorrect rewritings with arbitrary
combinations of INNER/OUTER joins, even on different columns. I will checkout
your branch and try to create a test for the scenario with multiple OUTER/INNER
joins for which the rewriting might execute incorrectly.
Outer joins was indeed something that we were looking forward to supporting in
the MV rewriting, that is why we had a general idea in mind on how to
proceed... However I have not had the time to work on it yet.
Finally, I think {{getAllPredicates}} can be extended as you propose. If you
create another issue and a PR with only those changes plus the tests, I can
review it and push it.
> Support outer joins for materialized views
> ------------------------------------------
>
> Key: CALCITE-1965
> URL: https://issues.apache.org/jira/browse/CALCITE-1965
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: Christian Beikov
> Assignee: Julian Hyde
>
> Currently, only inner joins are supported for materialized view
> substitutions. The support for outer joins involves creating new pulled up
> predicates in case of outer joins that represent semantics of the join. For a
> join predicate like "a.id = b.id" the inner join just pulls up that
> predicate. When having a left join like e.g. {{select * from a left join b on
> a.id = b.id}}, the actual pulled up predicate would be {{OR(=(a.id,
> b.id),ISNULL(b.id))}}. For a right join it would be {{OR(=(a.id,
> b.id),ISNULL(a.id))}} and for a full outer join it would be {{OR(=(a.id,
> b.id),ISNULL(a.id),ISNULL(b.id))}}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
