Major Description
When MaterializedViewOnlyAggregateRule does its rewrite it will sometimes produce a project above the materialized view that references the incorrect input. For example,
Materialization Rel:
LogicalAggregate(group=[{0}], ORDER_ITEMS.ORDER_SIZE=[COUNT()], ORDER_ITEMS.ORDER_SUM=[$SUM0($1)])
LogicalProject(ORDER_ID=[$1], AMOUNT=[$2])
2(table=[[orders_items]])
Query Rel:
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]])
Will Produce the following rel after MaterializedViewOnlyAggregateRule fires:
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]])
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.