[jira] [Comment Edited] (CALCITE-4276) Rewriting for materialized view will project wrong input

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

    [ https://issues.apache.org/jira/browse/CALCITE-4276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17293350#comment-17293350 ] 

Julian Hyde edited comment on CALCITE-4276 at 3/2/21, 5:13 AM:
---------------------------------------------------------------

[~jswett] and I have worked together on a fix, and we believe we have one in [julianhyde/4276-mv|https://github.com/julianhyde/calcite/tree/4276-mv].

[~jswett], There's one more thing that needs to be done before commit. We need a more concise test case, part of an existing test suite ({{MaterializationTest}}, probably), that looks as similar as possible to the existing tests in that suite. Now we have a fix, we can validate that the test passes if and only if the fix is present. 

Is there any chance the test case could look something like [this|https://github.com/apache/calcite/blob/ff4c16d1ea2192435e543fc9572ae3a44decbf79/core/src/test/java/org/apache/calcite/test/MaterializedViewRelOptRulesTest.java#L527]?


was (Author: julianhyde):
[~jswett] and I have worked together on a fix, and we believe we have one in [julianhyde/4276-mv|https://github.com/julianhyde/calcite/tree/4276-mv].

[~jswett], There's one more thing that needs to be done before commit. We need a more concise test case, part of an existing test suite ({{MaterializationTest}}, probably), that looks as similar as possible to the existing tests in that suite. Now we have a fix, we can validate that the test passes if and only if the fix is present. 

> Rewriting for materialized view will project wrong input
> --------------------------------------------------------
>
>                 Key: CALCITE-4276
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4276
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Justin Swett
>            Assignee: Julian Hyde
>            Priority: Major
>         Attachments: TemporalMaterializationWithJoinsTest.java
>
>
> When MaterializedViewOnlyAggregateRule does its rewrite it will sometimes produce a project above the materialized view that references the incorrect input. For example,
> Materialization Rel:
> {noformat}
> LogicalAggregate(group=[{0}], ORDER_ITEMS.ORDER_SIZE=[COUNT()], ORDER_ITEMS.ORDER_SUM=[$SUM0($1)])
>   LogicalProject(ORDER_ID=[$1], AMOUNT=[$2])
>     2(table=[[orders_items]]){noformat}
> Query Rel:
> {noformat}
> LogicalAggregate(group=[{0}], agg#0=[COUNT()])
>   LogicalProject($f0=[FLOOR($1, FLAG(YEAR))])
>     LogicalJoin(condition=[=($0, $1)], joinType=[inner])
>       LogicalProject(ORDER_ID=[$1])
>         2(table=[[orders_items]])
>       LogicalProject(ID=[$0], CREATED_AT=[$4])
>         4(table=[[orders]]){noformat}
> Will Produce the following rel after MaterializedViewOnlyAggregateRule fires:
> {noformat}
> LogicalAggregate(group=[{8}], agg#0=[$SUM0($1)])
>   LogicalProject(ORDER_ID=[$0], ORDER_ITEMS.ORDER_SIZE=[$1], ORDER_ITEMS.ORDER_SUM=[$2], ID=[$3], USER_ID=[$4], ORDER_AMOUNT=[$5], STATUS=[$6], CREATED_AT=[$7], $f8=[FLOOR($0, FLAG(YEAR))])
>     LogicalFilter(condition=[=($0, $3)])
>       LogicalJoin(condition=[true], joinType=[inner])
>         6(table=[[testing_mv]])
>         4(table=[[orders]]){noformat}
> Notice the "*$f8=[FLOOR($0, FLAG(YEAR))*" the $0 is pointing to the test_mv order_id field. It should be point to $7 for this test, so perhaps something with the filter?
> The attached test case reproduces the issue.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)