[jira] [Updated] (CALCITE-2796) JDBC adapter should convert "GROUP BY ROLLUP(x, y)" to "GROUP BY x, y WITH ROLLUP" for MySQL 5

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-2796?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde updated CALCITE-2796:
---------------------------------
    Description: 
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}}".

  was:
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}}".


> 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
>            Reporter: Julian Hyde
>            Assignee: Julian Hyde
>            Priority: Major
>
> 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)