[CALCITE-1802] Add post-aggregation step for Union in materialized view rewriting

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.13.0
• Component/s: core
• Assignee: Jesús Camacho Rodríguez
• Reporter: Jesús Camacho Rodríguez
• Created: 22/May/17 19:09
• Updated: 27/Feb/24 22:24
• Resolved: 31/May/17 11:44

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;

Issue Links

• relates to: CALCITE-1795 Extend materialized view rewriting to produce rewritings using Union operators