[
https://issues.apache.org/jira/browse/CALCITE-5416?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-5416:
------------------------------------
Labels: pull-request-available (was: )
> 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
> Assignee: Jiajun Xie
> Priority: Minor
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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)
