[jira] [Commented] (CALCITE-1802) Add post-aggregation step for Union in materialized view rewriting

["Jesus Camacho Rodriguez (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16031600#comment-16031600 ] 

Jesus Camacho Rodriguez commented on CALCITE-1802:
--------------------------------------------------

Added fix to bail out if aggregation is on DISTINCT and additional tests in http://git-wip-us.apache.org/repos/asf/calcite/commit/83cef58.

> 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)