[
https://issues.apache.org/jira/browse/CALCITE-1965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16143385#comment-16143385
]
Jesus Camacho Rodriguez commented on CALCITE-1965:
--------------------------------------------------
[~christian.beikov], I have checkout your code and as you mentioned, the
rewriting is not being triggered for the tests that you added.
However, after going through the debugger, when the rewriting is not being
triggered is by chance, i.e. the algorithm is not complete and it might miss a
rewriting, rather than by design.
For instance, this simple example shows the wrong behavior, as query is being
rewritten using the materialized view but they are obviously not equivalent:
{noformat}
view:
select "dependents"."name" from "dependents" left join "locations" on
("dependents"."empid" = "locations"."empid");
query:
select "dependents"."name" from "dependents" cross join "locations" where
"locations"."empid" is null or "dependents"."empid" = "locations"."empid";
{noformat}
(As a side note, if you want to reproduce this example, you will also need to
change the type of the {{empid}} field in {{JdbcTest.Dependent}} to _Integer_
rather than _int_ so the field is nullable).
As I mentioned above, the original implementation of the rewriting algorithm
does not support OUTER joins, i.e., it does not 'understand' the different
semantics of the INNER/OUTER joins. Thus, extending it to support them is more
elaborated than simply extending the metadata providers.
I will not be able to tackle this work immediately, but I gave some rough
guidelines on where to start looking above. If you start working on it but you
need more help, let me know.
> 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)
