[
https://issues.apache.org/jira/browse/CALCITE-1965?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16143851#comment-16143851
]
Christian Beikov commented on CALCITE-1965:
-------------------------------------------
I read the paper and understand what happens now. I'd like to propose a
solution(more like a WIP right now) that should be compatible to the concepts
of the paper i.e. it's an extension.
We introduce a subtype of {{RexCall}} called {{RexCallOuter}} that similar to
the idea of the oracle style join syntax, keeps track of columns that are from
an outer joined relation i.e. are row preserving.
In {{RelMdAllPredicates}} we rewrite the {{RexCall}} instances to
{{RexCallOuter}}.
Not sure if the following is strictly necessary, but I felt this 1. reduces
complexity and feels kind of 2. being the analogy of transitivity for outer
joins.
_In addition to that, we introduce a step to normalize the join structure of a
query before beginning optimization. This step will essentially change the join
type of outer join to an inner join if they are followed by a dependent inner
join. The goal is to ensure the RHS of a left join or LHS of a right join will
always be a left join or right join respectively._
When computing the equivalence classes for a query and materialization query,
we will also compute the "outer equivalence classes" which are composed of the
outer joined columns. Note that the outer equivalence classes should contain
all connected columns of the normal equivalence classes i.e. the outer
equivalence classes for a query like "select ... from a join b on a.a = b.a and
left join c on a.a = c.a" are *{a.a, b.a, c.a}* whereas the normal equivalence
classes are just *{a.a, b.a}*
The *equijoin subsumption test* is extended. It will additionally check if the
columns of the view's outer equivalence classes are contained in either the
normal or outer equivalence classes of the query. This allows to match a view
like "select ... from a left join b on a.a = b.a" also for a query like "select
... from a join b on a.a = b.a" although it needs a {{IS_NOT_NULL}}
compensation predicate for all columns of the view's outer equivalence classes
that were matched against columns of the normal equivalence classes of the
query.
What do you think, could that work? I worked out some examples that seem to
work.
a | id | a
-------------
| 1 | 1
| 2 | 2
| 3 | NULL
b | id | a | c
---------------
| 1 | 1 | 1
| 2 | 3 | 1
c | id | c
-----------
| 1 | 1
| 2 | 2
*View: m0*
Query: select a.id, b.id from a left join b on a.a = b.a
EQ = {}
OEQ = {a.a, b.a}
Result:
1, 1
2, null
*View: m1*
Query: select a.id, b.id, c.id from a left join b on a.a = b.a left join c on
b.c = c.c
EQ = {}
OEQ = {a.a, b.a} {b.c, c.c}
Result:
1, 1, 1
2, null, null
*View: m2*
Query: select a.id, b.id, c.id from a join b on a.a = b.a left join c on b.c =
c.c
EQ = {a.a, b.a}
OEQ = {b.c, c.c}
Result:
1, 1, 1
*Query 1 for m0*
Query: select a.id, b.id from a join b on a.a = b.a
EQ = {a.a, b.a}
OEQ = {}
Result:
1, 1
=> Since OEQ matches EQ, we need a compensation predicate
Rewritten-Query: select a.id, b.id from m0 where b.id is not null
*Query 2 for m1*
Query: select a.id, b.id, c.id from a join b on a.a = b.a left join c on b.c =
c.c
Result:
1, 1, 1
EQ = {a.a, b.a}
OEQ = {b.c, c.c}
=> Since OEQ matches EQ, we need a compensation predicate
Rewritten-Query: select a.id, b.id, c.id from m1 where b.id is not null
*Query 3 for m1*
Query: select a.id, b.id, c.id from a join b on a.a = b.a inner join c on b.c =
c.c
Result:
1, 1, 1
EQ = {a.a, b.a, b.c, c.c}
OEQ = {}
=> Since OEQ matches EQ, we need a compensation predicate
Rewritten-Query: select a.id, b.id, c.id from m1 where b.id is not null and
c.id is not null
> 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)
