Leonid Chistov created CALCITE-5416:
---------------------------------------
Summary: 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
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)
