[
https://issues.apache.org/jira/browse/CALCITE-4148?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17169459#comment-17169459
]
xzh_dz commented on CALCITE-4148:
---------------------------------
Maybe we can use `RelFieldTrimmer` to trim unused fields,the relational algebra
is shown below.It did not recognize materialized views successfully.
MV:
{code:java}
LogicalAggregate(group=[{}], C=[COUNT()]) LogicalTableScan(table=[[hr,
events]])
{code}
Query:
{code:java}
LogicalAggregate(group=[{0}], EXPR$1=[COUNT()])
LogicalCalc(expr#0..1=[{inputs}], expr#2=[CAST($t1):TIMESTAMP(0)],
expr#3=[FLAG(MINUTE)], expr#4=[FLOOR($t2, $t3)], $f0=[$t4])
LogicalTableScan(table=[[hr, events]])
{code}
And check `AggregateOnCalcToAggregateUnifyRule` in SubstitutionVisitor. The
projection is not a mapping.
> MaterializedViewAggregateRule invalid optimization with time unit
> "rollupable" column in query
> ----------------------------------------------------------------------------------------------
>
> Key: CALCITE-4148
> URL: https://issues.apache.org/jira/browse/CALCITE-4148
> Project: Calcite
> Issue Type: Bug
> Reporter: Steven Talbot
> Priority: Major
>
> The issue occurs when you have a time unit in your query and no group fields
> in your view aggregate, and the view aggregate sits directly on top of a
> TableScan.
> Here is a test that is almost a regression test in
> MaterializedViewRelOptRules test:
>
> {code:java}
> @Test void testAggregateMaterializationAggregateFuncsNoInvalidMatch() {
> sql("select "
> + "count(*) as c\n"
> + "from \"events\"\n",
> "select floor(cast(\"ts\" as timestamp) to minute), count(*)\n"
> + "from \"events\" group by floor(cast(\"ts\" as timestamp) to
> minute)")
> .noMat();
> }
> {code}
>
> However, this test passes in current code. The reason is that the SQL for the
> materialization is converted to
>
> {noformat}
> LogicalAggregate(group=[{}], C=[COUNT()])
> LogicalProject($f0=[0])
> LogicalTableScan(table=[[hr, events]])
> {noformat}
> with the interleaved LogicalProject projecting an unused constant.
> Without that logical project (from what I saw hitting the bug in my code),
> what happens is that MaterializedViewAggregateRule.generateMapping gets the
> input, in this case the table, sees that the input contains the field (in
> this case "ts") that can be rolled up into the floor, and maps it. Downstream
> code then mishandles that unexpected mapping, and optimization occurs where
> it should not.
> It seems as though this rule generally expects the input to the view
> aggregate to be a project that contains only fields the aggregate will group
> by or aggregate. I can make my code comply with that paradigm, but still it
> seems this is worth fixing.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
