Hi, Chunwei.
Thank you very much for your reply and I read it carefully. Then I tried
running a test in the MaterializedViewSubstitutionVisitorTest unit test
class.
I expected it to rewrite successfully, but not. The test is as follows:
@Test void test() {
String mv = ""
+ "select \"emps\".\"empid\", \"emps\".\"deptno\",
\"depts\".\"deptno\" from\n"
+ "\"hr\".\"emps\" left join \"hr\".\"depts\"\n"
+ "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
+ "where \"emps\".\"deptno\" > 10";
String query = ""
+ "select \"emps\".\"empid\", \"emps\".\"deptno\",
\"depts\".\"deptno\" from\n"
+ "\"hr\".\"emps\" left join \"hr\".\"depts\"\n"
+ "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
+ "where \"emps\".\"deptno\" > 20";
sql(mv, query).ok();
}
Next, I will read the source code to find out why it is not supported. If
possible, I will support out of join queries in materialization view in the
next month.
On Tue, Nov 23, 2021 at 4:09 PM Chunwei Lei <[email protected]> wrote:
> Hi, Hongkang.
>
> Calcite has two ways to do query-rewriting, namely substitution, and
> rules[1]. MaterializedViewRule builds on the ideas presented in the
> paper[2], which does not talk much about outer-join rewriting. If you want
> to support outer join, you can try to use
> MaterializedViewSubstitutionVisitor.
>
>
>
>
> [1]
> https://calcite.apache.org/docs/materialized_views.html#union-rewriting.
> [2] https://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.95.113
>
> Best,
> Chunwei
>
>
> On Tue, Nov 23, 2021 at 3:00 PM hongkang jiang <[email protected]>
> wrote:
>
> > Hello everyone，
> > I have a question about that Calcite does not support outer join in
> > materialized view. I have been thinking for a long time and still haven't
> > figured it out. Could anyone help me answer the reason? Thanks very much.
> > I also found in Calcite's materialization a annotation like that
> Currently
> > we only support tablescan-project-filter-inner Join , And the method
> > MaterializedViewRule. IsValidRelNodePlan () will filter out the outer
> join.
> >
>
--
Best,
Hongkang.
