[
https://issues.apache.org/jira/browse/CALCITE-4198?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17190155#comment-17190155
]
xzh_dz commented on CALCITE-4198:
---------------------------------
[~zhishui]
Sorry for the late reply, you can take it. It is better to register custom
materialized recognition matching rules.This
[PR|https://github.com/apache/calcite/pull/2094] register custom materialized
recognition rules to enhance the ability of materialized recognition.
Here are some ideas to share:
1. It is possible that sum and count contain many different columns，would you
add some cases?
2.It seems that there is no example of AVG aggregation roll up?
3.If the query has an AVG aggregate function, MV has avg function and count
function, can materialization be identified？
> Query contains AVG function, materialized view contains SUM and COUNT
> functions, and materialized recognition fails
> -------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-4198
> URL: https://issues.apache.org/jira/browse/CALCITE-4198
> Project: Calcite
> Issue Type: Wish
> Reporter: xzh_dz
> Assignee: zhishui
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> There is a semantic equivalent case, which should be able to materialize and
> identify successfully, materialized view can express the calculation logic of
> query,but failed. This case can be reproduced as below.
> {code:java}
> // MaterializedViewSubstitutionVisitorTest
> @Test void testAvgMvMatch() {
> sql(
> "select \"empid\", sum(\"salary\"), count(\"salary\") from \"emps\"
> where \"empid\" > 10 group by \"empid\"",
> "select \"empid\", avg(\"salary\") from \"emps\" where \"empid\" > 10
> group by \"empid\"")
> .ok();
> }
> {code}
> The error message：
> {code:java}
> java.lang.AssertionError: Materialized view failed to be matched by optimized
> results:java.lang.AssertionError: Materialized view failed to be matched by
> optimized results: at
> org.apache.calcite.test.AbstractMaterializedViewTest.checkMaterialize(AbstractMaterializedViewTest.java:112)
> at
> org.apache.calcite.test.AbstractMaterializedViewTest.access$000(AbstractMaterializedViewTest.java:64)
> at
> org.apache.calcite.test.AbstractMaterializedViewTest$Sql.ok(AbstractMaterializedViewTest.java:227)
> at java.lang.invoke.MethodHandle.invokeWithArguments(MethodHandle.java:627)
> at
> org.apache.calcite.util.ImmutableBeans.lambda$makeDef$3(ImmutableBeans.java:275)
> at
> org.apache.calcite.util.ImmutableBeans$BeanImpl.invoke(ImmutableBeans.java:446)
> at com.sun.proxy.$Proxy12.ok(Unknown Source) at
> org.apache.calcite.test.MaterializedViewSubstitutionVisitorTest.testAvgMvMatch(MaterializedViewSubstitutionVisitorTest.java:101)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
