[jira] [Commented] (CALCITE-4148) MaterializedViewAggregateRule invalid optimization with time unit "rollupable" column in query

["xzh_dz (Jira)" <ji...@apache.org>]

    [ 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)