Hello everyone. I'd like to discuss an example of materialized view recognition.
Query: select c1, count(distinct c2) as countd_c2, sum(c3) as sum_c3 from table group by c1 Mv1: select c1, c2 from table group by c1, c2 Mv2: select c1, c4, c5, sum(c3) as sum_c3_mv2 from table group by c1, c4, c5 If query tries to identify MV1 or MV2, neither of them can be recognized.Because the materialized view cannot express the columns required by Query. One solution as follows: select a.c1, a.countd_c2, b.sum_c3 from ( select c1, count(distinct c2) as countd_c2 from table group by c1 ) a join ( select c1, sum(c3) as sum_c3 from table group by c1 ) b on a.c1 = b.c1 Materialized view recognition: select a.c1, a.countd_c2, b.sum_c3 from ( select c1, count(distinct c2) as countd_c2 from MV1 group by c1 ) a join ( select c1, sum(sum_c3_mv2) as sum_c3 from MV2 group by c1 ) b on a.c1 = b.c1 If query is splited into Join RelNode, MV1 and MV2 can be recognized.This method can accurately identify materialized views and improve the reuse of materialized views.This example has been shown in doc. In the project, we use RelOptRule to realize it.However, Calcite does not support custom normalization rules for materialized view recognition. I have implemented the custom normalization rules before materialized view recognition, PR, which has been working for a long time in my project.Looking forward to the discussion and more feedback,thanks. DOC??https://docs.google.com/document/d/1mmAsK_uW-fBs893JERP1gspMurX2lNXcVGeesP4XUqQ/edit PR : https://github.com/apache/calcite/pull/2262
