[CALCITE-5518] RelToSql converter generates invalid order of ROLLUP fields

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.32.0
• Fix Version/s: 1.34.0
• Component/s: jdbc-adapter
• Labels: pull-request-available
• Assignee: Jiajun Xie
• Reporter: Leonid Chistov
• Created: 08/Feb/23 13:23
• Updated: 14/Mar/23 13:53
• Resolved: 26/Feb/23 07:06

Description

RelToSqlConverter generates wrong code for Aggregate nodes with grouping sets classified as `ROLLUP` grouping but with order of rollup not matching order of grouping fields.

This can be demonstrated by the following test, that would fail if added to RelToSqlConverterTest class:

@Test void testGroupingSetsRollupNonNaturalOrder() {
  final String query = "select \"product_class_id\", \"brand_name\"\n"
      + "from \"product\"\n"
      + "group by GROUPING SETS ((\"product_class_id\", \"brand_name\"),"
      + " (\"brand_name\"), ())\n";
  final String expected = "SELECT \"product_class_id\", \"brand_name\"\n"
      + "FROM \"foodmart\".\"product\"\n"
      + "GROUP BY ROLLUP (\"brand_name\", \"product_class_id\")";
  sql(query)
      .withPostgresql().ok(expected);
}

As the result we get the following SQL code:

SELECT product_class_id, brand_name
FROM foodmart.product
GROUP BY ROLLUP(product_class_id, brand_name)

While the correct code would be:

SELECT product_class_id, brand_name
FROM foodmart.product
GROUP BY ROLLUP(brand_name, product_class_id)

Source of the bug is that we treat grouping sets {0, 1}, {1}, {} as rollup grouping, but right after that we generate SQL code as if grouping sets were {0, 1}, {0}, {}.

Issue Links

• links to: GitHub Pull Request #3066