Description
Follow-up on CALCITE-1795.
Rewriting for Aggregate queries needs a post-aggregation step that is not currently added.
Query:
SELECT empid, deptname, SUM(salary) AS s FROM emps JOIN depts ON (emps.deptno = depts.deptno) WHERE salary > 10000 GROUP BY empid, deptname;
Materialized view definition:
SELECT empid, deptname, SUM(salary) AS s FROM emps JOIN depts ON (emps.deptno = depts.deptno) WHERE salary > 12000 GROUP BY empid, deptname;
Rewriting:
SELECT empid, deptname, SUM(s) FROM ( SELECT empid, deptname, s FROM mv UNION ALL SELECT empid, deptname, SUM(salary) AS s FROM emps JOIN depts ON (emps.deptno = depts.deptno) WHERE salary > 10000 AND salary <= 12000 GROUP BY empid, deptname) subq GROUP BY empid, deptname;
Attachments
Issue Links
- relates to
-
CALCITE-1795 Extend materialized view rewriting to produce rewritings using Union operators
-
- Closed
-