[
https://issues.apache.org/jira/browse/CALCITE-4276?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17293888#comment-17293888
]
Justin Swett edited comment on CALCITE-4276 at 3/2/21, 6:06 PM:
----------------------------------------------------------------
Hmmm.. I think I have the SQL's ported to the test correctly with the one
exception maybe being around the dates but as far as I can tell the rewrite is
firing correctly:
{code:java}
// code placeholder
final String matSql = " SELECT\n" +
" \"deptno\",\n" +
" COUNT(*) AS \"dept_size\",\n" +
" SUM(\"salary\") AS \"dept_budget\"\n" +
" FROM\n" +
" \"emps\" \n" +
" GROUP BY\n" +
" \"deptno\"";
final String querySql = " SELECT\n" +
" FLOOR(\"CREATED_AT\" TO YEAR) AS by_year,\n" +
" COUNT(*) AS \"num_emps\"\n" +
" FROM\n" +
" (SELECT\n" +
" \"deptno\"\n" +
" FROM\n" +
" \"emps\") AS \"t\"\n" +
" JOIN (SELECT\n" +
" \"deptno\",\n" +
" cast('1997-01-20 12:34:56' as timestamp) as \"CREATED_AT\"\n" +
" FROM \"depts\") using (\"deptno\")\n" +
" GROUP BY\n" +
" FLOOR(\"CREATED_AT\" TO YEAR)";
Sql r = sql(matSql, querySql);
{code}
Rels:
{noformat}
MV Rel
LogicalAggregate(group=[{0}], dept_size=[COUNT()], dept_budget=[SUM($1)])
LogicalProject(deptno=[$1], salary=[$3])
LogicalTableScan(table=[[hr, emps]])
Query Rel
LogicalProject(BY_YEAR=[$0], num_emps=[$1])
LogicalAggregate(group=[{0}], num_emps=[COUNT()])
LogicalProject($f0=[FLOOR($2, FLAG(YEAR))])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalProject(deptno=[$1])
LogicalTableScan(table=[[hr, emps]])
LogicalProject(deptno=[$0], CREATED_AT=[1997-01-20 12:34:56])
LogicalTableScan(table=[[hr, depts]])
Rewrite Rel
LogicalAggregate(group=[{7}], num_emps=[$SUM0($1)])
LogicalProject(deptno=[$0], dept_size=[$1], dept_budget=[$2], deptno0=[$3],
name=[$4], employees=[$5], location=[$6], $f7=[FLOOR(1997-01-20 12:34:56,
FLAG(YEAR))])
LogicalFilter(condition=[=($0, $3)])
LogicalJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[hr, MV0]])
LogicalTableScan(table=[[hr, depts]]){noformat}
was (Author: jswett):
Hmmm.. I think I have the SQL's ported to the test correctly with the one
exception maybe being around the dates but as far as I can tell the rewrite is
firing correctly:
{code:java}
// code placeholder
final String matSql = " SELECT\n" +
" \"deptno\",\n" +
" COUNT(*) AS \"dept_size\",\n" +
" SUM(\"salary\") AS \"dept_budget\"\n" +
" FROM\n" +
" \"emps\" \n" +
" GROUP BY\n" +
" \"deptno\"";
final String querySql = " SELECT\n" +
" FLOOR(\"CREATED_AT\" TO YEAR) AS by_year,\n" +
" COUNT(*) AS \"num_emps\"\n" +
" FROM\n" +
" (SELECT\n" +
" \"deptno\"\n" +
" FROM\n" +
" \"emps\") AS \"t\"\n" +
" JOIN (SELECT\n" +
" \"deptno\",\n" +
" cast('1997-01-20 12:34:56' as timestamp) as \"CREATED_AT\"\n" +
" FROM \"depts\") using (\"deptno\")\n" +
" GROUP BY\n" +
" FLOOR(\"CREATED_AT\" TO YEAR)";
Sql r = sql(matSql, querySql);
{code}
Rels:
{noformat}
MV Rel
LogicalAggregate(group=[{0}], dept_size=[COUNT()], dept_budget=[SUM($1)])
LogicalProject(deptno=[$1], salary=[$3])
LogicalTableScan(table=[[hr, emps]])Query Rel
LogicalProject(BY_YEAR=[$0], num_emps=[$1])
LogicalAggregate(group=[{0}], num_emps=[COUNT()])
LogicalProject($f0=[FLOOR($2, FLAG(YEAR))])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalProject(deptno=[$1])
LogicalTableScan(table=[[hr, emps]])
LogicalProject(deptno=[$0], CREATED_AT=[1997-01-20 12:34:56])
LogicalTableScan(table=[[hr, depts]])
Rewrite Rel
LogicalAggregate(group=[{7}], num_emps=[$SUM0($1)])
LogicalProject(deptno=[$0], dept_size=[$1], dept_budget=[$2], deptno0=[$3],
name=[$4], employees=[$5], location=[$6], $f7=[FLOOR(1997-01-20 12:34:56,
FLAG(YEAR))])
LogicalFilter(condition=[=($0, $3)])
LogicalJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[hr, MV0]])
LogicalTableScan(table=[[hr, depts]]){noformat}
> 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)
