[
https://issues.apache.org/jira/browse/CALCITE-1965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16145198#comment-16145198
]
Christian Beikov commented on CALCITE-1965:
-------------------------------------------
To support matching between queries containing equivalent subqueries/derived
tables we could do a nested matching. Imagine the following view
{noformat}
select *
from a
left join (
select b.a as x
from b
left join c on b.b = c.b
) on a.a = x
{noformat}
EQ={}
OEQ={a.a, x}
=> x is a marker for nested matching
xEQ={}
xOEQ={b.b, c.b}
If such a marker is encountered during matching, then an nested matching is
invoked on the subqueries. The nested matching is a bit different from the
normal matching. The query's xEQ and view's xEQ must be equal. If only
non-nullable FK joins are allowed, it should be sufficient that qxEQ is a
subset of vxEQ. The query's xOEQ must be a strict(including type equality)
prefix of the view's xOEQ.
What do you say? Think this could work?
> 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)
