You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by hongkang jiang <jh...@gmail.com> on 2021/11/23 07:00:16 UTC
Why calcite does not support outer join in materialized view
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.
Re: Why calcite does not support outer join in materialized view
Posted by hongkang jiang <jh...@gmail.com>.
I found a reason for not supporting materialized view outer join.
There is the verification code in JoinOnLeftCalcToJoinUnifyRule class
apply() method:
if (joinRelType != JoinRelType.INNER
&& !(joinRelType.isOuterJoin() && qInput1Cond.isAlwaysTrue())) {
return null;
}
then, I try to remove it and test the previous case, the result is rewrite
successfully, the result is as follows:
*SELECT *FROM "hr"."MV0"WHERE "deptno" > 20*
But, I need to further analyze this part of the code to see if this
adjustment is possible.
On Tue, Nov 23, 2021 at 8:19 PM hongkang jiang <jh...@gmail.com> wrote:
> 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 <ch...@gmail.com>
> 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 <jh...@gmail.com>
>> 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.
>
Re: Why calcite does not support outer join in materialized view
Posted by hongkang jiang <jh...@gmail.com>.
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 <ch...@gmail.com> 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 <jh...@gmail.com>
> 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.
Re: Why calcite does not support outer join in materialized view
Posted by Chunwei Lei <ch...@gmail.com>.
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 <jh...@gmail.com> 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.
>