[
https://issues.apache.org/jira/browse/CALCITE-4735?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xurenhe updated CALCITE-4735:
-----------------------------
Description:
`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.
was:
`SubstitutionVisitor` don't work for rewriting query by mv, let me show a
example of the current failure.
{code:java}
@Test void testAggCallArgExpressedByMvProjs2() {
final String mv = ""
+ "select \"deptno\""
+ "from \"emps\" group by \"deptno\"";
final String query = ""
+ "select \"deptno\", count(*)"
+ "from \"emps\" group by \"deptno\"";
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.
> 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)
