You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Jesus Camacho Rodriguez (JIRA)" <ji...@apache.org> on 2017/05/23 13:01:04 UTC
[jira] [Updated] (CALCITE-1802) Add post-aggregation step for Union
in materialized view rewriting
[ https://issues.apache.org/jira/browse/CALCITE-1802?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jesus Camacho Rodriguez updated CALCITE-1802:
---------------------------------------------
Description:
Follow-up on CALCITE-1795.
Rewriting for Aggregate queries needs a post-aggregation step that is not currently added.
Query:
{code:sql}
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON (emps.deptno = depts.deptno)
WHERE salary > 10000
GROUP BY empid, deptname;
{code}
Materialized view definition:
{code:sql}
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON (emps.deptno = depts.deptno)
WHERE salary > 12000
GROUP BY empid, deptname;
{code}
Rewriting:
{code:sql}
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;
{code}
was:
Follow-up on CALCITE-1795.
Rewriting for Aggregate queries needs a post-aggregation step that is not currently added.
Query:
{code:sql}
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON (emps.deptno = depts.deptno)
WHERE salary > 10000
GROUP BY empid, deptname;
{code}
Materialized view definition:
{code:sql}
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON (emps.deptno = depts.deptno)
WHERE salary > 12000
GROUP BY empid, deptname;
{code}
Rewriting:
{code:sql}
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);
{code}
> Add post-aggregation step for Union in materialized view rewriting
> ------------------------------------------------------------------
>
> Key: CALCITE-1802
> URL: https://issues.apache.org/jira/browse/CALCITE-1802
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Jesus Camacho Rodriguez
> Assignee: Jesus Camacho Rodriguez
> Fix For: 1.13.0
>
>
> Follow-up on CALCITE-1795.
> Rewriting for Aggregate queries needs a post-aggregation step that is not currently added.
> Query:
> {code:sql}
> SELECT empid, deptname, SUM(salary) AS s
> FROM emps
> JOIN depts ON (emps.deptno = depts.deptno)
> WHERE salary > 10000
> GROUP BY empid, deptname;
> {code}
> Materialized view definition:
> {code:sql}
> SELECT empid, deptname, SUM(salary) AS s
> FROM emps
> JOIN depts ON (emps.deptno = depts.deptno)
> WHERE salary > 12000
> GROUP BY empid, deptname;
> {code}
> Rewriting:
> {code:sql}
> 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;
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)