[
https://issues.apache.org/jira/browse/CALCITE-2796?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16786776#comment-16786776
]
Kevin Risden commented on CALCITE-2796:
---------------------------------------
[~julianhyde] - are you planning to merge your branch?
> JDBC adapter should convert "GROUP BY ROLLUP(x, y)" to "GROUP BY x, y WITH
> ROLLUP" for MySQL 5
> ----------------------------------------------------------------------------------------------
>
> Key: CALCITE-2796
> URL: https://issues.apache.org/jira/browse/CALCITE-2796
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
> Affects Versions: 1.18.0
> Reporter: Julian Hyde
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.19.0
>
>
> The JDBC adapter currently pushes down ROLLUP to all dialects. MySQL supports
> the ROLLUP and CUBE functions but only in version 8 and later. MySQL 5 has a
> similar but less powerful feature "GROUP BY ... WITH ROLLUP", but the JDBC
> adapter should use it if possible. For example,
> {code:java}
> SELECT x, y
> FROM t
> GROUP BY ROLLUP(x, y){code}
> should be pushed down to MySQL 5 as
> {code:java}
> SELECT x, y
> FROM t
> GROUP BY x, y WITH ROLLUP{code}
> "GROUP BY ... WITH ROLLUP" cannot be combined with "ORDER BY", but
> nevertheless guarantees output order, and therefore the JDBC adpater should
> just remove an ORDER BY clause if it is satisfied. For example,
> {code:java}
> SELECT x, y
> FROM t
> GROUP BY ROLLUP(x, y)
> ORDER BY x, y{code}
> should be pushed down to MySQL 5 as
> {code:java}
> SELECT x, y
> FROM t
> GROUP BY x, y WITH ROLLUP{code}
> Note that MySQL 5 supports explicit {{ASC}} and {{DESC}} keywords in {{GROUP
> BY}} to control sort direction (e.g. {{GROUP BY x DESC, y ASC WITH ROLLUP}})
> but I cannot see a reason to use it, because if there is no {{ROLLUP}}
> function we can continue to use {{ORDER BY}}. should convert "{{GROUP BY
> ROLLUP(x, y)}}" to "{{GROUP BY x, y WITH ROLLUP}}" for MySQL 5.
> MySQL 5 does not support CUBE, but note that ROLLUP with one argument is
> equivalent to CUBE with one argument; therefore we should convert "{{GROUP BY
> CUBE\(x)}}" to "{{GROUP BY x WITH ROLLUP}}".
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
