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

["Leonid Chistov (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5518 ]


    Leonid Chistov deleted comment on CALCITE-5518:
    -----------------------------------------

was (Author: JIRAUSER298393):
The source of this bug is the following:
{code:java}
private List<SqlNode> generateGroupList(Builder builder,
    List<SqlNode> selectList, Aggregate aggregate, List<Integer> groupList) { 
.........

switch (aggregate.getGroupType()) {
..........

case ROLLUP:
  return ImmutableList.of(
      SqlStdOperatorTable.ROLLUP.createCall(SqlParserPos.ZERO, groupKeys));{code}
Here we create ROLLUP SQL clause ignoring the "rolling up order" of original Aggregate.

Grouping sets "\{0,1}, \{0}, {}" and "\{0, 1}, \{1}, {}" are both classified as ROLLUP-s and they have equal list of grouping keys (0, 1), but different SQL needs to be generated for them.

 

> RelToSql converter generates invalid order of ROLLUP fields
> -----------------------------------------------------------
>
>                 Key: CALCITE-5518
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5518
>             Project: Calcite
>          Issue Type: Bug
>          Components: jdbc-adapter
>    Affects Versions: 1.32.0
>            Reporter: Leonid Chistov
>            Priority: Major
>
> 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:
> {code:java}
> @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);
> }{code}
> As the result we get the following SQL code:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(product_class_id, brand_name){code}
> While the correct code would be:
> {code:java}
> SELECT product_class_id, brand_name
> FROM foodmart.product
> GROUP BY ROLLUP(brand_name, product_class_id){code}
> 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}, {}.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)