[
https://issues.apache.org/jira/browse/CALCITE-7604?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18089471#comment-18089471
]
Julian Hyde commented on CALCITE-7604:
--------------------------------------
Makes sense. Thanks for doing this.
Obviously, this rule would not be enabled by default. (It would cycle if in the
same program as {{JoinAggregateTransposeRule}}, and needs to be enabled based
on a cost model.)
Does this rule work for all join types (inner, left, right, full) or are there
restrictions?
> Add rule to pull up GROUP BY above JOIN
> ---------------------------------------
>
> Key: CALCITE-7604
> URL: https://issues.apache.org/jira/browse/CALCITE-7604
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
>
> Implement a new rule to pull up a GROUP BY above a JOIN when possible. The
> major benefit of the group-by pull up transformation is that the join may
> reduce the number of input rows to the group-by, if the join is selective.
> The idea of pulling the aggregation above a join is rather old and the
> following research papers are among the first that describe the individual
> transformations to a greater extend:
> # Yan & Larson (1995) "Interchanging the order of grouping and join"
> Technical Report
> # Yan & Larson (1995) "Eager Aggregation and Lazy Aggregation", VLDB
> Below a simple query demonstrating the group by pull up transformation using
> SQL syntax.
> +Before+
> {code:sql}
> SELECT s.sales
> FROM (SELECT ss_sold_date_sk, SUM(ss_sales_price) AS sales
> FROM store_sales
> GROUP BY ss_sold_date_sk) s
> JOIN date_dim d
> ON s.ss_sold_date_sk = d.d_date_sk
> WHERE d.d_year = 2000;
> {code}
> +After+
> {code:sql}
> SELECT SUM(ss_sales_price) AS sales
> FROM store_sales s
> JOIN date_dim d
> ON s.ss_sold_date_sk = d.d_date_sk
> WHERE d.d_year = 2000
> GROUP BY s.ss_sold_date_sk;
> {code}
> More examples can be found in the respective papers.
> The first version of the rule aims to cover the simplest form of group-by
> pull up described under "Interchanging the order of grouping and join". The
> more advanced "lazy" variants can be implemented in follow-ups.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
