[
https://issues.apache.org/jira/browse/CALCITE-1965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16140369#comment-16140369
]
Jesus Camacho Rodriguez commented on CALCITE-1965:
--------------------------------------------------
Thanks for your PR [~christian.beikov]!
I think the change that you propose is more complex than what it is implemented
in your PR. In particular, I would keep the concept of lineage as it is (once
we have an outer join, IMO the lineage of the columns in the inner side is not
clear anymore) and the MV rewriting algorithm
({{AbstractMaterializedViewRule}}) would need to be extended. Let me try to go
a bit more into detail.
Currently the algorithm relies on the commutativity and associativity
properties of INNER joins. Then it tries to do complex rewritings on top of the
MV scan, trying to add e.g. missing joins or adding compensation filters.
With the change that you did, OUTER joins will be treated as INNER joins
(although additional predicates will be inferred) which would yield wrong
results in more complex cases. One of the key concepts that the algorithm uses
is the equivalence sets (columns that are known to hold equal values for a
given row) and in this case equality columns for OUTER joins would end up in
the same equivalence set. To see this with a real example, play with INNER and
OUTER joins in the same query after applying your change. For instance, I would
expect that if you try to have an inner join followed by an outer join in your
query, and an outer join followed by an inner join in your view, the rewriting
will trigger in occasions that it should not.
I think only extension of {{AbstractMaterializedViewRule}} is needed for this
case. Similar to {{INSTANCE_JOIN}}, an {{INSTANCE_OUTER_JOIN}} could be
created. The simplest case would be to match a single OUTER join at first
(afterwards the rule could be extended with those corner cases for which
multiple outer joins can be rewritten). From a high-level perspective, the
filter predicates would be pulled up from below the OUTER join, the available
columns would be checked, and then the type and conditions of the OUTER join
would be checked for equivalence. A variant with a Project operator on top
could be added to, similar to {{INSTANCE_PROJECT_JOIN}}.
If you want to continue with the implementation and need more help, let me
know. I think it is certainly very interesting to have support for OUTER joins
in the rewriting algorithm!
> 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)
