[
https://issues.apache.org/jira/browse/CALCITE-4733?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4733:
---------------------------------
Description:
Original subject: "MaterializedViewAggregateRule should not rewrite top level
name alias in project".
We use *MaterializedViewAggregateRule* to do optimize.
the original sql is :
{code:java}
SELECT (intDiv(ts/1000, 60) * 60) * 1000 as t, source_idc, dest_idc,
sum(jitter200)/sum(total) as `jitter200_rate` FROM metricsdb.test_table WHERE
ts/1000 >= 1627874961 AND source_idc in ('xxx') AND dest_idc IN ('xxx') GROUP
BY t,source_idc,dest_idc ORDER BY t
{code}
after optimize, the sql is :
{code:java}
SELECT `intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `$f14`, `source_idc`,
`dest_idc`, SUM(`jitter200`) / SUM(`total`) AS `$f3` FROM
`metricsdb`.`s2s_idc2idc_jitter_mv_all` WHERE `ts` / 1000 >= 1627874961 AND
`source_idc` IN ('xxx') AND `dest_idc` IN ('xxx') GROUP BY `source_idc`,
`dest_idc`, `intDiv`(`ts` / 1000, 60) * 60 * 1000 ORDER BY `intDiv`(`ts` /
1000, 60) * 60 * 1000
{code}
we expected it to be :
{code:java}
`intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `t`{code}
, not
{code:java}
`intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `$f14`{code}
After examine the code , we found in *rewriteView* of
*MaterializedViewAggregateRule*
{code:java}
return relBuilder
.project(rewrittenExprs)
.convert(topRowType, false) // this means do not compare the names.
.build();
{code}
when change it to true, everything goes as expected.
I think the rewrite rule should maintain then same type and name , not just
type.
If it's true, we can come up with some modification and tests.
was:
We use *MaterializedViewAggregateRule* to do optimize.
the original sql is :
{code:java}
SELECT (intDiv(ts/1000, 60) * 60) * 1000 as t, source_idc, dest_idc,
sum(jitter200)/sum(total) as `jitter200_rate` FROM metricsdb.test_table WHERE
ts/1000 >= 1627874961 AND source_idc in ('xxx') AND dest_idc IN ('xxx') GROUP
BY t,source_idc,dest_idc ORDER BY t
{code}
after optimize, the sql is :
{code:java}
SELECT `intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `$f14`, `source_idc`,
`dest_idc`, SUM(`jitter200`) / SUM(`total`) AS `$f3` FROM
`metricsdb`.`s2s_idc2idc_jitter_mv_all` WHERE `ts` / 1000 >= 1627874961 AND
`source_idc` IN ('xxx') AND `dest_idc` IN ('xxx') GROUP BY `source_idc`,
`dest_idc`, `intDiv`(`ts` / 1000, 60) * 60 * 1000 ORDER BY `intDiv`(`ts` /
1000, 60) * 60 * 1000
{code}
we expected it to be :
{code:java}
`intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `t`{code}
, not
{code:java}
`intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `$f14`{code}
After examine the code , we found in *rewriteView* of
*MaterializedViewAggregateRule*
{code:java}
return relBuilder
.project(rewrittenExprs)
.convert(topRowType, false) // this means do not compare the names.
.build();
{code}
when change it to true, everything goes as expected.
I think the rewrite rule should maintain then same type and name , not just
type.
If it's true, we can come up with some modification and tests.
> Column names in materialization do not match
> --------------------------------------------
>
> Key: CALCITE-4733
> URL: https://issues.apache.org/jira/browse/CALCITE-4733
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.27.0
> Reporter: Enze Liu
> Priority: Minor
> Attachments: image-2021-08-13-14-06-36-712.png
>
>
> Original subject: "MaterializedViewAggregateRule should not rewrite top level
> name alias in project".
> We use *MaterializedViewAggregateRule* to do optimize.
> the original sql is :
> {code:java}
> SELECT (intDiv(ts/1000, 60) * 60) * 1000 as t, source_idc, dest_idc,
> sum(jitter200)/sum(total) as `jitter200_rate` FROM metricsdb.test_table WHERE
> ts/1000 >= 1627874961 AND source_idc in ('xxx') AND dest_idc IN ('xxx') GROUP
> BY t,source_idc,dest_idc ORDER BY t
> {code}
> after optimize, the sql is :
> {code:java}
> SELECT `intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `$f14`, `source_idc`,
> `dest_idc`, SUM(`jitter200`) / SUM(`total`) AS `$f3` FROM
> `metricsdb`.`s2s_idc2idc_jitter_mv_all` WHERE `ts` / 1000 >= 1627874961 AND
> `source_idc` IN ('xxx') AND `dest_idc` IN ('xxx') GROUP BY `source_idc`,
> `dest_idc`, `intDiv`(`ts` / 1000, 60) * 60 * 1000 ORDER BY `intDiv`(`ts` /
> 1000, 60) * 60 * 1000
> {code}
> we expected it to be :
> {code:java}
> `intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `t`{code}
> , not
> {code:java}
> `intDiv`(`ts` / 1000, 60) * 60 * 1000 AS `$f14`{code}
>
> After examine the code , we found in *rewriteView* of
> *MaterializedViewAggregateRule*
> {code:java}
> return relBuilder
> .project(rewrittenExprs)
> .convert(topRowType, false) // this means do not compare the names.
> .build();
> {code}
> when change it to true, everything goes as expected.
>
> I think the rewrite rule should maintain then same type and name , not just
> type.
>
> If it's true, we can come up with some modification and tests.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
