[
https://issues.apache.org/jira/browse/CALCITE-1965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16144152#comment-16144152
]
Jesus Camacho Rodriguez commented on CALCITE-1965:
--------------------------------------------------
Thanks for the detailed explanation [~christian.beikov]. I have some comments /
questions.
* We could assume a normalization step is executed before trying to rewrite,
however we cannot make assumptions about the shape of the plan, as this might
lead to incorrect rewriting. What I mean with this is that it is OK to trigger
the rewriting under some preconditions for the plan, but rewriting should bail
out if those preconditions are not met, rather than assuming that the plan will
meet those preconditions.
* What happens when there are filters in between the joins, e.g., we have joins
among subqueries? Where would those fall? How do you know whether they are
below or between INNER / OUTER joins when you pull up the predicates?
* What about these views / queries?
View1: select a.id, b.id, c.id from a left join b on a.a = b.a left join c on
b.a = c.c;
Query1: select a.id, b.id, c.id from a left join c on a.a = c.c left join b on
a.a = b.a;
View2: select a.id, b.id, c.id from a left join b on a.a = b.a right join c on
b.a = c.c;
Query2: select a.id, b.id, c.id from a right join c on a.a = c.c left join b on
a.a = b.a;
Would columns end up in the same OEQ group? Will you recognize between left /
right / full?
* OUTER joins do not have the same commutativity / associativity properties
than INNER joins, thus the order of the join is important but it is not
currently captured by OEQ. Maybe OEQ should not be a set but rather a list?
That would be like splitting the query into multiple blocks. Also, I am not
sure how this algorithm would play with the extension that have been
implemented to compensate queries and that are not part of the original paper;
you can see those here:
http://calcite.apache.org/docs/materialized_views#rewriting-coverage . Any
ideas? Those that are not applicable when OUTER joins are present, should not
be triggered (we could simply bail out in initial implementation, but we would
need to identify them).
> 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)
