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 <[email protected]> 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 <[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.
>
