[jira] [Assigned] (CALCITE-4735) Some opinions for SubstitutionVisitor of Aggregate.

["Xurenhe (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-4735?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Xurenhe reassigned CALCITE-4735:
--------------------------------

    Assignee: Xurenhe

> Some opinions for SubstitutionVisitor of Aggregate.
> ---------------------------------------------------
>
>                 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
>
> `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:red}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)