[
https://issues.apache.org/jira/browse/CALCITE-5416?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17642870#comment-17642870
]
Leonid Chistov edited comment on CALCITE-5416 at 12/3/22 4:53 PM:
------------------------------------------------------------------
[~jiajunbernoulli]
I somehow failed to see this test, but I would be brave enough to say that it
looks incorrect for me.
Order of fields in the ROLLUP clause does matter, we cannot just change it
without changing the semantics of ROLLUP.
It looks like in general case there is really no way to generate such clause
with a single SELECT statement.
Even *Other Considerations When using ROLLUP* section of the
[https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html] article
proposes that we use second SELECT clause for the sort that does not match the
order of fields in ROLLUP.
was (Author: JIRAUSER298393):
[~jiajunbernoulli]
I somehow failed to see this test, but I would be brave enough to say that it
looks incorrect for me.
Order of fields in the ROLLUP clause does matter, we cannot just change it
without changing the semantics of ROLLUP.
It looks like in general case there is really no way to generate such clause
with a single SELECT statement.
Even *Other Considerations When using ROLLUP* of the
[https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html] article
proposes that we use second SELECT clause for the sort that does not match the
order of fields in ROLLUP.
> RelToSql converter generates invalid code when merging rollup and sort clauses
> ------------------------------------------------------------------------------
>
> Key: CALCITE-5416
> URL: https://issues.apache.org/jira/browse/CALCITE-5416
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.32.0
> Reporter: Leonid Chistov
> Priority: Minor
>
> For SQL dialects (MySQL, Hive, MsSQL) that do not support "GROUP BY
> ROLLUP(...)" syntax, but do support "GROUP BY ... WITH ROLLUP" syntax
> instead, wrong code is generated by RelToSqlConverter in the following
> situation:
> * There is an Aggregate node with ROLLUP grouping
> * It has a parent Sort node with an order of fields different from the order
> of fields in ROLLUP Aggregation
> This can be demonstrated by the following test, that would fail if added to
> RelToSqlConverterTest class:
> {code:java}
> @Test void testSelectQueryWithGroupByRollupOrderByReversed() {
> final String query = "select \"product_class_id\", \"brand_name\"\n"
> + "from \"product\"\n"
> + "group by rollup(\"product_class_id\", \"brand_name\")\n"
> + "order by 2, 1";
> final String expectedMysql = "SELECT `product_class_id`, `brand_name`\n"
> + "FROM `foodmart`.`product`\n"
> + "GROUP BY `product_class_id`, `brand_name` WITH ROLLUP";
> sql(query)
> .withMysql().ok(expectedMysql);
> }
> {code}
> As the result we get the following SQL code:
> {code:java}
> SELECT `product_class_id`, `brand_name
> FROM `foodmart`.`product
> GROUP BY `brand_name`, `product_class_id` WITH ROLLUP {code}
> It can be observed that order of fields of aggregation was changed to match
> the order of fields in ORDER clause, thus changing the semantics of the
> ROLLUP clause itself.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
