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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.18.0
• Fix Version/s: 1.20.0
• Component/s: jdbc-adapter
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 22/Jan/19 18:34
• Updated: 24/Jun/19 15:02
• Resolved: 27/Mar/19 22:33

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,

SELECT x, y
FROM t
GROUP BY ROLLUP(x, y)

should be pushed down to MySQL 5 as

SELECT x, y
FROM t
GROUP BY x, y WITH ROLLUP

"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,

SELECT x, y
FROM t
GROUP BY ROLLUP(x, y)
ORDER BY x, y

should be pushed down to MySQL 5 as

SELECT x, y
FROM t
GROUP BY x, y WITH ROLLUP

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".