[ 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)