[
https://issues.apache.org/jira/browse/CALCITE-4735?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wang Yanlin resolved CALCITE-4735.
----------------------------------
Resolution: Fixed
Fixed in
[https://github.com/apache/calcite/commit/46fb2632ea4457e5c0f87d42536ae2d5cdf5f07d,]
thx [~wojustme]
> SubstitutionVisitor of Aggregate Failed, when aggcalls of query could be
> expressed by target's grouping
> -------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-4735
> URL: https://issues.apache.org/jira/browse/CALCITE-4735
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Xurenhe
> Assignee: Xurenhe
> Priority: Major
> Labels: pull-request-available
> Time Spent: 6h 40m
> Remaining Estimate: 0h
>
> `SubstitutionVisitor` don't work for rewriting query by mv, let me show a
> example of the current failure.
> {code:java}
> @Test void testAggCallArgExpressedByMvProjs() {
> final String mv = ""
> + "select \"deptno\", \"name\""
> + "from \"emps\" group by \"deptno\", \"name\"";
> final String query = ""
> + "select \"deptno\", \"name\", count(distinct \"name\")"
> + "from \"emps\" group by \"deptno\", \"name\"";
> sql(mv, query).ok();
> }
> {code}
> I debug this issue, I found that: when `AggregateToAggregateUnifyRule`
> executing `unifyAggregates`, code want to find agg-calls of query could be
> equal or rollup by target's agg-calls.
> Should we have other way to handle in `AggregateToAggregateUnifyRule` or
> `AggregateOnCalcToAggregateUnifyRule`?
> I will give some examples to explain the problem:
> {code:sql}
> -- schema
> create table emps (
> empid integer,
> deptno integer,
> name varchar,
> salary float,
> commission integer
> )
> {code}
> ----
> AggregateToAggregateUnifyRule
> case1: mv's group equal to query's group
> {code:sql}
> --mv
> select deptno, commission
> from emps
> group by deptno, commission
> --query
> select deptno, commission, count(1), count(distinct commission)
> from emps
> group by deptno, commission
> --rewrite
> select deptno, commission, count(1), count(distinct commission)
> from mv
> group by deptno, commission
> {code}
> case2: mv's group rollup to query's group
> {code:sql}
> --mv
> select deptno, commission
> from emps
> group by deptno, commission
> --query
> select deptno, max(commission)
> from emps
> group by deptno
> --rewrite
> select deptno, max(commission)
> from mv
> group by deptno
> {code}
> ----
> AggregateOnCalcToAggregateUnifyRule
> case1: mv's group equal to query's group
> {code:sql}
> --mv
> select deptno, commission, mix(salary)
> from emps
> group by deptno, commission
> --query
> select deptno, commission, count(1)
> from emps
> group by deptno, commission
> --rewrite
> select deptno, commission, count(1)
> from mv
> group by deptno, commission
> {code}
> case2: mv's group rollup to query's group
> {code:sql}
> --mv
> select deptno, commission, name, mix(salary)
> from emps
> group by deptno, commission, name
> --query
> select deptno, commission, count(1), count(distinct name)
> from emps
> group by deptno, commission
> --rewrite
> select deptno, commission, count(1), count(distinct name)
> from mv
> group by deptno, commission
> {code}
> ----
> Here, Should we create new agg-calls to compensate missing agg-calls of
> query, which is based on mv's agg-group?
> Rewriting query's agg-call could be rewritten by equal agg-call of mv or
> rollup agg-call of mv,
> also could be {color:#ff0000}rewritten by emitted agg-group of mv{color}.
> Please review this viewpoint, if this viewpoint is right, I can do it.
> Thanks a lot.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
