Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2796

JDBC adapter should convert "GROUP BY ROLLUP(x, y)" to "GROUP BY x, y WITH ROLLUP" for MySQL 5

    XMLWordPrintableJSON

    Details

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

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              julianhyde Julian Hyde
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: